Dictionaryを用いたデータ集計 - 列ごとにDictionary作成版

Dictionary(連想配列)の活用例として、データ集計マクロを作成してみます。

以下のような「データシートから集計シートを作成する処理」を書きましょう。

◇ データシート

◇ 集計表シート

「データを商品ごと1行に集計する処理」ですので、
Key=商品ごとのItemを保持・計算できるDictionaryの十八番ですね。

さてこの集計シートをDictionaryで作るわけですが、Keyが商品は当然として、
件数・個数・売上をどのようにItemに持たせるかには様々な方法があります。

本ブログでは以下4パターンを紹介いたします。

◇ Dictionaryを列数分作成するパターン

◇ Itemに配列(Array)を格納するパターン

◇ Itemに自作クラスを格納するパターン

◇ ItemにさらにDictionaryを格納するパターン

それぞれメリット・デメリットがありますので見比べて見ていただき、
気に入った方法、使いたいマクロに合った方法を選んでお使いください。

本コードは見比べられるようにそれぞれ別ページで作成しました。

このページでは「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を列数分作成 版
Sub Dictionaryによるデータ集計_列数分Dictionaryを作成版()
    
    ' 出力列ごとにDictionaryを定義
    Dim Dic品物 As New Dictionary
    Dim Dic件数 As New Dictionary
    Dim Dic個数 As New 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品物, key品物
            Dic件数.Add key品物, 0
            Dic個数.Add key品物, 0
            Dic売上.Add key品物, 0
        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
    
    ' 各列を集計表シートへ出力
    WS集計表.Cells(R1st集計表, CNo集計表.品物).Resize(Dic品物.Count).Value = Fx.Transpose(Dic品物.Items)
    WS集計表.Cells(R1st集計表, CNo集計表.件数).Resize(Dic品物.Count).Value = Fx.Transpose(Dic件数.Items)
    WS集計表.Cells(R1st集計表, CNo集計表.個数).Resize(Dic品物.Count).Value = Fx.Transpose(Dic個数.Items)
    WS集計表.Cells(R1st集計表, CNo集計表.売上).Resize(Dic品物.Count).Value = Fx.Transpose(Dic売上.Items)
    
End Sub

' WorksheetFunctionの短縮入力
Function Fx() As WorksheetFunction
    Set Fx = WorksheetFunction
End Function

◇ ソースコード(Excelファイル)はこちら
Dictionaryによるデータ集計マクロ集.xlsm

解説

処理内容は基本に忠実な集計プログラムです。

データ全行にループを回して

件数 = 件数 + 1
個数 = 個数 + 加算する個数
売上 = 売上 + 加算する売上

この計算を行っています。

この3つの保管場所に上記4つのパターンがあり、
「3行ぞれぞれにDictionaryを用意した」バージョンが本ページですね。

Dic件数(key品物) = Dic件数(key品物) + 1
Dic個数(key品物) = Dic個数(key品物) + WSデータ.Cells(R, CNoデータ.個数).Value
Dic売上(key品物) = Dic売上(key品物) + WSデータ.Cells(R, CNoデータ.売上).Value

　
集計処理自体の解説についてはここでは割愛します。

詳しく解説を読みたい方は、こちらの記事をどうぞ。

www.limecode.jp

この方法の特徴（メリット・デメリット）

Dictionaryを列数分用意するこのパターンですが、
まずはなんといってもセルへの出力が簡単で高速なのが特長です。

他のパターンではどうしても出力時に

For Each key品物 In Dic集計表.Keys

このFor Each文を回す必要がありますが、

今回のパターンでは、

    WS集計表.Cells(R1st集計表, CNo集計表.品物).Resize(Dic品物.Count).Value = Fx.Transpose(Dic品物.Items)
    WS集計表.Cells(R1st集計表, CNo集計表.件数).Resize(Dic品物.Count).Value = Fx.Transpose(Dic件数.Items)
    WS集計表.Cells(R1st集計表, CNo集計表.個数).Resize(Dic品物.Count).Value = Fx.Transpose(Dic個数.Items)
    WS集計表.Cells(R1st集計表, CNo集計表.売上).Resize(Dic品物.Count).Value = Fx.Transpose(Dic売上.Items)

