ディクショナリが好きです。
配列も好きですが、ディクショナリはキー値を探すときに配列と違ってループしなくていいから好きです。
さて、今回はエクセルのシートのデータをディクショナリを使って集計してみたら、案外簡単にできたので、備忘録としてここに記します。
１．VBAのディクショナリとは
そもそもVBAにおけるディクショナリとは。
dictionaryとは。連想配列です。キーと値をセットで格納するオブジェクトです。
連想配列とは？キーを出すと、このキーならこの値だね！と出してきてくれるので、キーから値を連想するから連想配列というのでしょうかね（想像）。
たとえば、生徒番号がキーで値が生徒氏名のディクショナリdic生徒だったら
{“1″:”山田”,”2″:”田中”,”1″:”鈴木”}
dicSeito(“1”)とすると「山田」という答えが返ってきます。
また、たとえば、dic山田というディクショナリを作ってみたとします。
{“姓”:”山田”,”名”:”太郎”,”年齢”:”20″}
といった、山田を表すディクショナリにしたりもできます。
dic山田(“年齢”)とすると「20」という答えが返ってきます。
キー値は数値でも文字列でも大丈夫のようです。ただしディクショナリから呼び出しする場合、キーが数値の場合は数値で、文字列の場合は文字列で呼び出ししないと「ないです」という答えになってしまします。
上の例でdic生徒は文字列の数字をキーにしています。その場合dic生徒(1)を数値を入れたら「そんなキーは存在しません」という答えになってしまいます。
dic生徒(Cstr(1))のように、数値を文字列変換することで、この問題は解決します。
それと、同一辞書内にキーの重複は許されません。existsメソッドでキーの存在確認ができますので、確かめてから入れたります。
ディクショナリの使い方は「VBA dictionary」で検索するとたくさん出てきます。
２．ディクショナリを使ってSUMIF、COUNTIFのような集計をする
さて、いよいよ。
dataシートに部署名と所属社員の残業時間があり、集計シートに部署ごとの残業時間と人数を集計したいと思います。普通は関数のSUMIF,COUNTIFでやっちゃいますよね。
これをわざわざ関数の代わりにVBAでディクショナリを使って集計していきます。
関数を使って集計した場合、エクセルのセルの中身を見ただけで「あーこれを集計してるのね」ってすぐわかるのが利点だと思います。しかし、ほかのセルやシートを参照しているため、集計の部分だけ切り離してどこかに移したい等というときに、参照を値貼り付けしたりする必要が出てきます。VBAで集計して値をインプットする方法は、わかりにくくはありますが、参照を気にしなくてよいので便利です。
こちらがコードです。ディクショナリを使いやすくするために、Microsoft Scripting Runtimeを参照設定しています。
ディクショナリを2つ用意し、一つの時間を合計する用、もう一つは人数をカウントする用、としています。残業時間は、ざっくりで、整数の時間です。
Sub dicsumcount()
'部署ごとの時間を合計するディクショナリ
Dim dicSum As Dictionary: Set dicSum = New Dictionary
'部署ごとの人数をカウントするディクショナリ
Dim dicCount As Dictionary: Set dicCount = New Dictionary
Dim i As Long, rowEnd As Long
Dim key As String 'キー値（部署）を一旦受け取りする変数
Dim jikan As Long '残業時間を一旦受け取りする件数
'件数、時間をディクショナリに登録する
With Worksheets("data")
'最終行をrowEndに代入
rowEnd = .Cells(.Rows.Count, 1).End(xlUp).Row
'２行目から最終行まで1行ずつ処理
For i = 2 To rowEnd
'キー値（部署）→key
key = .Cells(i, 1).Value
'時間→jikan
jikan = .Cells(i, 3).Value
'**キー値が初出の場合
If Not dicCount.Exists(key) Then
'dicSum：key=部署、値=時間 を登録
dicSum.Add key, jikan
'dicCount：key=部署、値=1 を登録
dicCount.Add key, 1
'**キー値がすでに登録されている場合
Else
'該当部署の登録されている時間を呼び出し、そこに新たに今回の時間を加算し、再度登録しなおす
dicSum(key) = dicSum(key) + jikan
'該当部署の登録されている件数を呼び出し、そこに新たに今回の1を加算し、再度登録しなおす
dicCount(key) = dicCount(key) + 1
End If
Next i
End With
'集計シートに書き出し
With Worksheets("集計")
rowEnd = .Cells(.Rows.Count, 2).End(xlUp).Row
For i = 4 To rowEnd - 1
'集計シート該当列の部署名→key
key = .Cells(i, 2).Value
'**dicCount(dicSumでもよい）に登録済の部署であれば、時間・件数をシートに印字
If dicCount.Exists(key) Then
.Cells(i, 3).Value = dicSum(key)
.Cells(i, 4).Value = dicCount(key)
'**dicCount(dicSumでもよい）に登録のない部署であれば、時間・件数は0を印字
Else
.Cells(i, 3).Value = 0
.Cells(i, 4).Value = 0
End If
Next i
End With
End Sub順を追って解説していきます。
'部署ごとの時間を合計するディクショナリ
Dim dicSum As Dictionary: Set dicSum = New Dictionary
'部署ごとの人数をカウントするディクショナリ
Dim dicCount As Dictionary: Set dicCount = New Dictionary部署ごとの時間を合計していくディクショナリdicSumを宣言し、Newで利用できるようにしています。部署ごとの人数をカウントしていくディクショナリdicCountも宣言し、Newで利用できるようにしています。
Microsoft Scripiting Runtimeを参照設定していない場合は、ディクショナリをオブジェクトで宣言しCreateObject（“Scripting.Dictionary”）でセットします。書き方は以下のようになります。
Dim dicSum As Object
Set dicSum = CreateObject(“Scripting.Dictionary”)
Dim i As Long, rowEnd As Long
Dim key As String 'キー値（部署）を一旦受け取りする変数
Dim jikan As Long '残業時間を一旦受け取りする件数変数を宣言します。iは行をカウントする変数、rowEndは最終行を格納する変数。keyは一旦部署名を受け取る変数、jikanは残業時間をいったん受け取りする変数です。
With Worksheets("data")
'最終行をrowEndに代入
rowEnd = .Cells(.Rows.Count, 1).End(xlUp).Row
'２行目から最終行まで1行ずつ処理
For i = 2 To rowEnd
'キー値（部署）→key
key = .Cells(i, 1).Value
'時間→jikan
jikan = .Cells(i, 3).Valuedataシートを2行目からデータの最後まで1行ずつ処理します。DoLoopよりForが好きなので、最初に最終行を求めて2行目から最終行までForでループします。
A列の部署名をkeyに格納します。
C列の残業時間をjikanに格納します。
最初の2行目は「営業部」時間は「20」ですね。
'**キー値が初出の場合
If Not dicCount.Exists(key) Then
'dicSum：key=部署、値=時間 を登録
dicSum.Add key, jikan
'dicCount：key=部署、値=1 を登録
dicCount.Add key, 1dictionary.Exists(キー値）はディクショナリにそのキー値が登録済かどうかを検査します。登録済の場合はTrue、登録されていない場合はFalseが返ります。
上記は If Not なので「登録されていない場合」ということになります。
「営業部」はディクショナリに登録済ではないのでここに流れます。
ディクショナリに登録するのは dictionary.Add キー, 値 と書きます。
dicSumに キー：営業部、値：20 を登録しました。
dicCountには同様にキーには「営業部」を登録します。営業部は1回目なので値には「１」を登録します。
次の行は「開発部」です。「開発部」も初めて出たのでディクショナリに登録されます。
dicSumに キー：開発部、値：35 を登録。
dicCountに キー：開発部、値「１」を登録します。
'**キー値がすでに登録されている場合
Else
'該当部署の登録されている時間を呼び出し、そこに新たに今回の時間を加算し、再度登録しなおす
dicSum(key) = dicSum(key) + jikan
'該当部署の登録されている件数を呼び出し、そこに新たに今回の1を加算し、再度登録しなおす
dicCount(key) = dicCount(key) + 1If Not なので「登録されていない場合」に当てはまらない場合（つまりすでに登録済）はElse以下の式に流れます。
4行目の「開発部」は3行目ですでに登録されているため、こちらに流れます。
ここがちょっとむずかしいのですが、dicSumの「開発部」の値を呼び出し、そこに4行目の時間を追加し、そのうえで再度dicSumの値に再代入して値を書き換えます。
わかりにくいのですが、いったんtmpjikanなどと変数に取り出してから計算しても同じです。
tmpjikan = dicSum(key) '開発部に登録された時間をtmpjikanに代入
dicSum(key) = tmpjikan + jikan ’tmpjikanに42を足してdicSum("開発部")に再代入するdicCountの方もだいたい考え方は同じです。
dicCount(“開発部”)に登録されている値は「１」。それに「１」を足して、dicCount(“開発部”)に「２」を突っ込みます。
dicCount(“開発部”) = 1[dicCount(“開発部”) ]+ 1
その次に「開発部」が出てきたらdicCount(“開発部”)に登録されている値は「2」に「１」を足して、dicCount(“開発部”)に「３」を突っ込みます。同じ部署が出てくるたびに「１」カウントアップするのでCOUNTIFと同じ動きとなります。
'集計シートに書き出し
With Worksheets("集計")
rowEnd = .Cells(.Rows.Count, 2).End(xlUp).Row
For i = 4 To rowEnd - 1
'集計シート該当列の部署名→key
key = .Cells(i, 2).Value
'**dicCount(dicSumでもよい）に登録済の部署であれば、時間・件数をシートに印字
If dicCount.Exists(key) Then
.Cells(i, 3).Value = dicSum(key)
.Cells(i, 4).Value = dicCount(key)
'**dicCount(dicSumでもよい）に登録のない部署であれば、時間・件数は0を印字
Else
.Cells(i, 3).Value = 0
.Cells(i, 4).Value = 0
End If
Next i
End WithdicSum、dicCountの中身を「集計」シートに書き出ししていきます。またForでループします。最終行は合計の行なので、最終行の1行前までのループとします。
key = .Cells(i, 2).Value
で、keyに部署名を代入します。最初の部署は「営業部」です。
If dicCount.Exists(key) Then
で、存在確認をしています。今回はすべて存在することが分かっているのでやらなくてもよいのですが念のため。もし登録されていない部署（キー値）がある場合、からっぽですよのEmptyが返ります。エラーにはならないのですが、その行は空欄になってしまうので、空欄にしたくない場合はElseで登録されてないときはどうしたいか、という処理を記述しておきます。
.Cells(i, 3).Value = dicSum(key)
.Cells(i, 4).Value = dicCount(key)
C列にdicSumの部署に対応する値を出力します。「営業部」は部の残業時間の138が出力されます。
D列にdicCountの部署に対応する値を出力します。「営業部」は部の人数（行数）5が出力されます。
Else
.Cells(i, 3).Value = 0
.Cells(i, 4).Value = 0
End If
前述しましたが、Else以下にはディクショナリに登録されている部署以外が出てきた時の対応を書いています。
ない場合はC列、D列に「０」を出力するようにしています。
集計結果はこのようになりました。心配な場合は、SUMIF、COUNTIF関数を使って集計してい見て結果が同じであることを確認すると安心です。dataシートと集計シートの合計を確認するだけでもした方がいいですね。
コメント