【ExcelVBA】Dictionaryで要素ごとのデータ数をカウントする

Dictionary(ディクショナリ：連想配列)オブジェクトを使って、
要素ごとのデータ数をカウントする方法を解説します。

Dictionaryで要素ごとのデータ数をカウント

こんな処理ですね。

Excelのシート関数で作業する場合は「重複のないリストを作ってCOUNTIF」

要素ごとにCOUNTIF

Excelの機能を使う場合は「ピボットテーブル」

ピボットテーブル

あたりでやる作業でしょうか。

こういった「要素ごとの集計」をVBAの内部で行う場合は、
Dictionaryを使うと簡単に書けて、処理速度も高速です。

汎用性が高く、応用が利く機能ですので、
是非ともマスターしておきましょう。

ソースコード
解説
汎用関数化＋汎用マクロ化
おまけ：省略記法の正確な動き

ソースコード

Sub 商品ごとのデータ数をカウントする()

    ' Keyを商品名、Itemをデータ数として使うディクショナリを作成
    Dim Dic商品リスト As New Dictionary

    Dim データ範囲 As Range
    Set データ範囲 = Worksheets("商品データ").Range("A2:A12")
    
    ' データのカウント部分
    Dim cell As Range
    For Each cell In データ範囲.Cells
        Dim key商品名 As String: key商品名 = cell.Value
    
        If Dic商品リスト.Exists(key商品名) Then
            Dic商品リスト.Item(key商品名) = Dic商品リスト.Item(key商品名) + 1
        Else
            Dic商品リスト.Add key商品名, 1
        End If
        
    Next
    
    ' 作ったリストの使用例（イミディエイトに出力するサンプル）
    Dim key
    For Each key In Dic商品リスト.Keys
        Debug.Print key, Dic商品リスト.Item(key)
    Next

End Sub

実行結果

Dictionary集計の実行結果

解説

Dictionaryに商品リストを登録/カウント

Dictionaryを使うときは、

Dim Dic商品リスト As New Dictionary ' ①

Dim Dic商品リスト As Object ' ②
Set Dic商品リスト = CreateObject("Scripting.Dictionary")

このいずれかのコードでDictionaryの変数を用意します。

①で書くには「Microsoft Scripting Runtime」の参照が必要です。
②は参照設定をしなくても書けますが、↓の入力候補が出なくなります。

Dictionaryのインテリセンス

Dictionaryはこの入力候補を使うことも大事な要素なので、
通常はしっかり参照設定して①で書きましょう。

他にも、Dic商品リスト(○○)とItemメソッドを省略した際に、
②では正常に動かないこともあります。

さて本題の「要素ごとのカウント」部分の解説に入ります。

コメントにも書いたとおり、
「Keyを商品名、Itemをデータ数として使ったDictionary」ですので、

key商品 Itemデータ数のDictionary

こんな連想配列を作っています。
それをイメージしてコードを眺めてみてください↓

Dim cell As Range
For Each cell In データ範囲.Cells
    Dim key商品名 As String: key商品名 = cell.Value

    If Dic商品リスト.Exists(key商品名) Then
        Dic商品リスト.Item(key商品名) = Dic商品リスト.Item(key商品名) + 1
    Else
        Dic商品リスト.Add key商品名, 1
    End If
    
Next

ループ分自体は基本に忠実なセル範囲のForEach文です。

Dictionaryの登録部分も読みやすい構文なのでわかりやすいですね。

keyが登録されているかをExistsメソッドで調べる
既に登録されているならItem=Item+1
まだ登録されていないならAddで追加。その時のItemは1

という手順で商品の登録/カウントを行っています。

なお、Dictionaryは「Itemが既定の(省略時の)プロパティ」ですので、

Dic商品リスト.Item(key商品名) = Dic商品リスト.Item(key商品名) + 1
' ↓
Dic商品リスト(key商品名) = Dic商品リスト(key商品名) + 1

と省略可能です。
慣れてきたらこちらでどうぞ。
※ 前述の通り、参照設定をしている時限定の書き方です。

ちなみにkey商品名という変数を用意しない場合は、

Dic商品リスト.Add cell.Value, 1

と、「必ずValueプロパティを明記」してください。

