使用Excel与Word软件生成水质评价结果

倪俊尧

摘  要：鉴于手工计算的复杂性和应对大批量水样结果汇总后需要批量计算获得准确水质评价结果，通过应用Excel 2007与Word 2007软件，在输入水质检测各项目数据后，生成所需的水质评价结果，以此来提高水质评价的工作效率。

关键詞：Excel;Word;插入函数;水质评价;生成文本结果

中图分类号：X830        文献标志码：A         文章编号：2095-2945（2020）18-0054-04

Abstract： In view of the complexity of manual calculation and the need for batch calculation to obtain accurate water quality evaluation results after summarizing the results of a large number of water samples， the required water quality evaluation results are generated after inputting the data of various water quality testing projects by using Excel 2007 software and Word 2007 software， so as to improve the efficiency of water quality evaluation.

Keywords： Excel; Word; insert function; water quality evaluation; generating text results

引言

生态文明建设在华夏大地如火如荼地开展，水质监测则是其中极为重要的一块内容。通过应用Excel 2007与Word 2007软件，可快速准确地将汇总后的大批量水样结果制作为相关评价结果，直接代入每次的计算数据，生成报告所需的图表或文本，避免了不必要的二次手动的输入过程，提高评价结果的制作效率。

1 运用Excel软件生成水质概况内容

水质概况内容一般要对特定时间内检测断面总数、河道总数和水质类别等进行统计，并按照水质类别进行断面百分比的计算，最终获得一段固定的文本内容。

1.1 评价不重复河道总数与监测断面总数的统计方法

若评价河道中所有监测断面的河道无重复，则评价河道总数与监测断面总数相等。然而，在大量采样过程中，不可避免地遇到河道有重复的情况。因此，借助Excel软件进行统计，能够大大加快评价报告的编制效率与准确率。

选中河道列，在菜单中选择“数据-筛选-高级”命令，进入高级筛选对话框，选择“将筛选结果复制到其他位置”，勾选“选择不重复的记录”，并选择需要复制到的位置，获得不重复的河道名称，再通过COUNTA函数框选此列，来统计不重复河道总数与监测断面的总数。

当我们获得监测断面数据统计表（表1）后，按照上述筛选方法，可得不重复河道名称筛选结果，见表2。

1.2 生成固定句式的统计结果输出文字

每月水质概况中需要对水质类别进行区分，并按照断面个数与总体的比值进行计算后整理为文字结果。若对每个水质类别的结果进行生成文字，则可以使报告生成的效率提高。如表3，需将该表在Excel软件中生成为一整段统计文字。

需要进行以下几步操作：

将占比数字修约。本文中保留小数点后一位。若小数点后一位均不为0，则ROUND函数与TEXT函数可以达到一致的效果;否则，需要使用TEXT函数进行修约，方能获得保留位数一致的效果。因此TEXT函数在处理需要统一小数位数的统计文本时更方便。例如：对A列4行的数据进行保留小数点后一位，则输入=TEXT（A4，"0.0"）即可。

将每一行的统计数字生成一段文字。运用“&”符号进行各个数据与所需生成的文本的连接。需注意的是，所有函数中的符号仅可使用英文状态下的符号，文本内容需要用双引号进行包络。

一般统计报告中，所有为0的数据需要剔除，因此，可以通过加入IF函数来进行数据是否为正值的判断。若为0，则显示空文本，否则显示输出文本。

例如，对表1中第2行Ⅱ类水断面个数进行0值判断是否生成文本（水质类别位于A列），若是，则显示“Ⅱ类水断面占0.0%;”的句式，则输入语句如下：

=IF（B2=0，""，A2&"类水断面占"&C2&"%;"）

结果如表4。

连接文字除了使用“&”符号外，还可以用 CONCATENATE函数[1]将所有文字全部合并。需要注意的是，此函数必须将输出文本逐一选中，不可进行范围框选，每个对象中间用英文逗号隔开。

最后，复制总计行的文本结果，对结尾的标点符号进行检查，若为分号则手动修改为句号。

2 运用Excel软件IF语句进行单项指标计算并汇总获得最终评价结果

