VBAを使うなら理解しておきたいアルゴリズム - 抽出・結合・集計

VBAでのアルゴリズムの書き方の覚書です。

目次

• 説明に使用するデータ構造
• 抽出
• 結合
• 集計
• 関連記事

※ソートは扱いません

説明に使用するデータ構造

アルゴリズムの説明のために、以下のようなディクショナリの配列を使います。

Dim Countries(6) As Scripting.Dictionary
Dim Country As Scripting.Dictionary

Set Country = New Scripting.Dictionary
Country("name") = "日本"
Country("currency") = "JPY"
Country("population") = 127156000
Set Countries(0) = Country

Set Country = New Scripting.Dictionary
Country("name") = "フランス"
Country("currency") = "EUR"
Country("population") = 65073482
Set Countries(1) = Country

Set Country = New Scripting.Dictionary
Country("name") = "スペイン"
Country("currency") = "EUR"
Country("population") = 44904000
Set Countries(2) = Country

Set Country = New Scripting.Dictionary
Country("name") = "ロシア"
Country("currency") = "RUB"
Country("population") = 141903979
Set Countries(3) = Country

Set Country = New Scripting.Dictionary
Country("name") = "ベトナム"
Country("currency") = "VND"
Country("population") = 84238000
Set Countries(4) = Country

Set Country = New Scripting.Dictionary
Country("name") = "カンボジア"
Country("currency") = "KHR"
Country("population") = 14805000
Set Countries(5) = Country

Set Country = New Scripting.Dictionary
Country("name") = "コートジボワール"
Country("currency") = "XOF"
Country("population") = 44904000
Set Countries(6) = Country

このディクショナリの配列は、以下のようなコードでExcelシートに表示できます。

Dim I As Integer
Dim Row As Long

'// ヘッダを出力する。
Row = 1
Sheets("Output").Cells(Row, 1) = "国名"
Sheets("Output").Cells(Row, 2) = "通貨"
Sheets("Output").Cells(Row, 3) = "人口"

'// 配列中のディクショナリ毎に以下を繰り返す。
Row = Row + 1
For I = 0 To UBound(Countries)
    Set Country = Countries(I)
    
    '// データを出力する。
    Sheets("output").Cells(Row, 1) = Country("name")
    Sheets("output").Cells(Row, 2) = Country("currency")
    Sheets("output").Cells(Row, 3) = Country("population")

    Row = Row + 1
Next

このコードの実行結果は以下の通りです。

国名	通貨	人口
日本	JPY	127156000
フランス	EUR	65073482
スペイン	EUR	44904000
ロシア	RUB	141903979
ベトナム	VND	84238000
カンボジア	KHR	14805000
コートジボワール	XOF	44904000

■抽出

複数のデータの中から、特定の条件を満たすデータを抽出するアルゴリズムです。

例として、商品データProductsから特定の条件を満たすデータを抽出します。

Dim Products(5) As Scripting.Dictionary
Dim Product As Scripting.Dictionary

Set Product = New Scripting.Dictionary
Product("product_id") = "1010"
Product("product_name") = "えんぴつ"
Product("price") = 80
Set Products(0) = Product

Set Product = New Scripting.Dictionary
Product("product_id") = "1020"
Product("product_name") = "ボールペン"
Product("price") = 100
Set Products(1) = Product

Set Product = New Scripting.Dictionary
Product("product_id") = "1030"
Product("product_name") = "消しゴム"
Product("price") = 100
Set Products(2) = Product

Set Product = New Scripting.Dictionary
Product("product_id") = "2010"
Product("product_name") = "定規"
Product("price") = 140
Set Products(3) = Product

Set Product = New Scripting.Dictionary
Product("product_id") = "2020"
Product("product_name") = "コンパス"
Product("price") = 300
Set Products(4) = Product

Set Product = New Scripting.Dictionary
Product("product_id") = "3010"
Product("product_name") = "のり"
Product("price") = 200
Set Products(5) = Product

商品データProductsをExcelシートに表示すると、以下のようになります。

商品ID	商品名	価格
1010	えんぴつ	80
1020	ボールペン	100
1030	消しゴム	100
2010	定規	140
2020	コンパス	300
3010	のり	200

