【EXCEL:VBA】2つのデータの差分を抽出したい

2つのデータの差分を抽出したい

この記事では、EXCELで2つのデータを比較して、差分行を抽出する機能について説明しています。

 

比較したいデータを2つのシートにそれぞれ入力し、比較元シートと比較先シートを指定すれば差分行を抽出できます。

 

1行ずつを比較するので、全く同じ行が存在しない場合、差分として抽出します。

 

 

データ比較は、結構時間がかかる作業ですし、比較ミスしやすい作業です。

 

こういう単純で、もれなく行う必要がある作業は、VBAを使うことをオススメします。

 

 

それでは、EXCELで2つのデータを比較して、差分を抽出する機能を作成していきましょう。

 

困っている女性

EXCELに入力されている2つのデータの差分を抽出したいの・・・。

困っている女性

以前のデータに対して、何を追加したか忘れてしまって、調べるのに時間がかかってしまうの。
それなら、VBAを使えば2つのデータを比較して、差分行を簡単に抽出できるよ。

解決する男性

困っている女性

それそれ、早く教えてよ。

 

以下の記事では、2つのデータを比較して、追加・変更・削除したデータを調べる機能について説明しています。

【EXCEL:VBA】2つのデータを比較したい

今回の記事と合わせて、読んでみてください。

 

EXCELで2つのデータの差分を抽出する機能に必要なものを、1つ1つ順番に説明していきます。

1.2つのデータの差分を抽出するために必要なシートを作成する

「メイン」シートを作成する

メインシートを追加する

メインシートを追加する

  1. 「メイン」という名前のシートを作成します。
  2. 1行目に「比較元シート」、「比較先シート」のタイトル行を作成します。
  3. 2行目は「比較元シート」、「比較先シート」の名前を入力する行となります。

 

「比較元シート」、「比較先シート」には、準備したシートの名前を入力します。

 

比較したいシートを2つ作成する

比較したいシートその1

比較したいシートその1

 

比較したいシートの1つ目を作成します。

  1. 上記画像では「データ①」という名前にしていますが、お好きなお名前でシートを作成します。
  2. 1行目には、各項目の項目名を入力します。いわゆる、ヘッダー行です。
  3. 2行目以降は、実際のデータを入力します。

 

比較したいシートその2

比較したいシートその2

 

比較したいシートの2つ目を作成します。

  1. 上記画像では「データ②」という名前にしていますが、お好きなお名前でシートを作成します。
  2. 1行目には、各項目の項目名を入力します。いわゆる、ヘッダー行です。
  3. 2行目以降は、実際のデータを入力します。

 

「差分」シートを作成する

差分シートを追加する

差分シートを追加する

2つのシートを比較して、抽出した差分を書き込むシートを作成します。

  1. 「差分」という名前のシートを作成します。
  2. 中身は特に何も入力する必要はありません。

 

2.2つのデータを比較する機能を記入する

「Microsoft Visual Basic for Applications」を起動します。

VBAプログラムを記入する(その1)

VBAプログラムを記入する(その1)

 

VBAプログラムを記入する(その2)

VBAプログラムを記入する(その2)

 

標準モジュールを追加して、以下のソースを書きます。

 

3.2つのデータを比較して差分を抽出する機能の使い方

それでは、2つのデータを比較する方法を説明します。

マクロボタンをクリックする

マクロボタンをクリックする

 

まず、「メイン」シートのA2セルに「比較元シート名」を入力します。

上図では、「データ①」を入力しています。

そして、B2セルに「比較先シート名」を入力します。

上図では、「データ②」を入力しています。

 

次に、「開発」タブの「マクロ」をクリックします。

 

実行ボタンをクリックする

実行ボタンをクリックする

 

先程作成した自作関数「MainProc」が一覧に表示されていますので、選択します。

次に、「実行」ボタンをクリックしてデータ比較を開始します。

 

少し待つと、データ比較して抽出した差分が「差分」シートに追加されます。

それでは、「差分」シートに追加された差分データを確認してみましょう。

差分抽出結果その1

差分抽出結果その1

 

比較元シート「データ①」に存在しており、比較先シート「データ②」に存在しない行が、差分シートに追加されています。

 

次は、逆にして実行してみます。

比較元シートに「データ②」、比較先シートに「データ①」を入力して、実行します。

比較先と比較元を変更

比較先と比較元を変更

 

差分の結果は、以下です。

差分結果その2

差分結果その2

 

比較元シート「データ②」に存在しており、比較先シート「データ①」に存在しない行が、差分シートに追加されています。

 

どうですか、簡単でしょ。

もっと大量のデータでも、同じく簡単ですのでやってみてください。

 

4.2つのデータを比較して差分を抽出するVBAプログラムの説明

