EXCEL VBA 指定した行列に小計を追加する。Subtotal関数または、配列を駆使して効率的な集計を行う方法

 

 

EXCEL VBA 指定した行列に小計を追加する。Subtotal関数または、配列を駆使して効率的な集計を行う方法

 

 

 

 ●はじめに

今回説明するのは、データや集計表等を作成した後に、小計を追加する方法を説明したいと思います。今回のサンプルプログラムでは、Subtotal関数または、配列を利用して指定した場所(行・列)及び指定した範囲で小計を集計する方法を説明いたします。詳しくは、サンプルプログラムを交えて順番に説明いたします。

●【EXCEL VBA セル範囲の串刺し集計・計算・カウント・平均(テクニック)、下記を参照して下さい】

EXCEL VBA セル範囲の串刺し集計・計算・カウント・平均(テクニック)

●【EXCEL VBA 連想配列で合計・グループ集計・別シート転記・重複削除 (Scripting.Dictionary)、下記を参照して下さい】

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

 

 

 

 

 

 

EXCEL VBA 指定した列に小計を追加する。Subtotal関数を駆使して効率的な集計を行う方法。Excel列の小計計算を自動化する方法

 

 

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

下記のサンプルプログラムはExcelシートの特定の列の小計を求め、その結果を新たに挿入した列に記入するものです。特定の列範囲を選択し、その範囲の小計を求める処理を行います。今回のサンプルプログラムでは、小計を求める範囲を2列設定しています。今回のプログラムでは、小計の計算にはExcelの組み込み関数「SUBTOTAL関数」を使用しています。

【プログラムの流れ】

 

上記のフローチャートの内容について簡単に説明します。

① メインプロシージャ AddSubTotal が開始します。
② Worksheetの設定を行います。ここでは使用するシートを指定します。
③ データが存在する最後の行を特定します。
④ 列に挿入し、ExcelのSubtotal関数を用いて小計を計算します。これを2回行います。
⑤ メインプロシージャが終了します。
また、小計を計算し、指定された位置に挿入するプロシージャ InsertColumnAndCalculate については以下のようになります。

① 指定された列に新しい列を挿入します。
② 小計のセルにSubtotal関数を入れて計算します。
③ ’小計’をセルに挿入します。
④ 集計行の背景色を薄い水色にします。
⑤ 罫線を引きます。
⑥ プロシージャが終了します。

Subtotal関数を使って小計を計算し、列に挿入する。列の小計挿入位置は、以下の通りに指定します。
① InsertColumnAndCalculate ws, 4, 2, 3, lastRow
※  ワークシート名を指定、4列目(D列)に小計を挿入する、2列目~3列目(B列~C列)を集計、最終行まで
② InsertColumnAndCalculate ws, 8, 5, 7, lastRow
※  ワークシート名を指定、8列目(H列)に小計を挿入する、5列目~7列目(E列~G列)を集計、最終行まで

【プログラム実行条件・注意事項】
① このコードは「Sheet1」という名前のワークシートを対象としています。必要に応じてワークシート名を変更してください。
② 小計を計算する列範囲は固定されています。必要に応じて、startColumnとendColumnの値を変更してください。
③ 計算結果を記入する列も固定されています。必要に応じて、insertColumnの値を変更してください。


★【サンプルプログラム】

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

 

 

 

 

●実行前~実行後 ※プログラム実行後、指定した範囲の列の小計が計算され、その結果が新たに挿入した列に記入されます。また、その列には背景色が設定され、罫線が引かれます。
(画面クリックして拡大)

 

 

 

 

EXCEL VBA 指定した行に小計を追加する。Subtotal関数を駆使して効率的な集計!自動小計挿入!

 

 

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

下記のサンプルプログラムは、データを指定した範囲で行ごとに集計し、その小計を新しく挿入した行に表示します。小計の計算はSubtotal関数を使用して、特定の範囲のデータを行ごとに集計します。小計が表示される行は、背景色を薄い水色にします。今回のサンプルプログラムでは、小計を求める範囲を2行設定しています。


【プログラムの流れ】

上記のフローチャートの内容について簡単に説明します。

① メインプロシージャ AddSubTotal が開始します。
② Worksheetの設定を行います。ここでは使用するシートを指定します。
③ データが存在する最後の列を特定します。
④ 行を挿入し、ExcelのSubtotal関数を用いて小計を計算します。これを2回行います。
⑤ メインプロシージャが終了します。
また、小計を計算し、指定された位置に挿入するプロシージャ InsertRowAndCalculate については以下のようになります。

① 指定された行に新しい行を挿入します。
② ExcelのSubtotal関数を用いて小計を計算し、新しい行に挿入します。
③ ’小計’をセルに挿入します。
④ 集計行の背景色を薄い水色にします。
⑤ プロシージャが終了します。

