大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAで、CountIf関数の機能を高速化する方法をご紹介します。結論から言うと、「Dictionary」を使えばかなり高速化することができます。VBAコードをシンプルにしたい場合は「数式を埋め込む」方法がそこそこ高速で、シンプルです。

はじめに

この記事では、「CountIf関数」の機能を高速化する方法について、ご紹介します。

次の4つの方法で、比較してみます。

  • WorksheetFunctionでCountIf関数を使う
  • 数式埋め込みでCountIf関数を使う ←そこそこ速くでシンプル
  • ForとIfを組み合わせて条件一致のセルをカウント
  • Dictionaryで条件一致のセルをカウント ←かなり高速

「Dictionary」を使えばかなり高速に条件一致のセルをカウントできます。

「数式を埋め込む方法」は、そこそこ速くて、VBAコードがシンプルです。

高速に処理したい場合は「Dictionary」で、VBAコードをシンプルにしたい場合は「数式埋め込み」を使うといいです。

では、「CountIf関数」の機能を高速化する方法について、解説していきます。

この記事で紹介すること

  • 「CountIf関数」の機能を高速化する方法

目次から見たい項目へ移動すると便利ですよ。

目次

条件に一致する値をカウント

条件に一致する値をカウントを、次の3の方法でやってみます。

  • WorksheetFunctionでCountIf関数を使う
  • 数式を埋め込んでCountIf関数を使う
  • ForとIfを組み合わせて条件一致のセルをカウント

CountIf関数はちょっと遅いです。

さらにForとIfの組み合わせで力技でやるとさらに遅くなります。

どれぐらい遅いかを確認してみます。

やりたいこと

やりたいことは、大量のデータで、条件に一致したセルをカウントする、ということです。

条件に一致したセルをカウントしたい

条件に一致したセルをカウントしたい

カウント元が「10,001行」で、カウント先が「50,001行」でやってみます。

WorksheetFunctionでCountIf関数を使ってみる

WorksheetFunctionでCountIf関数を使って、条件一致のセルをカウントしてみます。

VBAコードは、こんな感じになります。

Sub TEST1()
    
    t = Timer
    
    Dim A
    'カウント用の値を取得
    A = Range("A2:B10001")
    
    '商品をループ
    For i = 1 To UBound(A, 1)
        '条件に一致する値をカウント
        A(i, 2) = WorksheetFunction.CountIf(Range("D:D"), A(i, 1))
    Next
    
    '配列をセルに入力
    Range("A2").Resize(UBound(A, 1), UBound(A, 2)) = A
    
    Debug.Print Timer - t & " 秒"
    
End Sub

大量データを用意します。

大量データを用意

大量データを用意

では、VBAコードを実行してみます。

WorksheetFunctionでCountIf関数を使ってカウント

WorksheetFunctionでCountIf関数を使ってカウント

WorksheetFunctionでCountIf関数を使ってカウントできました。

かかった時間

かかった時間は、「56.19922 秒」となりました。

まぁ遅いですね。

埋め込み数式でCountIf関数を使ってみる

次は、「埋め込み数式」でCountIf関数を使ってカウントしてみます。

Sub TEST2()
    
    t = Timer
    
    'CountIf関数を埋め込む
    Range("B2:B10001") = "=COUNTIF(D:D,A2)"
    Range("B2:B10001").Value = Range("B2:B10001").Value '値に変換
    
    Debug.Print Timer - t & " 秒"
    
End Sub

大量データを用意しておきます。

大量データを用意

大量データを用意

では、VBAコードを実行してみます。

埋め込み数式でCountIf関数を使ってカウント

埋め込み数式でCountIf関数を使ってカウント

「埋め込み数式」でCountIf関数を使ってカウントできました。

かかった時間

かかった時間は、「21.88281 秒」となりました。

WorksheetFunctionよりは速くなりました。

VBAコードもシンプルです。

でも、まだ遅いです。

ForとIfを組み合わせて条件に一致する値をカウント

次は、「ForとIfを組み合わせて」条件に一致する値をカウントしてみます。

これが一番遅いですね。

VBAコードは、こんな感じになります。

Sub TEST3()
    
    t = Timer
    
    Dim A, B
    A = Range("A2:B10001") 'カウント元の値を取得
    B = Range("D2:D50001") 'カウント先の値を取得
    
    'カウント元をループ
    For i = 1 To UBound(A, 1)
        'カウント先をループ
        For j = 1 To UBound(B, 1)
            '値が一致する場合
            If A(i, 1) = B(j, 1) Then
                A(i, 2) = A(i, 2) + 1 'カウントアップ
            End If
        Next
    Next
    
    '配列をセルに入力
    Range("A2").Resize(UBound(A, 1), UBound(A, 2)) = A
    
    Debug.Print Timer - t & " 秒"
    
