エクセルのデータ同士を比較・照合して、チェックをしたり、色をつけたりすることがあります。
たとえば、以下の表1と表2のデータを比較して、チェックする場合です。
こういう作業をすることを指して、表1と表2を「照合する」「マッチングさせる」といいます。こういった仕事は、関数でも可能ですが、エクセルマクロでやるほうが圧倒的に有利です。
この記事ではエクセルマクロで照合、マッチングを行う方法を理由もあわせて紹介します。
- エクセル関数(vlookup match index)は大量のデータ照合に向かない
- エクセルマクロで2つの表データを照合
- エクセルマクロで別シートの2つのデータ同士を照合
- エクセルマクロで別ファイルの2つのデータ同士を照合
- 複数条件でデータを照合して色付け
- マクロで部分一致のデータを照合してカウント
- マクロによるデータ照合では最終行を自動取得すると便利
- テンプレートファイルをダウンロードしたい方はコチラから
- サンプルプログラムを上手に活用するために自力で編集できるようになろう
エクセル関数(vlookup match index)は大量のデータ照合に向かない
エクセルマクロを使えない場合、VLookUp関数(エクセルのワークシート関数)を使います。あるいはMatch関数やIndex関数をVLookUp関数と複数組み合わせて、ようやく解決できます。
一般的に、これらのワークシート関数の習得は難しいとされています。
しかし、エクセルのワークシート関数を習得できれば「すべてうまくいくか？」というと、そうでもありません。なぜなら、これらのワークシート関数には重大な欠点があるからです。
エクセル関数で大量のデータを処理すると重くなる
実は、エクセルのワークシート関数は、エクセルの動作を重くなる原因になります。
たとえば、数百行くらいのセルにこれらのワークシート関数が埋め込まれていると、PC画面に砂時計が出てきて、エクセルが動かない時間が続きます。
そのため、これらのワークシート関数が1000件も埋め込まれていれば、「セルの値を1回なおすごとに、エクセルが1分くらい固まる」ことはザラです。そうなると、仕事になりません。
習得に苦労する割に、使えるようになっても仕事が進まないので、ワークシート関数を覚える努力をしてもあまりいいことはありません。
では、どうすべきかというと。「VLookUp関数」、「Match関数」、「Index関数」は覚えない。その代わりに、マクロを覚えることをオススメします。
エクセルマクロを習得するほうがトクする
エクセルマクロを習得すれば、エクセルが固まることなく、自動計算が可能です。しかも、アレンジを加えることもできます。たとえば、照合結果の印刷も組み合わせることができます。
もし、あなたがまだマクロは書けないなら、ぜひマクロを習得することをオススメします。
こちらの記事で、エクセル関数よりエクセルマクロを学んだ方がトクする理由を紹介していますので、ぜひ合わせて読んでみて下さい。
なお、この記事ではマクロのプログラムもすべて公開しています。もしあなたの仕事で使うなら、こちらの記事を参考にしてマクロを使えるように設定してみてください。
エクセルマクロで2つの表データを照合
エクセルマクロを使って、2つの表のデータを比較・照合する方法を紹介します。
ただし、この方法には欠点があります。それは、データ数があまりに増えると処理に時間がかかることです。それでも、エクセルのワークシート関数よりは効率的です。
紹介するマクロは、表1, 表2のすべての行に対し、For Next構文で比較してマッチング・照合していきます。
データ比較照合して一致したらOKを出力
・表1と表2に同じデータがある→OK
・表1にはあるが、表2にはない→空欄
マクロのコードは以下のとおりです。
Sub sample1_1() Dim hida As Long Dim migi As Long For migi = 2 To 11 For hida = 2 To 21 If Range("F" & migi).Value = Range("B" & hida).Value Then Range("C" & hida).Value = "OK" Exit For End If Next Next End Sub
データ比較照合して不一致だったらセルを黄色
・表1と表2に同じデータがある→OK
・表1にはあるが、表2にはない→セルを黄色にする
マクロのコードは以下のとおりです。
Sub sample1_2() Dim hida As Long Dim migi As Long For migi = 2 To 11 For hida = 2 To 21 If Range("F" & migi).Value = Range("B" & hida).Value Then Range("C" & hida).Value = "OK" Exit For End If Next Next For hida = 2 To 21 If Range("C" & hida).Value = "" Then Range("C" & hida).Interior.Color = vbYellow Else Range("C" & hida).Interior.Color = xlNone End If Next End Sub
エクセルマクロで別シートの2つのデータ同士を照合
上記で紹介したマクロを、別シート同士で行う場合を紹介します。
データを比較して照合｜照合済にはOKをつける
・シート「sample2_1」の表1 と シート「sample2_2」の表2に同じデータがある→OK
・シート「sample2_1」の表1 にはデータあるが、シート「sample2_2」の表2にはない→空欄
マクロのコードは以下のとおりです。
Sub sample2_1() Dim hida As Long Dim migi As Long Dim ws1 As Worksheet Dim ws2 As Worksheet Set ws1 = Worksheets("sample2_1") Set ws2 = Worksheets("sample2_2") For migi = 2 To 11 For hida = 2 To 21 If ws2.Range("A" & migi).Value = ws1.Range("B" & hida).Value Then ws1.Range("C" & hida).Value = "OK" Exit For End If Next Next End Sub
データ比較照合して不一致だったらセルを黄色
・シート「sample2_1」の表1 と シート「sample2_2」の表2に同じデータがある→OK
・シート「sample2_1」の表1 にはデータあるが、シート「sample2_2」の表2にはない→セルを黄色にする
マクロのコードは以下のとおりです。
Sub sample2_2() Dim hida As Long Dim migi As Long Dim ws1 As Worksheet Dim ws2 As Worksheet Set ws1 = Worksheets("sample2_1") Set ws2 = Worksheets("sample2_2") For migi = 2 To 11 For hida = 2 To 21 If ws2.Range("A" & migi).Value = ws1.Range("B" & hida).Value Then ws1.Range("C" & hida).Value = "OK" Exit For End If Next Next For hida = 2 To 21 If ws1.Range("C" & hida).Value = "" Then ws1.Range("C" & hida).Interior.Color = vbYellow Else ws1.Range("C" & hida).Interior.Color = xlNone End If Next End Sub
エクセルマクロで別ファイルの2つのデータ同士を照合
上記で紹介したマクロを、別ファイル同士で行う場合を紹介します。
データを比較して照合｜照合済にはOKをつける
[1] ファイルA シート「sample6」の表1
[2] ファイルB シート「sample」の表2
[1]と[2]に同じデータがある→OK
[1]にはあるが、[2]にはない→黄色
マクロのコードは以下のとおりです。
Sub sample6() Dim hida As Long Dim migi As Long Dim ws1 As Worksheet Dim ws2 As Worksheet Set ws1 = ThisWorkbook.Worksheets("sample6") Workbooks.Open Filename:=ThisWorkbook.Path & "\ファイルB.xlsm" Set ws2 = ActiveWorkbook.Worksheets("sample") For migi = 2 To 11 For hida = 2 To 21 If ws2.Range("A" & migi).Value = ws1.Range("B" & hida).Value Then ws1.Range("C" & hida).Value = "OK" Exit For End If Next Next For hida = 2 To 21 If ws1.Range("C" & hida).Value = "" Then ws1.Range("C" & hida).Interior.Color = vbYellow Else ws1.Range("C" & hida).Interior.Color = xlNone End If Next End Sub
注意点が2つあります。
[1] このマクロは、ファイルAのみ開いている状態で動かしてみてください。そうすると、ファイルBが自動で起動します。
[2] このマクロが正常に起動するには、以下の画面のように「ファイルA.xlsm」と「ファイルB.xlsm」が同じフォルダに保管されている必要があります。
複数条件でデータを照合して色付け
表1, 表2に対し、複数条件で比較してマッチング・照合していきます。
マッチングの条件
条件1：取引先毎
条件2：取引金額が10,000円以上
・2つの条件にマッチする行→OK
・2つの条件にマッチしない行→セルを黄色にする
マクロのコードは以下のとおりです。
Sub sample3() Dim hida As Long Dim migi As Long For migi = 2 To 11 For hida = 2 To 41 If Range("G" & migi).Value = Range("B" & hida).Value And Range("C" & hida).Value >= 10000 Then Range("D" & hida).Value = "OK" Exit For End If Next Next For hida = 2 To 41 If Range("D" & hida).Value = "" Then Range("D" & hida).Interior.Color = vbYellow Else Range("D" & hida).Interior.Color = xlNone End If Next End Sub
マクロで部分一致のデータを照合してカウント
表1の内、「愛」が含まれるデータの数を表2に出力します。
・表1に「愛」という文字が含まれる→OK
・表2に「愛」という文字が含まれるデータの合計数を出力
マクロのコードは以下のとおりです。
Sub sample4() Dim hida As Long Dim k As Long For hida = 2 To 41 If InStr(Range("B" & hida).Value, "愛") > 0 Then Range("C" & hida).Value = "OK" k = k + 1 End If Next Range("F2").Value = k End Sub
マクロによるデータ照合では最終行を自動取得すると便利
上記では、表1から「愛」が含まれるデータにOKを出力し、その数をカウントしました。ただし、このマクロは最終行が41行になっていますので、データ数が変わるたびに41の値を変更する必要があります。
しかし、実際に仕事では、最終行を仕事のたびに最終行の値を変更していては、面倒です。そこで、最終行を自動取得するマクロを組みます。
基本的に上記の事例で、表1で「愛」が含まれるデータの数を表2に出力します。
・表1に「愛」という文字が含まれる→OK
・表2に「愛」という文字が含まれるデータの合計数を出力(ただし、最終行を自動取得する)
マクロのコードは以下のとおりです。
以下のマクロを使います。
Sub sample5() Dim hida As Long Dim k As Long Dim cmax As Long cmax = Range("A65536").End(xlUp).Row For hida = 2 To cmax If InStr(Range("B" & hida).Value, "愛") > 0 Then Range("C" & hida).Value = "OK" k = k + 1 End If Next Range("F2").Value = k End Sub
テンプレートファイルをダウンロードしたい方はコチラから
この記事で、紹介した照合のプログラムを一から作るのは大変なのです。そこで、記事の中で紹介したマクロのコードが入ったファイルは無料でダウンロードできるようにしました。以下のフォームにメールアドレスを入力いただくと、返信メールからエクセルファイルをダウンロードできます。
ぜひご活用ください。
サンプルプログラムを上手に活用するために自力で編集できるようになろう
この記事で紹介したプログラムを利用すれば、「エクセルマクロを自分でも使える！」と思われるかもしれません。しかし、実際にあなたの仕事で応用するのは、そう簡単ではありません。なぜなら、サンプルを使いこなせるのは上級者だからです。
実際に、上級者はサンプルプログラムを読んで、その意図や意味をサッと理解して、必要な部分を書き換えて使いまわしていきます。
しかし、初心者の場合、サッと理解することができません。「何から手を付ければいいの？」、「手を加えたら、マクロが動かなくなるのでは？」と不安になります。その結果、ウェブサイトのマクロのプログラムの情報を活用できません。
私自身も、最初にマクロにチャレンジしたとき、メール送信のサンプルファイルをダウンロードしました。しかし、まったく動作せず、結局ファイルはどこかへいってしまいました。メール送信できるようになったのは、自分の実力がついた後です。
サンプルコード集を真の意味で活用するには、上級者になる必要があります。しかし、いきなり上級者になることはなかなかムズカシイです。僕も、マクロ上級者になるには、10ヶ月～1年くらいかかりました。詳しくはこちらの記事をよんでみてください。
サンプルプログラムを活用するためのコツ
サンプルプログラムを活用するためには、手書きで編集する実力を付けることがもっともいい方法です。
マクロは正しく学べば、そこまで難しくありません。とはいえ、いきなり手書きで編集するの？と思われるかもしれません。たしかに、いきなりすべてを手書きするワケではありません。
まずやるべきことは、マクロの基本を学ぶこと
まずやるべきことは、自分でマクロの基本を学ぶことです。基本が分かってしまえば、なんとなく読めるようになります。読めるようになれば、サンプルコードをコピペして、使えるようになります。どうしてもわからない部分は、ウェブで調べる。という進め方でなんとかなります。
もし、基本を習得したいなら、こちらの無料動画がオススメです。マクロの使い方から自分で編集するためのコツを紹介しています。無料で今スグ動画で勉強を始めることができます。ぜひ参考にしてみてください。
この記事を読んだ方は、こちらもオススメです。ぜひ合わせて読んでみてください。
「マクロの力を実感したい」と感じているなら、こちらの記事がオススメです。マクロが入ったエクセルファイルをダウンロードできるようにしています。もし詳細が知りたいなら、以下の記事で紹介していますので、合わせて読んでみてください。
サンプル1｜月末処理の自動処理する
サンプル2｜エクセルで在庫管理するマクロ
サンプル3｜outlookのメールを自動送信する
サンプル4｜outlookの受信メールをエクセルに一覧にして、添付ファイルも保管する
サンプル5｜インターネットに自動アクセスする
サンプル6｜ヤフオクの情報を自動取得して、エクセルに出力する
サンプル7｜エクセルの情報をワードに差し込み、印刷まで行う
ぜひご活用ください。
