使用Office Excel求解定量化學(xué)問(wèn)題

徐少飛

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

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

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

1 單變量求解功能介紹

Excel中的單變量求解功能通過(guò)迭代法，可對(duì)有實(shí)根的任意一元方程進(jìn)行求解。下面以求解方程x3– 5x2 + 6x =1為例進(jìn)行說(shuō)明。

1.1 設(shè)置迭代計(jì)算

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

1.2 輸入方程和變量

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

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

1.3 使用單變量求解功能進(jìn)行計(jì)算

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

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

1.4 其他實(shí)根的求解

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

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

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

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

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

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

（2.6）

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

（2.7）

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

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

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

2.2 列表并進(jìn)行單變量求解

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

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

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

可見(jiàn)在1.0mol/L的（NH4）2CO3溶液中，92%的CO2－3與46%的NH+4發(fā)生了水解，導(dǎo)致CO2－3這個(gè)“原初”離子的濃度大大降低。各物種濃度從大到小的順序?yàn)椋篬NH+4]>[NH3·H2O]>[HCO3－]>[CO2－3]>[H2CO3]>[OH－]>[H+]。主要存在的物種濃度之比[NH+4]︰[HCO3－]︰[NH3·H2O]≈1︰1︰1，因此（NH4）2CO3溶液相當(dāng)于NH4HCO3與NH3·H2O約1︰1的混合溶液。若向NH4HCO3溶液中通入等物質(zhì)的量的氨氣，相當(dāng)于得到NH4HCO3和NH3·H2O的混合溶液，兩者發(fā)生反應(yīng)生成（NH4）2CO3的程度較低。

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

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

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

3 酸堿滴定曲線的繪制

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

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

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

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

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

（3.1）

3.2 列表并輸入方程

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

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

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

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

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

3.3 啟用宏

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

3.3.1 添加開(kāi)發(fā)工具選項(xiàng)卡

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

3.3.2 添加按鈕控件

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

單擊工作表空白處，在彈出窗口中點(diǎn)擊“新建”按鈕，進(jìn)入VBA編輯窗口。見(jiàn)圖5C。

3.3.3 輸入宏代碼

在VBA編輯窗口中光標(biāo)的默認(rèn)處輸入下列代碼（代碼中’后的文字為對(duì)本行代碼的注釋說(shuō)明，不必輸入）。

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單元格為目標(biāo)單元格，目標(biāo)值為0，Ci單元格為可變單元格，進(jìn)行單變量求解。注意代碼中“GoalSeek”和“Goal”之間有一空格。

Range（"C" + Mid（Str（i+1）， 2））= Range（"C" + Mid（Str（i）， 2））’令C（i+1）單元格（下一行的[H+]）等于Ci單元格（當(dāng)前行中已計(jì)算出的[H+]）。由于滴定過(guò)程中相鄰兩行的[H+]一般相差不大，此操作可以使每一行[H+]的初始值盡量接近最終計(jì)算結(jié)果。

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

3.3.4 使用按鈕控件計(jì)算

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

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

3.4 繪圖

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

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

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

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

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

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

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

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

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

3.6 其他應(yīng)用

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

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

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

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

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

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

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

計(jì)算結(jié)果與文獻(xiàn)一致。

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

參考文獻(xiàn)

[ ]吳文中.探索計(jì)算機(jī)精確計(jì)算在高中化學(xué)研究中的應(yīng)用——基于" Wolfram Mathematica" 平臺(tái)[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