前月の一覧と今月の一覧を並べて、増えた行、消えた行、内容が変わった行を探す。私は手作業でこれをやったことがあります。目で追うと見落としますし、「変わった」のか「消えて、別の行が増えた」のかは、キーの扱い次第で結果が変わります。この記事では、コード(キー)で突合して追加・削除・変更を色分けするVBAを作り、中級者がつまずきやすい点を実測で確かめます。
前月と今月の一覧をキーで突合して3つに分ける
判定は3つだけです。
- 前月にあって今月にない行は「削除」
- 今月にあって前月にない行は「追加」
- 両方にあって、キー以外の列が違う行は「変更」
キーは、社員コードや商品コードのように、行を決める列です。名前のように変わりうる列はキーにしません。
前月と今月の一覧を比べるコード
前月の一覧をDictionary(キー→行番号)に入れ、今月の行を1行ずつ引きます。引けなければ追加、引けたらキー以外の列を比べて変更を判定します。最後まで引かれなかった前月の行が削除です。読み込みはValue2で配列に取り、結果も配列で作って、シートには1回で書き出します。色は、そのあとで必要なセルだけに付けます。Dictionaryの存在チェックの考え方は、【VBA】マスタ参照の存在チェックをDictionaryに置き換えるでも扱っています。
Option Explicit
Private Function NormKey(ByVal v As Variant) As String
NormKey = UCase$(Trim$(StrConv(CStr(v), vbNarrow)))
End Function
Private Function SameVal(ByVal a As Variant, ByVal b As Variant) As Boolean
If IsError(a) Or IsError(b) Then
If IsError(a) And IsError(b) Then SameVal = (CStr(a) = CStr(b))
Exit Function
End If
If IsEmpty(a) Then a = ""
If IsEmpty(b) Then b = ""
If VarType(a) = vbString And VarType(b) = vbString Then
SameVal = (StrComp(a, b, vbBinaryCompare) = 0)
ElseIf VarType(a) = vbString Or VarType(b) = vbString Then
SameVal = False
Else
SameVal = (a = b)
End If
End Function
Sub CompareLists()
Const NCOLS As Long = 4
Dim wsP As Worksheet, wsC As Worksheet, wsR As Worksheet
Dim p As Variant, c As Variant, d As Object
Dim matched() As Boolean, kind() As Byte, chg() As Boolean, out() As Variant
Dim i As Long, j As Long, r As Long, k As String, pr As Long, nP As Long, nC As Long
Set wsP = Worksheets("前月")
Set wsC = Worksheets("今月")
Set wsR = Worksheets("比較結果")
p = wsP.Range("A2").Resize(wsP.Cells(wsP.Rows.Count, 1).End(xlUp).Row - 1, NCOLS).Value2
c = wsC.Range("A2").Resize(wsC.Cells(wsC.Rows.Count, 1).End(xlUp).Row - 1, NCOLS).Value2
nP = UBound(p, 1)
nC = UBound(c, 1)
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To nP
k = NormKey(p(i, 1))
If Not d.Exists(k) Then d.Add k, i
Next i
ReDim matched(1 To nP)
ReDim out(1 To nC + nP, 1 To NCOLS + 1)
ReDim kind(1 To nC + nP)
ReDim chg(1 To nC + nP, 1 To NCOLS)
For i = 1 To nC
r = r + 1
For j = 1 To NCOLS
out(r, j + 1) = c(i, j)
Next j
k = NormKey(c(i, 1))
If d.Exists(k) Then
pr = d(k)
matched(pr) = True
For j = 2 To NCOLS
If Not SameVal(p(pr, j), c(i, j)) Then
chg(r, j) = True
kind(r) = 2
End If
Next j
If kind(r) = 2 Then out(r, 1) = "変更"
Else
kind(r) = 1
out(r, 1) = "追加"
End If
Next i
For pr = 1 To nP
If Not matched(pr) Then
r = r + 1
kind(r) = 3
out(r, 1) = "削除"
For j = 1 To NCOLS
out(r, j + 1) = p(pr, j)
Next j
End If
Next pr
Application.ScreenUpdating = False
wsR.Cells.Clear
wsR.Range("A1:E1").Value2 = Array("状態", "コード", "氏名", "部署", "金額")
wsR.Columns(2).NumberFormat = "@"
wsR.Range("A2").Resize(nC + nP, NCOLS + 1).Value2 = out
For i = 1 To r
Select Case kind(i)
Case 1
wsR.Range(wsR.Cells(i + 1, 1), wsR.Cells(i + 1, NCOLS + 1)).Interior.Color = RGB(198, 239, 206)
Case 3
wsR.Range(wsR.Cells(i + 1, 1), wsR.Cells(i + 1, NCOLS + 1)).Interior.Color = RGB(217, 217, 217)
Case 2
For j = 2 To NCOLS
If chg(i, j) Then wsR.Cells(i + 1, j + 1).Interior.Color = RGB(255, 192, 0)
Next j
End Select
Next i
wsR.Columns("A:E").AutoFit
Application.ScreenUpdating = True
End Sub
このコードは、ScreenUpdating を False にして、最後に True へ戻します。途中でエラーが出て止まると、True に戻る行まで進まず、False のままになります。戻す書き方は、【VBA】エラー時も設定を元に戻す|ScreenUpdating等の後片付けにあります。
シート「前月」「今月」は1行目が見出しで、A列がキーです。結果はシート「比較結果」に出します。下の画像は、前月5行・今月5行の小さなサンプルでの結果です(A003が削除、A006が追加、A002の部署とA004の金額が変更)。

