読者です 読者をやめる 読者になる 読者になる

死ぬまでWEB屋

WEBの事とか色々書いてます

WEB UNTIL DiE

いんたーねっとはおもしろい

【Excel術】今すぐ実践出来るエクセルのデータ加工術


スポンサードリンク

今回は超絶仕事が早く終わるエクセル術!!

 

とか

 

今まで誰もが使わなかった知られざるエクセルの裏ワザ!!

 

というものではなく、以外とみんなこういう使いかたしないよね?

という少し変化球なエクセルの使い方です。

 

 

【こういう人向け】

・50行ぐらい〜のリストを普段の業務で扱う人

 

CSVデータを使って業務を行う人

 

・その他エクセルを使ってデータ加工を行う人

 

 

エクセルを使って何らかの作業を行う場合

絶対にやってはいけないのが「手作業」

 

ミス発生のもとや、多大な工数がかかってしまいますよね。特にリスト作成やデータ加工となると、精度がモノを言いますし、なによりもデータの量が膨大だと、手作業では到底終わらせることが出来ません。

 

そこで今回は今まで何千・何万というデータ加工を行ってきた僕が、特に使うエクセル術を紹介します。

 

 

リストを一気に分割する

f:id:uraweb:20160713233056p:plain

こういうリストがあるとします。品番で使われてたり、CSVデータだとよくシステム上でのデータの固有名だったりもありますね。このリストを英語・ひらがな・数字に分割したい場合。以下の手順をとります。

 

 

①分割したい部分を選択

f:id:uraweb:20160713233337p:plain

分割したい情報が入力されているセルを選択します。

 

 

②「データ」タブにある「区切り位置」を選択

f:id:uraweb:20160713233510p:plain

MACとWINでUIが違いますが、どちらにも区切り位置の機能は存在します。

 

 

③「区切り記号付き(デフォルト)」を選択し、「次へ」をクリック

f:id:uraweb:20160713233658p:plain

 

 

④区切り位置の情報を入力

f:id:uraweb:20160713233830p:plain

「その他」にチェックを入れ、何でセルを区切るか(この場合は「-」)を右側に入力。画面下にある「データのプレビュー」で区切りたい位置に縦線が入っていればOK!「次へ」をクリック。

 

 

⑤ポップアップを無視して「OK」をクリック

f:id:uraweb:20160713234021p:plain

とりあえずは無視していいです。

 

 

⑥セルの分割が完成!

f:id:uraweb:20160713234136p:plain

英語・ひらがな・数字に分けることが出来ました!

 

 

ちょっと応用編

f:id:uraweb:20160714000743p:plain

こんな感じのリストがあったとして、「GA-◯◯◯◯◯◯」の部分だけ抜き出したいとします。ただ色んな記号があって、どの位置で区切れば上手く欲しい部分だけ一気に抜き出せるかわかりませんよね。こんな時は置換を使います。

 

 

①抜き出したい部分の直前をコピー

f:id:uraweb:20160714001126p:plain

 

 

②置換で区切り位置を一括で挿入

f:id:uraweb:20160714001246p:plain

置換でコピー部分の後ろに区切り位置の基準となる記号を入力します。

(この場合は区切り位置を「$」にしています。)

 

 

③同様に後ろ側も置換

f:id:uraweb:20160714001459p:plain

後ろ側もコピーして区切り位置となる記号を入力します。

 

 

f:id:uraweb:20160714001621p:plain

こんな感じで抜き出したい部分の前後に「$」が入ります。

 

 

④前述の区切り位置の処理をして完成!

f:id:uraweb:20160714001626p:plain

あとは前述の区切り位置の処理をすれば見事抜き出せました!

このように、置換を使ってある程度柔軟に区切り位置の挿入が出来るので、多少の可変要素があるリストでは重宝します。

 

※「'」で置換をせずとも抜き出しが可能ですが、説明上このような処理をしています。

 

 

 

COUNTIFで固有のものを1つずつ抜き出す

f:id:uraweb:20160714003226p:plain

上記のような感じの順不同、それぞれの情報が何個ずつあるかもわかりません。

せめてそれぞれ1個ずつ抜き出せたら、COUNTIFをつかってそれぞれの情報が何個ずつあるのか計算する事ができます。

 

なので、今回はそれぞれの情報を1つずつ抜き出す処理をします。

 

 

①COUNTIFで解決!

f:id:uraweb:20160714003524p:plain

リストの上方部分、一番上の右側にCOUNTIFを入力します。

 

 

②COUNTIFの関数を少し変えます。

f:id:uraweb:20160714003641p:plain

通常のCOUNTIFは 

 

=COUNTIF(カウントをする範囲,カウントしたい情報)

 

 

上記のような関数ですが、そのままつかってしまうと

f:id:uraweb:20160714004045p:plain

それぞれの情報が何個あるかという値しかでません。

なので今回は前述の「COUNTIF改」を使います。(今名づけましたw)

 

=COUNTIF($A$4:A4,A4)

 

上記のように入力します。

それを下にドラッグすると・・・

f:id:uraweb:20160714004418p:plain

 

こんな感じにでました。

この数字がなにを意味するかというと

上から数えた時に、同じ情報が何個あるか?という計算をしています。

実際にみてもらった方が早いですねw

 

 

f:id:uraweb:20160714005012p:plain

上記の範囲$A$4:A7の範囲には「AA-あいうえお-10001」が2つあります。

したがって、上記のセルをアクティブにしている部分の値は2になります。

これだけではわかりにくいとおもうので、もう少し下にずらすと・・・

 

 

f:id:uraweb:20160714004625p:plain

上記の範囲$A$4:A14の範囲には「AA-あいうえお-10001」が3つあります。

したがって、セルをアクティブにしている部分の値は3になります。

 

 

おわかりいただけただろうか・・・w

 

 

 

下に行けば行くほど、カウントする領域が増えて、その中にいくつ固有の情報があるかを計算してくれる関数になります。つまり下にずらしていった際に、同じ情報があればカウントされてどんどん数字が大きくなっていくという計算です。

 

この方法で今自分がカウントしたいセルの情報は上から数えて何個目なのか?という値が出てきます。

 

 

f:id:uraweb:20160714005712p:plain

あとは絞り機能で「1」のみしぼれば、固有の情報が1つずつ出てきます。

1つずつ出すことが出来ればそれを起点としてCOUNTIFや、SUMIFなどを使って表形式にすることもできますね!

 

 

 

&関数でつなげる

f:id:uraweb:20160714010400p:plain

項目A・B・Cにある文字列を繋げたい場合、コピペしてセルの中身を隣に移して・・・というのは非効率ですよね!こういう時は「&」を使いましょう!

 

 

①関数は超カンタン!

f:id:uraweb:20160714010607p:plain

セルを選択して、間に「&」を入れるだけです!

 

 

②あとはドラッグで下にずらすだけ!

f:id:uraweb:20160714010728p:plain

はいっ!全部くっつきました!

間に「&」を入れるだけなので、簡単ですよね!

 

 

まとめ

ということで、特によく使う3つの方法をご紹介しました。

膨大なデータを扱うほど、一気に加工出来た時は気持ちがいいですね!

時短にもなり、手動でやるよりもミスが起きる可能性を極限まで減らしてくれるので、なるべく関数やエクセルの機能を使って処理するのがベターです。

 

今後もこんな感じでノウハウ記事を更新していこうとおもいます!