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

警示:这些材料所描述的实验具有潜在的危险性,因此需要高标准的安全训练特殊的设备和装置,并在合适的人员指导下才能进行. 对于履行这样的安全程序和措施,你负有全部的责任和义务,并独自承担其风险. 对于所提供的任何材料的内容或其执行情况,MIT将不负任何责任和义务,不承担任何风险. 法律提示

用微软Excel求解程序进行非线性曲线拟合1

计算k obs k real及迪拜休克尔曲线

由医学博士Gheorghiu编写

I. 动力学: k obs(k观察)和k real(k真实)的计算

1. 在文件菜单中点击新建,然后点击普通工作簿:

1如果你有什么意见请与Mircea Gheorghiu博士联系.

2. 在文件菜单中,保存此工作簿. 我喜欢将工作簿命名为动力学_MG(MG

是我名字的缩写)并将它保存在个人文件夹内.

3. 你的工作簿中需要五个工作表. 四个用于动力学数据. 第五个用来进行

迪拜休克尔计算. 这五个表要附在书面报告或口头报告中.

如果工作表不够,点击插入然后在菜单中点击工作表.

为四个动力学工作表命名. 例如我选择动力学A(0.02M NaNO3),动力学

B(0.05M NaNO3),动力学C(0.1M NaNO3)和动力学D(0.2M NaNO3).

4. 现在开始将实验数据添加到四个工作表中. 以动力学B工作表为例: 在

A栏中输入时间(以秒为单位),B栏中输入相应时间下的实验得到的吸光

率(@420nm). 表格中再增加两栏一栏为计算得到的吸光率(由方程式3

计算得到) 另外一栏为实验吸光率(B栏)与计算吸光率(C栏)差的平方.

5. 提请注意,以下是在我发的讲义中介绍的二级反应积分动力学方程. 变

量的意义和我的讲义以及实验手册上的是一致的:

()t k c exp A A A 11A A obs f 0f 0f ???= (3)

以与实验数据拟合的吸光率表示的积分二级反应动力学方程

A 0: 初始吸光率

A: t 时刻的吸光率

A f : 当所有H2Asc 反应完全时的吸光率

6. 我们刚刚完成使用Microsoft Excel 求解程序的准备工作. 每一个动力学

工作表中需要增加包含两类信息的单元格,H2和H3分别输入固定值A 0和epsilon . H5(A f 值)和H6(k obs )栏的内容则是可变的. 首先将估计值输入到A f 和k obs 栏中. 在最小化过程之后,求解程序分别在可变单元H5和H6中返回回归系数. 求解程序不提供系数的标准偏差.

7. 为了能自动代入动力学方程,含A 0,epsilon ,A f ,kobs 的单元格必须给定名

称(这是Excel所要求的).

? 对于A0,在H2单元格中输入B2.

? 将epsilon值输入H3单元格,你得到的epsilon值由朗伯比尔公式计算得到(实验第一天记录). 由我的结果根据最小二乘法计算得到斜率ε

1020.

? 在H5单元格输入A f的最佳估计值,即0.25(为什么?).

? 将k obs的估计值输入H6单元格中. 我的估计值是5.

为了能自动插入方程式3,A0,epsilon,A f,k obs必须给定名称. 以命名A0为例,首先点击H2单元格,然后点击插入,名称,定义:

以下窗口将会弹出:

请注意并核对A0值的正确位置,在这个例子中为(根据Excel的语法): 动力学B!$H$2,表示在动力学工作表B的H2单元格. 点击添加键,选择OK. 继续命名H3:H5单元格. 然后,我们将矢量A2:A22命名为t. 首先选择A2:A22,使其亮显,然后点击插入,命名,定义并将其在工作簿中的名称改为t (检查表示一栏以确保输入名称正确). 定义名称窗口如下

8. 求解程序通过两步优化曲线:

? 第一步,计算吸光率的“原始”值.

? 第二步为优化步骤,计算得到的原始吸光率经过优化以符合实验值. A. 初始步骤:

在C2单元格中输入=Af/((1-((A0-Af)/A0)*EXP(-k obs*t*Af/epsilon))).H2单元格中填入t0的吸光率计算值. 由公式3可得它等于A0.

