使用Office Excel求解定量化学问题

摘要基于化学平衡和分析化学的理论，借助常用办公软件Microsoft Office Excel中的单变量求解和宏功能，对电解质溶液的pH及各物种浓度的计算、酸碱滴定曲线的绘制、可逆反应中反应物起始浓度对其平衡转化率的影响等复杂定量化学问题进行求解，以辅助教学。

关键词Excel单变量求解宏物种浓度滴定曲线转化率

在化学平衡、分析化学中常会遇到需要通过定量计算来解决的问题。人工计算费时费力，可借助计算机手段完成[ ][ ]。笔者通过研究和尝试，总结出一套使用常规办公软件Microsoft Office Excel中的单变量求解和宏功能，对复杂体系进行计算的方法，解决了化学教学中遇到的一些无法通过定性方法解决的问题，以期为一线教师提供一些新的思路。

1 单变量求解功能介绍

Excel中的单变量求解功能通过迭代法，可对有实根的任意一元方程进行求解。下面以求解方程x3– 5x2 + 6x =1为例进行说明。

1.1 设置迭代计算

新建一个Microsoft Office Excel工作簿，依次点击文件-选项-公式。在计算选项中“启用迭代计算”前的方框中打勾。最多迭代次数可设置不超过32767次的迭代次数，通常设置300次足以完成一般的计算。最大误差设置为1E-10（Excel中科学记数法的表示方式，如1.6×10-10记作1.6E-10）可满足一般的计算精度。最大误差的值越小，计算结果越精确，设置为0时没有误差。但由于计算机求解精度有限，往往无法在有限的迭代过程中达到误差为0，故不建议将最大误差设置为0或太小的数值。注意每一个需要启用迭代计算的Excel工作簿中均需进行该项设置。见图1A。

1.2 输入方程和变量

将待求方程的非零项全部整理到方程左侧得：x3– 5x2 + 6x – 1 = 0。按照图1B在工作表的相应单元格中输入相应内容。A2中以函数形式输入上述方程的左侧项，其中以用“B2”代替x。函数形式要以等号“=”开始，抑扬符“^”表示指数运算，如3^2代表32，星号“*”代表乘号，斜杠“/”代表除号。

B2称为可变单元格，在称为目标单元格的A2中作为方程中的未知数x被调用。设法调整B2中的数值，使A2所得的值等于待求方程右侧的目标值——0（或与目标值之差小于已设定的最大误差），此时B2中的数值即是所求方程的一个实根（或其高精度近似值），这就是单变量求解功能的基本原理。

1.3 使用单变量求解功能进行计算

单击选中A2，依次点击菜单中的数据-模拟分析-单变量求解。在跳出窗口中，目标单元格默认为当前已选中的A2；将目标值设为0，即待求方程右侧的数值；可变单元格设为B2。见图1C。

点击确定，可看到A2与B2中的数字快速变动，直至A2变成1.61648E-13（小于设定的最大误差1E-10），同时B2变为1.554958后停止变动。1.554958即为方程x3– 5x2 + 6x =1的一个实根的高精度近似值。见图1D。

1.4 其他实根的求解

事实上方程x3 - 5x2 + 6x =1有三个实根，要想得到另外两个实根，可以修改B2中的初始值，使其大致接近某一个根。如在B2中输入0，重复上述操作，求解结果为0.198062；在B2中输入3，求解结果为3.24698。一般的化学计算中，往往只有一个根是合理的，可以先大致估计所求根，将估计的值作为初始值后进行迭代计算。

2 计算电解质溶液的pH及各物种浓度

对于电解质溶液，分析化学中通常需要针对不同情况采取不同的近似处理的方法进行求解[ ]。下面以计算1.0mol/L （NH4）2CO3溶液的pH及各物种浓度为例，介绍一种通用方案。

2.1 利用分布系数对（NH4）2CO3溶液进行处理

（NH4）2CO3溶液中除[H+]、[OH-]外的各物种浓度均可通过分布系数[ ]表示：

注意[NH+4]、[NH3·H2O]计算式中的Ka为NH+4的Ka[ ]：

（2.6）

将[OH-]用Kw和[H+]表示：

（2.7）

列出（NH4）2CO3溶液的质子守恒式（对不易列出质子守恒式的体系，也可列出电荷守恒式），并把所有非零项整理至等式左侧：

[NH3·H2O] + [OH－] - 2[H2CO3] - [HCO3－] - [H+] = 0 （2.8）

将前述以[H+]表示的各物种浓度带入上式，即可得到仅含一个未知数[H+]的高次方程，列表后用单变量求解功能即可一次性计算出H+及其他所有物种的浓度。

2.2 列表并进行单变量求解

按照图2输入其中前6行的相应内容。B1～B5中输入的是A1～A5中的各项所对应的数据，分别为H2CO3的两级电离常数、NH+4、H2O的电离常数、溶液初始浓度。这样只需修改相应数据就可以用于计算其他类似组成的溶液。J6、K6分别代表NH+4、CO2－3的水解度。

由于（NH4）2CO3溶液略显碱性，可将[H+]的初始值设为1.0E-9。为简化A7单元格中方程项对应的函数，可先根据公式2.1～2.7中列出的各物种浓度公式在相应物种浓度所在单元格的下方单元格中输入函数，之后再在A7中引用这些单元格的编号即可。以下为各单元格中需要输入的数据或函数：

B7：1.0E-9

C7：=B4/B7

D7：=B5*B7^2/（B7^2+B1*B7+B1*B2）

E7：=B5*B1*B7/（B7^2+B1*B7+B1*B2）