以下のコードは、商品データProductsから「price > 100」を満たすデータを抽出します。

'// 抽出結果を格納する動的配列
Dim Results() As Scripting.Dictionary

'// 動的配列のサイズ
Dim Size As Long
Size = -1

Dim I As Integer

'// すべての商品データについて繰り返す
For I = 0 To UBound(Products)
    Set Product = Products(I)
    
    '// 商品データのpriceが100より大きいことを確認する。
    If Product("price") > 100 Then
        '// 商品データを抽出結果に追加する。
        Size = Size + 1
        ReDim Preserve Results(Size)
        Set Results(Size) = Product
    End If
Next

抽出結果は、変数Resultsに格納されます。抽出結果ResultsをExcelシートに表示すると、以下のようになります。

商品ID	商品名	価格
2010	定規	140
2020	コンパス	300
3010	のり	200

■結合

２種類のデータを、特定の条件で結合するアルゴリズムです。

例として、商品データProductsと売上データSalesを結合します。

Dim Products(5) As Scripting.Dictionary
Dim Product As Scripting.Dictionary

Set Product = New Scripting.Dictionary
Product("product_id") = "1010"
Product("product_name") = "えんぴつ"
Product("price") = 80
Set Products(0) = Product

Set Product = New Scripting.Dictionary
Product("product_id") = "1020"
Product("product_name") = "ボールペン"
Product("price") = 100
Set Products(1) = Product

Set Product = New Scripting.Dictionary
Product("product_id") = "1030"
Product("product_name") = "消しゴム"
Product("price") = 100
Set Products(2) = Product

Set Product = New Scripting.Dictionary
Product("product_id") = "2010"
Product("product_name") = "定規"
Product("price") = 140
Set Products(3) = Product

Set Product = New Scripting.Dictionary
Product("product_id") = "2020"
Product("product_name") = "コンパス"
Product("price") = 300
Set Products(4) = Product

Set Product = New Scripting.Dictionary
Product("product_id") = "3010"
Product("product_name") = "のり"
Product("price") = 200
Set Products(5) = Product


Dim Sales(6) As Scripting.Dictionary
Dim Sale As Scripting.Dictionary

Set Sale = New Scripting.Dictionary
Sale("sales_date") = "20090702"
Sale("sales_no") = "00001"
Sale("product_id") = "1020"
Sale("amount") = 10
Set Sales(0) = Sale

Set Sale = New Scripting.Dictionary
Sale("sales_date") = "20090702"
Sale("sales_no") = "00002"
Sale("product_id") = "1010"
Sale("amount") = 5
Set Sales(1) = Sale

Set Sale = New Scripting.Dictionary
Sale("sales_date") = "20090702"
Sale("sales_no") = "00003"
Sale("product_id") = "3010"
Sale("amount") = 20
Set Sales(2) = Sale

Set Sale = New Scripting.Dictionary
Sale("sales_date") = "20090703"
Sale("sales_no") = "00001"
Sale("product_id") = "1030"
Sale("amount") = 10
Set Sales(3) = Sale

Set Sale = New Scripting.Dictionary
Sale("sales_date") = "20090703"
Sale("sales_no") = "00002"
Sale("product_id") = "1020"
Sale("amount") = 15
Set Sales(4) = Sale

Set Sale = New Scripting.Dictionary
Sale("sales_date") = "20090704"
Sale("sales_no") = "00001"
Sale("product_id") = "2020"
Sale("amount") = 30
Set Sales(5) = Sale

Set Sale = New Scripting.Dictionary
Sale("sales_date") = "20090704"
Sale("sales_no") = "00002"
Sale("product_id") = "3010"
Sale("amount") = 20
Set Sales(6) = Sale

商品データProductsをExcelシートに表示すると以下のようになります。

商品ID	商品名	価格
1010	えんぴつ	80
1020	ボールペン	100
1030	消しゴム	100
2010	定規	140
2020	コンパス	300
3010	のり	200

また、売上データSalesをExcelシートに表示すると以下のようになります。

