徐少飛

摘要基于化學(xué)平衡和分析化學(xué)的理論，借助常用辦公軟件Microsoft Office Excel中的單變量求解和宏功能，對電解質(zhì)溶液的pH及各物種濃度的計算、酸堿滴定曲線的繪制、可逆反應(yīng)中反應(yīng)物起始濃度對其平衡轉(zhuǎn)化率的影響等復(fù)雜定量化學(xué)問題進行求解，以輔助教學(xué)。

關(guān)鍵詞Excel單變量求解宏物種濃度滴定曲線轉(zhuǎn)化率

在化學(xué)平衡、分析化學(xué)中常會遇到需要通過定量計算來解決的問題。人工計算費時費力，可借助計算機手段完成[ ][ ]。筆者通過研究和嘗試，總結(jié)出一套使用常規(guī)辦公軟件Microsoft Office Excel中的單變量求解和宏功能，對復(fù)雜體系進行計算的方法，解決了化學(xué)教學(xué)中遇到的一些無法通過定性方法解決的問題，以期為一線教師提供一些新的思路。

1 單變量求解功能介紹

Excel中的單變量求解功能通過迭代法，可對有實根的任意一元方程進行求解。下面以求解方程x3– 5x2 + 6x =1為例進行說明。

1.1 設(shè)置迭代計算

新建一個Microsoft Office Excel工作簿，依次點擊文件-選項-公式。在計算選項中“啟用迭代計算”前的方框中打勾。最多迭代次數(shù)可設(shè)置不超過32767次的迭代次數(shù)，通常設(shè)置300次足以完成一般的計算。最大誤差設(shè)置為1E-10（Excel中科學(xué)記數(shù)法的表示方式，如1.6×10-10記作1.6E-10）可滿足一般的計算精度。最大誤差的值越小，計算結(jié)果越精確，設(shè)置為0時沒有誤差。但由于計算機求解精度有限，往往無法在有限的迭代過程中達到誤差為0，故不建議將最大誤差設(shè)置為0或太小的數(shù)值。注意每一個需要啟用迭代計算的Excel工作簿中均需進行該項設(shè)置。見圖1A。

1.2 輸入方程和變量

將待求方程的非零項全部整理到方程左側(cè)得：x3– 5x2 + 6x – 1 = 0。按照圖1B在工作表的相應(yīng)單元格中輸入相應(yīng)內(nèi)容。A2中以函數(shù)形式輸入上述方程的左側(cè)項，其中以用“B2”代替x。函數(shù)形式要以等號“=”開始，抑揚符“^”表示指數(shù)運算，如3^2代表32，星號“*”代表乘號，斜杠“/”代表除號。

B2稱為可變單元格，在稱為目標單元格的A2中作為方程中的未知數(shù)x被調(diào)用。設(shè)法調(diào)整B2中的數(shù)值，使A2所得的值等于待求方程右側(cè)的目標值——0（或與目標值之差小于已設(shè)定的最大誤差），此時B2中的數(shù)值即是所求方程的一個實根（或其高精度近似值），這就是單變量求解功能的基本原理。

1.3 使用單變量求解功能進行計算

單擊選中A2，依次點擊菜單中的數(shù)據(jù)-模擬分析-單變量求解。在跳出窗口中，目標單元格默認為當前已選中的A2；將目標值設(shè)為0，即待求方程右側(cè)的數(shù)值；可變單元格設(shè)為B2。見圖1C。

點擊確定，可看到A2與B2中的數(shù)字快速變動，直至A2變成1.61648E-13（小于設(shè)定的最大誤差1E-10），同時B2變?yōu)?.554958后停止變動。1.554958即為方程x3– 5x2 + 6x =1的一個實根的高精度近似值。見圖1D。

1.4 其他實根的求解

事實上方程x3 - 5x2 + 6x =1有三個實根，要想得到另外兩個實根，可以修改B2中的初始值，使其大致接近某一個根。如在B2中輸入0，重復(fù)上述操作，求解結(jié)果為0.198062；在B2中輸入3，求解結(jié)果為3.24698。一般的化學(xué)計算中，往往只有一個根是合理的，可以先大致估計所求根，將估計的值作為初始值后進行迭代計算。

2 計算電解質(zhì)溶液的pH及各物種濃度

對于電解質(zhì)溶液，分析化學(xué)中通常需要針對不同情況采取不同的近似處理的方法進行求解[ ]。下面以計算1.0mol/L （NH4）2CO3溶液的pH及各物種濃度為例，介紹一種通用方案。

2.1 利用分布系數(shù)對（NH4）2CO3溶液進行處理

（NH4）2CO3溶液中除[H+]、[OH-]外的各物種濃度均可通過分布系數(shù)[ ]表示：

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

