XLOOKUPやCOUNTIFの式は合っているのに、同じはずのIDが一致しない。
このとき、関数をさらに複雑にする前にキー列のデータそのものを確認します。
Excelの突合が合わない原因は、式ではなく「見た目は同じでも内部では違う値」にあることが少なくありません。
原因1：前後に空白がある
たとえば画面ではどちらも ABC123 に見えても、片方が次の値なら一致しません。
"ABC123"
"ABC123 "
末尾の空白は目で見落としやすい部分です。
確認用の別列を作り、文字数を比べると見つけやすくなります。
=LEN(A2)
必要ならTRIM等で確認用キーを作ります。ただし、原本をいきなり置換せず、原本列と照合用列を分けるほうが安全です。
原因2：数値と文字列が混ざっている
123 と表示されていても、片方は数値、片方は文字列ということがあります。
とくにCSVから取り込んだ列と、Excelで手入力した列を突合するときに起こりやすい問題です。
ここで「全部数値に変換」と決める前に、そのキーが本当に数値なのか考えます。
顧客コードや商品コードなら、計算する数値ではなく識別子としての文字列かもしれません。
原因3：先頭ゼロが消えている
次は見た目以上に重要です。
00123
123
番号としては別の表現です。
郵便番号、商品コード、社員番号、顧客IDなどでは、先頭ゼロ自体に意味があります。
数値化してからTEXT関数で見た目だけ戻す方法もありますが、元のデータがすでに変わっている場合、正しい桁数を推測できないことがあります。
先頭ゼロを持つ可能性があるキーは、取り込み時点から文字列として扱うほうが安全です。
原因4：キーが重複している
「一致しない」とは逆に、複数件一致しているのに気づかないケースもあります。
C001,田中,A
C001,田中,B
XLOOKUPは既定で最初に見つけた一致を返すため、キー重複があると「一致したように見えるが、どの行と照合したのか分からない」状態になります。
COUNTIFで件数を確認できます。
=COUNTIF($A:$A,A2)
結果が2以上なら、キーが一意ではありません。
原因5：全角・半角、記号、見えない文字
日本語データでは、
- 全角スペース
- 半角スペース
- 全角/半角英数字
- ハイフンの種類
- 改行や制御文字
などが混ざることがあります。
すべてを一括置換してしまうと、意味のある文字まで変える可能性があります。
名寄せの場合は、表示用の原本と照合用の正規化キーを分けます。
原本を上書きしない
突合を合わせるために、原本のIDを直接加工し続けると、何を変えたのか分からなくなります。
おすすめは次の構造です。
| 原本ID | 照合用ID | 突合結果 |
|---|---|---|
| 00123 | 00123 | 一致 |
照合用IDを作る処理も、何を除いたかを記録しておくと後で説明できます。
式を直す前の確認順
突合が合わないときは、次の順に確認すると効率的です。
- 前後空白・文字数
- 数値/文字列
- 先頭ゼロ
- キー重複
- 全角/半角・記号
- 本当に同一対象か
最後の「本当に同一対象か」も重要です。同じ名前だから同じ顧客、同じ商品名だから同じ商品とは限りません。
まとめ
Excelの突合が合わないとき、式を複雑にする前にキーを確認します。
特に、
- 空白
- 型
- 先頭ゼロ
- 重複キー
は見た目では分かりにくい原因です。
原本を残し、照合用キーを別列で作ると、原因を追いやすくなります。
参考一次資料
- Microsoft Support: XLOOKUP 関数
https://support.microsoft.com/ja-jp/excel/functions/xlookup-function
