軟件的回歸分析及在水質監測數據處理中的應用

魏華煒 廖晶晶

（1白云區環境監測站 貴州貴陽 550014 2貴州師范學院地理與旅游學院 貴州貴陽 550018）

對大氣、水、噪聲及土壤等環境要素進行采樣分析，掌握各個階段環境質量及污染狀況，進而為環境保護相關部門提供可靠的依據，是環境監測站的日常工作。在進行環境監測時，對原始監測數據進行分類整理及計算是重中之重。對涉及到的復雜公式的計算，如AOX質量濃度計算、動植物油的含量計算等，采用傳統的計算器計算，計算數據的重復勞動不僅工作量大，而且還容易出錯，進而嚴重影響了出監測報告的速度和工作效率。

作者對Excel軟件在水質監測分析數據處理方面進行研究，嘗試運用回歸方程的計算模式和Excel軟件對水質監測數據進行分析，不僅提高了計算速度和準確率，還能降低了出錯率，可以很輕松地實現數據的計算與分析。進而使人們的工作變得更加輕松快捷，能達到對監測數據的處理準確、快速、方便、易復核的目的。

1 回歸方程計算模式

考慮回歸函數是y是x的線性函數的情形，即y=a+bx[1]，其中a、b是與x無關的常數，b通常稱為回歸系數。式中a、b通過以下方法計算所得：

式中，為標準物質含量的標準偏差；為標準物質含量；n為繪制工作曲線測點數。

式中，為空白校正后吸光度的標準偏差；為空白校正吸光度。

式中，x為標準物質的均值，y為空白校正吸光度的均值；σ 為協方差。

式中，R為相關系數。

2 利用Excel 工作表處理數據

2.1 水質數據處理模板

筆者通過對環境監測分析工作中的監測數據進行處理、匯總，充分應用Excel軟件，結合環境監測數據處理的復雜、繁瑣等特點，編寫了回歸方程的計算公式，并制作了水質氨氮監測數據處理的工作表格[2]。詳見圖1。

圖1 Excel 水質氨氮數據處理模板

2.2 模板中的公式說明

通過圖1可以直接填報水質氨氮監測結果，實現其原始數據的記錄、處理和上報工作。其中各單元中需計算的公式介紹如下：

單元格E8中輸入公式“=INTERCEPT(D7:K7,D4:K4)”，即通過INTERCEPT函數計算出a值；

單元格G8中輸入公式“=LINEST(D7:K7,D4:K4)”，即通過LINEST函數計算出b值；

單元格I8中輸入公式“=CORREL(D7:K7,D4:K4)”，即通過CORREL函數計算出r值；

單元格H11中輸入公式“=F11-G11”,計算得到樣品實測吸光度（即測定樣品與空白樣品吸光度的差值）；單元格I11中輸入公式“= (H11-$E$8)/$G$8*E11/D11”,計算樣品編號為JD140110-5-1的濃度。

對于樣品JD140110-5-2、JD140110-5-3、JD140110-5-4、JD140110-5-5的計算可以應用Excel復制公式的功能：將鼠標指向單元格I11的右下腳，鼠標變成一個黑色的十字，按住鼠標左鍵，向下拖動鼠標到I15的右下腳，即自動計算出了四個樣品的濃度值。

當工作曲線使用時間超過3個月時，應重新制作工作曲線，只要重新輸入D5~K5；D6~K6區域內的數據，可立即得到水質氨氮工作曲線。同時處理多個樣品的數據，只需將F11~F15區域中每一個單元格的數據重新輸入，接著重新填寫空白吸光度（G11~G14）的數據，即可得到各水質樣品氨氮的監測結果。

2.3 模板的保護

由于環境水質監測數據數量較多，輸入數據時往往容易出錯。模板的保護功能可以將包含計算公式的單元格保護起來，防止工作人員因操作錯誤將相應單元格中的計算公式改動而造成誤報。

具體設置步驟為：選定整張工作表→選定（A1~K1；A15~K15）區域單擊右鍵→設置單元格格式→保護→取消鎖定→選定包含計算公式的單元格(D7、E7、F7、G7、H7、I7、J7、K7、E8、G8、I8、H11、H12、H13、H14、H15、I11、I12、I13、I14、I15)→設置單元格格式→保護→鎖定→工具→保護→保護工作表。經過設置了保護的單元格，當因操作不慎點擊時會彈出提示對話框。從而避免因不當操作導致錯誤結果的上報，釀成對環境監管起到錯誤的引導，并給環境研究工作帶來工作失誤。模板的保護功能不僅可以有效地減少工作失誤，提高工作效率，還能給操作人員提高工作的準確性和可靠性。

3 結果與討論

利用Excel軟件的回歸分析處理水質監測結果，可以有效的提高環境監測結果的準確性和可靠性。將Excel應用于環境監測和數據的分析中，加強了監測工作的自動化辦公。本文根據實際工作的需要，運用算術運算函數及回歸分析等實現了對原始數據的自動化分析處理。本文對表格計算數據進行了保護，能有效防止工作人員因操作錯誤將相應單元格中的計算公式改動而造成誤報，提高數據處理工作的準確性和可靠性，具有一定的推廣價值和意義。

[1]魏立力,馬江洪,顏榮芳,編著.概率統計引論[M].北京：科學出版社，2012.

[2]國家環保總局.水和廢水監測分析方法(第4版)[M].北京:中國環境科學出版社,2002.