Excel VBA オートフィルターで抽出した件数や数値合計を求める

スポンサーリンク
スポンサーリンク

オートフィルターで抽出した件数や数値合計を求める方法

VBAでオートフィルターを表す
AutoFilterオブジェクトには
「オートフィルターで抽出した結果の件数」を返す
プロパティはありません。
また、抽出した結果の可視セルの数値を
集計するメソッドやプロパティもありません。

これらの処理を実行するには
ワークシート関数の
SUBTOTAL【サブトータル】関数をVBAに呼び出して使用します。

WorksheetFunction【ワークシートファンクション】プロパティ

ワークシート関数のコンテナ(入れ物)を表す
WorksheetFunction【ワークシートファンクション】オブジェクトを取得するには
Application【アプリケーション】オブジェクトの
WorksheetFunction【ワークシートファンクション】プロパティを使用します。

WorksheetFunction【ワークシートファンクション】プロパティの書式

[]内は省略可能です。
[オブジェクト.]WorksheetFunction


設定値の説明

  • オブジェクト(省略可)
    Application【アプリケーション】オブジェクトを指定します。通常は省略します。

ワークシート関数のSubtotal【サブトータル】関数

VBAでワークシート関数の
Subtotal【サブトータル】関数を使用するには
Application【アプリケーション】オブジェクトの
WorksheetFunction【ワークシートファンクション】プロパティを使用して
取得した、ワークシート関数のコンテナ(入れ物)を表す
WorksheetFunction【ワークシートファンクション】オブジェクトの

Subtotal【サブトータル】関数を使用します。

Subtotal【サブトータル】関数の書式

[]内は省略可能です。
オブジェクト.Subtotal( 集計方法, 集計範囲1[, 集計範囲2,・・・] )

  • オブジェクト(必須)
    ワークシート関数のコンテナ(入れ物)を表す
    WorksheetFunction【ワークシートファンクション】オブジェクトを指定します。
  • 集計方法(必須)
    以下の集計方法を表す値を設定します。
    手動でセルを非表示にした場合の
    非表示セルを集計に含める場合は1~11の値
    非表示セルを集計に含めない場合は101~111の値を使用します。
    ※フィルターで非表示になったセルの場合は常に集計されません。


    非表示セル
    含める

    非表示セル
    含めない
    集計方法同等の
    ワークシート関数
    1101平均値AVERAGE
    2102数値のあるセル個数COUNT
    3103空白を除くセル個数COUNTA
    4104最大値MAX
    5105最小値MIN
    6106PRODUCT
    7107標本の標準偏差STDEV
    8108母集団全体の標準偏差STDEVP
    9109合計SUM
    10110標本による分散の予測値VAR
    11111母集団全体の分散VARP
  • 集計範囲1(必須)
    集計する範囲をRange【レンジ】オブジェクトで指定します。
  • 集計範囲2,・・・(省略可)
    次の集計する範囲をRangeオブジェクトで指定します。
    追加の集計範囲は2~254個まで設定できます。

オートフィルターで抽出したデータ件数を取得するコード例

Sub 結果件数()
ActiveSheet.AutoFilterMode = False
Dim 結果件数 As Long
Range("A1").AutoFilter field:=1, Criteria1:=">=1", _
                Operator:=xlAnd, Criteria2:="<=5"
結果件数 = WorksheetFunction.Subtotal(3, Columns(1)) - 1
MsgBox "抽出結果は" & 結果件数 & "件です。"
End Sub

コードの解説

2行目
【ActiveSheet.AutoFilterMode = False】

Worksheetオブジェクトの
AutoFilterMode【オートフィルターモード】プロパティ
Falseを設定してオートフィルターモードを解除します。


3行目
【Dim 結果件数 As Long】

ワークシート関数のSubtotal【サブトータル】関数で取得した
空白を除くセル個数を格納する
変数「結果件数」を
長整数型(Long)で宣言します。


4行目~5行目
【Range(“A1″).AutoFilter field:=1, Criteria1:=”>=1″, _
Operator:=xlAnd, Criteria2:=”<=5″】

Range【レンジ】オブジェクトの
AutoFilter【オートフィルター】メソッドを使用して
A1セルから始まるアクティブセル領域の1列目を
1以上5以下の条件でデータを抽出します。


6行目
【結果件数 = WorksheetFunction.Subtotal(3, Columns(1)) – 1】

Application【アプリケーション】オブジェクトの
WorksheetFunction【ワークシートファンクション】プロパティで取得した
ワークシート関数のコンテナ(入れ物)を表す
WorksheetFunction【ワークシートファンクション】オブジェクトの
ワークシート関数を表す
Subtotal関数を使用して集計します。
第1引数の集計方法は3の空白セルを除くセルの個数を指定します。
第2引数の集計範囲はRaneg【レンジ】オブジェクトの
列を表す、Columns【カラムズ】プロパティの
引数に1を設定して1列目を参照して
1列目の空白ではないセルの個数を取得して
1行目の見出し行を除くため1を減算して
変数「結果件数」に代入します。


7行目
【MsgBox “抽出結果は” & 結果件数 & “件です。”】

MsgBox関数を使用して変数「結果件数」に格納されている
値を表示します。


実行結果


オートフィルターで抽出したデータの値の合計を取得するコード例

Sub 合計()
ActiveSheet.AutoFilterMode = False
Dim 合計 As Long
Range("A1").AutoFilter field:=1, Criteria1:=">=1", _
                Operator:=xlAnd, Criteria2:="<=5"
合計 = WorksheetFunction.Subtotal(9, Columns(1))
MsgBox "合計は" & 合計 & "です。"
End Sub

コード例

2行目
【ActiveSheet.AutoFilterMode = False】

Worksheetオブジェクトの
AutoFilterMode【オートフィルターモード】プロパティ
Falseを設定してオートフィルターモードを解除します。


3行目
【Dim 合計 As Long】

ワークシート関数のSubtotal【サブトータル】関数で取得した
選択範囲の合計を格納する
変数「合計」を
長整数型(Long)で宣言します。


4行目~5行目
【Range(“A1″).AutoFilter field:=1, Criteria1:=”>=1″, _
Operator:=xlAnd, Criteria2:=”<=5″】

Range【レンジ】オブジェクトの
AutoFilter【オートフィルター】メソッドを使用して
A1セルから始まるアクティブセル領域の1列目を
1以上5以下の条件でデータを抽出します。


6行目
【結果件数 = WorksheetFunction.Subtotal(9, Columns(1))】

Application【アプリケーション】オブジェクトの
WorksheetFunction【ワークシートファンクション】プロパティで取得した
ワークシート関数のコンテナ(入れ物)を表す
WorksheetFunction【ワークシートファンクション】オブジェクトの
ワークシート関数を表す
Subtotal関数を使用して集計します。
第1引数の集計方法は9の合計を指定します。
第2引数の集計範囲はRaneg【レンジ】オブジェクトの
列を表す、Columns【カラムズ】プロパティの
引数に1を設定して1列目を参照して
1列目の可視セル合計取得して
変数「合計」に代入します。


7行目
【MsgBox “合計は” & 合計 & “です。”】

MsgBox関数を使用して変数「合計」に格納されている
値を表示します。

実行結果


以上で
オートフィルターで抽出した件数や数値合計を求める
方法についての解説を終了します。
ありがとうございました。

スポンサーリンク
スポンサーリンク

フォローする

スポンサーリンク
スポンサーリンク