Dictionary(連想配列)の活用例として、データ集計マクロを作成してみます。
以下のような「データシートから集計シートを作成する処理」を書きましょう。
◇ データシート
◇ 集計表シート
「データを商品ごと1行に集計する処理」ですので、
Key=商品ごとのItemを保持・計算できるDictionaryの十八番ですね。
さてこの集計シートをDictionaryで作るわけですが、Keyが商品は当然として、
件数・個数・売上をどのようにItemに持たせるかには様々な方法があります。
本ブログでは以下4パターンを紹介いたします。
◇ Dictionaryを列数分作成するパターン
◇ Itemに配列(Array)を格納するパターン
◇ Itemに自作クラスを格納するパターン
◇ ItemにさらにDictionaryを格納するパターン
それぞれメリット・デメリットがありますので見比べて見ていただき、
気に入った方法、使いたいマクロに合った方法を選んでお使いください。
本コードは見比べられるようにそれぞれ別ページで作成しました。
このページでは「ItemにもDictionaryを格納パターン」を紹介します。
◇ その他のパターンはこちら
www.limecode.jp
www.limecode.jp
www.limecode.jp
◇ ソースコード(Excelファイル)はこちら
Dictionaryによるデータ集計マクロ集.xlsm
ソースコード
' シートレイアウトの定数定義（別モジュール記載でも可） ' ※ Enumを初めて見る方はこちらの記事をどうぞ ' https://www.limecode.jp/entry/syntax/enumeration Public Const R1stデータ = 2 Public Enum CNoデータ First = 1 品物 = First 価格 個数 売上 Last = 売上 Count = Last - First + 1 End Enum Public Const R1st集計表 = 2 Public Enum CNo集計表 First = 1 品物 = First 件数 個数 売上 Last = 売上 Count = Last - First + 1 End Enum
' Dictionary ← Dictionary 版 Sub Dictionaryによるデータ集計_ItemにもDictionary版() Dim Dic集計表 As New Dictionary ' データシート全行をループ Dim R As Long For R = R1stデータ To WSデータ.Range("A1", WSデータ.UsedRange).Rows.Count Dim key品物: key品物 = WSデータ.Cells(R, CNoデータ.品物).Value ' 新出の品物をDictionaryに新規登録 If Dic集計表.Exists(key品物) = False Then Dic集計表.Add key品物, New Dictionary Dic集計表(key品物)("品物") = key品物 End If ' 各データの加算処理 Dic集計表(key品物)("件数") = Dic集計表(key品物)("件数") + 1 Dic集計表(key品物)("個数") = Dic集計表(key品物)("個数") + WSデータ.Cells(R, CNoデータ.個数).Value Dic集計表(key品物)("売上") = Dic集計表(key品物)("売上") + WSデータ.Cells(R, CNoデータ.売上).Value Next ' 全Keyを集計表シートへ出力 R = R1st集計表 For Each key品物 In Dic集計表.Keys WS集計表.Cells(R, CNo集計表.品物) = Dic集計表(key品物)("品物") WS集計表.Cells(R, CNo集計表.件数) = Dic集計表(key品物)("件数") WS集計表.Cells(R, CNo集計表.個数) = Dic集計表(key品物)("個数") WS集計表.Cells(R, CNo集計表.売上) = Dic集計表(key品物)("売上") R = R + 1 Next End Sub
◇ ソースコード(Excelファイル)はこちら
Dictionaryによるデータ集計マクロ集.xlsm
解説
処理内容は基本に忠実な集計プログラムです。
データ全行にループを回して
件数 = 件数 + 1 個数 = 個数 + 加算する個数 売上 = 売上 + 加算する売上
この計算を行っています。
この3つの保管場所に上記4つのパターンがあり、
「見出し名をKeyとしたDicitonaryを使ったバージョン」が本ページですね。
Dic集計表(key品物)("件数") = Dic集計表(key品物)("件数") + 1 Dic集計表(key品物)("個数") = Dic集計表(key品物)("個数") + WSデータ.Cells(R, CNoデータ.個数).Value Dic集計表(key品物)("売上") = Dic集計表(key品物)("売上") + WSデータ.Cells(R, CNoデータ.売上).Value
集計処理自体の解説についてはここでは割愛します。
詳しく解説を読みたい方は、こちらの記事をどうぞ。
この方法の特徴（メリット・デメリット）
今回はDictionaryのItemに「見出しがKeyのDictionary」を入れたパターンでした。
いわゆる「Dictionary In Dictionary」のサンプルコードになります。
正直なところ、今回の単純な集計処理でこれを使うメリットは特にありません。
今回は見出しが「品物・件数・個数・売上」の4列であり、
それぞれの列で行う処理も明確でしたからね。
他のパターンで扱っていたような、
- Itemに要素4つの一次元配列
- Itemに変数4つの自作クラス
- 列ごとに4つのDictionary
という設計で、十分わかりやすくコーディングができますからね。
Enumやクラス変数で定義できる各列の設定を、
わざわざ「見出し名」を文字列として使って、
Dic集計表(key品物)("件数") = Dic集計表(key品物)("件数") + 1
こう組む必要は特にありません。
ということで、今回の処理では無用の長物感があるDic In Dicですが、
もちろんこの設計が力を発揮する場面はあります。
例えば集計シートが「列数も動的にする」クロス集計だった場合↓
この場合はデータ値を列見出しとして出力する必要があるため、
Keyとして活用するこの使い方とうまくマッチして扱いやすくなります。
クロス集計などのように、
- キーとなる変数が2つある処理
- 2次元の処理だが、2次元目の構造がデータごとに一定ではない
ような処理を行う際は、Dictionary In Dictionary の利用を考えてみて下さい。
DicitonaryにはAddなしでKeyを生成できる仕様がある
この使い方の利便性を高めているのがDictionaryの面白い特長である、
「Itemを指定されたとき、そのKeyがなかったらその場で作る」仕様です。
今回のコードでも、Dicitonary内のDictionaryを新規登録する際は、
Dic集計表.Add key品物, New Dictionary
これしか書いていませんでした。
なんとなく↓の初期化が必要そうですが特に行っていません。
Dic集計表(key品物).Add "品物", "" Dic集計表(key品物).Add "件数", 0 Dic集計表(key品物).Add "個数", 0 Dic集計表(key品物).Add "売上", 0
にもかかわらず、件数などの集計コードでいきなり
Dic集計表(key品物)("件数") = Dic集計表(key品物)("件数") + 1
これを書いていますが、問題なく動いているのがわかります。
これが(良し悪しありますが)DicitonaryのItemプロパティの特長で、
If Dic.Exists(key) = False Then Dic.Add key, Empty End If ' ↓ 実は同じ処理になる Debug.Print Dic(key) ' ← 呼ばれた瞬間に中身EmptyでItemを作る
こんな仕様になっています。
これがクロス集計のような処理ととても相性が良く、
- 列見出しリストを先に作らなくても、適当に代入していけば勝手にできる
ようなマクロの組み方ができます。
Dictionaryを活用する際、この仕様を知っているとスッキリ書ける場面も増えますし、
逆に知らないと予期せぬ落とし穴に落ちることもあります。
- 生成と代入を同時にやりたいならAddは不要
- 存在確認をやりたいならExistsメソッド
という使い分けを、しっかり覚えておいてください。
ということで少し横道にそれましたが、
以上で「DictionaryのItemにもDicitonary版」の解説を終わります。
- 本来はもっと複雑なマクロで真価を発揮する方法
- クロス集計などのキーとなる変数が2つある処理が得意
というのが特徴でしたね。
他のパターンとも見比べてみて、気に入ったものを採用してください。
www.limecode.jp
www.limecode.jp
www.limecode.jp
