利用Excel处理物理化学实验的非线性关系数据的研究

王 琼 张 敏

（湖南科技学院 生命科学与化学工程系，湖南 永州 425100）

利用Excel处理物理化学实验的非线性关系数据的研究

王 琼 张 敏

（湖南科技学院 生命科学与化学工程系，湖南 永州 425100）

本文应用Excel 2003中的规划求解功能，对最大气泡法测定溶液的表面张力实验中表面张力和浓度的非线性关系数据进行了处理，并方便的求出了正丁醇饱和吸附量及其分子的横截面积。用实例说明了Excel软件处理非线性关系数据过程简单、快捷，并提高了效率和准确性。

表面张力；Excel；非线性；规划求解

在最大气泡法测定溶液的表面张力实验中，对表面张力和浓度的非线性关系数据的处理时，传统的实验数据处理方法是手工计算和作图，工作量繁杂，且主观性较大，结果重复性差，不甚准确。随着计算机的普及，目前可用的计算机处理非线性关系数据的方法有C语言编写程序[1]、Origin软件[2-3]、Excel软件[4-6]等，这些办法都取得了一定进展，但还存在需要编写复杂程序或数据处理不连贯等缺点。 Excel的规划求解是一个对数据进行因果分析的有效而简洁的工具，利用它可对表面张力和浓度关系中的待定参数进行非线性最小二乘拟合求解，并结合希斯科夫斯基经验公式和吉布斯吸附等温式可方便求出表面吸附量和分子横截面积的数值。研究发现利用该方法可快速、简便、准确且不用编程就能处理溶液表面张力测定的实验数据。

1 实验原理

表1 毛细管常数K的测定

表2 正丁醇溶液的浓度及表面张力

2 实验数据

实验温度为25℃，该温度下纯水的表面张力s水为0.07197N/m，溶液为正丁醇的水溶液。打开Excel2003，在新建工作表中录入溶剂水的一些参数，在E4单元格中输入公式=（B4+C4+D4）/3，在F4单元格中输入公式=A4*E4，处理结果如表1所示；在表2中输入正丁醇溶液的浓度和最大压力差数据，在E12单元格中输入公式=（B12+C12+D12）/3，自动获得对应浓度下最大压力差的平均值，在F12单元格中输入公式=A4*E12，自动获得对应浓度下的表面张力，最后用Excel的自动填充功能，分别在E13:E20区域和F13:F20区域分别填入各溶液下最大压力差的平均值和表面张力的测量值，如表2。

2.1 σ-c关系曲线拟合

在稀溶液中，希斯科夫斯基经验公式可以较好地表达σ-c关系，其经验公式为： σ= σ水（1 - b ln(1 +c/ k ))，式中σ水为水的表面张力，b、k为待定经验常数，通常b值约为0.3，而k值相当于是溶液表面张力比纯溶剂水低约12%时溶液的浓度数值。由于上式为非线性函数，要用非线性最小二乘曲线拟合方法。具体操作如下：

（1）在G6单元格输入b的初始值0.3，在G7单元格输入k的初始值0.04。

（2）用自动填充功能，在G12:G20区域填入各溶液表面张力的计算值。在G12单元格中输入希斯科夫斯基经验公式：=$A$4*(1-$G$6*LN(1+A12/$G$7))。

（3）用自动填充功能，在H12:H20区域填入各溶液表面张力的测量值与计算值的差值。在H12单元格中输入公式：=F12-G12。

（4）用自动填充功能，在I12:I20区域填入各溶液表面张力的测量值与计算值的差值的平方。在I12单元格中输入公式：= H12^2。在I21单元格得残差平方和：=SUM（I12:I20）。如图1所示。

图1 “规划求解”优化b、k值前的工作表

图2 规划求解对话框

接下来用“规划求解”对此工作表中的b、k数值进行优化处理[8]：

（1）打开工具菜单，选“规划求解”，出现“规划求解”对话框（图2）。

（2）用引用图标，在“设置目标单元格”编辑框中输入残差平方和位置$I$21。

（3）根据最小二乘法，残差平和应为极小，因此在“等于”的三个单选框中选“最小值”。

（4）用引用图标，在“可变单元格” 编辑框中输入b、k对应值所在单元格$G$6:$G$7。

