Sub TEST1()
t = Timer
Dim A, B
A = Sheets("Sheet3").Range("B1:K1") '列見出しを配列に格納
B = Sheets("Sheet3").Range("A2:A1001") '行見出しを配列に格納'元データを配列に格納
With Sheets("Sheet2").Range("A1").CurrentRegion
C = .Resize(.Rows.Count - 1).Offset(1, 0)
End With
Dim D
'クロス集計用の配列
ReDim D(1 To 1000, 1 To 10)
'列見出しのループ
For j = 1 To UBound(A, 2)
'行見出しのループ
For i = 1 To UBound(B, 1)
'元データをループ
For k = 1 To UBound(C, 1)
'商品と型番が一致する場合
If A(1, j) = C(k, 1) And B(i, 1) = C(k, 2) Then
'クロス集計用の配列に価格を加算
D(i, j) = D(i, j) + C(k, 3)
End If
Next
Next
Next
'クロス集計用の配列をセルに入力
Sheets("Sheet3").Range("B2").Resize(UBound(D, 1), UBound(D, 2)) = D
Debug.Print Timer - t & " 秒"
End Sub
50,000行分のデータから、クロス集計表を作成します。
配列とFotとIfを使ってクロス集計表を作成
配列とFotとIfを使って、クロス集計表を作成してみます。
次の1,000行×10列のクロス集計表を作成します。
実行すると、クロス集計表を作成できます。
クロス集計表を作成できた
クロス集計表を作成できました。
時間を計測
では、実行するのに時間をみてみます。
かかった時間
かかった時間は、約130秒となりました。
配列とFotとIfで、力技で実行した場合はかなり時間がかかります。
次は、連想配列を使って、クロス集計表の作成を高速化してみます。
連想配列を使ってクロス集計表の作成を高速化
連想配列を使って、クロス集計表の作成を高速化してみます。
連想配列を使う
連想配列の特徴について簡単に解説していきます。
連想配列は、キーとアイテムがセットになった配列のようなものになります。
配列は「キー」と「アイテム」がセット
そして、連想配列の特徴は3つになります。
特徴は3つ
特徴は、次の3つです。
キーの存在確認が高速
キーで検索してアイテムを取得できる
キーで検索してアイテムに書き込みできる
1つ目の特徴として、キーの存在確認がかなり高速です。
2つ目の特徴として、キーで検索して、アイテムを取得することができます。
3つ目の特徴として、キーで検索して、アイテムを変更することができます。
クロス集計表を作成する際には、複数キーを文字区切りで結合して登録して使います。
クロス集計表は複数キーを文字区切りで登録する
では、連想配列を使って、クロス集計表を作成する手順についてみていきます。
実行する手順
実行する手順ついてみていきます。
まずは、クロス集計表の行と列見出しの組み合わせを、連想配列に登録します。
区切り文字で結合して連想配列に登録
連想配列を検索して価格を合計してきます。
連想配列を検索して価格を合計する
商品と支店を「"/"」区切りで結合して、連想配列を検索します。
検索したキーのアイテムに、価格を加算します。
同じように次のデータでも、検索したキーのアイテムに価格を加算していきます。
すべてのデータをループして、連想配列に価格を加算します。
次は、クロス集計の形に並び替えしてセルに入力します。
クロス集計の形に並び替えしてセルに入力
作成した連想配列のアイテムを、クロス集計表の形に並び替えます。
クロス集計表の形に並び替えた配列を、セルに入力します。
これで、連想配列を使って、クロス集計表を作成できます。
クロス集計表を作成できる
こんな感じで、連想配列を使って、クロス集計表を作成できます。
では、VBAコードをみてみます。
VBAコード
連想配列を使って、クロス集計表を作成するVBAコードです。
Sub TEST2()
Dim Dic
'連想配列を作成
Set Dic = CreateObject("Scripting.Dictionary")
Dim A, B
A = Range("F1:G1") '列見出しを配列に格納
B = Range("E2:E3") '行見出しを配列に格納'連想配列に、クロス集計表の列と行の見出しの組み合わせを登録
For j = 1 To UBound(A, 2)
For i = 1 To UBound(B, 1)
Dic.Add A(1, j) & "/" & B(i, 1), 0
End If
Next
Next
Dim C
'元データを配列に格納
With Range("A1").CurrentRegion
C = .Resize(.Rows.Count - 1).Offset(1, 0)
End With
'価格と型番が一致するキーを検索して、アイテムに価格を加算
For i = 1 To UBound(C, 1)
Dic(C(i, 1) & "/" & C(i, 2)) = Dic(C(i, 1) & "/" & C(i, 2)) + C(i, 3)
Next
Dim D
'連想配列のアイテムを取得
D = Dic.items
Dim E
'クロス集計用の配列を作成
ReDim E(1 To 2, 1 To 2)
'連想配列の価格を、クロス集計用に並べ替える
k = 0
For j = 1 To UBound(E, 2)
For i = 1 To UBound(E, 1)
E(i, j) = D(k)
k = k + 1
Next
Next
'クロス集計用の配列をセルに入力
Range("F2").Resize(UBound(E, 1), UBound(E, 2)) = E
End Sub
クロス集計表を作成します。
クロス集計表を作成する
VBAコードを実行すると、クロス集計表を作成できます。
クロス集計表を作成できた
クロス集計表を作成できました。
では、次は大量データで連想配列を使って、クロス集計表を作成してみます。
大量データで連想配列を使ってクロス集計表を作成
大量データで連想配列を使ってクロス集計表を作成してみます。
VBAコード
連想配列を使って、大量データからクロス集計表を作成するVBAコードです。
先ほどのVBAコードから赤文字の部分を変更しています。
Sub TEST3()
t = Timer
Dim Dic
'連想配列を作成
Set Dic = CreateObject("Scripting.Dictionary")
Dim A, B
A = Sheets("Sheet3").Range("B1:K1") '列見出しを配列に格納
B = Sheets("Sheet3").Range("A2:A1001") '行見出しを配列に格納'連想配列に、クロス集計表の列と行の見出しの組み合わせを登録
For j = 1 To UBound(A, 2)
For i = 1 To UBound(B, 1)
Dic.Add A(1, j) & "/" & B(i, 1), 0
End If
Next
Next
Dim C
'元データを配列に格納
With Sheets("Sheet2").Range("A1").CurrentRegion
C = .Resize(.Rows.Count - 1).Offset(1, 0)
End With
'価格と型番が一致するキーを検索して、アイテムに価格を加算
For i = 1 To UBound(C, 1)
Dic(C(i, 1) & "/" & C(i, 2)) = Dic(C(i, 1) & "/" & C(i, 2)) + C(i, 3)
Next
Dim D
'連想配列のアイテムを取得
D = Dic.items
Dim E
'クロス集計用の配列を作成
ReDim E(1 To 1000, 1 To 10)
'連想配列の価格を、クロス集計用に並べ替える
k = 0
For j = 1 To UBound(E, 2)
For i = 1 To UBound(E, 1)
E(i, j) = D(k)
k = k + 1
Next
Next
'クロス集計用の配列をセルに入力
Sheets("Sheet3").Range("B2").Resize(UBound(E, 1), UBound(E, 2)) = E
Debug.Print Timer - t & " 秒"
End Sub
