【エクセル パワークエリ】便利で危険なクエリのマージ【Excel PowerQuery】

---

動画による解説

#0426.クエリのマージでVLOOKUP関数のようなデータ接続をする | Excelドカタの集会所

動画で学ぶパワークエリ・パワーピボット

---

パワークエリには、VLOOKUP関数の代わりに、テーブル同士のデータ結合をすることができる「クエリのマージ」という非常に便利な機能があります。

厳密にいえば仕様は異なりますが、現象としてはVLOOKUP関数をつかったような結果を得ることができますので、パワークエリではVLOOKUPの代わりによく使われます。

ところが、Excel実務でトランザクションテーブルとマスタテーブルを「クエリのマージ」をつかって結合する場合、注意しなければいけないことがあります。

それは、トランザクションテーブルに重複がある状態で「クエリのマージ」を使うと、トランザクションテーブルのレコードが重複して増えてしまうということです。

言い換えると、トランザクションテーブルの行がダブって増殖します。

クエリのマージによってデータ行が増殖

マスタテーブルに重複がない場合

マスタテーブルに重複データがなければ、「クエリのマージ」でテーブル同士のデータを左外部で結合すると、VLOOKUP関数と同じ結果を得ることができます。

「クエリのマージ」によって行は増えない

マスタテーブルに重複がある場合

マスタテーブルに重複データがあると、VLOOKUPであれば一番上のレコードだけが結合されることになるので大して問題はありませんが、「クエリのマージ」だとマスタテーブルの重複するレコードの数だけ、トランザクションテーブルのレコードも重複し増えることで、すべての結合パターンのレコードが生成されます。

「クエリのマージ」によって行が増える

VLOOKUP関数に慣れていると不思議な現象に感じるかもしれませんが、これは「クエリのマージ」という機能の仕様としては正しい動作です。

この仕様を理解せずに「クエリのマージ」をVLOOKUP関数と同じ感覚で使ってしまうと、マージ後に出来上がるトランザクションテーブルのレコードが水増しされたように感じるはずです。

もともとの合計と一致しない

マスタテーブルがきちんとコード管理されていたり、システムなどからエクスポートしたマスタテーブルをそのままつかっていたりすれば、こういったデータの重複はほとんど起こりません。

ところが、実務においてはマスタテーブルを人が手で作成するというのは、よくあります。
データをカテゴライズするために、Excelで読替表を作っている場合などです。

人が手で作った読替表

人が手で作成したマスタテーブルは、意図的に重複データをチェックする仕組みを入れていないと、結構な割合で重複したデータが入っています。

そんなマスタテーブルで、VLOOKUPと同じ感覚のまま「クエリのマージ」を使うと、前述のとおり、トランザクションテーブルの行数が増えることでマージ前後の集計値が一致しない、ということになってしまいます。

重複データに対処する方法

マスタテーブルのデータ重複に対処するために、私は以下のような方法をとっています。

方法１．Excelのマスタテーブルに重複データをチェックする仕組みを入れる

そもそものマスタテーブルのファイルの時点で重複データのチェックをする方法です。

■数式で重複データをカウントする。
■条件付き書式の強調表示で重複する値に色をつける。
　など

条件付き書式で重複データに色をつける

重複チェックの数式
=IF(COUNTA(商品カテゴリマスタ[商品名])-COUNTA(UNIQUE(商品カテゴリマスタ[商品名]))=0,"OK","NG!!!")

※ここでは重複チェックにUNIQUE関数をつかっていますが、カウントの方法はCOUNTIFでもなんでも構いません。

重複データがあった場合は、マスタテーブルのExcelシート上で

■重複データを手動で行削除する。
■データタブにある「重複データの削除」を使う。

などで重複データを削除します。

重複チェックをスキップして、最初から「重複データの削除」を使ってもいいとは思いますが、重複データのどれを残すべきか確認したいのであれば、重複データのチェック、重複データの削除、という手順を踏んだほうが安心です。

この方法の懸念点は、いくらチェック手順を追加しても、担当者がチェックや重複行の削除自体を忘れる可能性があることです。

方法２．クエリに重複データを削除するステップを追加する

マスタテーブルを取得するクエリの最後に「重複の削除」のステップを最初から入れてしまう方法です。

Power Query エディター＞ホーム＞行の削除＞重複の削除

このステップをあらかじめ入れておけば、マスタテーブルのファイルに重複データがあっても、クエリで取得した後に自動で重複行は削除されます。

この方法の懸念点は、重複行の一番上の行だけが残り、下にある重複行がすべて削除されてしまうことです。

私の環境では、マスタテーブルに後から追加したレコードの方が正しいことが多いので、一番新しい、つまり一番下の行を残して重複データを削除する方式をとっています。

さいごに

パワークエリをつかうと、Excelによる作業の多くの部分を簡単に自動化できるようにはなりますが、それぞれの機能の仕様をきちんと理解していないと、全く想定していない結果になってしまうことがあります。

特に今回のようなケースは、あらかじめ知っていないと、集計が終わり、一番最後の合計チェックの段階になって発覚することになり、確認のための手戻り作業が発生し、余計な時間を割くことになってしまいます。

Excel実務でパワークエリを利用し始めると、このようなミスや修正を繰り返しながら、時間をかけて段々と経験を蓄積することになります。

私がストアカで開催するオンライン講座では、そういった実務で必要となるノウハウの部分を重点的に解説しています。

パワークエリ習得に要する時間を短縮したい、手戻り作業の手間を増やしたくない、といった方は、ぜひこちらの講座をご覧ください。

Excelパワークエリ講座 入門編 実習でパワークエリの威力を体験 / 千葉悠市 @NO‼︎マクロ、NO‼︎VBAでExcelを効率化

「Excelクエリ講座 初級編 PowerQueryの基本を全て学ぶ」by 千葉悠市 @NO‼︎マクロ、NO‼︎VBAでExcelを効率化｜ストアカ

おしまい。

動画で学ぶExcel パワークエリ・パワーピボット