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

この記事では、Excel VBAを利用して「商品カテゴリー別売上集計」の業務効率化について詳しく解説します。具体的なコード例、その詳細な解説、さらに実践的な応用例を通じて、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を利用することで、「商品カテゴリー別売上集計」などの業務プロセスを大幅に効率化することができます。基本的なコードから、さらに発展的な応用例までを駆使して、日常の業務をよりスムーズに進める手助けとしましょう。