オートフィルターで抽出した件数や数値合計を求める方法
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の値を使用します。※フィルターで非表示になったセルの場合は常に集計されません。値
非表示セル
含める値
非表示セル
含めない集計方法 同等の
ワークシート関数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 - 集計範囲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
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
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【メッセージボックス】関数を使用して変数「合計」に格納されている値を表示します。
実行結果
以上で、オートフィルターで抽出した件数や数値合計を求める方法についての解説を終了します。ありがとうございました。