Excel VBA 表に合計行と合計列を追加する・行と列の合計を自動生成する方法（テクニック）
Excel VBA 表に合計行と合計列を追加する・行と列の合計を自動生成する方法（テクニック）
今回説明するのは、Excelのワークシート内のデータ表に対して、表の大きさを把握して各行と各列の合計を計算し、その結果を表の右側と下側に追加するものです。また、全体の総合計も計算し、表の右下に表示します。合計値が表示されるセルは、背景色を薄い青色に変更して視覚的にわかりやすくしています。自動的に表の大きさを把握して合計を作成しますので、とても便利です。詳しくは、サンプルプログラムを交えて順番に説明いたします。
●【EXCEL VBA セル範囲の串刺し集計・計算・カウント・平均（テクニック）、下記を参照して下さい】
●【EXCEL VBA 指定した行列に小計を追加する。Subtotal関数または、配列を駆使して効率的な集計を行う方法】については、下記を参照して下さい】
Excel VBA 表の合計値を自動計算する方法/Excel表の各列・各行の合計値を簡単に表示する方法
今回説明するプログラムは、表に自動的に合計値を自動作成するプログラムです。具体的には、まずアクティブなワークシートとその中の使用されている範囲（データ表）を取得します。その後、データ表の開始行、終了行、開始列、終了列を特定します。次に、各行と各列の合計を計算し、その結果を新たに追加した行と列に表示します。最後に、全体の総合計を計算し、表の右下に表示します。
【プログラムの流れ】
① アクティブなワークシートとその中の使用されている範囲（データ表）を取得する。
② データ表の開始行、終了行、開始列、終了列を特定する。
③ 各行と各列の合計を計算し、その結果を新たに追加した行と列に表示する。
④ 全体の総合計を計算し、表の右下に表示する。
【プログラム実行条件・注意事項】
① アクティブなワークシートのみに作用します。他のワークシートに対しては作用しません。
② データ表の1行目と1列目は合計の計算から除外されます。これらの行と列には数値データではなく、ヘッダー情報が含まれていると想定されています。
③ 数値データが含まれている表に対してのみ正しく動作します。数値以外のデータが含まれている場合、エラーが発生する可能性があります。
★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
● CalculateTableTotals01 （サンプルプログラム）
|
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 66 67 |
' ' Option Explicit Sub CalculateTableTotals() '行列の合計値表示 Dim activeWs As Worksheet Dim dataTable As Range Dim startRow As Long, endRow As Long Dim startColumn As Long, endColumn As Long Dim currentRow As Long, currentColumn As Long Dim totalSum As Double ' アクティブなワークシートを取得 Set activeWs = ActiveSheet ' 表の開始位置と終了位置を取得 Set dataTable = activeWs.UsedRange startRow = dataTable.row endRow = dataTable.row + dataTable.Rows.Count - 1 startColumn = dataTable.column endColumn = dataTable.column + dataTable.Columns.Count - 1 ' 1行目と1列目に「合計」を代入 activeWs.Cells(startRow, endColumn + 1).Value = "合計" activeWs.Cells(endRow + 1, startColumn).Value = "合計" ' 「合計」部分の背景色を水色にする activeWs.Cells(startRow, endColumn + 1).Interior.Color = RGB(200, 220, 255) activeWs.Cells(endRow + 1, startColumn).Interior.Color = RGB(200, 220, 255) ' 各列の合計を計算し登録 For currentColumn = startColumn To endColumn ' 表の1行目はスキップ If currentColumn <> startColumn Then activeWs.Cells(endRow + 1, currentColumn).Value = WorksheetFunction.Sum(activeWs.Range(activeWs.Cells(startRow, currentColumn), activeWs.Cells(endRow, currentColumn))) activeWs.Cells(endRow + 1, currentColumn).Interior.Color = RGB(200, 220, 255) ' 薄い青色 End If Next currentColumn ' 各行の合計を計算し登録 For currentRow = startRow To endRow ' 表の1列目はスキップ If currentRow <> startRow Then activeWs.Cells(currentRow, endColumn + 1).Value = WorksheetFunction.Sum(activeWs.Range(activeWs.Cells(currentRow, startColumn), activeWs.Cells(currentRow, endColumn))) activeWs.Cells(currentRow, endColumn + 1).Interior.Color = RGB(200, 220, 255) ' 薄い青色 End If Next currentRow ' 表の1行目と1列目の合計値は表示しない（計算しない） activeWs.Cells(endRow + 1, endColumn + 1).ClearContents ' 総合計を計算し表示 totalSum = WorksheetFunction.Sum(activeWs.Range(activeWs.Cells(startRow, startColumn), activeWs.Cells(endRow, endColumn))) activeWs.Cells(endRow + 1, endColumn + 1).Value = totalSum ' 表に罫線を引く With activeWs.Range(activeWs.Cells(startRow, startColumn), activeWs.Cells(endRow + 1, endColumn + 1)).Borders .LineStyle = xlContinuous .Weight = xlThin End With ' 合計値を表示する部分の背景色を変更 activeWs.Cells(endRow + 1, endColumn + 1).Interior.Color = RGB(200, 220, 255) '行と列の合計値部分も背景色を薄い青色で塗りつぶす End Sub ' ' |
（画面クリックして拡大）
Excel VBA 表のから特定の「合計」行と列を一瞬で削除するプログラム（合計の行・列を自動削除）
今回説明するプログラムは、上記サンプル①説明した自動作成する行・列の合計値を削除するサンプルプログラムです。具体的には、Excelのアクティブなワークシートの表にある最後の行と列のセルが「合計」を示している場合に、それらを行・列を削除するプログラムになります。
【プログラムの流れ】
① アクティブなワークシートを取得
② ワークシート内の使用中の範囲（データテーブル）を取得
③ データテーブルの開始行、終了行、開始列、終了列の情報を取得
④ 最終行の第1列が「合計」である場合、その行を削除
⑤ 最終列の第1行が「合計」である場合、その列を削除
【プログラム実行条件・注意事項】
① このコードはアクティブなワークシートのみを処理します。他のワークシートを与えることはありません。
② 最後の行または列が「合計」であることを確認せずに実行すると、期待しない行または列が削除される可能性があります。
③ 「合計」の文字列が異なる言語または別の表現（例えば、”Total”や”Sum”など）で表されている場合、このコードはそれを認識しません。
★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
● CalculateTableTotals01（サンプルプログラム）
（画面クリックして拡大）
EXCEL VBA ワークシート名で指定した全ての表（テーブル）に合計値を自動追加する。
下記のプログラムは、Excelのワークブック内の全てのワークシートをループし、各ワークシート内のテーブル（使用中の範囲）の行と列の合計値を計算し、その合計値をテーブルの右端の列と下部の行に追加します。さらに、シート名に「表」の文字が含まれているシートのみを対象としています。具体的には、Excelの全てのシートを順に見ていき、シート名に「表」が含まれているものを対象とします。対象のシートの使用範囲を取得し、その範囲の行と列の合計を計算します。計算した合計は、「合計」の列と行にそれぞれ記入されます。さらに、合計が記入されるセルは薄い青色に塗られ、全体に罫線が引かれます。また、全ての列と行の合計値の全体の合計も計算され、テーブルの右下に表示されます。
【プログラムの流れ】
① ワークブック内の全シートをループ
② シート名に「表」の文字が含まれているシートを対象
③ 対象のワークシートの使用範囲を取得
④ 使用範囲の開始と終了位置を取得
⑤ 合計値を記入する1行目と1列目に「合計」と記入
⑥ 各列の合計を計算し、合計行に記入
⑦ 各行の合計を計算し、合計列に記入
⑧ 全体の合計値を計算し、右下に記入
⑨ 表に罫線を引き、合計値を表示する部分の背景色を薄い青色に変更
【プログラム実行条件・注意事項】
① このコードは、シート名に「表」が含まれているワークシートのみを対象としています。この名前の条件はコード内で変更可能です。
② 合計を計算する範囲は「UsedRange」で自動的に取得しています。特定の範囲のみを対象にしたい場合は、この部分を修正する必要があります。
③ 計算対象の範囲に非数値のデータが含まれていると、エラーが発生する可能性があります。
★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
● CalculateTableTotals03（サンプルプログラム）
|
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 66 67 68 69 70 71 72 |
Option Explicit ' ' Sub CalculateTableTotals03() 'ワークシート全体に対して表に合計を表示する。 Dim ws As Worksheet Dim activeWs As Worksheet Dim dataTable As Range Dim startRow As Long, endRow As Long Dim startColumn As Long, endColumn As Long Dim currentRow As Long, currentColumn As Long Dim totalSum As Double ' ワークブック内の全シートをループ For Each ws In ThisWorkbook.Worksheets ' シート名に「表」の文字が含まれているシートのみを対象 If InStr(ws.Name, "表") > 0 Then Set activeWs = ws ' 対象のワークシートを取得 ' 表の開始位置と終了位置を取得 Set dataTable = activeWs.UsedRange startRow = dataTable.row endRow = dataTable.row + dataTable.Rows.Count - 1 startColumn = dataTable.column endColumn = dataTable.column + dataTable.Columns.Count - 1 ' 1行目と1列目に「合計」を代入 activeWs.Cells(startRow, endColumn + 1).Value = "合計" activeWs.Cells(endRow + 1, startColumn).Value = "合計" ' 「合計」部分の背景色を水色にする activeWs.Cells(startRow, endColumn + 1).Interior.Color = RGB(200, 220, 255) activeWs.Cells(endRow + 1, startColumn).Interior.Color = RGB(200, 220, 255) ' 各列の合計を計算し登録 For currentColumn = startColumn To endColumn ' 表の1行目はスキップ If currentColumn <> startColumn Then activeWs.Cells(endRow + 1, currentColumn).Value = WorksheetFunction.Sum(activeWs.Range(activeWs.Cells(startRow, currentColumn), activeWs.Cells(endRow, currentColumn))) activeWs.Cells(endRow + 1, currentColumn).Interior.Color = RGB(200, 220, 255) ' 薄い青色 End If Next currentColumn ' 各行の合計を計算し登録 For currentRow = startRow To endRow ' 表の1列目はスキップ If currentRow <> startRow Then activeWs.Cells(currentRow, endColumn + 1).Value = WorksheetFunction.Sum(activeWs.Range(activeWs.Cells(currentRow, startColumn), activeWs.Cells(currentRow, endColumn))) activeWs.Cells(currentRow, endColumn + 1).Interior.Color = RGB(200, 220, 255) ' 薄い青色 End If Next currentRow activeWs.Cells(endRow + 1, endColumn + 1).ClearContents ' 行と列の合計を計算し表示 totalSum = WorksheetFunction.Sum(activeWs.Range(activeWs.Cells(endRow + 1, startColumn), activeWs.Cells(endRow + 1, endColumn))) + WorksheetFunction.Sum(activeWs.Range(activeWs.Cells(startRow, endColumn + 1), activeWs.Cells(endRow, endColumn + 1))) activeWs.Cells(endRow + 1, endColumn + 1).Value = totalSum ' 表に罫線を引く With activeWs.Range(activeWs.Cells(startRow, startColumn), activeWs.Cells(endRow + 1, endColumn + 1)).Borders .LineStyle = xlContinuous .Weight = xlThin End With ' 合計値を表示する部分の背景色を変更 activeWs.Cells(endRow + 1, endColumn + 1).Interior.Color = RGB(200, 220, 255) '行と列の合計値部分も背景色を薄い青色で塗りつぶす End If Next ws End Sub ' ' |
（画面クリックして拡大）
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。