Excelのオートフィルターで絞り込んだデータの件数と合計を関数で求める：VBA／マクロ便利Tips

業務効率化に役立つVBA／マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、AutoFilterメソッドで絞り込んだデータに対してさまざまな集計関数を使うことができるワークシート関数Subtotalの使い方を解説します。

» 2014年07月11日 18時00分公開

[薬師寺国安，PROJECT KySS]

連載目次

※本Tipsの環境：Windows 8.1 Enterprise（64ビット）＋Excel 2013

　今回もTips「Excelのオートフィルターでデータ抽出条件を設定／解除」同様「オートフィルター」に関するTipsを紹介する。「オートフィルター」は、大量の「社員データ」や「顧客データ」や「商品データ」から、任意のデータを抽出する場合に、大いに力を発揮する。

　今回のTipsは、日常の業務処理において必ず必要とされるものである。知っているのと、知らないのとでは、処理効率に大きな差が出ることは間違いない。

SubTotal関数でオートフィルターで絞り込んだデータの件数を表示

　今回は、オートフィルターの設定を行う前に、Sheet1のワークシート名を「入力画面」に変更し、図1の赤○で囲んだアイコンをクリックして新しいシートを挿入し、その名前を「抽出画面」としておく（図1）。

図1　「入力画面」と「抽出画面」という2つのシートを作成しておく

　「入力画面」シートには「都道府県」を入力するセルを用意し、Excelメニューの［挿入］→［図形］を選択して「角丸四角形」を配置する。テキストは「実行」としておく（図2）。

図2　「都道府県」を入力するセルと「実行」ボタンを配置した

　「抽出画面」シートには「都道府県」と「氏名」と「金額」のデータを表示させ、下の方に「抽出件数」を表示させるセルと、「オートフィルターの解除」ボタンを配置している（図3）。

　図2、図3共にいえることだが、書式の設定や配置は自由に変更してよい。ただし、セルの位置などのように、処理に関わる部分を変更した場合は、VBA中のセルを指定するコードも変更する必要がある。

図3　「都道府県」と「氏名」と「金額」のデータが表示されている。下の方に「抽出件数」を表示させるセルと「オートフィルターの解除」ボタンを配置している

　「オートフィルター」で絞り込んだデータの件数を表示させるには、WorksheetFunctionオブジェクトのSubtotal関数に引数を指定する。

WorksheetFunction.Subtotalの書式

WorksheetFunction.Subtotal(｛集計方法番号｝,｛参照1｝,｛参照2｝,……)

　Subtotal関数の｛集計方法番号｝には、表1のような値を指定する。｛参照1｝｛参照2｝……には集計関数の対象となるセルを指定する。

表1　集計関数
｛集計方法番号｝	集計関数	説明
1	AVERAGE	平均
2	COUNT	数値の個数
3	COUNTA	データの個数
4	MAX	最大値
5	MIN	最小値
6	PRODUCT	積
7	STDEV	標本標準偏差
8	STDEVP	母標準偏差
9	SUM	合計
10	VAR	標本分散
11	VARP	母分散
参考：SUBTOTAL - Excel

　VBE（Visual Basic Editor）を起動してModule1内にリスト1のコードを記述する。

Option Explicit
Sub 抽出件数の表示()
  Dim 件数 As Long
  件数=0
  If Range("C2").Value = "" Then
    MsgBox "都道府県を指定してください。"
    Exit Sub
  Else
    Worksheets("抽出画面").Range("A1").AutoFilter Field:=1, Criteria1:=Worksheets("入力画面").Range("C2").Value
    件数 = WorksheetFunction.Subtotal(2, Worksheets("抽出画面").Range("C:C"))
    Worksheets("抽出画面").Range("A17").Value = 件数 & "件"
    Worksheets("抽出画面").Select
  End If
End Sub

リスト1　オートフィルターで、抽出したデータ件数を表示するコード

