【ExcelVBA】t検定ツールをExcelマクロで作った

2025/02/19

会社のPCのOffice365で突如分析ツールが消えてしまい、日ごろ使っている平均値の検定（t検定）が使えなくなったので急遽マクロで作成しました。

以下が結果です。A列、B列にデータを入れるとD〜F列に検定結果が展開されます。I〜K列に分析ツールで検定した結果も添えていますが、同じ値を出力していることが解ると思います。（この検定は自宅PCで行ったもの）

以下がプログラムになります。

Sub TTest_UnequalVarianceWithPTValues()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    ' データ範囲を定義
    Dim data1 As Range, data2 As Range
    Set data1 = ws.Range("A1", ws.Cells(ws.Rows.Count, 1).End(xlUp))
    Set data2 = ws.Range("B1", ws.Cells(ws.Rows.Count, 2).End(xlUp))
    
    ' 標本サイズ
    Dim n1 As Long, n2 As Long
    n1 = data1.Rows.Count
    n2 = data2.Rows.Count

    ' 平均と不偏分散を計算
    Dim mean1 As Double, mean2 As Double
    Dim var1 As Double, var2 As Double
    mean1 = Application.WorksheetFunction.Average(data1)
    mean2 = Application.WorksheetFunction.Average(data2)
    var1 = Application.WorksheetFunction.Var(data1)
    var2 = Application.WorksheetFunction.Var(data2)

    ' t値を計算（Welchのt検定）
    Dim tValue As Double
    tValue = (mean1 - mean2) / Sqr((var1 / n1) + (var2 / n2))
    
    ' 自由度を計算 (Welch?Satterthwaite equation)
    Dim df As Long
    df = data1.Count + data2.Count - 2

    ' p値を計算
    Dim pValueOneSide As Double, pValueTwoSide As Double
    pValueOneSide = Application.WorksheetFunction.T_Dist(Abs(tValue), df, True) ' 片側
    pValueTwoSide = Application.WorksheetFunction.T_Dist_2T(Abs(tValue), df)   ' 両側
    
    ' t分布の臨界値（片側・両側）
    Dim tCriticalOneSide As Double, tCriticalTwoSide As Double
    tCriticalOneSide = Application.WorksheetFunction.T_Inv(0.05, df) ' 片側5%有意水準
    tCriticalTwoSide = Application.WorksheetFunction.T_Inv_2T(0.05, df) ' 両側5%有意水準
    
    ' 結果をシートに出力
    ws.Cells(1, 4).Value = "t-検定: 分散が等しくないと仮定した２標本による検定"
    ws.Cells(2, 4).Value = ""
    ws.Cells(3, 5).Value = "変数 1"
    ws.Cells(3, 6).Value = "変数 2"
    ws.Cells(4, 4).Value = "平均"
    ws.Cells(4, 5).Value = mean1
    ws.Cells(4, 6).Value = mean2
    ws.Cells(5, 4).Value = "分散"
    ws.Cells(5, 5).Value = var1
    ws.Cells(5, 6).Value = var2
    ws.Cells(6, 4).Value = "観測数"
    ws.Cells(6, 5).Value = data1.Count
    ws.Cells(6, 6).Value = data2.Count
    ws.Cells(7, 4).Value = "自由度"
    ws.Cells(7, 5).Value = df
    ws.Cells(8, 4).Value = "t"
    ws.Cells(8, 5).Value = Abs(tValue)
    ws.Cells(9, 4).Value = "P(T<=t) 片側"
    ws.Cells(9, 5).Value = Abs(pValueOneSide)
    ws.Cells(10, 4).Value = "t 境界値 片側"
    ws.Cells(10, 5).Value = Abs(tCriticalOneSide)
    ws.Cells(11, 4).Value = "P(T<=t) 両側"
    ws.Cells(11, 5).Value = Abs(tCriticalOneSide)
    ws.Cells(12, 4).Value = "t 境界値 両側"
    ws.Cells(12, 5).Value = Abs(tCriticalTwoSide)
End Sub

- Excel, VBA