売上日	売上番号	商品ID	数量
20090702	00001	1020	10
20090702	00002	1010	5
20090702	00003	3010	20
20090703	00001	1030	10
20090703	00002	1020	15
20090704	00001	2020	30
20090704	00002	3010	20

以下のコードは、商品データと売上データをproduct_idをキーとして結合します。

Dim I As Integer
Dim product_id As String
    
'/////////////////////////////////////////////////////////////////////////////////
'// ハッシュテーブルを作成し、 product_idをキーとして商品データを取得できるようにする。
'/////////////////////////////////////////////////////////////////////////////////

'// ハッシュテーブル
Dim HashTable As New Scripting.Dictionary

'// すべての商品データについて繰り返す。
For I = 0 To UBound(Products)
    Set Product = Products(I)
    
    '// 商品データのproduct_idを取得する。
    product_id = Product("product_id")

    '// product_idをキーとして、商品データをハッシュテーブルに格納する。
    Set HashTable(product_id) = Product
Next

'/////////////////////////////////////////////////////////////////////////////////
'// 売上データと商品データを結合する。
'/////////////////////////////////////////////////////////////////////////////////

'//すべての売上データについて繰り返す。
For I = 0 To UBound(Sales)
    Set Sale = Sales(I)

    '// 売上データのproduct_idを取得する。
    product_id = Sale("product_id")

    '// product_idをキーとして、商品データをハッシュテーブルから取得する。
    Set Product = HashTable(product_id)
    If Not IsEmpty(Product) Then
        '// 商品データを取得できた場合:
        
        '// 商品データの値を売上データに格納する。
        Sale("product_name") = Product("product_name")
        Sale("price") = Product("price")
    End If
Next

結合後の売上データSalsesをExcelシートに表示すると、以下のようになります。

売上日	売上番号	商品ID	商品名	数量	価格
20090702	00001	1020	ボールペン	10	100
20090702	00002	1010	えんぴつ	5	80
20090702	00003	3010	のり	20	200
20090703	00001	1030	消しゴム	10	100
20090703	00002	1020	ボールペン	15	100
20090704	00001	2020	コンパス	30	300
20090704	00002	3010	のり	20	200

■集計

複数のデータを特定の条件で集計するアルゴリズムです。

例として、売上データSalesを集計します。

Dim Sales(6) As Scripting.Dictionary
Dim Sale As Scripting.Dictionary

Set Sale = New Scripting.Dictionary
Sale("sales_date") = "20090702"
Sale("sales_no") = "00001"
Sale("product_name") = "ボールペン"
Sale("amount") = 10
Sale("price") = 100
Set Sales(0) = Sale

Set Sale = New Scripting.Dictionary
Sale("sales_date") = "20090702"
Sale("sales_no") = "00002"
Sale("product_name") = "えんぴつ"
Sale("amount") = 5
Sale("price") = 80
Set Sales(1) = Sale

Set Sale = New Scripting.Dictionary
Sale("sales_date") = "20090702"
Sale("sales_no") = "00003"
Sale("product_name") = "のり"
Sale("amount") = 20
Sale("price") = 200
Set Sales(2) = Sale

Set Sale = New Scripting.Dictionary
Sale("sales_date") = "20090703"
Sale("sales_no") = "00001"
Sale("product_name") = "消しゴム"
Sale("amount") = 10
Sale("price") = 100
Set Sales(3) = Sale

Set Sale = New Scripting.Dictionary
Sale("sales_date") = "20090703"
Sale("sales_no") = "00002"
Sale("product_name") = "ボールペン"
Sale("amount") = 15
Sale("price") = 100
Set Sales(4) = Sale

Set Sale = New Scripting.Dictionary
Sale("sales_date") = "20090704"
Sale("sales_no") = "00001"
Sale("product_name") = "コンパス"
Sale("amount") = 30
Sale("price") = 300
Set Sales(5) = Sale

Set Sale = New Scripting.Dictionary
Sale("sales_date") = "20090704"
Sale("sales_no") = "00002"
Sale("product_name") = "のり"
Sale("amount") = 20
Sale("price") = 200
Set Sales(6) = Sale

売上データSalesをExcelシートに表示すると以下のようになります。

