2つの名簿を突き合わせて、半分も当たらない。原因は表記のゆれで、直し方も知られている。問題は、どの直し方がどれだけ効くかが分からないことだ。
500件で測った。そのままだと40.8%。まず勧められるTRIMを足しても43.6%にしかならない。全部やると100%になる。
以下の計算は、2026年9月21日にMicrosoft 365のExcel（バージョン16）で行った。500件のデータは、現実に起きるゆれを入れて自分で作ったものだ。
入れたゆれの種類
測る前に、どんなゆれを入れたかを書いておく。実際の名簿で見かけるものを混ぜた。
人の名前は、姓と名の間が半角スペース、全角スペース、空白なしの3通り。前に空白が付いているものも入れた。
会社の名前は、株式会社、(株)、㈱の3通り。株式会社のあとに空白が入っているものも入れた。
どちらにも、数字が全角になっているものを3割ほど混ぜた。名簿の番号や支店の番号が全角で入るのは、よくある。
500件のうち半分が人の名前、半分が会社の名前になっている。
TRIMだけでは2.8ポイントしか上がらない
一致の判定はCOUNTIFで数えた。片方の名簿の各行が、もう片方に存在するかを見る。
=SUMPRODUCT(--(COUNTIF($名簿Aの範囲,$名簿Bの範囲)>0))そのまま突き合わせると204件、40.8%だった。
TRIMをかけると218件、43.6%になる。上がったのは14件、2.8ポイントだけだ。
TRIMは前後の空白と、文字の間の連続した空白を1つにまとめる関数だ。前に空白が付いているものには効くが、姓と名の間が全角スペースか半角スペースかという違いには効かない。空白の種類は変えないからだ。
表記ゆれにはTRIM、という覚え方をしていると、ここで止まる。
いちばん効いたのは全角スペースの除去
空白そのものを消すと344件、68.8%まで上がった。25.2ポイントの上昇で、5段階のうちいちばん大きい。
=SUBSTITUTE(SUBSTITUTE(B2," ","")," ","")SUBSTITUTEを2回かける。1つ目が全角スペース、2つ目が半角スペースだ。順番はどちらでもいい。
人の名前を突き合わせるなら、空白は消してしまうのがいちばん確実になる。姓と名の間に空白を入れるかどうかは、入力する人の癖で決まる。統一しようとするより、両方から消すほうが速い。
住所のように空白に意味がある列では、消すと別の問題が起きる。名前の列だけに使う。
ASCで半角に揃えると75.6%
全角の数字を半角に直すと378件、75.6%になった。6.8ポイントの上昇だ。
=ASC(SUBSTITUTE(SUBSTITUTE(B2," ","")," ",""))ASCは全角の英数字とカタカナを半角にする。逆のJISもあるが、どちらに揃えるかを決めて片方だけ使う。混ぜると効かない。
注意するのは、ASCが全角カタカナも半角カタカナに変えてしまうことだ。カタカナの名前を含む名簿では、半角カタカナに揃うことになる。見た目が変わるので、元の列は残しておく。
数字だけを半角にしたいなら、ASCではなく数字1文字ずつSUBSTITUTEする方法もある。10回書くことになるが、カタカナは動かない。
会社の種別を取ると100%になった
最後に株式会社、(株)、㈱を消すと500件すべてが一致した。24.4ポイントの上昇だ。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ASC(SUBSTITUTE(SUBSTITUTE(B2," ",""),
" ","")),"株式会社",""),"(株)",""),"㈱","")入れ子が深くなるので、作業列を段階的に作って最後に1本にまとめるほうが作りやすい。
有限会社、合同会社、一般社団法人も同じように足す。(有)や㈲も忘れずに入れる。前株と後株の両方があるので、位置を指定せずに消す。
取りすぎると、別の法人がくっつく
ここで止まらずに、最後に1つ確かめる。
「株式会社みどり」と「みどり」を並べて、上の式にかけた。どちらも「みどり」になり、同じものとして扱われる。
別の法人として両方が取引先にいる場合、突き合わせの時点で1つにまとまる。金額の集計なら合算されて、どちらの数字か分からなくなる。
上の500件では、正規化のあとに重複が増えなかった。元から重複していた290件がそのまま290件で、ユニークな名前の数も335件のまま変わっていない。ただ、これはたまたま会社名に番号を付けていたからだ。
確かめる式はこうなる。
=SUMPRODUCT(--(COUNTIF($正規化後,$正規化後)>1))正規化の前と後で、この数字を比べる。増えていたら、どの行が増えたかを見る。増えていなければ、そのまま使ってよい。
表記のぶれで集計が合わなくなる話は在庫のSUMIFSの記事にも書いた。あちらは1つの表の中の話で、こちらは2つの表をまたぐ話になる。
重複を消すときは、消す行に印を付ける
突き合わせが終わったら、同じ名簿の中の重複を片付ける。行を消す前に、どれを消すかを列に出す。
上から見て初めて出てきた行を残し、2回目以降を消す形にする。
=IF(COUNTIF($正規化後の列の先頭:$その行,$その行)=1,"残す","消す")範囲の始まりを絶対参照、終わりを相対参照にするのが要点だ。下にコピーすると範囲が伸びていくので、その行より上に同じものがあるかを見ていることになる。
500件にかけたら、残すが335行、消すが165行と出た。ユニークな名前の数をSUMPRODUCTで数えた値と一致する。
=SUMPRODUCT(1/COUNTIF($範囲,$範囲))この2つの数が合わないときは、正規化の列に空白セルが混ざっている。空白があるとCOUNTIFが0を返し、1で割る計算がエラーになる。
どれを残すかを更新日で決めたいなら、MAXIFSで各名前の最新の日付を出してから、その日付と一致する行だけを残す。上から順ではなく、内容で選ぶ形になる。
消す前に、消すと出た行だけを別のシートに写しておく。あとで「この取引先の情報が消えた」と言われたときに戻せる。並べ替えてから行を削除すると、写しを取る前に消えてしまう。
元の列は壊さない
正規化は作業列でやる。元の名前の列を書き換えない。
理由は2つある。突き合わせが終わったあと、人に見せるのは元の名前だからだ。半角カタカナに変わった名前を請求書に印刷することになる。
もう1つは、直し方を変えたくなるからだ。会社の種別まで取るか取らないかは、やってみて決める。元の列が残っていれば何度でも試せる。
突き合わせの結果を返す式はXLOOKUPかVLOOKUPになる。検索する側も検索される側も、正規化した列を指定する。
=XLOOKUP(正規化したB列のセル,正規化したA列の範囲,返したい列,"見つからない")VLOOKUPを使うなら、正規化した列を表のいちばん左に置く。作業列を右端に足す癖があると、この並びで詰まる。
踏んだ失敗
TRIMをかけて一致率が上がらず、データがおかしいと思い込んだことがある。空白の種類が混ざっているだけだった。上の測定でいえば、43.6%で止まっていた状態だ。
ASCとJISを両方かけたこともある。片方が半角にして、もう片方が全角に戻すので、結果として何も変わらない。片方だけにする。
元の列を上書きして正規化したこともある。カタカナの取引先名が半角になり、そのまま送付状に印刷した。戻せなかったので、元のファイルから取り直した。
会社の種別を取ったまま集計して、別の法人の金額を合算したこともある。金額が合わないことで気づいた。種別を取るのは突き合わせのときだけにして、集計は元の名前でやる。記録の残し方そのものは電話とメールの記録の記事に書いた。入力規則のリストから選ばせれば、そもそもぶれない。
確認していないこと
旧字と新字の違いは扱っていない。髙と高、﨑と崎のような文字は、上の式では別のものとして残る。置換の表を別に持つ方法があるが、試していない。
ひらがなとカタカナの違い、送り仮名の違いも扱っていない。
上の40.8%という数字は、私が入れたゆれの割合で決まる。自分の名簿でどれだけ当たるかは、実際に測るしかない。段階ごとにCOUNTIFを並べれば、同じ形で測れる。
正規化しても当たらない行をどうするかも書いていない。名前が本当に違うのか、ゆれが残っているのかは、目で見るしかない。突き合わせで残った行を人が見る形にする考え方は経費精算のチェックの記事と同じだ。
