こんにちは！かいけつマクロです。
前回第15講までで、売上げ情報の集計に必要な準備は、全て完了しました。本当にお疲れ様です！
今回は、いよいよ、最終的な目標だった、商品毎の月別の売上高を集計します。
ここまで読んでいただいた方なら、きっと全く難しくないと、感じていただけるはず！
最後まで気を引き締めて頑張りましょう！
①ファイル一覧を作成する …済（第12講）
②ファイル一覧を利用して、データを取込む …済（第13講、第14講）
③集計しやすいように、「商品＞年月」の順に、データをソートする …済（第15講）
④ソートされたデータを用いて、データを集計する …今回（第16講）
売上高を「商品毎に」集計する
最終的な目標は、商品毎、かつ、月別に売上げを集計することですが、いきなりゴールに到達するのは大変。。。
そこで、まずは、「商品毎に」売上高を集計してみましょう！
商品が切り替わるまで足し合わせる
商品毎に集計するには、
売上げ情報をForループで足し合わせ、
商品が変わったところで、集計結果シートに反映させる
のでした。（詳しくは、第15講を参照）
そこで、こんなコードを追加してみましょう。
'商品毎・月別に売上数量を集計する
Sub summarize_by_product_month()
'「集計結果.xlsm」と、「集計結果」「wk_売上げ情報一覧_sorted」シートを定義=============
Dim b集計結果 As Workbook
Dim s集計結果 As Worksheet
Dim sWk_売上げ情報一覧_sorted As Worksheet
Set b集計結果 = ThisWorkbook
Set s集計結果 = b集計結果.Worksheets("集計結果")
Set sWk_売上げ情報一覧_sorted = b集計結果.Worksheets("wk_売上げ情報一覧_sorted")
Dim i As Long '「wk_売上げ情報一覧_sorted」シートの行番号（データの取得元）
Dim j As Long '「集計結果」シートの行番号（データの書込先）
Dim tmp_uriagesuryo As Long '売上数量を集計するための変数
j = 2 '変数jを初期化
tmp_uriagesuryo = 0 '変数tmp_uriagesuryoを初期化
For i = 2 To sWk_売上げ情報一覧_sorted.Cells(Rows.Count, 1).End(xlUp).Row
'商品毎・年月別に売上数量を集計する===============================
'1つ前のループと同一商品、かつ、同一年月の場合--------
If sWk_売上げ情報一覧_sorted.Cells(i - 1, 2) = sWk_売上げ情報一覧_sorted.Cells(i, 2) Then
'売上数量を追加する
tmp_uriagesuryo = tmp_uriagesuryo + sWk_売上げ情報一覧_sorted.Cells(i, 3)
'1つ前のループと商品、または、年月が異なる場合--------
Else
'1つ前のループまでの情報を、集計結果シートに書き込む
s集計結果.Cells(j, 1) = sWk_売上げ情報一覧_sorted.Cells(i - 1, 2)
s集計結果.Cells(j, 2) = Format(sWk_売上げ情報一覧_sorted.Cells(i - 1, 1), "yyyy") & "年" & Format(sWk_売上げ情報一覧_sorted.Cells(i - 1, 1), "mm") & "月"
s集計結果.Cells(j, 3) = tmp_uriagesuryo
'売上数量を初期化して、一から集計を開始する
tmp_uriagesuryo = sWk_売上げ情報一覧_sorted.Cells(i, 3)
'「集計結果」シートの書込先の行番号を次に進める
j = j + 1
End If
Next
End Sub
ここまで、頑張って勉強していただいた方には、おちゃのこさいさい のはず
簡単に内容を説明すると、
- 1～2行目：Subプロシージャを定義
- 4～17行目：処理に利用する、エクセルブック・シートや、売上数量を集計するための変数を定義
- 19～45行目：For文で、ソート済みの売上げ情報一覧から売上げ情報を取得し、商品が切り替わったタイミングで「集計結果」シートに書き込む
- 47行目：End Sub
実際にVBAマクロを実行してみる
ようやく準備が完了しました！
「summarize_by_product_month」プロシージャにカーソルを当てて、VBAマクロを実行してみましょう。（F5を押して実行）
・・・あれ？
2行目に、変な行が含まれてしまっています。。。
それもそのはず。先ほどの実装では、
1つ前のループと商品が異なる場合は、1つ前のループまでで取得した情報を、
「集計結果」シートに書き込む
ようにしていました。
・・・ということは、1回目のループ（＝「wk_売上げ情報一覧_sorted」シートの2行目）の場合は、
- 「wk_売上げ情報一覧_sorted」シートの1行目
- 「wk_売上げ情報一覧_sorted」シートの2行目
を比較し、異なる場合は、
「wk_売上げ情報一覧_sorted」シートの1行目 を、「集計結果」シートの2行目 に
書き込んでいたのです。
集計結果にヘッダが含まれないようにする
それでは、集計結果にヘッダ行を含まないように調整してみましょう。
「summarize_by_product_month」プロシージャを、以下のように修正します。
'商品毎・月別に売上数量を集計する
Sub summarize_by_product_month()
'「集計結果.xlsm」と、「集計結果」「wk_売上げ情報一覧_sorted」シートを定義=============
Dim b集計結果 As Workbook
Dim s集計結果 As Worksheet
Dim sWk_売上げ情報一覧_sorted As Worksheet
Set b集計結果 = ThisWorkbook
Set s集計結果 = b集計結果.Worksheets("集計結果")
Set sWk_売上げ情報一覧_sorted = b集計結果.Worksheets("wk_売上げ情報一覧_sorted")
Dim i As Long '「wk_売上げ情報一覧_sorted」シートの行番号（データの取得元）
Dim j As Long '「集計結果」シートの行番号（データの書込先）
Dim tmp_uriagesuryo As Long '売上数量を集計するための変数
j = 2 '変数jを初期化
tmp_uriagesuryo = 0 '変数tmp_uriagesuryoを初期化
For i = 2 To sWk_売上げ情報一覧_sorted.Cells(Rows.Count, 1).End(xlUp).Row
If i <> 2 Then
'商品毎・年月別に売上数量を集計する===============================
'1つ前のループと同一商品、かつ、同一年月の場合--------
If sWk_売上げ情報一覧_sorted.Cells(i - 1, 2) = sWk_売上げ情報一覧_sorted.Cells(i, 2) Then
'売上数量を追加する
tmp_uriagesuryo = tmp_uriagesuryo + sWk_売上げ情報一覧_sorted.Cells(i, 3)
'1つ前のループと商品、または、年月が異なる場合--------
Else
'1つ前のループまでの情報を、集計結果シートに書き込む
s集計結果.Cells(j, 1) = sWk_売上げ情報一覧_sorted.Cells(i - 1, 2)
s集計結果.Cells(j, 2) = Format(sWk_売上げ情報一覧_sorted.Cells(i - 1, 1), "yyyy") & "年" & Format(sWk_売上げ情報一覧_sorted.Cells(i - 1, 1), "mm") & "月"
s集計結果.Cells(j, 3) = tmp_uriagesuryo
'売上数量を初期化して、一から集計を開始する
tmp_uriagesuryo = sWk_売上げ情報一覧_sorted.Cells(i, 3)
'「集計結果」シートの書込先の行番号を次に進める
j = j + 1
End If
End If
Next
End Sub
修正内容は、FOR分の中の売上数量を集計処理を、全てIF文で囲っています。
追加したIF文の内容は、「wk_売上げ情報一覧_sorted」シートの行番号（変数i）が、
- 2の場合（＝ヘッダ行） ⇒ 22～44行目は実施しない
- 2以外の場合（＝ヘッダ行以外） ⇒ 22～44行目を実施する
それでは、改めて、VBAマクロを実行してみましょう。
（「summarize_by_product_month」プロシージャにカーソルを当てて、F5を押す）
これで、集計結果シートに不要な行が追加されなくなりました！
商品毎に加えて、月別にも集計できるようにする
ここまでで、商品毎には集計できるようになりました。
次に、月別にも集計できるようにしましょう。
'商品毎・月別に売上数量を集計する
Sub summarize_by_product_month()
'「集計結果.xlsm」と、「集計結果」「wk_売上げ情報一覧_sorted」シートを定義=============
Dim b集計結果 As Workbook
Dim s集計結果 As Worksheet
Dim sWk_売上げ情報一覧_sorted As Worksheet
Set b集計結果 = ThisWorkbook
Set s集計結果 = b集計結果.Worksheets("集計結果")
Set sWk_売上げ情報一覧_sorted = b集計結果.Worksheets("wk_売上げ情報一覧_sorted")
Dim i As Long '「wk_売上げ情報一覧_sorted」シートの行番号（データの取得元）
Dim j As Long '「集計結果」シートの行番号（データの書込先）
Dim tmp_uriagesuryo As Long '売上数量を集計するための変数
j = 2 '変数jを初期化
tmp_uriagesuryo = 0 '変数tmp_uriagesuryoを初期化
For i = 2 To sWk_売上げ情報一覧_sorted.Cells(Rows.Count, 1).End(xlUp).Row
If i <> 2 Then
'商品毎・年月別に売上数量を集計する===============================
'1つ前のループと同一商品、かつ、同一年月の場合--------
If sWk_売上げ情報一覧_sorted.Cells(i - 1, 2) = sWk_売上げ情報一覧_sorted.Cells(i, 2) And _
Year(sWk_売上げ情報一覧_sorted.Cells(i - 1, 1)) = Year(sWk_売上げ情報一覧_sorted.Cells(i, 1)) And _
Month(sWk_売上げ情報一覧_sorted.Cells(i - 1, 1)) = Month(sWk_売上げ情報一覧_sorted.Cells(i, 1)) Then
'売上数量を追加する
tmp_uriagesuryo = tmp_uriagesuryo + sWk_売上げ情報一覧_sorted.Cells(i, 3)
'1つ前のループと商品、または、年月が異なる場合--------
Else
'1つ前のループまでの情報を、集計結果シートに書き込む
s集計結果.Cells(j, 1) = sWk_売上げ情報一覧_sorted.Cells(i - 1, 2)
s集計結果.Cells(j, 2) = Format(sWk_売上げ情報一覧_sorted.Cells(i - 1, 1), "yyyy") & "年" & Format(sWk_売上げ情報一覧_sorted.Cells(i - 1, 1), "mm") & "月"
s集計結果.Cells(j, 3) = tmp_uriagesuryo
'売上数量を初期化して、一から集計を開始する
tmp_uriagesuryo = sWk_売上げ情報一覧_sorted.Cells(i, 3)
'「集計結果」シートの書込先の行番号を次に進める
j = j + 1
End If
End If
Next
End Sub
変更したのは、26・27行目。
なんとなく、意味は分かるかもしれませんが、
EXCELから取得した日付の、「年」と「月」を抜き出して、
それらが各々一致しているかを検証
しています。
それでは、改めて、VBAマクロを実行してみましょう。
（「summarize_by_product_month」プロシージャにカーソルを当てて、F5を押す）
想定通り、商品毎・月別に集計できたように見えますが、、、
あれ？今度は、「餃子」の「2021年12月」の明細が存在しません。
ループの最後に、書き込み処理を追加する
その原因は、ズバリ、
ループの一番最後の処理（「餃子」の「2021年12月」）で、
「集計結果」シートに書き込む処理がないため
です。
ということで、ループの一番最後に書き込み処理を追加します。
'商品毎・月別に売上数量を集計する
Sub summarize_by_product_month()
'「集計結果.xlsm」と、「集計結果」「wk_売上げ情報一覧_sorted」シートを定義=============
Dim b集計結果 As Workbook
Dim s集計結果 As Worksheet
Dim sWk_売上げ情報一覧_sorted As Worksheet
Set b集計結果 = ThisWorkbook
Set s集計結果 = b集計結果.Worksheets("集計結果")
Set sWk_売上げ情報一覧_sorted = b集計結果.Worksheets("wk_売上げ情報一覧_sorted")
Dim i As Long '「wk_売上げ情報一覧_sorted」シートの行番号（データの取得元）
Dim j As Long '「集計結果」シートの行番号（データの書込先）
Dim tmp_uriagesuryo As Long '売上数量を集計するための変数
j = 2 '変数jを初期化
tmp_uriagesuryo = 0 '変数tmp_uriagesuryoを初期化
For i = 2 To sWk_売上げ情報一覧_sorted.Cells(Rows.Count, 1).End(xlUp).Row
If i <> 2 Then
'商品毎・年月別に売上数量を集計する===============================
'1つ前のループと同一商品、かつ、同一年月の場合--------
If sWk_売上げ情報一覧_sorted.Cells(i - 1, 2) = sWk_売上げ情報一覧_sorted.Cells(i, 2) And _
Year(sWk_売上げ情報一覧_sorted.Cells(i - 1, 1)) = Year(sWk_売上げ情報一覧_sorted.Cells(i, 1)) And _
Month(sWk_売上げ情報一覧_sorted.Cells(i - 1, 1)) = Month(sWk_売上げ情報一覧_sorted.Cells(i, 1)) Then
'売上数量を追加する
tmp_uriagesuryo = tmp_uriagesuryo + sWk_売上げ情報一覧_sorted.Cells(i, 3)
'ループの一番最後の場合、書き込み処理を行う
If i = sWk_売上げ情報一覧_sorted.Cells(Rows.Count, 1).End(xlUp).Row Then
'集計結果シートに書き込む
s集計結果.Cells(j, 1) = sWk_売上げ情報一覧_sorted.Cells(i - 1, 2)
s集計結果.Cells(j, 2) = Format(sWk_売上げ情報一覧_sorted.Cells(i - 1, 1), "yyyy") & "年" & Format(sWk_売上げ情報一覧_sorted.Cells(i - 1, 1), "mm") & "月"
s集計結果.Cells(j, 3) = tmp_uriagesuryo
End If
'1つ前のループと商品、または、年月が異なる場合--------
Else
'1つ前のループまでの情報を、集計結果シートに書き込む
s集計結果.Cells(j, 1) = sWk_売上げ情報一覧_sorted.Cells(i - 1, 2)
s集計結果.Cells(j, 2) = Format(sWk_売上げ情報一覧_sorted.Cells(i - 1, 1), "yyyy") & "年" & Format(sWk_売上げ情報一覧_sorted.Cells(i - 1, 1), "mm") & "月"
s集計結果.Cells(j, 3) = tmp_uriagesuryo
'売上数量を初期化して、一から集計を開始する
tmp_uriagesuryo = sWk_売上げ情報一覧_sorted.Cells(i, 3)
'「集計結果」シートの書込先の行番号を次に進める
j = j + 1
End If
End If
Next
End Sub
修正したのは、32～38行目の追加した部分です。
もう一度、VBAマクロを実行してみると、
「餃子」の「2021年12月」の明細が表示されるようになりました！
おめでとうございます。これで、VBAの実装は完了です！
本日のまとめ
今回は、商品毎・月別など、あるまとまりで集計する方法を学びました。
まとまりごとに集計するためには、ソートして、切り替わるところまで足し合わせる、というのがポイントになります。
そして、FORループの実装方法によって、思ったようにデータが出力できない場合があることも分かったと思います。（特に、ループの最初と最後には要注意！）
これについては、VBAマクロや、その他の言語の実装に慣れている人でもやってしまうものなので、ある程度は仕方ないのですが、それよりも重要なのは、VBA実装後のテストを十分に行い、実際に利用を開始する前に、なるべくバグをなくしておく ということです。
みなさんも、「実装」が終わったら、必ず「テスト」を実施して、思った通りの結果になっているか、確かめるクセをつけるようにしましょう！
そして、今回で、VBAマクロの講義としては終了です。本当にお疲れさまでした！
皆さんのVBAマクロの実力は、勉強を開始する前から格段に上がっていると思います。
こんなVBAマクロ作ったぞ、ドヤ！という方は、ぜひ、コメントから教えていただけるとありがたいです。
今回で一旦のVBAマクロの講義は完了なのですが、今のままでは、いちいちVBEを開く必要があり、少々面倒に思っている方もいらっしゃるかもしれません。
そこで、次回は、EXCEL上に設置したボタンをクリックするだけで、VBAマクロを実行できるようにします。
ぜひ、次回もお楽しみにして下さい。
- まとまり毎の集計は、ソートして、切り替わるところまで足し合わせる
- FOR文などのループ処理は、データが正しく出力されているか、入念にテストを行う（特に、ループの最初と最後には要注意！）
コメント