エクセルで管理してる売上台帳を月別に集計する(VBA)

ExcelVBA
2023.10.16

・きっかけ

私が所属する会社の取引先担当者様から、「自社の決算月となり上司から取引先別の年月単位での売上金額一覧表を提出してしてほしい」との指示があったのだが、「管理しているエクセルの表はデータが請求日ごとに管理しているため、エクセルのフィルター機能を取引先ごとに使いながら1件づつ行えばできるが、集計ミスや作業時間等を考えると他に正確でいい方法はないか」との問い合わせでした。その時の解決方法としてエクセルVBAを利用したコードを公開いたします。

・今回のVBAではこんなことができます

・明細(1行ごと)で管理している数字の情報を集計し、集計後の数字を表に作成することができます。

・こんな方におすすめです

・明細(1行)を区分ごとに別途計算し、別シートなどに結果を転記している方。

・毎回ピボットテーブルを作成して集計している方。

・サンプルエクセルシート(結果)

「取引先別集計」ボタンをクリックするとピボットテーブル(指定単位で集計結果)を作成するVBAです。
※縦は年月別、横は取引先別の集計となっております。

・入力例(取引先毎集計ボタンを配置しクリックイベントに入力)

Private Sub CommandButton1_Click()
'ピボットテーブルの作成
    Dim pvCacheData As PivotCache, pvTableData As PivotTable

    'ピボットテーブルが1つの場合は更新をする。
    If ActiveSheet.PivotTables.Count = 1 Then
        ActiveSheet.PivotTables(1).PivotCache.Refresh
        Exit Sub
    End If
    
    'ピボットの範囲を設定
    Set pvCacheData = ActiveWorkbook.PivotCaches.Create _
            (SourceType:=xlDatabase, SourceData:=Range("A2:D" & Range("A2").CurrentRegion.Rows.Count & ""))
    'ピボットテーブルを作成
    Set pvTableData = pvCacheData.CreatePivotTable _
            (TableDestination:=Range("G2"), TableName:="売上集計")
    
    With pvTableData
        .PivotFields("請求日").Orientation = xlRowField
        .PivotFields("取引先").Orientation = xlColumnField
        .AddDataField Field:=pvTableData.PivotFields("金額"), _
                      Caption:="合計金額", Function:=xlSum
        .PivotFields("合計金額").NumberFormat = "#,##0"
        
        '年月単位で集計
        Range("G4").Group periods:=Array(False, False, False, False, True, False, True)
        
    End With
    
End Sub

・まとめ

ピボットテーブルの存在を知るまではわざわざフィルターを使ったりして集計作業を行っておりましたが、この存在を知ってからは、そのような面倒な作業をしなくてもよいことや分析に使う情報が簡単に作成できるようになったので作業時間の短縮や新しい資料を作るためのきっかけになればと思います。
最後にですが、この記事は私の実体験をもとに書いております。VBAを使いエクセルの自動化をすることで同じことの繰り返し作業やケアレスミスを減らすことができるのではないでしょうか。また、視点を変えて違うことへの利用を検討したり、この記事を通して新しいアイディアが生まれたり、VBAに興味を持っていただけたら幸いです。
ここまでご覧いただき、誠にありがとうございます。

・ご注意

・掲載されている内容には細心の注意をしてるつもりですが、
間違いやご指摘等がありましたら、「お問い合わせ」からご連絡をいただけますと幸いです。
・掲載されている入力VBAコード等は動作を保証するものではなく、あくまでサンプルとして掲載しております。
・掲載されている入力VBAコード等は自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
・対応バージョン
　Excel2021、2019、2016

コメント