条件统计函数在农村水质监测分析中的应用

广西壮族自治区疾病预防控制中心(530028) 黎 勇

近年来，中央每年安排一定专项资金用于补助地方开展农村饮用水卫生监测工作，自2008年水质监测结果实现了网络直报，各级用户可从网络直报系统中导出数据(Excel格式)进行统计分析。由于数据信息量庞大，统计分析人员往往需要利用Excel排序、筛选、计数、求和等方法进行重复繁杂的操作，增加了统计分析的工作量。应用Excel表格中的条件统计函数，使导出的数据经过简单的处理，即可在设计好的表格中自动记录统计分析结果。本文便介绍其在农村水质监测统计分析的应用。

对象和方法

1.对象的选择 从中国疾病预防控制信息系统，导出2011年丰水期A市的农村饮用水水质监测数据，保存为Excel格式。

2.研究内容 统计分析各种类型的水质合格率。包括不同消毒方式的水质合格率、不同供水规模水质合格率、不同水处理方式水质合格率、不同水源类型的合格率、各项检测指标合格率和辖区各县合格率等。

3.研究方法 根据《生活饮用水卫生标准》限值，利用if逻辑函数判断水质是否合格，不合格的结果返回0，合格返回1。1表示合格，方便合格率的计算。如一批水样，利用计数函数算得水样份数为100，利用求和函数算得水质是否合格的返回值合计为80，则该批水样合格率为80%。

(1)各检测指标是否合格的判断

打开Excel，在工作薄中插入两个工作表，分别命名为“待统计”和“统计结果”。“待统计”工作表存放导出的原始数据，“统计结果”工作表中按照拟统计分析的内容设计表格用以显示(记录)统计分析结果。为了避免改动原始数据，在“待统计”工作表中，选定Y1(色度):AQ1(耐热大肠菌群)单元格，复制此19个检测指标到AR1:BJ1。

①色度是否合格的判断:在“色度”下面的AR2单元格输入“=if(Y2＞15，0，1)”。说明，如果 Y2(色度)的值大于15，则在AR2单元格返回0，否则返回1。

② pH值是否合格的判断:在“pH”下面的AV2单元格输入“=if(OR(AC2 ＞8.5，AC2 ＜6.5)，0，1)”。说明，如果AC2(pH)的值大于8.5或者小于6.5，则在AV2单元格返回0，否则返回1。

同理，浑浊度、铁、菌落总数等其他17个指标是否合格的判断，也类似处理。

(2)水质是否合格的判断

检测的19个指标均合格，水质才能判定为合格。也就是说，19个指标的返回值合计等于19，水质才能判定为合格。故可用if函数嵌套sum函数进行判断，返回值1表示水质合格。操作如下:把19个检测指标的最后一列，即把BK列的字段名改为“综合判断”，在BK2 单元格输入“=if(SUM(AR2:BJ2)=19，1，0)”。说明，如果19个指标(AR2:BJ2)返回值的合计等于19，则在BK2单元格返回1，否则返回0。

选定AR2:BK2，往下拖动填充句柄至最后一行，即可完成各检测指标及水质是否合格的判断。

(3)多重条件统计函数

①COUNTIFS函数，用于统计一组给定条件所指定的单元格数。语法为:

COUNTIFS(criteria_range1，criteria1，〔criteria_range2，criteria2〕…)

criteria_range1在其中计算关联条件的第一个区域。

criteria1条件的形式为数字、表达式、单元格引用或文本，可用来定义将对哪些单元格进行计数。例如，条件可以表示为 32、“＞32”、A4、“苹果”。

criteria_range2，criteria2，…可选。附加的区域及其关联条件。每一个附加的区域都必须与参数 criteria_range1具有相同的行数和列数，这些区域无需彼此相邻。

通过以下示例1有助于理解COUNTIFS函数。

示例1:求产地为广州且单价大于20，销售额为200的种类数。

②SUMIFS函数，用于对一组给定条件所指定的单元格求和。语法为:

SUMIFS(sum_range，criteria_range1，criteria1，〔criteria_range2，criteria2〕，…)

sum_range是对一个或多个单元格求和，包括数字或包含数字的名称、区域或单元格引用。忽略空白和文本值。

criteria_range1是在其中计算关联条件的第一个区域。

criteria1是条件的形式为数字、表达式、单元格引用或文本，可用来定义将对criteria_range1参数中的哪些单元格求和。例如，条件可以表示为 32、“＞32”、B4、“苹果”或“32”。

criteria_range2，criteria2，…可选。附加的区域及其关联条件。

可以在条件中使用通配符，即问号(?)和星号(*)。问号匹配任一单个字符;星号匹配任一字符序列。

结 果

节选1，统计“待统计”工作表中不同消毒方式的出厂水的合格率，见表1。

表1 不同消毒方式的水质综合判断结果

操作过程:首先，在“统计结果”工作表设计如下表格，见表2。然后，在B2单元格输入“=COUNTIFS(待统计!L2:L7，“出厂水”，待统计!M2:M7，A2)”;在C2单元格输入“=SUMIFS(待统计!BK2:BK7，待统计!L2:L7，“出厂水”，待统计!M2:M7，A2)”;在D2单元格输入“=C2/B2*100”。最后，选定B2:D2，往下拖动填充句柄至“漂白粉”所在的行，即可在表2自动记录统计结果。

上述函数语法中的“待统计!”表示引用“待统计”工作表中的数据。实际上，函数语法中的“待统计!L2:L7”不需要逐字输入，只需用鼠标在“待统计”工作表中选择L列2至7行，即可自动输入。

表2 不同消毒方式出厂水水质合格率

节选2，统计“待统计”工作表中供水能力为500～1000的末梢水合格率，见表3。

操作过程:首先，在“统计结果”工作表设计如下表格，见表4。然后，在B7单元格输入“=COUNTIFS(待统计!L:L，“末梢水”，待统计!U:U，“＞ =500”，待统计!U:U，“＜ =1000”)”;在 C7 单元格输入“=SUMIFS(待统计!BK:BK，待统计!L:L，“末梢水”，待统计!U:U，“＞ =500”，待统计!U:U，“＜=1000”)”;最后，在 D7 单元格输入“=C7/B7*100”即可。

表3 不同供水能力的水质综合判断结果

上述函数语法中的“L:L”表示引用L列的数据。由于在表3中L列没有其他无相关数据，所以，引用L:L和引用L2:L7的统计结果是一样的。

表4 不同供水能力的末梢水水质合格率

讨 论

本文只需将系统中导出的数据放在“待统计”工作表，经过简单的复制粘贴及if函数的判断操作，利用多重条件函数在“统计结果”工作表即可自动记录统计结果，并且只要“待统计”工作表的数据改动，统计结果随即有相应的变化。Excel有着强大的数据统计分析功能，已逐渐成为应用广泛的数据分析软件。在使用过程中遇到的疑难问题，可以随时使用Excel内置的联机帮助系统获得帮助。目前常用的统计分析软件有SAS和SPSS，虽然他们的统计分析功能很强大，但是这些软件的使用相对比较复杂，没有经过培训很难运用自如〔1〕。对于数据量不是很多的县级用户(疾病预防控制中心)，没必要使用SAS软件，简单易懂的Excel才是首选。需要注意的是，Excel2003版本用户须将Excel升级到2007或2010版本，才能使用countifs和sumifs函数。

1.蔡丽君，温仲民.Excel数据分析在医学统计中的应用.南通医学院学报，2009，29(2):134-135.