用EXCEL中处理数据时,我常常用到拟合功能,尤其是为系列测试数据拟合经验公式时,这个功能很好用.特别是3次多项式拟合,拟合出来的曲线十分贴近.

但我最近发现一个新问题,在有些情况下,虽然拟合出来的曲线很漂亮很贴近,但用拟合出来的公式去计算数值,该数值与测定值的差距很大,明显不如趋势线上那么贴近,十分反常.先看一个例子.

粗一看,这没什么,曲线与数据点很贴近,符合得很好.

但是,如果你较真一点,根据右上图的公式(温度单位为K的EXCEL拟合公式),去计算粘度值,会发现这个数值与实测值相距非常大.

我把以摄氏度和绝对温度为单位的数值,分别在EXCEL(以下简称E), Origin(以下简称O) 和 Matlab(以下简称M)中都进行了拟合,求出拟合公式, 然后计算了实测值与计算值的方差以及方差之和.

差异是巨大的!

当温度单位为摄氏度,X值较小,拟合公式的准确性还不错,实测值与拟合值之差的平方和都比较小,E/O/M三者的计算结果都不错,O和M的精度较好一点.

Matlab做得特别好的是,当结果偏差较大时,会给出友好的提示.

但是, 温度单位为K时, X的值放大了很多, EXCEL 的拟合公式出现了严重的问题,实测粘度只有一点几cp, 拟合公式算出来有20多.完全错误.

而O和M的值依然是准确无误,精度依然良好(注意,O,M都设置为显示最高精度).我发现这两个软件的数值精度,已经高于EXCEL的计算精度,也就是说,小数点后的值再多,EXCEL也用不上了.(EXCEL只有15位有效数字).

如果仔细对比一下这几种拟合公式的系数,--与Origin或Matlab拟合的公式对比,EXCEL拟合出来的3次方项的系数只相差了0.0000008左右,但由于和绝对温度的三次方相乘,结果会放大很多,导致计算值完全偏离了.

实质上,这是用很大的几个数进行加减,然后得到一个很小的数.这样对大数的计算精度要求就非常高,否则误差就会很大. 而EXCEL2016的拟合公式中,默认情况下,三次方项的系数是精度最差的,只有一位有效数字.如果使用这样的精度进行计算,结果是无法使用的.

之前,我以为这是EXCEL的一个Bug,但是在网上搜索研究之后,发现EXCEL公式的这个精度,其实是可以调整的.方法是:选中趋势线公式的文本框,然后在弹出菜单中点击"设置趋势线标签格式...",在弹出的对话框中,将数字的精度设定到小数为12位左右即可.

这个时候我们再来看一下数据,已经正常了,E与M,O的计算结果没有差异了:

这个精度是令人满意的!

因此,在用EXCEL做多项式(或指数)拟合曲线时,基于以上情况,建议:

1.如果Y值不大,但X值很大,要特别注意精度问题,最好验算一下默认的公式是否足够精确.

2.EXCEL拟合默认显示出来的公式系数,其精度不够高,当发现实际值与拟合值相差显著时,应该手动设置一下趋势线的数字显示精度,避免出现拟合结果不准确的问题.

Update 3:原来EXCEL也可以设置公式系数的精度,不是BUG而是我不会用,汗颜! 2018-1-22

Update 2:研究发现Origin也可以调高显示的结果精度,调整之后精度与Matlab相同了.2018-1-22

Update:根据网友陈奂生的建议,试用matlab拟合并对比一下,发现matlab的精度非常好,谢谢! 2018-1-20