あなたは毎週、毎月の売上報告書などを使った表の集計をするのが、面倒だと思ったことはありませんか？
例えば、売上報告書、販売集計など、表を基に担当者別、商品別の売上金額、数量などを定期的に行う場合、毎回エクセルのフィルターを使ってソートしていたり、ピボットテーブルを使って担当者別に表示したり・・・大変ですよね。
エクセルのマクロを使えば、表の集計は自動化できるので、かなりラクになります。
今回は「エクセルで表の集計とグラフ化を自動化するボタンを作る方法」を紹介します。
エクセルで表の集計とグラフ化を自動化するボタンを作る方法
エクセルで表の集計とグラフ化を自動化するボタンを作る方法です。
これが今回使うサンプルデータです。
よくある表ですよね。
csvデータをエクセルに落としたときの、ただデータが並んだ表です。
これを今回は、①月ごとの担当者別の売上合計 ②月ごとの機種別の売上数量 ③月ごとの機種別の売上合計 の３つの表作成を自動化するのを例に紹介します。
マクロを作るときは、「開発」タブを使います。
開発タブが出ていない方は、下のリンクで出し方を紹介していますので、まずは準備をお願いします。
→エクセルで開発タブを表示する方法
１．「開発」タブをクリック →「挿入」をクリック →「ボタン」をクリック
２．「新規作成」をクリック
３．コード入力画面が開く
４．コードを入力
コードを入力します。
新しいシートを作り、ピボットテーブルで、それぞれの表を作る、という意味です。
赤枠部のコードです。
Dim ws As Worksheet
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim lastRow As Long
Dim pivotCache As pivotCache
Dim pivotTable As pivotTable
Dim dataRange As Range
' 元データがあるシートを指定
Set ws = ThisWorkbook.Sheets("Sheet1") ' 元データがあるシート名を指定
' データの最終行を取得
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
' データ範囲を指定
Set dataRange = ws.Range("A1:I" & lastRow)
' 新しいシートを追加し、集計表を作成するシートを指定
Set ws1 = ThisWorkbook.Sheets.Add
ws1.Name = "担当者別合計金額"
Set ws2 = ThisWorkbook.Sheets.Add
ws2.Name = "機種別合計数量"
Set ws3 = ThisWorkbook.Sheets.Add
ws3.Name = "機種別合計金額"
' 担当者別合計金額のピボットテーブル作成
Set pivotCache = ThisWorkbook.PivotCaches.Create(xlDatabase, dataRange)
Set pivotTable = pivotCache.CreatePivotTable(ws1.Range("A1"), "担当者別合計金額")
With pivotTable
.PivotFields("日付").Orientation = xlRowField
.PivotFields("日付").NumberFormat = "yyyy年mm月"
.PivotFields("日付").ShowAllItems = True ' 全ての月を表示
' 担当者を列ラベルに設定
.PivotFields("担当者").Orientation = xlColumnField
' データフィールドとして合計金額を設定
.AddDataField .PivotFields("合計"), "合計金額", xlSum
End With
' 機種別合計数量のピボットテーブル作成
Set pivotCache = ThisWorkbook.PivotCaches.Create(xlDatabase, dataRange)
Set pivotTable = pivotCache.CreatePivotTable(ws2.Range("A1"), "機種別合計数量")
With pivotTable
.PivotFields("日付").Orientation = xlRowField
.PivotFields("日付").NumberFormat = "yyyy年mm月"
.PivotFields("日付").ShowAllItems = True ' 全ての月を表示
' 機種を列ラベルに設定
.PivotFields("機種").Orientation = xlColumnField
' データフィールドとして合計数量を設定
.AddDataField .PivotFields("数量"), "合計数量", xlSum
End With
' 機種別合計金額のピボットテーブル作成
Set pivotCache = ThisWorkbook.PivotCaches.Create(xlDatabase, dataRange)
Set pivotTable = pivotCache.CreatePivotTable(ws3.Range("A1"), "機種別合計金額")
With pivotTable
.PivotFields("日付").Orientation = xlRowField
.PivotFields("日付").NumberFormat = "yyyy年mm月"
.PivotFields("日付").ShowAllItems = True ' 全ての月を表示
' 機種を列ラベルに設定
.PivotFields("機種").Orientation = xlColumnField
' データフィールドとして合計金額を設定
.AddDataField .PivotFields("合計"), "合計金額", xlSum
End Withコードを入力したら、コードの入力画面を閉じて下さい。
５．ボタンができた →ボタンをクリック
ボタンができたら、ボタンをクリックします。
６．それぞれの表ができる
今回作りたかった、3つの表ができました。
①担当者別合計金額
②機種別合計数量
③機種別合計金額
それぞれの表を作れましたが、月の表示が複数行に分かれているので、まとめていきます。
７．年・月の表示されているセルのところで、右クリック →グループ化 →左クリック
「2024年1月」など年月が表示されているセルのあたりで、右クリックし、メニューから「グループ化」を選んで、左クリックします。
８．「月」→「OK」をクリック
「月」を選んで、「OK」をクリックします。
９．月でまとまった
これで月ごとにまとまりました。
右端に月ごとの合計値も表示されています。
こっちの方が見やすいですね。
他の表も同じように月ごとにまとめます。
合計数量もまとまりました。
機種別の金額もまとまりました。
これで完了です。
後は、数字にカンマを入れるとか、見やすいように調整してみて下さい。
グラフ化してより見やすくする
集計で数字は把握できたら、次にグラフ化すると、より見やすくなります。
１．データを範囲選択 →「挿入」 →「おすすめグラフ」
グラフ化したいデータを選び、グラフを挿入します。
２．「折れ線」 →「積み上げ折れ線」を選択
ここでは月ごとのデータを見たいので、折れ線を選択しました。
３．グラフが挿入された
グラフができました。
グラフ化すると、例えば、黄色い線（担当者D）の12月の合計金額が多いな、というように、見やすくなります。
でも、これを手動でやると大変なので、自動化します。
これでボタンを押すと、表に加えて、グラフが挿入されました。
グラフのタイトルも自動で付きます。
①担当者別合計金額の積み上げ折れ線グラフ
②機種別合計数量の積み上げ折れ線グラフ
③機種別合計金額の積み上げ折れ線グラフ
これならグラフの作成も簡単ですね！
まとめ
エクセルで表の集計とグラフ化を自動化するボタンを作る方法のまとめです。
１．「開発」タブをクリック →「挿入」をクリック →「ボタン」をクリック
２．新規作成をクリック
３．コードを入れる画面が出る
４．コードを入力
５．ボタンができた →ボタンをクリック
６．表から新しい表ができた
７．表を見やすいように体裁を整える
これで3つの表とグラフが一度に作成できました。毎月・毎週の集計資料作成がグッと楽になりますね！
最後に、今回紹介したコードです。
Dim ws As Worksheet
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim lastRow As Long
Dim pivotCache As pivotCache
Dim pivotTable As pivotTable
Dim dataRange As Range
Dim chartObj As ChartObject
' 元データがあるシートを指定
Set ws = ThisWorkbook.Sheets("Sheet1") ' 元データがあるシート名を指定
' データの最終行を取得
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
' データ範囲を指定
Set dataRange = ws.Range("A1:I" & lastRow)
' 新しいシートを追加し、集計表を作成するシートを指定
Set ws1 = ThisWorkbook.Sheets.Add
ws1.Name = "担当者別合計金額"
Set ws2 = ThisWorkbook.Sheets.Add
ws2.Name = "機種別合計数量"
Set ws3 = ThisWorkbook.Sheets.Add
ws3.Name = "機種別合計金額"
' 担当者別合計金額のピボットテーブル作成
Set pivotCache = ThisWorkbook.PivotCaches.Create(xlDatabase, dataRange)
Set pivotTable = pivotCache.CreatePivotTable(ws1.Range("A1"), "担当者別合計金額")
With pivotTable
.PivotFields("日付").Orientation = xlRowField
.PivotFields("日付").NumberFormat = "yyyy年mm月"
.PivotFields("日付").ShowAllItems = True ' 全ての月を表示
' 担当者を列ラベルに設定
.PivotFields("担当者").Orientation = xlColumnField
' データフィールドとして合計金額を設定
.AddDataField .PivotFields("合計"), "合計金額", xlSum
End With
' 担当者別合計金額のグラフ作成
Set chartObj = ws1.ChartObjects.Add(Left:=300, Width:=500, Top:=50, Height:=300)
With chartObj.Chart
.SetSourceData Source:=pivotTable.TableRange2
.ChartType = xlLineStacked
.HasTitle = True
.ChartTitle.Text = "担当者別合計金額の積み上げ折れ線グラフ"
End With
' 機種別合計数量のピボットテーブル作成
Set pivotCache = ThisWorkbook.PivotCaches.Create(xlDatabase, dataRange)
Set pivotTable = pivotCache.CreatePivotTable(ws2.Range("A1"), "機種別合計数量")
With pivotTable
.PivotFields("日付").Orientation = xlRowField
.PivotFields("日付").NumberFormat = "yyyy年mm月"
.PivotFields("日付").ShowAllItems = True ' 全ての月を表示
' 機種を列ラベルに設定
.PivotFields("機種").Orientation = xlColumnField
' データフィールドとして合計数量を設定
.AddDataField .PivotFields("数量"), "合計数量", xlSum
End With
' 機種別合計数量のグラフ作成
Set chartObj = ws2.ChartObjects.Add(Left:=300, Width:=500, Top:=50, Height:=300)
With chartObj.Chart
.SetSourceData Source:=pivotTable.TableRange2
.ChartType = xlLineStacked
.HasTitle = True
.ChartTitle.Text = "機種別合計数量の積み上げ折れ線グラフ"
End With
' 機種別合計金額のピボットテーブル作成
Set pivotCache = ThisWorkbook.PivotCaches.Create(xlDatabase, dataRange)
Set pivotTable = pivotCache.CreatePivotTable(ws3.Range("A1"), "機種別合計金額")
With pivotTable
.PivotFields("日付").Orientation = xlRowField
.PivotFields("日付").NumberFormat = "yyyy年mm月"
.PivotFields("日付").ShowAllItems = True ' 全ての月を表示
' 機種を列ラベルに設定
.PivotFields("機種").Orientation = xlColumnField
' データフィールドとして合計金額を設定
.AddDataField .PivotFields("合計"), "合計金額", xlSum
End With
' 機種別合計金額のグラフ作成
Set chartObj = ws3.ChartObjects.Add(Left:=300, Width:=500, Top:=50, Height:=300)
With chartObj.Chart
.SetSourceData Source:=pivotTable.TableRange2
.ChartType = xlLineStacked
.HasTitle = True
.ChartTitle.Text = "機種別合計金額の積み上げ折れ線グラフ"
End With
今回の内容が「良かったなー」と思った方、「シェアしたいなー」と思ってくれた方の為の
このページのQRコードです。
コメント