大体でIT

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

大体でIT

Excel VBAを使って、クロス集計表を高速で作成するには、Dictionaryを使って連想配列を使うとできます。大量データからクロス集計表を高速で作成したい、といった場合にかなり役立ちます。連想配列を使ってクロス集計表を作成する方法について、マスターしていきましょう。

はじめに

この記事では、連想配列を使って、高速でクロス集計表を作成する方法について、ご紹介します。

連想配列を使うには、「Dictionary」を使うとできます。

クロス集計表を作成するには、複数の条件で検索する必要があります。

なので、複数条件を区切り文字で結合して、キーに登録するのがポイントになります。

連想配列を使えば、かなり高速でクロス集計表を作成することができます。

大量データからクロス集計表を作成したい、といった場合に使えます。

連想配列を使ってクロス集計表を作成する方法についてマスターして、実務で活用していきましょう。

では、連想配列を使って、高速でクロス集計表を作成する方法について、解説していきます。

この記事を読むメリット

  • 連想配列を使って高速でクロス集計表を作成できるようになります。

本記事の内容を動画にて解説しております。

VBAコードの作成手順について、動画で確認できます。

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

目次

高速でクロス集計表を作成したい

高速でクロス集計表を作成してみます。

やりたい内容

やりたい内容は、高速でクロス集計表を作成したい、ということになります。

高速でクロス集計表を作成したい

高速でクロス集計表を作成したいです。

高速でクロス集計表を作成したいです

クロス集計表の行見出しと列見出しはあらかじめ入力しておきます。

クロス集計表の行見出しと列見出しはあらかじめ入力しておきます

次のような感じで、大量データから高速にクロス集計表を作成します。

クロス集計表を作成する

大量データから高速にクロス集計表を作成したいです

では、クロス集計表を作成するVBAコードを作成していきます。

まずは、配列とForとIfを使って力技でクロス集計表を作成して、その時間を計測してみます。

配列を使ってクロス集計表を作成

配列を使ってクロス集計表を作成してみます。

結果から言うと、かなり遅いです。

では、作成してみます。

配列とForとIfでクロス集計表を作成

配列とForとIfでクロス集計表を作成するVBAコードです。

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を使って、クロス集計表を作成してみます。

配列とFotとIfを使って、クロス集計表を作成してみます

次の1,000行×10列のクロス集計表を作成します。

クロス集計表を作成します

実行すると、クロス集計表を作成できます。

クロス集計表を作成できた

クロス集計表を作成できました

クロス集計表を作成できました。

時間を計測

では、実行するのに時間をみてみます。

かかった時間

配列とFotとIfで、力技で実行した場合はかなり時間がかかります

かかった時間は、約130秒となりました。

配列とFotとIfで、力技で実行した場合はかなり時間がかかります。

次は、連想配列を使って、クロス集計表の作成を高速化してみます。

連想配列を使ってクロス集計表の作成を高速化

連想配列を使って、クロス集計表の作成を高速化してみます。

連想配列を使う

連想配列の特徴について簡単に解説していきます。

連想配列は、キーとアイテムがセットになった配列のようなものになります。

配列は「キー」と「アイテム」がセット

連想配列は、キーとアイテムがセットになった配列のようなものです

そして、連想配列の特徴は3つになります。

特徴は3つ

特徴は、次の3つです。

  • キーの存在確認が高速
  • キーで検索してアイテムを取得できる
  • キーで検索してアイテムに書き込みできる

1つ目の特徴として、キーの存在確認がかなり高速です。

1つ目の特徴として、キーの存在確認がかなり高速です

2つ目の特徴として、キーで検索して、アイテムを取得することができます。

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

大量データで、連想配列を使ってクロス集計表を作成してみます。

大量データでクロス集計表を作成

大量データで、連想配列を使ってクロス集計表を作成してみます

実行すると、連想配列を使って、クロス集計表を作成できます。

クロス集計表を作成できた

連想配列を使って、クロス集計表を作成できました

連想配列を使って、クロス集計表を作成できました。

では、かかった時間をみてみます。

時間を計測

連想配列を使って、クロス集計表を作成するのにかかった時間は次のようになります。

かかった時間

連想配列を使ってクロス集計表を作成するとかなり高速です

結果は、約0.1秒となりました。

配列とFotとIfを使う方法では、約130秒でしたので、1000倍ぐらい高速になっています。

こんな感じで、連想配列を使ってクロス集計表を作成するとかなり高速です。

おわりに

この記事では、連想配列を使って、高速でクロス集計表を作成する方法について、ご紹介しました。

連想配列を使うには、「Dictionary」を使うとできます。

クロス集計表を作成するには、複数の条件で検索する必要があります。

なので、複数条件を区切り文字で結合して、キーに登録するのがポイントになります。

連想配列を使えば、かなり高速でクロス集計表を作成することができます。

大量データからクロス集計表を作成したい、といった場合に使えます。

連想配列を使ってクロス集計表を作成する方法についてマスターして、実務で活用していきましょう。

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

関連する記事から探す

カテゴリから探す

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

サイト内を検索する

↓キーワードを入力する

アーカイブから探す