Excel VBAで基本統計量を自動計算する方法｜平均・分散・標準偏差を集計

はじめに

大量のデータを品種や工程ごとに分けて、平均値や標準偏差を毎回手作業で計算するのは時間がかかります。集計条件が増えるほど、範囲の選択ミスや計算漏れも起こりやすくなります。

Excel VBAを使えば、元データを読み込み、同じ品種・工程の値をまとめて、件数・最小値・最大値・平均値・分散・標準偏差を一度に出力できます。この記事では、グループ別の基本統計量を自動集計するための考え方と、実行できるVBAコードを解説します。

今回作る集計

ここでは「データ」シートに、品種・工程・所要時間が次の形で並んでいるものとします。

品種	工程	所要時間（分）
A	加工	32
A	加工	35
A	検査	18
B	加工	41
B	加工	39

VBAを実行すると、「集計」シートへ品種と工程の組み合わせごとに結果を出力します。

品種	工程	件数	最小値	最大値	平均値	分散	標準偏差
A	加工	2	32	35	33.5	4.5	2.12
A	検査	1	18	18	18	–	–
B	加工	2	39	41	40	2	1.41

この記事では統計量の自動集計に集中します。開始時刻と終了時刻から所要時間を求める処理は「Excel VBAで所要時間を自動計算！時分別列データ対応」、値ごとにワークシートを分ける処理は「Excel VBA データ自動分類：指定列基準で分類する方法」で扱っています。

事前準備

Excelブックに「データ」と「集計」という2つのワークシートを用意します。「データ」シートでは1行目を見出しとし、A列に品種、B列に工程、C列に集計対象の数値を入力してください。

コードはVisual Basic Editor（VBE）の標準モジュールへ貼り付けます。VBEを開き、「挿入」→「標準モジュール」を選択してから、次のコードを入力します。

「集計」シートのA～H列は実行時に内容を消去してから結果を書き込みます。A～H列に残したいデータがある場合は、別のシートへ移すか、コードの出力範囲を変更してから実行してください。

VBAコード

Option Explicit

Sub SummarizeStatistics()
    Dim wsData As Worksheet
    Dim wsSummary As Worksheet
    Dim lastRow As Long
    Dim rowIndex As Long
    Dim resultRow As Long
    Dim groups As Object
    Dim groupKey As String
    Dim key As Variant
    Dim values As Collection
    Dim dataArray As Variant
    Dim parts As Variant
    Dim headers As Variant
    Dim headerIndex As Long
    Dim categoryValue As Variant
    Dim processValue As Variant
    Dim targetValue As Variant

    Set wsData = ThisWorkbook.Worksheets("データ")
    Set wsSummary = ThisWorkbook.Worksheets("集計")
    Set groups = CreateObject("Scripting.Dictionary")

    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row

    For rowIndex = 2 To lastRow
        categoryValue = wsData.Cells(rowIndex, "A").Value2
        processValue = wsData.Cells(rowIndex, "B").Value2
        targetValue = wsData.Cells(rowIndex, "C").Value2

        If Not IsError(categoryValue) _
           And Not IsError(processValue) _
           And Not IsError(targetValue) Then

            If CStr(categoryValue) <> "" _
               And CStr(processValue) <> "" _
               And IsNumeric(targetValue) Then

                groupKey = CStr(categoryValue) & vbTab & CStr(processValue)

                If Not groups.Exists(groupKey) Then
                    Set values = New Collection
                    groups.Add groupKey, values
                Else
                    Set values = groups(groupKey)
                End If

                values.Add CDbl(targetValue)
            End If
        End If
    Next rowIndex

    wsSummary.Range("A:H").ClearContents

    headers = Array("品種", "工程", "件数", "最小値", "最大値", "平均値", "分散", "標準偏差")
    For headerIndex = LBound(headers) To UBound(headers)
        wsSummary.Cells(1, headerIndex + 1).Value2 = headers(headerIndex)
    Next headerIndex

    resultRow = 2

    For Each key In groups.Keys
        Set values = groups(key)
        dataArray = CollectionToArray(values)
        parts = Split(CStr(key), vbTab)

        wsSummary.Cells(resultRow, 1).Value2 = parts(0)
        wsSummary.Cells(resultRow, 2).Value2 = parts(1)
        wsSummary.Cells(resultRow, 3).Value2 = values.Count
        wsSummary.Cells(resultRow, 4).Value2 = WorksheetFunction.Min(dataArray)
        wsSummary.Cells(resultRow, 5).Value2 = WorksheetFunction.Max(dataArray)
        wsSummary.Cells(resultRow, 6).Value2 = WorksheetFunction.Average(dataArray)

        If values.Count >= 2 Then
            wsSummary.Cells(resultRow, 7).Value2 = WorksheetFunction.Var_S(dataArray)
            wsSummary.Cells(resultRow, 8).Value2 = WorksheetFunction.StDev_S(dataArray)
        Else
            wsSummary.Cells(resultRow, 7).Value2 = "-"
            wsSummary.Cells(resultRow, 8).Value2 = "-"
        End If

        resultRow = resultRow + 1
    Next key
