EXCELのVBAを使用したデータの分析例

EXCEL

はじめに

VBA(Visual Basic for Applications)は、Excelを強化し、自動化を実現するための強力なツールです。本記事では、VBAを使用して売り上げデータを分析する方法について解説します。

具体的には、売り上げデータの例を作成し、それを基に5つの分析パターンを紹介します。これにより、VBAの基本的な使い方から応用までを学ぶことができます。

VBAを使用する詳細な方法は以下の記事を参考にしてください。

売り上げデータの例

まず、分析対象となる売り上げデータの例を作成します。実務では、もっと複雑で大量のデータが扱われますが、以下のような簡単なデータがあるとします。

分析の例

パターン1: 売り上げの合計を計算する

まず、売り上げの合計を計算するVBAコードを作成します。以下のコードをVBAエディタに入力して実行します。

Option Explicit
Sub CalculateTotalSales()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    Dim total As Double
    total = 0

    Dim i As Long
    For i = 2 To 10
        total = total + ws.Cells(i, 3).Value
    Next i

    ws.Cells(1, 5).Value = "Total Sales"
    ws.Cells(2, 5).Value = total
End Sub

このコードは、シート1「A1:C10」の売り上げデータを合計し、その結果をE1に表示したものです。

重要な行の説明

  • Set ws = ThisWorkbook.Sheets("Sheet1")を含んだ前後のコード:現在のワークブックのSheet1を対象にする。
  • For i = 2 To 10を含んだ前後のコード:C2セルからC10まで合計を計算する。
  • ws.Cells(1, 5).Value = “Total Sales”を含んだ前後のコード:E1に合計を記入する。

結果↓

パターン2: 商品ごとの売り上げを計算する

次に、商品ごとの売り上げを計算するVBAコードを作成します。以下のコードをVBAエディタに入力して実行します。

Sub CalculateSalesByProduct()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")

    Dim i As Long
    For i = 2 To 10
        Dim product As String
        product = ws.Cells(i, 2).Value

        Dim sales As Double
        sales = ws.Cells(i, 3).Value

        If dict.exists(product) Then
            dict(product) = dict(product) + sales
        Else
            dict.Add product, sales
        End If
    Next i

    Dim row As Long
    row = 1

    ws.Cells(row, 5).Value = "Product"
    ws.Cells(row, 6).Value = "Total Sales"

    For Each key In dict.Keys
        row = row + 1
        ws.Cells(row, 5).Value = key
        ws.Cells(row, 6).Value = dict(key)
    Next key
End Sub

このコードは、商品ごとの売り上げを計算し、その結果をE1に表示したものです。

重要な行の説明

  • Set dict = CreateObject(“Scripting.Dictionary”)を含んだ前後のコード:dict 変数にスクリプト辞書オブジェクトを作成して代入しています。スクリプト辞書は、キーと値のペアを格納するためのコレクションです。
  • If dict.exists(product) Thenを含んだ前後のコード:辞書dictに商品名が存在するかどうかをチェックして売上合計を出していく。

結果↓

パターン3: 日付ごとの売り上げを計算する

次に、日付ごとの売り上げを計算するVBAコードを作成します。以下のコードをVBAエディタに入力して実行します。

Sub CalculateSalesByDate()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")

    Dim i As Long
    For i = 2 To 10
        Dim dateStr As String
        dateStr = ws.Cells(i, 1).Value

        Dim sales As Double
        sales = ws.Cells(i, 3).Value

        If dict.exists(dateStr) Then
            dict(dateStr) = dict(dateStr) + sales
        Else
            dict.Add dateStr, sales
        End If
    Next i

    Dim row As Long
    row = 1

    ws.Cells(row, 5).Value = "Date"
    ws.Cells(row, 6).Value = "Total Sales"

    For Each key In dict.Keys
        row = row + 1
        ws.Cells(row, 5).Value = key
        ws.Cells(row, 6).Value = dict(key)
    Next key
End Sub

このコードは、日付ごとの売り上げを計算し、その結果をE1に表示したものです。

重要な行の説明

  • If dict.exists(dateStr) Thenを含んだ前後のコード:辞書dictに日付が存在するかどうかをチェックして売上合計を出していく。

結果↓

パターン4: 売り上げの平均を計算する

次に、売り上げの平均を計算するVBAコードを作成します。以下のコードをVBAエディタに入力して実行します。

Sub CalculateAverageSales()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    Dim total As Double
    total = 0

    Dim count As Long
    count = 0

    Dim i As Long
    For i = 2 To 10
        total = total + ws.Cells(i, 3).Value
        count = count + 1
    Next i

    Dim average As Double
    average = total / count

    ws.Cells(1, 5).Value = "Average Sales"
    ws.Cells(2, 5).Value = average
End Sub

重要な行の説明

  • average = total / countを含んだ前後のコード:平均売上を計算していく。

このコードは、売り上げの平均を計算し、その結果をE1に表示したものです。

結果↓

パターン5: 売り上げの最大値と最小値を計算する

最後に、売り上げの最大値と最小値を計算するVBAコードを作成します。以下のコードをVBAエディタに入力して実行します。

Sub CalculateMinMaxSales()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    Dim maxSales As Double
    maxSales = 0

    Dim minSales As Double
    minSales = 999999999

    Dim i As Long
    For i = 2 To 10
        Dim sales As Double
        sales = ws.Cells(i, 3).Value

        If sales > maxSales Then
            maxSales = sales
        End If

        If sales < minSales Then
            minSales = sales
        End If
    Next i

    ws.Cells(1, 5).Value = "Max Sales"
    ws.Cells(2, 5).Value = maxSales

    ws.Cells(3, 5).Value = "Min Sales"
    ws.Cells(4, 5).Value = minSales
End Sub

重要な行の説明

  • If sales > maxSales Thenを含んだ前後のコード:現在の売上が maxSales より大きい場合の条件をチェックします。
  • If sales < minSales Thenを含んだ前後のコード:現在の売上が minSales より小さい場合の条件をチェックします。

このコードは、売り上げの最大値と最小値を計算し、その結果をE1に表示したものです。

まとめ

本記事では、ExcelのVBAを使用して売り上げデータを分析する5つのパターンを紹介しました。
これらの例を通じて、VBAの基本的な構文や機能、そしてExcelとの連携方法について学ぶことができました。

特に、ワークシートの操作、ループ処理、条件分岐、辞書オブジェクトの使用など、実務で頻繁に使用される技術を実践的に解説しました。

VBAを使用することで、大量のデータを素早く正確に解析することができるようになります。これにより、手作業での計算ミスを減らし、データ分析の効率を大きく改善することができます。

ここで紹介した例は基本的なものですが、これらを組み合わせたり拡張したりすることで、より複雑な分析や自動化も実現できます。例えば、複数のシートに存在するデータの分析や、分析結果をグラフ化する処理なども可能です。

VBAは強力なツールですが、使いこなすには練習が必要です。