Excelで複数のテーブルを結合する際に、XLOOKUPとPower Queryのどちらがいいか?

はじめに

Excelで複数のテーブルを結合する際に、XLOOKUPを使う方法とPower Queryを使う方法についてそれぞれ記事を書きました。

blog.jbs.co.jp

blog.jbs.co.jp

じゃあ結局どっちがいいのか、という事について書き…ません。

タイトルに反しますが、それぞれ特徴があるので、やりたいことに合わせて選びましょう、というのが回答になるかと思いますが、 今回は特に、僕が以前ハマった挙動の違いについて共有します。

元のデータ

またも以前の記事と同じなのですが、再掲します。

Usersテーブル

例えば、社員番号、名前、UPNのある社員一覧があるとします。

EmpNo Name UPN
1111 User A userA@example.com
2222 User B userB@example.com
3333 User C userC@example.com
4444 User D userD@example.com

Formsテーブル

さらに、Formsのアンケート結果をダウンロードしたものがあるとします。

ID 開始時刻 完了時刻 メール 名前 answer1 answer2 answer3
1 10:00 10:05 userA@example.com User A 1 2 3
2 11:00 11:05 userD@example.com User D 2 3 4
3 12:00 12:05 userB@example.com User B 3 4 1

結合したデータ

今回はすでに、XLOOKUPなりPower Queryなりでデータの結合が終わっている状態を考えます。

EmpNo Name UPN answer1 answer2 answer3
1111 User A userA@example.com 1 2 3
2222 User B userB@example.com 3 4 1
3333 User C userC@example.com #N/A #N/A #N/A
4444 User D userD@example.com 2 3 4

データのアップデート時の挙動の違い

ここで、アンケートが未提出だったUser Cが後からアンケートを提出したケースを考えます。

元のテーブルにデータを追加するケース

まず、元のFormsテーブルにデータを追加する、という方法が考えられます。

ID 開始時刻 完了時刻 メール 名前 answer1 answer2 answer3
1 10:00 10:05 userA@example.com User A 1 2 3
2 11:00 11:05 userD@example.com User D 2 3 4
3 12:00 12:05 userB@example.com User B 3 4 1
4 13:00 13:05 userC@example.com User C 4 1 2
XLOOKUPの場合

XLOOKUPでテーブルを結合している場合は、すぐにデータが反映されます。*1

Power Queryの場合

一方、Power Queryで結合したテーブルの場合は、明示的に更新するまでは反映されません。

結合先のテーブルにデータを追加するケース

続いて、結合してできた方のテーブルに直接値を入れてしまう、という方法も考えられます。

せっかくテーブルを分けているので分けて管理した方がとは思うのですが「アンケート回答の無かったCさんには口頭で直接確認してしまったのでFormsの回答が無く、直で記入した方が楽」というケースは無くはないのかなと思います。

XLOOKUPの場合

XLOOKUPでテーブルを結合している場合は、セル内のXLOOKUPの式を上書きして数値が入ります。

もし、そのあとでFormsテーブルにUser Cのデータが入っても、追従しなくなってしまいます。

Power Queryの場合

一方、Power Queryで結合したテーブルの場合でも、上書きは可能です。

ただ、この場合、クエリそのものに手を加えているわけではありません。

この状態でクエリの更新を行うと、直接上書きしたデータは消えて、クエリの結果にリセットされます。*2

挙動の違いのまとめ

テーブル結合方法 参照先データの反映 結合後のセルの上書き セル上書き後のデータ更新の影響 セル上書き後に参照先データを更新した場合
XLOOKUP 即時 可能 なし(上書きされた値を保持) 反映されない
Power Query 更新実行時 可能 あり(クエリ結果でリセット) 反映される

結論

  • XLOOKUPかPower Queryかに関わらず、基本的には、せっかくテーブルを分けているのであれば分けたまま更新する
  • セルにデータを直接書き込む必要がある場合は、特にPower Queryの場合は更新で値が消えてしまうことに注意

おわりに

ということで、XLOOKUPとPower Queryの比較については以上となります。

あまり深く使っているわけではないので見落としている部分も多々あると思うのですが、参考になれば幸いです。

*1:同ファイル内での参照の場合。ファイルをまたぐ場合は異なる可能性あり

*2:Formsテーブルのデータ更新有無にかかわらずリセットされます

投稿者プロフィール
舟越 匠

舟越 匠

人材戦略部に所属。社内向けの技術研修をメインにしつつ、JBS Tech BlogやMS認定資格取得の推進役もやっています。資格としてはAzure Solutions Architect Expertを所持。Power AutomateやLogic Appsで楽をするのが好きです。

執筆記事一覧