VBAでExcelの集計を自動化しよう
こんにちは。分析部剣士の武彦です。
データ分析の仕事をする際、欠かせないのがExcelです。
関数でデータを加工したり、ピボットで集計したり、グラフを作成したり…
誰でも簡単に扱えて便利ですよね。
でも、ピボットやグラフを作成する際に毎回データ範囲を選択して、挿入タブをクリックして…
とやるのは本格的にデータを扱うようになると時間もかかるし面倒です。
そこで便利なのが"マクロ"です。
皆さんの中にも「マクロの記録」を使って、簡単な反復作業を自動化したことがある人は多いのではないでしょうか。
また、多少なりともプログラミングができる方なら、マクロの処理内容が記述された"VBA"のプログラムを書き換えたりして、より便利に活用しているのではないでしょうか。
この記事は、そんな「"VBA"のプログラムを書き換えたりして」活用している方向けの記事になります。
1.売上データを集計して月ごとの売上金額グラフを作ろう！
以下のような、店舗の売上データから月ごとの売上金額を1年分マクロで集計して、棒グラフを作ってみましょう。
2.ピボットテーブルを作って集計するマクロを組もう
以下のようなVBAで、「元データ」シートにあるデータから「ピボット」シートにピボットテーブルを作成して集計できます。
Sub ピボットマクロ()
'元データ取得
Set data_range = ThisWorkbook.Worksheets("元データ").Range("A1").CurrentRegion
'ピボット作成して名前設定
ThisWorkbook.PivotCaches.Create(xlDatabase, data_range).CreatePivotTable ThisWorkbook.Worksheets("ピボット").Range("A3")
ThisWorkbook.Worksheets("ピボット").PivotTables(ThisWorkbook.Worksheets("ピボット").PivotTables.Count).Name = "ピボット1"
'行と集計値の設定
With ThisWorkbook.Worksheets("ピボット").PivotTables("ピボット1")
.PivotFields("年").Orientation = xlRowField '行フィールド設定
.PivotFields("月").Orientation = xlRowField '行フィールド設定
.PivotFields("売上金額").Orientation = xlDataField '値フィールド設定
.RowAxisLayout xlTabularRow
End With
End Sub
この集計値をコピーして棒グラフにしてみましょう。
おっと、何かおかしいですね…
3.ピボットテーブルの罠
普通は2ヶ月間も全く売上データが無いなんてことは起こりませんが…2020年と言えばあの年ですね。この店舗は2ヶ月間も営業自粛していたようです。
しかし、時系列グラフですからこれではいけません。
3月と6月の間も、他と同じく1ヶ月間隔でなければいけません。
ピボットテーブルを使う方法だと、こういったイレギュラーなデータが入力された際に適切に対応させるのが難しくなることがあります。
4.VBAの配列に集計しよう
そこで私のおすすめする方法が、ピボットテーブルを使わずVBAの配列に集計していく方法です。
Sub 集計マクロ()
Dim output_array(1 To 12, 1 To 2) As Long
Dim input_array As Variant
Dim end_row As Long
Dim input_row As Long
'元データを取得します
With ThisWorkbook.Worksheets("元データ")
end_row = .Cells(.Rows.Count, 1).End(xlUp).Row '元データの最終行を取得
input_array = .Range(.Cells(1, 1), .Cells(end_row, 5)) '元データを配列に格納
End With
'取得した元データを1行ずつ処理していきます
For input_row = 2 To end_row
If input_array(input_row, 1) = 2020 Then '2020年のデータだけ集計する
'1月ならoutput_array(1,2)に、2月ならoutput_array(2,2)に...と集計していきます
output_array(input_array(input_row, 2), 2) = output_array(input_array(input_row, 2), 2) + input_array(input_row, 5)
End If
Next input_row
'集計結果を出力します
With ThisWorkbook.Worksheets("集計結果")
'その前に表側を設定
output_array(1, 1) = 1
output_array(2, 1) = 2
output_array(3, 1) = 3
output_array(4, 1) = 4
output_array(5, 1) = 5
output_array(6, 1) = 6
output_array(7, 1) = 7
output_array(8, 1) = 8
output_array(9, 1) = 9
output_array(10, 1) = 10
output_array(11, 1) = 11
output_array(12, 1) = 12
.Range(.Cells(2, 1), .Cells(13, 2)) = output_array
End With
End Sub上記のVBAを実行すると、「集計結果」シートに以下のように結果が出力されます。
ちゃんと4月と5月の項目も入ってますね。
棒グラフもきちんと1ヶ月間隔で作成されます。
このようにVBAの配列に集計していく方法だと、予期せぬデータの”歯抜け”にも影響されずに集計表やグラフを作成することが簡単にできます。
また、ピボットテーブルを使う方法に比べて
・実行が早い
・複雑な集計条件でも対応できる
という長所があります。
多少のプログラミングスキルは必要になりますが、一段上の”VBA使い”を目指して、こちらの方法にも挑戦してみてはいかがでしょう。
お読みいただきありがとうございました。
ここまでお読みいただき、ありがとうございました！
この記事が少しでも参考になりましたら「スキ」を押していただけると幸いです！
株式会社分析屋について
弊社が作成を行いました分析レポートを、鎌倉市観光協会様HPに掲載いただきました。
ホームページはこちら。
noteでの会社紹介記事はこちら。
【データ分析で日本を豊かに】
分析屋はシステム分野・ライフサイエンス分野・マーケティング分野の知見を生かし、多種多様な分野の企業様のデータ分析のご支援をさせていただいております。 「あなたの問題解決をする」をモットーに、お客様の抱える課題にあわせた解析・分析手法を用いて、問題解決へのお手伝いをいたします！
【マーケティング】
マーケティング戦略上の目的に向けて、各種のデータ統合及び加工ならびにPDCAサイクル運用全般を支援や高度なデータ分析技術により複雑な課題解決に向けての分析サービスを提供いたします。
【システム】
アプリケーション開発やデータベース構築、WEBサイト構築、運用保守業務などお客様の問題やご要望に沿ってご支援いたします。
【ライフサイエンス】
機械学習や各種アルゴリズムなどの解析アルゴリズム開発サービスを提供いたします。過去には医療系のバイタルデータを扱った解析が主でしたが、今後はそれらで培った経験・技術を工業など他の分野の企業様の問題解決にも役立てていく方針です。
【SES】
SESサービスも行っております。