それでは、VBAプログラムを説明します。

①「メイン」シートを変数に格納する

ここでは、このあとの処理で使う「メイン」シートを変数に格納しています。

そうすることにより、入力するプログラムを短くすることができるからです。

何度も「ThisWorkbook.Sheets(“メイン”)」と入力するより、「shtMain」と入力するほうが少なくてすむからです。

 

②比較元シート名を変数に格納する

ここでは、A2セルに入力されている「比較元シート名」を変数に格納しています。

 

③比較先シート名を変数に格納する

ここでは、B2セルに入力されている「比較先シート名」を変数に格納しています。

 

④比較元シートを変数に格納する

ここでは、比較元シートを変数に格納しています。

 

⑤比較先シートを変数に格納する

ここでは、比較先シートを変数に格納しています。

 

⑥差分シートを変数に格納する

ここでは、差分シートを変数に格納しています。

 

⑦比較元シートの最終行を取得する

ここでは、比較元シートのデータが入力されている最終行を取得して、変数に格納しています。

最終行を求めておくことで、何行目まで比較するのかを判断するのに利用するからです。

 

⑧比較元シートの最終列を取得する

ここでは、比較元シートの最終列を変数に格納しています。

最終列を求めておくことで、何列目まで比較するのかを判断するのに利用するからです。

 

⑨比較先シートの最終行を取得する

ここでは、比較先シートのデータが入力されている最終行を取得して、変数に格納しています。

最終行を求めておくことで、何行目まで比較するのかを判断するのに利用するからです。

 

⑩差分シートをクリアする

ここでは、差分シートに入力されているデータをクリアしています。

何度実行しても良いように差分シートをクリアしています。

 

⑪比較元シートのヘッダー行を差分シートにコピーする

ここでは、比較元シートのヘッダー行を差分シートの1行目にコピーしています。

 

⑫比較元シートと比較先シートを比較し、差分行を差分シートにコピーする

 

ここでは、比較元シートと比較先シートを比較し、差分行を差分シートにコピーしています。

具体的には、比較元シートに存在しているが、比較先シートに存在しない行を差分シートにコピーします。

 

それでは、比較したいデータを2つ用意して、差分データを抽出してみましょう。

 

以上です。

 

 

最後まで読んでいただきありがとうございました。
この記事をシェアしていただけると喜びます。

 

6 COMMENTS

たなかしんすけ

ネットで検索したところ上記記事を見つけて試させて頂いたのですが、
思うように差分を出力が出来ず藁にもすがる思いで連絡させて頂きました。
業務で利用しているデータなので実ファイルを提供できないのですが、
比較元に4134行のデータ、比較先に3658行のデータで差分を出力させたところ
1322件のデータがヒットしました。そこで4134件のデータに1322件のデータを
追加したところ複数の重複したデータが存在していました。
そこで重複したデータの片方(1行)を比較元、データのもう片方(1行)を比較先に
入力して差分を出力させたところ差分は、0件となりました。
これだけの情報では難しいかもしれませんが考えられる原因がわかれば
教えて頂けると大変助かります。以上、宜しくお願い致します。

返信する
くうねる

比較元データと比較先データに重複する行が含まれている可能性があります。
それぞれのデータから重複する行を削除してください。

具体的には、以下を試してみてください。
1.比較元データ(4134行)に対して、EXCELの機能「重複の削除」を実行する。(全ての列を対象とする)
2.比較先データ(3658行)に対して、EXCELの機能「重複の削除」を実行する。(全ての列を対象とする)
3.2つのデータ差分を抽出する(マクロ)

返信する
たなか

早速ご回答頂きありがとうございます。
比較元と比較先のシートにおいて重複の削除を実行しましたが、
重複は、みつかりませんでした。
自分のレベルじゃわからない問題があるのかもしれません。
お時間を割いて頂きありがとうございました。

返信する
ゴウ

初心者なので、とても参考になりました。
差分で違ったセルごとに色を付けるには、どうコードを書けば良いでしょうか?
教えて頂ければ幸いです。何卒、宜しくお願い致します。

返信する
くうねる

比較元シートの行(各列の値の組み合わせ)が、比較先シートに存在しているかをチェックするプログラムとなっています。
したがって、違ったセルに色をつけることはできません。

求められている機能は、以下を想像しています。
例えば従業員番号が同じで、それ以外の値のどこが異なっているかを知りたい。

上記のような機能を求められているのでしたら、以下の記事を参考にされてみてはどうでしょうか?

【EXCEL:VBA】2つのデータを比較したい
https://excel.kuuneruch.com/sasikomi-comp-data/

返信する
ゴウ

ご回答ありがとうございます。
助かります!
参考にさせて頂きます。

返信する

くうねる へ返信する コメントをキャンセル

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