ここでは WorksheetFunction × Offset × Resize × Array × Dictionary を組み合わせて「複数次元のデータを動的に取得 → 配列に展開 → 統計処理 → Dictionaryでキーごとに集計」までを自動化する黄金パターンをまとめます。
これにより、売上表・勤怠表・在庫表などの 多次元統計分析 を VBA で効率的に実現できます。
基本パターン 10選
1. 部署別売上合計（Dictionary＋Sum）
Dim dict As Object, arr As Variant, r As Range
Set dict = CreateObject("Scripting.Dictionary")
Set r = Range("A2").Resize(20, 2) ' 部署・売上
arr = r.Value
Dim i As Long
For i = 1 To UBound(arr, 1)
If arr(i, 1) <> "" Then
If dict.Exists(arr(i, 1)) Then
dict(arr(i, 1)) = dict(arr(i, 1)) + arr(i, 2)
Else
dict.Add arr(i, 1), arr(i, 2)
End If
End If
Next i
👉 部署ごとに売上を合計。
2. 商品カテゴリ別平均売上
Dim dict As Object, arr As Variant, r As Range
Set dict = CreateObject("Scripting.Dictionary")
Set r = Range("B2").Resize(30, 2) ' カテゴリ・売上
arr = r.Value
Dim i As Long
For i = 1 To UBound(arr, 1)
If arr(i, 1) <> "" Then
If dict.Exists(arr(i, 1)) Then
dict(arr(i, 1)) = dict(arr(i, 1)) + arr(i, 2)
Else
dict.Add arr(i, 1), arr(i, 2)
End If
End If
Next i
' 平均値算出（件数で割る）
👉 カテゴリ別に売上平均を算出。
3. 月別売上推移（Resize＋Dictionary）
Dim dict As Object, arr As Variant
Set dict = CreateObject("Scripting.Dictionary")
arr = Range("C2").Resize(Cells(Rows.Count, "C").End(xlUp).Row - 1, 2).Value
Dim i As Long, key As String
For i = 1 To UBound(arr, 1)
key = Format(arr(i, 1), "yyyy/mm")
If dict.Exists(key) Then
dict(key) = dict(key) + arr(i, 2)
Else
dict.Add key, arr(i, 2)
End If
Next i
👉 月別に売上を集計。
4. 顧客別購入回数（Dictionary＋Count）
Dim dict As Object, arr As Variant
Set dict = CreateObject("Scripting.Dictionary")
arr = Range("D2").Resize(50, 1).Value
Dim i As Long
For i = 1 To UBound(arr, 1)
If arr(i, 1) <> "" Then
If dict.Exists(arr(i, 1)) Then
dict(arr(i, 1)) = dict(arr(i, 1)) + 1
Else
dict.Add arr(i, 1), 1
End If
End If
Next i
👉 顧客ごとの購入回数を集計。
5. 商品別在庫最大値
Dim dict As Object, arr As Variant
Set dict = CreateObject("Scripting.Dictionary")
arr = Range("E2").Resize(40, 2).Value
Dim i As Long
For i = 1 To UBound(arr, 1)
If dict.Exists(arr(i, 1)) Then
If arr(i, 2) > dict(arr(i, 1)) Then dict(arr(i, 1)) = arr(i, 2)
Else
dict.Add arr(i, 1), arr(i, 2)
End If
Next i
👉 商品別に在庫の最大値を算出。
6. 部署別勤務時間標準偏差
Dim dict As Object, arr As Variant
Set dict = CreateObject("Scripting.Dictionary")
arr = Range("F2").Resize(30, 2).Value
Dim i As Long
For i = 1 To UBound(arr, 1)
If dict.Exists(arr(i, 1)) Then
dict(arr(i, 1)) = dict(arr(i, 1)) & "," & arr(i, 2)
Else
dict.Add arr(i, 1), arr(i, 2)
End If
Next i
' Dictionaryの値をSplitしてWorksheetFunction.StDevで標準偏差算出
👉 部署別勤務時間の標準偏差を算出。
7. 月別エラー件数
Dim dict As Object, arr As Variant
Set dict = CreateObject("Scripting.Dictionary")
arr = Range("G2").Resize(25, 2).Value
Dim i As Long, key As String
For i = 1 To UBound(arr, 1)
key = Format(arr(i, 1), "yyyy/mm")
If dict.Exists(key) Then
dict(key) = dict(key) + 1
Else
dict.Add key, 1
End If
Next i
👉 月別にエラー件数を集計。
8. タグ別出現頻度（Split＋Dictionary）
Dim dict As Object, arr As Variant, tags As Variant, i As Long, j As Long
Set dict = CreateObject("Scripting.Dictionary")
arr = Range("H2").Resize(20, 1).Value
For i = 1 To UBound(arr, 1)
If arr(i, 1) <> "" Then
tags = Split(arr(i, 1), ",")
For j = LBound(tags) To UBound(tags)
If dict.Exists(Trim(tags(j))) Then
dict(Trim(tags(j))) = dict(Trim(tags(j))) + 1
Else
dict.Add Trim(tags(j)), 1
End If
Next j
End If
Next i
👉 タグを分割して出現頻度を集計。
9. 顧客別売上＋購入回数（多次元統合）
Dim dict As Object, arr As Variant
Set dict = CreateObject("Scripting.Dictionary")
arr = Range("I2").Resize(50, 2).Value
Dim i As Long
For i = 1 To UBound(arr, 1)
If dict.Exists(arr(i, 1)) Then
dict(arr(i, 1)) = Array(dict(arr(i, 1))(0) + arr(i, 2), dict(arr(i, 1))(1) + 1)
Else
dict.Add arr(i, 1), Array(arr(i, 2), 1)
End If
Next i
👉 顧客ごとに「売上合計＋購入回数」を同時に集計。
10. 統合結果を表形式で出力
Dim k As Variant, pasteRow As Long
pasteRow = 60
For Each k In dict.Keys
Cells(pasteRow, 1).Value = k
If IsArray(dict(k)) Then
Cells(pasteRow, 2).Value = dict(k)(0) ' 売上合計
Cells(pasteRow, 3).Value = dict(k)(1) ' 購入回数
Else
Cells(pasteRow, 2).Value = dict(k)
End If
pasteRow = pasteRow + 1
Next k
👉 Dictionaryで統合した結果を表形式で出力。
✅ まとめ
- WorksheetFunction → SUM, AVERAGE, MAX, MIN, STDEV, COUNT など統計関数を活用
- Offset × Resize → 動的に範囲を指定して柔軟に対応
- Array → 範囲を配列に取り込み効率的に処理
- Dictionary → キーごとにデータを統合・多次元集計
- 黄金パターン → 部署別・商品別・顧客別・月別・タグ別などの多次元統計分析を一瞬で自動化
この複合黄金パターンの強み
- 多次元分析：部署×月、顧客×購入回数、商品×在庫など複数指標を同時に扱える
- 柔軟性：Resizeで範囲を動的に指定し、Arrayで効率的に処理、Dictionaryでキーごとに集計
- 拡張性：WorksheetFunctionを組み合わせることで、平均・分散・標準偏差など高度な統計処理も可能
- 再利用性：テンプレ化すれば、どんな表でも即座に多次元統計分析に対応できる
💡 この「多次元統計分析 × WorksheetFunction × Offset × Resize × Array × Dictionary」パターン集を使えば、売上表・勤怠表・在庫表などの 複雑な多次元統計処理＋集計 を VBA で効率的に自動化できます。
