ExcelVBA テーブルに集計行を追加する

スポンサーリンク

テーブルに集計行を表示して集計方法を設定する方法

テーブルに設定した表範囲を表すListObject【リストオブジェクト】オブジェクトに集計行を表示するにはListObject【リストオブジェクト】オブジェクトのShowTotals【ショートータルズ】プロパティを使用します。その集計行の集計する各列に集計方法を設定するには、ListObjects【リストオブジェクツ】コレクションのテーブル範囲の列を表すListColumn【リストカラム】オブジェクトのTotalsCalculation【トータルズカルクレーション】プロパティを使用します。

ShowTotals【ショートータルズ】プロパティ

テーブルに設定した表範囲の集計行の表示、非表示の設定および、取得をするには、ListObject【リストオブジェクト】オブジェクトのShowTotals【ショートータルズ】プロパティを使用します。

ShowTotals【ショートータルズ】プロパティの書式と設定値の説明

【取得】
オブジェクト.ShowTotals
【設定】
オブジェクト.ShowTotals = 設定値

  • オブジェクト(必須)
    テーブルに設定した表範囲を表すListObject【リストオブジェクト】オブジェクトを指定します。
  • 設定値(必須)
    集計行の表示と非表示をブール型の値で設定します。

    設定値内容
    True集計行を表示します。
    False集計行を非表示にします。

※指定したテーブルが存在しない場合はエラーになります。


表範囲をテーブルにして集計行を表示するコードと解説

Sub 集計行()
On Error GoTo エラー処理
Range("A1").Select
With ActiveSheet.ListObjects.Add
    .ShowTotals = True
End With
Exit Sub
エラー処理:
     MsgBox "テーブルは作成済みです。"
End Sub
2行目【On Error GoTo エラー処理】
On Error【オンエラー】ステートメントと GoTo【ゴウトゥ】ステートメントを使用してエラーが発生したら(テーブル設定済みの範囲にテーブル設定をした場合)8行目の「エラー処理」ラベルまで処理をジャンプします。


3行目【Range(“A1”).Select】
見出しを含む、データの始まりのセルを選択します。A1セルから始まるアクティブセル領域を参照するので、データ範囲全体を選択する必要はありません。


4行目【With ActiveSheet.ListObjects.Add】
Worksheet【ワークシート】オブジェクトのListObjects【リストオブジェクツ】プロパティで取得したListObjects【リストオブジェクツ】コレクションのAdd【アド】メソッドを使用して、データ範囲にテーブルを作成し、With【ウィズ】ステートメントで指定します。


5行目【.ShowTotals = True】
With【ウィス】ステートメントで指定したテーブルを表すListObject【リストオブジェクト】オブジェクトのShowTotals【ショートータル】プロパティにTrueを設定してテーブルに集計行を表示します。既定で右端の列に集計結果が表示されます。


7行目【Exit Sub】
Exit【エグジット】ステートメントでSub【サブ】プロシージャを途中で終了します。


8~9行目【エラー処理:
MsgBox “テーブルは作成済みです。”】

エラーが発生した場合、2行目のステートメントで8行目の「エラー処理」ラベルまで処理がジャンプし、9行目からの処理を実行します。

実行結果

テーブルと集計行を解除するコードと解説

Sub テーブル解除()
On Error Resume Next
With ActiveSheet.ListObjects(1)
     .TableStyle = ""
     .ShowTotals = False
     .Unlist
End With
End Sub
3行目【With ActiveSheet.ListObjects(1)】
Worksheet【ワークシート】オブジェクトのListObjects【リストオブジェクツ】プロパティで参照したインデックス番号1のテーブルをWith【ウィズ】ステートメントで指定します。


4行目【 .TableStyle = “”】
With【ウィズ】ステートメントで指定したテーブルを表すListObject【リストオブジェクト】オブジェクトのTableStyle【テーブルスタイル】プロパティに「””」長さ0の文字列(空白)を指定して既定のスタイルが適用されているテーブルのスタイルを解除します。


5行目【 .ShowTotals = False】
With【ウィズ】ステートメントで指定したテーブルを表すListObject【リストオブジェクト】オブジェクトのShowTotals【ショータイトル】プロパティにFalseを指定して集計行を非表示にします。集計行を非表示にしないと次のUnlist【アンリスト】メソッドを実行しても集計行は残ります。


6行目【.Unlist】
With【ウィズ】ステートメントで指定したテーブルを表すListObject【リストオブジェクト】オブジェクトのUnlist【アンリスト】メソッドを使用してテーブルを解除します。


実行結果

ListColumns【リストカラムズ】プロパティ

テーブルを設定した表範囲を表すListObject【リストオブジェクト】オブジェクトの列を表すListColumn【リストカラム】オブジェクトを
参照するには、ListObject【リストオブジェクト】オブジェクトのListColumns【リストカラムズ】プロパティを使用します。

ListColumns【リストカラムズ】プロパティの書式と設定値(引数)の説明

[]内は省略可能です。
オブジェクト.ListColumns[(index)]

  • オブジェクト(必須)
    テーブルを設定した表範囲を表すListObject【リストオブジェクト】オブジェクトを指定します。
  • 設定値(省略可)
    参照する列をインデックス番号(列の左からの順番)または、列の項目名で指定します。省略した場合はすべての列を表すListColumns【リストカラムズ】コレクションが参照されます。

TotalsCalculation【トータルズカルクレーション】プロパティ

指定したテーブルの集計行の各列に集計方法を設定するには、テーブルの各列を表すListColumn【リストカラム】オブジェクトのTotalsCalculation【トータルズカルクレーション】プロパティを使用します。値の設定および取得ができます。

TotalsCalculation【トータルズカルクレーション】プロパティの書式と設定値の説明

【取得】
オブジェクト.TotalsCalculation
【設定】
オブジェクト.TotalsCalculation = 設定値

  • オブジェクト(必須)
    テーブルの各列を表すListColumn【リストカラム】オブジェクトを指定します。
  • 設定値(必須)
    集計方法をXlTotalsCalculation列挙型の定数または、値で指定します。

    XlTotalsCalculation列挙型
    定数内容
    xlTotalsCalculationNone0計算なし
    xlTotalsCalculationSum1合計
    xlTotalsCalculationAverage2平均
    xlTotalsCalculationCount3空ではないセルの数
    xlTotalsCalculationCountNums4数値データの数
    xlTotalsCalculationMin5リストの最小値
    xlTotalsCalculationMax6リストの最大値
    xlTotalsCalculationStdDev7標準偏差値
    xlTotalsCalculationVar8変数
    xlTotalsCalculationCustom9ユーザー設定の計算

集計行の表示、非表示の設定をするShowTotals【ショートータルズ】プロパティにTrueを設定して集計行を表示すると、既定では右端の列の集計結果だけが表示されます。集計方法は、列に含まれるデータの種類によってExcelが自動で設定します。

右端列の集計結果が不要な場合は、右端の列を表すListColumn【リストカラム】オブジェクトのTotalsCalculation【トータルカルクレーション】プロパティにxlTotalsCalculationNoneを設定して、「集計なし」に設定します。


売上の集計方法を平均に設定するコード

Sub 集計行()
On Error GoTo エラー処理
Range("A1").Select
With ActiveSheet.ListObjects.Add
    .ShowTotals = True
    .ListColumns("売上").TotalsCalculation = 2
End With
Exit Sub
エラー処理:
     MsgBox "テーブルは作成済みです。"
End Sub

実行結果


以上で、テーブルに集計行を追加する方法についての解説を終了します。

スポンサーリンク

関連記事・広告