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

《用微软Excel求解程序进行非线性曲线拟合》由会员分享，可在线阅读，更多相关《用微软Excel求解程序进行非线性曲线拟合（25页珍藏版）》请在装配图网上搜索。

1、警示：这些材料所描述的实验具有潜在的危险性，因此需要高标准的安全训练特殊的设备和装置并在合适的人员指导下才能进行.对于履行这样的安全程序和措施，你负有全部的责任和义务 并独自承担其风险.对于所提供的任何材料的内容或其执行情况，MIT将不负任何责任和义务不承担任何风险.法律提示用微软Excel求解程序进行非线性曲线拟合1计算 k obs k real 及迪拜休克尔曲线由医学博士 Gheorghiu编写I. 动力学:kobs（k观察）和k rea l（k 真实 ）的计算1. 在文件菜单中点击 新建，然后点击普通工作簿：如果你有什么意见请 与Mircea Gheorghiu博士联系.P MW*HE*

2、 F-S V* 9t*2. 在文件菜单中，保存此工作簿.我喜欢将工作簿命名为动力学_MG （MG 是我名字的缩写）并将它保存在个人文件夹内| 谢 Mk*ioN： E M；d Uu*.42 22a r *丘 I加科Fjyn姑D ”.,ta叮 M+W.汕 4叶errt+ogmnJL1叶CW43I牛5eD7JE iS Jll*s-_;W 血B，常orsht bed -togto111212厲也：1 irr ： * 1C? 3 *. LI昭也.訖十 国.！*( * 卜.片，-3. 你的工作簿中需要五个工作表.四个用于动力学数据第五个用来进行迪拜 休克尔计算这五个表要附在书面报告或口头报告中如果工作表不

3、够，点击插入然后在菜单中点击 工作表O Micnnoft Excel - Kinetics_MQ.xli19站电亀Oh癌为四个动力学工作表命名例如我选择 动力学A(0.02M NaNO 3),动力学B(0.05M NaNO 3),动力学 C(0.1M NaNO 3)和动力学 D(0.2M NaNO 3).1J14151617181920212223n *H 4 M / KinetoEi / KineticC *netk$D /Ready4. 现在开始将实验数据添加到四个工作表中.以动力学B工作表为例：在A栏中输入时间(以秒为单位),B栏中输入相应时间 下的实验得到的吸光 率(420nm).表格

4、中再增加两栏一栏为计算得到的吸光率(由方程式3计算得到)另外一栏为 实验吸光率(B栏)与计算吸光率(C栏)差的平方.1time (seconds)experimental ahsorhances (420 nrn)Calculated absorbances (equation 3)DiffA2 (exp *calc)A2l2 |00.507233r co0.4896241200.473351800.4587762400.446087r 3oo04334683600.42337942004129104800.40375115400J9626126000.38828136600380491472

5、00.3738115780036777168400J622717900P 0356618r 9600.352131910200.347292010800.343122111400,3392522 12QQ 0J35577 I,LK M I 卜 1制1KineticsB/ KinetiC / KneticsD / Sheets /5. 提请注意，以下是在我发的讲义中介绍的二级反应积分动力学方程变量的意义和 我的讲义以及实验手册上的是一致的：A。- Af1 -exp(- Cfkobst)A o以与实验数据拟合的吸光率表示的积分二级反应动力学方程Ao：初始吸光率A: t时刻的吸光率Af：当所有H2A

6、sc反应完全时的吸光率6. 我们刚刚完成使用Microsoft Excel求解程序的准备工作.每一个动力学 工作表中需要增加包含两类信息的单元格,H2和H3分别输入固定值A0 和epsilon. H5 (Af值)和H6(kbs)栏的内容则是可变的.首先将估计值输入 到Af和kobs栏中在最小化过 程之后,求解程序 分别在可变单元H5和H6 中返回回归系数求解程序 不提供系 数的标准偏 差.7. 为了能自动代入动力学方 程,含A。, epsilo n,Af, kobs的单元格必须给定名称（这是Excel所要求的）.?对于Ao,在H2单元格中输入 B2.? 将epsilon值输入H3单元格，你得到

7、的epsilon值由朗伯 比尔公式计算 得到（实验第一天记录）.由我的结果根据最小二乘法计算得到 斜率& 1020.?在H5单元格输入Af的最佳估计值，即0.25（为什么？）.?将kobs的估计值输入H6单元格中我的估计值是5.Fie 刖it Vie*j 1砒誠 Ffljrmjt Joels Rat* Window HdpAri韵II10 BKffJ- U臺手言宦雷，如羽章彊田H6Z= 5ABcDEFG|1limefsecuridsexperimpnUE(4 2D nm)Calcula 馆 R h$orbance$ (equation 3)|(exp-calc)2200.50723A0-0.5

8、07233460U 48962epsilon=10201200J7335180(U5077M=0,25!|624Q1,44608kobs-3730(10.43346(nconih)(tom?AD- 即賀il 口旷I D.507劄 1iu?n为了能自动插入方程式3, A0, epsilon , Af, kbs必须给定名称以命名A。为例，首先 点击H2单元格,然后点击插入，名称，定义：|D澎Qsaxtn z斤訂知n a :广 0,團4 A世Edo理 -letoB / U爭吞乞宙$ %tm羽事寧田挣占H2TCGUWWAcrihret011 Ef J GHDiff-27(20Af- kikbs-1d0

9、Tininn以下窗口将会弹出:#.50723.4|4 生區成4.4|一.3!.3|.3|.3:33|3.35213I 00理3 io?o10.255请注意并核对Ao值的正确位置，在这个例子中为（根据Excel的语法）：动力学 B!$H$2,表示在动力学工作表B的H2单元格点击添加键,选择OK .继续命名 H3: H5单元格然后,我们将矢量A2:A22命名为t.首先选择A2:A22 ,使其亮显, 然后点击插入,命名，定义并将其在工作簿中的名称改为t （检查表示一栏以确保 输入名称正确）.定义名称 窗口如下timeI ;(seconds)experimentalahsorbiices20 *诃)D

10、600.507230.489G2120180240300Calculatedabsorhances(叭nation 可0時P|(exp-calt* 2().50723102QT w n 亘 133604204fl054060066004oi0J?o3ioji15 is iz1BW7H06400+3960epsilon-n.3472?055Insert Frmt TotMs 阳也 Wrtdow Helpi rr | 页 $ % ,拥盘y庐田.4ABCDE |_ FG丄H I8. 求解程序 通过两步优化曲线：?第一步,计算吸光率的原始”值.?第二步为优化步骤，计算得到的原始吸光率经过优化 以符合实

11、验值.A.初始步骤:在C2单元格 中输入=Af/(1-(A0-Af)/A0)*EXP(-kbs*t*Af/epsilon) . H2单元格中填入t0的吸光率计算值.由公式3可得它等于Ao.盟二|=闵做14理冋出四工界巴斗曲汨乂1/即口对)ABcDEFGH1time (seconds)experimental423 nm)Caicuhied b&orbdftces Jpuualinri ILDifF2 (exp-cal21D0+TO7230.50_23A0-D.5O723360epsilon=1200.4733180045877Af-240DJ460Skobs=5300D.H346A1111.4

12、7317为了填写C3到C22单元格，点击C2单元格将鼠标箭头指向该单元格右下角并 按住左键不放，向下拖动鼠标到C22单元格,松开左键.从C2到C22的单元格都 会按照C2的计算方式算出(初始)吸光率：A .8CD1time (seconds)experimental absorbances (420 nm)Calculated absorbances jequdtion 3)Diff*2 (exp calc)A2200.507230,507233r co0.48962I 0.472 5 |4r 1200473304446651800.458770.4214062400.44608j 04018

13、713000.433460385288r 3600.423370371049420041290.35873104800.40375034800115400.396260.338590.3302912P 6000.3882813r 6600.3804903229414720037381031640157800.36777031055168400.362270.3053117900035680.30059W960035213629634mlHbl KineticsA Kinetfc$B/ KinettcsC / KhnetksD / Sheets /B.优化步骤：非线性曲线拟合步骤9. 在单元格D

14、2中键入=（B2-C22 .回车.10点击单元格D2,将鼠标拖到D22,按计算吸光率的方式操作.11.在单元格D23对D2到D22进行求和（点击工图标）.sum 1 于| * J | =SUM(p2:D22)AI BCI.DE73000.43346038528 r 2 3217EO383600.42337037104；2.7381 E-03|9.4200.4129035873 匚2.9343E-03rJ10480040375034800!3 1080M3ILL11540039626033859；3 3257E-03f12600038B28033029 匚3 3624E-03；136600.38

15、049032294!3 3119E-03i147200J73810316403.2963E-03157800.36777031055 匚3 274tE*D3!16840036227030531；3 2447E-03!179000.35680.50059 匚3 1592E-03J169600J52130.29634 t3.1123E-03;19102GQ347290J9250；2010800343120.28902 匚2 9273M3J2111400339250.28585 f2.8612E*03ff2212000.335570.28298 L2 766tE*03；23C f1然后回车.12点击

16、单元格D23点击工具,在菜单中点击 求解程序(Solver)11* Microsoft Excel - KineticsMG.xk亘File Edit View Insert FormatAnalTools Data Window Helpf? bdeling -D23=SlA873000.43346a360042337942004129104000.403751154003962612 6000.3882813 6600.30049147200373811517800.367771fi 840036327rlShare Workbook. r.ProtedtfonAdd-Ins.Custom

17、ize. h.Options.EE-03E433E-03E-03E-03E-030322940.316400.310550.305313.3119E-033.2963E-O33.2741 E033 2447F413* H / CU 也|開冷j洛屈K 匸弹出求解参数窗口，目标单元格是D23 .LVJHMMA吁/JUO8(947Di10IBOt11MO(B40t廿r .lM0r(9M17D10B0D34J12fi ?：T“iSLW(D2 L-J|5-aiwf mMMMleriUE3J划环瞅eJL_n_lE初心悄也*忸广泌eof|og IB在通过改变 中键入单元格H5和H6(即$H$5和$H$6).

18、13equdbon 3) 1DjWFOOCOOEO030472752M69&Wepsilon-1Q200.444668 2W6-040 421401 3%5tG3Ah0250.4018*1 如了131如山5 M m 且MhJIK 17Tt7Fs I .mm2在求解参数窗口中点击选择键,将最大时间调整到1200秒（动力学运行时间）.选 择0K .S*limeCtlMlhc)8C一 Lo ZUE eKpentnentdlCakuUtedt)HT2么卜盅迥小萨dfiAlfeCrk ftn-f-M! 1创=SUM(D2 D27)AW亠 1kuQD60pnmuEKEMnia.aoocci210W密9*n

19、c：|0 OOniA0-0.50721io?bC-Mkakrt5144204BUiio厂血.、“审95臥士 Estrrriteibe拧T护gmf广f#求解参数窗口重新出 现，先点击最小值 键然后点击 求解键:#a0 4733Il 2?3r-0l1.39&5E4I33f4tb(Target 匚科:Tn； 180这时可以打印部分报告：包括结果 灵敏度和极限.比如结果报告应为以下格 式：重复步骤4到12以得到动力学A 动力学C和动力学D的结果.必要时请更新定义 名称栏的指代信息.II.迪拜休克尔方程在 动力学中”讲义中（参见其中变量意义）,迪拜休克尔方 程式被定义为:|12 |1 2lOgk rea

20、l =logk0 + 1.02* Z1* Z2= logk0 + 1.02* 3*右(6)1 + 11 + I其中kreal由方程得到:realobsH + Kal用工作表5（重命名为迪拜 休克尔）来计算，并以lOgk real （y轴）对l.5/（l 5+1）（X轴） 作图完成后的迪拜 休克尔工作表如下：1ABCDEFGk t /?*1|toqkralkohskfeal网/KONaNO3 |stock uilulionmncNiNO rval tte HNO3.恂电“ diluted to hdlQSquare raol f lhe Ionic atMfi|lh P1K?0.1D9121/?

21、Jl/U14/J1IMVH40JO15OD.1/25fl.HZ?ZJM?IB4J5ntrifli)Qd WiiQ.1BW2430,11*1?n.ow*D2MJn ?(I7?,5310001H.100200 1fl50D1Z40S9101112HhOJU IV%AnttirbiE dciri.K1-IlKercpp*- W-13 ft* 214怡1b172 100 150.202,761,87 7J.90 fl/iWS1019* 电卜卜i. i.F ,. - 宀-/ n.g k。屮一h削2 602 552.502452 4Q2.352 301. 建一个5行7列的表,标题的顺序和内容如 X图所示切

22、记 在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 .&fRj Mli 曲I DM # Ql氛 Mi ov * 八 x aii B 41，陵呵.吋 Firg Int Fjrr；r 应占 0*0 尸胆切 httoABCEiV 1 7 ft11.7 *1)lugkieAlkobkbl(kobs-rlH.piK-

23、1)NdHO3 恫取d isnlifiiunC4IIC)HdNDJ idj (Mintjl fht HKO1+ then kittd teitiolsil ihe iFjnlr dlieviytfiFW22JMJ343jn当non 1:fi3* To - |j B 1 U寸蔘F團牢X ,铀*泸仟_ k C5 i pkinetirdilHlg在单元格B7中填入工作表 信息HNO3的摩尔浓度.将抗坏血酸的酸度系 数(心=6.76*10 -5)填入B8单元格17ABCDEFG12kglcrttlfculnkin nl0tab(M*lyKaf)Hatt03 悴qdt solutian wnQHN03

24、al j“M恤*沽詈 HMOMbl：，r釧T ID弋默hIO2?D32M2|41H34川圖7HHOJD.OO%aAvctrHc acM,K41-4IS 證山B / U 固 X t坤援&Filr 工3. 在单元格D2到D5中填入计算得到 的kreal （见方程式4）.例如在单元格D2中填入：（C2/$B$8）*0.5*$B$7（0.5 的出现是因为紫外比色皿 中HNO 3 的浓度已被稀释到原始溶液浓度 的一半）.由于单元格B7和B8是引用确 定的地址,例如$B$7,所以单元格D2到D5的结果可以自动生成点击0I昭&国力宁帕絆6if二ruNoiIni|kl dlkraal网 KaiHN032 DT

25、D3.3Dtutiflii tone)NdMOJ rrdl (10 ihhi) (he HN03. ihtn ilklnld ioSquare root of ih IonicXlrengrttD2单元格,将鼠标箭头指向该单元格右下角并按住左键不放,向下拖动鼠 标到D5单元格,这些单元格的结果将自动生成jcld,K1-4. 用LOG10（D2）在D2中计算logki,用先前描述的方法将鼠标箭头拖动到 D5单元格进行计算：圧 K起啡切H L acqI tlifttMnL t kdG uhda口 qh旷 jt 电(bc* e a tiij tg jaiw g?g,时 EHEki Fvp,brf I

26、叶p 密心;刘 HtlZ五 U / = U =里阳维協烈事讴田飞B6时 -”丨门匸1【“【曲1AB C0EFG1-12hijkrekobskiealub*iH.ppK*1)NjH03 (dock cilulianEonc|仿Mding th?HN03. (hen dihjtf?d tn haltSqiiaie rool of (he lon4c vlriEnqlh KIA22.172.D70U7J013 ,5 17.0122.4 J37932KL3OZ.531 IBOfl34 U067HN03 (mniJilM-Ojoq%0瓦托E D595. 余下的计算是 关于|.5/（|.5+1）,即在A列

27、中计算x轴变量的值.?首先填入NaN03的原始溶液摩尔浓度.我在实验中使用E2到E5单元格 的数据.? 其次，在F2到F5单元格中计算NaN03 +HNO 3的真实摩尔浓度例如在 F2单元格中 计算 （E2+$B$7）*0.5 .乘以0.5是因为当以3ml+3ml混合 时（参见实验和讲义）紫外比色皿中的溶液为初始溶液浓度 的一半.记住 对于单价阴离子和阳离子,摩尔浓度在数值上等于离子强度.l 里f ie St 住 hFgut aofo 2d(M He誓ii / u r tii s %* t- ffirt2n*i 2+1)laakTBal? 17777777koh*2J070?iiivW3krc

28、dl(kDbrgHLAKdlA9.3UnunXlULkconc|n.i.iJ-m: ojioZI (Mt03NflKOl falIdiklitiq lheHK03 thendiluind in(UQlSflo iraSquAm roal ol (lie liinic 9friH|1h01790UHMI9199#HHOii in cilmi 蛆Ascmhic jLid.KjI-6 f&l (K9#9#? 第三步,在G2到G5单元格中计算F2到F5的平方根例如在G2中键入 SQRT（F2）,按回车键.? 第四步 在A2到A5单元格中计算I0.5/（10.5+1）.例如在A2中键入9#G2/(G2+

29、1) 点击A2,将鼠标箭头指向该单元格右下角并按住左键不放, 向下拖动鼠标到A5单元格.flutr AtA 二JAJ R BDEFG1lofkrBfllknlnkifial fkmbi 溝*PKKt)MaH03soEutiDn etinONniNCi iR4ii 门詛圖“町th it HNUJ. ditn diluted tn badFont of (lie Mink vtienqlh k1Z22fl. 1041j.U2.070117.01DjO?Q40.01500.1ZM3tJ4722J71S4.75D.05000 11796D.1724ejMrg3J532b9J0D.tGO?090C.2M

30、352.53J.BtIBMt.40DJW130.1050DJJ4C67HMO (TMjIdJ 帥-auo%号AmrHc *cMiKjl-JID11t?皿nw13艸 MWtM i6. 最后一步为迪拜休克尔图的制作.?选择A2到B5,使其显亮点击插入,然后在菜单中点击 图表fij E奪 已少 比岬IrwfrtQ川瘴 坐心旳 为卩尽叶5；& t LTtgn.kiEInb(H.p：i Ka1) tU.lfljl(M盹t 一BMU1 2J3Eyi* 中 Myperik,., CMHC147111mmJ4i+wHN03umi%& g toAMzorbk ciitTAt-& 76EO5?在图表格式窗口第一步

31、,选择图表形式：XY（分散型）;然后选择图表亚形式, 使其显亮.tJ t* V* ；* Tk FgwwtE- KF T B U : 也2ij R-1TmJ_亶血sCtAjnfiISmtnnitMaot才-_.ADiiK *dd”Kil-LI IMF*.fdSum 匚:*i歼101112nudm、HEJOtaHi iMtanii 5 Bap I ital I ClhM 1冲亀21#?点击进入第二步和第三步，分别填写图名 X轴和Y轴的轴名.Lh fht IISF Z Q 團 X X 用事事 * * iSj俯衣I4rfiihn ihMjNOJ#?点击下一步然后点击完成.?经过一些编辑后图表显示如下:

32、ACDEfG2n MPii vnur mDH?1Mimw0 1?753GW1UJ5M5Q*D LTJ 17?60.1Mun2IHJU .1QU7IIJMZMJ0.2U72 WM)R341 40.?003DlffW0 V4DHNtn |wuUn*i Astaibic tnl.Kai11121315EFG041WD.174 AWojotnTStme.io2QJDM92M3O.2W30.14HO0.3240HHO37AM：irWt aciri,Kt-d123D.IW九o*s4-?点击图表，在下拉菜单中点击 趋势线,在图中添加最小二乘法拟合的直 线22选择曲线趋势/衰减模式,线性.0100150 20

33、0 25r0 5/?K5+1)#Plot Aj轴jJA0 I fil fl82J7Eoa?(M0.ID1213u1518190HAfciDrK00tJI020020-n j tiH】JF -V*i gJ1 x- 1192319#?点击选择键,检查显示图表中的方程 和显示图表中的R平方值:Plat Aw Ei AO.ltFHB717D1474)1AdnlCftcrs |riantPhuLnur (Smul. |AbcdrHR*ecasiII 1/7bII7 fl(10111J1314佔17ia0 201f0 5+1)19#19#?最小二乘法直线符合方程:y 2.7835X+1.8686, R2

34、0.9809（满足要求,然 而我确信5.310的学生会得到更好的R2值）.19#Bc0efGh? 1/程IEMJ41fillipII IZTi1HLJ5n iBOiUlMWrimMlXTft)曲 JOII.1HVJ41.40IIJWJ0.1IM24# 10 15HJrO5M) 0258.为了计算斜率(1.02*Z1*Z2),截距k(|=o时的斜率)和R2,首先在迪拜 克尔工作表中添加H10到H12单元格.? 在 Slope=后的单元格(B10)后键入 SLOPE(B2:B5,A2:A5).? 在 Intercept=后的单元格(B11)后键入 INTERCEPT(B2:B5,A2:A5) ?在ko后的单元格(B11)后键入10711.? 在 RA2=后的单元格(B13)键入 RSQ(B2:B5,A2:A5).n J v 应寧那JJ1IV DI0 J0J1UDJ?UI0.1?turnt HVjn64wim1阳DJ3435I Hiinn1Ml i liiiIl MM石OOMHhu 1r Iltlvil-L fcttM Iiti皿f i mn MW25