Excel の2つのシートを比較して差分を見つける方法
一意の ID で Excel シートを比較し、値の変更や追加と欠落を確認する方法を解説します。具体例で重複 ID と双方向の照合を学び、AI への依頼範囲も明確にします。
2つの注文データは、行数が同じでも別の注文を含んでいることがあります。片方を並べ替えたり、レコードを挿入したりしていれば、A2 同士を比べるだけでは不十分です。
業務レコードは、まず変わらない識別子で対応付け、その識別子に属する項目を比較します。結果は変更されたレコード、旧シートだけにあるレコード、新シートだけにあるレコードに分けましょう。両方の元データを残し、比較レポートは別のシートに作成します。
比較したい内容を決める
| 確認したいこと | 適した方法 |
|---|---|
| 注文の金額が変わったか | 注文 ID を照合してから金額を比較する |
| どの注文が追加または削除されたか | ID の有無を双方向に確認する |
| 数式やセルの書式が変わったか | 戻り値だけでなくブックのバージョンを比較する |
| 小さな2つのシートに見た目の違いがあるか | 並べて表示し、違いを調べる |
Microsoft のワークシートを並べて表示する機能は目視確認に役立ちます。ただし、業務上の識別子でレコードを揃える機能ではありません。以下の例は値の比較であり、書式や数式の文字列は比較しません。
一意のキーを用意する
注文 ID、社員 ID など、各データ内で1回だけ現れるはずの識別子を使います。名前だけでは適さない場合が多くあります。1件の注文に複数の明細行があるなら、注文 ID だけでは一意になりません。注文 ID と明細番号など、定義の明確な組み合わせを使ってください。
照合前に、空の ID、重複、先頭のゼロ、数値と文字列の型の違いを確認します。ID を整える場合も元の ID を併記し、無理に一致させるために意味のある空白や先頭のゼロを削除しないでください。別の表から項目を取り込む作業は、列の照合ガイドで紹介しています。
以下の数式は英語の関数名とカンマを使用しています。Excel の言語や地域設定によっては、関数名の翻訳やセミコロンが必要です。この例の ID はワイルドカードを含まず、金額は空白のない数値です。
小さなデータで比較する
ブックのコピーに Old と New という2つのシートを作成します。次の架空データを A 列と B 列に入力し、1行目を見出しにしてください。行の順序は意図的に変えています。
| Old の ID | Old の金額 | New の ID | New の金額 |
|---|---|---|---|
| A101 | 120 | A103 | 75 |
| A102 | 80 | A101 | 120 |
| A103 | 75 | A105 | 60 |
| A104 | 50 | A102 | 95 |
左の2列を Old に、右の2列を New に入れます。これは説明用のデータであり、顧客の成果や処理速度の測定結果ではありません。
まず各シートの補助列で、その行の ID が同じシート内に何件あるか数えます。
=COUNTIF($A$2:$A$5,A2)
この例では、空白でない各 ID の結果は 1 になるはずです。検索を行う前に、1 より大きい結果を調べてください。COUNTIF は大文字と小文字を区別せず、条件にワイルドカードを使用できます。大文字と小文字で別の ID を表す場合や、ID に *、?、~ が含まれる場合は別の照合ルールが必要です。これらの単純な数式をそのまま使わないでください。
次に Old!D2 で新しいデータ内の一致件数を数え、下方向にコピーします。
=COUNTIF(New!$A$2:$A$5,A2)
0 は Old にしか存在しない ID、1 は一意の候補、1 より大きい値は対応が曖昧な状態を示します。Old!E2 で新しい金額を取得します。
=XLOOKUP(A2,New!$A$2:$A$5,New!$B$2:$B$5,"Not found",0)
D が 1 で、両方の金額が有効な数値である場合に限り、E と B を比較します。レコードの欠落、空の金額、実際のゼロを同じものとして扱わないでください。計算で求めた金額なら、差分を分類する前に丸め方や許容差を決めます。
XLOOKUP は最初の一致項目を返すだけで、重複を解決するわけではありません。また、Excel 2016 と 2019 では使えません。これらのバージョンでは、同じ一意性の確認を済ませてから、E2 に次の完全一致の代替式を使えます。
=IFNA(INDEX(New!$B$2:$B$5,MATCH(A2,New!$A$2:$A$5,0)),"Not found")
関数の動作と互換性は、Microsoft の XLOOKUP の説明および INDEX と MATCH のガイドを参照してください。
最後に New 側で各 ID が Old に何件あるか数え、追加されたレコードを探します。
=COUNTIF(Old!$A$2:$A$5,A2)
この列を 0 で絞り込むと A105 が見つかります。Old から New への確認だけでは見落としてしまいます。
差分の理由がわかるレポートを作る
この例では、別シートのレポートは次の内容になります。
| ID | 旧金額 | 新金額 | 状態 |
|---|---|---|---|
| A101 | 120 | 120 | 変更なし |
| A102 | 80 | 95 | 変更あり |
| A103 | 75 | 75 | 変更なし |
| A104 | 50 | — | Old のみに存在 |
| A105 | — | 60 | New のみに存在 |
異なる ID は5件で、変更なしが2件、変更ありが1件、Old のみが1件、New のみが1件です。元データはどちらも4行あります。同じ行番号同士を比較すると、実態を誤って捉えてしまいます。
複数の項目を比べる場合は、変更ごとに項目名、旧値、新値を記録します。ID に加えて元データの行参照も残してください。空または重複したキーは黙って削除せず、別の確認リストに分けます。元データを変更する前に、重複を安全に削除する方法も確認しましょう。
GetSheetAI に比較範囲を指定する
GetSheetAI の Excel サイドバーでは、「このシートを比較して」だけでなく、キー、項目、出力先、ルールを指定します。次の依頼文の項目名は実際の見出しに置き換えてください。元データに金額列しかなければ、Status は省いてください。
Old と New の実際にデータが入っている範囲を、Order ID で比較してください。まず各データの空 ID と重複 ID を報告してください。一意に対応付けられない場合は停止して、照合方法を質問してください。比較するのは Amount と Status のみです。レコードの欠落、空の値、ゼロを区別してください。Comparison シートを新規作成し、ID、変更項目、旧値、新値、結果の分類、元データの行参照を記載してください。片方にしかないレコードも含めてください。元のシートはどちらも変更しないでください。件数を集計し、未解決の行を示してください。
変更を受け入れる前に、提案された照合ルールを確認してください。AI は比較作業を整理する手助けになりますが、異なる2つの識別子が同じ注文を表すかどうかは、あなたがルールを示さなければ判断できません。
レポートを使う前に確認する
- 変更なしと変更ありをそれぞれ1件、各データにしかないレコードをそれぞれ1件確認します。
- 全分類の異なる ID の件数を照合し、未解決の ID は別に数えます。
- どちらの元データを並べ替えても、分類が変わらないことを確認します。
- 表示行やフィルター後の行だけでなく、データ全体の範囲を含めたか確認します。
- 他の人が比較を再現できるよう、元のエクスポートを保存します。
バージョン比較ではなく、請求書と入金の照合をしたい場合は、請求書の照合手順を使ってください。1枚の請求書に複数の入金がある場合は、この例の「1 ID に1レコード」とは違うルールが必要です。
