Excel数值方法

Excel数值方法1.组合图表2.内嵌小插图3.数值求导4.微分方程数值解5.滴定分析6.线性回归7.方程求根8.规划求解1.组合图表当需要绘制两组数据，有一坐标的单位和范围不相同时,这类图表称为组合图。绘制步骤如下：1、按一般绘制两组Y系列的方法得到统一Y轴的XY散点图2、选定其中一个系列的Y值为第二图表的Y轴值。方法是：光标指向该组数据点，双击左键弹出“数据系列格式”对话框。选择其中的“坐标轴”选项卡。3、在“坐标轴”选项卡中选取“次坐标轴”，则将所选Y值定为次坐标轴。单击“确定”，得到组合图。4、添加Y轴标题。光标指向图表区，单击右键，出现图表区设置的快捷菜单栏。选中“图表选项”指令，出现“图表选项”对话框。选择对话框的“标题”选项卡，完成坐标轴标题的设定。0.00.51.01.52.02.5050100150200t/min未区分Y轴的图表y=0.0062x+0.93070.00.10.20.30.40.5050100150200t/min吸光度0.00.51.01.52.02.5-ln(At-A)2.内嵌小插图当需要将一小图嵌入一大图表内时，通常的做法是将小图表叠加在主图表上，这得到的不是统一的整体。以后若对这种图表进行复制、移动等操作，则会发现只能选定其中之一。有两种方法将两个（或多个）图表融合为一体：粘贴和组合。一、粘贴1.按通常办法制作图表，设置好它的大小、标题、刻度等。2.按通常办法绘制内嵌小图。注意大、小图中“图案”选项卡中的“区域”均选“无”单选框，否则会产生部分遮盖现象。3.选定小图表，按住Shift键，同时打开“编辑”菜单，选“复制图片”，显现“复制图片”对话框，并按图确定外观、大小与格式单选项。4.激活主图表,单击工具栏的“粘贴”按钮，小图即被嵌在大图中（见例子）。二、组合1、按要求制好大、小图表后，将小图表拖曳到主图表内适当位置。2、光标指向工作表下方绘图工具栏的“选择对象”箭头“”，单击左键。3、鼠标指向大图表区域外左上角适当位置后，按下鼠标左键不放，用拖曳法在大图表四周画出一个矩形。4、鼠标指在图表内任何位置，当光标成为十字双箭头时单击鼠标右键弹出快捷菜单。5、鼠标指针指向子菜单“组合”，显示“组合”子菜单后选择其中的“组合”指令，内嵌小插图制作完成。3.数值求导当难以得到导数的表达式时，可以根据导数的定义计算导数值：xxfxxfxyxx)()(0000limlimExcel的数值可以有15位有效数字，Δx可以设置得很小，得到相当精确的导数值。用导数法判定酸碱滴定化学计量点的过程如下：判定滴定终点工作表2、D列给出pH增量ΔpH，D3单元格输入公式：=B3-B23、E列得到滴定过程中pH变化与体积变化之比ΔpH/ΔV，E3单元格输入公式：=D3/C34、绘ΔpH/ΔV的图象需要决定作为横坐标的体积，取平均值较为合理，其值在F列给出，F3输入公式：=（A3+A2）/25、在G列计算二阶导数，G3单元格输入公式：=（E3-E2）/C36、选定C3：G3，用自动填充得到所有数据。7、一阶导数（小插图）和二阶导数图1、C列给出体积增量ΔV，C3单元格输入公式为：=A3-A2-200-150-100-500501001502001.81.92.02.12.22.3VΔ(ΔpH/ΔV)/ΔV1.71.92.1V返回内嵌小插图4.微分方程数值解化学反应动力学方程常为一阶微分方程。并非所有的微分方程都有解析式的解。事实上除了一些简单的基元反应，大多数反应动力学给以得到解析解或解析式很复杂，甚至于不可能有解析解。因此必须求助于数值解。另一方面化学动力学关心的是在时间t体系中各物质的浓度有足够精度的近似解即可。常微分方程数值解利用离散方法，最简单的是Euler法，但该法累积误差很大，其原因是f(x,y)为曲线，用Teller公式展开：y(xn+h)=y(xn)+hy`(xn)+h2/y``(xn)+…….Euler公式只取线性项，忽略了高次项，其递进公式为：y(xn+h)=y(xn)+hy`(xn)Runge-Kutta法解微分方程RK法考虑了四次项，因此精度在为提高。在递进公式里x取值为：xi,xi+h/2,xi+h，而y为yi+1=yi+(T1+2T2+2T3+T4)/6其中：T1=hf(xi,yi)T2=hf(xi+h/2,yi+T1/2)T3=hf(xi+h/2,yi+T2/2)T4=hf(xi+h,yi+T3)当用四阶RK法计算一级反应动力学过程时，仅涉及因变量，上述四项RK表达式简化为：T1=-kh[A]tT2=-kh([A]t+T1/2)T3=-kh([A]t+T2/2)T4=-kh([A]t+T3)Excel工作表操作过程如下：1、T1、T2、T3、T4的值在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）后[A]t，在F6输入公式：=F5+(B6+2*C6+2*D6+E6)/63、选定区域B6：F6，自动填充得到所需数据。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由此得，V[H+]=K（Veq-V），当VVeq时，V[H+]对V作图得一直线，外推直线，与横坐标交于终点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图法——趋势线法用A4:A10和D4:D10的数据作散点图，在图上加趋势线。y=-2.809146E-09x+7.031566E-08R2=9.999692E-0189101112010203040VNaOH/MLpH0.0E+001.0E-082.0E-083.0E-084.0E-085.0E-08VNaOH*[H+]6.线性回归找出与数据点{xi,yi}吻合的最好的函数系数，这一过程称为曲线拟合。用最小二乘法求最佳拟合参数的过程称为回归分析。回归分析的另一目的是获得回归参数的标准偏差以及确定数据与模型数学表达式吻合程度。根据函数关系式y计算=f(x)的性质，有线性回归和非线性回归之分。这里介绍几种用Excel处理的一元及多元线性回归分析的方法。一、一元线性回归将一组数据{xi,yi}拟合为一直线最简单，即找出一条通过它们的直线：y=ax+bExcel中的SLOPE-INTERCEPT和LINEST函数都可以求回归参数a和b，使得直线y=ax+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：A10），得到直线的斜率-2.809E-09。2、在F6单元格填入：=INTERCEPT（D4：D10，A4：A10），得到直线的截距7.0316E-08。如工作表所示。LINEST函数法LINEST函数可对一组数据点{xi，yi}作线性回归分析。LINEST可处理的线性方程的通式为：y=ax+b或y=a1x1+a2x2+…+amxm+bLINEST的语法为：LINEST(y值数列，x值数列，常数_逻辑，统计_逻辑)。常数_逻辑为一逻辑值，指明是否强制使常数b为0。如果常数_逻辑是TRUE、1或被省略，回归参数包括截距b；否则直线过原点（b=0）。而统计_逻辑指明是否返回附加回归统计值。如果统计_逻辑是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+…+amxm若将变量进行变换：x1=x,x2=x2,…xm=xm则多项式化为多元一次函数：y=a1x1+a2x2+…+amxm+b可用LINEST函数求多项式的参数及其回归统计。通常到三次方（三阶）就有中等程度的精度。对于N个数据点，用于拟合的多项式最高阶数为（N-1）。例如某物质在二组分混合溶剂中的溶解度s与溶剂组成x之间有非直线关系。用LINEST进行多项式拟合过程如下：1、二阶多项式拟合。选定单元格区域B12：D14，输入公式：=LINEST(A2:A11,B2:C11,1,1),按Ctrl+Shift+Enter，得到二次函数的回归系数及统计。2、三阶多项式拟合。选定单元格区域B15：E17，输入公式：=LINEST(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+0.939x2+3.038x3根据计算值得到的拟合曲线与实验值很吻合。返回线性回归0123456700.51xsΔ为实验数据，实线为三阶多项式拟合曲线本节介绍一元线性方程和多元线性方程组的解法。一、单变量求解Excel“工具”菜单中的“单变量求解”是用Newton-Raphson迭代法求一元方程近似解的程序。Newton-Raphson迭代法的基本思想是：先在函数可能为零的x值范围内确定一初始值x1,求出曲线在处的斜率s1，然后由函数在x1的斜率得到改进了的根x2：x2=x1-y1/s1如此重复，直至得到满意的结果。“单变量求解”通过改变一选定单元格（可变单元格）的值，使得另一单元格（目标单元格）的值达到预定值，见“单变量求解”对话框。7.方程求根例：求方程x2-7.8e-