文档介绍：第17卷第3期大学化学2002年6月计算机与化学用Excel处理稳定常数实验数据徐抗成(兰州大学化学化工学院兰州730000 摘要在Excel中应用图表绘制、线性回归分析、“规划求解”最小二乘法非线性拟合等数值方法对平衡常数实验数据进行处理。在化学实验课程中,稳定常数测定的数据处理较为复杂。通常作法是用计算机编程代替繁重的计算[1,2],但作图仍为手工。Excel电子表格软件既能进行复杂的计算,又能方便地作图[3],可以大大提高实验教学效率。1 “规划求解”最小二乘法非线性拟合用分光光度法测定***红的电离平衡常数,得吸光度A与氢离子浓度之间的关系[1]: A=+1+K/[H+](1K是平衡常数,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=,y=,K=×10-5。与文献[1]的结果(x=,y=,K=×10-5。一致。用Excel的优点是免去化(1式为线性方程组、然后解方程这类纯数学问题,保持了原问题的化学原理性质,学生容易接受。2 半n法中级无机实验[2]中的pH法测定稳定常数是常用方法。该法设备简单、操作方便、数据精确、应用广泛。实验过程通常为滴定,例如将碱溶液滴加到含有金属离子、配体和酸的溶液中,每滴加一定量的标准碱溶液后,搅拌使溶液达到平衡,记录相应的pH值。根据初始条件、滴加的体积、测量的pH值可以计算在此条件下游离配体的浓度[L]和生成函数n。用Excel计算整个滴定过程的[L]2n。拖曳单元格右下角的填充柄,Excel可根据活动单元格的公式,按变化了的体积和pH值自动计算整个滴定过程的[L]2n。有了一组[L]2n数据后,以n为纵坐标,pL为横坐标作图。图中n=,,…所对应的pL即为lgK1,lgK2,…。K1,K2,…为配合物逐级稳定常数。以Excel的XY散点图方式绘制生成曲线,改变坐标轴的刻度及范围即可读出逐级稳定常数。图2和图3是根据“pH法测