【Excel/VBA】ディクショナリとクラスを使って集計する

前回、ディクショナリを使ってSUMIF、COUNTIFみたいな集計をするというのをやりました。
【Excel/VBA】ディクショナリでCOUNTIF、SUMIFみたいな加算をやる

今回は、複数の項目を集計したい場合、クラスを利用するとさらに便利だなあと思ったので、やり方を記します。

1.そもそもクラスとは

ディクショナリとは？は前回ざっくり書きました。こんどはそもそもクラスとは？
うーーん。一言で説明できない。
モノはこれです。クラスモジュールです。

私は主に、タカハシさんの記事で勉強させていただきました。
https://tonari-it.com/excel-vba-class-property/
なんか、「クラスを使ってます」というとVBAをスマートに使いこなしている人みたいに見えそうだから、クラスを使いたくて必死に勉強しました。いろんなネットの記事を見て、でもよくわからなくて、でもよくわからないけどとにかく無理くりに使っているうちに、クラスを使わないとなんか気持ち悪い、という感じになりました。クセになる？

クラスというとオブジェクト指向のプログラミング、というのがセットで出てきます。オブジェクト指向とは？とまたそこでよくわからないのですが、のんべんだらりと記述するタイプではなく、コードをカタマリとしてとらえてあーだこーだする、みたいな感じ？（多分全然違う）

私はクラスを自分のアタマで解釈できるように、このようにとらえています。
クラスとは、設計図。
プロパティ（属性）とメソッド(処理)を持てる。
プロパティ（属性）は持ち物（？）
メソッドは〇〇ができる、△をほおり込んだら△□◇にして返す、などの処理。

VBAから外れて考えて、たとえば「人間」というクラスを作成したとします。
プロパティは「名前」「身長」「体重」があります。
メソッドは「食べる」「寝る」「走る」「歩く」を持っています。

今の状態はこうゆうものが持ててこうゆうことができるよという「設計図」なので、まだ使えません。
使おうと思ったらインスタンス化して実体化します。よくわからないのですがそのクラスをNewしてオブジェクト変数に突っ込むと使えるようになります。
クラス名が「人間」だとすると
Dim ningen As 人間
set ningen = New 人間
といった感じに書くと、使えるようになります。

プロパティを設定します。
クラスのインスタンス化した オブジェクト名.プロパティ名=〇〇と書きます。
名前を山田、身長175、体重65にします。
ningen.名前 = “山田”
ningen.身長 = 175
ningen.体重 = 65

寝る、メソッドを発動します。寝るメソッドは睡眠時間と寝る場所を引数に取ります。
8時間、ベットで寝てもらいましょう。
クラスのインスタンス化した オブジェクト名.メソッド名 引数1,引数2 のように書きます。
ningen.寝る 8, ベット

食べる、ファンクションを発動します。食べるは食べ物を消化後にう○ことして返すので、ファンクションです。引数に食品名を取ります。戻り値を受け取る場合は受け取り用変数=ファンクションとします。この場合、引数はかっこで囲むことが必要です。
u = ningen.食べる(“カレー”)

いったい私は何が言いたかったのか、よくわからなくなりました。概念としてのクラスを説明したつもりですが、小学校高学年程度のアタマで理解した「クラスの概念」なので、正確にクラスと言うものを表してはいないと思いますのでご承知おきください。

私はどのようにクラスを使っているかというとインポートして、集計して、請求書を作成するという処理で、「インポート」クラス、「集計」クラス、「請求書作成」クラスと作ってそれぞれそれ用の役割を持ってもらいます。そして標準モジュールからそれぞれのクラスを実行していきます。そうすると、モジュールがすっきりかけます。

クラスがプロパティを持っていることを利用して、変数のセット(ユーザー定義型変数、構造体みたいな)として使うこともあります。Typeでオリジナルのユーザー定義型変数を作成するのと同じ感覚ですが、Typeはモジュール内でしか使えない制約がありますが、クラスはそんなことはないので、より使いやすいです。
ユーザー定義型変数についてはこちらの記事にわかりやすく解説がありました
https://excel-ubara.com/excelvba1/EXCELVBA410.html

2.ディクショナリとクラスを使ってVBAでSUMIF、COUNTIFを実現する

