順番が違う2つのExcelファイルから変更・追加・削除を見つけるには？
顧客IDで同じ対象を対応付けてから変更された値を比較します。並び順に左右されず、ランク変更と追加・削除された顧客を確認する例です。
同じセル位置ではなく顧客IDで対応するデータをつなぎ、その後にランクや金額を比較します。JOIN SHEETではIDを結合キー、変更を調べる列を「値を比較」に設定できます。両側の全行を保持すれば、値の変更だけでなく追加・削除された項目も確認できます。
並べ替えと値の変更を区別する
以前の顧客一覧はC10・C20・C30、現在はC20・C10・C40の順です。C10のランクはSilverからGoldへ変更され、C20はGoldのままです。C30は現在の一覧からなくなり、C40が追加されています。
これは説明用の架空例です。図も実際の画面ではなく入力・設定・結果を示す説明図です。
IDを接続しランクを比較する
- 以前のファイルを1つ目、現在のファイルを2つ目のシートとして読み込みます。
- 両側の「顧客ID」をつなぎ「両方のシートの全行を保持」を選びます。
- 両側の「ランク」も接続します。接続線の結合キーのメニューを開き「ランク ↔ ランク」の横の「VS」を押して値の比較へ切り替えます。
- 「結果のプレビュー」で「比較結果」「変更された列」「変更内容」を確認します。
- 同名列をまとめて比べる場合は接続線の「自動比較」を使えます。顧客IDなどの結合キーは対象外です。名前が異なる列は個別に比較用の線でつなぎます。
変更1件・同じ1件・片方のみ各1件
| 顧客ID | 比較結果 | 読み取れること |
|---|---|---|
| C10 | 変更あり | ランクがSilver → Gold |
| C20 | 同じ | 比較したランクはGoldのまま |
| C30 | 1つ目のみ | 現在の一覧にない |
| C40 | 2つ目のみ | 以前の一覧にない |
結果は4行です。C20は場所が変わってもランクは変わっていないため「同じ」です。ただし「同じ」は選んだ比較対象の値についての判断であり、選ばなかったすべての列まで同じだと保証するものではありません。
同じIDが複数行ある場合は？
変更比較では、比較対象の行を結合キーで一意に特定できる必要があります。顧客ごとの支店別データなら、顧客IDと支店を組み合わせてつなぎます。区別できない行が残る場合にJOIN SHEETが任意の1行を選んで比較することはありません。
「結果を整理」の「絞り込み」で変更された項目に絞れます。全結果を保存する前に、代表的なIDと変更前後の値を元データと照合してください。IDがない場合は複数列の値の組み合わせを比較する方法を使いますが、同じ対象の更新かどうかまでは特定できません。
繰り返しの作業を減らす
シートを接続・比較して、必要な結果を確認できます。
この記事はProプランの利用環境を前提としています。
JOIN SHEETを使ってみる ↗Excel・CSVの作業はPCでご利用ください。新しいタブで開くため、作業中のデータは置き換わりません。
サンプルの検証日:
出典
How to compare two lists: missing values and different values — Reddit r/excel
公開質問の欠落と変更の問題を説明するため、顧客ランクの架空例を作成しました。質問者の実データやJOIN SHEETの利用事例ではありません。
