Excelの突合が合わない。目で見ると5件とも同じなのに、一致は2件でした

請求書と入金の突合をしていて、目では合っているのに一致しない、ということはありませんか。

実際に作ってみました。目で見ると5件とも同じです。Excelが一致と判定したのは2件でした。

見えない差が3つ入っています

左が自社の請求一覧、右が先方の支払明細です。人の目には同じに見えます。中身はこうです。

1行目。 「株式会社ABC」と「株式会社　ABC」。全角の空白が1つ入っています。画面では詰まって見えます。

2行目。 「ABC商事」と「ABC商事 」。末尾に半角の空白があります。これは絶対に見えません。

4行目。 「株式会社デザイン」と「デザイン株式会社」。語順が違います。これは見れば分かりますが、100行あったら見落とします。

5行目。 名前は一致していますが、金額で落ちました。片方の67000が数値、もう片方が文字列です。セルの左上に緑の三角が出ている方が文字列です。

Excelは、表示ではなくデータを見ています

ここが本質です。画面に出ている文字と、セルが持っているデータは別物です。

空白も、全角と半角の違いも、数値と文字列の違いも、表示ではほとんど区別がつきません。ところがExcelの比較はデータそのものを見るので、そこで落ちます。

そして落ち方が最悪です。エラーは出ません。「ちがう」と静かに表示されるだけです。5件のうち3件が落ちていることに、集計まで進んでから気づきます。

先に「件数の突合」を置く

やることは1つです。中身を突き合わせる前に、件数を突き合わせます。

目で見た一致件数 … 5
一致と判定された件数 … 2
差 … 3
この差が0でなければ止める。それだけです。1行目から順に目で追う必要はありません。差の数だけ、見えない汚れが混じっていると分かります。

一致件数を数えるのは1つの数式で足ります。

判定列（この例ではG列）に「OK」がいくつあるかを数えているだけです。突合の表を作ったら、その下にこの1行を置くのを習慣にします。

汚れを落としてから比べる

差が出たら、比較する前に整えます。順番はこうです。

ひとつ目。空白を落とす。 TRIM関数で前後の空白が消えます。前後の全角の空白も消えますが、名前の内側の全角の空白と、改行なしの空白（CHAR(160)）は残るので、先にSUBSTITUTEで半角に置き換えてからTRIMします。

ふたつ目。英数字の全角と半角をそろえる。 ASC関数で全角の英数字が半角になります。「ＡＢＣ」と「ABC」は別物のままだと永遠に一致しません。

みっつ目。数値と文字列をそろえる。 文字列の数字はVALUE関数で数値になります。列を選んで「データ」→「区切り位置」→そのまま完了、でもまとめて直せます。

よっつ目。語順は直せない。 「株式会社デザイン」と「デザイン株式会社」は、関数では一致させられません。ここは目で見て手で直すか、会社名から「株式会社」を除いた部分で突き合わせます。

AIに任せるなら、こう頼む

整える式を自分で組み立てるより、汚れの種類を先に言わせるほうが早いです。

「一致させる数式を書いて」と頼むと、いきなり長い数式が返ってきます。汚れの種類を先に出させると、自分の表に何が混ざっているかが分かり、次から同じ手順で確認できるようになります。

まとめ

目で見て同じでも、Excelは表示ではなくデータを比べる。だから見えない差で落ちる
実際に作ったら、目視5件一致に対して、一致判定は2件だった
混ざっていた差は全角の空白／末尾の半角空白／語順／数値と文字列の4種類
落ち方が最悪で、エラーは出ない。「ちがう」と静かに出るだけ
対策は、中身の前に件数を突き合わせること。目視の件数 − 一致判定の件数 が0でなければ止める
一致件数は COUNTIF の1行で数えられる。ただし、数字と文字列が混ざる列や16桁の番号では、一致していない行まで数える。SUMPRODUCTの結果と並べて確かめる。突合表の下に必ず置く
整える順番は、全角を半角に→空白を落とす→英数字をそろえる→数値と文字列をそろえる
語順だけは関数で直せない。ここは目で見るか、会社名の一部で突き合わせる
頼み方は「一致させる数式」ではなく、汚れの種類を先に列挙させる
突合が合わないとき、たいてい悪いのは目ではありません。比べる前に、揃っていなかっただけです。

同じExcelでも、VLOOKUPが引けないときの5つの原因はこちらにまとめています。

自分の業務のどの工程からAIに渡すか決めたい方は、LINE公式「AIメモ室」で「AI用途の棚卸しシート」をお渡ししています。

突合が合わないのは「揃っていないから」でした。同じ理由で、VLOOKUPは間違った値を静かに返します。
近似一致で20行10,400円ずれた実例と、直す前に作る検算列、そのまま貼れる指示文6本はこちらです。記事で使ったExcelファイルもダウンロードできます。