Excel ファイルを比較するのは難しい方法です。より良い方法が 2 つあります。

月曜の朝、Excel ブックの「更新された」コピーが受信トレイにあります。しかし、実際に開いてみると、何が変わったのかわからないことがよくあります。間違い探しゲームをする代わりに、これらの組み込み Excel ツールを使用して、数秒で違いを強調表示します。

方法 1: 条件付き書式を使用して不一致の値を強調表示する

視覚的なアラートを使用して小規模なデータセットを監査する

条件付き書式設定は、2 つのデータセット間の違いを素早く視覚的に見つける方法です。ただし、これは両方のバージョンが同じブック内にある場合にのみ機能します。Excel では、条件付き書式設定の数式が別のブックを参照することは許可されていません。異なるワークブックにある場合は、両方のワークブックを開いて、次の手順に従ってそれらを統合します。

1. のタブを右クリックします。 更新されました シートをクリックし、 移動またはコピー。

2. で 予約するには ドロップダウン メニューで、 オリジナル ワークブック。

3. 選択 最後に移動 したがって、更新されたシートは元のシートの右側に表示されます。また、チェックしてください コピーを作成する このシートを他のワークブックに複製するだけの場合は、このシートを永久に移動する場合は、このオプションのチェックを外します。

4. クリック わかりました。

両方のシートを 1 つのファイルにまとめた状態で、 新しいウィンドウ で ビュー タブをクリックしてファイルの 2 番目のインスタンスを開き、 すべて > 縦方向に配置 それらをタイル状に並べます。これで、両方のワークシートを同時に並べて開くことができます。

これで、2 つのバージョン間の相違点を強調表示するように Excel に指示する準備が整いました。

1. 元のシート上のデータ範囲全体を選択します。

2. で 家 タブ、クリック 条件付き書式設定 > 新しいルール。

3. クリック 数式を使用して書式設定するセルを決定する。

4. ダイアログで、 をクリックします。 形式をクリックし、明るい赤などのハイライトの色を選択します。

5. 元のデータセットの最初のセルを選択し、次のように入力して数式を構築します。 <> 更新されたシート内の対応するセルを選択します。プレス F4 各参照に対して 3 回実行して、絶対ロックを解除します。

この例で使用した式は次のとおりです。

=A2<>Sales_Updated!A2

この方法は、小さくクリーンなデータセットにはうまく機能しますが、完全な行の配置に依存するという大きな弱点があります。いずれかのシートで誰かが行を挿入、削除、または並べ替えると、Excel は位置による比較を継続するため、誤った不一致が蔓延することになります。

方法 2: Power Query 結合を使用して行データを比較する

大規模なワークシートに対する耐久性のある監査証跡を構築する

より大きなデータセットのバージョンを比較する必要がある場合は、Power Query が最も堅牢な方法です。セルを位置で比較するのではなく、定義した値に基づいてデータを照合するため、行の移動や構造の変更に対する耐性が得られます。

まず、両方のデータセットが Excel テーブル (Ctrl+T)、両方を接続とし​​て Power Query エディターに読み込みます。

1. セルの 1 つを選択し、最初のテーブルを Power Query に読み込みます。 データ > テーブル/範囲から。

2. エディターで、 閉じてロード。

3. 選択 接続のみを作成する そしてクリックしてください わかりました。

4. 2 番目のテーブルに対してこれらの手順を繰り返します。

両方のテーブルを Power Query に読み込むと、比較を開始できます。まず、元のテーブルと更新されたバージョンを比較します。

1. 内のクエリの 1 つをダブルクリックします。 クエリと接続 ペインをクリックしてエディタを再度開きます。

2. 次に、 家 タブ、クリック クエリをマージ > クエリを新規としてマージ。

3. で マージ ダイアログで、 元のテーブル 上と 更新されたテーブル 下に。

4. 上の表の最初の列をクリックし、次に下の表の同じ列をクリックします。それから、ホールドしてください Ctrl 他のすべての列に対してこのプロセスを繰り返します。各列のペアには、一致する順序を示す番号が付けられていることに注意してください。

5. 選ぶ 左アンチ 結合タイプとして選択し、 わかりました。これにより、元の行には存在するが、更新されたバージョンには完全に一致しない行が返されます。つまり、それらの行は削除されたか、一致を破るような方法で変更されました。

ここで、新しいマージされたクエリにいくつかの小さな調整を加えます。

1. マージされた 2 番目のテーブル (ネストされたテーブルの列) を含む列を削除します。

2. クエリの名前を次のように変更します。 v1_変更済み。

ここでプロセスを繰り返す必要がありますが、「マージ」ダイアログでテーブルの順序を逆にします。 上部の表を更新しました そして 以下の元の表、同じように実行します 左アンチ クエリを結合して名前を変更します (たとえば、 v2_変更済み）。両方向でマージを実行すると、2 つの出力が得られます。1 つは元のデータセットにのみ存在する行 (更新で削除または変更された) を表示し、もう 1 つは更新されたデータセットにのみ存在する行 (新規または変更された行) を表示します。

これを完了したら、をクリックします 閉じてロード、 チェック テーブルをクリックして、 わかりました これらのクエリを 2 つの新しいワークシートにロードします。

この方法の利点は、2 つのソース データセットのいずれかに新しい行を追加して、 すべて更新 で データ タブに移動すると、新しいレコードが自動的に「変更済み」クエリに含まれ、情報が最新の状態に保たれます。

次回「更新された」ワークブックが受信トレイに届いたら、データに基づいてツールを選択してください。小さなリストを 5 分間で簡単に監査するには、条件付き書式設定のテクニックが最適です。ただし、行の順序が信頼できない大規模なデータセットの場合、Power Query は、漏れがないことを保証する最良の方法です。いずれにせよ、退屈な手作業を繰り返し可能な自動化されたプロセスに変えることができました。

OS

Windows、macOS、iPhone、iPad、Android

無料トライアル

1ヶ月

Microsoft 365 には、最大 5 台のデバイスで Word、Excel、PowerPoint などの Office アプリ、1 TB の OneDrive ストレージなどへのアクセスが含まれています。