最小二乘法处理环境监测数据及Excel建模探索

刘作云

(湖南环境生物职业技术学院 园林学院，湖南 衡阳421005)

环境监测工作的最终目的是要获得准确度高的监测数据，为客观评价环境质量优劣、环境管理与执法、环境科学研究等提供基础数据.

在实际工作中，即使国标的分析方法附有计算公式或模型，但数据处理仍然是一项十分繁琐，又容易出现错误的工作，并最终影响整个监测工作的质量.尤其选择依靠标准曲线分析样品中某污染指标含量时，数据处理的过程更为复杂. 虽然有部分环保工作者通过总结工作经验，提出了一些数据处理的方法，如单文坡等提出了采用Excel 和Origin对监测数据进行处理，不仅发挥了Excel 强大的数据处理功能，又充分利用了Origin 强大的绘图分析优势［1］;吴海斌提出Excel 的函数和VBA 编程功能相结合，可以完成监测数据处理的自动化［2］;贾威等提出了通过标准曲线的回归分析可实现简便、快速处理数据［3］;吴惠璇等则提倡使用Excel 的描述统计功能对自动监测数据进行分析处理［4］.

专业的软件和程序虽然为工作提供了便利，提高了工作效率，但数据的处理终归属于数学的范畴，对于标准曲线法测某一污染指标时，首要的工作就是计算标准曲线的回归方程，再依据回归方程的截距、斜率以及样品的吸光度等求得污染物的浓度，而计算标准曲线的回归方程的理论基础就是最小二乘法.该文以纳氏试剂比色法水质铵的测定为例，分析最小二乘法数据处理的过程.

1 最小二乘法方法基础

对于两个变量X(x1、x2……xn)、Y(y1、y2……yn)，要找出其相关关系，就必须计算其回归方程，即Y=aX+b.按照最小二乘法原理，应先对两组变量进行初步处?理，求出中间参数然后将这些参数代入如下计算公式:

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

通过以上计算，即可求得两个变量的回归方程Y=aX+b.

2 最小二乘法在环境监测数据处理中的应用

环境监测工作中，采用最小二乘法处理数据的过程实际比较简单，只需要将与标准曲线在同等条件下测得的已扣除了空白值的样品吸光度代入回归方程，就能得出样品中某一污染指标的浓度.以下就以纳氏试剂比色法测水中铵的浓度为例说明.

［例1］已知铵标准溶液浓度为10 μg/mL，空白试验吸光度为0.015，水样吸光度为1.048，比色管容量为50 mL，标准曲线数据如表1，试用最小二乘法求该水样中铵的浓度.

表1 纳氏试剂比色法水质铵的监测数据表Tab.1 The data tables of monitoring the content of ammonium in water quality with Nessler’s reagent colorimetric method

解:

步骤一:求标准曲线回归方程

去年乌有先生与子虚道人出谷查看天下之势，黄昏里经过潼关，只觉得残阳如血，雄关雾列，草木交缠在惨惨兵气之中。两老修道学易，知道此地是天下关键，十年内外，必然有事。果然十年之后，潼关之上，灵宝西原，哥舒翰的大军陷入崔乾祐重围，数十万的大军出潼关，渡黄河，被叛军的虎狼之师赶进高原中的深堑，在对岸雷鸣一样的鼓声里哭爹喊娘地被刀剑切割成残肢，被木石冲撞成肉酱，被油火烧成焦炭，马血与人血，骨头与脑髓，泥浆与草木，搅拌在一起。

首先应对表1 数据进行初步处理，将标准曲线吸光度扣除空白实验吸光度.

表2 校正后监测数据表Tab.2 The monitoring data table after correction

然后计算中间参数

再将LXX、L(Y－Y0)(Y－Y0)、LX(Y－Y0)数值代入P、a、b值计算公式.

经计算:

则标准曲线回归方程为:Y = 0.029X +0.009

步骤二:水样吸光度已知，将其扣除空白试验值后代入回归方程.

即:1.048 －0.015 =0.029X+0.009，可求得水样X 值为35.361 mL，而铵标准溶液浓度为10 μg/mL，则水样中铵的含量为353.61 μg，再除以水样用量50 mL，即可计算出水样铵的浓度为7.072 mg/L.

