Dictionaryを用いたデータ集計 - Itemにクラス版

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

ソースコード

' シートレイアウトの定数定義（別モジュール記載でも可）
' ※ 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

' クラスモジュール「Class集計表1行」
Public 品物 As String
Public 件数 As Long
Public 個数 As Long
Public 売上 As Long

' Dictionary ← クラス 版
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
            Dic集計表.Add key品物, New Class集計表1行
            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つのパターンがあり、
「品物,件数,個数,売上の4変数を持つクラスを使ったバージョン」が本ページですね。

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

' クラス内
Public 品物 As String
Public 件数 As Long
Public 個数 As Long
Public 売上 As Long

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

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

www.limecode.jp

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

今回はDictionaryのItemに「変数4つの自作クラス」を入れたパターンでした。
※ より正確には「変数4つの自作クラスのインスタンス」を1行ごとに入れました。

このパターンはItemに配列パターンと見比べると理解がしやすいので、
よろしければこちらのページと並べてみてみてください。
www.limecode.jp

まずこのパターンのメリット、
というよりは「配列版のデメリット」というべきかもしれませんが、
Dictionaryは「Itemの配列を直接書き替えできない」という仕様があります。

今回の処理を「Itemに配列を入れる版」で書こうとすると、
↓このように直接Itemを編集することはできません。

Dic集計表.Item(key品物)(CNo集計表.件数) = Dic集計表.Item(key品物)(CNo集計表.件数) + 1

　
配列を使う場合は、以下のように3ステップを刻む必要があり、

' 一旦配列に出す
Dim Arr: Arr = Dic集計表.Item(key品物)

' 出した配列を書き替える
Arr(CNo集計表.件数) = Arr(CNo集計表.件数) + 1

' 書き替えた配列を丸ごとItemに入れ直す
Dic集計表.Item(key品物) = Arr

こんな風に配列の1要素を変えるだけでも配列すべてを出し入れする必要があります。

今回は処理が単純なため、この配列の仕様はそこまで気になりませんが、
この対応でコードが煩雑になる場合はクラスの採用を検討してください。

他のメリットとしては、設計が明確になるというのも挙げられます。

配列を使う場合、定義上はあくまでただの1次元配列であり、

• 第1要素から順番に「品物、件数、個数、売上」として使う
• インデックス番号には出力時の列番号(CNo集計表)を併用する

というルールはあくまで人間が勝手に決めたものであるため、
これを理解したうえで、気を付けてコードを書く必要があります。

例えば、

• 出力列が「B～E列」だった時に、配列も2～5にするのか
• それとも1～4にして出力するときに1を足すのか

によって↓のコードのどちらが正しいかが変わりますからね。

Dic集計表(key品物)(CNo集計表.件数)
Dic集計表(key品物)(CNo集計表.件数 - CNo集計表.First + 1)
' ↑ どちらが正しいかをしっかり調べてからでないとコードが書けない。

出力仕様も踏まえて計算しなければいけない負担がかかります。

対してクラスの場合ははっきりと「件数、個数、売上」という変数が定義されるため、

Dic集計表(key品物).件数

という記述で件数を扱うことができます。

出力時に列番号をどうするかは出力時まで考えなくていいということですね。

このメリットもマクロが複雑化するほど恩恵が大きくなると思います。

ただし今回の場合は、実はクラスのメリットを半分しか享受できていません。

というのもDictionaryは「Itemの型を指定できない」仕様であるため、

このインテリが出ないのです。

自分は「インテリセンスこそクラスの醍醐味」と思っている節があるので、
これだとちょっと惜しい気がしてしまいますね。

続いてこのパターンのデメリットになりますが、
まずは「このままではセルに一括で出力する方法がない」というのが挙げられます。

• 「Dictionaryを列数分用意パターン」では列ごとに一括出力
• 「Itemに配列を入れるパターン」では行ごとに一括出力

が出来たのですが、このパターンでは1つずつ出力するしかありません。

すべてのセルに1回ずつアクセスすることになるため、
他パターンに比べ速度面が問題になることがあります。

この対策は単純で、以下のように一度二次元配列に入れてから出力すればよいです。

' 全Keyを集計表シートへ出力
Dim Arr出力配列()
ReDim Arr出力配列(1 To Dic集計表.Count, 1 To 4)
R = 1
For Each key品物 In Dic集計表.Keys
    Arr出力配列(R, CNo集計表.品物) = Dic集計表(key品物).品物
    Arr出力配列(R, CNo集計表.件数) = Dic集計表(key品物).件数
    Arr出力配列(R, CNo集計表.個数) = Dic集計表(key品物).個数
    Arr出力配列(R, CNo集計表.売上) = Dic集計表(key品物).売上
    R = R + 1
Next

WS集計表.Cells(R1st集計表, 1).Resize(Dic集計表.Count, 4) = Arr出力配列

気軽に高速処理できないというだけなので、致命的なデメリットではないですね。

他には、モジュールが1つ増えてしまうというのもデメリットかもしれません。

クラスの中身はこの程度↓なので準備は簡単なのですが、

Public 品物 As String
Public 件数 As Long
Public 個数 As Long
Public 売上 As Long

これだけで1モジュールになってしまうのは若干コストオーバーな気もします。

こちらも致命的なデメリットではありませんが、好みが分かれる部分ですね。

以上が「DictionaryのItemに自作クラス版メリット・デメリットでした。

• Dictionary内の要素を直接書き替えができる(Item配列版ではできない)
• 設計が明確になってコードがシンプルに
• 複雑なマクロになるほど真価を発揮
• そのままではセルへの一括出力方法がない
• 速度が気になる場合は一旦二次元配列に入れてからセルへ

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

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

おまけ：なぜType(構造体)で定義しないのか

今回のクラスは、

Public 品物 As String
Public 件数 As Long
Public 個数 As Long
Public 売上 As Long

こんな実装でしたので、この程度であれば

Type 集計表1行
    品物 As String
    件数 As Long
    個数 As Long
    売上 As Long
End Type

こんな風に「Type(構造体)」で十分じゃないか？という疑問がわきます。

が、残念ながらDictionaryのItemにはType変数を格納できません。

格納しようとすると、

コンパイル エラー:パブリック オブジェクト モジュールで定義されたユーザー定義型に限り、変数に割り当てることができ、実行時バインディングの関数に渡すことができます。

エラーが発生し、そもそもコンパイルが通らないのです。

より厳密には、Type(構造体)はVariant変数に入れることができませんので、

Dim Varinat変数
Dim Type変数 As 集計表1行
Varinat変数 = Type変数

単純なこのコードでも上記のエラーが発生します。

複数の変数をまとめて持つ」なんて聞くとTypeの出番だと思ってしまいますが、
Dictionaryとの併用はできませんので本記事のようにクラスで実装してください。