Excel。VBA。手早く小計機能で処理したいので、プログラムをつくってみた
＜Excel VBA：subtotal＞
Excelの小計機能を使うときには、最初に並べ替えをして、そのあとに、データタブの「小計」をつかって、集計するわけですね。
ただ、処理数が多く、意外と面倒な処理だといえます。
そこで、Excel VBAでプログラムをつくって対応していきます。
次のデータを用意しました。
では、次のようにブログラムをつくってみました。
Sub 小計機能()
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add2 Key:=Range("b1"), Order:=xlAscending
.SetRange Range("a1").CurrentRegion
.Header = xlYes
.Apply
End With
Range("a1").CurrentRegion.Subtotal groupby:=2, Function:=xlSum, totallist:=4
End Sub
実行してみます。
このように、小計処理をおこなうことができました。
では、プログラム文を説明していきます。
色々なプログラム言語とExcel VBAが少々ことなるところは、あくまでも、「Excel」なんだということです。
Excelのダイアログボックスをつかった処理は、そのダイアログボックスと同じ項目を設定する必要があります。
小計機能は、基準となる列を並べ替え処理をして、まとめておく必要があります。
並べ替えをしているのが、下記の部分です。
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add2 Key:=Range("b1"), Order:=xlAscending
.SetRange Range("a1").CurrentRegion
.Header = xlYes
.Apply
End With
これは、並べ替えのダイアログボックスをみれば、どこを設定しているかが、わかります。
「SortFields.Add2 Key:=Range("b1"), Order:=xlAscending」の「SortFields.Add2 Key:=Range("b1")」は、最優先されるキーです。
商品名ごとの集計をしたいので、見出しの「商品名」があるのがB1なので、「Range("b1")」とします。
「Order:=xlAscending」は、順序なので、昇順で並べ替えをしたいので、「xlAscending」と設定します。
「SetRange Range("a1").CurrentRegion」は、並べ替えは自動的に範囲選択をしますので、A1を起点として連続する範囲を、並べ替えの対象とします。
「Header = xlYes」は、先頭行を見出しとして使用するのチェックマークです。
「Apply」は、OKボタンですね。
これで、並べ替えが行われます。
そして、小計機能をおこなうわけです。
この小計機能もダイアログボックスなので、それを設定するように、プログラムをつくってあげます。
Range("a1").CurrentRegion.Subtotal groupby:=2, Function:=xlSum, totallist:=4
出だしの「Range("a1").CurrentRegion.Subtotal」は、A1を起点とした範囲を「Subtotal」＝小計します。
「groupby:=2」は、グループの基準です。
左から2列目の商品名を基準として集計しますので、「2」です。
「Function:=xlSum」は、集計方法です。合計なので、「xlSum」と設定します。
「totallist:=4」は、集計するフィールドです。
左から4列目の売上高の集計をしたいので、「4」と設定します。
これで、小計をすることができます。
また、データ量が増減しても、「CurrentRegion」としていることで、対応することも可能です。