End Sub

Private Function CollectionToArray(ByVal items As Collection) As Variant
    Dim result() As Double
    Dim index As Long

    ReDim result(1 To items.Count)

    For index = 1 To items.Count
        result(index) = CDbl(items(index))
    Next index

    CollectionToArray = result
End Function

コードのポイント

品種と工程を1つのキーとしてまとめる

Scripting.Dictionaryは、キーと値の組み合わせを保存できるオブジェクトです。このコードではCreateObjectで生成しているため、VBEで参照設定を追加せずに利用できます。「品種」と「工程」をタブ文字で連結し、「A＋加工」「A＋検査」のような組み合わせをグループとして管理しています。

同じ組み合わせの値はCollectionへ追加します。これにより、元データの並び順に関係なく、同じグループの数値をまとめて統計量へ渡せます。

WorksheetFunctionで基本統計量を計算する

集めた値は配列へ変換し、WorksheetFunction.Min、Max、Average、Var_S、StDev_Sへ渡します。Excelのワークシート関数に近い考え方で、VBAから最小値・最大値・平均値・分散・標準偏差を求められます。

この例のVar_SとStDev_Sは、データを母集団から取り出した標本として扱う場合の分散と標準偏差です。手元のデータが母集団全体を表す場合は、用途に応じてVar_PとStDev_Pを使用します。

1件だけのグループでは分散と標準偏差を出さない

標本分散と標本標準偏差は、値が1件しかないグループでは計算できません。そのため、コードではvalues.Count >= 2を確認し、1件だけの場合は「-」を出力しています。

この確認を入れておくと、データ件数の少ない品種や工程が含まれていても、途中でエラーになりにくくなります。

実行手順

1. 「データ」シートのA列へ品種、B列へ工程、C列へ数値を入力します。
2. 「集計」シートを作成します。
3. 標準モジュールへコードを貼り付けます。
4. SummarizeStatisticsを実行します。
5. 「集計」シートにグループ別の統計量が出力されたことを確認します。

元データを追加した後は、同じマクロを再実行すれば集計結果を作り直せます。既存の集計内容は実行時にクリアされるため、前回結果が残ったままになることもありません。

よくある失敗

シート名がコードと一致していない

コードでは「データ」と「集計」というシート名を前提にしています。実際のブックで別の名前を使う場合は、Worksheets("データ")とWorksheets("集計")の文字列を実際のシート名へ変更してください。

集計列に文字列が含まれている

C列は数値だけを集計対象にしています。コードではIsNumericで確認しているため文字列は集計から除外されますが、「未測定」などの文字を数値と同じ列へ混在させると、件数の認識が意図とずれることがあります。集計前にデータの入力ルールをそろえておくことが重要です。

標本と母集団を区別していない

分散と標準偏差には、標本を対象にする計算と母集団全体を対象にする計算があります。サンプルデータから全体のばらつきを推定するならVar_S・StDev_S、対象となる全データそのものを集計するならVar_P・StDev_Pというように、データの意味に合わせて使い分けます。

まとめ

Excel VBAでは、Scripting.Dictionaryで同じ品種・工程のデータをまとめ、WorksheetFunctionを使ってグループ別の基本統計量を自動計算できます。手作業で範囲を選び直す必要がなくなるため、同じ形式のデータを繰り返し集計する処理に向いています。

まずは件数・最小値・最大値・平均値から確認し、分散や標準偏差についてはデータが標本か母集団全体かを判断して関数を選びます。コードをそのまま使う場合も、シート名と列構成を自分のブックに合わせてから実行してください。

関連記事

• Excel VBA データ自動分類：指定列基準で分類する方法
• Excel VBAで所要時間を自動計算！時分別列データ対応
• データ分析の必須スキル：Excelで基本統計量を算出する方法