【VBA】15.3 Subtotal関数を使ってみよう！

2024年9月19日

Subtotal関数の概要

Subtotal関数は、集計を行う関数です。
マクロで使用する場合は、Subtotal関数をAutoFilterと組み合わせると、有効に活用することができます。

Subtotal関数には、以下の主な2つの機能があります。

AutoFilterで列の絞り込みが行われている場合、ワークシート上に表示されているセルに対して、集計を行います。
Subtotal関数の集計対象にSubtotal関数が集計したものが含まれていた場合、集計対象内にあるSubtotal関数が集計したものを除外して集計を行います。
11種類の集計方法が提供されています。

集計を行う関数としては、SUM関数を思い浮かべることが多いと思いますが、SUM関数は指定した領域の単純な合計しかできないのに対して、Subtotal関数は上記のような柔軟な集計を行うことができます。

[overdoserの経験則] Subtotal関数を実際の業務で使用するケース

実際のExcelマクロの開発では、AutoFilterで絞り込みを行った後、Subtotal関数で集計を行う、というケースが非常に多いと考えられます。
業務においての実際のケースとしては、以下のような例が想定されます。

社内の部署毎の人数を集計するため、全社員が記載された社員一覧シートに対して、全ての部署コードでフィルターし、それぞれの部署コードの人数をSubtotal関数で集計し、部署コードとその部署の人数を一覧化する。
社員がアクセスしているインターネット上のサイトのリストに対して、主要なドメイン名でフィルターし、それぞれのドメイン名のアクセス数をSubtotal関数で人数を集計し、ドメイン名毎のアクセス数を一覧化する。

Subtotal関数の構文

Subtotal関数の構文は、以下の通りです。

戻り値 = WorksheetFunction.Subtotal(集計方法, 集計領域)

引数名	必須/ 任意	解説
集計方法	必須	Subtotal関数が集計する集計方法を、表15.3.2の引数で指定します。
集計領域	必須	Subtotal関数が集計する領域を指定します。

表15.3.1

概要で11種類の集計方法があると記述しましたが、Excelマクロを作成していて頻度が高い3つについて、以下で解説します。

非表示セルを含めて集計する場合	非表示セルを含めずに集計する場合	集計方法	使用頻度
3	103	空白を除くセルの個数	非常に高い
9	109	合計	中
2	102	数値のあるセルの個数	低

表15.3.2

「非表示セルを集計する場合」と「非表示セルを集計しない場合」の違いは、AutoFilterではなく、手動で非表示にしたセルがある場合について、非表示セルを集計に含める/含めないの制御することができます。

【手動で行を非表示する場合のイメージ】

【マクロで行を非表示にする場合のサンプルコード】

    ws_inputfile.Rows(10).Hidden

Subtotal関数の使用例

Subtotal関数を実際に使用した例は、以下の通りです。

    num_employee = WorksheetFunction.Subtotal(3, ws_employeelist.Columns(6))

上記のサンプルコードは、以下の処理を行っています。

社員一覧シート（ws_employeelist）の6列目に表示されている空白以外のセルの数（非表示にしたセルも含む）を集計し、社員数の変数（num_employee）にセットする。

なお実際のマクロを作成する際の注意点としては、通常はヘッダーが1行目に設定されており、ヘッダーは空白ではない場合が大半であるため、上記のままではヘッダー行も含めてセルの数が算出されるため、ヘッダーがある場合で実データのセルの数のみを算出する場合は、上記のコードに「- 1」を追加する必要があります。

[応用編] Subtotal関数を用いたコーディング例

今回のコーディング例は、以下の要件を実現するものです。

入力ファイル.xlsxの役職コード一覧シートのA列に記載されている全ての役職コードの人数を社員一覧シートから集計し、出力ファイル.xlsxに役職名毎の人数を出力する。

上記の太字の箇所のみを実行するコーディング例は、以下の通りです。

    For num_row = 2 To ws_positionlist.Cells(Rows.Count, 1).End(xlUp).Row
        If ws_employeelist.AutoFilterMode Then
            ws_employeelist.Range("A1").AutoFilter
        End If
        
        ws_outputfile.Cells(num_row, 1) = ws_positionlist.Cells(num_row, 1)

        ws_employeelist.Range("A1").AutoFilter field:=6, Criteria1:=ws_positionlist.Cells(num_row, 1)
    
        ws_outputfile.Cells(num_row, 2) = WorksheetFunction.Subtotal(3, ws_employeelist.Columns(6)) - 1
    Next num_row

上記のコーディング例は、以下のような処理を行っています。

役職コード一覧シートの1列目の2行目から最終行まで、以下の処理を繰り返す。
社員一覧シートにフィルターが設定されている場合、フィルターを外す。
出力シートのnum_row行、1列目のセルに、役職コード一覧シートのnum_row行、1列目の値をセットする。
社員一覧シートにフィルターを設定し、6列目を役職コード一覧シートのnum_row行、1列目の値で絞り込む。
社員一覧シートの6列目を、Subtotal関数で表示されている空白以外のセルの個数を集計し、出力シートのnum_row行、2列目のセルにセットする。

