VBAにて2つのExcelシートの差分を取り出してみる

事務のお仕事、とりわけ人事給与計算の仕事をしていると、前月のデータと今月のデータをチェックする機会があります。10件程度であれば、目検でチェックでも良いですが、膨大にあり、しかもその種類が多いとなると、それだけでも相当の時間と体力を消費します。しかも、人間はミスをします。

今回のVBAはそれらを解消し、給与計算の定時改定、随時改定、人事異動、住所変更や氏名変更などの様々なマスターデータの変更を一括チェックします。およそ5000件のデータで40秒ほどでチェックが可能です。今回は、VBAで一個ずつ検出して出力する方式と、特定のOfficeのエディションについてくるInquireアドインを使った2パターンを紹介してみようと思います。

今回使用するExcelシート

サンプルデータは、疑似個人情報データ生成サービスを用いて生成しています。生成データを貼り付けてください。また、今回はVBAにて1行ずつ比較していますが、VBAを使わず手っ取り早く相違するデータだけを抽出したい場合は、Power Queryを使うという手段もあります。以下のエントリーを参考に作ってみて下さい。

Power Queryで2つのExcelシートの差分を取り出してみる

図:機能はシンプルに2個だけ

使い方

システム要件

今回のシートは主にマスターデータなどで、2つのシートを比較しその差分を取り出すプログラムです。そのため、以下のルールが存在します。

  1. 1列目はID2列目は名前などの列を用意します。
  2. 精査対象のブックの1枚目のシートのみをチェック対象にしています。2枚以上ある場合、そのシートを選ぶことはできません。
  3. 1行目は見出しである必要があります。
  4. 見出しは基準元も比較先も同じ見出し列の数、見出し名である必要があります。
  5. マスターチェックであるため、基準元も比較先も、例えば同じ人物のデータが複数存在してはいけません。
  6. 基準元を基準に、比較先のデータに変更のあるセルは赤字で表示されます。また、比較先にしかないデータは追加シートに、基準元にしかないデータは削除シートに抽出されます。
  7. 取り込めるデータは、xls形式、xlsx形式、xlsm形式およびcsv形式です。

チェック手順

2枚の同じようなシートを順番に読み込ませて、チェックを開始します。

  1. 差分チェッカー.xlsmを起動する
  2. 最初の1回は、「コンテンツの有効化」をクリックします。これは必須です。
  3. コンペア君という専用のタブが出現します。
  4. 2シートデータのインポート」をクリックします。
  5. まずは、基準元データをインポートする。通常は前月のデータを指定します。
  6. つづけて、比較先データをインポートする。通常は今月のデータを指定します。
  7. インポートが完了すると、それぞれ基準元比較先シートにデータが書き込まれます。
  8. 次に、「シートデータの比較検証」をクリックします。
  9. 差分だけ抽出するか?」について、「はい」をクリックすると、差分のあるレコードのみが差分抽出シートに出力されます。また、差分のあるセルだけが書き込まれ、赤字で表示されます。
  10. いいえ」をクリックすると、差分のあるレコード以外の全ても差分抽出シートに出力されます。この場合、差分のないデータは灰色で表示され、差分のあるセルだけが赤字で表示されます。こちらは、抽出まで時間が掛かります。
  11. 完了のダイアログが出たら終了です。
  12. 毎回インポート時、シートデータの比較検証時にそれぞれのシートは全クリアされるので、そのまま続けて利用も可能です。
  13. スペシャル版で差分抽出を行う」にチェックを入れて実行した場合、比較元と比較先の新旧データを並べた状態で、抽出を行います。その為、相応の時間が掛かります。

図:VBAが含まれているのでコンテンツの有効化が必須

図:差分抽出方法の質問ダイアログ

ソースコード

インポートを行うコード

  • twosheetimport関数がインポートを担当する関数ですが、実際にインポート作業を行う関数は、getsheetdata関数が担当しています。
  • インポートでは、カラムの数相違のチェック、カラムタイトル行の値の相違チェックの2つを行っています。
  • それぞれ順番に基準元、比較先のシートにデータを洗い替えでインポートしますので、既存データは完全に空になります。

差分チェックを行うコード

  • comparecheck関数が差分チェックを担当する関数ですが、実際に差分検出作業を行うのは、sabuninsert関数です。
  • 差分チェックでは、差分のあるレコードだけ表示するか?差分のないレコードも表示するか?をsabunflgでもって分岐させています。
  • データは配列の塊に対して行われます。書き込みは1行単位で書き込みを行っています。
  • addinsert関数は、比較先で追加されたレコードがあるものを検出して、追加シートへ記述する為の関数です。
  • sabuninsert関数ではそれぞれのシートのクリア、行見出しの設定なども行っています。
  • sabuninsert関数では比較先で削除されているレコードの検出も兼務しています。
  • 差分データは赤字で出力されます。差分のないレコードについては灰色で出力されます。
  • 差分チェック自体は、メモリ上に確保した配列データをループで回し、1個ずつ比較しては書き込みを行っています。フォントカラーだけ別に配列を用意できれば、すべてをオンメモリで一発書き込みも可能だと思います。高速化も期待できます。

より高度な差分チェックを行うコード

スペシャル版で差分抽出を行う」のチェックボックスがリボンにありますが、このオプションを利用した場合の処理は、新旧両方の値を並べる機能として追加実装しています。このオプションはデフォルトでFalseですが、チェックを入れた場合には別のコードで処理をするようになっています。また、動的にリボンの値を取得する必要があるので、リボンにもコードの追加がなされています。

