ここでは WorksheetFunction × Offset × Resize × Array を組み合わせて「範囲を動的に指定し、配列に取り込み、統計関数で高度な分析を行う」黄金パターンをまとめます。WorksheetFunction の統計関数を活用し、Offset と Resize で柔軟に範囲を指定し、Array に展開することで、複雑な統計処理を VBA で自動化できます。
基本パターン 10選
1. 配列に取り込み → 合計値算出
Dim rng As Range, arr As Variant
Set rng = Range("A2").Resize(10, 1)
arr = rng.Value
Range("A2").Offset(0, 1).Value = WorksheetFunction.Sum(arr)
👉 配列に取り込んだ値を合計。
2. 配列に取り込み → 平均値算出
Dim rng As Range, arr As Variant
Set rng = Range("B2").Resize(20, 1)
arr = rng.Value
rng.Offset(0, 1).Value = WorksheetFunction.Average(arr)
👉 配列に取り込んだ値を平均。
3. 配列に取り込み → 最大値算出
Dim rng As Range, arr As Variant
Set rng = Range("C2").Resize(15, 1)
arr = rng.Value
rng.Offset(0, 1).Value = WorksheetFunction.Max(arr)
👉 配列に取り込んだ値を最大値。
4. 配列に取り込み → 最小値算出
Dim rng As Range, arr As Variant
Set rng = Range("D2").Resize(12, 1)
arr = rng.Value
rng.Offset(0, 1).Value = WorksheetFunction.Min(arr)
👉 配列に取り込んだ値を最小値。
5. 配列に取り込み → 標準偏差算出
Dim rng As Range, arr As Variant
Set rng = Range("E2").Resize(30, 1)
arr = rng.Value
rng.Offset(0, 1).Value = WorksheetFunction.StDev(arr)
👉 配列に取り込んだ値を標準偏差。
6. 配列に取り込み → 件数算出
Dim rng As Range, arr As Variant
Set rng = Range("F2").Resize(25, 1)
arr = rng.Value
rng.Offset(0, 1).Value = WorksheetFunction.Count(arr)
👉 配列に取り込んだ値の件数。
7. 配列に取り込み → 空白セル件数算出
Dim rng As Range, arr As Variant
Set rng = Range("G2").Resize(20, 1)
arr = rng.Value
rng.Offset(0, 1).Value = WorksheetFunction.CountBlank(arr)
👉 配列に取り込んだ空白セル件数。
8. 配列に取り込み → 条件付き件数（COUNTIF）
Dim rng As Range, arr As Variant
Set rng = Range("H2").Resize(20, 1)
arr = rng.Value
rng.Offset(0, 1).Value = WorksheetFunction.CountIf(rng, ">=100")
👉 配列を条件付きで件数集計。
9. 配列に取り込み → 条件付き合計（SUMIF）
Dim rng1 As Range, rng2 As Range, arr1 As Variant, arr2 As Variant
Set rng1 = Range("I2").Resize(20, 1)
Set rng2 = Range("J2").Resize(20, 1)
arr1 = rng1.Value
arr2 = rng2.Value
rng1.Offset(0, 2).Value = WorksheetFunction.SumIf(rng1, "VIP", rng2)
👉 配列を条件付きで合計。
10. 動的範囲を配列に取り込み → 高度統計処理
Dim rng As Range, arr As Variant
Set rng = Range("K2").Resize(Cells(Rows.Count, "K").End(xlUp).Row - 1, 1)
arr = rng.Value
Range("K2").Offset(0, 1).Value = WorksheetFunction.Var(arr)
👉 K列の最終行まで動的に範囲を取得し、分散を算出。
✅ まとめ
- WorksheetFunction → SUM, AVERAGE, MAX, MIN, STDEV, COUNT, COUNTIF, SUMIF, VAR など統計関数を活用
- Offset → 結果を隣列や下段に自在に出力
- Resize → データ範囲を動的に拡張・縮小して柔軟に対応
- Array → 範囲を配列に取り込み、複雑な統計処理を効率化
- 黄金パターン → 合計・平均・最大/最小・標準偏差・件数・条件付き集計・分散などを一瞬で自動化
💡 この「高度統計分析 × WorksheetFunction × Offset × Resize × Array」パターン集を使えば、売上表・勤怠表・在庫表などの 高度な統計処理＋動的集計 を一瞬で自動化できます。
