見出し画像

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


動画による解説

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


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

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

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

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

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

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



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

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

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


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

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

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


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

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

画像4
もともとの合計と一致しない


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

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

画像5
人が手で作った読替表


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

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

画像4


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

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


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

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

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

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

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

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

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

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

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


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

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


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

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

画像7
Power Query エディター>ホーム>行の削除>重複の削除


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

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

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


さいごに

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

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

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


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

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



おしまい。


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

ここから先は

0字

こちらのマガジン購読で有料販売している過去の記事をすべて閲覧することができます。有料記事は1本500円程度で販売しておりますので、個別に購入いただくよりもお得になっています。

ExcelやPowerQueryを実務で使うために有用なテクニックやノウハウを書いた有料記事をまとめています。過去に投稿した有料記事を、す…

よければサポートしていただけると嬉しいです😀 有用な記事や動画を制作していけるように頑張ります❗️