Excelで2つの名簿を比較し、追加・削除・変更された人を見つける

前月と今月の社員名簿、取引先一覧などを見比べて、追加された人・いなくなった人・内容が変わった人を関数で見つける方法です。Excel 2016でも使える式と、Microsoft 365向けの短い式の両方を載せ、照合ミスが起きる原因と対策もまとめます。

目次(8項目)

💡 やりたいこと

前月と今月の社員名簿、先月と今月の取引先一覧――2つの名簿を見比べて、「追加された人」「いなくなった人」「内容が変わった人」を見つけたい。目で見比べると見落としが出ますが、Excelの関数を使えば1行ずつ自動で判定できます。

考え方:両方の名簿に共通する「キー」(社員番号・取引先コードなど、1人に1つだけの番号)を決め、キーで相手の名簿を探します。
・相手にキーがない → 追加(今月にだけいる)/削除(前月にだけいる)
・相手にキーがあるが中身が違う → 変更

準備:2つの名簿を同じブックに並べる

同じブックに「前月」「今月」の2つのシートを作り、同じ列の並びにそろえます。

シート「前月」
A 社員番号B 氏名C 部署D 役職
21001山田 太郎営業部主任
31002佐藤 花子総務部
41003鈴木 一郎営業部
シート「今月」
A 社員番号B 氏名C 部署D 役職
21001山田 太郎営業部係長
31003鈴木 一郎経理部
41004田中 次郎営業部

この例の正解は、追加:1004 田中、削除:1002 佐藤、変更:1001 山田(役職)・1003 鈴木(部署)です。

手順1:追加された人を見つける(今月にだけいる)

シート「今月」のE2に次の式を入れ、下へコピーします。

=IF(COUNTIF(前月!$A:$A,A2)=0,"追加","")

前月のA列に同じ社員番号が1件もなければ「追加」と表示します。

  • COUNTIF(前月!$A:$A,A2):前月の社員番号の列に、A2と同じ番号がいくつあるかを数えます。
  • 0件なら、前月にはいなかった=今月追加された人です。

手順2:いなくなった人を見つける(前月にだけいる)

反対向きに、シート「前月」のE2に次の式を入れて下へコピーします。

=IF(COUNTIF(今月!$A:$A,A2)=0,"削除","")

今月のA列に同じ社員番号がなければ「削除」(退職・取引終了など)と表示します。

追加と削除はそれぞれ逆向きに調べる必要があります。今月側だけを見ていると、いなくなった人には気づけません。

手順3:内容が変わった人を見つける

3-1. 行全体で「どこか変わったか」を判定する

両方のシートのF列に、比較したい列をつないだ「比較用」の列を作ります(前月・今月とも同じ式)。

=B2&"|"&C2&"|"&D2

氏名・部署・役職を「|」でつないだ文字列。どれか1つでも変わると、この文字列も変わります。

次に、シート「今月」のG2に次の式を入れます。

=IF(COUNTIF(前月!$A:$A,A2)=0,"追加",IF(INDEX(前月!$F:$F,MATCH(A2,前月!$A:$A,0))=F2,"変更なし","変更"))

前月にいなければ「追加」、いれば前月の比較用の列と比べて「変更なし」か「変更」を表示します。Excel 2016でも使えます。

Microsoft 365/Excel 2021以降なら、XLOOKUPで短く書けます。

=IF(COUNTIF(前月!$A:$A,A2)=0,"追加",IF(XLOOKUP(A2,前月!$A:$A,前月!$F:$F)=F2,"変更なし","変更"))

INDEX+MATCHの式と同じく、「追加/変更なし/変更」の3つのどれかを返します。

3-2. 変わったセルに色を付ける(条件付き書式)

「変更」と分かった人の、どの列が変わったかを色で見せます。

  1. シート「今月」のB2:D500を選びます。
  2. 「ホーム」→「条件付き書式」→「新しいルール」→「数式を使用して、書式設定するセルを決定」を選びます。
  3. 次の式を入れ、塗りつぶしの色を選んで「OK」を押します。
=IFERROR(B2<>INDEX(前月!B:B,MATCH($A2,前月!$A:$A,0)),FALSE)

同じ社員番号の前月の値と違うセルだけ色が付きます。前月にいない人(追加)はIFERRORで色を付けません。

別のシートを参照する条件付き書式は、Excel 2010以降で使えます。

手順4:結果を一覧にする(Microsoft 365/Excel 2021以降)

FILTER関数を使うと、追加・削除された人を別の場所に一覧で取り出せます。

=FILTER(今月!A2:D500,(今月!A2:A500<>"")*(COUNTIF(前月!A2:A500,今月!A2:A500)=0),"追加なし")

今月にいて前月にいない人の一覧(追加された人)。

=FILTER(前月!A2:D500,(前月!A2:A500<>"")*(COUNTIF(今月!A2:A500,前月!A2:A500)=0),"削除なし")

前月にいて今月にいない人の一覧(いなくなった人)。

Excel 2016/2019では、手順1・2の判定列でオートフィルターをかけて「追加」「削除」だけを表示します。

正しく比べるための注意

よくある失敗と対策
失敗原因対策
同じ内容なのに「変更」になる前後の空白、全角・半角の違い比較用の列を =TRIM(ASC(B2))&"|"&… のように整えてからつなぎます。
結果がおかしい人がいるキーが重複している(同じ社員番号が2行)先に =COUNTIF($A:$A,A2) が2以上の行がないか確認します。
社員番号のような番号がないキーが決められない「氏名+生年月日」のように複数の列をつないでキーを作ります。氏名だけでは同姓同名を区別できません。
先頭の0が消えて一致しないCSVを直接開いたCSVのゼロが消える原因と正しい取り込み方
判定の列が #N/A になる社員番号が、片方は数値でもう片方は文字列。COUNTIFは見つけるのに、MATCHは見つけられないキーの型をそろえます(数字が文字列になったときの直し方)。
16桁以上の番号で、別の番号なのに一致したと判定されるCOUNTIFは数字の文字列を数値として比べるので、16桁目から先が無視される長い番号は =SUMPRODUCT(--(前月!$A$2:$A$500=A2)) で数えます(CSVのゼロが消える原因と正しい取り込み方)。

確認した環境:Microsoft 365 版 Excel(Windows、バージョン 2610)。2026年11月2日に検証用のブックで、社員番号の型が違う行(COUNTIFは1件と数えるが、判定の列は #N/A)、16桁の番号(COUNTIFは別の番号でも1件、SUMPRODUCTは0件)、別のシートを参照する条件付き書式を確認しました。

毎月くり返すならPower Queryも検討

毎月同じ比較をするなら、Power Queryの「マージ」の左反結合で「前月にいない人」「今月にいない人」を取り出す方法もあります。名簿を差し替えて「すべて更新」するだけで結果が変わります。手順は テーブルの結合(マージと追加) を見てください。