在Excel中进行样条插值计算.doc

样条插值计算的EXCEL实现范肖南安徽理工大学材料科学与工程系 安徽淮南232001摘要本文介绍了样条插值在Excel中实现的方法，并以实例说明了具体应用过程。关键词样条插值 规划求解 EXCEL应用Excel功能强大，它的数学及工程函数有近百个，使用起来十分方便，可以用来解决许多工程问题，例如，实验数据处理、回归分析、曲线拟合建模等。在Excel中可以利用图表工具绘制光滑连续曲线，但是如果打算利用Excel进行样条插值计算或是从光滑曲线上获取数值时，就会遗憾地发现没有相应的内置函数可供利用。样条插值方法被广泛的用于选矿工程的数据处理中，Excel可以应用于选矿工程应用的方方面面，因此解决在Excel中使用样条插值的问题具有实际应用意义。1 样条函数的基本公式1设有n+1个互异的节点，函数在各节点处的值为 ，分段函数在子区间上的表达式是次数不高于3的代数多项式，样条函数就是由这些分段函数构成的，其表达式是，（1）式中，是待定参数，对于常用的自然样条，其它值可以通过解以下方程组得到（2）式中，对于样条函数的计算，关键是样条函数在各节点上的二阶微分值的确定。2 在Excel中求解样条函数的思路在EXCEL中根据公式（1）和公式（2）确定样条函数中的待求参数并进行样条插值计算需要解决三个问题，一是在工作表中如何进行数据设置，二是如何求解样条函数在各节点上的二阶微分值，三是如何在工作表中应用样条函数。（1） EXCEL工作表中的变量布置在工作表中的数据设置形式应当以输入数据量小，计算公式输入方便为准，对于变量通常采用列的形式设置。（2） 样条函数在节点上二阶微分值的确定样条函数在各节点上的二阶微分值的确定是建立样条函数的关键。方程组（2）可以利用Excel的自带工具“规划求解”进行求解。从形式上看这个问题没有明显的目标函数和约束条件。实际上可以将方程组中的任意一个方程作为目标函数，而将其他方程作为约束条件。明确这一点以后，求解方程组的方法也就和其它规划求解问题类似了。为了便于在规划求解对话框中设置目标函数和约束条件，将方程组（2）改写为（3）这样在设置目标函数和约束条件时，输入数据0要比输入其它数据更为方便。（3） 工作表中样条插值函数的计算样条函数是分段函数，对于n+1个节点，具有n个分段函数，在进行插值计算时，首先要判定欲求变量所处的子区间，然后确定对应的分段函数，代入相应数据进行计算，利用VBA编程可以简化其工作量。具体解算过程在实例中介绍。3 样条函数求解实例在分选作业的预测计算中，为了使计算结果更准确，往往希望增加浮沉试验的密度级，这在实际应用中是十分困难的。可以利用计算的办法将浮沉试验密度级加密，取得符合需要的一组新的可选性数据，这个过程也称为可选性数据的细化。利用三次样条函数插值法，建立迈耶尔曲线和密度曲线的数学模型，并由此出发导出可选性分析的全部数据，实现可选性曲线细化2。迈耶尔曲线（M曲线）可以采用改进的M曲线进行样条插值，改进的M曲线的纵坐标仍然是累计重量，横坐标则是累计灰分量。为了便于与密度曲线结合导出其它可选性分析数据，在进行样条插值时，通常将M曲线的自变量和因变量交换，即以横坐标为累计重量，纵坐标为累计灰分量。下面以改进的M曲线的样条插值为例，说明如何在Excel中建立插值函数的方法。例 原煤浮沉试验的数据见表1，试以累计重量为横坐标，累计灰分量为纵坐标对改进的M曲线建立样条插值函数，两端点按自然样条处理，即M0=Mn=0。表1 原煤浮沉试验密度级-1.31.31.41.41.51.51.61.61.8+1.8重量W,%15.1528.1612.46.487.5830.23灰分A,%4.9710.7718.7327.6439.5873.71由表1可以计算得到相应的累计重量和累计灰分量，计算数据见表2表2 改进的M曲线上的节点节点序号i0123456累计重量xi0.0015.1543.3155.7162.1969.77100.00累计灰分量yi0.0075.30378.58610.83789.941089.953318.21对表2所示的节点数据进行样条插值，在Excel工作表中将各变量分别输入在不同列中，按照图1所示的变量布置方式，各变量公式的输入过程非常简单。图1 Excel 工作表中的变量设置（1）变量公式的输入变量、与按照上述相应公式输入，在公式输入过程中仅需输入节点1对应的变量公式，其它节点通过公式拷贝的方法即可完成全部节点的公式输入。例如对于变量的输入，在单元格D4中输入公式=B4-B3，将鼠标移至D4单元格的右下方，这时会出现一个小十字 标记，按下鼠标左键向下选取单元格D4:D9，松开左键，公式自动拷贝至D5:D9单元格中，完成所有节点的公式输入。其它公式的输入方法类似。对于各节点对应的二阶微分值，除了首尾节点有外，其余是待定参数，可任意给定，例如都设为0.5。的输入，按公式（3）将各节点对应的的左端公式输入，如对于节点1，在单元格I4中输入公式=E4*H3+2*H4+F4*H5-G4，将单元格I4中的公式按上述拷贝方法拷贝到单元格区域I5:I8中即可完成全部节点对应公式的输入。（2）节点二阶微分值的求解 规划求解参数的设置用鼠标单击“工具”菜单中的“规划求解”命令，屏幕上出现“规划求解参数”对话框。在“设置目标单元格”框中输入公式所在的单元格地址I4，将“等于”设置为=0，在“可变单元格”框中输入所在单元格区域的地址，即单元格地址H4:H8。然后设置约束条件：单击“添加”按钮，出现“添加约束”对话框，在“单元格引用位置”框中输入I5，“约束值”设置为=0，单击“添加”按钮继续设置其它约束，依次在“单元格引用位置”框中输入I6、I7和I8，“约束值”均设置为=0，全部约束设置完成后，单击“确定”按钮，回到“规划求解参数”对话框。设置完成后的“规划求解参数”对话框如图2所示。图2 “规划求解参数”对话框及设置 计算得出规划求解的结果在“规划求解参数”对话框中完成了参数设置后，用鼠标单击对话框的“求解”按钮，即可得到相应的结果。此时，如果选择“保存规划求解结果”选项，单击“确定”按钮退出，EXCEL将在工作表中保持各单元格改变后的值。对应所在单元格的数值就是的解。求解结果如图3所示。（3）样条函数的输入与计算在得到各节点的后，就可以按照公式（1）写出各子区间对应的样条函数，本例中共有6个分段函数，但各个函数对前述各变量的引用关系相同，因此利用相对引用关系，在建立第一个子区间的样条插值函数后，通过上述拷贝的方法将其它子区间的样条函数公式写出，参见图3所示。图3 节点二阶微分值的求解结果及样条函数写出各子区间的样条函数后，就可以进行插值计算。对于任意给定的自变量x值，首先必须判定该自变量x所在的子区间，然后利用相应子区间的样条函数进行插值计算。在图3中就是将x写入J列中相应单元格中后，就可以在K列中对应单元格中获得其样条插值的计算值。如果要进行较多数据的插值计算，如可选性分析数据加密，上述应用虽然简单，但是有些繁琐，对于有一定编程基础的应用者可以在Excel中利用VBA进行少量编程，实现给定批量x的插值自动计算。4 结语样条插值计算的编程工作量较大，在Excel中完成样条插值计算并不需要具备编程知识，使用者只要熟悉样条函数公式变量的对应关系，具有基本的电子表格知识，例如，知道如何用鼠标去选择一个单元格，以及去拷贝和粘贴它们，了解EXCEL公式输入的基本方法。就可以很便捷地完成样条插值计算。本文介绍的样条插值计算方法扩展了EXCEL的应用领域，对于希望借助EXCEL解决工程问题的专业工作者具有实际应用价值。参考文献1颜庆津等计算方法，高等教育出版社，1991年10月2冯绍灌，选煤数学模型，煤炭工业出版社，1990年10月ABSTRACTCubic Spline Interploation Used in EXCELFAN XiaonanDepartment of Materials Science and TechnologyAnhui University of Science and TechnologyThis paper provides a method of establishing a spline fitting function in EXCEL. A real example of spline fitting in EXCEL was demonstrated. Keyword