在Excel中进行样条插值计算

《在Excel中进行样条插值计算》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。

1、样条插值计算的EXCEL实现范肖南安徽理工大学材料科学与工程系安徽淮南232001摘要本文介绍了样条插值在Excel中实现的方法，并以实例说明了具体应用过程。关键词样条插值规划求解EXCEL应用Excel功能强大，它的数学及工程函数有近百个，使用起来十分方便，可以用来解决许多工程问题，例如，实验数据处理、回归分析、曲线拟合建模等。在Excel中可以利用图表工具绘制光滑连续曲线，但是如果打算利用Excel进行样条插值计算或是从光滑曲线上获取数值时，就会遗憾地发现没有相应的内置函数可供利用。样条插值方法被广泛的用于选矿工程的数

2、据处理中，Excel可以应用于选矿工程应用的方方面面，因此解决在Excel中使用样条插值的问题具有实际应用意义。1．样条函数的基本公式[1]设有n+1个互异的节点，函数在各节点处的值为，分段函数在子区间上的表达式是次数不高于3的代数多项式，样条函数就是由这些分段函数构成的，其表达式是，——————（1）式中，是待定参数，对于常用的自然样条，，其它值可以通过解以下方程组得到——————（2）式中，，对于样条函数的计算，关键是样条函数在各节点上的二阶微分值的确定。1．在Excel中求解样条函数的思路在EXCEL中根据公式（1）

3、和公式（2）确定样条函数中的待求参数并进行样条插值计算需要解决三个问题，一是在工作表中如何进行数据设置，二是如何求解样条函数在各节点上的二阶微分值，三是如何在工作表中应用样条函数。（1）EXCEL工作表中的变量布置在工作表中的数据设置形式应当以输入数据量小，计算公式输入方便为准，对于变量通常采用列的形式设置。（2）样条函数在节点上二阶微分值的确定样条函数在各节点上的二阶微分值的确定是建立样条函数的关键。方程组（2）可以利用Excel的自带工具“规划求解”进行求解。从形式上看这个问题没有明显的目标函数和约束条件。实际上可以将

4、方程组中的任意一个方程作为目标函数，而将其他方程作为约束条件。明确这一点以后，求解方程组的方法也就和其它规划求解问题类似了。为了便于在规划求解对话框中设置目标函数和约束条件，将方程组（2）改写为——————（3）这样在设置目标函数和约束条件时，输入数据0要比输入其它数据更为方便。（3）工作表中样条插值函数的计算样条函数是分段函数，对于n+1个节点，具有n个分段函数，在进行插值计算时，首先要判定欲求变量所处的子区间，然后确定对应的分段函数，代入相应数据进行计算，利用VBA编程可以简化其工作量。具体解算过程在实例中介绍。2．样

5、条函数求解实例在分选作业的预测计算中，为了使计算结果更准确，往往希望增加浮沉试验的密度级，这在实际应用中是十分困难的。可以利用计算的办法将浮沉试验密度级加密，取得符合需要的一组新的可选性数据，这个过程也称为可选性数据的细化。利用三次样条函数插值法，建立迈耶尔曲线和密度曲线的数学模型，并由此出发导出可选性分析的全部数据，实现可选性曲线细化[2]。迈耶尔曲线（M曲线）可以采用改进的M曲线进行样条插值，改进的M曲线的纵坐标仍然是累计重量，横坐标则是累计灰分量。为了便于与密度曲线结合导出其它可选性分析数据，在进行样条插值时，通常将

6、M曲线的自变量和因变量交换，即以横坐标为累计重量，纵坐标为累计灰分量。下面以改进的M曲线的样条插值为例，说明如何在Excel中建立插值函数的方法。例原煤浮沉试验的数据见表1，试以累计重量为横坐标，累计灰分量为纵坐标对改进的M曲线建立样条插值函数，两端点按自然样条处理，即M0=Mn=0。表1原煤浮沉试验密度级-1.31.3~1.41.4~1.51.5~1.61.6~1.8+1.8重量W,%15.1528.1612.46.487.5830.23灰分A,%4.9710.7718.7327.6439.5873.71由表1可以计算得

7、到相应的累计重量和累计灰分量，计算数据见表2表2改进的M曲线上的节点节点序号i0123456累计重量xi0.0015.1543.3155.7162.1969.77100.00累计灰分量yi0.0075.30378.58610.83789.941089.953318.21对表2所示的节点数据进行样条插值，在Excel工作表中将各变量分别输入在不同列中，按照图1所示的变量布置方式，各变量公式的输入过程非常简单。图1Excel工作表中的变量设置（1）变量公式的输入①变量、、与按照上述相应公式输入，在公式输入过程中仅需输入节点1对

8、应的变量公式，其它节点通过公式拷贝的方法即可完成全部节点的公式输入。例如对于变量的输入，在单元格D4中输入公式=B4-B3，将鼠标移至D4单元格的右下方，这时会出现一个小十字╋标记，按下鼠标左键向下选取单元格D4:D9，松开左键，公式自动拷贝至D5:D9单元格中，完成所有节点的公式输入。其它公式的输入方