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

《用微软ecel进行非线性曲线拟合》由会员分享，可在线阅读，更多相关《用微软ecel进行非线性曲线拟合（21页珍藏版）》请在金锄头文库上搜索。

1、 1 警示警示:这些材料所描述的实验具有潜在的危险性,因此需要高标准的安全训练 特殊的设备和装置,并在合适的人员指导下才能进行. 对于履行这样的安全程序和措施,你负有全部的责任和义务,并独自承担其风险. 对于所提供的任何材料的内容或其执行情况,MIT 将不负任何责任和义务,不承担任何风险. 法律提示 用微软用微软 Excel 求解程序进行非线性曲线拟合求解程序进行非线性曲线拟合1 计算计算 kobs kreal及迪拜及迪拜 休克尔曲线休克尔曲线 由医学博士由医学博士 Gheorghiu 编写编写 I. 动力学动力学: kobs(k观察观察)和和 kreal(k真实真实)的计算的计算 1. 在文

2、件文件菜单中点击新建新建 ,然后点击普通工作簿普通工作簿: 1 如果你有什么意见请与Mircea Gheorghiu博士联系. 2 2. 在文件文件菜单中,保存保存此工作簿. 我喜欢将工作簿命名为动力学动力学_MG(MG 是我名字的缩写)并将它保存在个人个人文件夹内. 3 3. 你的工作簿中需要五个工作表你的工作簿中需要五个工作表. 四个用于动力学数据. 第五个用来进行 迪拜 休克尔计算. 这五个表要附在书面报告或口头报告中这五个表要附在书面报告或口头报告中. 如果工作表不够,点击插入插入然后在菜单中点击工作表工作表. 为四个动力学工作表命名. 例如我选择 动力学动力学A(0.02M NaNO

3、3),动力学动力学 B(0.05M NaNO3),动力学动力学C(0.1M NaNO3)和动力学动力学D(0.2M NaNO3). 4. 现在开始将实验数据添加到四个工作表中. 以动力学动力学 B 工作表工作表为例: 在 A 栏中输入时间时间(以秒为单位),B 栏中输入相应时间下的实验得到的吸光实验得到的吸光 率率(420nm). 表格中再增加两栏 一栏为计算得到的吸光率计算得到的吸光率(由方程式 3 计算得到) 另外一栏为实验吸光率实验吸光率(B 栏栏)与计算吸光率与计算吸光率(C 栏栏)差差的平方. 4 5. 提请注意,以下是在我发的讲义中介绍的二级反应积分动力学方程. 变 量的意义和我的

4、讲义以及实验手册上的是一致的: ()tkcexpAAA11AAobsf 0f0f = (3) 以与实验数据拟合的吸光率表示的积分二级反应动力学方程以与实验数据拟合的吸光率表示的积分二级反应动力学方程 A0: 初始初始吸光率吸光率 A: t 时刻时刻的吸光率的吸光率 Af: 当当所有所有 H2Asc 反应反应完全完全时的吸光率时的吸光率 6. 我们刚刚完成使用 Microsoft Excel 求解程序求解程序的准备工作. 每一个动力学 工作表中需要增加包含两类信息的单元格,H2 和 H3 分别输入固定值 A0 和 epsilon. H5(Af值)和 H6(kobs)栏的内容则是可变的. 首先将估

5、计值输入 到Af和kobs栏中. 在最小化过程之后,求解程序求解程序分别在可变单元H5和H6 中返回回归系数. 求解程序求解程序不提供系不提供系数的数的标准偏标准偏差差. 7. 为了能自动代入动力学方程,含 A0,epsilon,Af,kobs 的单元格必须给定名必须给定名5 称称(这是 Excel 所要求的). 对于 A0,在 H2 单元格中输入 B2. 将 epsilon 值输入 H3 单元格,你得到的 epsilon 值由朗伯 比尔公式计算 得到(实验第一天记录). 由我的结果根据最小二乘法计算得到斜率 1020. 在 H5 单元格输入 Af的最佳估计值,即 0.25(为什么?). 将

6、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

