ここまでの組み合わせに Subtotal を加えると、検索・抽出・加工・フィルタリング・並べ替え・集計まで一気通貫で扱えるようになります。
実務で役立つ応用テンプレを整理しました。
基本操作（Subtotal）
- 表全体に小計を追加
Range("A1").CurrentRegion.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
- 特定列で件数を集計
Range("A1").CurrentRegion.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(2), Replace:=True
Find × Subtotal
- 検索結果列を基準に小計
Dim tbl As Range, f As Range
Set tbl = Range("A1").CurrentRegion
Set f = tbl.Find("都道府県")
If Not f Is Nothing Then
tbl.Subtotal GroupBy:=f.Column - tbl.Column + 1, Function:=xlSum, TotalList:=Array(f.Column + 1), Replace:=True
End If
- 検索結果を基準に件数集計
Dim tbl As Range, f As Range
Set tbl = Range("A1").CurrentRegion
Set f = tbl.Find("市区町村")
If Not f Is Nothing Then
tbl.Subtotal GroupBy:=f.Column - tbl.Column + 1, Function:=xlCount, TotalList:=Array(f.Column + 2), Replace:=True
End If
AutoFilter × Subtotal
- フィルタ後に小計を追加
Dim tbl As Range
Set tbl = Range("A1").CurrentRegion
tbl.AutoFilter Field:=2, Criteria1:="東京都"
tbl.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), Replace:=True
- フィルタ後の件数を集計
Dim tbl As Range
Set tbl = Range("A1").CurrentRegion
tbl.AutoFilter Field:=3, Criteria1:="渋谷区"
tbl.Subtotal GroupBy:=3, Function:=xlCount, TotalList:=Array(1), Replace:=True
Sort × Subtotal
- 並べ替え後に小計
Dim tbl As Range
Set tbl = Range("A1").CurrentRegion
tbl.Sort Key1:=tbl.Columns(2), Order1:=xlAscending, Header:=xlYes
tbl.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), Replace:=True
- 複数キー並べ替え＋小計
Dim tbl As Range
Set tbl = Range("A1").CurrentRegion
tbl.Sort Key1:=tbl.Columns(2), Order1:=xlAscending, Key2:=tbl.Columns(3), Order2:=xlDescending, Header:=xlYes
tbl.Subtotal GroupBy:=2, Function:=xlCount, TotalList:=Array(4), Replace:=True
Offset × Resize × Subtotal
- 表の右隣にコピー＋小計
Dim tbl As Range
Set tbl = Range("A1").CurrentRegion
tbl.Offset(0, tbl.Columns.Count + 1).Resize(tbl.Rows.Count, tbl.Columns.Count).Value = tbl.Value
tbl.Offset(0, tbl.Columns.Count + 1).Resize(tbl.Rows.Count, tbl.Columns.Count).Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), Replace:=True
- 表の下にコピー＋件数集計
Dim tbl As Range
Set tbl = Range("A1").CurrentRegion
tbl.Offset(tbl.Rows.Count + 2, 0).Resize(tbl.Rows.Count, tbl.Columns.Count).Value = tbl.Value
tbl.Offset(tbl.Rows.Count + 2, 0).Resize(tbl.Rows.Count, tbl.Columns.Count).Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(2), Replace:=True
SpecialCells × Subtotal
- 定数セルを対象に小計
Dim tbl As Range
Set tbl = Range("A1").CurrentRegion
On Error Resume Next
tbl.SpecialCells(xlCellTypeConstants).Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), Replace:=True
On Error GoTo 0
- 数式セルを対象に小計
Dim tbl As Range
Set tbl = Range("A1").CurrentRegion
On Error Resume Next
tbl.SpecialCells(xlCellTypeFormulas).Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), Replace:=True
On Error GoTo 0
高度な応用
- 検索＋フィルタ＋並べ替え＋小計
Dim tbl As Range, f As Range
Set tbl = Range("A1").CurrentRegion
Set f = tbl.Find("都道府県")
If Not f Is Nothing Then
tbl.AutoFilter Field:=f.Column - tbl.Column + 1, Criteria1:="東京都"
tbl.Sort Key1:=f, Order1:=xlAscending, Header:=xlYes
tbl.Subtotal GroupBy:=f.Column - tbl.Column + 1, Function:=xlSum, TotalList:=Array(f.Column + 1), Replace:=True
End If
- フィルタ後の可視セルを右隣にコピー＋小計
Dim tbl As Range
Set tbl = Range("A1").CurrentRegion
tbl.AutoFilter Field:=2, Criteria1:="東京都"
tbl.SpecialCells(xlCellTypeVisible).Copy Destination:=tbl.Offset(0, tbl.Columns.Count + 2).Cells(1, 1)
tbl.Offset(0, tbl.Columns.Count + 2).Resize(tbl.Rows.Count, tbl.Columns.Count).Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), Replace:=True
- 表の下に合計行を追加＋小計
Dim tbl As Range
Set tbl = Range("A1").CurrentRegion
tbl.Offset(tbl.Rows.Count, 0).Resize(1, tbl.Columns.Count).Value = "合計行"
tbl.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), Replace:=True
実務での使い分けポイント
- 表全体 → CurrentRegion
- 検索 → Find
- 隣接セル操作 → Offset
- 範囲拡張 → Resize
- 特定セル抽出 → SpecialCells
- フィルタリング → AutoFilter
- 並べ替え → Sort
- 集計 → Subtotal
💡 この「Cells × Range × Offset × Resize × CurrentRegion × SpecialCells × Find × AutoFilter × Sort × Subtotal」テンプレを押さえておけば、表全体の動的検索・抽出・加工・フィルタリング・並べ替え・集計まで自在に扱えます。
