EXCEL VBA表をグループ化による集計・小計・合計(データ集計・グループ化の設定/解除:SubTotalメソッド)

 

 

EXCEL VBA 表をグループ化による集計・小計・合計 (データ集計・グループ化の設定・解除:SubTotalメソッド)

 

 

 ●はじめに

今回説明するのは、表をグループ化による集計をEXCEL VBAを利用して行います。普段業務等でデータを集計する事があると思いますが、このグループ化による集計を使う事で簡単および素早く集計する事ができます。一から集計するブログラムを作成するよりも簡単に作成する事ができます。それでは、サンプルプログラムを交えて順番に説明致します。

 

●【連想配列でクロス集計・項目別集計は、下記を参照して下さい】

EXCEL VBA 連想配列でクロス集計・項目別集計・グループ集計 (Scripting.Dictionary) テクニック

●【SubTotalメゾットについては、下記を参照して下さい(Microsoft社 様)】
https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.subtotal

 

 ●書式の説明 (SubTotalメソッド)

● SubTotalメソッドを利用するには、下記の通りに設定を行います。

● オブジェクト.Subtotal (GroupBy, Function, TotalList, Replace, PageBreaks, SummaryBelowData)

 

● SubTotalメゾットの引数について

引数必須・省略データ型説明
GroupBy必須Longグループ化の基準となるフィールドの番号を整数で指定します。
Function必須XlConsolidationFunction
※別表参考
小計関数を指定します。
TotalList必須Variant集計をフィールドを指定します。集計するフィールドは、複数指定する事ができます。
Replace省略可能Variant既存の集計表と置き換えるには、Trueを指定します。既定値は、Trueです。
PageBreaks省略可能Variantグループごとに改ページ指定するか設定します。Trueは、改ページします。Falseは、改ページしません。既定値は、Falseです。
SummaryBelowData省略可能XlSummaryRow
※別表参照
集計データを小計を上が下に指定します。

● 集計方法の定数一覧(Function)

定数説明
xlAverage-4106平均
xlCount-4112カウント
xlCountNums-4113カウント数値のみ。
xlDistinctCount11Distinct Count 分析を使ったカウント
xlMax-4136最大
xlMin-4139最小
xlProduct-4149
xlStDev-4155標本に基づく標準偏差
xlStDevP-4156母集団全体に基づく標準偏差
xlSum-4157合計
xlUnknown1000小計に使用する関数は指定されません。
xlVar-4164標本に基づく変動
xlVarP-4165母集団全体に基づく変動

● 集計位置について(SummaryBelowData)

定数説明
xlSummaryAbove0集計行は、上に設置します。
xlSummaryBelow1集計行は、下に設置します。

【使用例】(サンプルプログラム)

 

 

【注意点】

● SubTotalメゾット(グループ化・集計)を実行する際は、集計対象(グループ化:GroupBy)の列は並び替えを実行前に(昇順又は、降順)整列する必要があります。

● 集計対象列を並び替えした場合としない場合

【並び替えを行った場合】・・・正常に集計します。

【並び替えを行わなった場合】・・・集計がバラバラになってしまう。(集計項目が重複する)

 

 

 

EXCEL VBA 表をグループ化(集計対象を1項目)による集計・小計・合計 (データ集計・グループ化の設定)

 

 

 ●プログラム説明 (サンプル①)

下記のサンプルプログラムは、表をグループ化して集計対象1項目による集計・小計・合計を行います。SubTotalメゾットを利用してデータをグループ化する事で簡単に集計する事ができます。それでは、順番に説明いたします。


【プログラムの流れ】

① 集計結果が表示されるワークシート「集計」をクリアーします。
② ワークシート「元データ」に登録されてる表の範囲全体をコピーします。
③ ワークシート「集計」のセル「A1」を指定して、先ほどコピーした内容を貼り付けます。(文字列のみ貼り付け)
④ ワークシート「集計」に貼り付けたデータのA列「勘定科目」ごとに集計します。(C列:税抜き・D列:消費税・E列:合計)
⑤ ワークシート「集計」の アウトラインをクリアー(削除)
⑥ ワークシート「集計」の表範囲に罫線を引く
⑦ ワークシート「集計」A列の最終行と1行目の最終列を取得します。
⑧ ワークシート「集計」の1行目に背景色33の色を塗りつぶします。
⑨ ワークシート「集計」のB列が空白なら集計行なので背景色34の色を塗りつぶします。
⑩ ワークシート「集計」の」C列~E列(最終列)までセルの表示形式を「”#,##0″」にします。
⑪ ワークシート「集計」の表の最終行に背景色35の色を塗りつぶします。
⑫ ワークシート「集計」をアクティブにする(前面表示)

