Excel VBAを使ってCOUNTIFS関数を超高速化！条件を＆で結合してやってみた

本記事はExcel VBAを使ってCOUNTIFS関数を高速化させる方法について書いています。

連想配列(Dictionary)を使うからというか、セル作業が少ないから速い

【高速化】Excel VBAを使ってCOUNTIF関数を効率化でも書きましたが、Dictionaryオブジェクトを使います。

Dictionaryオブジェクトが速いというか、全部変数内と配列内でやっちゃうから速くて軽いです。

WorksheetFunction​のcouintifsってセル関数でやるより重かったりしますからね、、、

VBAでCOUNTIFSを高速化してみよう！

今回使ったのは年齢（５歳階級），男女別人口－都道府県（大正９年～平成27年）を加工して作成したデータです。

とりあえずやってみた

とりあえず、都道府県と元号を条件に全年齢の人口総数を出してみようと思います。
これなら結構簡単にできます。

セル関数でやった場合

=COUNTIFS(B:B,L2,D:D,M2)

こんな感じです。
簡単ですね。

Option Explicit

Sub test()

        Dim i           As Long
        Dim n           As Long
        Dim 範囲    As Variant
        Dim 条件    As Variant
        Dim 保存用      As Variant
        Dim Key         As String
        Dim MaxRowB     As Long
        Dim MaxRowL     As Long
        Dim dic         As Object
            
        MaxRowB = Cells(Rows.Count, 2).End(xlUp).Row 'B列の最終点を格納
        範囲 = Range("A1").CurrentRegion
 '①とりあえず全部配列へ
        
        MaxRowL = Cells(Rows.Count, 12).End(xlUp).Row 'L列の最終点を格納
        条件 = Range(Cells(2, 12), Cells(MaxRowL, 13)) '②数える項目を変数へ
        
        保存用 = Range(Cells(2, 14), Cells(MaxRowL, 14)) '③保存用に同サイズの空のセルを変数へ
        
        Set dic = CreateObject("Scripting.Dictionary") 'Dictionaryをオブジェクト型の変数に格納
        
        For n = 1 To UBound(範囲) '要素数分ループさせる
                            
            Key = 範囲(n, 2) & 条件(n, 4)'③これはどっちでもいいけど、わかりやすく変数に条件を結合して格納
            

            If Not dic.Exists(Key) Then '既存のキーと同じ値がないなら
            
                dic.Add Key, 1 'キーと1をセットで登録
                
            Else
            
                dic(Key) = dic(Key) + 1 'キーがすでに登録済みの場合は、アイテムを＋1
                
            End If
            
        Next n
        
        For n = 1 To UBound(条件) 'まとめるデータの要素分ループ
        
            Key = 条件(n, 1)&条件(n, 2)
            
            保存用(n, 1) = dic(Key) 'dicにkeyを入れて、アイテムを保存用に格納していく
        
        Next n
        
        Range("N2:N" & MaxRowL) = 保存用 '保存したものを一気にセルに入力
        
        Set dic = Nothing
    
    End Sub

実行した結果

まったく意味のない数字ですが、できました。

解説

【高速化】Excel VBAを使ってCOUNTIF関数を効率化これとほぼ一緒です。

変わったのは、検索条件を2つにして結合したところぐらいです。

Keyの中に北海道大正という文字を作って入れ、北海道大正というセットの数を数えているだけです。

なので、この条件を増やす、例えば北海道大正1920みたいに増やしていけば、複数条件もOKです。

個人的にはわかりにくいから好きじゃない

別記事で書きますが、あんまり好きな方法じゃないです。
シンプルで分かりやすいんですが、複雑にやろうと思うとどうやっていいのかわかりません。(教えてほしい＞＜)

https://stugori.com/user-defined-type-speeding-up/

[temp id=2]