Excelで2つの名簿を比較する方法｜片方にしかないデータをCOUNTIFで抽出

2つの名簿の差分を調べるには、COUNTIFで「相手の一覧に同じIDが何件あるか」を数え、0件なら片方だけにあるデータと判定します。同じ行どうしを比較しないため、並び順が異なる名簿にも使えます。

無料Excelサンプルで、まず試してみる

名簿比較サンプルをダウンロード（無料・Excel）

.xlsx／登録不要／数式・練習データ入り
対応：Microsoft 365・Excel 2024・2021・2019・2016

開くシートは「まず試す」。最初はこの3ステップです。

1. 「まず試す」シートで、左と右の名簿を見比べます。
2. 黄色いD11のIDを「A006」から「A004」に変更します。
3. 左側のC12が「左だけ」から「両方」に変われば成功です。今回はIDの存在を比較し、氏名は判定に使いません。

保護ビューで入力できない場合は、入手元を確認して「編集を有効にする」を選びます。別の練習を始める前に、ファイルのコピーを保存しておくと初期状態に戻せます。

比較の前に「何が同じなら同一人物か」を決める

氏名だけでは同姓同名や表記揺れを区別できません。社員番号・会員番号など、一意に決まるIDがある場合はIDを使います。今回のサンプルは英字Aと数字で構成するIDを比較し、氏名は確認用に表示しています。

比較したいこと	今回の方法で分かるか
相手の名簿にIDが存在するか	分かる
行の順番が違っても同じIDがあるか	分かる
同じIDの氏名・住所が変更されたか	別の項目比較が必要
同じIDが何件ずつ重複しているか	存在判定だけでは不十分。件数比較が必要

手順1：左の名簿にしかないIDを判定する

左のIDはA9:A13、右のIDはD9:D13です。C9に次の式を入れ、C13までコピーします。

=IF(LEN(A9)=0,"",IF(COUNTIF($D$9:$D$13,A9)=0,"左だけ","両方"))

COUNTIFで、右の名簿の中にA9のIDが何件あるか数えます。0件なら「左だけ」、1件以上なら「両方」です。外側のIFは空のIDに判定を出さないためのものです。

比較先の$D$9:$D$13には$を付けて固定します。比較するA9は固定しないので、下へコピーするとA10、A11と変わります。サンプルの初期状態ではA003とA004が「左だけ」です。

手順2：右の名簿にしかないIDも調べる

片方向の比較だけでは、右に新しく追加された人を見落とします。F9には、比較方向を逆にした式を入れます。

=IF(LEN(D9)=0,"",IF(COUNTIF($A$9:$A$13,D9)=0,"右だけ","両方"))

F13までコピーすると、初期状態ではA006とA007が「右だけ」になります。左を旧名簿、右を新名簿とするなら、左だけは削除候補、右だけは追加候補として確認できます。候補の理由を確認してから実際の名簿を更新してください。

手順3：「左だけ」「右だけ」の行を取り出す

1. 左の表A8:C13を見出しごと選び、「データ」→「フィルター」を設定します。
2. C列のフィルターで「左だけ」に絞ります。
3. 表示されたIDと氏名を確認します。コピーする場合は必要に応じて「可視セル」を選びます。
4. 右側も調べる場合は、左のフィルターを解除してからD8:F13に設定し、F列を「右だけ」に絞ります。

Microsoft 365・Excel 2021・2024なら、別の空き領域に次の式を入れて一覧を作れます。下の式はA列～C列の判定結果を使うので、COUNTIFの式を先に用意します。

=FILTER(A9:B13,C9:C13="左だけ","該当なし")

FILTERを使わない手順はExcel 2019・2016でも利用できます。2つの名簿が別シートにある場合は、比較先を'新名簿'!$A$2:$A$100のようにシート名付きで指定します。

一致するはずなのに「片方だけ」になるとき

原因	確認方法
前後のスペースや改行	見た目だけで判断せず、LENで長さを確認。元データの正規化を検討。
IDの桁や先頭の0が違う	001と1を同じIDにするか、業務上の定義を確認。
範囲が足りない	名簿の最終行まで比較先を広げる。
固定参照がずれた	比較先の範囲に$を付けているか確認。

COUNTIFは英字の大文字・小文字を区別せず、*と?はワイルドカードとして扱います。これらの記号を含むIDや、大文字小文字を区別する管理番号は、この基本式の対象外として比較方法を見直してください。

同じ行どうしの比較との使い分け

=A9=D9は同じ行の値が等しいかを確認する式です。並べ替えや行の追加で順番がずれる名簿の差分には、そのまま使えません。行をそろえて項目単位の変更を確認する場合は文字列の比較方法を参照してください。

サンプルで結果を確かめる

本文のセル位置はダウンロードファイルにそろえています。まず黄色いセルを変更して動きを確認し、次に自分の表に合う範囲へ置き換えてください。

名簿比較サンプルをダウンロード（無料・Excel）

最初に試す操作へ戻る

関連する解説・参考資料

• COUNTIF関数の基本
• 文字列の余分なスペースを削除する
• Microsoft：COUNTIFの使い方