連載
» 2014年07月11日 18時00分 UPDATE

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

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

[薬師寺国安,PROJECT KySS]
「VBA/マクロ便利Tips」のインデックス

連載目次

※本Tipsの環境:Windows 8.1 Enterprise(64ビット)+Excel 2013


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

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

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

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

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

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

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

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

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

ExcelVBATips8_03.png 図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だ。

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

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

Sub オートフィルターの解除()
  Range("A1").AutoFilter
  Range("A17").Value = ""
  Worksheets("入力画面").Range("C2").Value = ""
End Sub
リスト2 オートフィルターを解除するコード

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

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

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

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

ExcelVBATips8_05.png 図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行だけ追加しておく。

Range("F1").Value = ""
リスト4

 これで「合計金額」の表示されたセルが空になる。

ExcelVBATips8_06.png 図6 「北海道」で抽出した「合計金額」が表示された

 次回も引き続き、「オートフィルター」関連のTipsを紹介する予定だ。お楽しみに。

著者プロフィール

kuniyasu.jpg

薬師寺 国安(やくしじ くにやす) / 薬師寺国安事務所

薬師寺国安事務所代表。Visual Basicプログラミングと、マイクロソフト系の技術をテーマとした、書籍や記事の執筆を行う。

1950年生まれ。事務系のサラリーマンだった40歳から趣味でプログラミングを始め、1996年より独学でActiveXに取り組む。

1997年に薬師寺聖とコラボレーション・ユニット「PROJECT KySS」を結成。

2003年よりフリーになり、PROJECT KySSの活動に本格的に参加。.NETやRIAに関する書籍や記事を多数執筆する傍ら、受託案件のプログラミングも手掛ける。

Windows Phoneアプリ開発を経て、現在はWindowsストアアプリを多数公開中。

Microsoft MVP for Development Platforms - Client App Dev(Oct 2003-Sep 2012)。

Microsoft MVP for Development Platforms - Windows Phone Development(Oct 2012-Sep 2013)。

Microsoft MVP for Development Platforms - Client Development(Oct 2013-Sep 2014)。


Copyright© 2017 ITmedia, Inc. All Rights Reserved.

@IT Special

- PR -

TechTargetジャパン

この記事に関連するホワイトペーパー

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。