Excel中矩阵行最简形（RREF）的VBA代码失效问题排查

核心问题：错误的行交换逻辑 #

你的代码在处理主元为0需要换行时，用行相加代替了行交换，这是导致特定矩阵计算错误的关键：

If i <> r Then
    For c = lead To colCount
        matrix(r, c) = matrix(r, c) + matrix(i, c)
    Next c
End If

这种操作不是交换第r行和第i行，而是把第i行的数据累加到第r行上，会直接污染当前行的原始数据。当矩阵行数较少时，可能碰巧没触发严重偏差，但行数超过3后，后续的主元计算会基于被篡改的行数据，最终结果完全偏离预期。

正确的行交换逻辑应该是用临时变量存储一行数据，再互相赋值：

If i <> r Then
    Dim temp As Variant
    For c = lead To colCount
        temp = matrix(r, c)
        matrix(r, c) = matrix(i, c)
        matrix(i, c) = temp
    Next c
End If

次要问题：浮点数精度判断 #

直接用matrix(i, lead) = 0判断主元是否为0，在处理Excel浮点数时容易出现精度误差——比如计算后得到极小的非零数，会被误判为0，导致错误的列偏移。可以改成判断绝对值是否小于极小值：

While Abs(matrix(i, lead)) < 1E-10

修复后的完整代码 #

整合以上修改后的正确RREF函数：

Function ReduceToRREF(matrixRange As Range) As Variant
    Dim matrix As Variant
    Dim rowCount As Long
    Dim colCount As Long
    Dim lead As Long
    Dim r As Long
    Dim c As Long
    Dim i As Long
    Dim multiplier As Double
    Dim temp As Variant

    matrix = matrixRange.Value
    rowCount = UBound(matrix, 1)
    colCount = UBound(matrix, 2)
    lead = 1

    For r = 1 To rowCount
        If colCount < lead Then Exit For
        i = r
        ' 处理主元为0的情况，增加浮点数精度判断
        While Abs(matrix(i, lead)) < 1E-10
            i = i + 1
            If rowCount < i Then
                i = r
                lead = lead + 1
                If colCount < lead Then Exit For
            End If
        Wend
        ' 正确的行交换逻辑
        If i <> r Then
            For c = lead To colCount
                temp = matrix(r, c)
                matrix(r, c) = matrix(i, c)
                matrix(i, c) = temp
            Next c
        End If
        multiplier = matrix(r, lead)
        ' 避免除以0的防护判断
        If Abs(multiplier) > 1E-10 Then
            For c = lead To colCount
                matrix(r, c) = matrix(r, c) / multiplier
            Next c
        End If
        ' 消去其他行的主元
        For i = 1 To rowCount
            If i <> r Then
                multiplier = matrix(i, lead)
                For c = lead To colCount
                    matrix(i, c) = matrix(i, c) - multiplier * matrix(r, c)
                Next c
            End If
        Next i
        lead = lead + 1
    Next r

    ReduceToRREF = matrix
End Function

内容的提问来源于stack exchange，提问作者O.S.