Dictionaryはkeyにオブジェクトも渡せるため、
Valueを省略するとRangeオブジェクトそのものがkeyになってうまく動きません。

そもそもkeyにしたcell.Valueは↓のコードでも何回も出てくるため、

Dic商品リスト(key商品名) = Dic商品リスト(key商品名) + 1

変数に入れてしまった方が安全で読みやすいですね。

このように、Existsによる既登録判定や、Keyを使ってのItemの呼び出しが、
「簡単に書けてしかも速い」というのがDictionaryの強みです。

特にスピードなのですが、例えば

Dic商品リスト("さくらんぼ")

↑このコードで「さくらんぼの個数を取得」している様子は、

key商品 Itemデータ数のDictionary

↑この表をVLOOKUPで探しているイメージだと理解しやすいです。

が、実際にDictionaryがやっているのはVLOOKUPのような検索ではありません。

ハッシュと呼ばれる仕組みを使って、keyからピンポイントにitemを取ってきます。

「さくらんぼ」と言われた段階で、
探すまでもなくそれが3つ目にあるとDictionaryさんは知っています。
　

Dic商品リスト(key商品名)

このコードは「keyを手掛かりにitemを探しにいく」のではなく、
「keyと紐づいたitemにピンポイントに向かう」処理です。

検索(VLOOKUP)よりはるかに超速ということは覚えておいてください。

Dictionaryは使いこなせば高速でデータのリスト化が行えるようになる上、
核となるコードは↓のように非常にわかりやすいです。

If Dic.Exists(key) Then
    Dic(key) = Dic(key) + 1
Else
    Dic.Add key, 1
End If

　
今回はCOUNTIFをやったので「+1」でしたが、

' COUNTIF
Dic商品リスト(key商品名) = Dic商品リスト(key商品名) + 1

' SUMIF
Dic商品リスト(key商品名) = Dic商品リスト(key商品名) + 商品名セル.Offset(,1).Value

とするだけで、例えば右隣の売上セルへのSUMIFもこなしてくれますし、
Itemにはセルのようなオブジェクトを入れることもできます。

速度・可読性・汎用性のそろった優秀なオブジェクトですので、
是非ともマスターしておきましょう

強力な省略記法

Dictionaryのメインメソッドたちを余すことなく使った↓のコードですが

If Dic商品リスト.Exists(key商品名) Then
    Dic商品リスト(key商品名) = Dic商品リスト(key商品名) + 1
Else
    Dic商品リスト.Add key商品名, 1
End If

これを大幅に省略する強力な短縮記法があります。

それがこちら↓

Dic商品リスト(key商品名) = Dic商品リスト(key商品名) + 1

短縮というか、2行目のコードそのままです。

要するに、

'If Dic商品リスト.Exists(key商品名) Then
    Dic商品リスト(key商品名) = Dic商品リスト(key商品名) + 1
'Else
'   Dic商品リスト.Add key商品名, 1
'End If

実はこれでも同じように動くということなんですね。

DictionaryオブジェクトのItemメソッドは、
「渡されたkeyの登録が無ければその場で作る」
という素晴らしい仕様を持っています。

要するにExistsとAddも裏でやってくれるということですね。

まさに「2行目のコードだけでOK」なのです。

この省略記法を知っていると、ただでさえ便利なDictionaryを、
即席マクロの高速コーディングにも活用できるようになります。

是非とも覚えて活用してください。

が、見てわかる通り若干可読性が落ちます。

この仕様を知らない人が見ると、突如代入が始まって「？？？」となりますし、

既登録/未登録時の処理がもっと細かい場合は、
素直にExistsで分岐した方が読みやすいことが多いです。

また、この仕様のため、

代入では意図しない新規キーの登録を検知できない
同様に意図しない既存キーへの上書きも検知できない

ことも覚えておきましょう。

特に上書きの拒否はExistsでしかできません。

強力な武器ですが諸刃の刃的な面もあるので、
上手く使っていってください。

Dictionaryで作ったリストの読み込み方

ではDictionaryで作ったリストを使うコードの解説に入ります。

コードはこちらでした↓

Dim key
For Each key In Dic商品リスト.Keys
    Debug.Print key, Dic商品リスト.Item(key)
Next

実行結果