こんな風に「出力範囲.Value=Transpose(Itemの配列)」を並べるだけになります。

Rangeへのアクセスも列の数だけしか行いませんので、
出力速度は段違いにこの方法が速いです。

データ量が多く速度面が気になる場合はこのパターンでの実装をおすすめします。

また細かいですが、この方法は候補入力(インテリセンス)に優れており、
dic + Ctrl + Space でこちらのリストから入力ができます。

※ もちろん変数名をDicから始めるルールで書いた場合です

このインテリが効くため、コードの書きやすさもこの方法に軍配が上がる気がします。

総合すると、自分はこの方法が一番気に入っていますね。

反面、この方法の一番のデメリットになるのが、
「各Dictionaryの順番が保証されているわけではない」という点です。

今回は新出の商品を発見した際、
以下のように「すべてのDictionaryを同時にAdd」しているため、

If Dic品物.Exists(key品物) = False Then
    Dic品物.Add key品物, key品物
    Dic件数.Add key品物, 0
    Dic個数.Add key品物, 0
    Dic売上.Add key品物, 0
End If

　
以下の一括出力時に行のズレが発生することはありませんでした。

WS集計表.Cells(R1st集計表, CNo集計表.品物).Resize(Dic品物.Count).Value = Fx.Transpose(Dic品物.Items)
WS集計表.Cells(R1st集計表, CNo集計表.件数).Resize(Dic品物.Count).Value = Fx.Transpose(Dic件数.Items)
WS集計表.Cells(R1st集計表, CNo集計表.個数).Resize(Dic品物.Count).Value = Fx.Transpose(Dic個数.Items)
WS集計表.Cells(R1st集計表, CNo集計表.売上).Resize(Dic品物.Count).Value = Fx.Transpose(Dic売上.Items)

しかし、この「全Dictionary同時Add」に失敗した場合には、
最後に配列からセルに書き出す際にデータがズレるという最悪のバグが発生します。

今回の例だと「無償譲渡(売上が0)の時にDic売上をAddし忘れる」などで発生します。

この方法を用いる際には、Keyの登録順に細心の注意を払ってください。

その他のデメリットとしては、集計シートの列数が多いときに、

Dim Dic品物 As New Dictionary
Dim Dic件数 As New Dictionary
Dim Dic個数 As New Dictionary
Dim Dic売上 As New Dictionary

WS集計表.Cells(R1st集計表, CNo集計表.品物).Resize(Dic品物.Count).Value = Fx.Transpose(Dic品物.Items)
WS集計表.Cells(R1st集計表, CNo集計表.件数).Resize(Dic品物.Count).Value = Fx.Transpose(Dic件数.Items)
WS集計表.Cells(R1st集計表, CNo集計表.個数).Resize(Dic品物.Count).Value = Fx.Transpose(Dic個数.Items)
WS集計表.Cells(R1st集計表, CNo集計表.売上).Resize(Dic品物.Count).Value = Fx.Transpose(Dic売上.Items)

この部分が列数分必要になってしまって面倒というのがあります。

このあたりは、「実行時間≒行数：コード量≒列数」のバランスを見て、
この方法を採用するかを決めてください。

以上が「Dictionaryを列数分作成パターン」のメリット・デメリットでした。

• セルへの一括出力が簡単で高速
• インテリセンスに優れ書きやすい
• 各Dictionaryの順番が保証されない点に注意
• 列数が多いとDictionaryが増えてやや面倒

というのが特徴でしたね。

他のパターンとも見比べてみて、気に入ったものを採用してください。
www.limecode.jp
www.limecode.jp
www.limecode.jp

おまけ：WorksheetFunctionの短縮入力用関数

WorksheetFunctionを使用する際、

' WorksheetFunctionの短縮入力
Function Fx() As WorksheetFunction
    Set Fx = WorksheetFunction
End Function

この関数を用意しておくと、

こんな風にFxから関数を選べるようになります。

今回の方法で「Transposeを連打する」のにも、とても重宝しましたね。

書きやすさ、読みやすさともに大幅に向上しますので、
是非採用をご検討ください(´∀｀)
www.limecode.jp