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

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

■こんな人向け

  • 大容量データをCOUNTIFS関数で抽出する必要がある
  • PCスペックが低い
  • 時間を短縮したい
  • 関数を書き直すのが面倒
  • 効率化したい
スポンサーリンク

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

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

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

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

スポンサーリンク

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

今回使ったのは年齢(5歳階級),男女別人口-都道府県(大正9年~平成27年)を加工して作成したデータです。

出典:政府統計の総合窓口(e-Stat)(https://www.e-stat.go.jp/)を加工して作成

とりあえずやってみた

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

セル関数でやった場合

=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

実行した結果

出典:政府統計の総合窓口(e-Stat)(https://www.e-stat.go.jp/)を加工して作成

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

解説

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

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

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

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

ポイントとしては&が結合として使えるというところぐらいです。

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

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

【VBA】SUMIFSやCOUNTIFS、VLOOKUPを複合的に超高速化!ユーザー定義型変数を使ってみた
連想配列(Dictionary)を使うことで、計算を高速で行えるようになった頃です。いざ使ってみようと思うと、かゆいと...

自由コメント 登録不要です!

タイトルとURLをコピーしました