顧客一覧、商品マスタ、請求一覧など、2つのExcel表を突合するときは、いきなりXLOOKUPの式を書くより先に何を共通キーにするかを決めます。
XLOOKUPは便利ですが、キーが曖昧なら正しい式でも正しい結果にはなりません。
この記事では、IDをキーにして「一致」「片側にしかない」「同じIDだが値が違う」を確認します。
1. 共通キーを決める
例として、旧マスタと新マスタがあります。
旧マスタ：
| 顧客ID | 顧客名 | 区分 |
|---|---|---|
| C001 | 田中商店 | A |
| C002 | 佐藤事務所 | B |
| C003 | 鈴木企画 | A |
新マスタ：
| 顧客ID | 顧客名 | 区分 |
|---|---|---|
| C001 | 田中商店 | A |
| C002 | 佐藤事務所 | A |
| C004 | 高橋制作 | B |
ここでは 顧客ID をキーにします。
名前ではなくIDを使うのは、同名や表記ゆれを避けるためです。
2. XLOOKUPで同じIDがあるか確認する
MicrosoftのXLOOKUPは、検索範囲から値を探し、同じ行の戻り範囲から値を返します。
Excelのバージョン注意: XLOOKUPはMicrosoft 365や比較的新しいExcelで利用できます。Excel 2016 / 2019では利用できないため、その場合はINDEX/MATCHやPower Queryなど別手段を使います。
新マスタのA2が顧客IDなら、旧マスタに同じIDがあるかは次のように確認できます。
=XLOOKUP(A2,旧マスタ!$A:$A,旧マスタ!$A:$A,"旧にない")
- C001 → C001
- C002 → C002
- C004 → 旧にない
となります。
C004 は新側にだけあるため、新規追加候補です。
3. 旧側だけにあるIDも確認する
新→旧だけを見ると、新規追加は分かりますが、削除候補は分かりません。
今度は旧マスタ側から新マスタを検索します。
=XLOOKUP(A2,新マスタ!$A:$A,新マスタ!$A:$A,"新にない")
C003 は新マスタにないため、削除・失効・抽出漏れなどの確認対象です。
ここで「削除された」と即断せず、抽出条件や対象期間も確認します。
4. 同じIDの属性差分を確認する
同じIDがあっても、値が変わっていることがあります。
新マスタの区分と、旧マスタの区分を比較します。
=XLOOKUP(A2,旧マスタ!$A:$A,旧マスタ!$C:$C,"旧にない")
旧区分を取り出し、新区分と比較します。
=IF(C2=D2,"一致","差分")
C002は、
旧: B
新: A
なので差分です。
5. 結果を3種類に分ける
突合結果は、少なくとも次の3種類に分けると扱いやすくなります。
- 一致：両方にあり、比較項目も同じ
- 片側のみ：新規/削除/抽出条件違いなどの確認対象
- 属性差：同じキーだが値が違う
1つの「OK/NG」だけにすると、後で何を確認すべきか分かりにくくなります。
6. キーの重複を先に確認する
XLOOKUPは既定では最初に一致した項目を返します。
そのため顧客IDが2行あるようなデータで、どちらも同じキーなら、突合結果が意図と違う可能性があります。
たとえば、
C002,佐藤事務所,A
C002,佐藤事務所,B
のような重複です。
突合前にCOUNTIFなどでキー重複を確認し、1キー1レコードになっているかを見ます。
7. 一致しないときは式よりデータを疑う
見た目が同じでも、
- 前後に空白がある
- 文字列と数値が混在する
- 先頭ゼロが消えている
- 全角/半角が違う
と一致しないことがあります。
この場合、XLOOKUPの式を複雑にする前に、照合用キーを別列で作り、原本を残したまま確認します。
8. 大量・反復処理ならPower Queryも候補
毎月同じ2表を突合するなら、XLOOKUPを毎回コピーするよりPower Queryで処理手順を保存する方法もあります。
ただし、Power Queryを使ってもキー設計の問題は消えません。
最初に「何を同じレコードとみなすか」を決めることが先です。
まとめ
Excelで2つの一覧を突合するときの順番は、
- 共通キーを決める
- 新→旧で存在確認する
- 旧→新でも存在確認する
- 同じキーの属性を比較する
- キー重複を確認する
です。
XLOOKUPは突合の道具であって、突合ルールそのものではありません。キーを先に決めるだけで、式がかなり単純になります。
参考一次資料
- Microsoft Support: XLOOKUP 関数
https://support.microsoft.com/ja-jp/excel/functions/xlookup-function