3 Excel 建模分析

实际工作中，环境监测人员每天需要分析大量的样品，如果每一个样品均要采用最小二乘法计算，工作量十分大. 为了简化计算过程，避免因计算产生的错误，可将最小二乘法的计算过程采用Excel编程建立模型.以下仍以上述例题为例，阐述Excel编程建模过程.

步骤二:LXX、L(Y－Y0)(Y－Y0)、LX(Y－Y0)、P、a、b 的计算过程使用Excel 编程.

步骤三:将水样吸光度、铵标准溶液等数据输入Excel 表格，并将相关计算过程编程，如图1所示.

图1 水样铵浓度计算过程编程示意图Fig.1 The sketch of calculation process programming of ammonium concentration in the samples

步骤四:经过上述三个步骤，纳氏试剂比色法水质铵的测定数据处理Excel 模板已经形成. 在实际工作中，纳氏试剂比色法水质铵的测定均是按照GB7479—87 规定的步骤进行的，铵标准溶液用量、比色管数量与容量是固定的，而标准曲线吸光度、空白试验吸光度、铵标准溶液浓度、水样吸光度则因试验条件或操作人员不同而不同. 因此，环境监测工作人员可以通过以上方式建立Excel 数据处理模型，将每次测得的相关数据输入到Excel 数据处理模板的相应位置，即可快速求得水样铵的浓度.以例2 为例阐述Excel 数据处理模板的应用.

【例2】已知铵标准溶液浓度为9.98 μg/mL，空白试验吸光度为0.013，水样吸光度为1.123，比色管容量为50 mL，标准曲线数据如表3，试用最小二乘法求该水样中铵的浓度.

表3 纳氏试剂比色法水质铵的实测数据表Tab.3 The data tables of the content of actual ammonium in water quality with Nessler’s reagent colorimetric method

解:将标准曲线吸光度、铵标准溶液浓度、空白试验吸光度、水样吸光度输入Excel 数据处理模板，即可直接读取该水样铵的浓度为7.414mg/L，如图2 所示.

通过建立Excel 数据处理模板，使得整个实验的数据处理过程大大简化，节约了大量的数据处理时间.同时，由于数据处理程序的简化，能大大提高数据处理的准确率，避免加减乘除过程中的失误.此外，建模还有利于数据处理的统一化，提高了数据间的可比性.

图2 Excel 数据处理模板应用实例图Fig.2 The instance diagram of the applications of Excel data processing template

4 Excel 函数在环境监测数据处理中的应用

Excel 作为强大的数据处理软件，包含有大量的数学函数.从标准曲线的回归方程计算角度出发，可利用的函数有slope(斜率)和intercept(截距)，通过利用这两个函数，可以实现进一步简化Excel 数据处理模板.以例1为例阐述slope(斜率)和intercept(截距)函数的应用.

步骤一:将表1 数据输入Excel 表格，并编程计算斜率和截距.步骤二:后续步骤与Excel 数据处理模板相同，如图3 所示.

图3 Excel 函数数据处理模板示意图Fig.3 The sketch of data processing template by Excel function

通过使用Excel 的函数功能，纳氏试剂比色法水质铵的测定的数据处理过程得到进一步简化.

5 结 语

数据处理是获得监测结果的必要步骤，如何快速的获取准确的监测结果是环境监测工作人员一直在探索的问题.最小二乘法作为回归方程计算的理论基础，是每一个环境监测人员必须掌握的基本方法.而为了提升工作效率，提高监测结果准确度，缓解监测人员工作压力，建立Excel 数据处理模板是行之有效的方法之一，应当在环境监测工作中得到充分的应用.

［1］单文坡，卢海霞，郑 辉.Excel 和Origin 在环境监测数据处理中的应用［J］.石家庄职业技术学院学报，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］吴海斌.水环境监测数据处理的自动化实现［J］.人民珠江，2007，(4):88-90.WU Hai-bin. Automatic realization of water environment monitoring data processing［J］. PEARL RIVER，2007，(4):88-90.

［3］贾 威，赵宝华.环境监测结果的数学处理［J］.科技创新导报，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 软件在环境监测数据处理中的应用［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.