Dictionaryの「Keys」「Items」メソッドは、
すべてのkey、itemを1次元配列にして返してくれます。

この配列生成もDictionaryのメリットですので、必ず覚えておきましょう。

とりあえず「すべてのkeyとItemの組を取り出す」場合は、
「すべてのkeyをFor Eachで回し、Item(key)でお供のItemを持ってくる」
のが基本のコードとなります。

前述の通りItemメソッドは超速ですので、
Keysをループすれば、Itemsはループする必要はありません。

この「Keys」「Items」メソッドが結構勘違いしやすいポイントで、

Dim i As Long
For i = 0 To Dic商品リスト.Count - 1
    Debug.Print Dic商品リスト.Keys(i), Dic商品リスト.Items(i)
Next

こんなコードをたまに見かけますが、これは誤りです。
　

Dic商品リスト.Keys(i)

このコードだけ見ると、一見「Dictionaryにインデックスでアクセスしている」と誤認しやすいのですが、この「i」は「Keysメソッドが作ってくれた配列の添字」です。

つまり↑の誤ったループ文では、

Debug.Print Dic商品リスト.Keys(i), Dic商品リスト.Item(i)

このコードのたびに新しく配列が作りなおされて、
膨大なメモリを使っては捨てる非常に遅いコードになりますのでご注意ください。

Dictionaryの中身をセルに出力する

Dictionaryで作ったリストをセルに出力する場合は、
keys/Itemsメソッドが1次元配列を返してくれる性質を利用できます。

' 作ったリストをセルに出力する
Dim 始点セル As Range
Set 始点セル = Worksheets("商品リスト").Range("A2")

始点セル.Resize(Dic商品リスト.Count).Value _
    = WorksheetFunction.Transpose(Dic商品リスト.Keys)
始点セル.Resize(Dic商品リスト.Count).Offset(,1).Value _
    = WorksheetFunction.Transpose(Dic商品リスト.Items)

出力するセル範囲を「始点セルからDictionaryのキー数だけResizeした範囲」でとり、そのValueプロパティに配列を代入すれば完了です。

1次元配列はそのままだと右に向かって出力されてしまうため、
シート関数「TRANSPOSE(行列の逆転)」をかませて渡すことで、
下方向に値を出力することが可能です。

このサンプルでは「始点セルから下にKeys」を、
「始点セルの右隣(Offsetで列に+1)から下にItemsを」出力していますので、

Dictionaryをセルに出力

これが出力されることになります。

このコードも非常に簡潔で読みやすい&書きやすいコードですので、
しっかり使えるようになっておきましょう。

汎用関数化＋汎用マクロ化

さて解説は以上ですが、今回作ったコードは非常に汎用性のあるコードですので、
Sub/Functionプロシージャにしておくと強力な武器になります。

コードはこんな感じです↓

Function CreateDictionary要素ごとのデータ数(データ範囲 As Range) As Object
    Set CreateDictionary要素ごとのデータ数 = CreateObject("Scripting.Dictionary")

    Dim cell As Range
    For Each cell In データ範囲.Cells
        CreateDictionary要素ごとのデータ数.Item(cell.Value) _
            = CreateDictionary要素ごとのデータ数.Item(cell.Value) + 1
    Next
End Function


Sub 要素ごとのデータ数を集計してセルに出力する(データ範囲 As Range, 出力始点セル As Range)
    Dim Dic: Set Dic = CreateDictionary要素ごとのデータ数(データ範囲)
    出力始点セル.Resize(Dic.Count).Value = WorksheetFunction.Transpose(Dic.Keys)
    出力始点セル.Resize(Dic.Count).Offset(, 1).Value = WorksheetFunction.Transpose(Dic.Items)
End Sub

省略表現も余すことなく使っているので、
簡潔で非常に短いコードですね。

ですが威力は絶大です。

Dictionaryを汎用関数化
↑冒頭の画像の緑色の部分を生成するコードが、

Call 要素ごとのデータ数を集計してセルに出力する(Range("A2:A12"), Range("C2"))

これで済むようになります。

たった1行でこの処理が書けてしまうのは、相当コーディングを楽にしてくれますね。

ちなみにセル範囲⇒セルの出力だけでいい場合は、
中身がDictionaryであることを意識する必要がありません。