さてと、だいぶわき道にそれましたが、クラスとディクショナリを使っての集計は、クラスの「プロパティ」の部分を利用します。ディクショナリにキーと値を入れていくのですが、値に「クラス」を１つずつ入れます。クラスのプロパティにはそれぞれの値を持たせることで集計が可能となります。

data2シートに社員の「研修日数」「出張日数」「休暇日数」があります。これを「集計2」シートに集計したいと思います。

オブジェクト名をClass1から「cData」に変更しました。なんでもいいです。変更しなくてもいいです。
クラスにはプロパティだけ書きます。
人数と、研修日数、出張日数、休暇日数を計算したいので、4つのプロパティを用意します。
ここではPublicで宣言します。(それが一番簡単なので)
数値を計算するのでLong型とします。

Public ninzu As Long　　　　　　'人数
Public kensyu As Long　　　　　 '研修日数
Public syuttyo As Long          '出張日数
Public kyuka As Long            '休暇日数

あとは標準モジュールでやります。

Sub test()
    Dim dic As Dictionary: Set dic = New Dictionary
    Dim i As Long, rowEnd As Long
    Dim key As String
    With Worksheets("data2")
        rowEnd = .Cells(.Rows.Count, 1).End(xlUp).Row
        For i = 2 To rowEnd
            key = .Cells(i, 1).Value
            If Not dic.Exists(key) Then
                Dim c As cData: Set c = New cData
                c.ninzu = 1
                c.kensyu = .Cells(i, 3).Value
                c.syuttyo = .Cells(i, 4).Value
                c.kyuka = .Cells(i, 5).Value
                dic.Add key, c
                Set c = Nothing
            Else
                dic(key).ninzu = dic(key).ninzu + 1
                dic(key).kensyu = dic(key).kensyu + .Cells(i, 3).Value
                dic(key).syuttyo = dic(key).syuttyo + .Cells(i, 4).Value
                dic(key).kyuka = dic(key).kyuka + .Cells(i, 5).Value
            End If
        Next i
    End With
    
    With Worksheets("集計2")
        rowEnd = .Cells(.Rows.Count, 2).End(xlUp).Row
        For i = 4 To rowEnd - 1
            key = .Cells(i, 2).Value
            If dic.Exists(key) Then
                .Cells(i, 3).Value = dic(key).ninzu
                .Cells(i, 4).Value = dic(key).kensyu
                .Cells(i, 5).Value = dic(key).syuttyo
                .Cells(i, 6).Value = dic(key).kyuka
            Else
                .Cells(i, 3).Value = 0
                .Cells(i, 4).Value = 0
                .Cells(i, 5).Value = 0
                .Cells(i, 6).Value = 0
            End If
        Next i
    End With
    
    Set dic = Nothing
End Sub

コード内にコメントを書いていないので、最初から順を追って説明します。

Dim dic As Dictionary: Set dic = New Dictionary

ディクショナリを宣言し、インスタンス化してdicにセットしました。
なお、ディクショナリを利用しやすいように Microsoft Scripting Runtime を参照設定しています。
参照設定しない場合の書き方は以下のようになります。
Dim dic As Object
Set dic = CreateObject(“Scripting.Dictionary”)

 Dim i As Long, rowEnd As Long
 Dim key As String

変数を宣言しました。
i はループするとき行カウンタで利用します。
データ最終行を代入しておくrowEnd。
keyは〇〇部を一時格納する変数として用意しておきます。文字列型の変数です

With Worksheets("data2")
    rowEnd = .Cells(.Rows.Count, 1).End(xlUp).Row
    For i = 2 To rowEnd
        key = .Cells(i, 1).Value

「data2」シートを2行目からデータの最終行まで1行ずつ処理します。
DoLoopよりForが好きなので、rowEndにデータ最終行を取得し、Forで2行目から最終行まで1行ずつ処理してきます。
まず、変数keyに部署名を代入します。2行目は営業部です。

If Not dic.Exists(key) Then
    Dim c As cData: Set c = New cData
    c.ninzu = 1
    c.kensyu = .Cells(i, 3).Value
    c.syuttyo = .Cells(i, 4).Value
    c.kyuka = .Cells(i, 5).Value
    dic.Add key, c
    Set c = Nothing

keyが最初に出てきた時の処理です。dic.Exists(key)でディクショナリにすでにあるかないかを確認し、Notを付けているのでなかった時の処理となります。

