最小二乘法處理環(huán)境監(jiān)測數(shù)據(jù)及Excel建模探索

劉作云

(湖南環(huán)境生物職業(yè)技術(shù)學(xué)院 園林學(xué)院，湖南 衡陽421005)

環(huán)境監(jiān)測工作的最終目的是要獲得準(zhǔn)確度高的監(jiān)測數(shù)據(jù)，為客觀評價環(huán)境質(zhì)量優(yōu)劣、環(huán)境管理與執(zhí)法、環(huán)境科學(xué)研究等提供基礎(chǔ)數(shù)據(jù).

在實際工作中，即使國標(biāo)的分析方法附有計算公式或模型，但數(shù)據(jù)處理仍然是一項十分繁瑣，又容易出現(xiàn)錯誤的工作，并最終影響整個監(jiān)測工作的質(zhì)量.尤其選擇依靠標(biāo)準(zhǔn)曲線分析樣品中某污染指標(biāo)含量時，數(shù)據(jù)處理的過程更為復(fù)雜. 雖然有部分環(huán)保工作者通過總結(jié)工作經(jīng)驗，提出了一些數(shù)據(jù)處理的方法，如單文坡等提出了采用Excel 和Origin對監(jiān)測數(shù)據(jù)進行處理，不僅發(fā)揮了Excel 強大的數(shù)據(jù)處理功能，又充分利用了Origin 強大的繪圖分析優(yōu)勢［1］;吳海斌提出Excel 的函數(shù)和VBA 編程功能相結(jié)合，可以完成監(jiān)測數(shù)據(jù)處理的自動化［2］;賈威等提出了通過標(biāo)準(zhǔn)曲線的回歸分析可實現(xiàn)簡便、快速處理數(shù)據(jù)［3］;吳惠璇等則提倡使用Excel 的描述統(tǒng)計功能對自動監(jiān)測數(shù)據(jù)進行分析處理［4］.

專業(yè)的軟件和程序雖然為工作提供了便利，提高了工作效率，但數(shù)據(jù)的處理終歸屬于數(shù)學(xué)的范疇，對于標(biāo)準(zhǔn)曲線法測某一污染指標(biāo)時，首要的工作就是計算標(biāo)準(zhǔn)曲線的回歸方程，再依據(jù)回歸方程的截距、斜率以及樣品的吸光度等求得污染物的濃度，而計算標(biāo)準(zhǔn)曲線的回歸方程的理論基礎(chǔ)就是最小二乘法.該文以納氏試劑比色法水質(zhì)銨的測定為例，分析最小二乘法數(shù)據(jù)處理的過程.

1 最小二乘法方法基礎(chǔ)

對于兩個變量X(x1、x2……xn)、Y(y1、y2……yn)，要找出其相關(guān)關(guān)系，就必須計算其回歸方程，即Y=aX+b.按照最小二乘法原理，應(yīng)先對兩組變量進行初步處?理，求出中間參數(shù)然后將這些參數(shù)代入如下計算公式:

式中:n 代表樣本容量;a 代表回歸方程斜率;b 代表回歸方程截距.

通過以上計算，即可求得兩個變量的回歸方程Y=aX+b.

2 最小二乘法在環(huán)境監(jiān)測數(shù)據(jù)處理中的應(yīng)用

環(huán)境監(jiān)測工作中，采用最小二乘法處理數(shù)據(jù)的過程實際比較簡單，只需要將與標(biāo)準(zhǔn)曲線在同等條件下測得的已扣除了空白值的樣品吸光度代入回歸方程，就能得出樣品中某一污染指標(biāo)的濃度.以下就以納氏試劑比色法測水中銨的濃度為例說明.

［例1］已知銨標(biāo)準(zhǔn)溶液濃度為10 μg/mL，空白試驗吸光度為0.015，水樣吸光度為1.048，比色管容量為50 mL，標(biāo)準(zhǔn)曲線數(shù)據(jù)如表1，試用最小二乘法求該水樣中銨的濃度.

表1 納氏試劑比色法水質(zhì)銨的監(jiān)測數(shù)據(jù)表Tab.1 The data tables of monitoring the content of ammonium in water quality with Nessler’s reagent colorimetric method

解:

步驟一:求標(biāo)準(zhǔn)曲線回歸方程

