Power Queryで2つのシートの差分を抽出

Power Queryを使いましょう

概要

Power Queryにてノーコードで2つのシートを比較して、異なってるセルの値をリストの形式で抽出するテクニックです。一度作ってしまえば、後は2つのシートの中身を入れ替えて、右クリック⇒更新するだけで作業が完了するので、RPAを用いずに業務の自動化を実現可能です。

Power Query１つ出来る出来ないで大きな差になり、事務職の人にとって大きな武器の１つになると思います。

今回はPower Queryの以下の機能を利用しています

• 型の変換

• 値の置換

• ピボット解除

• インデックス列の追加

• クエリのマージ

テクニック

手順としては以下のような感じで作成します。2つのシートのデータはどちらも社員番号等を基準にソートして揃えておきます。

1. シートをテーブル化しておく

2. 型の変換で全体をテキストに変換しておく

3. 値の置換でnullを0に置き換える

4. 列のピボット解除でリストに変換する

5. インデックス列を追加して1から連番を振る

6. これを2つのシートで行っておく

7. 新たにクエリでマージにて作成をする

8. 2つのリスト化したクエリをインデックス列を元に連結（内部結合にて）

9. カスタム列にて２つのリストの値が一致する場合は0, 一致しない場合は-1を返す列を作成する。数式としてはいかのようなもの（列名は不一致検知とした）
   if [メイン] = [比較] then 0 else -1

10. フィルタにて、不一致検知が-1のものだけを抽出する

11. シートに書き出す

双方のシートで同じ社員番号のデータがあれば連結されて、2つの同じ項目の値を比較し、一致してるかしていないかで判定します。テーブルなので、データ量が増えても手直しは不要です。

これが完成形になります

次回以降は2つのシートのテーブルデータを入れ替えて、右クリック⇒更新するだけで今回の作業と同じ事をPower Queryが行ってくれるので、RPAよりも高速確実に処理出来るのがPower Queryの良い所です。

自分はこのリストを元にVBAで続きの処理を作ったりしていますので、組み合わせると余計なコードを書かず最低限のコードのみで開発も出来る利点もあります。Power Queryで出来ることをVBAでロジックを組む必要が無いのもありがたいですね。

詳細や応用編はこちら

Power Queryで２つのExcelシートの差分を取り出してみる – 🌴 officeの杜 🥥

VBAにて２つのExcelシートの差分を取り出してみる – 🌴 officeの杜 🥥

上記のPower Queryのエントリーには、サンプルのシートもありますので、自由にダウンロードして弄り倒してみてください。