2つの名簿を列で突き合わせ、片方にしかない行を見つける進め方

キー列の選び方

キー列の良し悪しで、突き合わせの精度がほぼ決まります。

• 会員番号・社員番号などのID、向き不向き：最も向く、判断の目安：両方の表にあり、1人1つで重複しない
• メールアドレス、向き不向き：向く、判断の目安：変更されやすい。大文字小文字は区別されずに数えられる
• 氏名、向き不向き：単独では不向き、判断の目安：同姓同名、スペースの有無、旧字体や異体字で割れる
• 氏名+生年月日、向き不向き：IDがないときの代替、判断の目安：2つを連結して1つのキーにする
• 電話番号、向き不向き：条件付き、判断の目安：ハイフン有無や先頭の0の欠落が起きやすい

選ぶ前に、空白セルがないか、1つの表の中で同じ値が重複していないかを確認します。重複があると「あるかないか」は分かっても、1対1の対応は取れません。

補助列の設計メモ

元の列は直さず、右側に補助列を足します。元データを書き換えないので、後から戻せます。例では、シート名を「A名簿」「B名簿」とし、キーはどちらもA列にあるとします。

• C、見出し：照合キー、役割：空白と全角半角をそろえた文字列、数式：下のコードを参照
• D、見出し：Bでの件数、役割：相手の表に同じキーが何件あるか、数式：=COUNTIF('B名簿'!$C:$C,C2)
• E、見出し：A内の件数、役割：自分の表の中での重複確認、数式：=COUNTIF($C:$C,C2)
• F、見出し：判定、役割：結果を1語にまとめる、数式：=IF(C2="","キー空白",IF(D2=0,"Aのみ",IF(E2>1,"A内で重複",IF(D2>1,"B側で重複","一致"))))

C列(2行目)の数式は次の1本です。引数の "　" は全角スペース、その次の " " は半角スペースで、どちらも空文字に置き換えて消します。

=ASC(CLEAN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,UNICHAR(160),""),"　",""),
" ","")))

※ 紙面の都合で改行していますが、実際は1行で入力してください。

B名簿にも、B名簿自身のA列から同じ照合キー(C列)を作ります。「Bのみ」を探すときは、AとBの役割を入れ替えて同じ列を作ります。

各列の意図は次のとおりです。

• 照合キー: TRIM関数は文字コード32の通常のスペースが対象で、不間断スペース(コード160)は削除されないと公式ヘルプにあります。全角スペースには触れられていないため、この例ではSUBSTITUTEで直接消しています。途中のスペースも全部消すので、「山田 太郎」と「山田太郎」は同じキーになります。同一視してよいかは業務で判断してください。UNICHAR関数はExcel 2016以降が対象です。CLEAN関数は印刷できない文字(コード0〜31)を消しますが、127など一部の文字は残ります。ASC関数は、2バイト文字セット(DBCS)の言語で全角文字を半角にします。全角カタカナも半角になりますが、両方の表に同じ処理をかけるので比較には影響しません。
• 件数: COUNTIF関数は大文字小文字を区別せず、?と*はワイルドカードとして扱われます(文字そのものを探すには~を前に付けます)。255文字を超える文字列の照合には別の書き方が必要と公式ヘルプにあるため、長い文字列をキーにするのは避けます。
• 判定: 絞り込みやすいよう、文言を固定します。

氏名と生年月日で組む場合は、照合キーに&"|"&TEXT(生年月日のセル,"yyyymmdd")を連結します。区切りの|で、別々の値が連結されて偶然同じになるのを避けられます。生年月日は文字ではなく日付として入力されている必要があり、書式コードは言語設定によって異なる場合があります。

実際の進め方

