2020年10月4日研究了一下牛顿插值法,其用途是使用x、y两组数值,根据新的x值返回对应的y值,与TREND、FORECAST函数不同,这种方法可应对非线性数据。其作用类似于图表中的曲线拟合或LINEST多项式拟合。类似这种迭代题,数学书上写的很深奥,放到excel里就很直观了。
B、C列是x、y对应值,一阶均差D3=(C3-C2)/($B3-$B2),下拉;
![b668bd14569fafda8d41689747611d0a.png](https://i-blog.csdnimg.cn/blog_migrate/159373bad9b8fdea41904826c0948a59.jpeg)
二阶均差E4=(D4-D3)/($B4-$B2),下拉;
![188f8eefa4739d7a2f423ff3747f89dc.png](https://i-blog.csdnimg.cn/blog_migrate/9da40f3ee348d14eee97219103a254b5.jpeg)
三阶均差F5=(E5-E4)/($B5-$B2)。其他均差以此类推。每次新增一个已有数值,均要增加一阶。
![2e61ec4b0f52ac6fff8fef49f268bc48.png](https://i-blog.csdnimg.cn/blog_migrate/1965903b7a9cd323dd27a41176ee787a.jpeg)
这种方法只是为了便于理解,如果只有少量数据,使用这种方法也可行,如数据点多,每个点都要修改,工作量太大,故应使用D3=IFERROR((C3-C2)/($B3-OFFSET($B$7,-COLUMNS($A3:A$7)-ROWS($A3:A$7)+1,0)),""),右拉,下拉,返回相同的数据。
![9433a82d7508aff23ba540f35772e88a.png](https://i-blog.csdnimg.cn/blog_migrate/bced03866799eb7ee86b09cd74b3d5d8.jpeg)
A列是总计列,A2=C2,A3填{=OFFSET(D3,0,ROW(B1)-1)*PRODUCT($F$2-$B$2:B2)},下拉。
![704f0caa24a579bb7f42306ea72ed61a.png](https://i-blog.csdnimg.cn/blog_migrate/409fe2b108ee3699a2d85e448938649c.jpeg)
![7f64b1010318b8d35e6bb25f84c139d7.png](https://i-blog.csdnimg.cn/blog_migrate/1b53e83d24f5fdfa2e7cc5a615e74605.jpeg)
F2是新的x值,G2=SUM(A:A),即A列所有数值相加就是新y值。从结果看,精度相当高,其实萌软应该写个函数给大家用,然而并没有,所以现在只能写个公式玩,自定义函数肯定也能写出来,不过也很绕。此处空白太少,我写不下了。
![59c8e2a4c3816d5af56a80a25535c19f.png](https://i-blog.csdnimg.cn/blog_migrate/89aa9480ae9276ef8e3d0b74dfb4bb6f.jpeg)
这个是用LINEST做的多项式拟合,精度没有牛顿插值法那么高。下面的图表中的公式是excel自带的曲线拟合功能生成的公式,最高6次方,拟合的曲线此处与直线重合了,看不清楚。两者的原理相同,应该都是使用LINEST函数生成,所以系数完全一致。matlab里也有这种功能,如果数据要输入excel,在excel中直接计算会更加方便。
![6d6683f22cf2c7805002e858b0003448.png](https://i-blog.csdnimg.cn/blog_migrate/29cccb7e27129aa9800c11e65bf5c3fb.jpeg)
![274162f142bb57e2929769ee44fea34c.png](https://i-blog.csdnimg.cn/blog_migrate/ba961c39bd63c61b9c97aa57201f8686.jpeg)
x=11.5返回436.6132,还算可以接受。
![2da7c7fec2cce4cb16218a84dd10fdce.png](https://i-blog.csdnimg.cn/blog_migrate/c9106adf9ce64f3119ab2764bd65034f.jpeg)
像这种周期性数据就不能完全拟合了,可看到虚线,而且6次方多项式也不够,7次方多项式才会比较接近,当然图里只能到6次方多项式。x=11时一切正常。
![da9ca0676035e33697da82cf5ae4c557.png](https://i-blog.csdnimg.cn/blog_migrate/63fe54939a08700da40e158cdbe92eb0.jpeg)
x=12返回-13,也正常。
![da26179c4ac55d5347ac00514401188d.png](https://i-blog.csdnimg.cn/blog_migrate/70d16f1ac17476ebff0fa75f192dea56.jpeg)
然而,在使用一个自定义的x值时,牛顿插值法的表现又令人崩溃。x=11.5时返回-137.973,这和多项式拟合返回的结果截然不同。按说不太应该返回这个值。6.76这个值应该比较正常。不太确定怎么回事,或许牛顿插值法不太适用于这种周期数据?可能是遇到了传说中的龙格现象,高阶导数产生的误差逐渐扩散传播后导致最终值极度偏离。
![542b04b37049166c638db943bdc619b6.png](https://i-blog.csdnimg.cn/blog_migrate/8526cf7fbc9cfa3bb6e15e5c992a2daf.jpeg)