（2.6）

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

（2.7）

列出（NH4）2CO3溶液的質(zhì)子守恒式（對不易列出質(zhì)子守恒式的體系，也可列出電荷守恒式），并把所有非零項整理至等式左側(cè)：

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

將前述以[H+]表示的各物種濃度帶入上式，即可得到僅含一個未知數(shù)[H+]的高次方程，列表后用單變量求解功能即可一次性計算出H+及其他所有物種的濃度。

2.2 列表并進行單變量求解

按照圖2輸入其中前6行的相應(yīng)內(nèi)容。B1～B5中輸入的是A1～A5中的各項所對應(yīng)的數(shù)據(jù)，分別為H2CO3的兩級電離常數(shù)、NH+4、H2O的電離常數(shù)、溶液初始濃度。這樣只需修改相應(yīng)數(shù)據(jù)就可以用于計算其他類似組成的溶液。J6、K6分別代表NH+4、CO2－3的水解度。

由于（NH4）2CO3溶液略顯堿性，可將[H+]的初始值設(shè)為1.0E-9。為簡化A7單元格中方程項對應(yīng)的函數(shù)，可先根據(jù)公式2.1～2.7中列出的各物種濃度公式在相應(yīng)物種濃度所在單元格的下方單元格中輸入函數(shù)，之后再在A7中引用這些單元格的編號即可。以下為各單元格中需要輸入的數(shù)據(jù)或函數(shù)：

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

打開單變量求解，設(shè)置目標單元格為A7，目標值為0，可變單元格為B7，開始求解。[H+]求解完成時，其余各物種濃度、pH、NH+4、CO2－3的水解度也自動一并求出。見圖2第7行。

可見在1.0mol/L的（NH4）2CO3溶液中，92%的CO2－3與46%的NH+4發(fā)生了水解，導(dǎo)致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溶液中通入等物質(zhì)的量的氨氣，相當于得到NH4HCO3和NH3·H2O的混合溶液，兩者發(fā)生反應(yīng)生成（NH4）2CO3的程度較低。

修改B1、B2中的常數(shù)及B5中的濃度，再次使用單變量求解即可計算其他二元弱酸銨鹽的情況，見圖3。

由于H2SO3酸性較強，（NH4）2SO3溶液水解程度較小，溶質(zhì)主要以NH+4和SO2－3存在。

類似的，可設(shè)計出相應(yīng)的表格以計算一定組成的電解質(zhì)溶液的pH及各物種濃度。

3 酸堿滴定曲線的繪制

酸堿滴定曲線是分析化學(xué)中的重要內(nèi)容，表示了隨著滴定劑的加入，混合溶液pH的變化情況。滴定突躍的存在為使用指示劑判斷滴定終點提供了理論基礎(chǔ)。用單變量求解功能可以計算出加入一定體積的滴定劑時混合溶液的pH，再使用圖表功能進行繪圖即可得到滴定曲線。要想獲得足夠平滑的曲線，就需要計算足夠密集的數(shù)據(jù)。但如果對每一組數(shù)據(jù)逐一進行單變量求解，過于繁瑣且重復(fù)。此時可調(diào)用Microsoft Office的宏功能，對大量重復(fù)性工作進行批量操作，即可快速計算出所需數(shù)據(jù)，進而繪制出曲線。

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

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

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

設(shè)HA的電離常數(shù)為Ka?，初始體積為20.00mL，初始濃度為c mol/L。加入VmL同濃度的NaOH溶液時， ， ， ，帶入上式并將所有非零項均移至方程左側(cè)得：

（3.1）

3.2 列表并輸入方程

按照圖4輸入相應(yīng)內(nèi)容。B1、D1中輸入的是A1、C1中各項所對數(shù)據(jù)，分別為HA的電離常數(shù)、初始濃度。注意在后面的計算中需要通過絕對引用調(diào)用這些數(shù)據(jù)。如要通過絕對引用調(diào)用B1單元格中的數(shù)據(jù)時，需輸入$B$1。使用了絕對引用的數(shù)據(jù)在自動填充時不會發(fā)生行或列的改變。

V（NaOH）列中使用自動填充功能輸入以0.10為遞增量，自0.00至40.00的數(shù)值。在滴定終點附近的19.50～20.50之間，遞增量設(shè)為0.01，以提高滴定突躍附近曲線的順滑度。

以$B$1代替Ka，$D$1代替c，A3代替V，C3代替[H+]，1E-14代替Kw，在B3中輸入公式3.1的左側(cè)項：=$D$1*A3/（20+A3）+C3-20*$D$1/（20+A3）*$B$1/（C3+$B$1）-1E-14/C3。使用自動填充功能將其下方單元格全部填充。

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

