Excel VBAを活用した商品カテゴリー別売上集計と業務効率化の方法

この記事では、Excel VBAを利用して「商品カテゴリー別売上集計」の業務効率化について詳しく解説します。具体的なコード例、その詳細な解説、さらに実践的な応用例を通じて、VBAの力で日常の業務を効率化する方法を学びます。

目次

Excel VBAの基本

Excel VBA(Visual Basic for Applications)は、Microsoft Excelに組み込まれたプログラミング言語です。これを用いると、単純作業の自動化だけでなく、高度なデータ分析やレポート作成も可能になります。

そもそも、どこにVBAコードを書いて、どう実行すれば良いのか分からない場合は、以下の記事をご参照ください。

VBAによる商品カテゴリー別売上集計の基本

業務で扱うデータの中には、多種多様な商品やサービスが含まれることが多いです。その中で「商品カテゴリー別」に売上を集計することで、どのカテゴリーが好調なのか、どこに注力すべきかという判断材料を得ることができます。VBAを活用することで、この集計作業を自動化・効率化することが可能です。

基本のコード


Sub 商品カテゴリー別売上集計()

    Dim LastRow As Long
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1") 'ワークシート指定

    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row '最後の行を取得

    'A列にカテゴリー、B列に売上と仮定
    ws.Range("D1").Value = "カテゴリー"
    ws.Range("E1").Value = "合計売上"

    ws.Range("A1:B" & LastRow).Sort Key1:=ws.Range("A1"), Order1:=xlAscending, Header:=xlYes 'カテゴリーでソート

    ws.Range("D2").FormulaR1C1 = "=R2C1"
    ws.Range("E2").FormulaR1C1 = "=SUMIF(R2C1:R" & LastRow & "C1, R2C1, R2C2:R" & LastRow & "C2)"

    ws.Range("D2:E2").AutoFill Destination:=ws.Range("D2:E" & LastRow), Type:=xlFillDefault

End Sub

このコードは、シート1のA列に商品カテゴリー、B列に売上が入力されていると仮定しています。D列とE列にカテゴリー別の売上合計を出力します。

コードの詳細解説

1. **ワークシートの指定**: `ThisWorkbook.Worksheets(“Sheet1”)` で、シート1を操作対象としています。
2. **最後の行の取得**: VBAでは最後の行を取得することで、データの範囲を特定します。
3. **ソート処理**: A列の商品カテゴリーで昇順にソートしています。
4. **集計処理**: `SUMIF`関数を使用して、同じカテゴリーの売上を合計しています。

応用例1: カテゴリー別売上のグラフ作成

VBAを使って、集計結果から自動的にグラフを作成することができます。


Sub カテゴリー別売上グラフ作成()

    Dim LastRow As Long
    Dim ws As Worksheet
    Dim ChartRange As Range

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    LastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row

    Set ChartRange = ws.Range("D1:E" & LastRow)

    ws.Shapes.AddChart2(251, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=ChartRange

End Sub

応用例2: 売上トップ3のカテゴリーをハイライトする

集計結果から、売上の上位3つのカテゴリーを色付けすることで、目立たせることができます。


Sub 売上トップ3ハイライト()

    Dim ws As Worksheet
    Dim LastRow As Long
    Dim i As Integer

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    LastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row

    'E列のデータを降順にソート
    ws.Range("D1:E" & LastRow).Sort Key1:=ws.Range("E1"), Order1:=xlDescending, Header:=xlYes

    'トップ3のセルに色を付ける
    For i = 2 To 4
        ws.Cells(i, 5).Interior.Color = RGB(255, 217, 102)
    Next i

End Sub

応用例3: 売上が特定の値を超えたカテゴリーの一覧作成

例えば、売上が10万円を超えたカテゴリーだけを別のシートに一覧として出力することも可能です。


Sub 売上特定値超え一覧作成()

    Dim ws As Worksheet, wsNew As Worksheet
    Dim LastRow As Long, NewRow As Integer

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set wsNew = ThisWorkbook.Worksheets.Add

    LastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
    NewRow = 1

    '新しいシートにヘッダーを設定
    wsNew.Cells(1, 1).Value = "カテ

ゴリー"
    wsNew.Cells(1, 2).Value = "売上"

    For i = 2 To LastRow
        If ws.Cells(i, 5).Value > 100000 Then
            NewRow = NewRow + 1
            wsNew.Cells(NewRow, 1).Value = ws.Cells(i, 4).Value
            wsNew.Cells(NewRow, 2).Value = ws.Cells(i, 5).Value
        End If
    Next i

End Sub

これらの応用例を駆使することで、VBAを使用して業務の効率化を図ることができます。

まとめ

Excel VBAを利用することで、「商品カテゴリー別売上集計」などの業務プロセスを大幅に効率化することができます。基本的なコードから、さらに発展的な応用例までを駆使して、日常の業務をよりスムーズに進める手助けとしましょう。

VBAも良いけどパワークエリも良い

VBAの解説をしてきましたが、VBAは正直煩雑でメンテナンス性が悪いです。最近はモダンExcelと呼ばれるパワークエリやパワーピボットへのシフトが進んできています。本サイトでもパワークエリの特集をしており、サンプルデータを含む全11回の学習コンテンツでパワークエリを習得することができます。

クリックするとパワークエリの全11講座が表示されます。

パワーピボットの記事はありません。興味がある場合は、書籍で学んでみてください

コメント

コメントする

目次