《Excel数值方法.ppt》由会员分享，可在线阅读，更多相关《Excel数值方法.ppt（63页珍藏版）》请在三一文库上搜索。
1、Excel 数 值 方 法,1.组合图表,2.内嵌小插图,3.数值求导,4.微分方程数值解,5.滴定分析,6.线性回归,7.方程求根,8.规划求解,1.组合图表,当需要绘制两组数据，有一坐标的单位和范围不相同时,这类图表称为组合图。绘制步骤如下： 1、按一般绘制两组Y系列的方法得到统一Y轴的XY散点图 2、选定其中一个系列的Y值为第二图表的Y 轴值。方法是：光标指向该组数据点，双击左键弹出“数据系列格式”对话框。选择其中的“坐标轴”选项卡。 3、在“坐标轴”选项卡中选取“次坐标轴”，则将所选Y 值定为次坐标轴。单击“确定”，得到组合图。 4、添加Y轴标题。光标指向图表区，单击右键，出现图表区设
2、置的快捷菜单栏。选中“图表选项”指令，出现“图表选项”对话框。选择对话框的“标题”选项卡，完成坐标轴标题的设定。,未区分Y轴的图表,2.内嵌小插图,当需要将一小图嵌入一大图表内时，通常的做法是将小图表叠加在主图表上，这得到的不是统一的整体。以后若对这种图表进行复制、移动等操作，则会发现只能选定其中之一。 有两种方法将两个（或多个）图表融合为一体：粘贴和组合。,一、粘贴 1.按通常办法制作图表，设置好它的大小、标题、刻度等。 2.按通常办法绘制内嵌小图。注意大、小图中“图案”选项卡中的“区域”均选“无”单选框，否则会产生部分遮盖现象。 3.选定小图表，按住Shift键，同时打开“编辑”菜单，选“
3、复制图片”，显现“复制图片”对话框，并按图确定外观、大小与格式单选项。 4.激活主图表,单击工具栏的“粘贴”按钮，小图即被嵌在大图中（见例子） 。,二、组合 1、按要求制好大、小图表后，将小图表 拖曳到主图表内适当位置。 2、光标指向工作表下方绘图工具栏的“选择对象”箭头“ ” ，单击左键。 3、鼠标指向大图表区域外左上角适当位置后，按下鼠标左键 不放，用拖曳法在大图表四周画出一个矩形。 4、鼠标指在图表内任何位置，当光标成为十字双箭头时单击鼠标右键弹出快捷菜单。 5、鼠标指针指向子菜单“组合”，显示“组合”子菜单后选择其中的“组合”指令，内嵌小插图制作完成。,3.数值求导,当难以得到导数的表
4、达式时，可以根据导数的定义计算导数值：,Excel的数值可以有15位有效数字，x可以设置得很小，得到相当精确的导数值。用导数法判定酸碱滴定化学计量点的过程如下：,判定滴定终点工作表,2、D列给出pH增量pH ，D3单元格输入公式： =B3-B2 3、E列得到滴定过程中pH变化与体积变化之比pH/V ，E3单元格输入公式：=D3/C3 4、绘pH/V的图象需要决定作为横坐标的体积，取平均值较为合理，其值在F列给出，F3输入公式： =（A3+A2）/2 5、在G列计算二阶导数，G3单元格输入公式： =（E3-E2）/C3 6、选定C3：G3，用自动填充得到所有数据。 7、一阶导数（小插图）和二阶导
5、数图,1、C列给出体积增量V ，C3单元格输入公式为：=A3-A2,返回内嵌小插图,4.微分方程数值解,化学反应动力学方程常为一阶微分方程。并非所有的微分方程都有解析式的解。事实上除了一些简单的基元反应，大多数反应动力学给以得到解析解或解析式很复杂，甚至于不可能 有解析解。因此必须求助于数值解。另一方面化学动力学关心的是在时间t体系中各物质的浓度有足够精度的近似解即可。 常微分方程数值解利用离散方法 ，最简单的是Euler法，但该法累积误差很大，其原因是f(x,y)为曲线，用Teller公式展开： y(xn+h)=y(xn)+h y (xn) +h2/y(xn) +. Euler公式只取线性项
6、，忽略了高次项，其递进公式为： y(xn+h)=y(xn)+h y (xn),Runge-Kutta法解微分方程 RK法考虑了四次项，因此精度在为提高。在递进公式里x取值为：x i, x i +h/2, x i +h，而y 为 y i+1=y i +(T 1 +2T 2 +2T 3 +T 4 )/6 其中： T 1 =hf(x i ,y i ) T 2 =hf(x i +h/2,y i + T 1 /2 ) T 3 =hf(x i +h/2,y i + T 2 /2 ) T 4 =hf(x i +h,y i + T 3 ) 当用四阶RK法计算一级反应动力学过程时，仅涉及因变量，上述四项 RK表
7、达式简化为：,T 1 =-khAt T 2 =-kh(At + T 1 /2 ) T 3 =-kh(At + T 2 /2 ) T 4 =-kh(At + T 3 ) Excel工作表操作过程如下： 1、 T 1 、 T 2、 T 3 、 T 4 的值在B6（=-$D$1*F5*$D$2)、C6 （=-$D$1*(F5+B6/2)*$D$2) 、D6 (=-$D$1*(F5+C6/2)*$D$2) 、E6 （=-$D$1*(F5+D6)*$D$2)输入公式计算。 2、求出递进一个步长（20s）后At ，在F6输入公式： =F5+(B6+2*C6+2*D6+E6)/6 3、选定区域B6：F6，自
8、动填充得到所需数据。 4、G列为相应的解析解，H列为数值解相对于解析解的误差。可见RK法精度很高。,5.滴定分析,多数滴定曲线呈S形，终点在曲线的拐点处。用前述的一阶导数和二阶导数可以确定终点，但必须利用终点附近的数据。当终点的拐点很不明显，或终点附近的数据不全时，导数法便无能为力了，然而用Gran法可以准确地找到终点（见例子）的位置，它利用终点之外的数据。 设Veq为终点NaOH体积，加入V体积NaOH后弱酸HA转变为A，其浓度为A-=C0V/（V0+V），而此时HA的浓度为 HA=C0V0/（V0+V）-A-=H+*A-/K 由此得，VH+=K（Veq-V），当VVeq时， VH+对V作图
9、得一直线，外推直线，与横坐标交于终点Veq。,一、Gran计算法LINEST函数法 按下表输入V、pH数据，然后在C列计算氢离子浓度，在C4单元格输入公式：=10-B4；在D列计算V*H+的值，在D4单元格输入：=A4*C4，用自动填充得到所有数据。 用终点前的数据A4：A10计算直线的斜率和截距。方法是：选定E6：F8区域，输入公式：=LINEST（D4：D10，A4：A10，1，1） 按Shift+Ctrl+Enter，则在E6、F6中给出回归值，在E7、F7中给出标准偏差，在E8、F8中给出相关系数R2。 在F10计算终点体积：=-F6/E6,返回线性回归,二、Gran图法趋势线法 用A
10、4:A10和D4:D10的数据作散点图，在图上加趋势线。,6.线性回归,找出与数据点xi,yi吻合的最好的函数系数，这一过程称为曲线拟合。用最小二乘法求最佳拟合参数的过程称为回归分析。回归分析的另一目的是获得回归参数的标准偏差以及确定数据与模型数学表达式吻合程度。 根据函数关系式y计算=f(x)的性质，有线性回归和非线性回归之分。这里介绍几种用Excel处理的一元及多元线性回归分析的方法。,一、一元线性回归,将一组数据xi,yi拟合为一直线最简单，即找出一条通过它们的直线： y=ax+b Excel中的SLOPE- INTERCEPT和LINEST函数都可以求回归参数a和b，使得直线y=ax+
11、b与数据点的残差平方和最小。较为简捷的方法是作XY散点图，添加趋势线，并且选取“显示公式”和“显示R平方值”复选框。,SLOPE-INTERCEPT函数法 这两个函数的语法为：SLOPE（y值数列，x值数列）和INTERCEPT （y值数列，x值数列）。X值数列为自变量数据点集合xi，y值数列为观察倒的因变量数据集合yi。因此，SLOPE（）返回根据xi，yi数据点拟全的线性回归直线的斜率a。INTERCEPT（）返回根据xi，yi数据点拟全的线性回归直线的截距b。 用终点前的数据A4：A10计算直线的斜率和截距的工作表操作过程如下： 1、在E6单元格填入：=SLOPE（D4：D10，A4：A
12、10），得到直线的斜率-2. 809E-09。 2、在F6单元格填入：=INTERCEPT（D4：D10，A4：A10），得到直线的截距7.0316E-08。如工作表所示。,LINEST函数法 LINEST函数可对一组数据点xi，yi作线性 回归分析。LINEST可处理的线性方程的通式为： y=ax+b或y=a1x1 +a2x2+am xm +b LINEST的语法为：LINEST(y值数列，x值数列，常数_逻辑，统计_逻辑)。常数_逻辑为一逻辑值，指明是否强制使常数b为0。如果常数_逻辑是TRUE、1或被省略，回归参数包括截距b；否则直线过原点（b=0）。而统计_逻辑指明是否返回附加回归统计
13、值。如果统计_逻辑是TRUE或1，LINEST除给出回归系数a，b外，还给出回归统计数组。用LINEST函数计算直线的斜率和截距的方法是：选定E6：F8区域，输入公式： =LINEST（D4：D10，A4：A10，1，1） 按Shift+Ctrl+Enter，则在E6、F6中给出回归值，在E7、F7中给出标准偏差，在E8、F8中给出相关系数R2（见例子）,二、多元线性回归多项式拟合 有时实验数据表现为一曲线，相应的拟合函数未知。需要一种普适的函数拟合曲线。常用方法之一是用多项式拟合。原则上任何连续函数均可用多项式展开： y=b+a1x +a2x2+am xm 若将变量进行变换： x1=x ,x
14、2=x2,xm = xm 则多项式化为多元一次函数： y=a1x1 +a2x2+am xm +b 可用LINEST函数求多项式的参数及其回归统计。通常到三次方（三阶）就有中等程度的精度。对于N个数据点，用于拟合的多项式最高阶数为（N-1）。,例如某物质在二组分混合溶剂中的溶解度s与溶剂组成x之间有非直线关系。用LINEST进行多项式拟合过程如下： 1、二阶多项式拟合。选定单元格区域B12：D14，输入公式：=LINEST(A2:A11,B2:C11,1,1),按Ctrl+Shift+Enter，得到二次函数的回归系数及统计。 2、三阶多项式拟合。选定单元格区域B15：E17，输入公式：=LIN
15、EST(A2:A11,B2:D11,1,1),按Ctrl+Shift+Enter，得到三次函数的回归系数及统计。 3、四阶多项式拟合。选定单元格区域B18：F20，输入公式：=LINEST(A2:A11,B2:E11,1,1),按Ctrl+Shift+Enter，得到四次函数的回归系数及统计。,由于不同阶多项式的系数个数不同，选定区域大小也不一样。由图可知，三阶多项式拟合的回归系数及y值的标准偏差均小于其它两种拟合方式，故选用它。LINEST 结果表中系数排列顺序为： am 、 、a2、a1、 b 与工作表中x幂次排列顺序相反，因此溶解度与组成的函数关系为： s=-0.003+2.0275x
16、+0.939x2+3.038 x3 根据计算值得到的拟合曲线与实验值很吻合。,返回线性回归,为实验数据，实线为三阶多项式拟合曲线,本节介绍一元线性方程和多元线性方程组的解法 。 一、 单变量求解 Excel“工具”菜单中的“单变量求解”是用Newton-Raphson迭代法求一元方程近似解的程序。 Newton-Raphson迭代法的基本思想是：先在函数可能为零的x值范围内确定一初始值x1,求出曲线在处的斜率s1，然后由函数在x1的斜率得到改进了的根x2： x2 = x1-y1/s1 如此重复，直至得到满意的结果。 “单变量求解”通过改变一选定单元格（可变单元格）的值，使得另一单元格（目标单元
17、格）的值达到预定值，见“单变量求解”对话框。,7.方程求根,例：求方程x2-7.8e-7x1/2-2.9e-9=0的根 1.A2单元格输入数值1作为初始值。 根据（4.1.2.4）式，在B2单元格输入公式：=A22-7.8E-7*SQRT(A2)-2.9E-9,单击Enter,得x=1时该函数的值。 2.现在的任务是利用“单变量求解”指令，出现“单变量求解”对话框。 3.对话框的“目标单元格”编辑栏要求输入含有求解公式的单元格，本例为$B$2。 4.“目标值”编辑框要求输入一个数值，指定所求公式的计算值，在此输入0。 5.“可变单元格”编辑框要求输入目标单元格的公式中自变量所在单元格，因此填入
18、$A$2。,6.“目标单元格”和“可变单元格” 编辑框右边均有图标 ，便于单元格引用。 .单击“确定”，出现单变量求解状态对话框，显示这一次求解为y0.000492888。工作表和的数值为求解结果。 此解不是很精确，因未设置最大误差，只用了默认值0.001，若要得到较好的解，需设置最大误差，它限定了迭代运算停止前，两次迭代结果之差的最大值。,设置最大误差过程如下： 1.打开“工具”菜单,选其中的“选项”指令,出现“选项”对话框. 2.选项对话框包含八个选项卡,单击其中的“重新计算”选项卡标签,出现“重新计算”对话框. 3.在该对话框左下方“最大误差”编辑框中填入最大误差值如1.00E-12(一
19、般情况下,最大误差设置为1E-121E-15即可),单击“确定”.结果如下:,二、解线性联立方程矩阵解法 通常有三种方法解线性联立方程:行列式法、矩阵法和Newton-Raphson迭代法。 线性方程组AX=C的矩阵表示经数学变换得： X=A-1C 即解矩阵（X）等于系数矩阵A的逆矩阵（ A-1）乘以常数矩阵（C）。若A=0，则不存在逆矩阵，方程无解。 Excel的工作表函数：矩阵相乘MMULT（数组）和矩阵求逆MINVERSE（数组）为线性方程组提供了方便。,用矩阵法解下列方程组的过程如下： 2x + y z = 0 x y + z = 6 x + 2y + z = 3 1.在Excel工作
20、表A2:C4单元格区域输入方程的系数矩阵,D2:D4单元格区域输入常数矩阵. 2.选中区域E2:E4,然后输入公式:=MMULT(MINVERSE(A2:C4),D2:D4),同时按Ctrl+Shift+Enter,在E2、E3、E4分别得 x,y,z的解2、-1、3。 其中MINVERSE（A2:C4）得到系数矩阵的逆矩阵,然后由MMULT()得到逆矩阵与常数矩阵乘积.,8.规划求解,“规划求解”是独立的附加程序包，从“工具”菜单的“加载宏”列表框中选取“规划求解”，于是工具菜单中就会出现“规划求解”指令。它可作非线性回归和解非线性方程组。 一、非线性回归 “规划求解”是一种优化程序，它通过
21、改变一个或数个“可变单元格”的数值，使“目标单元格”为数值达到最大值或最小值。用“规划求解”作非线性最小二乘法曲线拟合操作过程如下： 1.在工作表上端适当单元格内输入待定系数的初始值(可变单元格) 2.输入需要拟合的实验数据:自变量x数列和因变量y实验值数列,3.添加y计算值数据列.它们是用拟合函数 计算所得的数值,公式中含x值和一个或 数个待定系数. 4.另一列填入各数据点的残差平方和(y实验值-y计算值)2 5.选定一单元格,在此计算残差平方和(目标单元格) 6.用“规划求解”改变拟合函数的待定系数(可变单元),使得残差平方和的值(目标单元格)极小. “规划求解”是一搜索程序，若设置的初始
22、值接近最终值，则它能以最快和最有效的方式找到解。反之，若设置的初始值偏离最终目标值太远，“规划求解”可能得不到方程的解。为保证“规划求解”得到的是全局性解而不是区域性解，最好用几套不同的初始值求解。,例：电极电势E与加入的K2Cr2O7体积V之间的 函数式为： E=E0+RT/nF*ln（Fe3+0 V0+6CV）/（Fe2+0V0-6CV） 根据实验求E0 、Fe3+0 、Fe2+0，此时上式不可能转变为它们的线性函数，因此必须作非 线性拟合。 用“规划求解”对Excel工作表中的E0 、Fe3+0 、Fe2+0数值优化过程如下： 1.打开工具菜单,选“规划求解”,出现“规划求解参数”对话框
23、 2.用引用图标 ,在“设置目标单元格”编辑框中输入残差平方和位置$D$13 3.根据最小二乘法,残差平方和应为极中,因此在“等于”的三个单选框中选“最小”,4.用引用图标 ,在“可变单元格”编辑框中输入 E0 、Fe3+0 、Fe2+0所在单元格$D$1:$D$3 5.当“规划求解”不能收敛到合理的解时，提高收敛度是一种解决办法.有时选中“规划求解选项”对话框中“自动按比例缩入”复选框或调整可变单元格的初始值也可能解决问题。 6.单击“求解”出现“规划求解结果”对话框,若对话框中显示:规划求解收敛于当前的解,可满足所有的约束.单击“确定”则得到优化E0 、Fe3+0 、Fe2+0后的Exce
24、l工作表.,返回解方程,返回非线性方程,二、解方程组 “规划求解”解方程组是“单变量求解”解一元方程的延伸，可用来解线性方程组和非线性方程组。目标单元格是方程组中任一方程所在，目标值为该方程的常数。可变单元格是方程组的自变量所在。约束为方程组中其余方程。“规划求解”改变可变单元格的值，使得目标值与约束均得到满足。例如解线性方程组： F1： 2x + y z = 0 F2：x y + z = 6 F3： x + 2y + z = 3 未知数x,y,z是可变单元格变量，将第一个方程的常数作为目标单元格的数值，第二和第三方程的常数作为约束条件。任意设置可变单元格的值（x,y,z），目标是寻找满足约束
25、条件（6，3），并且使目标值为0的（x,y,z）.,用“规划求解”解上述线性方程组的过程如下： 1.在Excel工作表中B2:D2输入未知数初始值(任意数)1,1,1 2.根据F1、F2、F3方程式,在B4、C4、D4单元格输入 公式:=2*B2+C2-D2、=B2-C2+D2、=B2+2*C2+D2。结果为（2,1,4），不等于（0,6,3），显然初始值不是方程的解。,3.打开“工具”菜单,单击“规划求解”.出现 “规划求解参数”对话框 4.“设置目标单元格”输入方程F1所在位置,$B$4, “等于”栏设置目标值为0. 5.“可变单元格”编辑框输入未知数所在单元格:$B$2:$D$2 6.单
26、击“约束”选项区的“添加”按钮,出现“添加约束”对话框.在“单元格引用位置”编辑框输入F2的位置:$C$4, 用下拉箭头选取等号“=”,在“约束值”编辑框输入F2的常数:6. 单击“添加”按钮.用同样方法对方程F3设置约束.单击“确定”按钮,回到“规划求解参数”对话框. 7.所有参数设置好后,按“求解”,得到方程组的解.这与矩阵法的结果一致。,返回线性方程,（二）非线性方程组 多元一次线性方程较为容易，对初始值设定无严格要求。若方程为非线性，例如溶液中离子平衡，则必须设置不同初始值，才能确保解的可信性。 在Cd(ClO4)2和NaCN的水溶液中有如下平衡： Cd2+ + iCN-CdCNi2-
27、i HCN H+ + CN- H 2O H + + OH- 由、Ka、Kw表达式结合物料平衡和电荷平衡可得到含3个未知数CN-、Cd2+、H+的3 个方程：,F1 Cd2+ + Cd2+ i CN- iCM =0 F2 CN- + CN- H+ /Ka+ Cd2+ ii CN- iCL =0 F3 H+ - CN- +2Cd2+ - Ka / H+ 2CM + Cd2+ (2-i)i CN- i =0 这3 个方程均为未知浓度的高次函数，用“规划求解”解此非线性方程过程如下： 1.在Excel工作表中按线性方程组方法输入相关各项 2.解非线性方程组,未知浓度CN-、Cd2+、H+的初始值的设置
28、很重要, 根据该溶液情况,在D2:F2中分别设置为：0.0007,1E-12,1E-10 3.以D4为目标,E4和F4为约束,用“规划求解”改变D2:F2可变单元格的值,使目标值和约束值均为0,4.因“添加约束”列表框的“约束值” 编辑框中没有设置下限的可变单元格, 欲对未知数设置下限,一种解决办法是： 选中“规划求解选项”对话框中“假定非负”复选框，可以使其下限为0。本例中若不选此项，则会出现负的氢离子浓度值，找不到有用的解。 5.按“求解”后，“规划求解结果”对话框显示：规划求解找不到有用的解。 6.不断改变未知浓度CN-、Cd2+、H+的初始值,当Cd2+、H+调整为1E-13 、1E-11时，“规划求解结果”对话框显示：规划求解找到一解，可以满足所有的约束及最优状况。单击“确定”则在Excel工作表中得到非线性方程组的解。,数据分析线性回归统计 回归统计可决定回归系数的值及其有效性，例如用最中二乘法拟合的曲线是否有效，回归参数的精度及置信区间，回归方程拟合好坏，两条回归直线是否相同（即是否能用同一直线表示它们），数据能否回归到过原点的直线等。,参考资料 1徐抗成.Excel数值方法及其在化学中的应用.兰州:兰州大学出版社.2000.,