寻求不调用WorksheetFunction的VBA协方差矩阵自定义实现方案

当然可以！我们可以直接在VBA里实现协方差的数学逻辑，完全替代Application.WorksheetFunction.Covar。首先明确协方差的计算规则：
对于两组数据X和Y，总体协方差（对应Excel旧版COVAR函数的行为）的计算公式是：

Cov(X,Y) = Σ[(Xi - X̄)(Yi - Ȳ)] / n
其中X̄是X的平均值，Ȳ是Y的平均值，n是数据点的数量。

如果需要的是样本协方差（对应Excel的COVARIANCE.S），则分母换成n-1，你可以根据需求调整。

下面是修改后的完整VBA代码，包含手动计算协方差的逻辑：

Function VarCovar(Rng As Range) As Variant
    Dim i As Integer, j As Integer
    Dim numcols As Integer, numrows As Integer
    Dim matrix() As Double
    Dim colX As Range, colY As Range
    Dim avgX As Double, avgY As Double
    Dim sumProduct As Double
    Dim k As Integer
    
    numcols = Rng.Columns.Count
    numrows = Rng.Rows.Count
    ' 如果数据行数小于2，无法计算协方差
    If numrows < 2 Then
        VarCovar = "数据行数不足"
        Exit Function
    End If
    
    ReDim matrix(numcols - 1, numcols - 1)
    
    For i = 1 To numcols
        Set colX = Rng.Columns(i)
        ' 计算X列的平均值
        avgX = CalculateAverage(colX)
        
        For j = 1 To numcols
            Set colY = Rng.Columns(j)
            ' 计算Y列的平均值
            avgY = CalculateAverage(colY)
            
            sumProduct = 0
            ' 遍历每一行，计算(Xi-X̄)(Yi-Ȳ)的总和
            For k = 1 To numrows
                sumProduct = sumProduct + (colX.Cells(k, 1).Value - avgX) * (colY.Cells(k, 1).Value - avgY)
            Next k
            
            ' 这里用总体协方差（对应原COVAR函数），如果要样本协方差就换成(numrows - 1)
            matrix(i - 1, j - 1) = sumProduct / numrows
        Next j
    Next i
    
    VarCovar = matrix
End Function

' 辅助函数：计算一列数据的平均值
Function CalculateAverage(col As Range) As Double
    Dim sumVal As Double
    Dim count As Integer
    Dim cell As Range
    
    sumVal = 0
    count = 0
    
    For Each cell In col
        ' 跳过空单元格或者非数值型数据
        If IsNumeric(cell.Value) Then
            sumVal = sumVal + cell.Value
            count = count + 1
        End If
    Next cell
    
    ' 如果有效数据为0，返回0或者根据需求抛出错误
    If count = 0 Then
        CalculateAverage = 0
    Else
        CalculateAverage = sumVal / count
    End If
End Function

代码说明： #

• 新增了CalculateAverage辅助函数，专门计算一列数据的平均值，还处理了空单元格和非数值的情况，比直接用内置函数更灵活。
• 主函数里通过三重循环实现协方差计算：外层两层遍历所有列组合，最内层遍历每行数据计算交叉乘积的总和。
• 注释里标注了切换总体/样本协方差的方法，你可以根据自己的需求修改分母。
• 增加了数据行数的判断，如果行数小于2，直接返回提示，避免计算错误。

这样修改后，你的协方差矩阵函数就完全不需要依赖Excel的内置Covar函数了，逻辑完全可控。

内容的提问来源于stack exchange，提问作者T123