他のコードでもDictionaryを使っていない場合は参照設定も不要にしたいので、
CreateObject("Scripting.Dictionary")の方法で書きました。

この様に、何度も使える処理は汎用関数にしておくと、
コードが格段に書きやすく、そして読みやすくなります。

Dictionaryを使いたい＝それくらい複雑な処理を書く必要が出た方にとって、
Dictionaryと同じくらいプロシージャ分割も重要になりますので、
こちらも一緒に勉強してみてください。

そしてこの汎用Sub/Functionプロシージャは、マクロ内で呼び出して使うだけでなく、「選択範囲に実行する便利マクロ」にもすることでさらなる力を発揮します。

例えば「選択セル範囲を要素ごとにカウントして、新しいシートに結果を出力する」マクロを、

Call 要素ごとのデータ数を集計してセルに出力する _
    (Selection, Workbooks.Add.Worksheets(1).Range("A1"))

このコードで実装できます。
これをリボンやクイックアクセスツールバーに登録しておきましょう。

こんな操作がワンクリックでできるようになります↓

Dictionaryによる要素カウントを汎用マクロ化

このマクロが日常業務ですごく便利なのは言うまでもないですが、
それがワンライナーで書けてしまうところが、汎用Sub/Functionの強さです。

他のマクロでも使いまわしたいコードを見かけたら、
「汎用関数化」⇒「便利マクロ作成」
を検討してみましょう。

以上で「Dictionaryによる要素ごとのデータ数カウント」の解説を終わります。

このように、Dictionaryオブジェクトを使うことで、
データをリスト化する処理全般が、非常に作りやすくなります。

簡単に書けてしかも速いという優秀なオブジェクトですので、
今回のようなシンプルな処理で基本の動きを覚えて、
いろいろなマクロに応用できるよう、慣れていってください。

おまけ：省略記法の正確な動き

さて、DictionaryオブジェクトのItemメソッドが、
「渡されたkeyの登録が無ければその場で作る」
という仕様であるため、

Dic商品リスト(key商品名) = Dic商品リスト(key商品名) + 1

と、省略して書けることを上記で説明しました。

この仕様の正確な説明と、ちょっとした罠の注意も記しておきます。

長くなるうえにちょっと難しいのでおまけに回しました。
余裕があれば読んでみてください。

省略表現を使ったコード

Dic商品リスト(key商品名) = Dic商品リスト(key商品名) + 1

をよく見るとわかりますが、代入をしたときに新規キーを登録しているわけではありません。

それが本当なら、代入前の右辺でエラーになりますからね。

この省略記法は代入だけでなくただ読み取るだけでもkeyを作ります。

つまり、

○○ = Dic商品リスト(key商品名) + 1

↑この右辺を先に計算した時点で、新商品のキーが登録されているということです。

そして「読み取りで作られた新キーはItemがEmpty」という仕様のため、

Dic商品リスト(key商品名) = Empty + 1

という計算を経て、
右辺での計算時に新しく作られたキーのItemが、
左辺の代入によってEmptyから1に上書きされています。

ちょっと難しい話なので簡単な例を挙げると、

Dim Dic商品リスト As New Dictionary
Debug.Print Dic商品リスト("みかん")
Debug.Print Dic商品リスト("りんご")
Debug.Print Dic商品リスト("ぶどう")

という一見意味の分からないコードで、

key	item
みかん	Empty
りんご	Empty
ぶどう	Empty

というDictionaryが出来上がるから注意してください。

という話ですね。

この挙動が罠を生むのが主にデバッグです。

「ディクショナリ内にみかんがあるか調べたい」と思ったとき、

?Dic商品リスト("みかん")

とやると、なくても作られます。

デバッグでは必ず

?Dic商品リスト.Exists("みかん")

とイミディエイトさんに聞いてください。

この様にDictionaryは渡されたkeyの登録が無ければその場で作りますが、
それは代入/読取のどちらでも作成されます。

強力な省略記法ですが、むやみやたらに乱用すると罠を生みます。
仕様をしっかり理解した上で使っていきましょう。

和風スパゲティのレシピ

日本語でコーディングするExcelVBA

Dictionaryで要素ごとのデータ数をカウントする