Excel。マクロ41。四半期小計と総計を挿入するのが面倒なのでマクロにしてみる
＜Excel VBA＞
Excelのマクロ。最近では現場で使っているという人が増えているようですね。
確かに簡単な操作ほど、マクロを作って処理をするほうが楽だし、『時短』にもつながりますからね。
さて、今回は、次のような表があります。
この表の途中に、四半期計の行を挿入して売上高と来店客数の四半期小計を算出して、最終行に、総計を算出する行を挿入していきたい場合のマクロを作っていきます。
やる処理自体は、簡単なんですよね。
行を挿入して、行見出しを入力して、売上高と来店客数をそれぞれ、オートSUMボタンを使って小計を算出する。
だけど、面倒なんです。
こういう時こそ、マクロを作っていくといいわけですね。
では、Excel VBAでつくってみましょう。
Sub subtotal()
Dim total_uriage As Long
Dim total_kyaku As Long
Dim i As Integer
Dim lastrow As Long
total_uriage = 0
total_kyaku = 0
For i = 2 To 13
total_uriage = Range("b" & i) + total_uriage
total_kyaku = Range("c" & i) + total_kyaku
Next
lastrow = Range("a1048576").End(xlUp).Row + 1
For i = lastrow To 5 Step -3
Rows(i).Insert
Range("a" & i).Value = "四半期計"
Range("b" & i).Formula = "=subtotal(109,b" & i - 3 & ":b" & i - 1 & ")"
Range("c" & i).Formula = "=subtotal(109,c" & i - 3 & ":c" & i - 1 & ")"
Next
lastrow = Range("a1048576").End(xlUp).Row + 1
Range("a" & lastrow).Value = "合計"
Range("b" & lastrow).Value = total_uriage
Range("c" & lastrow).Value = total_kyaku
End Sub
ちょっと長くなりましたが、やっていること自体は簡単です。
プログラマーさんならば、もっとここをこうして、となると思いますが、一般事務職の人でも作りやすいようにしたほうが実践的かなぁ～と。
では、実行してみましょう。
このように、四半期小計が挿入されて、それぞれの小計が算出されていますね。
さらに、最終行に合計行も挿入できています。
では、Excel VBAの中身を確認してみましょう。
最初は、お馴染み、変数宣言ですね。
Dim total_uriage As Long は、B18の売上高の合計で使います。
Dim total_kyaku As Long は、C18の来店客数の合計で使います。
Dim i As Integer 繰り返しで使う変数ですね。
Dim lastrow As Long 最終行の行番号で使います。
続いて
total_uriage = 0
total_kyaku = 0
念のために、0で初期化しておきます。
そして、18行目の合計を先に算出しておきます。
これは、四半期計を挿入してから算出するのが面倒なので、先に、For To Nextでそれぞれの合計を算出します。
For i = 2 To 13
total_uriage = Range("b" & i) + total_uriage
total_kyaku = Range("c" & i) + total_kyaku
Next
【四半期計の行を挿入】
最終行の行番号を変数にいれておきます。もっと大きなデータの場合でも対応できるようにしております。lastrow = Range("a1048576").End(xlUp).Row + 1
そして、最終行に+1（プラス1）した数値まで繰り返します。理由として、四半期計の行挿入をしますので、+1をしておきます。
繰り返し処理ですが、行を挿入する場合、下側から追加するようにします。
なぜならば、上側から行を挿入すると、当然行数が増えるわけなので、繰り返しの数が変わってしまうので、きちんと行を指定の場所に挿入することができません。
そのために、Step -3をしてカウントさせています。
For i = lastrow To 5 Step -3
Rows(i).Insert
Range("a" & i).Value = "四半期計"
Range("b" & i).Formula = "=subtotal(109,b" & i - 3 & ":b" & i - 1 & ")"
Range("c" & i).Formula = "=subtotal(109,c" & i - 3 & ":c" & i - 1 & ")"
Next
今回は、SUBTOTAL関数で数式を作っていますが、SUM関数でも構いません。
ただ、小計をつかって、算出するようなことがある場合には、SUBTOTAL関数のほうが都合がいいので、今回は、SUBTOTAL関数を使用しています。
【最終行に合計を挿入】
lastrow = Range("a1048576").End(xlUp).Row + 1四半期計の行を挿入しましたので、最終行の行番号が変わっていますので、再度最終行を取得します。
そして、その下に、合計行を挿入したいので、+1（プラス1）します。
Range("a" & lastrow).Value = "合計"
Range("b" & lastrow).Value = total_uriage
Range("c" & lastrow).Value = total_kyaku
これで、完成ですね。
単純な処理ほど、マクロを作って処理すると作業効率が改善できるかもしれませんね。