以下のソースコードは、上記のコーディング例をサンプルプログラムに組み込み、実際に動作するようにしたものです。
（出力シートに対して、見栄えを良くするコードを加えています。）

Option Explicit

Sub extract_employeelist()

    Dim ws_macro As Worksheet           '社員データ抽出マクロファイル内の社員データ抽出マクロシートのワークシート変数
    Dim ws_work As Worksheet            '社員データ抽出マクロファイル内のworkシートのワークシート変数
    Dim wb_inputfile As Workbook        '入力ファイルのワークブック変数
    Dim ws_employeelist As Worksheet    '入力ファイルの社員一覧シートのワークシート変数
    Dim ws_positionlist As Worksheet    '入力ファイルの役職一覧シートのワークシート変数
    Dim path_inputfile As String        '入力ファイルのフルパスを設定する変数
    Dim wb_outputfile As Workbook       '出力ファイルのワークブック変数
    Dim ws_outputfile As Worksheet      '出力ファイルのワークシート変数
    Dim path_outputfile As String       '出力ファイルのフルパスを設定する変数
    Dim num_row As Long                 'Forループ分のカウンター
    
    Application.ScreenUpdating = False

    Set ws_macro = ThisWorkbook.Worksheets("社員データ抽出マクロ")
    Set ws_work = ThisWorkbook.Worksheets("work")
    
    If Right(ws_macro.Range("D3"), 1) <> "\" Then
        path_inputfile = ws_macro.Range("D3") & "\" & ws_macro.Range("C3")
    Else
        path_inputfile = ws_macro.Range("D3") & ws_macro.Range("C3")
    End If
    
    Workbooks.Open Filename:=path_inputfile
    Set wb_inputfile = ActiveWorkbook
    Set ws_employeelist = ActiveWorkbook.Worksheets("社員一覧")
    Set ws_positionlist = ActiveWorkbook.Worksheets("役職コード一覧")
    
    If Right(ws_macro.Range("D4"), 1) <> "\" Then
        path_outputfile = ws_macro.Range("D4") & "\" & ws_macro.Range("C4")
    Else
        path_outputfile = ws_macro.Range("D4") & ws_macro.Range("C4")
    End If
    
    Workbooks.Add
    Set wb_outputfile = ActiveWorkbook
    Set ws_outputfile = ActiveWorkbook.Worksheets(1)
    ws_outputfile.Name = "役職名毎の社員数一覧"
    ws_outputfile.Range("A1") = "役職名"
    ws_outputfile.Range("B1") = "社員数"
    ws_outputfile.Range(ws_outputfile.Columns(1), ws_outputfile.Columns(2)).HorizontalAlignment = xlCenter
    ws_outputfile.Range("A1:B1").ColumnWidth = 10
    ws_outputfile.Range("A1:B1").Interior.Color = RGB(204, 255, 204)
    
    For num_row = 2 To ws_positionlist.Cells(Rows.Count, 1).End(xlUp).Row
        If ws_employeelist.AutoFilterMode Then
            ws_employeelist.Range("A1").AutoFilter
        End If
        
        ws_outputfile.Cells(num_row, 1) = ws_positionlist.Cells(num_row, 1)

        ws_employeelist.Range("A1").AutoFilter field:=6, Criteria1:=ws_positionlist.Cells(num_row, 1)
    
        ws_outputfile.Cells(num_row, 2) = WorksheetFunction.Subtotal(3, ws_employeelist.Columns(6)) - 1
    Next num_row
    
    ws_outputfile.Range("A1").CurrentRegion.Borders.LineStyle = xlContinuous

    wb_inputfile.Close SaveChanges:=False
    
    wb_outputfile.Close SaveChanges:=True, Filename:=path_outputfile
    
    MsgBox "役職コード毎の社員数一覧ファイルを作成しました。"
    
    ws_macro.Activate
    
    ThisWorkbook.Save
    
    Application.ScreenUpdating = True
    
End Sub

上記のマクロを実行すると、以下の結果となります。

[応用編] ソースコードの解説

開始行数	終了行数	処理概要
21	31	マクロシートに記載された入力ファイルをOpenし、入力ファイル用のワークブック変数、ワークシート変数を設定する。
32	40	マクロシートに記載された出力ファイルをOpenし、出力ファイル用のワークブック変数、ワークシート変数を設定する。
41	46	出力シートの見栄えについて、整形を行っている。
48	58	社員一覧シートで部署コード毎の社員数をカウントし、出力シートに出力している。
60	60	出力シートの値がセットされている領域に罫線をセットしている。
62	64	入力ファイルと出力ファイルをCloseしている。
66	72	後処理を行っている。

この記事を書いた人

overdoser

某IT企業に勤務。

2年間の休職を経て、一から独学でExcel VBAを習得し、半年でExcel VBA エキスパート資格のスタンダードに一発合格。

社内の非効率的な作業を効率化するExcelマクロの開発を、全行程まるっと一人で請け負い、これまでに約100個のExcelマクロを開発し、現在も増産中。