【VBA】表をグループ化し集計／解除、折り畳みや展開を行う

こんにちは、Ryoです。
Excelで表を扱うことはたくさんありますが、その中の便利な機能の一つに表をフィールドごとにグループ化して集計する方法があります。この機能はVBAでも扱うことが出来るので、今回はグループ化して集計、解除、アウトラインの折り畳みや展開について書いていきます。

1.サンプル概要

サンプルとして以下画像のような簡易的な表を使用します。

この表のA列（Date）をグループ化し、C列の数量（Quantity)合計を集計する処理を行うと、以下の通りになります。

Date欄が日付ごとにグループ化され、それぞれの集計と総計を自動的に処理してくれますので、便利な機能ですね。

また、画像の左側に表示されているアウトラインも折り畳みや展開の処理を行うことができます。画像では全て表示されている状態でしたが、レベル1だけの表示では以下にようになります。

レベル2までの展開であれば、このようになります。

これらのグループ化は当然ながら解除する処理も出来ます。解除後の状態は以下画像の通りで、集計処理した際に自動生成された数式などは残ります。

では、以上のサンプルについて書いていきます。

2.サンプルコード

◆グループ化して集計

Sub Sample1()

  'セルA2を含めた範囲を指定
  Range("A2").CurrentRegion.Subtotal _
  GroupBy:=1, Function:=xlSum _
  , TotalList:=3
  '1列目（A列 Date）をグループ化し、
  '3列目（C列 Quantity)の合計を集計

End Sub

改行して記述してますから3行ですが、実質1行で済みます。このように表をフィールドごとにグループ化して集計するにはSubTotalメソッドを使用します。

オブジェクト.SubTotal(GroupBy,Function,TotalList,
Replace,PageBreaks,SummaryBelowData)

この構文にある引数GroupByにグループ化する列を指定、引数Functionに集計方法を指定、引数TotalListに集計する列を指定します。これら3つの引数指定は必須で、以降の引数4つについてはオプションとなっています。

その他引数については特に記事では触れませんので、ご興味がある場合は英語ですがこちらのMSDNをご参照ください。

本サンプルではA列をグループ化⇒GroupBy:=1、C列を集計⇒Function:=xlSum , TotalList:=3とするものです。

また引数Functionに設定する値は他に以下がありますので、用途に応じて使い分けることも可能です。

補足ですが、サンプルコード記述中にあるCurrentRegionは「空白セルで囲まれた領域」を選択してくれるものです。そこを普通に指定したい場合は、Range(“A2:C12”).Subtotal …（以降は同じ）としてもOKなので、ご参考までに。

◆アウトライン展開／折り畳み

Sub Sample2()
  
  'Worksheetに対して処理を行う
  Worksheets("sheet3").Outline _
  .ShowLevels RowLevels:=1
  'Outlineレベル「1」だけにする

End Sub


Sub Sample3()
  
  'Outlineレベル「2」まで展開
  Worksheets("sheet3").Outline _
  .ShowLevels RowLevels:=2

End Sub


Sub Sample4()
  
  '全て展開したい場合は、実際に設定
  'されているレベルよりも大きい値を指定する
  '本例の場合は「3」
  Worksheets("sheet3").Outline _
  .ShowLevels RowLevels:=3

End Sub

アウトラインの展開や折り畳みを行う場合は、ShowLevelsメソッドを使用します。

オブジェクト.ShowLevels(RowLevels,ColumnLevels)

引数RowLevelsは行のレベル、引数ColumnLevelsは列のレベルを指定するもので、いずれか片方の値は必ず指定する必要があります。

Sample2()はレベル1のみ表示、Sample3()はレベル2まで表示、Sample4()はすべて展開となっており、すべて展開したい場合は実際に設定されているレベルよりも大きな値を設定すればOKです。

例えば、これらをボタンなどに登録しておくと1クリックで展開や折り畳みが出来るので用途によっては便利な場合もあるかと思います。

◆グループ化解除

Sub Sample5()

  'セルA2を含めた範囲のグループ化を解除
  Range("A2").CurrentRegion.ClearOutline
 
End Sub

グループ化を解除する場合はClearOutlieメソッドを使用します。

オブジェクト.ClearOutline

このオブジェクトには解除対象を指定しますので、セルA2を含む表全体のグループ解除であればRange(“A2”).CurrentRegion.ClearOutlineのようになります。

またClearOutlineメソッドは部分的にグループ解除することもできます。その場合は解除したい行や列を指定すればOKなので、本サンプルの3～5行目を解除したい時はこのような記述になります。

Sub Sample6()

  '3～5行のグループ化を解除
  Rows("3:5").ClearOutline
 
End Sub

3.まとめ

VBAを扱ってデータ収集を行う機会は多いと思いますが、そのデータを集計していく作業を補助する機能としては有用ではないかと思います。




但し、この処理を実行すると自動的に集計などを行ってくれますがグループ化解除しても元の表に戻してくれる訳ではありませんので、そこが必要な場合は別シートにコピーしてから実行するなどの配慮が必要になりますから、その点だけご注意を。

以上、【VBA】表をグループ化し集計／解除、折り畳みや展開を行う方法についてでした！今回の記事が何かの参考になれば幸いです。

Ryo