Subtotal関数を使って小計を計算し、行に挿入する。行の小計挿入位置は、以下の通りに指定します。
① InsertRowAndCalculate ws, 9, 2, 8, lastColumn
※  ワークシート名を指定、9行目に小計を挿入する、2行目~8行目を集計、最終列まで
② InsertRowAndCalculate ws, 17, 10, 16, lastColumn
※  ワークシート名を指定、17行目に小計を挿入する、10行目~16行目を集計、最終列まで

【プログラム実行条件・注意事項】
① このコードは指定のワークシート名(この例では “Sheet1″)に依存しているため、シート名が異なるとエラーになります。
② 挿入行を指定する部分(この例では 9行目と17行目)は固定値となっているため、必要に応じて調整する必要があります。
③ 現在のコードは集計範囲が固定の範囲となっており、範囲が変動する場合は適切に修正する必要があります。

 

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

 

 

 ●実行前~実行後 ※プログラム実行後、指定されたワークシートの指定された行に新しい行が挿入され、その行には特定の範囲のデータの小計が表示されます。小計は指定した範囲の列ごとに計算され、小計が挿入された行の背景色が薄い水色に変わります。
(画面クリックして拡大)

 

 

 

 

EXCEL VBA 列の小計を自動計算する方法。配列を駆使して効率的な集計を行う。

 

 

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

下記のサンプルプログラムは、上記のサンプルプログラム①の応用になります。このコードは、Excelのワークシートにおける特定の列の小計を計算し、その結果を新たな列に挿入するためのプログラムですが、このプログラムでは、まず指定されたワークシートと列の範囲を取得します。次に、指定した配列内のデータを行ごとに加算して指定した場所に小計を計算し、その結果を新たに挿入した列に書き込みます。小計の部分に関数を表示しない時に利用する場合に便利です。

【プログラムの流れ】
上記のフローチャートの内容について簡単に説明します。

  • 開始
  • AddSubTotal プロシージャ
    1. ワークシートと列の範囲を設定
    2. 新たな列を挿入し、配列を用いて計算
  • InsertColumnAndCalculate プロシージャ
    1. 列を挿入
    2. 指定範囲のデータを配列に読み込む
    3. 配列内のデータを行ごとに加算
    4. 計算結果を新たな列に書き込む
    5. 小計の列のヘッダーを設定
    6. 小計の列の背景色を設定
    7. 罫線を引く
  • 終了

【プログラム実行条件・注意事項】
① 指定されたワークシートと列の範囲に対してのみ動作します。そのため、適用するワークシートや列の範囲を変更する場合は、コードを適切に修正する必要があります。
② 数値データが格納されている列に対してのみ正確に動作します。数値以外のデータが含まれている場合、エラーが発生する可能性があります。
③ 新たな列を挿入するため、既存のデータが移動されます。そのため、コードを実行する前に、重要なデータが失われないようにバックアップを取ることを推奨します。

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

 

 

 

 ●実行前~実行後 ※プログラム実行後、指定された列の範囲のデータが行ごとに加算され、その結果が新たに挿入された列に書き込まれます。また、新たに挿入された列のヘッダーには”小計”と表示され、その列の背景色は薄い水色に設定されます。また、ワークシートの指定された範囲に罫線が引かれます。
(画面クリックして拡大)

 

 

EXCEL VBA 行を自動挿入し、データを集計するVBAコード!配列を駆使して効率的な集計!

 

 

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

下記のサンプルプログラムは、上記のサンプルプログラム②の応用になります。このコードは、Excelのワークシートにおける特定の行の小計を計算し、その結果を新たな行に挿入するためのプログラムです。このプログラムでは、まずExcelのシートと計算したい行の範囲を選びます。その後、選んだ範囲の数値を一つずつ足し合わせて、その小計を表示します。これは、合計の計算結果だけを表示したいときに便利なプログラムです。小計の集計方法は配列を利用して集計する様に変更しています。

【プログラムの流れ】

上記のフローチャートの内容について簡単に説明します。

  • 開始
  • AddSubTotal プロシージャ
    1. ワークシートと列の範囲を設定
    2. 新たな行を挿入し、配列を用いて計算
  • InsertRowAndCalculate プロシージャ
    1. 行を挿入
    2. 指定範囲のデータを配列に読み込む
    3. 配列内のデータを列ごとに加算
    4. 計算結果を新たな行に書き込む
    5. 小計の列のヘッダーを設定
    6. 小計の列の背景色を設定
    7. 罫線を引く
  • 終了

【プログラム実行条件・注意事項】
① このコードは”Sheet1″に対して動作します。他のシートに対して動作させる場合は、シート名を適切に変更する必要があります。
② 集計する範囲は、InsertRowAndCalculate関数の引数で指定します。範囲を変更する場合は、これらの引数を適切に変更する必要があります。
③ このコードは数値データの集計を行います。数値以外のデータが含まれている場合、エラーが発生する可能性があります。

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

 

 

 

 

 ●実行前~実行後 ※プログラム実行後、指定したデータ範囲の列ごとに集計され、その結果が指定した行に挿入されます。また、その挿入された小計行の背景色が薄い水色に塗りつぶしされます。
(画面クリックして拡大)

 

 

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