End Sub

大量データを用意しておきます。

大量データを用意

大量データを用意

VBAコードを実行します。

ForとIfを組み合わせてカウント

ForとIfを組み合わせてカウント

ForとIfを組み合わせてカウントできました。

かかった時間

かかった時間は、「116.9961 秒」となりました。

かなり遅くなりました。

ループにループを重ねると遅くなっちゃいます。

Dictionaryを使って高速化する

CountIf関数の機能を、「Dictionary」を使って高速化します。

VBAコード

CountIf関数の機能を「Dictionary」で作成してみます。

手順としては、

  • カウント元の値を配列に入力
  • カウント元の値を「Dictionary」に登録
  • カウント先をループして「アイテム」をカウントアップしていく
  • 「アイテム」をセルに入力

という流れです。

Sub TEST4()
    
    Dim A
    '辞書を作成
    Set A = CreateObject("Scripting.Dictionary")
    
    Dim B, C
    B = Range("A2:A4") 'カウント元の値を取得
    C = Range("D2:D10") 'カウント先の値を取得
    
    'カウント元をループ
    For i = 1 To UBound(B, 1)
        A.Add B(i, 1), 0 'カウント元を辞書に登録
    Next
    
    'カウント先をループ
    For i = 1 To UBound(C, 1)
        '辞書に登録されている場合
        If A.exists(C(i, 1)) Then
            A(C(i, 1)) = A(C(i, 1)) + 1 'カウントアップ
        End If
    Next
    
    '配列をセルに入力
    Range("B2").Resize(A.Count) = WorksheetFunction.Transpose(A.items)
    
End Sub

簡単な表を使って、実行結果をみてみます。

簡単な表を使う

簡単な表を使う

カウント元の値を「辞書」に登録していきます。

辞書に登録

辞書に登録

カウント先の商品をループしていきます。

商品のキーで「辞書」を検索して、「アイテム」をカウントアップしていきます。

商品をループしてカウント

商品をループしてカウント

「アイテム」をセルに入力します。

「アイテム」をセルに入力

「アイテム」をセルに入力

これで、条件一致のセルをカウントできます。

「Dictionary」で条件一致のセルをカウント

「Dictionary」で条件一致のセルをカウント

「Dictionary」で条件一致のセルをカウントできました。

こんな感じで「Dictionary」を使って「CounIf関数」の機能を作成することができます。

大量データで計測してみる

では、大量データで計測してみます。

Sub TEST5()
    
    t = Timer
    
    Dim A
    '辞書を作成
    Set A = CreateObject("Scripting.Dictionary")
    
    Dim B, C
    B = Range("A2:A10001") 'カウント元の値を取得
    C = Range("D2:D50001") 'カウント先の値を取得
    
    'カウント元をループ
    For i = 1 To UBound(B, 1)
        A.Add B(i, 1), 0 'カウント元を辞書に登録
    Next
    
    'カウント先をループ
    For i = 1 To UBound(C, 1)
        '辞書に登録されている場合
        If A.exists(C(i, 1)) Then
            A(C(i, 1)) = A(C(i, 1)) + 1 'カウントアップ
        End If
    Next
    
    '配列をセルに入力
    Range("B2").Resize(A.Count) = WorksheetFunction.Transpose(A.items)
    
    Debug.Print Timer - t & " 秒"
    
End Sub

大量データを用意しておきます。

大量データを用意

大量データを用意

では、VBAコードを実行してみます。

「Dictionary」でCountIfを高速化

「Dictionary」でCountIfを高速化

「Dictionary」で条件一致したセルをカウントできました。

かかった時間

かかった時間は、「0.203125 秒」です。

一番速かったのが、「数式埋め込み」の方法で「20 秒」ぐらいでしたので、かなり速くなっています。

CountIf関数の機能を高速化したい場合は、「Dictionary」を検討する価値ありです。

おわりに

この記事では、「CountIf関数」の機能を高速化する方法について、ご紹介しました。

次の4つの方法で、比較してみました。

  • WorksheetFunctionでCountIf関数を使う
  • 数式埋め込みでCountIf関数を使う ←そこそこ速くでシンプル
  • ForとIfを組み合わせて条件一致のセルをカウント
  • Dictionaryで条件一致のセルをカウント ←かなり高速

「Dictionary」を使えばかなり高速に条件一致のセルをカウントできます。

「数式を埋め込む方法」は、そこそこ速くて、VBAコードがシンプルです。

高速に処理したい場合は「Dictionary」で、VBAコードをシンプルにしたい場合は「数式埋め込み」を使うといいです。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

アーカイブから探す