1. 作業用にファイルをコピーする。元の名簿は触らない。
2. 両方の表の行数を、フィルター後ではなく全体で控える。
3. 補助列を両方の表に作り、数式を最終行まで入れる。
4. A名簿のF列(判定)を「Aのみ」で絞り込む。これが「片方にしかない行」の候補になる。
5. 候補を別シートに値で貼り付け、次の章のチェックリストで原因を調べる。
6. 原因を直したら補助列を再計算し、「Aのみ」が減るのを確認する。残ったものが本当の差分。
7. 報告前に行数を検算する。A名簿の行数(見出しを除く)が、「一致」「Aのみ」「A内で重複」「B側で重複」「キー空白」の合計と合えば、数え漏れはありません。合わないときは、数式の入っていない行や範囲指定を疑います。

「Aのみ」が大量に出たときは、人が足りないのではなく、キーの形が割れている可能性が高いです。1件ずつ探す前に、先にチェックリストを使います。

不一致の原因別チェックリスト

上から順に調べると、手間の少ない原因から消せます。

• 1、確認すること：範囲が足りているか、見分け方・直し方：COUNTIFの範囲が最終行まで届いているか。列全体を指定すると漏れにくい
• 2、確認すること：空白セル・見出し行の混入、見分け方・直し方：判定が「キー空白」の行を確認する
• 3、確認すること：前後や途中のスペース、見分け方・直し方：=LEN(A2)と=LEN(C2)を比べ、差があればスペース類などが入っていた(濁点付きカナは半角にすると文字数が増える場合もある)
• 4、確認すること：全角と半角、見分け方・直し方：見た目は同じでも別文字。照合キーで解消されるか確認
• 5、確認すること：数値と文字列、見分け方・直し方：=ISNUMBER(A2)で型を確認する。照合キーは文字列になるが、数値として取り込まれた時点で失われた桁は戻らない
• 6、確認すること：先頭の0の欠落、見分け方・直し方：「00123」が「123」になっていないか。元の列を文字列で取り込み直す。数字だけのIDは、結果を数件目で確かめる
• 7、確認すること：表記ゆれ、見分け方・直し方：ハイフンの種類、「(株)」と「株式会社」、旧字体など。照合キーでは直らない。置換ルールを決めて対応表を作る
• 8、確認すること：見えない文字、見分け方・直し方：改行やタブはCLEANで消える。127など消えない文字が残る場合は、該当セルを個別に調べる
• 9、確認すること：重複、見分け方・直し方：判定が「A内で重複」「B側で重複」の行。どちらを正とするか人が決める
• 10、確認すること：ワイルドカード、見分け方・直し方：キーに* ? ~が含まれていないか

1〜6は機械的な違いで、多くは数式で解消できます。7以降は人の判断が必要な差です。同姓同名の別人か、同一人物の表記違いかは、数式では決められません。

AIに任せること、人が確認すること

AIは、数式の組み立てや、不一致候補の分類案づくりに向いています。同一人物かどうかの最終判断と、元データの修正は人が行います。

渡す情報は、実データではなく構造と少数のダミー例にします。名簿の個人情報をそのまま貼ると、社内ルールや取り扱い方針に反する恐れがあります。実名や連絡先は架空の値に置き換えてください。

依頼文の例です。

Excelで2つの名簿を突き合わせています。次の条件で、補助列の数式を提案してください。

- A名簿: A列=会員番号(文字列)、B列=氏名。行数は約500
- B名簿: A列=会員番号(文字列)、B列=氏名。行数は約480
- 目的: A名簿にあってB名簿にない会員番号を探したい
- 使用環境: Excel(バージョン: ○○、日本語環境)
- 困りごと: 見た目が同じ会員番号なのに一致しない行がある

不一致のダミー例(説明用):
A: "AB-0012" / B: "ＡＢ-0012" / B: "AB-0012 "(末尾にスペース)

お願い:
1. 照合キー列、件数列、判定列の数式
2. このダミー例がどの原因に当たるかの説明
3. 数式で直せない不一致を人が確認するためのチェック項目

回答を受け取ったら、次の点を人が確認します。

• 提案された数式を、元データのコピーで数行試し、結果を目で確かめる。
• 自分のExcelのバージョンで、その関数が使えるか確認する。
• 「Aのみ」の件数が、想定と大きく違わないか見る。
• 表記ゆれの置換ルールは、業務上同じとみなしてよいか自分で決める。