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

李杰卡尔德

Excel趋势线拟合的局限性

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


第二步，添加趋势线


但是，该方法的缺陷也是显而易见的，就是只能拟合预设模板的几个函数，如果碰上其他函数就直接歇菜了，比如：
y=\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拟合了一下，结果几乎是完全一样的


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


拟合误差评估：

作为一个拟合结果，我们显然需要知道拟合的各种误差，常用的评价指标就是 R^2 ，不同于趋势线上可以直接显示R^2，这里我们要手动计算了：
我们之前已经用过残差平方和SSE的概念了（即每一项与其拟合值之间的偏差）：
SSE=\sum_{}^{}{(y-y_i)^2} \tag2
那么再介绍一个概念，叫做总离差平方和（SST），即每一项与其平均值的偏差：
SST=\sum_{}^{}{(y-{\rm mean}(y))^2} \tag3
由上面两项可以计算：R^2=1-\frac{SSE}{SST} \tag4

一个正直人

Excel会用了真的厉害，一个师兄不太会写代码，就捣鼓Excel，作图相当漂亮

为中国加油

牛了个大逼，先码住

静上月明泪

思路不错

超级小休

你们这群白嫖怪[发火]居然收藏比赞多！

刘胡

点赞只是手滑 收藏才是真爱啊[耶]

诺诺罗亚索隆

你看，我点赞收藏，关注三联，是不是有逼格多了[惊喜]

胡本達

这么神奇吗，我要是扔进去一个正态分布的数据他直接能给我拟合出一个正态分布的表达式[好奇]？？那真的太神奇了[赞同]

麻辣向锅

原始数据呈反比例函数趋势，所以采用a/（x+b）的吗？我的原始数据呈指数趋势，用这个方法拟合不了，是不是哪里出了错还是要调整操作

郭联

真的强，对多项式也适用[赞同]