【プログラム実行条件】

● ワークシート名:「元データ」を作成して集計元となるデータを登録します。
● ワークシート名:「集計」を作成します。(ワークシート作成のみ)

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
 Grouping①

 

 

 

●実行前~実行後 ※プログラム実行後、ワークシート「元データ」を元にワークシート「集計」に勘定科目ごとに集計されました。
(画面クリックして拡大)

 

 

 

EXCEL VBA 表をグループ化(集計対象を2項目)による集計・小計・合計 (データ集計・グループ化の設定)

 

 

 ●プログラム説明 (サンプル②)

下記のサンプルプログラムは、表をグループ化して集計対象2項目による集計・小計・合計を行います。SubTotalメゾットを利用してデータをグループ化する事で簡単に集計する事ができます。それでは、順番に説明いたします。


【SubTotalメゾット】の記入方法 (サンプル①とサンプル②の比較)
① サンプル①の場合
.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 4, 5)
【説明】GroupBy=1は、A列をグループ化・TotalListでArrayを使う事で複数列(C,D,E)を集計対象にする事ができます。

② サンプル②の場合
.Subtotal GroupBy:=Array(1, 2), Function:=xlSum, TotalList:=Array(3, 4, 5)
【説明】GroupBy=Array(1, 2)は、A列・B列がグルー化・TotalListでArrayを使う事で複数列(C,D,E)を集計対象にする事ができます。
※Arrayを使う事で複数列をグループ・集計対象にする事が出来ます。

【プログラムの流れ】(※基本、プログラムの流れは、サンプル①とほぼ同じです。)

① 集計結果が表示されるワークシート「集計」をクリアーします。
② ワークシート「元データ」に登録されてる表の範囲全体をコピーします。
③ ワークシート「集計」のセル「A1」を指定して、先ほどコピーした内容を貼り付けます。(文字列のみ貼り付け)
④ ワークシート「集計」に貼り付けたデータのA列「勘定科目」+B列「内容」ごとに集計します。(C列:税抜き・D列:消費税・E列:合計)
⑤ ワークシート「集計」の アウトラインをクリアー(削除)
⑥ ワークシート「集計」の表範囲に罫線を引く
⑦ ワークシート「集計」B列の最終行と1行目の最終列を取得します。(集計列がB列のため)
⑧ ワークシート「集計」の1行目に背景色33の色を塗りつぶします。
⑨ ワークシート「集計」のA列が空白なら集計行なので背景色34の色を塗りつぶします。
⑩ ワークシート「集計」の」C列~E列(最終列)までセルの表示形式を「”#,##0″」にします。
⑪ ワークシート「集計」の表の最終行に背景色35の色を塗りつぶします。
⑫ ワークシート「集計」をアクティブにする(前面表示)

【プログラム実行条件】

● ワークシート名:「元データ」を作成して集計元となるデータを登録します。
● ワークシート名:「集計」を作成します。(ワークシート作成のみ)

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。

● Grouping②

 

 

 

 

 

 

 

●実行前~実行後 ※プログラム実行後、ワークシート「元データ」を元にワークシート「集計」に勘定科目+内容ごとに集計されました。
(画面クリックして拡大)

 

 

 

EXCEL VBA 表をグループ化 日付による集計・小計・合計 (データ集計・グループ化の設定)

 

 

 ●プログラム説明 (サンプル③)

下記のサンプルプログラムは、サンプルプログラム①の応用として表をグループ化して日付を利用した集計対象1項目による集計・小計・合計を行います。SubTotalメゾットを利用してデータをグループ化する事で簡単に集計する事ができます。今回のプログラムでは、日付をグループ化する時に日付がシリアル値にならないように改良しています。それでは、順番に説明いたします。

①【日付データのまま集計を実行】

②【日付データを文字列に変更して集計を実行】

【改善点】

① ワークシート「元データ」のA列の日付データを日付から文字列に変更する。
② ワークシート「元データ」のA列の日付データが登録されているセルの書式設定を文字列に変更する。
③ ①で日付データから文字列に変更したデータをA列に文字列データとして転記する。

【プログラム実行条件】

● ワークシート名:「元データ」を作成して集計元となるデータを登録します。
● ワークシート名:「集計」を作成します。(ワークシート作成のみ)

 

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。

● Grouping③

 

 

 

 

●実行前~実行後 ※プログラム実行後、ワークシート「元データ」を元に日付ごとにデータが集計されました。
(画面クリックして拡大)

 

 

 

 

最後まで、ご覧いただきまして誠に有難うございました。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。