用Excel 处理稳定常数实验数据

第17卷　第3期大学化学2002年6月计算机与化学

用Excel处理稳定常数实验数据

徐抗成

(兰州大学化学化工学院　兰州730000)

摘要　在Excel中应用图表绘制、线性回归分析、

“规划求解”最小二乘法非线性拟合等数值方法对平衡常数实验数据进行处理。

在化学实验课程中,稳定常数测定的数据处理较为复杂。通常作法是用计算机编程代替繁重的计算[1,2],但作图仍为手工。Excel电子表格软件既能进行复杂的计算,又能方便地作图[3],可以大大提高实验教学效率。

1　“规划求解”最小二乘法非线性拟合

用分光光度法测定甲基红的电离平衡常数,得吸光度A与氢离子浓度之间的关系[1]:

A=x+y K/[H +]

1+K/[H+]

(1) K是平衡常数,x和y是与体系性质有关的常数。K、x、y待定。

文献[1]根据最小二乘法原理,由(1)式得到线性方程组。然后设计两个Basic程序,分别用以计算方程组的系数和解方程组,这些均可在Excel工作表上操作[3]。Excel的自动填充及相对引用具有程序语言的循环语句功效,能同样方便的计算方程组的系数。Excel内置矩阵求逆和矩阵相乘函数,可以直接解线性方程组。

若用Excel的“规划求解”程序包,则能由(1)式一步得到K、x和y。“规划求解”是一种优化程序,它通过改变一个或数个“可变单元格”的数值,使得“目标单元格”的数值达到最小或最大值。图1是用“规划求解”作最小二乘法非线

性拟合工作表。氢离子浓度[H+](A5～A11)和

吸光度A(B5～B11)为实验数据,取自文献[1]。

A2、B2、C2单元格为待定常数x、y、K位

置,需要设置初值。在C5～C11区域按(1)式计

算A计算。在D5～D11区域计算吸光度实验值

与计算值之差的平方,用Excel“自动求和”在D3

单元格得到残差平方和,即∑(A-A计算)2。

用最小二乘法求出使残差平方和最小的待定常

数。

打开“工具”菜单,选“规划求解”指令。设置

53

目标单元格为D3,使其等于最小值。在Excel 中“规划求解”是一迭代过程,它在运行过程中以一定方式改变A2、B2、C2单元格的值,使得残差平方和(D3单元格)的值最小。 图1给出“规划求解”后的结果:x =0.584,y =0.0370,K =1.06×10-5。与文献[1]的结

果(x =0.581,y =0.0354,K =1.04×10-5。

)一致。用Excel 的优点是免去化(1)式为线性方程组、然后解方程这类纯数学问题,保持了原问题的化学原理性质,学生容易接受。2　半 n 法

中级无机实验[2]中的p H 法测定稳定常数是常用方法。该法设备简单、操作方便、数据精确、应用广泛。实验过程通常为滴定,例如将碱溶液滴加到含有金属离子、配体和酸的溶液中,每滴加一定量的标准碱溶液后,搅拌使溶液达到平衡,记录相应的p H 值。根据初始条件、滴加的体积、测量的p H 值可以计算在此条件下游离配体的浓度[L ]和生成函数 n 。 用Excel 计算整个滴定过程的[L ]2 n 。拖曳单元格右下角的填充柄,Excel 可根据活动单元格的公式,按变化了的体积和p H 值自动计算整个滴定过程的[L ]2 n 。 有了一组[L ]2 n 数据后,以 n 为纵坐标,pL 为横坐标作图。图中 n =0.5,1.5,…所对应的pL 即为lg K 1,lg K 2,…。K 1,K 2,…为配合物逐级稳定常数。以Excel 的X Y 散点图方式绘制生成曲线,改变坐标轴的刻度及范围即可读出逐级稳定常数。

图2和图3是根据“p H 法测定乙二胺的逐级稳定常数”学生实验数据制作的生成曲线。从图3可以方便地读出K 1=1010.50=3.14×1010( n =0.5)。用同样的方法可得K 2=3.34×108( n = 1.5)。

3　Rossetti 图解法

乙二胺与铜有二级配合反应。根据生成函数定义, n 与累积稳定常数β1和β2有如下关系:

n (1- n )[L ]=β1+(2- n )[L ]1- n

β2(2)以 n (1- n

)[L ]对(2- n )[L ]1- n 作图得一直线,其截距为β1,斜率为β2。在Excel 中用拖曳填充柄的方法,可得到以不同[L ]2 n 计算的 n (1- n )[L ]和(2- n )[L ]1- n 数值;再用所得数据,作X Y 散63

点图(图4)。对数据点添加趋势线,并且选取“显示公式”和“显示R平方值”复选项。图4的相关系数平方(R2)为0.998,表明这组学生实验数据良好。

可以用Excel的SLOPE和IN TERCEPT函数直接得到

直线的斜率和截距,也可以用L IN EST函数或“数据分析”程

序包中的“回归”程序对数据作线性回归分析。后两种方法除

得到斜率和截距外,还给出回归统计。图5是用L IN EST线

性回归同一组数据的结果。A1、B1和A3单元格分别给出斜

率、截距和R2值,均与添加趋势线相同,因为它们利用同一最

小二乘法原理。A2和B2单元格分别为斜率和截距的标准偏差,B3单元格为y值(即 n

)的标准偏差。根据这些回归统计,学生可以对实验数据作自我评估。

(1- n)[L]

将两组[L]2 n代入(2)式,可得一个二元一次方程组,解出β1和β2;也可用“规划求解”作最小二乘法非线性拟合得到逐级稳定常数(参阅第1节)。4种方法计算结果见表1。由于两个稳定常数差别不大,用半 n法得到的结果偏离其他方法得到的结果。

表1　用4种方法计算乙二胺合铜的逐级稳定常数K1和K2

半 n法Rossetti法解二元一次方程“规划求解”法

K1 3.14×10107.76×10108.35×10108.17×1010

K2 3.43×108 1.39×108 1.17×108 1.34×108

4　结论

图解法、线性回归分析、非线性拟合等为实验数据处理的最基本方法,是实验课程重要内容之一。这些均能在Excel上操作。由于Microsoft Excel极为普及,学生掌握了Excel数值方法后,可以随时应用到学习和工作中,从而更有效地处理数据,更准确地表达研究结果。

参　考　文　献

1　王郁文,曾锡瑞,许招会.大学化学,1999,14(3):49

2　王伯康.新编中级无机化学实验.南京:南京大学出版社.1998:34

3　徐抗成.Excel数值方法及其在化学中的应用.兰州:兰州大学出版社,2000

73