Dictionary(連想配列)の活用例として、データ集計マクロを作成してみます。
以下のような「データシートから集計シートを作成する処理」を書きましょう。
◇ データシート
◇ 集計表シート
「データを商品ごと1行に集計する処理」ですので、
Key=商品ごとのItemを保持・計算できるDictionaryの十八番ですね。
さてこの集計シートをDictionaryで作るわけですが、Keyが商品は当然として、
件数・個数・売上をどのようにItemに持たせるかには様々な方法があります。
本ブログでは以下4パターンを紹介いたします。
◇ Dictionaryを列数分作成するパターン
◇ Itemに配列(Array)を格納するパターン
◇ Itemに自作クラスを格納するパターン
◇ ItemにさらにDictionaryを格納するパターン
それぞれメリット・デメリットがありますので見比べて見ていただき、
気に入った方法、使いたいマクロに合った方法を選んでお使いください。
本コードは見比べられるようにそれぞれ別ページで作成しました。
このページでは「Itemに配列を格納パターン」を紹介します。
◇ その他のパターンはこちら
www.limecode.jp
www.limecode.jp
www.limecode.jp
◇ ソースコード(Excelファイル)はこちら
Dictionaryによるデータ集計マクロ集.xlsm
- ソースコード
- 解説
- ！注意！Dictionary内の配列は要素の書き換えが出来ない
- この方法の特徴（メリット・デメリット）
- 高速化したい場合は一度二次元配列に
- おまけ：配列のインデックスと出力セルの列番号を合わせる
ソースコード
' シートレイアウトの定数定義（別モジュール記載でも可） ' ※ 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 ← 配列(Array) 版 Sub Dictionaryによるデータ集計_Itemに配列版() 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 ReDim Arr登録用(1 To 4) Arr登録用(CNo集計表.品物) = key品物 Dic集計表.Add key品物, Arr登録用 End If ' Dictionary内の配列は要素の書き換えが出来ないため、書換用に一時配列を作成 Dim Arr: Arr = Dic集計表(key品物) ' 各データの加算処理 Arr(CNo集計表.件数) = Arr(CNo集計表.件数) + 1 Arr(CNo集計表.個数) = Arr(CNo集計表.個数) + WSデータ.Cells(R, CNoデータ.個数).Value Arr(CNo集計表.売上) = Arr(CNo集計表.売上) + WSデータ.Cells(R, CNoデータ.売上).Value ' Dictionary内の配列を更新 Dic集計表(key品物) = Arr Next ' 全Keyを集計表シートへ出力 R = R1st集計表 For Each key品物 In Dic集計表.Keys WS集計表.Cells(R, 1).Resize(, 4).Value = Dic集計表(key品物) R = R + 1 Next End Sub
◇ ソースコード(Excelファイル)はこちら
Dictionaryによるデータ集計マクロ集.xlsm
解説
処理内容は基本に忠実な集計プログラムです。
データ全行にループを回して
件数 = 件数 + 1 個数 = 個数 + 加算する個数 売上 = 売上 + 加算する売上
この計算を行っています。
この3つの保管場所に上記4つのパターンがあり、
「品物,件数,個数,売上の要素数4の配列を使ったバージョン」が本ページですね。
Arr(CNo集計表.件数) = Arr(CNo集計表.件数) + 1 Arr(CNo集計表.個数) = Arr(CNo集計表.個数) + WSデータ.Cells(R, CNoデータ.個数).Value Arr(CNo集計表.売上) = Arr(CNo集計表.売上) + WSデータ.Cells(R, CNoデータ.売上).Value
集計処理自体の解説についてはここでは割愛します。
詳しく解説を読みたい方は、こちらの記事をどうぞ。
！注意！Dictionary内の配列は要素の書き換えが出来ない
今回のようにDictionaryに配列を入れる場合に注意が必要なのが、
Dictionary内の配列は要素の書き換えが出来ないことです。
今回のコードで、
' Dictionary内の配列は要素の書き換えが出来ないため、書換用に一時配列を作成 Dim Arr: Arr = Dic集計表(key品物) ' 各データの加算処理 Arr(CNo集計表.件数) = Arr(CNo集計表.件数) + 1 Arr(CNo集計表.個数) = Arr(CNo集計表.個数) + WSデータ.Cells(R, CNoデータ.個数).Value Arr(CNo集計表.売上) = Arr(CNo集計表.売上) + WSデータ.Cells(R, CNoデータ.売上).Value ' Dictionary内の配列を更新 Dic集計表(key品物) = Arr
このように実装していたように、
- Dictionaryとは別に配列変数を用意して一旦そこに出力
- その配列変数を書き替え（今回は加算処理）
- 出来上がった配列を丸ごとItemに入れて差し替え
この手順で実行する必要があります。
これを
' 各データの加算処理 Dic集計表(key品物)(CNo集計表.件数) = Dic集計表(key品物)(CNo集計表.件数) + 1 Dic集計表(key品物)(CNo集計表.個数) = Dic集計表(key品物)(CNo集計表.個数) _ + WSデータ.Cells(R, CNoデータ.個数).Value Dic集計表(key品物)(CNo集計表.売上) = Dic集計表(key品物)(CNo集計表.売上) _ + WSデータ.Cells(R, CNoデータ.売上).Value
このようにDictionary内の配列を直接編集してしまうと、
こんな風に最初にItemに入れたままの状態でマクロが終了してしまいます。
Dictionaryと配列を併用する際には、この仕様には十分注意しておきましょう。
この方法の特徴（メリット・デメリット）
今回はDictionaryのItemに「集計シート1行分の配列」を入れたパターンでした。
この画像を見るだけでもなんとなく仕様がわかったんじゃないかと思いますが、
この「設計がわかりやすい」というのが一番のメリットですね。
配列を直接書き替えることができればより良かったのですが、
この仕様はどうしようもありませんので我慢しましょう。
出力コードも単純明快なコードになり、
R = R1st集計表 For Each key品物 In Dic集計表.Keys WS集計表.Cells(R, 1).Resize(, 4).Value = Dic集計表.Item(key品物) R = R + 1 Next
このように配列⇒セルのお手本のようなコードで実装できます。
各機能の基本形コードを組み合わせて作れますので、
本コードを採用せずとも、勉強がてら一度書いてみてもいいかもしれませんね。
本パターンのデメリットとしては、
前述の「直接編集不可」を忘れると不具合になってしまう点と、
それに対応すべく配列を再生成する分、多少なり実行速度が落ちている点です。
といってもDictionaryのItem取得速度が超速であるため、
シート上でCOUNTIFやSUMIFを計算させるよりは圧倒的に早いです。
※ DictionaryがKeyからItemを持ってくる処理は、
検索ではなく「ハッシュ計算」で行われています。
なんとなくVLOOKUPに似ている処理をしていますが、
実際の速度は比較にならないくらい高速になります。
実行速度は「遅かった時に考える」くらいでいいと思いますので、
使ってみて、気になるようなら改修を考えてみてください。
高速化したい場合は一度二次元配列に
速度面の影響でいうと、配列の生成よりも「セルへのアクセス回数」が重要です。
高速化したい場合は、一次元配列を行数分セルに出力していた部分を、
一旦二次元配列にいれて、それをセルに一括出力するコードにして下さい。
' 全Keyを集計表シートへ出力 R = R1st集計表 For Each key品物 In Dic集計表.Keys WS集計表.Cells(R, 1).Resize(, 4).Value = Dic集計表.Item(key品物) R = R + 1 Next ' ↓ Dictionary.Itemを直接セルに書き出さず、一旦2次元配列に入れてからあらためてセルへ出力するコードへ書き替え ' 全Keyを集計表シートへ出力 ReDim Arr出力用(1 To Dic集計表.Count, 1 To 4) R = 1 For Each key品物 In Dic集計表.Keys Dim C As Long For C = 1 To 4 Arr出力用(R, C) = Dic集計表.Item(key品物)(C) Next R = R + 1 Next WS集計表.Cells(R1st集計表, CNo集計表.First).Resize(Dic集計表.Count, 4) = Arr出力用
これで数倍は速くなるはずです。
もし速度面で気になるようなら、こちらのコードを使用してください。
以上が「DictionaryのItemに配列を格納版」のメリット・デメリットでした。
- Key(商品)ごとに一次元配列を持つという設計がわかりやすい
- ただし中身の配列を直接書き替えられないことに注意
- 速度はそこそこなので、もし遅かったら二次元配列を経由してセルへ
というのが特徴でしたね。
他のパターンとも見比べてみて、気に入ったものを採用してください。
www.limecode.jp
www.limecode.jp
www.limecode.jp
おまけ：配列のインデックスと出力セルの列番号を合わせる
本筋とは逸れてしまうので末尾にて。
本コードで登場したDictionaryへの新規登録部分↓
' 新出の品物をDictionaryに新規登録 If Dic集計表.Exists(key品物) = False Then ReDim Arr登録用(1 To 4) Arr登録用(CNo集計表.品物) = key品物 Dic集計表.Add key品物, Arr登録用 End If
この部分は、以下のように簡単に書くことができます。
If Dic集計表.Exists(key品物) = False Then Dic集計表.Add key品物, Array(key品物, 0, 0, 0) End If
Array関数による配列の一発生成ですね。
これは確かに便利なのですが、これだと「配列の初項が0になる」ため、
加算処理を行うコートが以下の通り煩雑になってしまいます。
' 各データの加算処理 Arr(CNo集計表.件数 - 1) = Arr(CNo集計表.件数) + 1 Arr(CNo集計表.個数 - 1) = Arr(CNo集計表.個数) + WSデータ.Cells(R, CNoデータ.個数).Value Arr(CNo集計表.売上 - 1) = Arr(CNo集計表.売上) + WSデータ.Cells(R, CNoデータ.売上).Value
今回は集計表がA1始まりのためまだ「-1」で済んでいますが、
実際はこの値もシートレイアウトに合わせて考える必要があります。
これではいつかバグを書いてしまいそうですよね。
登録時にシートレイアウトに合わせて配列を定義するだけで、
後のコードが単純明快でメンテナブルに組むことができます。
ここはサボらずに定義しておきましょう。
なお、同じく本筋と逸れるため視認性を重視して書いていませんでしたが、
せっかくEnumでシートレイアウトを定義したなら以下のように書くのがベストです。
ReDim Arr登録用(1 To 4) ' ↓ 書き替え ReDim Arr登録用(CNo集計表.First To CNo集計表.Last)
WS集計表.Cells(R, 1).Resize(, 4).Value = Dic集計表.Item(key品物) ' ↓ 書き替え WS集計表.Cells(R, CNo集計表.First).Resize(, CNo集計表.Count).Value = Dic集計表.Item(key品物)
こうしておけば、集計表がC4スタートでも(3 To 6)で配列が定義され、
以降のコードで列番号と配列インデックスのズレを考える必要がなくなります。
このあたりの計算は混乱しやすい部分ですので、
自分の脳みそを気遣ったコードにしてあげましょう。
