第8讲 excel 插值与拟合

整理得到拟合曲线满足的方程：
m m m a ( xi )b yi i 1 i 1 m m m 2 ( xi ) a ( xi )b xi yi i 1 i 1 i 1
该方程可用消元法或克莱姆方法解出方程
a
xi yi
i 1 m m i 1 i 1
2 i 1 i 1 m m
50
0.0121 t 拟合得到直线方程为： p 0.30324
相关系数R为0.97296，平均绝对偏差SD为0.0707。
拟合的标准
m i 1
—— 实例
如果采用二次拟合，通过计算下述均 方误差
Q(a0 , a1 , a2 ) ( p(ti ) pi ) 2 (a0 a1ti a2ti2 pi ) 2
y ( x) y1 y0 ( x x0 ) y0 x1 x0
y=? y
并求取在该点的函数值。
y0
x0
x
x1
x
例题
用函数y=ex生成以下离散数据，请使 用插值的方法计算x=[2.55 2.63 2.77 2.86] 处的函数值。
x y 2.5 2.6 2.7 2.8 2.9
12.1825 13.4637 14.8797 16.4446 18.1741
i 1
m
1.0
0.8
拟合得二次方程为
压力 , P(MPa)
p 0.24845 0.00957 t 0.00015 t2
相关系数R为0.99972，平均绝对偏差SD为 0.00815,具体拟合曲线见图1-4。 比较图1-3和图1-4以及各自的相关系 数和平均绝对偏差可知，对于DME饱和蒸气 压和温度之间的关系，用二次曲线拟合优 于线性拟合。具体的计算方法及编程在下 一节里介绍。
二次拟合
整理右式得二次多项式函数拟合的满足条件方程：
m m xi 1 im x2 i i 1
x
i 1 m i 1 m
m
i
2 x i 3 x i i 1
m 2 x y i i i 1 a 0 i 1 m m 3 x a x y i 1 i i i 1 1 a 2 im m 4 x2 y x i i i i 1 i 1 m
i 1 i 1 i 1 i 1 2 i i 1
m
m
m
m
m
线性拟合应用举例
下表为实验测得的某一物性和温度之间的关系数据，表 中x为温度，y为物性数据，试用线性函数拟合温度与物 性之间的关系。 序号 1 2 3 x y 序号 8 9 10 x y 序号 15 16 17 x y
4 5 6 7
求解思路

根据基本公式 使用Excel图形的趋势线工具 使用Excel的分析工具
根据基本公式，使用Excel
使用Excel图形的趋势线工具
根据基本公式使用Excel求解
使用Excel图形的趋势线工具求解
使用Excel的数据分析工具求解
3.3.3 二次拟合
给定数据（xi ,yi), i=1, 2 , …, m ,用二次多项式函 p ( x ) a0 a1 x a2 x 2 数拟合这组数据。 设 ,作出拟合函数与数据序列的均方误差表达式 m m
0.2 0.6 0.8
p
0.4
0.495
由表1-2的数据观测可得，DME 的饱和蒸气压和温度有正相关关系， 0.0 -30 -20 -10 0 10 20 30 40 如果以函数p=a+bt来拟合,则拟合函 t 数是一条直线。通过计算均方误差Q ( a , b )最小值而确定直线方程。
Q(a, b) ( p(ti ) pi ) (a bti pi ) 2
14
13.5
13
12.5
12 2.5
2.55
2.6
2.65
2.7
2.75
1.1.3 插值方法评价
插值方法广泛应用于查表，对于表格中没有的
数据可以考虑外推。
一般的，外推的准确性较内插差。 线性插值是最常用的插值方法，可以满足大多
数工程要求。
二、拟合与参数估值
图1-1 含有噪声的数据
200
i 1
（2）用各点误差按绝对值的最大值表示
R max ( xi ) yi
1i m
（3）用各点误差的平方和表示
R R2 ( ( xi ) yi ) 2
i 1
m
或
R Q(x)- Y
2 2
式中R称为均方误差。由于计算均方误差的最小值的原则容易实现而被 广泛采用。按均方误差达到极小构造拟合曲线的方法称为最小二乘法。同时 还有许多种其他的方法构造拟合曲线，感兴趣的读者可参阅有关教材。本章 主要讲述用最小二乘法构造拟合曲线。
拟合的标准
序号
1 2 3 4 5 6 7
—— 实例
1.0
实验测得二甲醇（DME）的饱和蒸气压和温度的关系，见表1-2。
表1-2 DME饱和蒸气压和温度的关系 温度 ℃
-23.7 -10 0 10 20 30 40
图1-3 DME饱和蒸汽压和温度之间的线性拟合
蒸气压 MPa
0.101 0.174 0.254 0.359 0.662 0.880
Q(a0 , a1 , a 2 ) ( p( xi ) yi ) 2 (a0 a1 xi a 2 xi2 yi ) 2
i 1 i 1
由数学知识可知，Q( a0 ,a1 ,a2 )的极小值满足 ：
m Q 2 2 ( a a x a x 0 1 i 2 i yi ) 0 i 1 a0 m Q 2 (a0 a1 xi a 2 xi2 y i ) xi 0 i 1 a1 m Q 2 2 2 ( a a x a x y ) x 0 0 1 i 2 i i i a i 1 2
使用Excel求解
1.1.2 二次插值
线性插值并不 一定总是能够 满足精度要求。
y y2 y1 y=?
y0
x0
x1
x2
x
二次插值方法
已知数据点x0,y0,x1,y1，x2,y2 (x0<x1<x2)，求在x处 (x0<x<x1)相应的y值。 解法：由x0,y0,x1,y1 ,x2,y2构造二次曲线，并求取在x 点的函数值。 若将通过三点的曲线表示为二次多项式：
0.6
0.4 y=0.24845+0.00957 x+0.00015 x2 0.2
0.0 -30 -20 -10 0 10 20 30 40 50
温度 , t(℃ )
图1-4 DME饱和蒸气压和温度之间的 二次拟合
8.3.3 线性拟合
给定一组数据（xi,yi）,i=1, 2 , …, m ，作拟合直线 p (x)=a + bx , 均方误差为
y 0 1 ( x x0 ) 2 ( x x0 )(x x1 )
可知：
0 y0 1
y1 y0 x1 x0 以x x0代入上式 以x x1代入上式
y2 y0 y1 y0 x2 x0 x1 x0 2 x2 x1
以x x1代入上式
2.2.1 介绍
Y
150
在化工设计及化工模拟计算中，需要大 量的物性参数及各种设备参数。这些参数有 些可以通过计算得到，但大量的参数还是要 通过实验测量得到。实验测量得到的常常是 一组离散数据序列（xi ,yi）。 图1-2 如果数据序列（xi ,yi）(为一般起见), i=1,2, …,m ,含有不可避免的误差（或称“噪 声” ，如图1-1所示），或者无法同时满足 某特定的函数（如图1-2所示）,那么，只能 要求所作逼近函数ψ （x）最优地靠近样点， 即向量Q=（ψ (x1), ψ (x2), … , ψ (xm)）T 与Y=(y1,y2, …,ym)T的误差或距离最小。按Q 与Y之间误差最小原则作为“最优”标准构 造的逼近函数，称为拟合函数。
解此方程得到在均方误差最小意义下的拟合函 数p ( x )。上式称为多项式拟合的法方程。法方程的 系数矩阵是对称的。当拟合多项式n > 5时，法方程 的系数矩阵是病态的，使用通常的迭代方法求解线 性方程时会发散，要采用一些特殊算法。
二次拟合例题
请用二次多项式函数拟合下面这组数据。
序号 x y 1 -3 4 2 -2 2 3 -1 3 4 0 0 5 1 -1 6 2 -2 7 3 -5
50 0 -2 0
20 15
100
2
4
6
8
10
12
14
16
18
20
X
无法同时满足某特定函数的数据序列
Y
10
5
0
0
2
4
6

