VBA

【VBA】テーブル集計行の使い方(集計行の出し方、計算方法の選択、削除)

記事内に商品プロモーションを含む場合があります

このページでわかること

テーブルの集計行を出し方、しまい方、計算方法の選択が自由自在になります。

覚えること
  1. 集計行を出す
    テーブルオブジェクト.ShowTotals = True

  2. 集計行の削除(しまい方)
    テーブルオブジェクト.ShowTotals = False

  3. 変更できるようにしたいセルの処理
    レンジオブジェクト.Locked=False

  4. 集計行の計算方法の設定
    テーブルオブジェクト.ListColumns(列の名前もしくはインデックス番号).TotalsCalculation = 計算方法

  5. あるあるな間違い
    TotalsRowRangeは集計行の出し入れや計算方法の設定で使わない。
    集計行を出したりしまったり → テーブルオブジェクト.ShowTotals
    計算方法 → ListColumns(列の名前もしくはインデックス番号).TotalsCalculation

こんにちはhokkyokunです。

集計行はテーブルの大きなメリットの一つです。
簡単に合計や平均、カウントがとれるので(しかもフィルタリングすればフィルタリング後データで計算してくれます。)

基本的にはテーブル化しても、集計行は出ません。
プログラムを書くことで集計行を出すことが出来ます。

集計行の出し方

構文:テーブルオブジェクト.ShowTotals = True

ShowTotalsプロパティはブール型です。
ShowTotalsと「s」が付くので注意してください。
また、やりがちなんですが(私だけ?)、集計行を出すためのにTotalsRowRangeは使いません。

こんな感じで表を作成したとします。
集計行を出してみましょう。

Sub 集計行()

Dim table As ListObject
Set table = ActiveSheet.ListObjects(1)

table.ShowTotals = True

End Sub

こんな感じで集計行を出すことができました。

デフォルトで

  • 一番左の行は「集計」という文字列
  • 一番右に集計が表示される。何が表示されるかはデータを見て自動的に判断される
    数値は合計?
    文字列はカウント?

もし不要であるなら下記の計算方法の設定で xlTotalsCalculationNone  を選ぶと不必要な集計を消すことが出来ます。

集計行の計算方法の設定

集計行を出しただけではほしい集計は得られていない可能性が高いので、
しっかり設定してあげましょう。

構文:テーブルオブジェクト.ListColumns(列の名前もしくはインデックス番号).TotalsCalculation = 計算方法

イメージとしては列の取得をしてから、TotalsCalculationで集計行を選択していくという流れです。

よくあるのが(これも私だけ?)TotalsRowRangeから設定しようとして、できない。みたいなミスがあると思いますので注意してください。

計算方法の表

名前説明
xlTotalsCalculationAverage2平均
xlTotalsCalculationCount3空でないセルのカウント
xlTotalsCalculationCountNums4数値のあるセルのカウント
xlTotalsCalculationCustom9ユーザー設定の計算
xlTotalsCalculationMax6リストの最大値
xlTotalsCalculationMin5リストの最小値
xlTotalsCalculationNone0計算なし
xlTotalsCalculationStdDev7標準偏差値
xlTotalsCalculationSum1リスト列のすべての値の合計
xlTotalsCalculationVar8変数

下記表で

「年月」列は 最大値
「果物」列は  空でないセルのカウント
「個数」列は 合計
「単価」列は 平均
「金額」列は 最小値
「メモ」列は 空でないセルのカウント

をやってみます。

Sub 集計行の計算()

Dim table As ListObject
Set table = ActiveSheet.ListObjects(1)

table.ShowTotals = True

With table
    .ListColumns("年月").TotalsCalculation = xlTotalsCalculationMax '「年月」列の最大値
    .ListColumns("果物").TotalsCalculation = xlTotalsCalculationCount '「果物」列の空でないセルのカウント
    .ListColumns("個数").TotalsCalculation = xlTotalsCalculationSum '「個数」列の合計
    .ListColumns("単価").TotalsCalculation = xlTotalsCalculationAverage '「単価」列の平均
    .ListColumns("金額").TotalsCalculation = xlTotalsCalculationMin '「金額」列の最小値
    .ListColumns("メモ").TotalsCalculation = xlTotalsCalculationCount '「メモ」列の空でないセルのカウント
End With

End Sub

こんな感じですね。

集計行の削除

集計行は邪魔になったりする場合がありますので、
必要に応じて削除してください。

構文: テーブルオブジェクト.ShowTotals = False

まとめ

いかがでしょうか。
集計行はテーブルを使う上での大きなメリット、
むしろこれを使うためにテーブルを扱うといっても過言でないくらいデータベース処理の要です。

  • 集計行を出す:テーブルオブジェクト.ShowTotals = True
  • 集計行を削除:テーブルオブジェクト.ShowTotals = False
  • 計算方法の設定:テーブルオブジェクト.ListColumns(列の名前もしくはインデックス番号).TotalsCalculation = 計算方法

覚えることもそんなに多くないので、ぜひ使ってみてください。

ではでは。