【VBA】前月と今月の一覧を比べて追加・削除・変更を色分け

【VBA】前月と今月の一覧を比べて追加・削除・変更を色分けのアイキャッチ画像 VBA

前月の一覧と今月の一覧を並べて、増えた行、消えた行、内容が変わった行を探す。私は手作業でこれをやったことがあります。目で追うと見落としますし、「変わった」のか「消えて、別の行が増えた」のかは、キーの扱い次第で結果が変わります。この記事では、コード(キー)で突合して追加・削除・変更を色分けする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を使うと、こうした誤一致で「あるはずのない行」が見つかります。

MATCHと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.MatchCountIf
2,0000.009秒0.154秒0.283秒
5,0000.008秒0.891秒1.770秒
10,0000.037秒6.789秒13.138秒

他のExcelが動いていた可能性があり、絶対値は参考です。探し方の違いは、【VBA】線形探索とDictionaryの速度差を実測比較でも測っています。

色付けは、方式を4つ比べました(中央値、3回)。20,000件は追加400・変更1,300・削除400です。

件数色なし(書き出しのみ)1セル・1行ずつUnionでまとめて全セルに色を書く
5,0000.491秒0.450秒0.436秒1.418秒
20,0001.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オブジェクトにあります。

コメント

タイトルとURLをコピーしました