　以降、リスト1の中身を解説しよう。

　まず、3行目でLong型の変数「件数」を宣言している。次に、5行目の条件分岐で「都道府県」を入力するセルに何も入力されていない場合は、警告メッセージを表示して処理を抜ける。それ以外は8～11行目の下記の処理を行う。

　8行目では「抽出画面」シートの「A1」のセル（見出し）に対して、AutoFilterの「Field」に「1（A列）」、つまり「都道府県」列を指定し、「Criteria1」に「入力画面」シートの、「都道府県」セルに入力された値を指定して、入力された「都道府県」で抽出処理を行っている。

　9行目では変数「件数」に、「WorksheetFunction.Subtotal(2, Worksheets("抽出画面").Range("C:C"))」と指定して、「集計方法」に表1の「2」、つまり「COUNT」を指定して、「数値の個数」を求めている。「参照先」には「金額」を表示している「C列」を指定する。これで取得できた件数を変数「件数」に格納する。

　最後に、10行目で「抽出画面」シートの「A17」のセルに変数「件数」の値を表示し、11行目で「抽出画面」シートを選択して表示する。

　このマクロを「入力画面」シートの「実行」ボタンに関連付け、実行した結果が図4だ。

図4　「都道府県」に「愛媛県」を指定して、抽出件数が表示された

　図4の「オートフィルターの解除」ボタンにはリスト2のマクロを関連付ける。

Sub オートフィルターの解除()
  Range("A1").AutoFilter
  Range("A17").Value = ""
  Worksheets("入力画面").Range("C2").Value = ""
End Sub

リスト2　オートフィルターを解除するコード

　オートフィルターを解除し、「抽出画面」シートの「A17」のセルを空にし、「入力画面」シートの「C2」セルを空にする。

SubTotal関数でオートフィルターで絞り込んだデータの合計を表示

　さらに、抽出結果の「金額」の合計を求めてみよう。絞り込んだ結果の合計を求める書式は、前述の「SubTotal関数でオートフィルターで絞り込んだデータの件数を表示」と同じだ。

「抽出画面」シートのレイアウトを少し変更して、「合計金額」表示用のセルを用意しておく（図5）。

図5　「抽出画面」シートに「合計金額」表示用のセルを用意する

　ここで注意しなければならないのは、「合計金額」を表示させるセルを「C列」の「552,154」の金額下に設定してはならないということだ。「C列」に合計金額を表示させると、「抽出件数」に「合計金額」分まで追加されて、件数が1件増え、「合計金額」にも誤った数字が表示されてしまう。これは、表1の「番号」が「2」の「COUNT」を使って「数値の個数」を表示させているためだ。

　抽出したデータに合計を表示させるには、リスト1のコードにリスト3のコードを追加する。

Sub 抽出件数の表示()
  Dim 件数 As Long
  Dim 合計 As Long
  件数 = 0
  合計 = 0
～コード略～
  件数 = WorksheetFunction.Subtotal(2, Worksheets("抽出画面").Range("C:C"))
  合計 = WorksheetFunction.Subtotal(9, Worksheets("抽出画面").Range("C:C"))
  Worksheets("抽出画面").Range("A17").Value = 件数 & "件"
  Worksheets("抽出画面").Range("F1").Value = 合計
  Worksheets("抽出画面").Select
～コード略～
End Sub

リスト3　「合計」を求めるコード（リスト1に追加）

　リスト1と同様にWorksheetFunction.Subtotal関数を使用し、8行目の「集計方法」に前述の表1の「9」を指定して「SUM」すなわち「合計」を求めている。

　「合計」は10行目のように「F1」セルに表示する。合計を表示するセルには、あらかじめExcelメニューから、「桁区切り書式」を適用しておく。プログラムから3桁区切りを設定しているわけではない。

　また、リスト2の「オートフィルターの解除」のVBAには、リスト4の1行だけ追加しておく。