アクセス上位ページ
 
最近の更新内容

Excel VBA ≫ 6.ワークシート関数 - 特徴と使用例(1)-e.オートフィルタを利用した集計処理

オートフィルタを利用した集計処理

このページでは Subtotal 関数について説明します。

Subtotal 関数は第1引数に集計機能に対応した数値を指定することで、さまざまな集計処理が行える関数です。 例えば、合計処理を行いたい場合、第1引数に"9"を指定して次のように使用します。

sum = WorksheetFunction.Subtotal( 9, Range("B4:B13") )




合計以外にも平均値の計算やセルのカウント、最大値・最小値の取得などが可能です。以下に第1引数とその集計機能の対応表を記載します。

Subtotal 関数の第1引数に指定可能な数値と
その集計機能との対応表
集計方法 集計機能 対応する関数
1平均計算AVERAGE
2数値セルのカウントCOUNT
3入力済セルのカウントCOUNTA
4最大値取得MAX
5最初値取得MIN
6積計算PRODUCT
7標本による標準偏差計算STDEV
8母集団による標準偏差計算STDEVP
9合計計算SUM
10標本による分散計算VAR
11母集団による分散計算VARP


しかし、ただ合計するだけなら Sum 関数を使った方がコードを見たときにわかりやすいです。 では、Subtotal 関数を使用する理由は何でしょうか?

それは、オートフィルタによって非表示となった行を自動的に集計対象外としてくれる機能があるためです。 この機能は上記表のすべての集計機能に適用されます。

また、Subtotal 関数の第1引数には、前述した1〜11の数値以外にも、これらの数値に100を加えた101〜111の数値を指定することが可能です。 この100を加えた数値を指定すると、手動で非表示(オートフィルタは使用しない)に設定した行を集計対象外とすることもできます。

これらの使用方法は後述の使用例で取り上げていますので、その違いを理解していただけると思います。


Subtotal 関数の第1引数に指定可能な数値と
その集計機能との対応表(非表示行集計対象外)
集計方法 集計機能 対応する関数
101平均計算AVERAGE
102数値セルのカウントCOUNT
103入力済セルのカウントCOUNTA
104最大値取得MAX
105最初値取得MIN
106積計算PRODUCT
107標本による標準偏差計算STDEV
108母集団による標準偏差計算STDEVP
109合計計算SUM
110標本による分散計算VAR
111母集団による分散計算VARP


Subtotal 関数

  • 【構文】
    WorksheetFunction.Subtotal( 集計方法, 範囲1 [, 範囲2…] )

      【戻り値】 倍精度浮動小数点型 (Double)


  • 引数名 省略 説明
    集計方法 × 前述の集計方法を指定します。 集計方法にかかわらず、オートフィルタにより非表示となった行は集計対象外となります。
    範囲 集計対象のセル範囲(レンジオブジェクト)を指定します。
         ※…1つ目は省略不可。2つ目以降は任意。

  • 使用例
  • 以下のマクロはワークシート上にある売上表において、1月売上の合計値を3種類の方法で計算してその計算結果を比較するものです。

    ・合計方法[1]・・・Sum 関数を使用
    ・合計方法[2]・・・Subtotal 関数を使用(第1引数 = 9)
    ・合計方法[3]・・・Subtotal 関数を使用(第1引数 = 109)
    Sub sample_wf015_01()
        Const C_SUM1 = 9
        Const C_SUM2 = 109
        Dim sumRng As Range
        Dim s As Double
    
        '集計対象範囲の設定
        Set sumRng = Range("B4:B13")
    
        '[1]Sum関数
        s = WorksheetFunction.sum(sumRng)
        Debug.Print "[1]合計 " & s
    
        '[2]Subtotal 集計方法=9
        s = WorksheetFunction.Subtotal(C_SUM1, sumRng)
        Debug.Print "[2]合計 " & s
    
        '[1]Subtotal 集計方法=109
        s = WorksheetFunction.Subtotal(C_SUM2, sumRng)
        Debug.Print "[3]合計 " & s
    End Sub
    
    ⇒ サンプルマクロ集 へ


  • <ケース1>
  • 特にオートフィルタや、非表示行がない通常のケースです。 当然、3種類の合計値はすべて同じになります。

    ※赤色点線範囲が合計対象範囲 全店舗1〜3月分売上表 <実行結果(イミディエイトウィンドウ)>
    [1]合計 1589
    [2]合計 1589
    [3]合計 1589
    


  • <ケース2>
  • 店舗名でオートフィルタをかけたケースです。 Subtotal 関数を使用した方法は、表示されている行だけ合計されていることがわかります。

    ※赤色点線範囲が合計対象範囲 全店舗1〜3月分売上表(オートフィルタあり) <実行結果(イミディエイトウィンドウ)>
    [1]合計 1589
    [2]合計 527
    [3]合計 527
    


  • <ケース3>
  • 手動で行を非表示にしたケースです。 Subtotal 関数を使用し、第1引数に109を指定した方法のみ、表示されている行だけ合計されていることがわかります。

    ※赤色点線範囲が合計対象範囲 全店舗1〜3月分売上表(非表示行あり) <実行結果(イミディエイトウィンドウ)>
    [1]合計 1589
    [2]合計 1589
    [3]合計 1062
    


【補足】Subtotal 関数を使用せずに集計する

Subtotal 関数は集計の条件が指定できないため、実際に Subtotal 関数を使える場面はかなり限られてくると思います。 そんなときは Hidden プロパティ により行が表示されているか否かを判別し、ループ処理で集計します。 Hidden プロパティ の使い方は以下のサイト内リンクを参考にしてください。

カテゴリ内ページ



Page
Top