リボンのコード

メインの処理コード

  • リボンのチェックボックスの値は起動時にfalseで初期化。チェック時にオンオフ状態をグローバル変数に格納しています。
  • 見出しの行については、比較前に(B)、比較後に(A)と列名を付与しています。
  • 通常モードとの差はcntにて同じ位置のレコードを比較前と比較後のそれぞれのテーブルから値を書き込みしている点です。
  • また、何も書き込まないケースでもループを2回回して上げないと、書込位置がずれるので、そのようにしています。

Inquireプラグインを使ってみる

実は、Office Professional PlusおよびMicrosoft365 Professional Plusにだけ、Microsoft公式の差分チェッカーアドインが用意されています(実際には、差分チェックだけではなく、参照先チェックなどいろいろな機能が詰まってます)。このアドインはデフォルトでオフにされており、有効にしないと使えません。よって、通常のOfficeや単体パッケージを購入した場合、この差分チェッカーは使えないということです。

図:特定のバージョンじゃないと使えないのです

アドインを有効にする

指定のエディションであれば、既に初めからExcel側にはインストール済みです。しかし、デフォルトでオンにはなっていないので、オンにしてあげる必要があります。また、オンにする為には、「開発タブ」を表示してあげなければならないので、ちょっと手間です。

  1. 適当にExcelをまずは起動する
  2. メニューより「ファイル」⇒「オプション」を開く
  3. 左サイドメニューの中の「リボンのユーザ設定」をクリック
  4. 右サイドにあるチェックボックスのあるエリアに於いて、「開発」を探し、チェックを入れる
  5. OKボタンを押して閉じる。これでExcelに「開発」というタブが表示されます。
  6. 次に、その開発タブを開きます。
  7. COMアドイン」という項目があるので、これをクリックします。
  8. リストの中に「Inquire」というものがあるので、チェックを入れて、OKボタンを押します。
  9. これでさらにExcelに「検査」というタブが表示されるようになります。

図:開発タブをまずは表示しなければならない

図:Inquireアドインを有効にする

アドインの使い方

今回は2つのファイルを比較してみたいと思います。今回のサンプルであるtestdata1.xlsxおよびtestdata2.xlsxを立ち上げておきます。比較にはtestdata1.xlsx、比較対象にはtestdata2.xlsxを指定しておきます。

  1. 対象となる2つのブックを開いた状態にしておきます。
  2. 検査タブを開き、「ファイルを比較」をクリックします。
  3. ダイアログにおいて、比較には基準元になるファイルを、比較先には比較対象となるファイルを指定します。基準元は通常前月のファイル、比較先は通常は今月のファイルです。
  4. 比較ボタンをクリックする
  5. 比較結果が専用の画面で表示されます。ただし、英語表記なのでちょっと使いづらいかも。

図:正しく比較するファイルを指定しましょう

図:比較結果画面。正直、見づらい・・・・

比較結果の見方

比較結果は、中央下の画面に出てきます。非常に細かい分類で出てくるので、読み方が必要です。それぞれの検出結果をクリックすると対象の場所に、ジャンプする仕組みになっています。

  1. 赤字でAdded Rowsは比較先にだけある追加されたレコードを意味します。
  2. 赤字でDeleted Rowsは比較先では削除されているレコードを意味します。
  3. 緑字でEntered Value Changedは、比較先で変更されているセルを意味します。
  4. 緑字でEntered Value Addedは、空だったセルに比較先で追記されているセルを意味します。
  5. 緑字でEntered Value Deletedは、値が入っていたセルが空にされているセルを意味します。
  6. 緑字でCell Formatting Changedは、セルの書式設定が変更されているセルを意味します。

他にも、左下に検出項目の凡例がありますが、非常に細かく検出が可能です。タブにあるExport Resultsをクリックすると、検出結果のエクスポートが可能ですが、実務ではあまり役に立たないかなぁ。ただ、VBAで組んだ検出よりも高速に細かく検出が可能なのは素晴らしいと思います。

図:比較結果がExcelでも出力できる

関連リンク

VBAにて2つのExcelシートの差分を取り出してみる” に対して3件のコメントがあります。

  1. かず より:

    こんにちは
    エクセルの比較処理に苦慮して色々探しているうちにこのサイトに辿り着きました。
    素敵なツールだと思い早速DLして使ってみたのですが、インポートが下記エラーでできませんでした。
     「エラー91 オブジェクト変数またはWithブロック変数が設定されていません」

    VBのコードを見てみましたが恥ずかしながら理解するには時間がかかりそうです。
    原因を教えていただけると助かります。

    使用環境
     エクセル v16.36
     OSX

    よろしくお願い致します。

  2. ナベ より:

    差分抽出の方法を探していた時にこのサイトに出会いました。

    コードを確認していく内に「こんな書き方もあるのか」と目から鱗でした。

    記載されたコードを参考に差分抽出ツールを作成することが出来ました。

    助かりました。

    ありがとうございました。

    1. akanemaru2017 より:

      ナベ様

      それは良かったです。このコードは、件数がより多い場合は、配列を用意して配列で一発書き込みするように変更すれば
      より早く動くようになるので

      よければ挑戦してみてください。

      ※現在のコードは、1セルずつ書き込みです。

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)