クラスcDataをインスタンス化してcにセットします。
直前にDim c As cDataと宣言していますが、変数iを宣言しているあたりでやってしまっても同じです。

新しく作成したクラスcのプロパティをセットします。
c.ninzu = 1
c.kensyu = .Cells(i, 3).Value
c.syuttyo = .Cells(i, 4).Value
c.kyuka = .Cells(i, 5).Value

これでクラスcのプロパティは「ninzu:1,kensyu:18,syuttyo:3,kyuka:2」となります。
その状態のクラスcをディクショナリdicに追加します。キーはkey「営業部」です。

cをNothingで破棄します。ここで破棄しても後で使えます。そのあたりの仕組みはよく理解していませんが。

Else
    dic(key).ninzu = dic(key).ninzu + 1
    dic(key).kensyu = dic(key).kensyu + .Cells(i, 3).Value
    dic(key).syuttyo = dic(key).syuttyo + .Cells(i, 4).Value
    dic(key).kyuka = dic(key).kyuka + .Cells(i, 5).Value
       

部署がすでにディクショナリdicに登録済の時の処理です。
営業部が二回目に出てくる10行目で考えてみましょう。

dic(“営業部”) で値の c が呼び出しされます。cはクラスです。
dic(“営業部”).ninzu はつまり c.ninzu です。登録したninzu「1」が呼び出しされます。
そこに1を足します。2になります。「2」を先ほどの dic(“営業部”).ninzu に代入しなおします。
dic(“営業部”)は　つまり c なので、c.ninzu = 2 としたことになります。

次の行も同様に考えます。
dic(“営業部”) で値の c が呼び出しされるので、dic(“営業部”).kensyu はつまり c.kensyu。登録した「18」が呼び出しされます。18に10行目の研修日数「9」を足します。18+9=27。「27」を先ほどの dic(“営業部”).kensyu に代入しなおします。
dic(“営業部”)は　つまり c なので、c.kensyu = 27 としたことになります。

そんな具合でどんどこやって、ディクショナリを完成させます。
次は書き出しです。

With Worksheets("集計2")
    rowEnd = .Cells(.Rows.Count, 2).End(xlUp).Row
    For i = 4 To rowEnd - 1
        key = .Cells(i, 2).Value
        If dic.Exists(key) Then
            .Cells(i, 3).Value = dic(key).ninzu
            .Cells(i, 4).Value = dic(key).kensyu
            .Cells(i, 5).Value = dic(key).syuttyo
            .Cells(i, 6).Value = dic(key).kyuka
        Else
            .Cells(i, 3).Value = 0
            .Cells(i, 4).Value = 0
            .Cells(i, 5).Value = 0
            .Cells(i, 6).Value = 0
        End If
    Next i
End With

「集計2」シートに書き出ししていきます。
rowENdに最終行を取得し、4行目から最終行の1行前まで1行ずつ処理していきます。

まず、変数keyにB列の部署名を代入します。最初は「営業部」です。
If dic.Exists(key) Then
で、営業部がディクショナリに存在するかどうかを確認します。あるのでこの下の処理に進みます。

dic(key).ninzu ＝＞ dic(“営業部”).ninzu 。dic(“営業部”)の値は 営業部で登録した クラス c です。ゆえに c.ninzu と同じこととなります。
営業部で登録してある c.ninzu は「5」です。なので「５」がD列に出力されます。
研修日数、出張日数、休暇日数についても同様に考えます。

Else以下にディクショナリに登録のなかった場合の処理を書いています。C～F列に「０」を出力します。

Set dic = Nothing

最後に使い終わったdicを破棄しています。

以上となります。わかりにくかったですかね？やってみると意外と簡単です。

VBAのクラスについては以下の記事もあります。

• 【Excel/VBA】クラスを使って請求書作成
• 【Excel/VBA】クラスを使ってデータの流し込み
• 【Excel/VBA】テーブルデータをもとに複数のファイル作成①～クラス　シートモジュール　ディクショナリの実験的試行錯誤
• 【Excel/VBA】テーブルデータをもとに複数のファイル作成②～クラス　シートモジュール　ディクショナリの実験的試行錯誤
• 【Excel/VBA】テーブルデータをもとに複数のファイル作成③～クラス　シートモジュール　ディクショナリの実験的試行錯誤