第8讲excel插值与拟合

第1讲EXCEL插值与拟合插值与拟合的比较相同点都需要根据已知数据构造函数。可使用得到函数计算未知点的函数值。不同点插值需要构造的函数正好通过各插值点，拟合则不要求，只要均方差最小即可。对实验数据进行拟合时，函数形式通常已知，仅需要拟合参数值。一、插值插值需要首先构造一个正好通过各已知点的函数y=f(x)，即对于已知点xi,yi,i=1,2,…，有f(xi)=yi（然后求解插值点x*处的函数值y*=f(x*)。常用插值方法一维插值-一个自变量线性插值、一次插值非线性插值二次插值三次插值三次样条插值二维插值-两个自变量1.1.1线性插值已知数据点x0,y0,x1,y1(x0x1)，求在x处(x0xx1)相应的y值。解法：由x0,y0,x1,y1构造直线方程：并求取在该点的函数值。xyy0x0y1x1xy=?000101)()(yxxxxyyxy例题用函数y=ex生成以下离散数据，请使用插值的方法计算x=[2.552.632.772.86]处的函数值。x2.52.62.72.82.9y12.182513.463714.879716.444618.1741使用Excel求解1.1.2二次插值线性插值并不一定总是能够满足精度要求。xyy0x0y2x2y=?y1x1二次插值方法))(()(102010xxxxxxy已知数据点x0,y0,x1,y1，x2,y2(x0x1x2)，求在x处(x0xx1)相应的y值。解法：由x0,y0,x1,y1,x2,y2构造二次曲线，并求取在x点的函数值。若将通过三点的曲线表示为二次多项式：可知：代入上式以代入上式以代入上式以112010102022101011000xxxxxxyyxxyyxxxxyyxxy例题用函数y=ex生成以下离散数据，请使用插值的方法计算x=[2.552.632.772.86]处的函数值。x2.52.62.72.82.9y12.182513.463714.879716.444618.1741使用Excel求解一次、二次插值结果比较2.52.552.62.652.72.751212.51313.51414.515y=exp(x)一次插值二次插值1.1.3插值方法评价插值方法广泛应用于查表，对于表格中没有的数据可以考虑外推。一般的，外推的准确性较内插差。线性插值是最常用的插值方法，可以满足大多数工程要求。二、拟合与参数估值在化工设计及化工模拟计算中，需要大量的物性参数及各种设备参数。这些参数有些可以通过计算得到，但大量的参数还是要通过实验测量得到。实验测量得到的常常是一组离散数据序列（xi,yi）。如果数据序列（xi,yi）(为一般起见),i=1,2,…,m,含有不可避免的误差（或称“噪声”，如图1-1所示），或者无法同时满足某特定的函数（如图1-2所示）,那么，只能要求所作逼近函数ψ（x）最优地靠近样点，即向量Q=（ψ(x1),ψ(x2),…,ψ(xm)）T与Y=(y1,y2,…,ym)T的误差或距离最小。按Q与Y之间误差最小原则作为“最优”标准构造的逼近函数，称为拟合函数。024681005101520YX-202468101214161820050100150200YX图1-2无法同时满足某特定函数的数据序列图1-1含有噪声的数据2.2.1介绍参数拟合的一般步骤确定函数的形式确定待拟合参数确定拟合目标选用适当的方法利用软件工具进行拟合计算2.2.2拟合的标准前面已经提到按Q与Y之间误差最小原则作为“最优”标准构造的逼近函数，称为拟合函数，而向量Q与Y之间的误差或距离有各种不同的定义方法，一般有以下几种。（1）用各点误差绝对值的和表示（2）用各点误差按绝对值的最大值表示（3）用各点误差的平方和表示式中R称为均方误差。由于计算均方误差的最小值的原则容易实现而被广泛采用。按均方误差达到极小构造拟合曲线的方法称为最小二乘法。同时还有许多种其他的方法构造拟合曲线，感兴趣的读者可参阅有关教材。本章主要讲述用最小二乘法构造拟合曲线。miiiyxR11)(iimiyxR)(max122212Y-Q(x)R))((或imiiyxRR拟合的标准——实例实验测得二甲醇（DME）的饱和蒸气压和温度的关系，见表1-2。由表1-2的数据观测可得，DME的饱和蒸气压和温度有正相关关系，如果以函数p=a+bt来拟合,则拟合函数是一条直线。通过计算均方误差Q2121)())((),(imiiimiipbtaptpbaQ拟合得到直线方程为：相关系数R为0.97296，平均绝对偏差SD为0.0707。tp0.01210.30324-30-20-10010203040500.00.20.40.60.81.0pt图1-3DME饱和蒸汽压和温度之间的线性拟合序号温度℃蒸气压MPa1-23.70.1012-100.174300.2544100.3595200.4956300.6627400.880表1-2DME饱和蒸气压和温度的关系(a,b)最小值而确定直线方程。拟合的标准——实例如果采用二次拟合，通过计算下述均方误差拟合得二次方程为相关系数R为0.99972，平均绝对偏差SD为0.00815,具体拟合曲线见图1-4。比较图1-3和图1-4以及各自的相关系数和平均绝对偏差可知，对于DME饱和蒸气压和温度之间的关系，用二次曲线拟合优于线性拟合。具体的计算方法及编程在下一节里介绍。21221012210)())((),,(imiiimiiiptataaptpaaaQ2000150009570248450t.t..p-30-20-10010203040500.00.20.40.60.81.0y=0.24845+0.00957x+0.00015x2压力,P(MPa)温度,t(℃)图1-4DME饱和蒸气压和温度之间的二次拟合8.3.3线性拟合给定一组数据（xi,yi）,i=1,2,…,m，作拟合直线p(x)=a+bx,均方误差为2121)())((),(imiiimiiybxayxpbaQ由数学知识可知，Q(a,b)的极小值需满足：0)(2),(1imiiybxaabaQ0)(2),(1iimiixybxabbaQ))(()())(/()(2112111211211121121112111mimiiimimiimiiiimiimiimiiimiimiimiimiimiimiimiimiiimiimiixxmyxyxmbxxmyxxxyxxxmxyxxya整理得到拟合曲线满足的方程：mimimiiiiimimiiiyxbxaxybxma111211)()()(该方程可用消元法或克莱姆方法解出方程线性拟合应用举例下表为实验测得的某一物性和温度之间的关系数据，表中x为温度，y为物性数据，试用线性函数拟合温度与物性之间的关系。序号xy序号xy序号xy179821301535512912923331637543111510253617395741318112739184160515211229421943636172413314520456671927143348214769求解思路根据基本公式使用Excel使用其它工具软件根据基本公式使用Excel求解使用Excel图形的趋势线工具求解使用Excel的数据分析工具求解3.3.3二次拟合给定数据（xi,yi),i=1,2,…,m,用二次多项式函数拟合这组数据。设,作出拟合函数与数据序列的均方误差表达式2210xaxaap(x)21221012210)())((),,(imiiimiiiyxaxaayxpaaaQ由数学知识可知，Q(a0,a1,a2)的极小值满足：miiiiimiiiiimiiiixyxaxaaaQxyxaxaaaQyxaxaaaQ12221021221011221000)(20)(20)(2二次拟合整理右式得二次多项式函数拟合的满足条件方程：miiimiiimiimiimiimiimiimiimiimiimiiyxyxyaaaxxxxxxxxm121121014131213121121解此方程得到在均方误差最小意义下的拟合函数p(x)。上式称为多项式拟合的法方程。法方程的系数矩阵是对称的。当拟合多项式n5时，法方程的系数矩阵是病态的，使用通常的迭代方法求解线性方程时会发散，要采用一些特殊算法。二次拟合例题请用二次多项式函数拟合下面这组数据。序号1234567x-3-2-10123y4230-1-2-5求解思路根据基本公式使用Excel图形的趋势线工具使用Excel的分析工具根据基本公式，使用Excel使用Excel图形的趋势线工具