8/11/2024

Excel。VBA。四半期集計と総合計の集計行を追加したい【SUBTOTAL】

Excel。VBA。四半期集計と総合計の集計行を追加したい

<Excel VBA>

年間の売上表があります。


四半期集計の行と、最終行に、総合計の行を追加したのですが、定期的に行わないといけない作業だそうです。


また、単純な作業ですが、挿入と合計を作っていくのは面倒です。


そこで、Excel VBAで処理をさせようと考えてみました。


つくってみたのは、次のプログラム文です。

Sub 四半期集計()

    Dim i As Integer

    Dim j As Integer

    Dim total As Integer

    

    total = 0

    

    For i = 14 To 5 Step -3

        Rows(i).Insert

        Cells(i, "a") = "四半期計"

        Cells(i, "b").Formula = "=sum(b" & i - 3 & ":b" & i - 1 & ")"

        total = total + Cells(i, "b")

    Next

    

    j = (Cells(Rows.Count, "a").End(xlUp).Row) + 1

    Cells(j, "a") = "総合計"

    Cells(j, "b") = total

End Sub


では、実行してみます。

Excel VBA で四半期集計

このように、四半期集計と最終行に総合計を表示することができました。


では、プログラム文を確認します。

最初は、変数の宣言です。

Dim i As Integer

Dim j As Integer

Dim total As Integer


総合計を代入する変数totalを初期化します。

total = 0

    

今回は、For To Step Next文で繰り返し処理をするようにしました。


For i = 14 To 5 Step -3

ここがポイントになります。


人が作業する場合、上から行を挿入しますが、そのやり方で繰り返し文をつくってしまうと、繰り返しの上限の行数では、行が追加されるため、対応することができません。


そこで、後ろ側から追加してあげるようにします。


そのため、

For i = 14 To 5 Step -3

Step -3 としているわけです。


行を挿入します。

Rows(i).Insert


A列に 四半期集計 と表示させます

Cells(i, "a") = "四半期計"


B列に、四半期の合計を算出させます。FormulaプロパティでSUM関数の数式をつくります。

Cells(i, "b").Formula = "=sum(b" & i - 3 & ":b" & i - 1 & ")"


総合計を求めるために、四半期をtotalに加算していきます。

total = total + Cells(i, "b")


四半期行が追加された状態の表の最終行に+1した値をjに代入します。

    j = (Cells(Rows.Count, "a").End(xlUp).Row) + 1


一番下に行をInsertする必要はないので、

A列に総合計と表示します。


    Cells(j, "a") = "総合計"


B列に総合計の値を表示します。


    Cells(j, "b") = total