Excel VBA生成矩阵B：元素为矩阵A对应行与列的平均值

核心步骤 #

• 先计算矩阵A的每行平均值，存入一维数组rowAvgs
• 再计算矩阵A的每列平均值，存入一维数组colAvgs
• 遍历每个位置(i,j)，按公式 B[i][j] = (行i平均值 + 列j平均值) / 2 生成矩阵B

完整代码实现 #

假设你已将读取的矩阵A存储在二维数组arrA中，以下是可直接复用的代码：

Sub GenerateMatrixB()
    Dim arrA As Variant
    Dim arrB As Variant
    Dim rowAvgs() As Double
    Dim colAvgs() As Double
    Dim totalRows As Long, totalCols As Long
    Dim i As Long, j As Long
    Dim tempSum As Double
    
    ' 替换为你已读取的矩阵A，示例用题目中的3×3矩阵
    arrA = Array(Array(2, 4, 6), Array(8, 4, 2), Array(6, 8, 2))
    totalRows = UBound(arrA) + 1 ' 适配0起始索引的数组
    totalCols = UBound(arrA(0)) + 1
    
    ' 计算每行平均值
    ReDim rowAvgs(1 To totalRows)
    For i = 1 To totalRows
        tempSum = 0
        For j = 1 To totalCols
            tempSum = tempSum + arrA(i - 1)(j - 1)
        Next j
        rowAvgs(i) = tempSum / totalCols
    Next i
    
    ' 计算每列平均值
    ReDim colAvgs(1 To totalCols)
    For j = 1 To totalCols
        tempSum = 0
        For i = 1 To totalRows
            tempSum = tempSum + arrA(i - 1)(j - 1)
        Next i
        colAvgs(j) = tempSum / totalRows
    Next j
    
    ' 生成矩阵B
    ReDim arrB(1 To totalRows, 1 To totalCols)
    For i = 1 To totalRows
        For j = 1 To totalCols
            arrB(i, j) = (rowAvgs(i) + colAvgs(j)) / 2
        Next j
    Next i
    
    ' 输出到立即窗口（可改为写入Excel单元格）
    Debug.Print "矩阵B结果："
    For i = 1 To totalRows
        For j = 1 To totalCols
            Debug.Print Round(arrB(i, j), 2); " ";
        Next j
        Debug.Print
    Next i
End Sub

适配说明 #

• 若你的矩阵A是从Excel单元格读取的（如arrA = Range("A1:C3").Value），数组默认从1开始索引，需修改：
  • totalRows = UBound(arrA, 1)
  • totalCols = UBound(arrA, 2)
  • 循环中直接用arrA(i, j)替代arrA(i-1)(j-1)
• 如需将矩阵B写入Excel，可替换输出部分为：Range("E1").Resize(totalRows, totalCols) = arrB

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