7、 (检查表示表示一栏以确保 输入名称正确). 定义名称定义名称窗口如下 8. 求解程序求解程序通过两步优化曲线: 第一步,计算吸光率的吸光率的“原始原始”值. 7 第二步为优化步骤优化步骤,计算得到的原始吸光率经过优化以符合实验值. A. 初始初始步骤: 在在C2单元格中输入单元格中输入=Af/(1-(A0-Af)/A0)*EXP(-kobs*t*Af/epsilon). H2单元格中 填入t 0的吸光率计算值. 由公式3可得它等于A0. 为了填写为了填写 C3 到到 C22 单元格单元格,点击点击 C2 单元格单元格. 将鼠标箭头指向该单元格右下角并将鼠标箭头指向该单元格右下角并 按住左键不

8、放按住左键不放,向下拖动鼠标到向下拖动鼠标到 C22 单元格单元格,松开左键松开左键. 从从 C2 到到 C22 的单元格都的单元格都 会按照会按照 C2 的计算方式算出的计算方式算出(初始初始)吸光率吸光率: 8 B. 优化优化步骤: 非线性曲线拟合步骤非线性曲线拟合步骤 9. 在单元格 D2 中键入=(B2-C2)2. 回车回车. 10. 点击单元格 D2,将鼠标拖到 D22,按计算吸光率的方式操作. 11. 在单元格 D23 对 D2 到 D22 进行求和(点击图标). . 9 然后回车回车. 12. 点击单元格 D23. 点击工具工具,在菜单中点击求解程序(Solver ) 10 弹出

9、求解参数求解参数窗口,目标单元格是 D23. 在通过改变通过改变中键入单元格 H5 和 H6(即$H$5 和$H$6). 11 在求解参数求解参数窗口中点击选择选择键,将最大时间最大时间调整到1200秒秒(动力学运行时间). 选 择OK. 求解参数求解参数窗口重新出现,先点击最小值最小值键然后点击求解键求解键: 12 求解结果求解结果窗口弹出,请注意单元格H4和H5的值已被更新. 现在你知道kobs的值是 2.60. 注意初始值曾被估计为5. 这时可以打印部分报告: 包括结果结果 灵敏度和极限灵敏度和极限. 比如结果报告结果报告应为以下格 式: 13 重复步骤4到12以得到动力学动力学A 动力

10、学动力学C和动力学动力学D的结果. 必要时请更新定义定义 名称名称栏的指代指代信息. II. 迪拜迪拜 休克尔方程休克尔方程 在“动力学中”讲义中(参见其中变量意义),迪拜 休克尔方程式被定义为: 212102121210realI1I*3*1.02logkI1IZ*Z*1.02logklogk+=+= (6) 其中 kreal由方程(4)得到: alobsrealKHkk+ = (4) 用工作表5(重命名为迪拜迪拜 休克尔休克尔)来计算,并以logkreal (y轴)对I0.5/(I0.5+1) (x轴) 作图. 完成后的迪拜迪拜 休克尔休克尔工作表如下: 14 1. 建一个 5 行 7 列

11、的表, 标题的顺序和内容如 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 单元格

12、 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 单元格进行计算: 1

13、6 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 中键

14、入SQRT(F2),按回车回车键. 第四步 在 A2 到 A5 单元格中计算 I0.5/(I0.5+1). 例如在 A2 中键入 17 G2/(G2+1). 点击 A2,将鼠标箭头指向该单元格右下角并按住左键不放, 向下拖动鼠标到 A5 单元格. 7. 最后一步为迪拜迪拜 休克尔休克尔图的制作. 选择 A2 到 B5,使其显亮. 点击插入插入,然后在菜单中点击图表图表 : 18 在图表格式图表格式窗口第一步第一步,选择图表形式图表形式:XY(分散型分散型);然后选择图表亚形式图表亚形式, 使其显亮. 点击进入第二步和第三步,分别填写图名图名 X 轴和轴和 Y 轴的轴名轴的轴名. 点击下一步下一

15、步 然后点击完成完成. 经过一些编辑后图表显示如下: 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)后键入 10B11. 在 R2=后的单元格(B13)键入 RSQ(B2:B5,A2:A5).