D3中輸入：=-log10（C3），并自動填充其下方單元格。

3.3 啟用宏

宏是計算機中的一種批量處理方式，在Microsoft Office中，可自行錄制或使用VBA語言編程，從而大大簡化需要批量處理的重復(fù)性操作。已編譯好的宏可以通過快捷鍵或按鈕控件來調(diào)用，這里介紹第二種方式。

3.3.1 添加開發(fā)工具選項卡

依次點擊文件-選項-自定義功能區(qū)，在右側(cè)的主選項卡中勾選開發(fā)工具后確定。見圖5A。

3.3.2 添加按鈕控件

依次點擊開發(fā)工具-插入-表單控件-按鈕（窗體控件）。見圖5B。

單擊工作表空白處，在彈出窗口中點擊“新建”按鈕，進入VBA編輯窗口。見圖5C。

3.3.3 輸入宏代碼

在VBA編輯窗口中光標的默認處輸入下列代碼（代碼中’后的文字為對本行代碼的注釋說明，不必輸入）。

Dim i As Integer ’定義i為整數(shù)。

For i = 3 To 493’在下面的循環(huán)（到Next之前）中，令i依次取從3到493的整數(shù)。

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+]的初始值盡量接近最終計算結(jié)果。

Next’進行下一次循環(huán)。

3.3.4 使用按鈕控件計算

關(guān)閉整個VBA界面窗口，回到Excel工作簿。在此前單擊處出現(xiàn)一個按鈕，右擊可編輯其上的文字。單擊按鈕，稍等片刻即可完成全部計算。見圖6。

注意：包含宏的Excel文件在保存時需要另存為.xlsm（啟用宏的工作簿）文件，否則宏將不被保存。在新的設(shè)備上打開包含宏的Excel文件時需要先點擊啟用宏。

3.4 繪圖

按住Ctrl同時拖選A2～A493、D2～D493，依次點擊插入-圖表-散點圖-帶平滑線的散點圖，得到圖像。雙擊圖像可對坐標軸邊界、坐標軸標題等進行設(shè)置。見圖7。

固定c = 0.10mol/L，修改B1的數(shù)值，可得到不同Ka的一元酸的滴定曲線（將Ka設(shè)置為大于1.0×105的數(shù)值，可得到強酸的滴定曲線）；固定Ka = 1.0×10-5，修改D1的數(shù)值，可得到不同濃度的一元弱酸的滴定曲線。見圖8。

可以看到，隨著Ka或c的減小，突躍范圍都會越來越小，只有cKa> 10-8的一元酸被同濃度的堿滴定時才會有明顯的突躍，可以被準確滴定。以上結(jié)果與分析化學(xué)的結(jié)論一致[ ]。

3.5 NaOH溶液滴定二元弱酸滴定曲線的繪制

將計算表格中的方程替換為二元弱酸體系的電荷守恒式（注意需要準備兩級電離常數(shù)的單元格，并在方程中通過絕對引用調(diào)用）：

[Na+] + [H+] = [HA－] + 2[A2－] + [OH－]

將V（NaOH）的取值增加至60.00mL，并根據(jù)最大行數(shù)修改相應(yīng)VBA中i的最大值，點擊計算按鈕即可得到二元弱酸的滴定曲線。固定c = 0.10mol/L，修改兩級電離常數(shù)，繪制出相應(yīng)的滴定曲線。見圖9。

可以看到，類似于NaOH滴定一元弱酸，若cKa2> 10-8，無論Ka1大小如何，第二級均有明顯的突躍，可以被準確滴定（圖8A）；而Ka1/Ka2> 105時，第一級才可被準確滴定（圖8B），否則第二級會對第一級造成干擾。若二元酸的第一級完全電離，則在c/Ka2> 105時，第一級才可被準確滴定。見圖10。以上結(jié)果與分析化學(xué)的結(jié)論一致[ ]。

H2SO4的第一級完全電離，Ka2 = 1.2×10-2，顯然c/Ka2< 105，故僅第二級可被滴定。還可在列表中加入HSO－4、SO2－4的分布系數(shù)，一并繪入圖像。見圖11。

3.6 其他應(yīng)用

利用按鈕控件還可以設(shè)計制作弱酸、弱酸鈉鹽等溶液的計算器。見圖12。運用2.1中的方法列出指定溶液關(guān)于[H+]的方程，按照2.2中的方式列表。輸入相應(yīng)的Ka、c，按下“計算”按鈕（按鈕對應(yīng)的宏代碼中只需輸入相應(yīng)的單變量求解代碼）即可計算出溶液的pH、各物種濃度及其排序。排序可使用RANK函數(shù)，如圖12A中的G5單元格中顯示的是HA溶液中[H+]的排序，即G4中的值在G4～J4單元格中的排序，可在G5中輸入="No."&RANK（G4，$G4：$J4），自動填充H5～J5即可。

