2つの名簿の差分を調べるには、COUNTIFで「相手の一覧に同じIDが何件あるか」を数え、0件なら片方だけにあるデータと判定します。同じ行どうしを比較しないため、並び順が異なる名簿にも使えます。
画像をタップすると拡大
比較の前に「何が同じなら同一人物か」を決める
氏名だけでは同姓同名や表記揺れを区別できません。社員番号・会員番号など、一意に決まるIDがある場合はIDを使います。今回のサンプルは英字Aと数字で構成するIDを比較し、氏名は確認用に表示しています。
| 比較したいこと | 今回の方法で分かるか |
|---|---|
| 相手の名簿にIDが存在するか | 分かる |
| 行の順番が違っても同じIDがあるか | 分かる |
| 同じIDの氏名・住所が変更されたか | 別の項目比較が必要 |
| 同じIDが何件ずつ重複しているか | 存在判定だけでは不十分。件数比較が必要 |
まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
手順1：左の名簿にしかないIDを判定する
左のIDはA9:A13、右のIDはD9:D13です。C9に次の式を入れ、C13までコピーします。
COUNTIFで、右の名簿の中にA9のIDが何件あるか数えます。0件なら「左だけ」、1件以上なら「両方」です。外側のIFは空のIDに判定を出さないためのものです。
比較先の$D$9:$D$13には$を付けて固定します。比較するA9は固定しないので、下へコピーするとA10、A11と変わります。サンプルの初期状態ではA003とA004が「左だけ」です。
画像をタップすると拡大
手順2：右の名簿にしかないIDも調べる
片方向の比較だけでは、右に新しく追加された人を見落とします。F9には、比較方向を逆にした式を入れます。
F13までコピーすると、初期状態ではA006とA007が「右だけ」になります。左を旧名簿、右を新名簿とするなら、左だけは削除候補、右だけは追加候補として確認できます。候補の理由を確認してから実際の名簿を更新してください。
画像をタップすると拡大
手順3：「左だけ」「右だけ」の行を取り出す
Microsoft 365・Excel 2021・2024なら、別の空き領域に次の式を入れて一覧を作れます。下の式はA列～C列の判定結果を使うので、COUNTIFの式を先に用意します。
FILTERを使わない手順はExcel 2019・2016でも利用できます。2つの名簿が別シートにある場合は、比較先を'新名簿'!$A$2:$A$100のようにシート名付きで指定します。
一致するはずなのに「片方だけ」になるとき
| 原因 | 確認方法 |
|---|---|
| 前後のスペースや改行 | 見た目だけで判断せず、LENで長さを確認。元データの正規化を検討。 |
| IDの桁や先頭の0が違う | 001と1を同じIDにするか、業務上の定義を確認。 |
| 範囲が足りない | 名簿の最終行まで比較先を広げる。 |
| 固定参照がずれた | 比較先の範囲に$を付けているか確認。 |
COUNTIFは英字の大文字・小文字を区別せず、*と?はワイルドカードとして扱います。これらの記号を含むIDや、大文字小文字を区別する管理番号は、この基本式の対象外として比較方法を見直してください。
同じ行どうしの比較との使い分け
=A9=D9は同じ行の値が等しいかを確認する式です。並べ替えや行の追加で順番がずれる名簿の差分には、そのまま使えません。行をそろえて項目単位の変更を確認する場合は文字列の比較方法を参照してください。
サンプルで結果を確かめる
本文のセル位置はダウンロードファイルにそろえています。まず黄色いセルを変更して動きを確認し、次に自分の表に合う範囲へ置き換えてください。
