VBA实现贝叶斯估计

Function CalculateBayesianPosteriorMean(successesRange As Range, totalTrialsPerTest As Integer, alphaPrior As Double, betaPrior As Double) As Double

    Dim totalSuccesses As Double

    Dim numRows As Long

    Dim posteriorAlpha As Double

    Dim posteriorBeta As Double

    

    ' 初始化总成功次数

    totalSuccesses = 0

    

    ' 获取范围中的行数

    numRows = successesRange.Rows.Count

    

    ' 遍历范围中的每个单元格

    Dim cell As Range

    For Each cell In successesRange

        ' 累加成功次数

        totalSuccesses = totalSuccesses + cell.Value

    Next cell

    

    ' 更新Beta分布的参数

    posteriorAlpha = alphaPrior + totalSuccesses

    posteriorBeta = betaPrior + (numRows * totalTrialsPerTest - totalSuccesses)

    

    ' 计算后验分布的均值（期望）

    CalculateBayesianPosteriorMean = posteriorAlpha / (posteriorAlpha + posteriorBeta)

End Function