キーの型ゆれで、変更が「削除+追加」に見える
前月のキーがテキストの007、今月が数値の7だと、キーが別物になります。実測では、この組み合わせは削除1件と追加1件になりました。逆に、前月が数値の7で今月がテキストの7だと、CStrで文字列にそろえるため、同じキーとして突き合わさります。
キーの末尾の半角スペース、先頭の半角スペース、末尾の全角スペース、全角英数字(A001)、小文字(a001)も、そのまま突合すると、5通りとも削除1件と追加1件になりました。コードでは、Trim・StrConv(…, vbNarrow)・UCaseでそろえてからキーにしています(NormKey)。この5通りは、そろえると同じ行として突き合わさりました。今回の環境(日本語版Windows)では、Trimが全角スペースも除きました。
ワークシート関数で突合する場合は、動きが違います。同じ一覧で、MATCHとCOUNTIFを比べました。
- テキストの
007を数値の7で探す:MATCHは#N/A、COUNTIFは1件(一致する) - 数値の7をテキストの
"007"で探す:MATCHは#N/A、COUNTIFは1件(一致する) - 全角の
A003を半角のA003で探す:どちらも一致しない - 大文字小文字:MATCHは区別しない(
a001でA001が見つかった)
COUNTIFには、もう1つ癖があります。キーに*や?が入っていると、ワイルドカードとして働き、別のキーにも一致します(AB*Cで探すとABXCも数えて2件)。15桁を超える数字だけの文字列コードは、末尾が違っていても一致しました(12345678901234567で探して、末尾が8のコードも数えて2件)。存在チェックにCOUNTIFを使うと、こうした誤一致で「あるはずのない行」が見つかります。

変更の判定でも型を見る
セルの値をVariantのまま素の=で比べると、空白と0が同じになります。実測では、Empty = 0はTrueでした。空白から0に変わった行を、変更なしと判定してしまいます。そこで、コードではSameValを作り、空白は空文字と同じ扱い、文字列と数値は別の値、文字列どうしは大文字小文字を区別して比べました。
なお、文字列の"100"と数値の100は、素の<>でもTrue(別の値)でした。ここは素の比較でも変更として拾えます。
計算で出した数値には注意が必要です。0.1 + 0.2と0.3は、差が5.6E-17あり、素の比較でもSameValでも別の値でした。計算結果の列を比べるときは、見た目が同じ値が変更と判定される場合があります。
キーが重複しているとき
前月の一覧にキーの重複があると、Dictionary.Addは実行時エラー457になります。コードでは、最初の行だけを登録し、あとの行は登録しません。実測では、重複した2行目は突合の相手がなく、削除として出ました。今月の一覧に重複があるときは、前月の1行と2回突き合わさるだけで、追加0件のまま、重複に気づけません。突合の前に、キーの重複を別に確認しておくと安全です。Power Queryで重複を検知する方法は、【Power Query】結合で行が増える原因と重複の検知方法で扱っています。
突合と色付けの速さ
存在確認の速さを、今月の行ごとに前月にあるかを調べる片方向(追加の判定)で比べました。中央値は次のとおりです。Dictionaryは、2,000件で0.009秒、10,000件で0.037秒でした。10,000件では、MATCHの約180分の1、COUNTIFの約350分の1です。全件を探すMATCH・COUNTIFは、件数が増えるほど急に遅くなります。
| 件数 | Dictionary(作成込み) | Application.Match | CountIf |
|---|---|---|---|
| 2,000 | 0.009秒 | 0.154秒 | 0.283秒 |
| 5,000 | 0.008秒 | 0.891秒 | 1.770秒 |
| 10,000 | 0.037秒 | 6.789秒 | 13.138秒 |
他のExcelが動いていた可能性があり、絶対値は参考です。探し方の違いは、【VBA】線形探索とDictionaryの速度差を実測比較でも測っています。
色付けは、方式を4つ比べました(中央値、3回)。20,000件は追加400・変更1,300・削除400です。
| 件数 | 色なし(書き出しのみ) | 1セル・1行ずつ | Unionでまとめて | 全セルに色を書く |
|---|---|---|---|---|
| 5,000 | 0.491秒 | 0.450秒 | 0.436秒 | 1.418秒 |
| 20,000 | 1.803秒 | 1.830秒 | 1.104秒 | 5.186秒 |
必要なセルだけに色を付ける方式は、書き出しのみとほぼ同じでした。Unionは色なしより短い値が出ており、測定のばらつきの範囲なので、速いとは言えません。全セルに色を書く方式だけが、20,000件で約3.4秒増えました。色を付ける対象を絞ることが効きます。セルに触れる回数と時間の関係は、【VBA】処理が遅い本当の原因|シート操作が重くなる仕組みに書いています。
再実行したときに前回の色を残さない
結果シートに書き出す前に、Cells.Clearで全部を消します。ClearContentsだけだと、値は消えても色が残ります。差分のない一覧で再実行したところ、ClearContentsでは3セルに色が残り、Cells.Clearでは0セルでした。
まとめ
- 判定はキーで突合し、追加・削除・変更の3つに分ける。Dictionaryなら、10,000件でも0.037秒で、MATCHの約180分の1だった
- キーの型ゆれや表記ゆれは、そのままだと「削除+追加」に見える。そろえてから突合する
- COUNTIFは数値とテキストを同じに数え、ワイルドカードにも反応する。存在チェックには向かない場合がある
- キーの重複は、突合の前に別に確認する
- 色付けは必要なセルだけ。再実行には
Cells.Clear
環境はMicrosoft 365(Excel 16.0、日本語)です。Excel 2021未満・Mac・他のバージョンは未検証です。Dictionaryの仕様は、Microsoft LearnのDictionaryオブジェクトにあります。


コメント