売上日	売上番号	商品名	数量	価格
20090702	00001	ボールペン	10	100
20090702	00002	えんぴつ	5	80
20090702	00003	のり	20	200
20090703	00001	消しゴム	10	100
20090703	00002	ボールペン	15	100
20090704	00001	コンパス	30	300
20090704	00002	のり	20	200

以下のコードは、Salesを集計してsales_date毎の合計数量と合計売上高を求めます。

'///////////////////////////////////////////////////////
'// 集計結果の初期化
'///////////////////////////////////////////////////////

Dim ResultsDict As Scripting.Dictionary
Dim Result As Scripting.Dictionary
Dim sales_date As Variant

'// 集計結果を一時的に格納するディクショナリ
Set ResultsDict = New Scripting.Dictionary

'// すべての売上データについて繰り返す。
For I = 0 To UBound(Sales)
    Set Sale = Sales(I)
    
    '// 売上データのsales_dateを取得する。
    sales_date = Sale("sales_date")

    If IsEmpty(ResultsDict(sales_date)) Then
        '// ディクショナリにキーsales_dateが含まれていない:
        
        '// 集計結果の初期値を生成する。
        Set Result = New Scripting.Dictionary
        Result("sales_date") = sales_date
        Result("amount") = 0
        Result("total") = 0
        
        '// 生成した初期値をキーsales_dateに関連付けてディクショナリに格納する。
        Set ResultsDict(sales_date) = Result
    End If
Next

'///////////////////////////////////////////////////////
'// salesを集計
'///////////////////////////////////////////////////////

'// すべての売上データについて繰り返す。
For I = 0 To UBound(Sales)
    Set Sale = Sales(I)
    
    '// 売上データのsales_dateを取得する。
    sales_date = Sale("sales_date")
    
    '// キーsales_dateに関連付く集計結果をディクショナリから取得する。
    Set Result = ResultsDict(sales_date)

    '// 売上データのamountを集計結果に足し合わせる。
    Result("amount") = Result("amount") + Sale("amount")
    '// 売上データのpriceとamountとを掛けた値を集計結果に足し合わせる。
    Result("total") = Result("total") + Sale("price") * Sale("amount")
Next
    
'///////////////////////////////////////////////////////
'// 集計結果を配列に移し替え
'///////////////////////////////////////////////////////

Dim Results() As Scripting.Dictionary
Dim Size As Long

Size = -1

'// 一時ディクショナリの全てのキーとデータについて繰り返す。
For Each sales_date In ResultsDict
    Set Result = ResultsDict(sales_date)
    
    '// データを配列に追加する
    Size = Size + 1
    ReDim Preserve Results(Size)
    Set Results(Size) = Result
Next

集計結果は変数Resultsに格納されます。集計結果ResultsをExcelシートに表示すると以下のようになります。

売上日	合計数量	合計売上高
20090702	35	5400
20090703	25	2500
20090704	50	13000

関連記事

データ構造

• Javaを使うなら必ず覚えておきたいデータ構造 - 配列・リスト・マップ
• PHPなら覚えるべきデータ構造はひとつだけ？ - 配列
• Perlで覚えたいデータ構造 - 配列・ハッシュ
• VBAで覚えておくデータ構造 - 静的配列・動的配列・ディクショナリ
• JavaScriptで覚えておくとよいデータ構造 - 配列・オブジェクト
• Bashで覚えておくとよいデータ構造 - 配列 - 何かしらの言語による記述を解析する日記

アルゴリズム

• Javaを使うなら理解しておきたいアルゴリズム - 抽出・ソート・結合・集計 (リスト＆マップ編)
• Javaを使うなら理解しておきたいアルゴリズム - 抽出・ソート・結合・集計 (リスト＆ビーン編)
• PHPを使うなら理解しておきたいアルゴリズム - 抽出・ソート・結合・集計
• VBAを使うなら理解しておきたいアルゴリズム - 抽出・結合・集計
• Javascriptを使うなら理解しておきたいアルゴリズム - 抽出・ソート・結合・集計
• SQLを使うなら理解しておきたいアルゴリズム？(というか、select文の書き方) - where・order by・join・group by
• Bashを使うなら理解しておきたいアルゴリズム - 抽出・ソート・結合・集計