先月の名簿と今月の名簿を見比べて、「増えた行」と「消えた行」を探したいときがあります。目視で照合すると見落としが起きやすく、行数が多くなるほど時間もかかります。Excelには差分を自動で判定する専用の機能はありませんが、COUNTIF関数と条件付き書式を組み合わせると、追加・削除をセルの色や記号で分けて表示できます。
この記事では「旧表」と「新表」の2シートを比べて、新表に追加された行と旧表から削除された行をそれぞれ印で区別する手順を説明します。キーになる列(IDや氏名など重複しない値が入る列)が1列あれば対応できます。数式の入力前に必ずファイルをコピーして元データを残してください。
どの方法を選ぶか:印をつける3つのアプローチ
差分の出し方は目的によって変わります。まず自分の状況に合う方法を選んでください。
表は横にスクロールできます
| 方法 | 向いている場面 | 作業の大まかな流れ |
|---|---|---|
| COUNTIF+判定列 | 印を列に文字で残したい/後で集計したい | 別列に数式を入れて「追加」「削除」と表示 |
| 条件付き書式 | 色で一目で把握したい | COUNTIF式を条件付き書式のルールに入れる |
| 差分専用シートに抽出 | 一覧として出力・印刷したい | フィルターやIF式で別シートに行を集める |
この記事では「判定列に文字で表示」を中心に説明し、色付けへの応用も後半で示します。最も汎用的で、後から確認・修正しやすいためです。
準備:ファイルとシートを整える
作業を始める前に、次の順で準備します。
- ファイルを複製する 元のファイルをコピーし、別名(例:
名簿_差分確認_20261021.xlsx)で保存します。作業中に数式を誤って入れても、元ファイルに戻せるようにします。 - 2つの表をそれぞれ別シートに置く シート名を「旧表」「新表」とします。すでに別シートにある場合はそのまま使えます。
- キー列を確認する IDや社員番号など、行を一意に識別できる列を決めます。氏名を使う場合は同姓同名があると誤判定の原因になるため、IDとの組み合わせを検討してください。
- 表に見出し行があることを確認する 数式はデータ行(2行目以降)に入れます。
手順:新表に「追加か既存か」を判定する列を作る
ここでは次の架空の例を使います(架空の例です)。
旧表(シート名:旧表)
表は横にスクロールできます
| A列:ID | B列:氏名 |
|---|---|
| 1001 | 山田 花子 |
| 1002 | 鈴木 一郎 |
| 1003 | 田中 美咲 |
新表(シート名:新表)
表は横にスクロールできます
| A列:ID | B列:氏名 | C列:判定(入力予定) |
|---|---|---|
| 1001 | 山田 花子 | |
| 1003 | 田中 美咲 | |
| 1004 | 佐藤 健太 |
旧表にあった1002(鈴木 一郎)が消え、1004(佐藤 健太)が追加されています。
新表のC列に判定式を入れる
新表のC2に次の数式を入力します。
=IF(COUNTIF(旧表!$A:$A,A2)=0,"追加","既存")
COUNTIF(旧表!$A:$A,A2)は、旧表のA列全体を対象に、新表のA2(1001)が何件あるかを数えます。- 0件なら「追加」、1件以上なら「既存」と表示します。
- C2の数式を下のC3、C4にコピーします。
結果(架空の例です):
表は横にスクロールできます
| A列:ID | B列:氏名 | C列:判定 |
|---|---|---|
| 1001 | 山田 花子 | 既存 |
| 1003 | 田中 美咲 | 既存 |
| 1004 | 佐藤 健太 | 追加 |
旧表に「削除されたか」を判定する列を追加する
同じ発想で、旧表のC列に削除判定を入れます。旧表のC2に次の数式を入れ、下にコピーします。
=IF(COUNTIF(新表!$A:$A,A2)=0,"削除","継続")
旧表の結果(架空の例です):
表は横にスクロールできます
| A列:ID | B列:氏名 | C列:判定 |
|---|---|---|
| 1001 | 山田 花子 | 継続 |
| 1002 | 鈴木 一郎 | 削除 |
| 1003 | 田中 美咲 | 継続 |
これで「追加」「削除」「既存」「継続」の4種の印が揃います。
色付けに切り替える:条件付き書式を使う場合
判定列の文字ではなく、行全体を色で分けたい場合は条件付き書式を使います。画面の表示は版や設定により異なりますので、細かいボタン位置は公式のヘルプで確認してください。
新表で「追加」行を赤く塗る例を示します。
- 新表のA2からデータ末尾まで選択します(例:A2:C4)。
- 「条件付き書式」→「新しいルール」を開きます。
- 「数式を使用して、書式設定するセルを決定する」を選びます。
- 数式欄に次を入力します。
=COUNTIF(旧表!$A:$A,$A2)=0
- 書式で塗りつぶし色を設定し、OKで確定します。
数式の$A2は列を固定して行は動くよう書いています。選択範囲の左端の列番号に合わせて変更してください。
旧表の「削除」行に色をつけるときも同じ手順で、数式を旧表のシートに対して設定します。
迷いやすい例外ケース
キー列の値に表記ゆれがある場合 全角・半角の混在や末尾のスペースが原因で、同じIDが「別の値」として扱われることがあります。判定が意図通りでない行が出たら、キー列をまずTRIM関数でスペースを除去した上で確認してください。
キー列が2列以上になる場合 「支店コード+社員番号」のように2列を組み合わせてIDにしたい場合、COUNTIFSで複数条件を指定するか、キー列を1列に結合(=A2&"-"&B2)してから判定に使う方法があります。結合列を作った場合も元の列は残しておいてください。
同じIDが旧表・新表の両方に重複して存在する場合 COUNTIFは件数を数えるだけなので、重複の検出とは目的が異なります。重複の扱いは先に整理してから差分を取ってください。
個人情報を含む名簿を扱う場合 外部サービスや生成AIにデータを貼り付けて差分を確認しようとすることがあります。個人情報の取り扱いに関するルールを確認してから作業してください。
差分を一覧に抽出する:別シートにまとめる
「追加」「削除」の行だけを別シートに出力したい場合は、フィルター機能を使う方法が手軽です。判定列に「追加」と入力された行だけを表示してから、別シートに貼り付けます。
ExcelのバージョンによってはFILTER関数が使えます。新表の判定列がC列で、A:C列のデータを追加行だけ抽出するなら次のように書けます(FILTER関数が使えるバージョン限定です)。
=FILTER(新表!A:C, 新表!C:C="追加")
この関数を差分シートの任意のセルに入力すると、追加行だけが自動で表示されます。COUNTIFの判定式と組み合わせて使えます。
次の行動
まずファイルをコピーして、片方に「判定」列を追加するところから始めてください。COUNTIFの式をC2に入れて、出てきた結果が想定と合っているか2〜3行で確認してから下にコピーすると、誤入力に早く気づけます。色付けや抽出は判定列の内容が正しいと確認できてから進めると安全です。
参考資料
- IF 関数 | Microsoft Support · support.microsoft.com 参照日 2026-10-23
- Microsoft Excel で COUNTIF 関数を使用する | Microsoft Support · support.microsoft.com 参照日 2026-10-23
- FILTER 関数 | Microsoft Support · support.microsoft.com 参照日 2026-10-23
この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。
