大体でIT

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

大体でIT

Excel VBAで、「CountIfs関数」の機能を高速化する方法について、ご紹介します。できるだけ高速化したい場合は「Dictionary」が使えます。そこそこの速度で、VBAコードは「シンプル」がいいという場合は「数式埋め込み」でCountIfs関数を使う方法が簡単です。状況に応じて使い分けていきましょう。

はじめに

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

次の4つの方法で、複数条件に一致するセルをカウントしてみます。

  • WorksheetFunctionでCountIfs関数を使う
  • 数式埋め込みでCountIfs関数を使う ←そこそこ高速でシンプル
  • ForとIfでCountIfs関数の機能を作成
  • DictionaryでCountIfs関数の機能を作成 ←かなり高速

結果は、「Dictionary」を使う方法が一番速いという結果です。

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

できるだけ高速化したい場合は「Dictionary」を使って、そこそこの速さで簡単に作成したいという場合は「数式埋め込み」でCountIfs関数を使うといいです。

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

この記事で紹介すること

  • VBAで「CountIfs関数」を高速化する方法

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

目次

複数条件に一致するセルをカウントする

複数条件に一致するセルをカウントする方法で、「CountIfs関数」を使う方法があります。

ただ、「CountIfs関数」は遅いです。

どれぐらい遅いかを次の3パターンで確認してみます。

  • 「WorksheetFunction」でCountIfs関数を使う
  • 「埋め込み数式」でCountIfs関数を使う
  • 「ForとIfを組み合わせて」複数条件に一致する値をカウント

では、やってみます。

やりたいこと

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

大量データで複数条件に一致するセルをカウント

大量データで複数条件に一致するセルをカウント

「10,001×50,001行」のデータでやってみます。

WorksheetFunctionでCountIfs関数を使ってみる

「WorksheetFunction」でCountIfs関数を使ってみます。

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

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

大量データを用意

大量データを用意

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

WorksheetFunctionでカウント

WorksheetFunctionでカウント

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

かかった時間

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

まぁ遅いですね。

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

次は、「埋め込み数式」でCountIfs関数を使ってみます。

Sub TEST2()
    
    t = Timer
    
    'CountIfs関数を埋め込む
    Range("C2:C10001") = "=COUNTIFS(E:E,A2,F:F,B2)"
    Range("C2:C10001").Value = Range("C2:C10001").Value '値に変換
    
    Debug.Print Timer - t & " 秒"
    
End Sub

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

大量データを用意

大量データを用意

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

埋め込み数式でカウント

埋め込み数式でカウント

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

かかった時間

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

先ほどのWorksheetFunctionよりは速くなりました。

ただ、まだ遅いですね。

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

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

CountIfs関数を使うと遅いなら、力技でやってみようということです。

ただ、この方法が一番遅いです。

Sub TEST3()
    
    t = Timer
    
    Dim A, B
    A = Range("A2:C10001") 'カウント元の値を取得
    B = Range("E2:F50001") 'カウント先の値を取得
    
    'カウント元をループ
    For i = 1 To UBound(A, 1)
        'カウント先をループ
        For j = 1 To UBound(B, 1)
            '2つの値が一致する場合
            If A(i, 1) = B(j, 1) And A(i, 2) = B(j, 2) Then
                A(i, 3) = A(i, 3) + 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を組み合わせて」複数条件に一致するセルをカウントできました。

かかった時間

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

これは、実務では使わない方がいいですね。

Dictionaryを使って高速化する

「Dictionary」を使って高速化してみます。

「CountIfs関数」は今のところ遅いので、高速化する場合は、「Dictionary」を使うことになります。

VBAコード

「Dictoinary」を使って「CountIfs関数」の機能を作成するとこんな感じです。

手順としては、

  • カウント元を配列に入力
  • カウント元の文字列を結合して「Dictionary」に登録
  • カウント先をループして一致する「キー」の「アイテム」をカウントアップ
  • 「アイテム」をセルに入力

という感じです。

Sub TEST4()
    
    Dim A
    '辞書を作成
    Set A = CreateObject("Scripting.Dictionary")
    
    Dim B, C
    B = Range("A2:B4") 'カウント元の値を取得
    C = Range("E2:F10") 'カウント先の値を取得
    
    'カウント元をループ
    For i = 1 To UBound(B, 1)
        'カウント元を辞書に登録
        A.Add B(i, 1) & "/" & B(i, 2), 0 '←文字列を結合するのがポイント
    Next
    
    'カウント先をループ
    For i = 1 To UBound(C, 1)
        '辞書に登録されている場合
        If A.exists(C(i, 1) & "/" & C(i, 2)) Then '←2つの文字を結合するのがポイント
            A(C(i, 1) & "/" & C(i, 2)) = A(C(i, 1) & "/" & C(i, 2)) + 1 'カウントアップ
        End If
    Next
    
    '配列をセルに入力
    Range("C2").Resize(A.Count) = WorksheetFunction.Transpose(A.items)
    
End Sub

簡単な表で実行した結果を確認してみます。

簡単な表を用意

簡単な表を用意

カウント元の文字列を「結合」して「Dictionary」に登録していきます。

カウント元を辞書に登録

カウント元を辞書に登録

カウント先をループして、一致した「キー」の「アイテム」をカウントアップしていきます。

カウント先をループして「アイテム」をカウントアップ

カウント先をループして「アイテム」をカウントアップ

複数条件に一致するセルのカウントができます。

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

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

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

これで、「Dictionary」で複数条件に一致したセルをカウントできます。

「Dictionary」で複数条件に一致したセルをカウント

「Dictionary」で複数条件に一致したセルをカウント

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

こんな感じで、「Dictionary」を使って「CountIfs関数」の機能を作成します。

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

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

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

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

大量データを用意

大量データを用意

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

「Dictionary」で複数条件に一致するセルをカウント

「Dictionary」で複数条件に一致するセルをカウント

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

かかった時間

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

かなり高速ですね。

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

実務で「CountIfs関数」の機能を高速化したい場合は、「Dictionary」です。

おわりに

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

次の4つの方法で、複数条件に一致するセルをカウントしてみました。

  • WorksheetFunctionでCountIfs関数を使う
  • 数式埋め込みでCountIfs関数を使う ←そこそこ高速でシンプル
  • ForとIfでCountIfs関数の機能を作成
  • DictionaryでCountIfs関数の機能を作成 ←かなり高速

結果は、「Dictionary」を使う方法が一番速いという結果です。

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

できるだけ高速化したい場合は「Dictionary」を使って、そこそこの速さで簡単に作成したいという場合は「数式埋め込み」でCountIfs関数を使うといいです。

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

関連する記事から探す

カテゴリから探す

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

アーカイブから探す