去年烏有先生與子虛道人出谷查看天下之勢，黃昏里經(jīng)過潼關(guān)，只覺得殘陽如血，雄關(guān)霧列，草木交纏在慘慘兵氣之中。兩老修道學(xué)易，知道此地是天下關(guān)鍵，十年內(nèi)外，必然有事。果然十年之后，潼關(guān)之上，靈寶西原，哥舒翰的大軍陷入崔乾祐重圍，數(shù)十萬的大軍出潼關(guān)，渡黃河，被叛軍的虎狼之師趕進高原中的深塹，在對岸雷鳴一樣的鼓聲里哭爹喊娘地被刀劍切割成殘肢，被木石沖撞成肉醬，被油火燒成焦炭，馬血與人血，骨頭與腦髓，泥漿與草木，攪拌在一起。

首先應(yīng)對表1 數(shù)據(jù)進行初步處理，將標(biāo)準(zhǔn)曲線吸光度扣除空白實驗吸光度.

表2 校正后監(jiān)測數(shù)據(jù)表Tab.2 The monitoring data table after correction

然后計算中間參數(shù)

再將LXX、L(Y－Y0)(Y－Y0)、LX(Y－Y0)數(shù)值代入P、a、b值計算公式.

經(jīng)計算:

則標(biāo)準(zhǔn)曲線回歸方程為:Y = 0.029X +0.009

步驟二:水樣吸光度已知，將其扣除空白試驗值后代入回歸方程.

即:1.048 －0.015 =0.029X+0.009，可求得水樣X 值為35.361 mL，而銨標(biāo)準(zhǔn)溶液濃度為10 μg/mL，則水樣中銨的含量為353.61 μg，再除以水樣用量50 mL，即可計算出水樣銨的濃度為7.072 mg/L.

3 Excel 建模分析

實際工作中，環(huán)境監(jiān)測人員每天需要分析大量的樣品，如果每一個樣品均要采用最小二乘法計算，工作量十分大. 為了簡化計算過程，避免因計算產(chǎn)生的錯誤，可將最小二乘法的計算過程采用Excel編程建立模型.以下仍以上述例題為例，闡述Excel編程建模過程.

步驟二:LXX、L(Y－Y0)(Y－Y0)、LX(Y－Y0)、P、a、b 的計算過程使用Excel 編程.

步驟三:將水樣吸光度、銨標(biāo)準(zhǔn)溶液等數(shù)據(jù)輸入Excel 表格，并將相關(guān)計算過程編程，如圖1所示.

圖1 水樣銨濃度計算過程編程示意圖Fig.1 The sketch of calculation process programming of ammonium concentration in the samples

步驟四:經(jīng)過上述三個步驟，納氏試劑比色法水質(zhì)銨的測定數(shù)據(jù)處理Excel 模板已經(jīng)形成. 在實際工作中，納氏試劑比色法水質(zhì)銨的測定均是按照GB7479—87 規(guī)定的步驟進行的，銨標(biāo)準(zhǔn)溶液用量、比色管數(shù)量與容量是固定的，而標(biāo)準(zhǔn)曲線吸光度、空白試驗吸光度、銨標(biāo)準(zhǔn)溶液濃度、水樣吸光度則因試驗條件或操作人員不同而不同. 因此，環(huán)境監(jiān)測工作人員可以通過以上方式建立Excel 數(shù)據(jù)處理模型，將每次測得的相關(guān)數(shù)據(jù)輸入到Excel 數(shù)據(jù)處理模板的相應(yīng)位置，即可快速求得水樣銨的濃度.以例2 為例闡述Excel 數(shù)據(jù)處理模板的應(yīng)用.

【例2】已知銨標(biāo)準(zhǔn)溶液濃度為9.98 μg/mL，空白試驗吸光度為0.013，水樣吸光度為1.123，比色管容量為50 mL，標(biāo)準(zhǔn)曲線數(shù)據(jù)如表3，試用最小二乘法求該水樣中銨的濃度.

表3 納氏試劑比色法水質(zhì)銨的實測數(shù)據(jù)表Tab.3 The data tables of the content of actual ammonium in water quality with Nessler’s reagent colorimetric method

