條件統計函數在農村水質監測分析中的應用

廣西壯族自治區疾病預防控制中心(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.