【事例で学ぶExcel VBA #3】データの個数を自動集計する

ツールの全体像

まずは任意のエクセルシートを開いて、上記のシートを予め作成しておきます。

マクロ実行用の「データ集計」ボタンも配置しておきます。本来はエクセルに

CSVを取り込む処理も実装するのですが、これは既に#1で扱っているので

取り込まれている前提でマクロを実装しています。予めご了承くださいm(_ _)m

本ツールのVBAコードは以下の通りです。

VBAコード ※パソコンでの閲覧推奨

Option Explicit
'===================================================
'ツール名：データ数の集計ツール（まえきんブログ掲載用にカスタマイズ）
'作成日：2022/03/12
'処理概要：データ数の集計
'付属機能：なし
'作成者：まえきん
'===================================================
'===================================================
'定数宣言
'===================================================
Const syukei As String = "データ集計"
'===================================================
'関数名　：メイン関数1（データ集計ボタン）
'処理内容：
'トリガー：データ集計ボタンを押下した際にコールされる
'引数　　：なし
'戻り値　：なし
'===================================================
Private Sub CommandButton1_Click()
'----------
'データ集計処理
'----------
'データ集計関数をコール
'データAの集計
Call DataCount("A", 5)
'データBの集計
Call DataCount("B", 6)
'データCの集計
Call DataCount("C", 7)
'----------
'処理完了通知
'----------
MsgBox "データ集計が完了しました！"
End Sub
'===================================================
'関数名　：データ集計関数
'処理内容：指定のデータフラグ番号の抽出と抽出データの数をカウント
'トリガー：データ集計関数コール時
'引数　　：dataflg…集計データフラグ値、retu…データ数を表示する列
'戻り値　：なし
'===================================================
Sub DataCount(dataflg As String, retu As Integer)
'----------
'変数宣言
'----------
Dim Kosu As Long ' 集計データ数
'----------
'データ抽出
'----------
With Worksheets(syukei)
'指定の集計データフラグ値によるオートフィルターでデータ抽出
.Range("A1").AutoFilter Field:=1, Criteria1:=dataflg
'フィルタで抽出したデータの数をカウント（ヘッダーレコードを除くため-1する）
Kosu = WorksheetFunction.Subtotal(3, .Range("A1").CurrentRegion.Columns(1)) - 1
'集計データ数をセット
.Cells(2, retu).Value = Kosu
'オートフィルターを解除
.ShowAllData
End With
End Sub

VBAコード解説

データ集計処理

今回の集計内容として、データA、B、Cが一覧表の中に各々何個あるかを調べます。

今回はオートフィルターで対象のデータを絞り込み、Subtotal関数で対象のデータ数を

カウントする流れでマクロを実装する方法をとります。今回、集計する対象データが

3つあるので3回分上記の処理を記述することになりますが、全く同じ処理を3回長々と

書くのはVBAコードの可読性に欠けます。そこで同じ処理はサブルーチン化しましょう。

プロシージャは単体で実行するだけでなく、ほかのプロシージャから呼び出して実行が

可能です。いくつもの処理を含む１つの大きなプロシージャを作成するより、処理ごとの

１つの小さなプロシージャ（サブルーチン）を作成しておき、呼び出して実行するように

すれば、各プロシージャがシンプルになり、コードの保守性もグッと上がります😊

それが、このツール固有のサブルーチンであるDataCount関数になります。関数の引数に

「dataflg…集計データフラグ値（A／B／C）」、「retu…データ数を表示する列」を

与え、指定のデータフラグ番号の抽出と抽出データの数を表示します。

「Range」部分で抽出対象のセル、「AutoFilter Field:=」部分で抽出対象の列番号、

「Criteria1:=」部分で抽出対象のデータをそれぞれ指定します。

つづいて、「WorksheetFunction.Subtotal」部分でフィルタ抽出したデータの数を

カウントします。今回のケースでは先頭は見出しの行になっているので不要なレコードを

除くために総カウント数から-1することに注意してください。あとは、メイン関数の方で

Callステートメントでこの関数を呼び出し集計処理を実行することになります。

ツールの実行手順

①「データ集計」ボタンを押下する

実際にマクロを実行すると以下のような結果になります。

マクロ実行結果

処理完了通知

上記の通り、結果が得られましたでしょうか😊

今回ご紹介したこのツール、まだまだ完璧ではありません。勘のいい方はお気づきと

思いますが、そう、集計対象が増えるとその分関数をコールする回数が増えます orz

ただ、集計対象が少ない場合でサクッとボタン一つで集計したい！！っていう時には

十分に使えるツールなので落胆する必要はないと思います😅今のところ、実務でも

事足りるレベルなのでさらなるカスタマイズはしていません。データの数がどれだけ

多くても汎用的にデータの個数を集計できるツールはどのように実装すればいいのか？

それは、私自身もこれから考えていくべき課題です。これを考えていくプロセスもまた

Excel VBAを勉強する醍醐味であり、スキルアップにつながります。その先の実装に

ついて興味が湧いた方は、是非ご自身でもチャレンジしてみてください！！

今回はこれで以上です😊

最後まで読んで頂きありがとうございました！ではまた！