对河道参数进行评价，运用Excel软件中插入函数IF语句，将数据判断区间与评价结果一一对应，对单个项目作出正确评价后，方可进行整体评价。

IF函数语句成分如下：

水质类别评价结果分为Ⅰ类、Ⅱ类、Ⅲ类、Ⅳ类、Ⅴ类与劣Ⅴ类，我们可以使用自然数与之对应（劣Ⅴ类对应自然数6），并进行总体判断评价，从而提升评价效率。

2.1 唯一标准判定类别的输入方法

诸如pH值、化学需氧量等项目的水质类别判断是同一个系列的数字统一进行判定。按照地表水环境质量标准，凡是在6-9范围内，即为Ⅰ～Ⅴ类水，又因为非劣Ⅴ类水体还有其他待定判断类别项目，取最差指标进行最终结果判定，因此合格水体水质统一设定为Ⅰ类。假设对U列2行的pH值进行数据判别，函数可如下输入：

需要判别的系列值越多，则需要嵌套的IF函数则越多。

2.2 分类进行标准判定的输入方法

根据GB 3838-2002《地表水环境质量标准》可知，计算地表水总磷的过程中，需要对河流和湖泊进行分类判断。可以有以下两种方法进行快速评价：

方法一：对河流湖泊指定列进行整张表的排序，将河流和湖泊分类后，进行批量计算。然后，运用分配对应的IF函数进行计算。假设对K列3行的总磷的数据进行数据判别。河道则需输入

=IF（K3>0.4，6，IF（K3>0.3，5，IF（K3>0.2，4，IF（K3>0.1，3，IF（K3>0.02，2，1）））））……………………函数语句①

进行函数判别;而湖泊则需输入

=IF（K3>0.2，6，IF（K3>0.1，5，IF（K3>0.05，4，IF（K3>0.025，3，IF（K3>0.01，2，1）））））……………………函数语句②

此方法需要将第一行的河流和第一行的湖泊中输入对应的函数，然后进行批量公式填充，最后使用序号列对整张表格进行排列。

方法二：IF函数首先对水体类别列进行判断，判断条件为="湖泊"。若为湖泊则使用函数语句②;若不为湖泊，即为河流，则使用方法一中判断函数①。

2.3 汇总结果评价

待所有单项指标评价结束后，使用函数MAX对断面评价指标取最大值（使用MAX函数并框选范围），再将数字结果转化为对应的水质类别的文本结果。若出现由于部分河道整治中而没有采样数据，则需要进行IF函数对其中一列数据进行大于0的判断后，再进行取值。按照表1的统计数据举例，最终各检测项目水质类别评价结果与各断面的水质类别评价报告结果，见表5。

2.4 复制数据粘贴入计算表格消除文本格式与无效符号的技巧

另外，若上报数据中存在未采水样断面数据列出现“-”或“/”符号，或者部分数据为文本格式，以上这些数据无法参与或者导致评价错误计算的，则需要复制原数据后，鼠标移动到待复制区域，“右键”-“选择性粘贴”，“粘贴”-“数值”，“运算”-“加”，则可以将文本转为数字，而单独的符号可以全部清除。

2.5 与过去的特定值进行对比并进行评价

就某一特定值进行去年或上月的对比，前提为两次所选断面一致。先对两次的河道或断面和数据进行复制，由IF函数进行文本内容是否相同的判定，而后进行（今值-过去值）/过去值×100%的计算，获得对比的百分比值;若文本内容不一致，则显示“-”符号。

获得对比百分比值以后，根据自身范围需求，进行对比值的大小判定，并显示对应文字。例如：设对比绝对值>20%的为显著变化，>10%的为略有变化，<10%的为基本持平，负值为改善，正值为恶化。对CH3的数据进行计算，则有以下公式：

=IF（CH3<-20%，"显著改善"，IF（CH3<-10%，"略有改善"，IF（CH3<10%，"基本持平"，IF（CH3<20%，"略有惡化"，"显著恶化"））））

2.6 对辖区范围出入境河道进行水质变化情况生成计算