为了填写C3到C22单元格,点击C2单元格. 将鼠标箭头指向该单元格右下角并按住左键不放,向下拖动鼠标到C22单元格,松开左键. 从C2到C22的单元格都会按照C2的计算方式算出(初始)吸光率:

B. 优化步骤:非线性曲线拟合步骤

9. 在单元格D2中键入=(B2-C2)^2. 回车.

10. 点击单元格D2,将鼠标拖到D22,按计算吸光率的方式操作.

11. 在单元格D23对D2到D22进行求和(点击Σ图标).

.

然后回车.

12. 点击单元格D23. 点击工具,在菜单中点击求解程序(Solver…)

弹出求解参数窗口,目标单元格是D23.

在通过改变中键入单元格H5和H6(即$H$5和$H$6).

在求解参数窗口中点击选择键,将最大时间调整到1200秒(动力学运行时间). 选择OK.

求解参数窗口重新出现,先点击最小值键然后点击求解键:

求解结果窗口弹出,请注意单元格H4和H5的值已被更新. 现在你知道k obs的值是2.60. 注意初始值曾被估计为5.

这时可以打印部分报告: 包括结果灵敏度和极限. 比如结果报告应为以下格式:

重复步骤4到12以得到动力学A 动力学C 和动力学D 的结果. 必要时请更新定义名称栏的指代信息.

II. 迪拜 休克尔方程

在“动力学中”讲义中(参见其中变量意义),迪拜 休克尔方程式被定义为:

2

1210211210real I 1I *3*1.02logk I 1I Z *Z *1.02logk logk ++=++= (6) 其中k real 由方程(4)得到:

al

obs real K ][H k k += (4) 用工作表5(重命名为迪拜 休克尔)来计算,并以logk real (y 轴)对I 0.5/(I 0.5+1) (x 轴)作图. 完成后的迪拜 休克尔工作表如下:

1. 建一个5行7列的表, 标题的顺序和内容如X图所示. 切记在Excel

中x轴的值应在y轴的值的左边(例如,A列为x轴的值而B列为y轴的值).

的值将从指定的工作表中的相应位置读取. 例如:点击单元格C2并键2. k

obs

入:动力学A!$H$6. C2单元格将填入2.07作为k obs值. C3单元格中填入动力学B!$H$6,C4单元格中填入动力学C!$H$6而C5单元格中填入动

力学D!$H$6.

数(K al=6.76*10-5)填入B8单元格

4. 在单元格D2到D5中填入计算得到的k real(见方程式4). 例如在单元格

D2中填入: (C2/$B$8)*0.5*$B$7(0.5的出现是因为紫外比色皿中HNO3的浓度已被稀释到原始溶液浓度的一半). 由于单元格B7和B8是引用确定的地址,例如$B$7,所以单元格D2到D5的结果可以自动生成. 点击D2单元格,将鼠标箭头指向该单元格右下角并按住左键不放,向下拖动鼠标到D5单元格,这些单元格的结果将自动生成.

5. 用LOG10(D2)在D2中计算logk real,用先前描述的方法将鼠标箭头拖动到

D5单元格进行计算:

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中键入

G2/(G2+1). 点击A2,将鼠标箭头指向该单元格右下角并按住左键不放,向下拖动鼠标到A5单元格.

7. 最后一步为迪拜休克尔图的制作.

? 选择A2到B5,使其显亮. 点击插入,然后在菜单中点击图表:

? 在图表格式窗口第一步,选择图表形式:XY(分散型);然后选择图表亚形式,使其显亮.

? 点击进入第二步和第三步,分别填写图名X轴和Y轴的轴名.

? 点击下一步然后点击完成.

? 经过一些编辑后图表显示如下:

? 点击图表,在下拉菜单中点击趋势线,在图中添加最小二乘法拟合的直线.

选择曲线趋势/衰减模式,线性.

? 点击选择键,检查显示图表中的方程和显示图表中的R平方值:

? 最小二乘法直线符合方程:y 2.7835x+1.8686,R20.9809(满足要求,然而我确信5.310的学生会得到更好的R2值).