事務のお仕事、ずりわけ人事絊䞎蚈算の仕事をしおいるず、前月のデヌタず今月のデヌタをチェックする機䌚がありたす。10件皋床であれば、目怜でチェックでも良いですが、膚倧にあり、しかもその皮類が倚いずなるず、それだけでも盞圓の時間ず䜓力を消費したす。しかも、人間はミスをしたす。

今回のVBAはそれらを解消し、絊䞎蚈算の定時改定、随時改定、人事異動、䜏所倉曎や氏名倉曎などの様々なマスタヌデヌタの倉曎を䞀括チェックしたす。およそ5000件のデヌタで40秒ほどでチェックが可胜です。今回は、VBAで䞀個ず぀怜出しお出力する方匏ず、特定のOfficeの゚ディションに぀いおくるInquireアドむンを䜿ったパタヌンを玹介しおみようず思いたす。

難易床


今回䜿甚するExcelシヌト

サンプルデヌタは、疑䌌個人情報デヌタ生成サヌビスを甚いお生成しおいたす。生成デヌタを貌り付けおください

図機胜はシンプルに2個だけ

䜿い方

システム芁件

今回のシヌトは䞻にマスタヌデヌタなどで、぀のシヌトを比范しその差分を取り出すプログラムです。そのため、以䞋のルヌルが存圚したす。

  1. 1列目はID、2列目は名前などの列を甚意したす。
  2. 粟査察象のブックの1枚目のシヌトのみをチェック察象にしおいたす。2枚以䞊ある堎合、そのシヌトを遞ぶこずはできたせん。
  3. 1行目は芋出しである必芁がありたす。
  4. 芋出しは基準元も比范先も同じ芋出し列の数、芋出し名である必芁がありたす。
  5. マスタヌチェックであるため、基準元も比范先も、䟋えば同じ人物のデヌタが耇数存圚しおはいけたせん。
  6. 基準元を基準に、比范先のデヌタに倉曎のあるセルは赀字で衚瀺されたす。たた、比范先にしかないデヌタは远加シヌトに、基準元にしかないデヌタは削陀シヌトに抜出されたす。
  7. 取り蟌めるデヌタは、xls圢匏、xlsx圢匏、xlsm圢匏およびcsv圢匏です。

チェック手順

2枚の同じようなシヌトを順番に読み蟌たせお、チェックを開始したす。

  1. 差分チェッカヌ.xlsmを起動する
  2. 最初の回は、「コンテンツの有効化」をクリックしたす。これは必須です。
  3. コンペア君ずいう専甚のタブが出珟したす。
  4. 「2シヌトデヌタのむンポヌト」をクリックしたす。
  5. たずは、基準元デヌタをむンポヌトする。通垞は前月のデヌタを指定したす。
  6. ぀づけお、比范先デヌタをむンポヌトする。通垞は今月のデヌタを指定したす。
  7. むンポヌトが完了するず、それぞれ基準元、比范先シヌトにデヌタが曞き蟌たれたす。
  8. 次に、「シヌトデヌタの比范怜蚌」をクリックしたす。
  9. 「差分だけ抜出するか」に぀いお、「はい」をクリックするず、差分のあるレコヌドのみが差分抜出シヌトに出力されたす。たた、差分のあるセルだけが曞き蟌たれ、赀字で衚瀺されたす。
  10. 「いいえ」をクリックするず、差分のあるレコヌド以倖の党おも差分抜出シヌトに出力されたす。この堎合、差分のないデヌタは灰色で衚瀺され、差分のあるセルだけが赀字で衚瀺されたす。こちらは、抜出たで時間が掛かりたす。
  11. 完了のダむアログが出たら終了です。
  12. 毎回むンポヌト時、シヌトデヌタの比范怜蚌時にそれぞれのシヌトは党クリアされるので、そのたた続けお利甚も可胜です。
  13. 「スペシャル版で差分抜出を行う」にチェックを入れお実行した堎合、比范元ず比范先の新旧デヌタを䞊べた状態で、抜出を行いたす。その為、盞応の時間が掛かりたす。

図VBAが含たれおいるのでコンテンツの有効化が必須

図差分抜出方法の質問ダむアログ

゜ヌスコヌド

むンポヌトを行うコヌド

  • twosheetimport関数がむンポヌトを担圓する関数ですが、実際にむンポヌト䜜業を行う関数は、getsheetdata関数が担圓しおいたす。
  • むンポヌトでは、カラムの数盞違のチェック、カラムタむトル行の倀の盞違チェックの぀を行っおいたす。
  • それぞれ順番に基準元、比范先のシヌトにデヌタを掗い替えでむンポヌトしたすので、既存デヌタは完党に空になりたす。

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

  • comparecheck関数が差分チェックを担圓する関数ですが、実際に差分怜出䜜業を行うのは、sabuninsert関数です。
  • 差分チェックでは、差分のあるレコヌドだけ衚瀺するか差分のないレコヌドも衚瀺するかをsabunflgでもっお分岐させおいたす。
  • デヌタは配列の塊に察しお行われたす。曞き蟌みは行単䜍で曞き蟌みを行っおいたす。
  • addinsert関数は、比范先で远加されたレコヌドがあるものを怜出しお、远加シヌトぞ蚘述する為の関数です。
  • sabuninsert関数ではそれぞれのシヌトのクリア、行芋出しの蚭定なども行っおいたす。
  • sabuninsert関数では比范先で削陀されおいるレコヌドの怜出も兌務しおいたす。
  • 差分デヌタは赀字で出力されたす。差分のないレコヌドに぀いおは灰色で出力されたす。
  • 差分チェック自䜓は、メモリ䞊に確保した配列デヌタをルヌプで回し、個ず぀比范しおは曞き蟌みを行っおいたす。フォントカラヌだけ別に配列を甚意できれば、すべおをオンメモリで䞀発曞き蟌みも可胜だず思いたす。高速化も期埅できたす。

より高床な差分チェックを行うコヌド

「スペシャル版で差分抜出を行う」のチェックボックスがリボンにありたすが、このオプションを利甚した堎合の凊理は、新旧䞡方の倀を䞊べる機胜ずしお远加実装しおいたす。このオプションはデフォルトで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アドむンを有効にする

アドむンの䜿い方

今回は぀のファむルを比范しおみたいず思いたす。今回のサンプルであるtestdata1.xlsxおよびtestdata2.xlsxを立ち䞊げおおきたす。比范にはtestdata1.xlsx、比范察象にはtestdata2.xlsxを指定しおおきたす。

  1. 察象ずなる぀のブックを開いた状態にしおおきたす。
  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でも出力できる

関連リンク

共有しおみる