7/16/2023

Excel。VBA。手早く小計機能で処理したいので、プログラムをつくってみた【subtotal】

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」としていることで、対応することも可能です。