用微软excel进行非线性曲线拟合

1 警示:这些材料所描述的实验具有潜在的危险性,因此需要高标准的安全训练 特殊的设备和装置, 并在合适的人员指导下才能进行. 对于履行这样的安全程序和措施,你负有全部的责任和义务, 并独自承担其风险. 对于所提供的任何材料的内容或其执行情况,MIT 将不负任何责任和义务, 不承担任何风险. 法律提示 用微软 Excel求解程序进行非线性曲线拟合1 计算 kobs kreal及迪拜 休克尔曲线 由医学博士 Gheorghiu编写 I. 动力学: kobs(k 观察)和 kreal(k 真实)的计算 1. 在文件菜单中点击新建 ,然后点击普通工作簿: 1 如果你有什么意见请与Mircea Gheorghiu博士联系. 2 2. 在文件菜单中,保存此工作簿. 我喜欢将工作簿命名为动力学_MG(MG 是我名字的缩写)并将它保存在个人文件夹内. 3 3. 你的工作簿中需要五个工作 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf . 四个用于动力学数据. 第五个用来进行 迪拜 休克尔计算. 这五个表要附在 书 关于书的成语关于读书的排比句社区图书漂流公约怎么写关于读书的小报汉书pdf 面 报告 软件系统测试报告下载sgs报告如何下载关于路面塌陷情况报告535n,sgs报告怎么下载竣工报告下载 或口头报告中. 如果工作表不够,点击插入然后在菜单中点击工作表. 为四个动力学工作表命名. 例如我选择 动力学A(0.02M NaNO3),动力学 B(0.05M NaNO3),动力学C(0.1M NaNO3)和动力学D(0.2M NaNO3). 4. 现在开始将实验数据添加到四个工作表中. 以动力学 B 工作表为例: 在 A 栏中输入时间(以秒为单位),B 栏中输入相应时间下的实验得到的吸光 率(@420nm). 表格中再增加两栏 一栏为计算得到的吸光率(由方程式 3 计算得到) 另外一栏为实验吸光率(B栏)与计算吸光率(C栏)差的平方. 4 5. 提请注意,以下是在我发的讲义中介绍的二级反应积分动力学方程. 变 量的意义和我的讲义以及实验手册上的是一致的: ( )tkcexp A AA 1 1AA obsf 0 f0 f - - - = (3) 以与实验数据拟合的吸光率表示的积分二级反应动力学方程 A0: 初始吸光率 A: t时刻的吸光率 Af: 当所有 H2Asc反应完全时的吸光率 6. 我们刚刚完成使用Microsoft Excel求解程序的准备工作. 每一个动力学 工作表中需要增加包含两类信息的单元格,H2 和 H3 分别输入固定值 A0 和 epsilon. H5(Af值)和 H6(kobs)栏的内容则是可变的. 首先将估计值输入 到Af和 kobs栏中. 在最小化过程之后,求解程序分别在可变单元H5和H6 中返回回归系数. 求解程序不提供系数的标准偏差. 7. 为了能自动代入动力学方程,含 A0,epsilon,Af,kobs的单元格必须给定名 5 称(这是 Excel所要求的). · 对于 A0,在 H2单元格中输入 B2. · 将 epsilon 值输入 H3 单元格,你得到的 epsilon 值由朗伯 比尔公式计算 得到(实验第一天记录). 由我的结果根据最小二乘法计算得到斜率e 1020. · 在 H5单元格输入 Af的最佳估计值,即 0.25(为什么?). · 将 kobs的估计值输入 H6单元格中. 我的估计值是 5. 为了能自动插入方程式3,A0,epsilon,Af,kobs必须给定名称. 以命名A0为例,首先 点击H2单元格,然后点击插入,名称,定义: 以下窗口将会弹出: 6 请注意并核对 A0 值的正确位置,在这个例子中为(根据 Excel 的语法): 动力学 B!$H$2,表示在动力学工作表 B的H2单元格. 点击添加键,选择 OK. 继续命名 H3:H5单元格. 然后,我们将矢量A2:A22命名为 t. 首先选择A2:A22,使其亮显, 然后点击插入,命名,定义并将其在工作簿中的名称改为 t (检查表示一栏以确保 输入名称正确). 定义名称窗口如下 8. 求解程序通过两步优化曲线: · 第一步,计算吸光率的“原始”值. 7 · 第二步为优化步骤,计算得到的原始吸光率经过优化以符合实验值. A. 初始步骤: 在C2单元格中输入=Af/((1-((A0-Af)/A0)*EXP(-kobs*t*Af/epsilon))). H2单元格中 填入t 0的吸光率计算值. 由公式3可得它等于A0. 为了填写 C3到 C22单元格,点击 C2单元格. 将鼠标箭头指向该单元格右下角并 按住左键不放,向下拖动鼠标到 C22单元格,松开左键. 从 C2到 C22的单元格都 会按照 C2的计算方式算出(初始)吸光率: 8 B. 优化步骤: 非线性曲线拟合步骤 9. 在单元格 D2中键入=(B2-C2)^2. 回车. 10. 点击单元格 D2,将鼠标拖到 D22,按计算吸光率的方式操作. 11. 在单元格 D23对 D2到 D22进行求和(点击S图标). . 9 然后回车. 12. 点击单元格 D23. 点击工具,在菜单中点击求解程序(Solver… ) 10 弹出求解参数窗口,目标单元格是 D23. 在通过改变中键入单元格 H5和 H6(即$H$5和$H$6). 11 在求解参数窗口中点击选择键,将最大时间调整到1200秒(动力学运行时间). 选 择OK. 求解参数窗口重新出现,先点击最小值键然后点击求解键: 12 求解结果窗口弹出,请注意单元格H4和H5的值已被更新. 现在你知道kobs的值是 2.60. 注意初始值曾被估计为5. 这时可以打印部分报告: 包括结果 灵敏度和极限. 比如结果报告应为以下格 式: 13 重复步骤4到12以得到动力学A 动力学C和动力学D的结果. 必要时请更新定义 名称栏的指代信息. II. 迪拜 休克尔方程 在“动力学中”讲义中(参见其中变量意义),迪拜 休克尔方程式被定义为: 21 21 021 21 210real I1 I*3*1.02logk I1 IZ*Z*1.02logklogk + += + += (6) 其中 kreal由方程(4)得到: al obsreal K ][Hkk + = (4) 用工作表5(重命名为迪拜 休克尔)来计算,并以logkreal (y轴)对I0.5/(I0.5+1) (x轴) 作图. 完成后的迪拜 休克尔工作表如下: 14 1. 建一个 5 行 7 列的表, 标题的顺序和内容如 X 图所示. 切记 在 Excel 中 x轴的值应在 y 轴的值的左边(例如,A列为 x轴的值而 B列为 y轴的 值). 2. kobs的值将从指定的工作表中的相应位置读取. 例如:点击单元格C2并键 入:动力学 A!$H$6. C2 单元格将填入 2.07 作为 kobs值. C3 单元格中填 入动力学 B!$H$6,C4单元格中填入动力学C!$H$6而C5单元格中填入动 力学 D!$H$6. 3. 在单元格 B7中填入工作表信息HNO3的摩尔浓度. 将抗坏血酸的酸度系 数(Kal=6.76*10-5)填入 B8单元格 15 4. 在单元格 D2 到 D5 中填入计算得到的 kreal (见方程式 4). 例如在单元格 D2 中填入: (C2/$B$8)*0.5*$B$7(0.5 的出现是因为紫外比色皿中 HNO3 的浓度已被稀释到原始溶液浓度的一半). 由于单元格B7和B8是引用确 定的地址,例如$B$7,所以单元格 D2 到 D5 的结果可以自动生成. 点击 D2单元格,将鼠标箭头指向该单元格右下角并按住左键不放,向下拖动鼠 标到 D5单元格,这些单元格的结果将自动生成. 5. 用 LOG10(D2)在 D2中计算 logkreal,用先前描述的方法将鼠标箭头拖动到 D5单元格进行计算: 16 6. 余下的计算是关于 I0.5/(I0.5+1),即在 A列中计算 x轴变量的值. · 首先填入 NaNO3的原始溶液摩尔浓度. 我在实验中使用 E2到 E5单元格 的数据. · 其次,在 F2到 F5单元格中计算 NaNO3 +HNO3的真实摩尔浓度. 例如在 F2单元格中 计算 (E2+$B$7)*0.5. 乘以 0.5是因为当以 3ml+3ml混合 时(参见实验和讲义)紫外比色皿中的溶液为初始溶液浓度的一半. 记住 对于单价阴离子和阳离子,摩尔浓度在数值上等于离子强度. · 第三步,在G2到G5单元格中计算 F2到 F5的平方根. 例如在G2中键入 SQRT(F2),按回车键. · 第四步 在 A2 到 A5 单元格中计算 I0.5/(I0.5+1). 例如在 A2 中键入 17 G2/(G2+1). 点击 A2,将鼠标箭头指向该单元格右下角并按住左键不放, 向下拖动鼠标到 A5单元格. 7. 最后一步为迪拜 休克尔图的制作. · 选择 A2到 B5,使其显亮. 点击插入,然后在菜单中点击图表 : 18 · 在图表格式窗口第一步,选择图表形式:XY(分散型);然后选择图表亚形式, 使其显亮. · 点击进入第二步和第三步,分别填写图名 X轴和 Y轴的轴名. · 点击下一步 然后点击完成. · 经过一些编辑后图表显示如下: 19 · 点击图表,在下拉菜单中点击趋势线,在图中添加最小二乘法拟合的直 线. 选择曲线趋势/衰减模式,线性. 20 · 点击选择键,检查显示图表中的方程和显示图表中的 R平方值: · 最小二乘法直线符合方程:y 2.7835x+1.8686,R2 0.9809(满足要求,然 而我确信 5.310的学生会得到更好的 R2值). 21 8. 为了计算斜率(1.02*Z1*Z2),截距 k0(I=0 时的斜率)和 R2,首先在迪拜 休 克尔工作表中添加 H10到 H12单元格. · 在 Slope=后的单元格(B10)后键入 SLOPE(B2:B5,A2:A5). · 在 Intercept=后的单元格(B11)后键入 INTERCEPT(B2:B5,A2:A5). · 在 k0 后的单元格(B11)后键入 10^B11. · 在 R^2=后的单元格(B13)键入 RSQ(B2:B5,A2:A5).