对出入境河流进行水质变化计算，首先对需要对比的指数（假设位于B2）进行2位小数修约，可使用ROUND函数[3]，即=ROUND（B2，2）。然后，进行出入境断面的变化值计算：

变化量=（出境值-入境值）/入境值×100%

如表6，河道名称位于A列，需对两条跨境河道统计数据进行变化值变化的计算与变化情况的文字说明。

首先，对河道x计算变化值（位于G列2行）的正负值进行改善/不变/恶化的结果判定，计算结果在G列3行中显示。

=IF（G2>0，"恶化"，IF（G2=0，"不变"，"改善"））。

然后，编辑水质变化情况的文本输出显示，用IF函数先判断变化量是否为0，再用CONCATENATE函数将变化判断文本与变化百分比取绝对值（函数ABS）后进行TEXT函数文本化，则河道x变化情况文本可输入函数：

=IF（G2=0，G3，CONCATENATE（G3，TEXT（ABS（G2），"0.0"），"%"））

最后，复制左边六列入报告。

3 指定颜色显示水质类别结果

得到水质类别评价数据结果后，可能还需要在报告中列出其中超标的具体项目名称和其对应的水质类别，并用颜色进行标识，来进一步显示该水体污染程度和项目超标的具体情况。然而，仅通过人眼进行数据判别是否超标，还要判断显示颜色，出错概率很大。因此，需要使用函数或条件格式进行颜色辨别，协助水质报告编写者甄别超标项目，体现其高效和准确的作用。

3.1 批量显示水质类别评价结果指定颜色

水质类别评价后，若需要对现状水质进行特定颜色标识（绿色为优于或等于Ⅲ类，红色为Ⅳ类，紫色为Ⅴ类，黑色为劣V类），如表5中每个断面的水质类别结果，可以通过“开始”菜单-条件格式-突出显示单元格规则-等于，来设置内容：将Ⅰ类、Ⅱ类和Ⅲ类的设置绿色文本，红色为Ⅳ类，紫色为Ⅴ类，劣Ⅴ类无需设置，即为默认黑色[4]。

此方法同样可对表5中各项目的评价结果数字进行指定颜色的显示。

3.2 批量显示超出功能水质类别的项目并指定水质类别颜色

若需要将超出功能水质的污染项目列出，则通过数字评价表中超出Ⅲ类水要求的单项评价结果进行突出显示单元格规则设置。

设置之后，由于Excel复制数据过程中自动清除颜色，因此需要先复制入Word软件，再复制入需要进行超标项目名称显示的判断函数计算表格内，颜色同数据即可一同复制过来。然后对功能水质与当前水质进行判断，功能水质小于当前水质的结果显示当前项目名称，复制函数格，再对整列进行“选择性粘贴”-“公式”操作，即可得到有颜色的超出功能水质的带颜色的项目表。

需要注意的是，最右列检测项目左边的所有检测项目则在判断函数显示文本中加一个空格，这样可以避免多个项目连成一行文字，方便审阅或修改。例如：对氨氮换算得出的单项水质评价某一结果（O列11行）进行是否超出功能水质类别（功能水质类别对应数字位于C列11行）的判断显示输入函数：

=IF（O11>C11，"氨氮"，""）

最后，用“&”符号进行文本连接，复制入最终表格后按照列表颜色进行调整，得到各项目之间有空格的显示结果。

4 结束语

Excel 2007与Word 2007软件十分普遍。通过充分运用软件中的功能与特性，尽可能地减少手动输入与人工判断的繁琐过程，使用预先设定好的函数，在方法不断总结与优化的过程中，使大批量水质数据评价的固定格式评价结果生成变得更加快速与准确，简化评价人员的数据操作，最终提高每次报告的制作效率。

参考文献：

[1]张山风.用函数解决数值自动排名[J].应用经验，2008（127）：52.

[2]马锋.Excel在水文数据数字修约中的应用[J].陕西水利，2013（4）：19-21.

[3]谢有亮，麦爱华.EXCEL在环境监测数据统计分析中的应用[J].内蒙古环境科学，2008（3）：34-36.

[4]陈桂鑫.Excel2007按规律设置行列格式[J].电脑迷，2008（7）：79-80.