【超高速化】Excel VBAを使ってSUMIF＆SUMIFS関数を効率的に実行してみた 【基礎編】

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

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

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

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

WorksheetFunction​でもできますが、場合によってはセル関数でやるより重かったりしますからね、、、

VBAでSUMIF＆SUMIFS関数を高速化してみよう！

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

SUMIFもSUMIFSも大して変わらないので2条件で出す

都道府県名と大正を条件に総人口を出したいと思います。

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

セル関数で出すと

=SUMIFS(G:G,B:B,L2,D:D,M2)
これですよね。

=SUMIFS(G:G,B:B,@L:L,D:D,@M:M)
こんな風にも書けます。

実際にやってみた

Sub test()

        Dim i           As Long
        Dim n           As Long
        Dim 条件        As Variant
        Dim まとめ用    As Variant
        Dim 保存用      As Variant
        Dim Key         As String
        Dim 合計        As Long
        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 = 2 To UBound(条件) '要素数分ループさせる
                            
            Key = 条件(n, 2) & 条件(n, 4) '③これはどっちでもいいけど、わかりやすく変数に条件を結合して格納
            合計 = 条件(n, 7)

            If Not dic.Exists(Key) Then '既存のキーと同じ値がないなら
            
                dic.Add Key, 合計 'キーと数字をセットで登録
                
            Else
            
                dic(Key) = dic(Key) + 合計 'キーがすでに登録済みの場合は、合計をどんどん加算する
                
            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を使ってCONTIFS関数を超高速化！条件を＆で結合してやってみたこれとほぼ一緒です。

違いとしては、合計という変数を作って、そこに人口値を貯めています。
それくらいですかね。

これだけじゃ実務ではあんまり使えない

ユーザー定義型変数などを組み合わせることで、一気に便利になります。

↓も試してみてください。

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

[temp id=2]