F7：=B5*B1*B2/（B7^2+B1*B7+B1*B2）

G7：=2*B5*B7/（B7+B3）

H7：=2*B5*B3/（B7+B3）

I7：=-log10（B7）

J7：=2-G7/2/B5

K7：=1-F7/B5

A7：=H7+C7-2*D7-E7-B7

打开单变量求解，设置目标单元格为A7，目标值为0，可变单元格为B7，开始求解。[H+]求解完成时，其余各物种浓度、pH、NH+4、CO2－3的水解度也自动一并求出。见图2第7行。

可见在1.0mol/L的（NH4）2CO3溶液中，92%的CO2－3与46%的NH+4发生了水解，导致CO2－3这个“原初”离子的浓度大大降低。各物种浓度从大到小的顺序为：[NH+4]>[NH3·H2O]>[HCO3－]>[CO2－3]>[H2CO3]>[OH－]>[H+]。主要存在的物种浓度之比[NH+4]︰[HCO3－]︰[NH3·H2O]≈1︰1︰1，因此（NH4）2CO3溶液相当于NH4HCO3与NH3·H2O约1︰1的混合溶液。若向NH4HCO3溶液中通入等物质的量的氨气，相当于得到NH4HCO3和NH3·H2O的混合溶液，两者发生反应生成（NH4）2CO3的程度较低。

修改B1、B2中的常数及B5中的浓度，再次使用单变量求解即可计算其他二元弱酸铵盐的情况，见图3。

由于H2SO3酸性较强，（NH4）2SO3溶液水解程度较小，溶质主要以NH+4和SO2－3存在。

类似的，可设计出相应的表格以计算一定组成的电解质溶液的pH及各物种浓度。

3 酸碱滴定曲线的绘制

酸碱滴定曲线是分析化学中的重要内容，表示了随着滴定剂的加入，混合溶液pH的变化情况。滴定突跃的存在为使用指示剂判断滴定终点提供了理论基础。用单变量求解功能可以计算出加入一定体积的滴定剂时混合溶液的pH，再使用图表功能进行绘图即可得到滴定曲线。要想获得足够平滑的曲线，就需要计算足够密集的数据。但如果对每一组数据逐一进行单变量求解，过于繁琐且重复。此时可调用Microsoft Office的宏功能，对大量重复性工作进行批量操作，即可快速计算出所需数据，进而绘制出曲线。

3.1 NaOH溶液滴定一元弱酸体系的分析

向一元弱酸HA中滴加NaOH溶液，混合溶液中的电荷守恒式为：

[Na+] + [H+] = [A－] + [OH－]

设HA的电离常数为Ka¬，初始体积为20.00mL，初始浓度为c mol/L。加入VmL同浓度的NaOH溶液时， ， ， ，带入上式并将所有非零项均移至方程左侧得：

（3.1）

3.2 列表并输入方程

按照图4输入相应内容。B1、D1中输入的是A1、C1中各项所对数据，分别为HA的电离常数、初始浓度。注意在后面的计算中需要通过绝对引用调用这些数据。如要通过绝对引用调用B1单元格中的数据时，需输入$B$1。使用了绝对引用的数据在自动填充时不会发生行或列的改变。

V（NaOH）列中使用自动填充功能输入以0.10为递增量，自0.00至40.00的数值。在滴定终点附近的19.50～20.50之间，递增量设为0.01，以提高滴定突跃附近曲线的顺滑度。

以$B$1代替Ka，$D$1代替c，A3代替V，C3代替[H+]，1E-14代替Kw，在B3中输入公式3.1的左侧项：=$D$1*A3/（20+A3）+C3-20*$D$1/（20+A3）*$B$1/（C3+$B$1）-1E-14/C3。使用自动填充功能将其下方单元格全部填充。

C3中输入待求[H+]的初始值1.0E-3（根据Ka=1.0×10-5，c = 0.10mol/L估算）。

D3中输入：=-log10（C3），并自动填充其下方单元格。

3.3 启用宏

宏是计算机中的一种批量处理方式，在Microsoft Office中，可自行录制或使用VBA语言编程，从而大大简化需要批量处理的重复性操作。已编译好的宏可以通过快捷键或按钮控件来调用，这里介绍第二种方式。

3.3.1 添加开发工具选项卡

依次点击文件-选项-自定义功能区，在右侧的主选项卡中勾选开发工具后确定。见图5A。

3.3.2 添加按钮控件

依次点击开发工具-插入-表单控件-按钮（窗体控件）。见图5B。

单击工作表空白处，在弹出窗口中点击“新建”按钮，进入VBA编辑窗口。见图5C。

3.3.3 输入宏代码

在VBA编辑窗口中光标的默认处输入下列代码（代码中’后的文字为对本行代码的注释说明，不必输入）。

Dim i As Integer ’定义i为整数。

For i = 3 To 493’在下面的循环（到Next之前）中，令i依次取从3到493的整数。

Range（"B" + Mid（Str（i）， 2））.GoalSeek Goal：=0， ChangingCell：=Range（"C" + Mid（Str（i）， 2）） ’令Bi单元格为目标单元格，目标值为0，Ci单元格为可变单元格，进行单变量求解。注意代码中“GoalSeek”和“Goal”之间有一空格。

Range（"C" + Mid（Str（i+1）， 2））= Range（"C" + Mid（Str（i）， 2））’令C（i+1）单元格（下一行的[H+]）等于Ci单元格（当前行中已计算出的[H+]）。由于滴定过程中相邻两行的[H+]一般相差不大，此操作可以使每一行[H+]的初始值尽量接近最终计算结果。