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関数を使ってカウントできました。
かかった時間
かかった時間は、「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関数を使ってカウントできました。
かかった時間
かかった時間は、「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を組み合わせてカウントできました。
かかった時間
かかった時間は、「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」を使って「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
