如何在Excel中实现Matlab polyvaln功能？手动计算结果不符解惑

一、结果差异的核心原因 #

polyvaln不会主动舍弃系数，你遇到的巨大误差基本是以下两种情况：

1. 系数与多项式项的对应关系理解错误
   二元二次多项式的标准展开形式为：

   y = c₀ + c₁x₁ + c₂x₂ + c₃x₁² + c₄x₁x₂ + c₅x₂²
   Matlab拟合（比如用polyfitn）得到的系数向量是按项的阶次升序、同阶次按变量索引排序排列的，即顺序为[c₀, c₁, c₂, c₃, c₄, c₅]。如果手动计算时搞混了系数顺序（比如把x₁²的系数当成x₁的系数代入），会直接导致结果数量级偏差。

2. 拟合前的数据缩放未做逆变换
   很多时候Matlab拟合工具会自动对输入数据做归一化/标准化（比如将x₁、x₂缩放到[-1,1]或均值为0、标准差为1的区间），拟合得到的系数是针对缩放后的数据的。如果手动计算时直接用原始数据代入多项式，而没有先对数据做相同的缩放变换，结果会出现极大偏差。

二、Excel中实现polyvaln功能的方法 #

根据拟合得到的多项式形式，分两种场景实现：

场景1：已知明确的二元二次多项式项 #

假设你从Matlab得到的系数对应关系为[c₀, c₁, c₂, c₃, c₄, c₅]，对应多项式：
y = c₀ + c₁*x₁ + c₂*x₂ + c₃*x₁² + c₄*x₁*x₂ + c₅*x₂²
在Excel中直接写公式即可：

• 假设x₁存于A2单元格，x₂存于B2单元格，系数c₀到c₅分别存于$F$2到$F$7单元格
• 在目标单元格（比如C2）输入：

  =$F$2 + $F$3*A2 + $F$4*B2 + $F$5*A2^2 + $F$6*A2*B2 + $F$7*B2^2
  

  下拉填充即可批量计算所有数据的预测值。

场景2：高阶/多元多项式（通用方法） #

如果是更复杂的多项式，用SUMPRODUCT函数批量计算更高效：

1. 在Excel中单独列计算每一项的取值：
   • 列C：常数项（所有行填1）
   • 列D：x₁的取值（=A2）
   • 列E：x₂的取值（=B2）
   • 列F：x₁²的取值（=A2^2）
   • 列G：x₁x₂的取值（=A2B2）
   • 列H：x₂²的取值（=B2^2）
2. 把Matlab得到的系数按项的顺序依次存于$J$2到$J$7单元格
3. 在目标单元格输入：

   =SUMPRODUCT(C2:H2, $J$2:$J$7)
   

   下拉填充即可得到所有预测值。

注意：如果拟合时做了数据缩放 #

需先对Excel中的原始数据做逆缩放，再代入多项式：

1. 从Matlab中获取拟合时的缩放参数：x₁的均值mu1、标准差sigma1；x₂的均值mu2、标准差sigma2；y的均值mu_y、标准差sigma_y（如果y也做了缩放）
2. 在Excel中计算缩放后的x₁'和x₂'：

   x1' = (A2 - mu1)/sigma1
   x2' = (B2 - mu2)/sigma2
   

3. 用x₁'、x₂'代入多项式得到y'，再逆变换得到最终y：

   y = y'*sigma_y + mu_y
   

验证步骤 #

1. 取Matlab中用于测试的x₁、x₂原始值，代入Excel公式，看结果是否与polyvaln输出一致
2. 检查手动计算时的系数顺序、项的展开是否正确，是否遗漏了数据缩放步骤

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