软件的回归分析及在水质监测数据处理中的应用

魏华炜 廖晶晶

（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.