Excel約5分で読めます

Excelで同姓同名を別人として残す|名寄せ前に確認する会員IDと照合列

Excelで同姓同名を別人として残すを表したイメージ
記事の内容を表したイメージ(AI生成)

名簿の整理中に「架空 太郎」が2行ある。重複として消してよいか——この迷いを解消するには、氏名ではなくIDを先に確認する必要があります。

この記事では、会員IDと氏名の件数を別々に数える列を作り、削除・統合の判断材料にする手順を説明します。Excel関数の実機検証は別途必要です(後述)。


氏名の一致が「同一人物」を意味しない理由

同じ氏名でもIDが異なれば別人として登録されている可能性があります。逆に、同じIDが2行あればどちらかが誤登録や転記ミスの可能性があります。名寄せの出発点は氏名ではなく、IDの重複・欠番・空欄の確認です。


照合列を作る手順

以下の手順は、架空の練習用CSV(members.csv)S4をもとにした構成です。CSVは値と見出しのみで、C/D列の数式を内蔵していません。実際の名簿に応じて列の配置を読み替えてください。

前提の配置

表は横にスクロールできます

列内容
A会員ID
B氏名
CID件数(作成する)
D同じ氏名の件数(作成する)

A1・B1を見出し行とし、データは2行目から入力します。

手順1:C列にID件数を作る

C1に「ID確認」と入力し、C2に次の式を入力します。

=IF(A2="","ID空欄",COUNTIFS($A$2:$A$6,A2))

C2をC6までコピーします。

  • IDが空欄の行は「ID空欄」と表示されますS3。
  • IDが入力されている場合は、同じIDが何行あるかを数えますS1。
  • 値が1なら該当IDは1行のみ。2以上なら同じIDが複数行存在します。

式の注意点: $A$2:$A$6 の範囲はデータ行数に合わせて変更してください。COUNTIFSは対象範囲の行数を揃える必要がありますS1。

手順2:D列に氏名件数を作る

D1に「同じ氏名の件数」と入力し、D2に次の式を入力します。

=COUNTIFS($B$2:$B$6,B2)

D2をD6までコピーします。値が2以上でも、A列のIDが異なる場合は同一人物と決めず、両方の行を残して確認します。

式の注意点: これらの式は公式の構文を根拠に作成したものです。Excel・Google Sheetsでの実機実行は行っていないため、ご自身の環境で動作を確認してください。


架空例で期待値を確認する

以下は練習用CSV(members.csv)S4の内容と、上記の式を適用したときの期待値です。期待値はローカルでIDと氏名を独立して照合した結果であり、Excelアプリでの実行結果ではありません。

表は横にスクロールできます

A(ID)B(氏名)C(ID件数・期待値)D(氏名件数・期待値)
M01架空 太郎12
M02架空 太郎12
M03見本 三21
M03見本 四21
(空欄)未確認 五ID空欄1

M01・M02の行: 同じ「架空 太郎」という氏名ですが、IDはM01とM02で異なります。C列はどちらも1(IDの重複なし)、D列は2(同じ氏名が2行)。この状態では別人として扱い、どちらも削除しません。

M03の2行: 氏名は異なりますが、IDがM03で重複しています。C列が2になるため、原本でどちらが正しいIDかを確認する必要があります。重複が解消されるまで、どちらかを正解として処理を進めないでください。

空欄IDの行: C列が「ID空欄」と表示されます。IDが付与されていない理由を確認し、必要であればIDを発行してから名寄せに進みます。


確認の優先順位

照合列を作ったら、次の順で確認します。

  1. C列が「ID空欄」の行 — IDがない状態では同一人物の判定ができません。先に処理します。
  2. C列が2以上の行 — 同じIDが複数行あります。原本や別台帳でどちらが正しいか確認します。
  3. D列が2以上でC列が1の行 — 同姓同名の別人である可能性があります。IDと原本を確認し、氏名だけで統合せず両方の行を残します。

XLOOKUPを使う前に確認すること

IDで名簿から情報を引き出す際にXLOOKUPを使う場合があります。XLOOKUPは既定で完全一致検索を行い、最初に見つかった行の値を返しますS2。

上記のM03のようにIDが重複している状態でXLOOKUPを使うと、2行のうち最初の行の情報だけが返され、もう一方は参照されません。重複IDが残っている状態では、XLOOKUPの結果を「その人の確定情報」として扱わないでください。

また、XLOOKUPはExcel 2016・2019では利用できませんS2。お使いのバージョンを事前に確認してください。


行を追加した場合・範囲が変わる場合

データが6行目より下へ増える場合は、式の範囲 $A$2:$A$6・$B$2:$B$6 をデータの末尾行に合わせて変更してください。本例は固定範囲の式なので、追加行が集計範囲に入ったかを確認します。

CSVをExcelで直接開くと、先頭がゼロのIDや日付形式の文字列が自動変換されることがあります。IDの型が変わると一致しなくなるため、インポート時の列の形式を確認してください。


この記事の範囲と次の作業

この記事で説明した範囲は、IDと氏名の件数を数えて確認対象を絞ることです。重複IDの原因調査、旧字体・改姓への対応、他システムとの突合は含みません。

次に行う作業は一つ:練習用CSVS4をA1に貼り付け、C2・D2に上記の式を入力して期待値と一致するか確認することです。実際の名簿に適用する前に、小さなデータで式の動作を確かめてください。

この記事の確認用ファイル

架空データの記入例や、手元で確認するための素材です。

参考資料

公式資料と架空データを使って編集しています。配布CSVは値と見出しのみで、数式・書式・保護設定を含む完成ブックではありません。Excel・Sheetsの実機操作は未検証です。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