解:將標(biāo)準(zhǔn)曲線吸光度、銨標(biāo)準(zhǔn)溶液濃度、空白試驗吸光度、水樣吸光度輸入Excel 數(shù)據(jù)處理模板，即可直接讀取該水樣銨的濃度為7.414mg/L，如圖2 所示.

通過建立Excel 數(shù)據(jù)處理模板，使得整個實驗的數(shù)據(jù)處理過程大大簡化，節(jié)約了大量的數(shù)據(jù)處理時間.同時，由于數(shù)據(jù)處理程序的簡化，能大大提高數(shù)據(jù)處理的準(zhǔn)確率，避免加減乘除過程中的失誤.此外，建模還有利于數(shù)據(jù)處理的統(tǒng)一化，提高了數(shù)據(jù)間的可比性.

圖2 Excel 數(shù)據(jù)處理模板應(yīng)用實例圖Fig.2 The instance diagram of the applications of Excel data processing template

4 Excel 函數(shù)在環(huán)境監(jiān)測數(shù)據(jù)處理中的應(yīng)用

Excel 作為強大的數(shù)據(jù)處理軟件，包含有大量的數(shù)學(xué)函數(shù).從標(biāo)準(zhǔn)曲線的回歸方程計算角度出發(fā)，可利用的函數(shù)有slope(斜率)和intercept(截距)，通過利用這兩個函數(shù)，可以實現(xiàn)進一步簡化Excel 數(shù)據(jù)處理模板.以例1為例闡述slope(斜率)和intercept(截距)函數(shù)的應(yīng)用.

步驟一:將表1 數(shù)據(jù)輸入Excel 表格，并編程計算斜率和截距.步驟二:后續(xù)步驟與Excel 數(shù)據(jù)處理模板相同，如圖3 所示.

圖3 Excel 函數(shù)數(shù)據(jù)處理模板示意圖Fig.3 The sketch of data processing template by Excel function

通過使用Excel 的函數(shù)功能，納氏試劑比色法水質(zhì)銨的測定的數(shù)據(jù)處理過程得到進一步簡化.

5 結(jié) 語

數(shù)據(jù)處理是獲得監(jiān)測結(jié)果的必要步驟，如何快速的獲取準(zhǔn)確的監(jiān)測結(jié)果是環(huán)境監(jiān)測工作人員一直在探索的問題.最小二乘法作為回歸方程計算的理論基礎(chǔ)，是每一個環(huán)境監(jiān)測人員必須掌握的基本方法.而為了提升工作效率，提高監(jiān)測結(jié)果準(zhǔn)確度，緩解監(jiān)測人員工作壓力，建立Excel 數(shù)據(jù)處理模板是行之有效的方法之一，應(yīng)當(dāng)在環(huán)境監(jiān)測工作中得到充分的應(yīng)用.

［1］單文坡，盧海霞，鄭 輝.Excel 和Origin 在環(huán)境監(jiān)測數(shù)據(jù)處理中的應(yīng)用［J］.石家莊職業(yè)技術(shù)學(xué)院學(xué)報，2008，20(2):58-60，67.SHAN Wen-po，LU Hai-xia，ZHENG Hui. Applications of Excel and Origin in environmental monitoring data［J］.Journal of Shijiazhuang Vocational Technology Institute，2008，20(2):58-60，67

［2］吳海斌.水環(huán)境監(jiān)測數(shù)據(jù)處理的自動化實現(xiàn)［J］.人民珠江，2007，(4):88-90.WU Hai-bin. Automatic realization of water environment monitoring data processing［J］. PEARL RIVER，2007，(4):88-90.

［3］賈 威，趙寶華.環(huán)境監(jiān)測結(jié)果的數(shù)學(xué)處理［J］.科技創(chuàng)新導(dǎo)報，2008，(35):101，103.JIA Wei，ZHAO Bao-hua. Mathematical treatment of environmental monitoring results［J］. Science and Technology Innovation Herald，2008，(35):101，103.

［4］吳惠璇，林宙峰，郭益軍. Excel 軟件在環(huán)境監(jiān)測數(shù)據(jù)處理中的應(yīng)用［J］.科教文匯(上旬刊)，2007，(7):202.WU Hui-lin，LIN Zhou-feng，GUO Yi-jun. Application of Excel software in data processing of environmental monitoring［J］. The Science Education Article Collects，2007，(7):202.