重複を除いた一覧を作り、件数を数える（Dictionary の使い方）

どんな場面で使うか

売上明細から「今月取引のあった取引先の一覧」を作りたい、商品コードごとの件数や合計金額を出したい、といった場面です。Excel の「重複の削除」機能でも一覧は作れますが、元データを書き換えてしまうことや、件数・合計を同時に出せないことが不便です。

VBA の Dictionary（連想配列）を使うと、キーの重複を自動で判定しながら、キーごとに件数や合計を足し込めます。数万行でも数秒で終わります。

Dictionary の基本

Dictionary は「キー」と「値」の組を持つ入れ物です。同じキーは1つしか登録できず、Exists でキーがすでにあるかを確認できます。参照設定なしで使えるよう、CreateObject で作成します。

Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare      ' 大文字・小文字を区別しない（必要に応じて）
If Not dic.Exists("A001") Then dic.Add "A001", 0
dic("A001") = dic("A001") + 1

取引先ごとの件数と合計金額を出すコード

B列の取引先名ごとに、件数とE列の金額の合計を集計して、別シートに一覧で出力します。件数と合計の2つを持たせるため、値に要素2つの配列を入れています。

Sub SummaryByCustomer()
    Dim ws As Worksheet, outWs As Worksheet
    Dim dic As Object, data As Variant, key As Variant
    Dim lastRow As Long, i As Long, r As Long, rec As Variant

    Set ws = ThisWorkbook.Worksheets("明細")
    Set outWs = ThisWorkbook.Worksheets("取引先別")
    Set dic = CreateObject("Scripting.Dictionary")

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then Exit Sub
    data = ws.Range("A2:E" & lastRow).Value

    For i = 1 To UBound(data, 1)
        key = Trim(CStr(data(i, 2)))
        If key <> "" Then
            If dic.Exists(key) Then
                rec = dic(key)
            Else
                rec = Array(0, 0)
            End If
            rec(0) = rec(0) + 1
            If IsNumeric(data(i, 5)) Then rec(1) = rec(1) + data(i, 5)
            dic(key) = rec                    ' 配列は取り出して変更し、入れ直す
        End If
    Next i

    outWs.Cells.Clear
    outWs.Range("A1:C1").Value = Array("取引先", "件数", "合計金額")
    r = 2
    For Each key In dic.Keys
        outWs.Cells(r, 1).Value = key
        outWs.Cells(r, 2).Value = dic(key)(0)
        outWs.Cells(r, 3).Value = dic(key)(1)
        r = r + 1
    Next key
    MsgBox dic.Count & " 件の取引先を集計しました"
End Sub

つまずきやすい点

Dictionary に入れた配列は、dic(key)(0) = 値 のように直接書き換えても反映されません。この記事のコードのように、一度変数に取り出して変更し、dic(key) = rec で入れ直す必要があります。

また、キーに前後の空白が混ざっていると、見た目は同じ取引先が別々に集計されます。Trim で空白を除き、全角・半角の揺れがある場合は StrConv で統一してからキーにします。

• 配列の値は取り出して変更し、入れ直す
• キーは Trim などで表記をそろえる
• 数値と文字列の「001」と「1」は別のキーになる

出力件数が多い場合

取引先が数千件ある場合は、出力も配列にまとめて一括で書き込むとさらに速くなります。dic.Keys と dic.Items はそれぞれ配列として取り出せるので、出力用の2次元配列を作って Range に代入します。

ほかの記事