Excel VBAでSUBTOTAL関数を使って集計する

2020年4月26日2023年6月25日

SUBTOTAL関数は集計方法の条件を指定することでさまざまな集計を求めることができます。
例えば集計方法を指定することにより、合計値はもちろんのこと、その他に平均値や最大値、最小値標準偏差などを求めることができます。
また、集計の際に非表示セルを含めたり含めなかったりできるので柔軟な対応が可能となります。

SUBTOTAL関数の説明

SortFields コレクションのメソッド
　WorksheetFunction.Subtotal( 集計方法, 集計範囲1[, 集計範囲2,・・・] )
Subtotal 関数には11種類の集計方法があり、引数で集計方法を決めることができます。
集計方法は下記表の1列目又は2列目にある番号で指定します。

非表示セルを含める	非表示セルを含めない	集計方法	対応する関数
1	101	平均値	AVERAGE
2	102	数値のあるセル個数	COUNT
3	103	空白を除くセル個数	COUNTA
4	104	最大値	MAX
5	105	最小値	MIN
6	106	積	PRODUCT
7	107	標本の標準偏差	STDEV
8	108	母集団全体の標準偏差	STDEVP
9	109	合計	SUM
10	110	標本による分散の予測値	VAR
11	111	母集団全体の分散	VARP

SUBTOTAL関数を使ったサンプルアプリの説明

「東京都に勤務している人数」ボタンをクリックすると、オートフィルタで勤務地が東京の社員を抽出して、ダイアログで人数を表示してくれます。

「東京都に勤務している人の平均の通勤手当」ボタンをクリックすると、オートフィルタで勤務地が東京の社員を抽出して、抽出された社員の平均通勤手当をダイアログで表示してくれます。

サンプルアプリのソースコード　　　コードの作成手順はこちら

「東京都に勤務している人数」ボタンのソースコード

Sub ボタン1_Click()

   'ワークシートを指定する
    Dim Sh1 As Worksheet
    Set Sh1 = ThisWorkbook.Worksheets("Sheet1")
    
   '変数を定義する
    Dim workEndR1, Ninzu As Long
    
   'データの最終行を取得する
    workEndR1 = Sh1.Cells(Rows.Count, 2).End(xlUp).Row
    
   'オートフィルタの対象となるデータ範囲を指定する
    Dim Rng1 As Range
    Set Rng1 = Range(Sh1.Cells(5, 2), Sh1.Cells(workEndR1, 7))
    
   'オートフィルタで勤務地が東京都の社員を抽出する。
    Rng1.AutoFilter Field:=5, Criteria1:="東京都"
    
   '集計対象となるデータ範囲を指定する
    Dim Rng2 As Range
    Set Rng2 = Range(Sh1.Cells(6, 6), Sh1.Cells(workEndR1, 6))
   
   '集計方法として「3」(件数の集計)を指定して該当人数を集計する。
    Ninzu = WorksheetFunction.Subtotal(3, Rng2)
    
   'ダイアログを表示して人数を表示
    MsgBox "東京都に勤務している社員の人数：" & Ninzu & "人"

   'フィルター状態によって処理分岐
    If ActiveSheet.AutoFilterMode = True Then
        'オートフィルタ解除
        Rng1.AutoFilter

    ElseIf ActiveSheet.AutoFilterMode = False Then
        'オートフィルタが解除されていたら何もしない
  
    End If

End Sub

「東京都に勤務している人の平均の通勤手当」ボタンのソースコード

Sub ボタン2_Click()

   'ワークシートを指定する
    Dim Sh1 As Worksheet
    Set Sh1 = ThisWorkbook.Worksheets("Sheet1")
    
   '変数を定義する
    Dim workEndR1, Heikin As Long
    
   'データの最終行を取得する
    workEndR1 = Sh1.Cells(Rows.Count, 2).End(xlUp).Row
    
   'オートフィルタの対象となるデータ範囲を指定する
    Dim Rng1 As Range
    Set Rng1 = Range(Sh1.Cells(5, 2), Sh1.Cells(workEndR1, 7))
    
   'オートフィルタで勤務地が東京都の社員を抽出する。
    Rng1.AutoFilter Field:=5, Criteria1:="東京都"
    
   '集計対象となるデータ範囲を指定する
    Dim Rng2 As Range
    Set Rng2 = Range(Sh1.Cells(6, 7), Sh1.Cells(workEndR1, 7))

   '集計方法として「1」(平均値の集計)を指定して該当者の平均通勤手当を集計する。
    Heikin = WorksheetFunction.Subtotal(1, Rng2)
    
   'ダイアログを表示して平均額を表示
    MsgBox "東京都に勤務している社員の平均通勤手当：" & Format(Heikin, "#,###") & "円"
       
   'フィルター状態によって処理分岐
    If ActiveSheet.AutoFilterMode = True Then
        'オートフィルタ解除
        Rng1.AutoFilter
        
    ElseIf ActiveSheet.AutoFilterMode = False Then
        'オートフィルタが解除されていたら何もしない
        
    End If

End Sub

VBA,集計処理Subtotal,VBA,集計

Posted by CoderM