圖12A中的H3A帶入的是H3PO4的Ka，經(jīng)計算可知，0.10mol/L H3PO4溶液中物種濃度順序為：[H3PO4] > [H+] > [H2PO－4]> [HPO2－4] > [OH－] > [PO3－4]。

圖12B中的H2A中帶入的是H2CO3的Ka，經(jīng)計算可知，0.10mol/L NaHCO3溶液中物種濃度順序為：[Na+] >[HCO－3] > [H?2CO3] >[CO2－3] > [OH－] > [H+]。

4 可逆反應(yīng)中反應(yīng)物的起始濃度對其自身轉(zhuǎn)化率的影響

部分參考資料認為，增加某種反應(yīng)物的起始濃度會導(dǎo)致其自身轉(zhuǎn)化率下降，然而已有文獻[ ]指出未必如此。以合成氨反應(yīng)為例，其他條件不變時，隨著氫氣起始濃度的增加，其自身轉(zhuǎn)化率會自0%開始先增大至某一極大值后，逐漸減小至趨于0%；而隨著氮氣起始濃度的增加，其自身轉(zhuǎn)化率會自100%減小至趨于0%。下面進行計算驗證。

設(shè)于1.0 L恒容密閉容器中按照n（N2） = 1.0 mol，n（H2） = a mol投料。一定溫度下（K=1.0）達到平衡時，氮氣轉(zhuǎn)化x mol，則平衡時c（N2） = （1.0 – x） mol/L，c（H2） = （a – 3x） mol/L，c（NH-3） = 2x mol/L。列出平衡常數(shù)表達式：

列表，令a取一系列數(shù)值（可根據(jù)圖像斜率調(diào)整疏密度），使用宏功能批量進行單變量求解對應(yīng)的x。進而得到對應(yīng)的氫氣轉(zhuǎn)化率（H2） = 3xa，繪制（H2）～n（H2）圖像。對于改變N2起始濃度，用同樣的方法可繪制（N2）～n（N2）圖像。見圖13。

計算結(jié)果與文獻一致。

綜上所述，無論是水溶液還是氣相平衡，將體系轉(zhuǎn)化成僅含一個未知數(shù)的方程，便可用單變量求解功能進行計算。遇到需要求解多組數(shù)據(jù)的體系，還可以使用宏功能進行批量操作。制作完成的Excel文件可作為現(xiàn)成工具使用，只需修改Ka、c等少量參數(shù)即可用于其他類似場景。定量計算的結(jié)果往往更直觀，更具有說服力，得到的數(shù)據(jù)或圖像可以更清晰地展現(xiàn)化學(xué)體系或過程，對日常教學(xué)也大有裨益。

參考文獻

[ ]吳文中.探索計算機精確計算在高中化學(xué)研究中的應(yīng)用——基于" Wolfram Mathematica" 平臺[J].化學(xué)教學(xué)， ?2018， （5）：92-97.

[ ]吳文中， 徐俊龍， 申燕.利用函數(shù)關(guān)系作圖解讀中和滴定原理[J].化學(xué)教與學(xué)， 2015， （6）：67-70.

[ ]武漢大學(xué).分析化學(xué)第六版[M]. 北京：高等教育出版社，2016.120-129.

[ ]武漢大學(xué).分析化學(xué)第六版[M]. 北京：高等教育出版社，2016.116-119.

[ ]武漢大學(xué).分析化學(xué)第六版[M]. 北京：高等教育出版社，2016.113.

[ ]武漢大學(xué).分析化學(xué)第六版[M]. 北京：高等教育出版社，2016.148.

[ ]武漢大學(xué).分析化學(xué)第六版[M]. 北京：高等教育出版社，2016.149.

[ ] 周惠忠， 吳新建. 恒溫恒容平衡體系中的反應(yīng)物轉(zhuǎn)化率隨濃度變化規(guī)律的探討[J]. 化學(xué)教育（中英文）， 2017， v.38（15）：70-73.

Use Office Excel to solve quantitative chemical problems

Xu Shao-fei*

（Shanxi Experimental Secondary School， Taiyuan 030006， China）

AbstractWith the aim of assisting teaching， using the goal seek command and macro function in Microsoft Office Excel， based on the theory of chemical equilibrium and analytical chemistry， this paper accurately solved the following complex quantitative chemistry problems. Calculate the concentration of each species in anelectrolyte solution and pH of the solution， draw acid-base titration curve， and analyse the effect of the initial concentration of reactants in a reversible reaction on the equilibrium conversion rate.

Keywords chemical equilibrium; analysis chemistry; Excel;goal seek; macro