Excelで管理している見積書、売上表、名簿、工程表などを更新していると、どこが変更されたのか分からなくなることがあります。
ファイル名が似たブックを目視で見比べる作業は時間がかかり、数式の違い、削除された行、書式だけの変更を見落としやすい点にも注意が必要です。
2つのExcelファイルを比較する方法は、目的に応じて数式、条件付き書式、Power Query、Spreadsheet Compareなどを使い分けるのが実用的です。
比較作業のポイントは、比較対象の並び順をそろえること、見出し行を除いて判定すること、差分の種類を値・数式・書式に分けることです。
この記事では、2つのExcelファイルから変更点を抽出し、確認しやすい一覧にする方法を詳しく解説します。
Excelファイルの差分を抽出する基本手順
それではまず、2つのExcelファイルを比較して変更点を抽出する基本手順について解説していきます。
| 商品コード | 旧ファイルの数量 | 新ファイルの数量 | 比較結果 |
|---|---|---|---|
| A001 | 12 | 12 | 一致 |
| A002 | 8 | 10 | 差分あり |
| A003 | 25 | 25 | 一致 |
比較前には、古いファイルと新しいファイルを別名で保存し、対象シートと比較範囲を決めておくと安全です。
比較する表の列構成と行の並び順が同じであることを確認してから数式やツールを使いましょう。
比較対象となるブックとシートの整理
比較元と比較先のファイルは、どちらが更新後なのか分かる名前にして保存します。
たとえば、旧版を「売上一覧_旧」、更新版を「売上一覧_新」としておけば、参照式を入力するときの選択ミスを減らせます。
Excelではブック名だけでなく、シート名、セル番地、数式、表示形式、コメントなどにも差分が生じます。
そのため、今回確認したい差分が売上金額のようなセル値なのか、計算ロジックのような数式なのかを先に決めることが大切です。
表の途中に空白行が入っている場合や、担当者ごとに行の並びが変わっている場合には、単純なセル同士の比較だけでは正しい結果にならないことがあります。
商品コード、社員番号、注文番号など、各行を特定できる一意のキー項目を用意して比較する方法が向いています。
【操作のポイント】比較の前に、旧版と新版のどちらを基準にするかを決め、ヘッダー名やキー列に表記ゆれがないか確認します。
差分の種類ごとの確認方針
セルの比較では、見た目が同じでも数式が異なるケースがあります。
たとえば、どちらのセルも100と表示されていても、一方が直接入力の100で、もう一方がSUM関数による計算結果なら、管理上は確認すべき差分かもしれません。
逆に、表示形式の違いだけを確認したい場面では、値の比較式だけでは不十分です。
値の差分、数式の差分、書式の差分を混ぜて考えないことが、比較結果を読みやすくする基本になります。
更新履歴を確認する目的なら、変更されたセルの位置と新旧の内容を一覧化すると、関係者への報告にも活用できます。
監査や承認のために比較する場合は、元ファイルを上書きせず、結果専用の比較ブックを作る方法が安心です。
値の比較ではセルの内容を照合し、数式の比較では数式バーに表示される式を照合し、書式の比較では色、罫線、表示形式、列幅などを別途確認します。
【操作のポイント】比較結果に差分ありと表示された後、値・数式・書式のどれが異なるのかを追加確認すると原因を追いやすくなります。
バックアップと比較用ファイルの作成
比較の作業中に元データを修正すると、変更前の状態を確認できなくなるおそれがあります。
まず比較対象の2ファイルを複製し、作業用フォルダに保存してから操作を始めましょう。
比較用ブックには、旧データ、新データ、比較結果の3シートを作成すると、参照関係が分かりやすくなります。
1行目にはヘッダーを置き、2行目からデータを入力する構成にすると、数式を下方向へオートフィルしやすくなります。
ファイルを共有する場合は、数式セルを誤って編集されないように、比較結果のシートだけを別ファイルで渡す運用も有効です。
比較結果は元データそのものではなく、確認のための記録として残すと、後から差分の経緯を確認できます。
【操作のポイント】元ファイルの上書き保存は避け、比較専用のコピーを作成してから参照式や条件付き書式を設定します。
数式を使ったセル単位の変更点比較
続いては、数式で2つのExcelファイルのセル内容を比較する方法を確認していきます。
| A列 商品コード | B列 旧数量 | C列 新数量 | D列 判定式 |
|---|---|---|---|
| A001 | 12 | 12 | 一致 |
| A002 | 8 | 10 | 差分あり |
セル位置がそろった表なら、IF関数と比較演算子を使うだけで差分の有無を判定できます。
数式による比較は、追加のツールを導入できない環境でも使いやすく、結果をフィルターで絞り込める点がメリットです。
IF関数による値の一致判定
旧データがB列、新データがC列、判定結果をD列に表示する場合を考えます。
1行目はヘッダーとし、最初のデータである2行目のD2セルに次の数式を入力します。
=IF(B2=C2,”一致”,”差分あり”)
この式では、B2セルとC2セルが等しい場合に一致、異なる場合に差分ありと表示されます。
入力後、D2セル右下のフィルハンドルを下へドラッグするか、ダブルクリックしてオートフィルを実行します。
比較式は先頭のデータ行だけに入力してから下へコピーすると、参照先が行ごとに自動調整されます。
文字列の前後に余分な空白がある場合は、見た目が似ていても差分ありになることがあります。
入力ミスではなく空白が原因と考えられるときは、TRIM関数で余分なスペースを除去してから比較する方法もあります。
【操作のポイント】数式を入力する前に、比較列のデータ型が文字列か数値かをそろえ、日付や金額の表示形式にも注意します。
空白セルとエラー値を含む比較式
空白セルを含む表では、両方が空白の場合を一致として扱うか、未入力として別表示にするかを決める必要があります。
未入力を判別したい場合は、D2セルに次のような数式を使用できます。
=IF(AND(B2=””,C2=””),”両方空白”,IF(B2=C2,”一致”,”差分あり”))
AND関数は複数の条件がすべて満たされるかを判定する関数です。
この例ではB2とC2がともに空白なら両方空白、それ以外では一致または差分ありを返します。
数式エラーが混じるデータでは、IFERROR関数を組み合わせて比較結果を読みやすくすることもできます。
ただし、エラーを一律に空文字へ置き換えると、元の計算エラーを見逃す可能性があります。
エラー値は差分そのものではなく、データ品質の問題を示す場合もあるため、別列で記録するとよいでしょう。
【操作のポイント】空白、ゼロ、エラーの扱いを事前に統一し、判定ルールを比較結果のヘッダーや注記に残します。
数式そのものを比較する方法
表示された計算結果ではなく、セルに入力されている数式を比較したいときにはFORMULATEXT関数が役立ちます。
たとえば旧ファイルのB2セルと新ファイルのC2セルに数式がある場合、数式を文字列として取り出して比較できます。
=IF(FORMULATEXT(B2)=FORMULATEXT(C2),”数式一致”,”数式差分あり”)
FORMULATEXT関数は数式セル以外を参照するとエラーになるため、値と数式が混在する表ではIFERROR関数を加える必要があります。
外部参照や絶対参照が含まれる数式は、見た目が似ていても参照先が異なることがあります。
数式比較の結果が異なるときは、参照セル、関数名、範囲、固定参照の有無を順に確認しましょう。
計算結果が一致していても数式が異なれば、将来の更新時に差が表面化する可能性があります。
重要な集計表や予算表では、値の比較と数式の比較を両方実施することが望ましいでしょう。
【操作のポイント】数式比較は計算セルだけに対象を絞り、値を直接入力する列とは分けて結果を確認します。
条件付き書式による差分セルの可視化
続いては、条件付き書式で異なるセルを色付けして確認する方法を解説していきます。
| 商品コード | 比較元 | 比較先 |
|---|---|---|
| A001 | 1200 | 1200 |
| A002 | 850 | 900 |
条件付き書式は、差分セルを一覧にしなくても、表の中で変更箇所をすぐに見つけられる機能です。
大量のセルを比較する場合は、判定列と色付けを併用すると、確認漏れを抑えられます。
比較範囲をそろえて条件付き書式を設定
比較元の表をB列、比較先の表をC列に配置し、2行目から最終行までを選択します。
ホームタブから条件付き書式を選び、新しいルールで数式を使用して書式設定するセルを決定する項目を選びます。
比較元のB2セルを対象にする場合の数式は次のとおりです。
=B2<>C2
不等号の組み合わせは、2つのセルが異なる場合に真になる比較式です。
書式として淡い赤色の塗りつぶしを設定すると、変更されたセルだけを視覚的に確認できます。
条件付き書式の数式では、先頭行のセル参照を基準にすることが重要です。
範囲選択と参照式がずれると、別の行と比較されてしまうため、適用先の範囲も確認しましょう。
【操作のポイント】条件付き書式は先頭データ行の式で設定し、列記号や行番号に不要な固定記号を付けないようにします。
一致しない行を抽出するフィルター
色付けだけでは、どの行が異なるのかを件数として把握しにくい場合があります。
そのときは、判定列にIF関数を追加し、差分ありだけをオートフィルターで絞り込む方法が便利です。
たとえばD列に一致または差分ありを表示し、データタブのフィルターを設定します。
判定列の▼をクリックして差分ありだけにチェックを残せば、変更されたレコードだけを画面に表示できます。
この方法なら、差分セルが複数列に散らばっていても、変更された行単位で確認できます。
差分ありの行を抽出した後に、フィルターで非表示になった行まで削除しないよう注意しましょう。
【操作のポイント】フィルター結果を別シートへコピーする場合は、可視セルだけを選択してからコピーすると、必要な行だけを取り出せます。
書式差分を確認するときの注意点
セル値が一致していても、フォント色、塗りつぶし、罫線、日付形式、表示桁数が異なることがあります。
条件付き書式は主に値や数式の判定に使うため、通常の書式変更を完全に検出する用途には向きません。
表示が異なるセルを見つけた場合は、ホームタブの書式のコピーやセルの書式設定画面を使って内容を比較します。
数値の1234と、表示形式が異なる1,234円や1234.00は、値としては一致していることがあります。
見た目の差とデータの差を区別することで、不要な修正作業を減らせます。
帳票として提出するファイルでは、印刷範囲、改ページ、列幅、非表示列の状態も確認対象に含めましょう。
【操作のポイント】書式の確認は、セルの書式設定画面で表示形式、配置、フォント、罫線、塗りつぶしの順に見ると整理しやすいです。
Spreadsheet Compareを使ったブック比較
続いては、Microsoft OfficeのSpreadsheet Compareを利用してブック全体を比較する方法を確認していきます。
| 比較項目 | 数式比較 | Spreadsheet Compare |
|---|---|---|
| セル値 | 対応可能 | 対応可能 |
| 数式 | 対応可能 | 対応可能 |
| 書式や構造 | 確認に手間 | 確認しやすい |
Spreadsheet Compareは、ブック、シート、セル、数式、書式などの違いを比較画面で確認できる機能です。
利用できるOfficeのエディションや環境には差があるため、見当たらない場合は数式やPower Queryによる方法を検討します。
比較する2ファイルの指定
Spreadsheet Compareを起動したら、比較するファイルと比較対象のファイルをそれぞれ指定します。
旧版を左側、新版を右側に選ぶなど、社内で比較の順番を決めておくと結果を読み違えにくくなります。
比較前にExcelで対象ブックを閉じておくと、保存状態の違いによる混乱を避けやすくなります。
ブック内に外部リンクや保護されたシートがある場合は、比較できる項目が制限されることもあります。
比較ツールは変更点を検出しますが、どちらが正しい値かまでは判断しません。
差分が見つかったら、更新担当者、更新日時、元資料を確認して修正の要否を判断しましょう。
【操作のポイント】比較対象を指定するときは、ファイル名だけでなく保存日時と保存場所も確認して、古いコピーを選ばないようにします。
比較結果の色分けと詳細表示
比較を実行すると、変更された項目が種類ごとに一覧表示され、該当セルの位置を確認できます。
セル値の変更、数式の変更、書式の変更、行や列の追加削除などを区別して確認できる点が特徴です。
結果画面で項目を選択すると、左右のブックの該当箇所へ移動して内容を見比べられます。
大量の差分があるときは、まず数式、次に値、最後に書式という順で絞り込むと、重要な変更を優先できます。
列の挿入や削除があると、以降の多数のセルが差分として表示される場合があります。
その場合は、単なるずれなのか、データ内容の変更なのかをシート構成から確認する必要があります。
【操作のポイント】差分一覧の件数だけで判断せず、変更種類でフィルターをかけて重要な数式や金額の変更から確認します。
上のイメージ図では、D2セルの判定とC2セルの変更値を赤枠で示しています。
表の先頭データ行で判定式を作成し、オートフィルで下方向にコピーすれば、行ごとの比較結果を素早く表示できます。
比較結果を記録して共有する方法
比較結果に重要な変更が含まれる場合は、画面で確認するだけでなく、結果を記録として残すことが大切です。
変更箇所、旧値、新値、変更理由、確認者、確認日を列として用意すると、変更管理表として利用できます。
比較ツールで見つかった差分をすべて修正する必要はありません。
意図した更新と誤操作を分け、承認済みの変更であるかを確認してから対応を決めます。
比較作業の目的は差分をなくすことではなく、差分の内容を正しく把握することです。
共有相手がExcelを操作しない場合は、結果一覧をPDF化するか、変更箇所をコメント付きで別シートへまとめる方法もあります。
【操作のポイント】差分一覧には旧値と新値を並べ、変更理由を記録する列を追加すると、再確認や引き継ぎがしやすくなります。
Power Queryによるキー列を基準にしたデータ照合
続いては、行の並び順が異なるファイルでも比較しやすいPower Queryの活用方法を解説していきます。
| 顧客コード | 旧ファイル | 新ファイル | 照合結果 |
|---|---|---|---|
| C101 | 東京支店 | 東京支店 | 一致 |
| C102 | 大阪支店 | 大阪営業所 | 変更あり |
Power Queryは、複数のテーブルを取り込み、共通のキー列で結合して照合する機能です。
担当者名の並び順やデータの行数が変わる一覧を比較したいときに、特に役立ちます。
テーブル化とデータの取り込み
比較元と比較先の表は、それぞれ1行目をヘッダーとしてテーブル化しておくと扱いやすくなります。
表内のセルを選択して、挿入タブからテーブルを作成し、先頭行をテーブルの見出しとして使用する設定を確認します。
テーブル名は旧データ、新データのように意味が分かる名前へ変更します。
データタブからテーブルまたは範囲から取得を選び、それぞれをPower Queryエディターに読み込みます。
キー列には商品コードや社員番号など、重複しにくい列を選ぶことが照合精度を左右します。
氏名だけをキーにすると同姓同名や表記ゆれで正しく結合できない場合があるため、コードと名称を併用することも検討しましょう。
【操作のポイント】取り込む前にテーブルの見出し名を統一し、キー列に空白や重複がないかを確認します。
クエリの結合による差分検出
Power Queryエディターでクエリの結合を選び、旧データと新データをキー列で結合します。
旧データに存在して新データにない行、新データに追加された行、両方に存在する行を区別するには、結合の種類を使い分けます。
外部結合を使うと、片方にしか存在しないレコードも残して確認できます。
結合後は展開ボタンで新データ側の必要な列を表示し、旧値と新値を横に並べます。
その後、カスタム列で一致または変更ありを返す式を作成すると、更新内容を一覧で確認できます。
行番号ではなくキー列を基準に照合するため、並べ替え後のファイルにも対応しやすい点がPower Queryの強みです。
Power Queryでは、結合後に旧金額と新金額を横並びにし、両列が異なるレコードをフィルターで抽出する流れが基本です。
【操作のポイント】結合の種類は目的に合わせて選び、追加データと削除データを見つけたい場合は片方だけを残さない設定を確認します。
更新ボタンによる定期比較
毎月同じ形式のファイルを比較する業務では、Power Queryの設定を保存しておくと効率的です。
次回以降は、参照先のファイルを差し替えるか、同じフォルダへ最新ファイルを保存してから更新を実行します。
更新によって新しい比較結果が作成されるため、同じ手順を繰り返し入力する負担を抑えられます。
ただし、列名の変更、ファイル名の大幅な変更、キー列の欠損があると、クエリがエラーになることがあります。
定期運用では、ファイルのテンプレートを固定し、保存場所と命名規則を共有しておくと安定します。
Power Queryの自動更新は、入力ルールが維持されて初めて正確に機能します。
【操作のポイント】定期比較用のフォルダには対象外のExcelファイルを混在させず、更新前に取り込み対象を確認します。
まとめ Excelファイルの比較と差分抽出の方法
Excelファイルを比較するときは、まず比較の目的を値、数式、書式、行の追加削除のどれに置くかを決めることが重要です。
セル位置と行順がそろっている表なら、IF関数による一致判定と条件付き書式の組み合わせで、変更点を素早く確認できます。
数式が変更されていないか確認したい場合は、FORMULATEXT関数や比較ツールを活用すると安心です。
書式やシート構造を含めてブック全体を見比べるなら、Spreadsheet Compareが便利な選択肢になります。
並び順が異なる名簿やマスターデータでは、商品コードや社員番号などのキー列で結合できるPower Queryが有効です。
比較結果は差分ありという表示だけで終わらせず、旧値、新値、変更理由を残すことで、確認と共有の質が高まります。
元データは必ずバックアップし、比較用のシートやブックで操作を進めましょう。
目的に合った比較方法を選び、目視だけでは見落としやすい変更点を正確に把握していきましょう。
