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

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

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

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

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

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

さらに、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でテーブルを結合している場合は、すぐにデータが反映されます。*1

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

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

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

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

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

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

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

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

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