Excel实现KANDS矩阵向量公式计算报#VALUE错误求解

问题描述 #

• 已完成全部基础数据准备、明确公式计算逻辑，在Excel中编写对应计算公式时持续报错，无法得到正确结果。
• 待实现的目标线性代数公式为最小二乘系数求解公式：

KANDS = (KSCOEFSᵀ · KSCOEFS)⁻¹ · KSCOEFSᵀ · OBS

• 已验证公式第一部分KSCOEFSᵀ · KSCOEFS可通过MMULT搭配转置操作正常计算，维度匹配无报错；但后续拼接计算时，因OBS是与KSCOEFS行数一致的单列数据，直接拼接运算返回#VALUE!错误。
• 输出要求：单组计算结果为1列宽、8行高的向量，该计算逻辑共需重复执行31次，单组逻辑调试完成后可沿波长维度批量复用，逐波长求解对应KANDS值。

现有错误写法 #

当前编写的公式存在语法和逻辑错误，无法返回正确解向量：

={POWER(MMULT(KSCOEFS),TRANSPOSE(KSCOEFS)),-1)}*MMULT(KSCOEFS,TRANSPOSE(OBS))

错误点说明 #

1. 函数参数语法错误：MMULT要求传入2个数组参数，上述写法中MMULT(KSCOEFS)仅传入1个参数，不符合函数语法要求。
2. 矩阵求逆方法错误：POWER函数仅支持逐元素幂运算，无法实现线性代数中的矩阵求逆操作，矩阵求逆需使用Excel专用的MINVERSE函数。
3. 矩阵乘法逻辑错误：Excel中*运算符仅执行逐元素相乘，无法实现矩阵乘法，所有矩阵乘操作必须嵌套使用MMULT函数。
4. 维度匹配错误：OBS本身是与KSCOEFS行数一致的列向量，计算KSCOEFSᵀ · OBS时不需要转置OBS，转置后会变为行向量，导致维度不匹配。

正确公式 #

• 若使用Excel 365/2021及以上支持动态数组的版本，选中8行1列输出区域的左上角单元格，直接输入以下公式按回车即可，结果会自动溢出填充：

=MMULT(MMULT(MINVERSE(MMULT(TRANSPOSE(KSCOEFS),KSCOEFS)),TRANSPOSE(KSCOEFS)),OBS)

• 若使用2019及更早的旧版Excel，需要先选中完整的8行1列输出区域，输入上述公式后按下Ctrl+Shift+Enter三键确认数组公式即可正常计算。

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