【エクセル パワークエリ】便利で危険なクエリのマージ【Excel PowerQuery】
動画による解説
パワークエリには、VLOOKUP関数の代わりに、テーブル同士のデータ結合をすることができる「クエリのマージ」という非常に便利な機能があります。
厳密にいえば仕様は異なりますが、現象としてはVLOOKUP関数をつかったような結果を得ることができますので、パワークエリではVLOOKUPの代わりによく使われます。
ところが、Excel実務でトランザクションテーブルとマスタテーブルを「クエリのマージ」をつかって結合する場合、注意しなければいけないことがあります。
それは、トランザクションテーブルに重複がある状態で「クエリのマージ」を使うと、トランザクションテーブルのレコードが重複して増えてしまうということです。
言い換えると、トランザクションテーブルの行がダブって増殖します。
マスタテーブルに重複がない場合
マスタテーブルに重複データがなければ、「クエリのマージ」でテーブル同士のデータを左外部で結合すると、VLOOKUP関数と同じ結果を得ることができます。
マスタテーブルに重複がある場合
マスタテーブルに重複データがあると、VLOOKUPであれば一番上のレコードだけが結合されることになるので大して問題はありませんが、「クエリのマージ」だとマスタテーブルの重複するレコードの数だけ、トランザクションテーブルのレコードも重複し増えることで、すべての結合パターンのレコードが生成されます。
VLOOKUP関数に慣れていると不思議な現象に感じるかもしれませんが、これは「クエリのマージ」という機能の仕様としては正しい動作です。
この仕様を理解せずに「クエリのマージ」をVLOOKUP関数と同じ感覚で使ってしまうと、マージ後に出来上がるトランザクションテーブルのレコードが水増しされたように感じるはずです。
マスタテーブルがきちんとコード管理されていたり、システムなどからエクスポートしたマスタテーブルをそのままつかっていたりすれば、こういったデータの重複はほとんど起こりません。
ところが、実務においてはマスタテーブルを人が手で作成するというのは、よくあります。
データをカテゴライズするために、Excelで読替表を作っている場合などです。
人が手で作成したマスタテーブルは、意図的に重複データをチェックする仕組みを入れていないと、結構な割合で重複したデータが入っています。
そんなマスタテーブルで、VLOOKUPと同じ感覚のまま「クエリのマージ」を使うと、前述のとおり、トランザクションテーブルの行数が増えることでマージ前後の集計値が一致しない、ということになってしまいます。
重複データに対処する方法
マスタテーブルのデータ重複に対処するために、私は以下のような方法をとっています。
方法１．Excelのマスタテーブルに重複データをチェックする仕組みを入れる
そもそものマスタテーブルのファイルの時点で重複データのチェックをする方法です。
■数式で重複データをカウントする。
■条件付き書式の強調表示で重複する値に色をつける。
など
重複チェックの数式
=IF(COUNTA(商品カテゴリマスタ[商品名])-COUNTA(UNIQUE(商品カテゴリマスタ[商品名]))=0,"OK","NG!!!")
※ここでは重複チェックにUNIQUE関数をつかっていますが、カウントの方法はCOUNTIFでもなんでも構いません。
重複データがあった場合は、マスタテーブルのExcelシート上で
■重複データを手動で行削除する。
■データタブにある「重複データの削除」を使う。
などで重複データを削除します。
重複チェックをスキップして、最初から「重複データの削除」を使ってもいいとは思いますが、重複データのどれを残すべきか確認したいのであれば、重複データのチェック、重複データの削除、という手順を踏んだほうが安心です。
この方法の懸念点は、いくらチェック手順を追加しても、担当者がチェックや重複行の削除自体を忘れる可能性があることです。
方法２．クエリに重複データを削除するステップを追加する
マスタテーブルを取得するクエリの最後に「重複の削除」のステップを最初から入れてしまう方法です。
このステップをあらかじめ入れておけば、マスタテーブルのファイルに重複データがあっても、クエリで取得した後に自動で重複行は削除されます。
この方法の懸念点は、重複行の一番上の行だけが残り、下にある重複行がすべて削除されてしまうことです。
私の環境では、マスタテーブルに後から追加したレコードの方が正しいことが多いので、一番新しい、つまり一番下の行を残して重複データを削除する方式をとっています。
さいごに
パワークエリをつかうと、Excelによる作業の多くの部分を簡単に自動化できるようにはなりますが、それぞれの機能の仕様をきちんと理解していないと、全く想定していない結果になってしまうことがあります。
特に今回のようなケースは、あらかじめ知っていないと、集計が終わり、一番最後の合計チェックの段階になって発覚することになり、確認のための手戻り作業が発生し、余計な時間を割くことになってしまいます。
Excel実務でパワークエリを利用し始めると、このようなミスや修正を繰り返しながら、時間をかけて段々と経験を蓄積することになります。
私がストアカで開催するオンライン講座では、そういった実務で必要となるノウハウの部分を重点的に解説しています。
パワークエリ習得に要する時間を短縮したい、手戻り作業の手間を増やしたくない、といった方は、ぜひこちらの講座をご覧ください。
おしまい。
続きをみるには
Excel PowerQuery に関する記事まとめ
ExcelやPowerQueryを実務で使うために有用なテクニックやノウハウを書いた有料記事をまとめています。過去に投稿した有料記事を、す…
サポートに対して特典等の提供ができず申し訳ありません。サポートはこれからも記事の執筆や動画の制作等の活動を続けるためのモチベーションにさせていただきます。