8
10
X
参数拟合的一般步骤
确定函数的形式 确定待拟合参数 确定拟合目标 选用适当的方法 利用软件工具进行拟合计算
2.2.2 拟合的标准
前面已经提到按Q与Y之间误差最小原则作为“最优”标准构造的逼近 函数，称为拟合函数，而向量Q与Y之间的误差或距离有各种不同的定义方 法，一般有以下几种。 （1）用各点误差绝对值的和表示 m R1 ( xi ) yi
例题
用函数y=ex生成以下离散数据，请使 用插值的方法计算x=[2.55 2.63 2.77 2.86] 处的函数值。
x y 2.5 2.6 2.7 2.8 2.9
12.1825 13.4637 14.8797 16.4446 18.1741
使用Excel求解
一次、二次插值结果比较
15 14.5 y=exp(x) 一次插值 二次插值
i 1 m
y
m
i
x
i 1 m i 1 2 i
m
i
m
x
i 1 m i 1
m
i
2 x i m
xi
i 1 m i 1
m
2 x i m m
( yi x xi xi yi ) /(m x ( xi ) 2 )
i 1 i 1 2 i i 1
b (m xi yi xi yi ) (m x ( xi ) 2 )
第1讲 EXCEL插值与拟合
插值与拟合的比较
相同点

都需要根据已知数据构造函数。 可使用得到函数计算未知点的函数值。 插值需要构造的函数正好通过各插值点，拟合 则不要求，只要均方差最小即可。 对实验数据进行拟合时，函数形式通常已知， 仅需要拟合参数值。
不同点

一、插值

插值需要首先构造一个正好通过各已知点 的函数y=f(x)，即对于已知点xi,yi,i=1,2,…，有 f(xi)=yi（然后求解插值点x*处的函数值y*=f(x*)。
7 9 11 13 15 17 19
9 12 15 18 21 24 27
11 12 13 14
21 23 25 27 29 31 33
30 33 36 39 42 45 48
18 19 20 21
35 37 39 41 43 45 47
51 54 57 60 63 66 69
求解思路
根据基本公式 使用Excel 使用其它工具软件
常用插值方法
一维插值-一个自变量

线性插值、一次插值 非线性插值

二次插值 三次插值 三次样条插值
二维插值-两个自变量
1.1.1 线性插值
已知数据点x0,y0,x1,y1(x0<x1)，求 在x处(x0<x<x1)相应的y值。 y1 解法：由x0,y0,x1,y1构造直线方程：
Q(a, b) ( p( xi ) yi ) 2 (a bxi yi ) 2
i 1 i 1 m m
由数学知识可知，Q (a , b)的极小值需满足：
m Q(a, b) 2 (a bxi yi ) 0 a i 1 m Q(a, b) 2 (a bxi yi ) xi 0 b i 1