Excel对任意非线性函数的拟合

相关准备

Excel,数据。

非线性拟合

Excel拟合多参数非线性函数主要是依靠最小二乘法来求解,本文以吸附模型拟合作为实例。
注意:Excel中的自带的幂函数拟合请慎用,因为它所拟合的结果并不一定正确,问题见链接

  • 首先将原始数据绘制成散点图,如图所示。
  • 将目标非线性函数的多个参数单独列出,如图最上面的黄颜色部分,并给定初始值(比如1.00000),注意有些函数中参数的限制要求(比如正负号)。
  • 通过函数公式,计算出因变量的预测值(输入公式时候注意参数的固定引用,如图中的$D$1)。
  • 因为需要通过最小二乘法拟合,因此还应该计算出预测值与原始数据的残差平方,如图所示。并求和每个点的数据残差平方。
  • 依次点击Excel的 文件–>选项–>加载项,将非活动应用程序中的规划求解加载项开启。返回点击工具栏数据–>规划求解打开窗口。
  • 设置目标选择残差平方和单元格,并设置目标到最小值,可变单元格选中参数值所在位置,如果有特殊参数限制,需要在下方添加约束条件。根据实际参数情况选择是否勾选非负数,求解方法为非线性,完成点击确定。通过计算得到满足最小目标的函数参数值。
  • 将预测值绘制到原数据图中。并将得到的结果与Origin、Matlab等专业软件对比,发现基本一致。
  • 从图中可以看出,选择Langmuir吸附模型拟合效果最佳。