本ページには広告が含まれています。

Excel VBA テーブル集計行の表示操作と値の設定・取得方法

テーブル集計行の表示操作と値の設定・取得方法
2021.03.09

テーブル操作第10回目です。テーブルには集計行を表示することができます。また、集計行が不要になったら消すことができます。ただし手動で操作しなければいけないので結構面倒です。

くるみこ
くるみこ

テーブルの集計行をVBAマクロで表示を切り替えられるようにしたいと思います。それから、集計行の値を取得するにはどうしたらいいのか、その方法について勉強していきましょう(^^)/

そうなんです。テーブルを選択していないと表示されないし、設定する場所がどこだったか探しちゃうんですよね(^^;

【この記事でわかることは】
・テーブル集計行の表示を操作する方法がわかります
・集計行から値を取得する方法がわかります

前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ

Excel VBA テーブルのデータを編集・取得する方法
Excel VBA テーブルのデータを編集する方法とテーブルからデータを取得する方法の解説です。VLOOKUP関数を使ってテーブルのデータを構造化参照で取得する方法を紹介。VBAでセルに計算式を書き込む方法と計算式を値化する方法まで紹介。
www.kurumico.com
くるみこ
くるみこ

前回記事は、テーブルのデータを編集する方法とテーブルからデータを取得する方法の解説記事でした。覗いてみてね(^^)/

スポンサーリンク

テーブル集計行の表示を操作する

テーブル集計行の表示を手動で切り変えるのは面倒なので(^^ゞ VBAで操作する方法を考えます。

テーブル集計行の表示切替を行うには

テーブル集計行は、ListObjectオブジェクトの ShowTotals プロパティを使います。

ShowTotals プロパティ

ShowTotals プロパティは ListObject の集計プロパティです。
集計行を表示するかどうかを示すブール型 (Boolean) の値を取得または設定します。
「True = 表示」、「False = 非表示」です。（テーブルが存在しないとエラーになる）

・ということは、テーブルの ShowTotals プロパティの現在の設定を調べてその反対を設定すれば表示の切り替えが出来るということですね。
・「ブール型」を調べるのなら、論理否定演算子の Not演算子 を使うのがうってつけです。
　　Not True の場合 → False 、Not False の場合 → True　を返します。
　　「テーブル.ShowTotals = Not テーブル.ShowTotals」の1行で済みます。
・もちろん If ステートメントを使ってもいいのですが(^^;

・アクティブシートの1番目のテーブル集計行の表示を切り替えるサンプルです。

'テーブル集計行の表示を切り替えます
Sub SetShowTotals()
    With ActiveSheet.ListObjects(1)
　　    .ShowTotals = Not .ShowTotals 'Not演算子でTrue ⇔ False切替
    End With
End Sub

・手動では、テーブルを選択していないと切り替えが出来ませんでしたが、VBAマクロならば選択していなくても切り替えることができました。

ShowTotalsプロパティにTrueを設定すると集計行が表示されます。集計行が最初に表示される際、既定では左端セルに「集計」の文字列と右端セルに「集計結果」が表示されます。「集計結果」の集計方法は、列に含まれるデータの種類によってExcelが自動で設定しています。
意図している集計と違っている場合は集計方法を修正する必要があります。
また、右端以外のセルにも集計結果を表示させるには設定する必要があります。

もちろん、左端セルの「集計」の文字も変更可能です。文字列は「なし」の設定です。

修正した設定は、その後表示を切り替えた場合でも保存されています。

テーブル集計行個別セルの表示設定

指定したテーブルの集計行の各列に集計方法を設定するには、テーブルの各列を表すListColumnオブジェクトのTotalsCalculationプロパティを使用します。値の設定および取得ができます。

TotalsCalculationプロパティ

列挙型の値に基づいて、リスト列の集計行の計算の種類を決定します。

TotalsCalculationプロパティの書式
【取得】ListColumnオブジェクト.TotalsCalculation
【設定】ListColumnオブジェクト.TotalsCalculation = 設定値

設定値は、集計方法を示すXlTotalsCalculation列挙型の定数または値で指定します。
XlTotalsCalculation列挙型

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

・テーブル集計行のセルの設定をVBAで個別に行うサンプルコード

'集計行個別セルを表示設定する
Sub SetShowTotalsCell()
    With ActiveSheet.ListObjects(1)
        .ShowTotals = True
        .ListColumns(3).TotalsCalculation = 2　'2 = 平均
        .ListColumns("クリック数").TotalsCalculation = 2
    End With
End Sub

・ShowTotals = True で必ず表示させておく必要があります。
・5行目、ListColumns(3) は列数（Index)または「列タイトル名」で指定します。
・6行目、ListColumns(“クリック数”) が「列タイトル名」で指定した例です。
・TotalsCalculation = n のｎは XlTotalsCalculation 列挙型定数又は値で指定します。

テーブル集計行から情報を取得する

・テーブル集計行からデータ取得するサンプルコードです。

'集計行の情報を取得する
Sub GetShowTotals()
    Dim x(3) As Variant
    With ActiveSheet.ListObjects(1)
        .ShowTotals = True
        x(0) = .TotalsRowRange.Address
        x(1) = .ListColumns(3).Index
        x(2) = .TotalsRowRange(x(1)).Formula
        x(3) = .TotalsRowRange(x(1)).Value
        MsgBox "集計行Address = " & x(0) & vbCrLf & _
            "集計行[表示回数]の列番 = " & x(1) & vbCrLf & _
            "集計行[表示回数]の計算式は = " & x(2) & vbCrLf & _
            "集計行[表示回数]の表示データは = " & x(3)
    End With
End Sub

・変数を配列で用意しました。
・取得した情報をメッセージボックスで表示します。
・実際に使用する場合の参考としてください。
・実行結果のメッセージボックスはこちらです。

実行結果のメッセージボックス

集計行に文字列を代入して書式設定する

・テーブル集計行に文字列を代入して書式設定するサンプル

'集計行のセルに文字列を代入して書式設定する
Sub GetShowTotalsCell()
    With ActiveSheet.ListObjects(1)
        .ShowTotals = True
        .ListColumns(3).TotalsCalculation = 2
        '.ListColumns("表示回数").Index
        '文字列をセットして右寄せにする
        .TotalsRowRange(2).Value = "平均値⇒"
        .TotalsRowRange(2).HorizontalAlignment = xlRight
        .TotalsRowRange(2).Font.ColorIndex = 3
    End With
End Sub

・集計行のセルは書式を変更できます。
・3番目のセルは平均に設定しています。
・2番目のセルに文字列をセットしています（文字列は左寄せになる）
・その後、書式を右寄せ、フォントを赤に設定しています。

まとめ（おわりに）

・いかがでしたでしょうか？
・「いつも汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーを念頭に記事を書いています。
・今回も、記事内で使用したコードのサンプルファイルを登録していますのでご利用ください。
・今までの記事のサンプルも登録していますのでよろしければお使いください(^^)

まとめと感想など

くるみこ
くるみこ

テーブルの集計行を活用することは少ないかもしれませんが、うまく活用すればリスト全体の現状をタイムリーに把握するには有効です。
集計行はレコードが増減しても自動的に集計値が現行化されます。
例えば、複数のテーブルの集計行数値を、一つの場所で表示するなどすれば、どのデータがどのくらいの量なのかなど一括で把握できるのではないでしょうか。

テーブル集計行もうまく活用できれば面白そうですね(^^)

くるみこ
くるみこ

テーブル操作については一区切りにして、次回は「Functionステートメント」ついて解説したいと思います(^^)

【今回分かったことは】
・テーブル集計行の表示を切り替える方法がわかりました
・テーブル集計行個別セルの表示設定方法がわかりました
・集計行から値を取得する方法がわかりました

★★★ ブログランキング参加中！ クリックしてね(^^)/ ★★★

にほんブログ村 IT技術ブログ VBAへ
にほんブログ村

人気ブログランキングアイコン
Visual Basicランキング

今後の記事について

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にもご期待ください(^^)/

スポンサーリンク
エックスサーバーバナー広告
スポンサーリンク

記事のサンプルファイルをダウンロードできます

今回の記事のサンプルをダウンロードできるようこのリンク先に登録しています！

過去の記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら（このリンク先）からご利用ください

【Excel VBA】サンプルファイル_ダウンロードのページ
【Excel VBA】の記事内で使ったサンプルファイルを（Zipファイルで）ダウンロードできるようにしました。記事内では細かい部分の解説が不十分ですが、実際にファイルをダウンロードして実行することで使えるのかどうか見ていただけたら幸いです。
www.kurumico.com