Excel。VBA。読み込んだデータで、月が替わる行に小計行を追加したい
＜Excel VBA＞
データを読み込んだら、ついでに、面倒な処理を自動的に行わせることができたら便利ですよね。
例えば、次のようなデータを読み込んだとします。
読み込んだ後に自動的に、月が替わったら、行を追加して、追加した行に月ごとの小計を算出したいとします。
こんな感じですね。
月が替わるところを見つけて、行を挿入して、さらに、月ごとの小計を算出するだけですが、イチイチこの作業を繰り返すのは面倒ですね。
しかも、月ごとのデータの件数が異なっていれば、SUM関数などの引数の範囲をその都度設定するのは、さらに面倒です。
そこで、Excel VBAでマクロを作ってしまうと、大幅に時短できますし、何より楽ができます。
色々なプログラム文があるとは思いますが、次のように作ってみました。
Sub 月別小計()
Dim i As Long
Dim mon As Integer
Dim n_mon As Integer
Dim sub_t As Long
i = 2
mon = Month(Range("a2"))
sub_t = 0
Do While Cells(i, "a") <> ""
n_mon = Month(Cells(i, "a"))
If mon = n_mon Then
sub_t = sub_t + Cells(i, "b")
Else
Cells(i, "a").EntireRow.Insert
Cells(i, "a").Value = mon & "月合計"
Cells(i, "b").Value = sub_t
sub_t = 0
mon = n_mon
End If
i = i + 1
If Cells(i, "a") = "" Then
Cells(i, "a").Value = mon & "月合計"
Cells(i, "b").Value = sub_t
i = i + 1
End If
Loop
End Sub
それでは、説明していきます。
お馴染みの変数宣言の文からスタートですね。
Dim i As Long
Dim mon As Integer
Dim n_mon As Integer
Dim sub_t As Long
monは、今月の値をいれる変数です。
n_monは、次のデータの月をいれる変数です。
sub_tは、月ごとの小計を算出する時に使う変数です。
初期値を設定します。
i = 2
mon = Month(Range("a2"))
sub_t = 0
mon = Month(Range("a2")) は、最初のデータの月を代入しています。
Do While Cells(i, "a") <> "" ～ Loop をつかって繰り返し処理をしています。
Do While Cells(i, "a") <> "" は、A列の値が空白でないという意味ですね。
つまり、データが無くなるまで繰り返し処理を行いたいわけです。
繰り返し処理のDo Whileの中を確認していきましょう。
n_mon = Month(Cells(i, "a")) は、次のデータの月の値を代入しています。
月替わりしたかどうかを確認するために、If～Then～Else～End Ifで条件分岐させた処理をします。
If mon = n_mon Then で、今までの月の値と、このデータの月の値を比べます。
もしも、同じだったらば、
sub_t = sub_t + Cells(i, "b") で、小計の合計値に、このデータの売上高のデータを追加していきます。
Else で、月の値に違いが発生している場合、つまり月が替わった時の処理を行います。
Cells(i, "a").EntireRow.Insert で、該当のセルの下側に空白行を追加することができます。
Cells(i, "a").Value = mon & "月合計" は、追加した行に、「○月合計」という見出しを入力させています。
Cells(i, "b").Value = sub_t は、月ごとの合計値を入力しています。
次の2行は、月が替わったので、初期化の作業をしています。
sub_t = 0
mon = n_mon
次の行のデータを参照させるための作業が、i = i + 1 です。
これで、メインのプログラムは終わっているのですが、このまま実行すると、空白行まで繰り返すという処理なので、最終月。
今回は、12月にあたりますが、その合計値の行を表示する作業を行わないで終了してしまいます。
そこで、次のプログラム文をいれました。
If Cells(i, "a") = "" Then
Cells(i, "a").Value = mon & "月合計"
Cells(i, "b").Value = sub_t
i = i + 1
End If
これで、月が替わった時に、自動的に空白行を追加してその追加した行に月ごとの合計値を算出することができました。
このように、ちょっとしたプログラム文でも作業効率を改善できるかもしれませんので、Excel VBAで対応できそうなところがあれば、少しずつでも移行していくといいのかもしれませんね。