（5）不要填“约束”栏。用“规划求解”对数据作最小二乘法拟合时，不要对解作任何约束。否则求得的方差总和不是“全局最小”，拟合系数可能有很大的误差。

（6）单击“求解”。“规划求解”优化b、k值后的工作表如图3所示。G6和G7分别为优化后的b、k值。现在残差平方和为1.18×10-5，较之优化前的5.18×10-4小1个数量级。比较F列测量值和G列理论值可知，拟合结果相当令人满意。

故拟合之后的希斯科夫斯基经验公式为：σ = 0.0719（71- 0.135*ln(1 +c /0.00891))。选中浓度、s测量和s计算数据，打开Excel工具栏中插入图表功能，选择“图表类型”中xy散点图（有关化学的数据一般选择该项），在接下来的“图表选项”对话框中输入x、y轴的名称，“图表位置”选中“作为其中的对象插入”即可，再根据需要直接双击图表，进行细微的修改。图4为用“规划求解”前后所得数据作的测量值与理论值的散点图，由图可知，拟合后的理论值与实验值相当吻合。拟合的曲线是单调的，符合表面张力随溶液浓度增加而单调下降的事实。整个曲线的凹凸性是一致的，曲率半径随浓度的增加也是单调变化的。

图3 “规划求解”优化b、k值后的工作表

图4 拟合前（左）、后（右）的s-c关系曲线

2.2 求c/Г-c关系方程

2.3 计算正丁醇分子的横截面积

图5 计算ds/dc值和c/s值的工作表......

图6 c/ ~cs 关系图

选中图5工作表中浓度和c/s两栏数据做“XY散点图”。在完成的图表上点击鼠标右键，选中“添加趋势线”，选中“类型”中的“线性（L）”，再打开“选项”对话框，在“显示公式（E）”和“显示R平方值（R）”前面的小方框中加“√”，完成对所作直线的拟合，其拟合的结果如图6所示，R2=1，直线斜率为253145，根据朗谬尔经验式正丁醇的饱和吸附量Γ∞为3.95×10-6mol/m2。正丁醇分子的横截面积由下式：A=1/(Γ∞.L)计算，式中L为Avogadro常数，即得正丁醇分子的横截面积A为2.38×10-19m2，与文献值[7]0.274-0.289nm2接近。

3 小结

利用Excel2003的非线性拟合与作图功能，可以不借助计算机语言编程，只需要将温度、温度对应的溶剂的表面张力、溶液浓度以及实验测定的最大压力差值重新输人，并做规划求解，不需要重新输人公式和作图，就可以将希斯科夫基经验公式中待定参数的拟合求解、溶液表面吸附量的计算、s一c关系曲线和c/s一c关系曲线的绘制，都能一气呵成。在物理化学实验中，除了“最大泡压法测表面张力”实验外，其他实验的线性和非线性数据都可参照上述过程来处理。

[1]王旭阳,向明礼,张锋.溶液表面张力检测处理软件的设计[J], 计算机与应用化学,2005,22(11):1047-1150.

[2]谢祖芳.用origin处理溶液表面张力实验数据[J].中国现代教育装备, 2007,3:50-51.

[3]段德良,信文瑜,屈庆,曹秋娥.应用 Origin 软件处理乙醇水溶液表面张力实验数据[J].昆明学院学报,2011,33(6):103-105.

[4]徐景士,黄德超,刘华卿,何国放,汪海燕.应用 Excel2000处理物理化学实验数据[J].江西师范大学学报（自然科学版）,2005,29(2):139-141.

[5]任占冬,朱玉婵，张开诚.利用Excel和LabView软件处理表面张力的实验数据[J]. 计算机与应用化学,2008,25(11):1424-1428.

[6]吴世彪,徐玲,王颖,陈艳.最大气泡法测溶液表面张力实验数据处理的改良[J].中国现代教育装备, 2010,5:65-66

[7]复旦大学.物理化学实验[M].第二版.北京：高等教育出版社,1993:458.

[8]徐抗成. Excel数值方法及其在化学中的应用[M]. 兰州: 兰州大学出版社,2000:112-123.

(责任编校：何俊华)

O646

A

1673-2219（2012）04-0065-03

2012－03－12

湖南科技学院教学改革课题（No. XKYJ2010024）

王琼(1975－)，女，湖南祁阳人, 高级实验师, 主要从事物理化学实验的教学及其电化学研究。