在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.3
1.3~1.4
1.4~1.5
1.5~1.6
1.6~1.8
+1.8
重量W,%
15.15
28.16
12.4
6.48
7.58
30.23
灰分A,%
4.97
10.77
18.73
27.64
39.58
73.71
由表1可以计算得到相应的累计重量和累计灰分量，计算数据见表2
表2 改进的M曲线上的节点
节点序号i
0
1
2
3
4
5
6
累计重量xi
0.00
15.15
43.31
55.71
62.19
69.77
100.00
累计灰分量yi
0.00
75.30
378.58
610.83
789.94
1089.95
3318.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月

ABSTRACT
Cubic Spline Interploation Used in EXCEL
FAN Xiaonan
Department of Materials Science and Technology
Anhui University of Science and Technology
This paper provides a method of establishing a spline fitting function in EXCEL. A real example of spline fitting in EXCEL was demonstrated.
Keywords Cubic Spline Interploation Programming Solve EXCEL Application

范肖南：1956.7~，安徽合肥人，中国矿业大学化工学院博士在读，现就职于安徽理工大学材料科学与工程系，副教授。
电话：[phone]（O） 0554-6668515（H）
邮编：232001
Email: xnfan@