使用Excel拟合任意函数的方法 - 知乎

Excel趋势线拟合的局限性

众所周知，Excel是可以进行简单的函数拟合的，主要步骤如下：

第一步，作图

第二步，添加趋势线

但是，该方法的缺陷也是显而易见的，就是只能拟合预设模板的几个函数，如果碰上其他函数就直接歇菜了，比如：

(1)y=ax+by=\frac{a}{x+b} \tag1

Excel进行任意函数拟合的方法

其实，Excel里是有这个功能的，只不过不是通过趋势线而是通过规划求解的方式来实现的，具体操作看这里：

启动规划求解模块

在Excel中打开文件菜单中的选项，在弹出菜单的加载项选项卡中找到Excel加载项，点一下右侧的转到，然后勾选规划求解加载项，确定保存

使用规划求解来解决任意函数的拟合问题

启用加载项后，在Excel的数据选项卡中就可以看到规划求解按钮了

那么，准备开工了：

准备数据

按照如下的表中准备数据（后面有说明）：

①xy列是原始数据

②新建拟合参数对应的列，并赋予初值，这里我们赋予的初始值是a=1，b=1

③使用ab的初始值计算对应的y（即yi），这里需要说明一下，对于C2单元格我们Excel公式是这么写的：

$H$2/(A2+$H$3)

其中$H$2表示绝对引用，H2的数值，即在复制公式的时候该值不变化；A2则表示相对引用A2的数值，在复制公式的时候对应变化。在引用了某个单元格后可以按F4切换绝对引用和相对引用。

例如，当我们将C2的单元格复制到C3的时候，公式就变成了：

$H$2/(A3+$H$3)

注意到绝对引用的位置不发生变化（仍然引用了H2，而相对引用则发生了变化，引用了A3）

获得yi后，计算yi与y偏差的平方，就是第四列

④对yi与y偏差的平方求和（其实就是残差平方和，SSE），这就算是我们的目标函数了。我们拟合的目的就是使得该值达到最小

规划求解

点开规划求解，按下图操作

①设定目标函数，记得目标选择为最小值（图中没有画上）

②选择参数单元格，这里就是ab两个

③设定约束条件，可以分别约束每个变量的取值范围、是否是整数等，这里因为问题简单就没有约束

④选择求解方案，一般都是非线性求解

然后点击求解，由于问题很简单那，5次就迭代到了结果，注意a和b的数值已经变化了，拟合完成，结果为：

a=2.78639, b=4.168637。实际上我又用scipy.optimize拟合了一下，结果几乎是完全一样的

做个图，显示拟合结果（橙色）与原始数据（黄色）之间匹配良好

拟合误差评估：

作为一个拟合结果，我们显然需要知道拟合的各种误差，常用的评价指标就是 R2R^2 ，不同于趋势线上可以直接显示R2R^2，这里我们要手动计算了：

我们之前已经用过残差平方和SSE的概念了（即每一项与其拟合值之间的偏差）：

(2)SSE=∑(y−yi)2SSE=\sum_{}^{}{(y-y_i)^2} \tag2

那么再介绍一个概念，叫做总离差平方和（SST），即每一项与其平均值的偏差：

(3)SST=∑(y−mean(y))2SST=\sum_{}^{}{(y-{\rm mean}(y))^2} \tag3

由上面两项可以计算：(4)R2=1−SSESSTR^2=1-\frac{SSE}{SST} \tag4

原网址: 访问
创建于: 2023-08-16 16:05:52
目录: default
标签: 无

未标明原创文章均为采集，版权归作者所有，转载无需和我联系，请注明原出处，南摩阿彌陀佛，知识，不只知道，要得到

请先后发表评论
  • 最新评论
  • 总共0条评论