Excel約6分で読めます

Excelで新旧名簿の追加・削除・変更を比べる|手直しした内容を次の更新で消さない

Excelで新旧名簿の追加・削除・変更を比べるを表したイメージ
記事の内容を表したイメージ(AI生成)

毎月新しい名簿が届いて旧ファイルを上書きしたとき、前回入れた担当メモや確認マークが消えてしまう。そういう状況向けに、IDをキーにして旧版・新版・メモを照合し、変更なし・変更・追加・旧版のみ(削除候補)を列に出す手順を説明します。

対象とする範囲をはじめに示しておきます。

  • 対象バージョン:Microsoft 365 / Excel 2021 / Excel 2024(XLOOKUPが使える環境)S1
  • Excel 2016 / 2019 では XLOOKUP を利用できませんS1
  • 以下の数式は公式構文をもとに作成し、架空例の差分と期待値をローカル計算で確認しています。Excel / Sheetsで式を実行した検証は行っていません
  • 名簿にIDとして使える一意の列(社員番号、会員番号など)があることが前提です

考え方:行番号ではなくIDで紐づける

名簿を更新すると行の順番が変わることがあります。行番号を頼りにメモを貼り付けると、別のIDにメモが付いてしまう可能性があります。IDで照合することで、行が増減しても正しい人にメモを対応させられます。


架空例の構成

説明には編集部が作成した架空データを使いますS4。実在の顧客情報や業務実績ではありません。

旧版(old.csv → 「旧」シート)

表は横にスクロールできます

ID住所(架空)
M01架空町1
M02架空町2
M03架空町3

新版(new.csv → 「新」シート)

表は横にスクロールできます

ID住所(架空)
M01架空町1
M02見本町2
M04見本町4

担当メモ(staff-memo.csv → 「メモ」シート)

表は横にスクロールできます

ID担当メモ(架空)
M01午前連絡
M02書面希望
M03確認待ち

この架空例での差分は次のとおりです。

  • M01:変更なし
  • M02:住所が「架空町2」→「見本町2」に変更
  • M03:旧版のみ(新版に存在しない。退会確定ではなく、理由の確認が必要)
  • M04:新版のみ(追加)

手順

1. 3つのCSVをシートへ配置する

old.csv を「旧」シートのA1、new.csv を「新」シートのA1、staff-memo.csv を「メモ」シートのA1へそれぞれ配置します。いずれも A列 = ID、B列 = 住所またはメモの構成にします。

原本ファイルは別フォルダに保存しておくことを勧めます。

2. 各シートでID重複を確認する(COUNTIFS)S2

照合が正しく機能するには、各シートのIDが重複していないことを先に確認します。

「新」シート C1 にラベル(例:新ID件数)、C2 に次の式を入れ、C4 までコピーします。

=COUNTIFS($A$2:$A$4,A2)

同様に、「旧」シートと「メモ」シートの C2(C4 まで)にも同じ式を入れます。

C列がすべて 1 であれば重複なしと判断できます。2 以上の値があれば、その IDが重複しているため、ここで作業を止めて原本を確認してください。IDが空欄の行がある場合も先に整理します。

架空例での期待値:旧・新・メモのいずれも C列は [1, 1, 1]。

3. 新版シートに照合列を追加する

COUNTIFS でのID重複確認が終わったら、「新」シートに以下の 4 列を追加します。

D列:旧版にある件数S2

D1 にラベル(例:旧版にある件数)、D2 に次の式を入れ D4 までコピーします。

=COUNTIFS(旧!$A$2:$A$4,A2)

架空例での期待値:[1, 1, 0](M04 は旧版にないため 0)

E列:旧住所(XLOOKUP)S1

E1 にラベル(例:旧住所)、E2 に次の式を入れ E4 までコピーします。

=XLOOKUP(A2,旧!$A$2:$A$4,旧!$B$2:$B$4,"該当なし",0)

この教材の架空例では住所が空欄の行はありません。実データに住所の空欄が含まれる場合は別途確認が必要です。

架空例での期待値:["架空町1", "架空町2", "該当なし"]

F列:判定(IF)S3

F1 にラベル(例:判定)、F2 に次の式を入れ F4 までコピーします。

=IF(D2=0,"追加",IF(B2=E2,"変更なし","変更"))

D 列が 0 か 1 のみであることを確認してから使います。

架空例での期待値:["変更なし", "変更", "追加"]

G列:担当メモ(XLOOKUP)S1

G1 にラベル(例:担当メモ)、G2 に次の式を入れ G4 までコピーします。

=XLOOKUP(A2,メモ!$A$2:$A$4,メモ!$B$2:$B$4,"",0)

対応するIDのメモが見つからない場合は空文字を返します。

架空例での期待値:["午前連絡", "書面希望", ""](M04 はメモなしのため空欄)

4. 旧版のみの候補を確認する

旧版シートにも、新版に存在するかを確認する列を追加します。

「旧」シートの D1 にラベル(例:新版にある件数)、D2 に次の式を入れ D4 までコピーします。

=COUNTIFS(新!$A$2:$A$4,A2)

架空例での期待値:[1, 1, 0](M03 は新版にないため 0)

D 列が 0 の行は新版に存在しないことを示します。ただし退会・削除が確定したわけではありません。転送、誤記、更新前の状態など、理由を原本で確認してから判断します。


まとめ:照合の流れ

旧版シート  ──┐
新版シート  ──┼──→ ID重複確認(C列 COUNTIFS)
メモシート  ──┘
                     ↓ 全列が 1 であれば続行
              新版D列(旧版にある件数 COUNTIFS)
              新版E列(旧住所 XLOOKUP)
              新版F列(判定 IF)
              新版G列(担当メモ XLOOKUP)
              旧版D列(新版にある件数 COUNTIFS)→ 0 は旧版のみ候補

追加行が増えたときの対応

名簿の行数が増えたときは、全ての式の参照範囲の末尾を見直します。例えばデータが 10 行になった場合、$A$2:$A$4 を $A$2:$A$11 に変更します。参照範囲の一部だけ更新を忘れると照合が合わなくなるため、一括して確認します。


空欄IDと重複IDへの対応

空欄IDは目視で、重複IDはCOUNTIFSの件数で確認します。空欄が1行だけなら件数が1になる場合もあるため、件数だけで空欄なしとは判断せず、原本を確認します。

この教材の架空例は非空欄・一意の ID を前提にしています。先頭ゼロを含む社員番号や日時形式の列を CSV で開くと、Excel が型変換する場合があります。実データを扱う際は列の型に注意してください。


配布している CSV ファイル(old.csv / new.csv / staff-memo.csv)は値と見出しだけの練習用データです。書式・入力規則・セル保護・数式を内蔵した完成ブックとは異なります。上の手順を参考に自分の名簿へ置き換えて使う素材として扱ってください。

この記事の確認用ファイル

架空データの記入例や、手元で確認するための素材です。

参考資料

公式資料と架空データを使って編集しています。配布CSVは値と見出しのみで、数式・書式・保護設定を含む完成ブックではありません。Excel・Sheetsの実機操作は未検証です。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