Excelで2つの名簿を比較し、追加・削除・変更された人を見つける
前月と今月の社員名簿、取引先一覧などを見比べて、追加された人・いなくなった人・内容が変わった人を関数で見つける方法です。Excel 2016でも使える式と、Microsoft 365向けの短い式の両方を載せ、照合ミスが起きる原因と対策もまとめます。
目次(8項目)
💡 やりたいこと
前月と今月の社員名簿、先月と今月の取引先一覧――2つの名簿を見比べて、「追加された人」「いなくなった人」「内容が変わった人」を見つけたい。目で見比べると見落としが出ますが、Excelの関数を使えば1行ずつ自動で判定できます。
・相手にキーがない → 追加(今月にだけいる)/削除(前月にだけいる)
・相手にキーがあるが中身が違う → 変更
準備:2つの名簿を同じブックに並べる
同じブックに「前月」「今月」の2つのシートを作り、同じ列の並びにそろえます。
| A 社員番号 | B 氏名 | C 部署 | D 役職 | |
|---|---|---|---|---|
| 2 | 1001 | 山田 太郎 | 営業部 | 主任 |
| 3 | 1002 | 佐藤 花子 | 総務部 | |
| 4 | 1003 | 鈴木 一郎 | 営業部 |
| A 社員番号 | B 氏名 | C 部署 | D 役職 | |
|---|---|---|---|---|
| 2 | 1001 | 山田 太郎 | 営業部 | 係長 |
| 3 | 1003 | 鈴木 一郎 | 経理部 | |
| 4 | 1004 | 田中 次郎 | 営業部 |
この例の正解は、追加:1004 田中、削除:1002 佐藤、変更:1001 山田(役職)・1003 鈴木(部署)です。
手順1:追加された人を見つける(今月にだけいる)
シート「今月」のE2に次の式を入れ、下へコピーします。
前月のA列に同じ社員番号が1件もなければ「追加」と表示します。
- COUNTIF(前月!$A:$A,A2):前月の社員番号の列に、A2と同じ番号がいくつあるかを数えます。
- 0件なら、前月にはいなかった=今月追加された人です。
手順2:いなくなった人を見つける(前月にだけいる)
反対向きに、シート「前月」のE2に次の式を入れて下へコピーします。
今月のA列に同じ社員番号がなければ「削除」(退職・取引終了など)と表示します。
追加と削除はそれぞれ逆向きに調べる必要があります。今月側だけを見ていると、いなくなった人には気づけません。
手順3:内容が変わった人を見つける
3-1. 行全体で「どこか変わったか」を判定する
両方のシートのF列に、比較したい列をつないだ「比較用」の列を作ります(前月・今月とも同じ式)。
氏名・部署・役職を「|」でつないだ文字列。どれか1つでも変わると、この文字列も変わります。
次に、シート「今月」のG2に次の式を入れます。
前月にいなければ「追加」、いれば前月の比較用の列と比べて「変更なし」か「変更」を表示します。Excel 2016でも使えます。
Microsoft 365/Excel 2021以降なら、XLOOKUPで短く書けます。
INDEX+MATCHの式と同じく、「追加/変更なし/変更」の3つのどれかを返します。
3-2. 変わったセルに色を付ける(条件付き書式)
「変更」と分かった人の、どの列が変わったかを色で見せます。
- シート「今月」のB2:D500を選びます。
- 「ホーム」→「条件付き書式」→「新しいルール」→「数式を使用して、書式設定するセルを決定」を選びます。
- 次の式を入れ、塗りつぶしの色を選んで「OK」を押します。
同じ社員番号の前月の値と違うセルだけ色が付きます。前月にいない人(追加)はIFERRORで色を付けません。
別のシートを参照する条件付き書式は、Excel 2010以降で使えます。
手順4:結果を一覧にする(Microsoft 365/Excel 2021以降)
FILTER関数を使うと、追加・削除された人を別の場所に一覧で取り出せます。
今月にいて前月にいない人の一覧(追加された人)。
前月にいて今月にいない人の一覧(いなくなった人)。
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の「マージ」の左反結合で「前月にいない人」「今月にいない人」を取り出す方法もあります。名簿を差し替えて「すべて更新」するだけで結果が変わります。手順は テーブルの結合(マージと追加) を見てください。