【高速化】Excel VBAを使ってCOUNTIF関数を効率化

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

■こんな人向け

• 大容量データをCOUNTIF関数で抽出する必要がある
• PCスペックが低い
• 時間を短縮したい

連想配列(Dictionary)を使う

今回はDictionaryオブジェクトを使って高速化します。

あれ、COUNTIFは？と普通は思いますよね。

COUNTIF関数って、単純に何か条件、たとえば”佐藤”という項目がどれだけあるか数えたいときに使いますよね。
つまり、COUNTIF関数　=　特定の項目を数える機能

この特定の項目を数える機能をDictionaryオブジェクトで代わりにやるというわけです。

Dictionaryオブジェクトとは、キーとデータをセットで格納するできるオブジェクトです。
イメージとしては、リンゴ→赤、バナナ→黄、みかん→黄、こんな感じで連想ゲームみたいに2つのセットの情報を保存する手段です。
重複ができないという特徴があり、今回はそれを利用します。(リンゴ→赤、リンゴ→緑は同時に成り立たない)

セル計算が少ないから速い

EXCELはセル操作を行うと遅くなりがちです。
いくらVBAを使ったとしても、1つ1つセルに記入したり、消したりしてたら、それはPCスペック次第でどれだけPCが人間より速く動けるかって話になります。

場合寄っては固まったりしてしまいます。

なので、セル操作ではなく、変数内や配列内で操作を行うと、表向き作業は行われず、すべてデータ上で処理ができるので速いのです。
すべて計算し終わってからまとめてセルに入力します。

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

文字で見てもわかりにくいので実際に書いてみました。
標準モジュールでOKです。

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

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(Cells(2, 2), Cells(MaxRowB, 2)) '①B列の見出し以外を格納
        
        MaxRowL = Cells(Rows.Count, 12).End(xlUp).Row 'L列の最終点を格納
        まとめ用 = Range(Cells(2, 12), Cells(MaxRowL, 12)) '②数える項目を格納
        
        保存用 = Range(Cells(2, 13), Cells(MaxRowL, 13)) '③保存用に空のセルを格納
        
        Set dic = CreateObject("Scripting.Dictionary") 'Dictionaryをオブジェクト型の変数に格納
        
        For n = 1 To UBound(都道府県) '要素数分ループさせる
                            
            Key = 都道府県(n, 1) '③これはどっちでもいいけど、わかりやすく変数に格納
            

            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, 1) = dic(Key) 'dicにkeyを入れて、アイテムを保存用に格納していく
        
        Next n
        
        Range("M2:M" & MaxRowL) = 保存用 '保存したものを一気にセルに入力
        
        Set dic = Nothing
    
    End Sub

無意味な計算ですが、すべて376個という結果がでました。(沖縄だけ371)

これくらいのデータ量だと、正直関数でいい気もしますね。
PCが低スペックだとかそういう場合に使ってみてください。

解説

重複を確認する

まずKeyという変数に都道府県（n,1）を入れています。
都道府県という配列には、B2から最終行までを格納しているため、例えば都道府県（1,1）はB2になります。

つまり、（2,1）はB3、（3,1）はB4となりますよね。

Keyには毎回、北海道だとか沖縄とかそういった都道府県名がはいります。

If Not dic.Exists(Key)は、dicという連想配列の中にKey、つまりその都道府県名があるかないか調べているのです。

なかったら、都道府県名と数字の1をセットで登録。
北海道,1みたいに格納されます。

重複してたら＋１

もし既に登録済みだったら

dic(Key)というのは、Keyとセットになったものを呼び出しています。
dic(北海道)であれば1みたいな感じです。

dic(北海道)＝dic(北海道)＋１
こうすることで、さっきまであった連想配列、北海道,1、を北海道,２に変えることができます。

これを最後の行まで繰り返すことで、最終的に北海道,376という連想配列を作っていきます。

保存用にまとめる

上記のループが完了すると、dic内には北海道,376とか岩手,376などが保管されています。

あとは出力するだけなんですが、これも一応配列でやっています。

Keyには北海道から沖縄まで順番に並んだL列を、上から順に入れていきます。

n=1であれば
保存用(1,1) = dic(北海道)
→dic(北海道)は376なので、保存用(1,1)には376が保管されます。

これを要素数分繰り返すわけです。

そして最後にセルに一気に吐き出して完了です。

最後は配列に入れなくてもいい

↑のようにしていましたが

  For n = 1 To UBound(まとめ用) 'まとめるデータの要素分ループ
        
            Key = まとめ用(n, 1)
            
            Cells(n + 1, 13) = dic(Key) 'dicにkeyを入れて、アイテムを保存用に格納していく
        
        Next n

こうやって直接代入してもOKです。

おわり

コードが長いですよね。

これくらいだとそこまでメリットないですが、大容量データだったり、ほかにも組み合わせて使うと便利です。

[temp id=2]