ここでは Offset × 条件分岐 × Subtotal を組み合わせて「表の部分集計を自動化する」黄金パターンをまとめます。WorksheetFunction.Subtotal を活用することで、フィルタ後の可視セルだけを対象にした集計や、複数条件付きの集計が可能になります。
基本パターン 10選
1. 合計値を右隣に出力
Dim r As Range
For Each r In Range("A2:A20")
If IsNumeric(r.Value) Then
r.Offset(0, 1).Value = WorksheetFunction.Subtotal(9, Range("A2:A20"))
End If
Next r
👉 列全体の合計を右隣に出力。9 は SUM。
2. 平均値を右隣に出力
Dim r As Range
For Each r In Range("B2:B20")
If IsNumeric(r.Value) Then
r.Offset(0, 1).Value = WorksheetFunction.Subtotal(1, Range("B2:B20"))
End If
Next r
👉 列全体の平均を右隣に出力。1 は AVERAGE。
3. 件数を右隣に出力
Dim r As Range
For Each r In Range("C2:C20")
If r.Value <> "" Then
r.Offset(0, 1).Value = WorksheetFunction.Subtotal(3, Range("C2:C20"))
End If
Next r
👉 列全体の件数を右隣に出力。3 は COUNT。
4. 最大値を右隣に出力
Dim r As Range
For Each r In Range("D2:D20")
If IsNumeric(r.Value) Then
r.Offset(0, 1).Value = WorksheetFunction.Subtotal(4, Range("D2:D20"))
End If
Next r
👉 列全体の最大値を右隣に出力。4 は MAX。
5. 最小値を右隣に出力
Dim r As Range
For Each r In Range("E2:E20")
If IsNumeric(r.Value) Then
r.Offset(0, 1).Value = WorksheetFunction.Subtotal(5, Range("E2:E20"))
End If
Next r
👉 列全体の最小値を右隣に出力。5 は MIN。
6. 標準偏差を右隣に出力
Dim r As Range
For Each r In Range("F2:F20")
If IsNumeric(r.Value) Then
r.Offset(0, 1).Value = WorksheetFunction.Subtotal(7, Range("F2:F20"))
End If
Next r
👉 列全体の標準偏差を右隣に出力。7 は STDEV。
7. フィルタ後の合計を出力
Range("G2:G20").Offset(0, 1).Value = WorksheetFunction.Subtotal(9, Range("G2:G20"))
👉 フィルタで表示されているセルだけの合計を右隣に出力。
8. フィルタ後の平均を出力
Range("H2:H20").Offset(0, 1).Value = WorksheetFunction.Subtotal(1, Range("H2:H20"))
👉 フィルタで表示されているセルだけの平均を右隣に出力。
9. 条件付き集計（VIP顧客のみ）
Dim r As Range, rng As Range
Set rng = Range("I2:I20")
For Each r In rng
If r.Offset(0, -1).Value = "VIP" Then
r.Offset(0, 1).Value = WorksheetFunction.Subtotal(9, rng)
End If
Next r
👉 VIP顧客の売上を条件付きで集計。
10. 部署ごとに小計を下段に出力
Dim deptRng As Range
For Each deptRng In Range("J2:J20").SpecialCells(xlCellTypeVisible).Areas
deptRng.Offset(deptRng.Rows.Count, 0).Resize(1, 1).Value = WorksheetFunction.Subtotal(9, deptRng)
Next deptRng
👉 部署ごとに小計を下段に出力。
✅ まとめ
- Offset → 集計結果を隣列や下段へ出力
- 条件分岐 → 数値判定・顧客判定・部署判定などを組み合わせ
- Subtotal → フィルタ後の可視セルだけを対象に集計可能
- 黄金パターン → 合計・平均・件数・最大/最小・標準偏差・条件付き集計・小計展開
💡 この「データ集計 × Offset × 条件分岐 × Subtotal」パターン集を使えば、売上表・勤怠表・在庫表などの 部分集計・小計処理 を一瞬で自動化できます。
