ピボットを使うとどんなデータが入っているか瞬時に把握できます。たぶん何よりも効率的でないかと思います。これを使ってデータを整える時に私がよくやる方法を紹介します。Excelをよく使う方でないと難しいかもしれませんね。イメージしながら読んでください。
「乱れたデータ」といっても色々なタイプがあります。今回は主に表記のゆれをなおすのに役立つ方法です。
簡単に言うと
１．ピボットを使ってグループ化する
２．データを変換する
３．元のデータに列を作って戻す
４．戻した列で再度ピボット集計
では具体的にやってみますね。
ピボット集計を使ってデータを整える
ピボットを使ってグループ化する
この表の品目欄を整えると仮定します。同じ品なのに表記が違っていますね。例えば「みかん」と「ﾐｶﾝ」、「ノート」と「ノートブック」といった感じです。30行ぐらいなら目視でも直せそうですが、品目が多いと骨が折れる作業です。手作業でやるにしても出来るだけ労力を減らしたいですね。
まずこのデータを品目でグループ化します。
▼元データシート
表全体を選択して「挿入」→「ピボットテーブルの挿入」をクリックします。
細かい手順は省略しますね。メニューに従って別シートにピボット集計を作成してください。操作は30秒もかかりません。やり方が分からない方は簡単なのでぜひマスターしてください。ピボット集計で検索すると基本操作がたくさん紹介されています。
作成直後は何も集計されていません。
フィールドリストから「品目」を選びます。
行ラベルにドラッグします。
行ラベルだけの表が出来ました。品目の種類が50音順で並びました。これってすごいことですよ。
▼「ピボット集計1」シート
これだけでも色んなことが分かります。
・何種類のデータが含まれているか
・半角・全角の表記のゆれがある
・消しゴムが３つある→おそらく後ろに空白がくっついている
ついでなので品目を値にドラッグします。
件数も集計されました。
集計用の項目をつくる
ピボット集計のデータをC列に値でコピーします。
＜方法は２つ＞
方法１．具体的にはA4からA15を範囲選択→C4で右クリック→形式を選択してはりつけ→値にチェックを入れてＯＫをクリック。
方法２．C4を選択=a4と入力してエンター→下までコピー
（GETPIVOTデータが入らないようにするためです）
▼「ピボット集計1」シート
集計用項目の表記をまとめたい項目に書き換えます。ﾐｶﾝ→みかんという風にします。空白も取り除きます。データがたくさんある時はTRIM関数を使うと一括でできますのでTRIM関数で取り除いてから、この作業をやってもよいでしょう。
ここでは、品目の件数も入れましたが無くてもできます。私も無しでやることが多いです。でも件数が入っているとそのデータの全体像がイメージしやすいのでその必要があれば便利です。「全体の中のごく一部なら他にまとめてしまおうか」とか判断する材料になります。
元のデータに列を作って戻す
元のデータに「集計用品目」の列を戻すための列を挿入します。この時VLOOKUP関数を使います。
関数ダイアログボックスは使わず手入力してください。使うと色々厄介なことがあります。（下図参照）
▼「元データ」シート
戻した列で再度ピボット集計
黄色の列を行ラベルに持ってきて集計しました。表記のゆれが整えられています。ついでに品目数と売り上げも集計しました。これで全体像が把握できます。
▼「ピボット集計２」シート
数千行とか数万行とかデータがあっても、このようにまとめるとぐっと楽になります。数が多いと一度に整わないので、この作業を何回か繰り返すこともあります。
ピボットを使うにあたって
謎のデータの詳細を見る方法
一瞬でできますよ。例えばこの状態の時です。「片仮名のﾐｶﾝっていつの売り上げ？」といった感じで元のデータを知りたい時、データの個数の数字の上でダブルクリックします。この場合セルB6。
新たにシートが自動的に追加され、該当のデータが再現されました。クリックごとにシートが追加されるので、私は確認したらこのシートはすぐ削除しています。一瞬で出てくるので便利ですよ！
書式の融通がきかないけど上手くつきあう
ピボットテーブルは途中経過として使いましょう。ピボットで出来る表の書式はあまり融通がききません。もちろんある程度デザインは取り揃えられているのですが、そうは言っても凝った表はできません。あくまでも、集計するためのツールとして考えましょう。その方が楽です。整えたい時はデータを別のシートにコピーして書式を触るのが得策です。
慣れたらデータ分析が非常に楽になります。ピボットのデザインが使いにくいといって使わないのは非常にもったいないです。私は上司にはピボットの見方を説明してそのまま見てもらっていて、他へ提出する時だけ書式を整えています。とても楽です。
getpivotdata関数のよしあし
どこかのセルで=を入力してピボットの表をクリックすると現れる「GETPIVOT関数」。自動で出てくるから楽ですね。しかし時にはこれが邪魔になる時もあります。
例えばこのセルの関数を他へコピーする時。ちょうど、先に説明したデータを整えるときのような場合、固定されてしまいやりにくいです。その時はピボットで参照したいセルを手入力します。例えば=a4などといったようにします。するとコピーが普通に出来ます。
更新する時にはぶつからないように注意
元データを書き換えたら、ピボットデータを選択して右クリック→更新をしますが、この時、表が広がることがあります。広がってしまう領域に元々データが入力されているとエラー出ます。
ピボットのエラーって分かりにくいことが多いですが、これも３大エラー原因のひとつでしょう。更新前には表がぶつからないか注意してくださいね。
上のやり方ではピボット集計１のＣ列で集計用項目に置き換える作業をしていますが、実践ではもっと離してやります。何かの拍子に表が横に広がると面倒なためです。
ケースバイケースで使い分けるといいですね。
以上、簡単ですが本日はここまでにします。伝わりましたでしょうか。どなたかのお役に立てると幸いです。
また、お会いしましょう。
