EXCEL VBA 指定した行列に小計を追加する。Subtotal関数または、配列を駆使して効率的な集計を行う方法
EXCEL VBA 指定した行列に小計を追加する。Subtotal関数または、配列を駆使して効率的な集計を行う方法
今回説明するのは、データや集計表等を作成した後に、小計を追加する方法を説明したいと思います。今回のサンプルプログラムでは、Subtotal関数または、配列を利用して指定した場所(行・列)及び指定した範囲で小計を集計する方法を説明いたします。詳しくは、サンプルプログラムを交えて順番に説明いたします。
●【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(サンプルプログラム)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
' ' Option Explicit Sub AddSubTotal() '列に小計を挿入する。 Dim ws As Worksheet Dim lastRow As Long ' Worksheetの設定 Set ws = ThisWorkbook.Sheets("Sheet1") ' 最終行を取得 lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row ' Subtotal関数を使って小計を計算し、列に挿入 InsertColumnAndCalculate ws, 4, 2, 3, lastRow InsertColumnAndCalculate ws, 8, 5, 7, lastRow End Sub Sub InsertColumnAndCalculate(ByRef ws As Worksheet, ByVal insertColumn As Long, ByVal startColumn As Long, ByVal endColumn As Long, ByVal lastRow As Long) ' 列を挿入 ws.Columns(insertColumn).Insert ' 小計のセルにSubtotal関数を入れて計算 ws.Range(ws.Cells(2, insertColumn), ws.Cells(lastRow, insertColumn)).FormulaR1C1 = "=SUBTOTAL(9,RC[" & (startColumn - insertColumn) & "]:RC[" & (endColumn - insertColumn) & "])" ' 小計列の見出しを設定 ws.Cells(1, insertColumn) = "小計" ' 集計行の背景色を薄い水色にする ws.Range(ws.Cells(1, insertColumn), ws.Cells(lastRow, insertColumn)).Interior.Color = RGB(173, 216, 230) ' 罫線を引く With ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, insertColumn)).Borders .LineStyle = xlContinuous .Color = RGB(0, 0, 0) .Weight = xlThin End With End Sub ' ' |
(画面クリックして拡大)
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 プロシージャ
- ワークシートと列の範囲を設定
- 新たな列を挿入し、配列を用いて計算
- InsertColumnAndCalculate プロシージャ
- 列を挿入
- 指定範囲のデータを配列に読み込む
- 配列内のデータを行ごとに加算
- 計算結果を新たな列に書き込む
- 小計の列のヘッダーを設定
- 小計の列の背景色を設定
- 罫線を引く
- 終了
【プログラム実行条件・注意事項】
① 指定されたワークシートと列の範囲に対してのみ動作します。そのため、適用するワークシートや列の範囲を変更する場合は、コードを適切に修正する必要があります。
② 数値データが格納されている列に対してのみ正確に動作します。数値以外のデータが含まれている場合、エラーが発生する可能性があります。
③ 新たな列を挿入するため、既存のデータが移動されます。そのため、コードを実行する前に、重要なデータが失われないようにバックアップを取ることを推奨します。
★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
● AddSubTotal03(サンプルプログラム)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
Option Explicit ' ' Sub AddSubTotal() '列小計を配列 Dim ws As Worksheet Dim lastRow As Long Dim dataRange As Range Dim dataArr As Variant Dim sumResult As Double Dim i As Long, j As Long ' Worksheetの設定 Set ws = ThisWorkbook.Sheets("Sheet1") ' 最終行を取得 lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row ' 列を挿入し、配列を用いて計算 InsertColumnAndCalculate ws, 4, 2, 3, lastRow InsertColumnAndCalculate ws, 9, 5, 8, lastRow End Sub ' ' Sub InsertColumnAndCalculate(ByRef ws As Worksheet, ByVal insertColumn As Long, ByVal startColumn As Long, ByVal endColumn As Long, ByVal lastRow As Long) Dim dataRange As Range Dim dataArr As Variant Dim sumResult As Double Dim i As Long, j As Long ' 列を挿入 ws.Columns(insertColumn).Insert ' 指定された範囲のデータを配列に読み込む Set dataRange = ws.Range(ws.Cells(2, startColumn), ws.Cells(lastRow, endColumn)) dataArr = dataRange.Value ' 配列内のデータを行ごとに加算 For i = LBound(dataArr, 1) To UBound(dataArr, 1) sumResult = 0 For j = LBound(dataArr, 2) To UBound(dataArr, 2) sumResult = sumResult + dataArr(i, j) Next j ' 計算結果を新たな列に書き込む ws.Cells(i + 1, insertColumn).Value = sumResult Next i ws.Cells(1, insertColumn) = "小計" ' 集計行の背景色を薄い水色にする ws.Range(ws.Cells(1, insertColumn), ws.Cells(lastRow, insertColumn)).Interior.Color = RGB(173, 216, 230) ' 罫線を引く With ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, insertColumn)).Borders .LineStyle = xlContinuous .Color = RGB(0, 0, 0) .Weight = xlThin End With End Sub ' ' |
(画面クリックして拡大)
EXCEL VBA 行を自動挿入し、データを集計するVBAコード!配列を駆使して効率的な集計!
下記のサンプルプログラムは、上記のサンプルプログラム②の応用になります。このコードは、Excelのワークシートにおける特定の行の小計を計算し、その結果を新たな行に挿入するためのプログラムです。このプログラムでは、まずExcelのシートと計算したい行の範囲を選びます。その後、選んだ範囲の数値を一つずつ足し合わせて、その小計を表示します。これは、合計の計算結果だけを表示したいときに便利なプログラムです。小計の集計方法は配列を利用して集計する様に変更しています。
【プログラムの流れ】
上記のフローチャートの内容について簡単に説明します。
- 開始
- AddSubTotal プロシージャ
- ワークシートと列の範囲を設定
- 新たな行を挿入し、配列を用いて計算
- InsertRowAndCalculate プロシージャ
- 行を挿入
- 指定範囲のデータを配列に読み込む
- 配列内のデータを列ごとに加算
- 計算結果を新たな行に書き込む
- 小計の列のヘッダーを設定
- 小計の列の背景色を設定
- 罫線を引く
- 終了
【プログラム実行条件・注意事項】
① このコードは”Sheet1″に対して動作します。他のシートに対して動作させる場合は、シート名を適切に変更する必要があります。
② 集計する範囲は、InsertRowAndCalculate関数の引数で指定します。範囲を変更する場合は、これらの引数を適切に変更する必要があります。
③ このコードは数値データの集計を行います。数値以外のデータが含まれている場合、エラーが発生する可能性があります。
★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
● AddSubTotal04(サンプルプログラム)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
Option Explicit ' ' Sub AddSubTotal() '小計行を追加 Dim ws As Worksheet Dim lastColumn As Long Dim dataRange As Range Dim dataArr As Variant Dim sumResult As Double Dim i As Long, j As Long ' Worksheetの設定 Set ws = ThisWorkbook.Sheets("Sheet1") ' 最終列を取得 lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column ' 行を挿入し、配列を用いて計算 InsertRowAndCalculate ws, 9, 2, 8, lastColumn InsertRowAndCalculate ws, 17, 10, 16, lastColumn End Sub ' ' Sub InsertRowAndCalculate(ByRef ws As Worksheet, ByVal insertRow As Long, ByVal StartRow As Long, ByVal EndRow As Long, ByVal lastColumn As Long) Dim dataRange As Range Dim dataArr As Variant Dim sumResult As Double Dim i As Long, j As Long ' 行を挿入 ws.Rows(insertRow).Insert ' 指定された範囲のデータを配列に読み込む Set dataRange = ws.Range(ws.Cells(StartRow, 2), ws.Cells(EndRow, lastColumn)) dataArr = dataRange.Value ' 配列内のデータを列ごとに加算 For j = LBound(dataArr, 2) To UBound(dataArr, 2) sumResult = 0 For i = LBound(dataArr, 1) To UBound(dataArr, 1) sumResult = sumResult + dataArr(i, j) Next i ' 計算結果を新たな行に書き込む ws.Cells(insertRow, j + 1).Value = sumResult Next j ws.Cells(insertRow, "A") = "小計" ' 集計行の背景色を薄い水色にする ws.Range(ws.Cells(insertRow, 1), ws.Cells(insertRow, lastColumn)).Interior.Color = RGB(173, 216, 230) End Sub ' |
(画面クリックして拡大)
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。