Excelピボットテーブル×VLOOKUP（XLOOKUP）関数。同じ取引先だけど複数の表記があるケース。

Excelでデータ集計するには、ピボットテーブルが便利です。
VLOOKUP関数（XLOOKUP）と組み合わせる使い方もあります。

 

「データは規則正しく」が原則

データ、デジタルは、あいまいさをなくさなければいけません。
規則正しく一定のルールでデータをつくる必要があります。

たとえば、会社名でいえば、

「タイムコンサルティング」

と

「株式会社タイムコンサルティング」

と

「㈱タイムコンサルティング」

 

人間がみれば、ああ、同じなんだろうなと推測できますが、パソコン（Excel）はそうではありません。
むしろ、これらを同じものとみなしてしまうほうが困るでしょう。

（AIなら認識してくれるかもしれませんが）

 

「株式会社タイムコンサルティング」と表記するのであれば、それを守り、必ずそう入力しなければいけません。
そうしなければ、

タイムコンサルティング　　　　　　100
株式会社タイムコンサルティング　　200
㈱タイムコンサルティング    300

と別々に集計してしまいます。

本当は、

株式会社タイムコンサルティング　　600

という集計結果が欲しくても、そう集計することはできないのです。
EXCELで欠かせないピボットテーブルを使っても、うまく集計できません。

ピボットテーブルで集計してもうまくいかないケース

たとえば、こういったデータがあるとします。
取引先別の売上データで、これを取引先別に集計するケースです。
パット見ただけでも、「武道」「武道㈱）」は一緒っぽいなとわかります。

このデータで、Ctrl＋T→Enter（CtrlとTを同時押しし、その後、Enter）で、

 

テーブルにし、

 

ピボットテーブルで集計するとこうなります。
アトランティススプリングなんとかが3つできてしまいました。

 

ピボットテーブルには、グループ化という機能もあり、このように1つのグループにすることができます。
ただ、ほとんど使わない機能です。
結局、手でちょっとずつやらなければいけませんし手間がかかりますし、ピボットテーブルの項目は基本的に昇順（数字が小さい、五十音、アルファベット等）で並ぶので、㈱〇〇と、〇〇は離れたところにあります。
それらを見つけて、グループ化するのは、ちょっとしんどいでしょう。

 

こういったとき、私は、VLOOKUP関数を使っています。
※記事執筆時現在、先行でXLOOKUP関数を使っており、通常のバージョンでも使えるようになる予定で、XLOOKUP関数のほうが便利です。
こちらの記事に書いています。

VLOOKUP関数を使ってはいけない。よりシンプルなXLOOKUP関数を使う。 | EX-IT

 

VLOOKUP（XLOOKUP）関数でデータを変換してピボットテーブルで集計

まず、項目のデータを準備しなければいけません。
ピボットテーブルは、同じデータ（取引先）なら集計して、単一の項目にできるので、これを利用します。

ピボットテーブルの項目をコピーして、

別シートに貼り付け、ひとまずＢ列にそのままコピーします。
こういったとき、セルA4にカーソルを置いた後、Ctrl+Shift＋下で項目をすべて選択し、そのままCtrl+Shift+右でＢ列の該当範囲を選択してから、

Ctrl+Rで右方向へコピーすると楽です

 

 

B列の項目をきれいにそろえていきます。
集計で項目に㈱が必要なければ、空白に置換して、整えましょう。

同様に、「株式会社」を空白に置換します。

㈱アリステラコンサルティング→アリステラコンサルティング

株式会社アリステラコンサルティング→アリステラコンサルティング

と同じものを同じ表現にそろえるわけです。

半角カナや全角英数字があったら、JIS（半角を全角へ）、ASC（全角を半角へ）といった関数を使わなければいけません。
空白（スペース）があっても、違うものとみなすので、もしある場合は、半角スペースを［置換後の文字列］に何もいれず置換して、消すようにしましょう。

こういったことを続けて元の取引先データをどう変換するかという表（シート「code」）をつくります。

 

こういったVLOOKUP関数を入れて、「取引先をシート「code」のA列から探して、完全に一致すれば、その行のB列のデータを表示する」という意味です。

 

なお、XLOOKUP関数だとこう入れます。
こちらのほうがシンプルでメンテナンスもしやすいです。

 

ピボットテーブルを右クリックして［更新］すれば、

右側のピボットテーブルのフィールドに、「変換後取引先」が出てくるので、

 

「取引先」と入れ替えます。
（入れ替えはチェックボックスのチェックやマウスのドラッグでできます）

 

ただ、まだ、完全ではないので、元のデータに戻って修正して、ピボットテーブルで更新して……というのを繰り返します。

その結果、このように集計ができるようになるわけです。

データをきれいに整えておき、きれいに集計できることが、やはり理想ではあります。

 

---

■編集後記

昨日は、RPA本の最終チェックを中心に。
完成が見えてきました。

■「1日1新」

ストアカ講師へ問い合わせ
グリーンスクリーン組み立て

 

■娘(2歳）日記

最近、服を自分で脱げるようになりました。
おむつも全部。
風呂に入るときに楽です。
ポイっと服を投げてしまいますが。