條件統(tǒng)計(jì)函數(shù)在農(nóng)村水質(zhì)監(jiān)測(cè)分析中的應(yīng)用

廣西壯族自治區(qū)疾病預(yù)防控制中心(530028) 黎 勇

近年來(lái)，中央每年安排一定專項(xiàng)資金用于補(bǔ)助地方開(kāi)展農(nóng)村飲用水衛(wèi)生監(jiān)測(cè)工作，自2008年水質(zhì)監(jiān)測(cè)結(jié)果實(shí)現(xiàn)了網(wǎng)絡(luò)直報(bào)，各級(jí)用戶可從網(wǎng)絡(luò)直報(bào)系統(tǒng)中導(dǎo)出數(shù)據(jù)(Excel格式)進(jìn)行統(tǒng)計(jì)分析。由于數(shù)據(jù)信息量龐大，統(tǒng)計(jì)分析人員往往需要利用Excel排序、篩選、計(jì)數(shù)、求和等方法進(jìn)行重復(fù)繁雜的操作，增加了統(tǒng)計(jì)分析的工作量。應(yīng)用Excel表格中的條件統(tǒng)計(jì)函數(shù)，使導(dǎo)出的數(shù)據(jù)經(jīng)過(guò)簡(jiǎn)單的處理，即可在設(shè)計(jì)好的表格中自動(dòng)記錄統(tǒng)計(jì)分析結(jié)果。本文便介紹其在農(nóng)村水質(zhì)監(jiān)測(cè)統(tǒng)計(jì)分析的應(yīng)用。

對(duì)象和方法

1.對(duì)象的選擇 從中國(guó)疾病預(yù)防控制信息系統(tǒng)，導(dǎo)出2011年豐水期A市的農(nóng)村飲用水水質(zhì)監(jiān)測(cè)數(shù)據(jù)，保存為Excel格式。

2.研究?jī)?nèi)容 統(tǒng)計(jì)分析各種類型的水質(zhì)合格率。包括不同消毒方式的水質(zhì)合格率、不同供水規(guī)模水質(zhì)合格率、不同水處理方式水質(zhì)合格率、不同水源類型的合格率、各項(xiàng)檢測(cè)指標(biāo)合格率和轄區(qū)各縣合格率等。

3.研究方法 根據(jù)《生活飲用水衛(wèi)生標(biāo)準(zhǔn)》限值，利用if邏輯函數(shù)判斷水質(zhì)是否合格，不合格的結(jié)果返回0，合格返回1。1表示合格，方便合格率的計(jì)算。如一批水樣，利用計(jì)數(shù)函數(shù)算得水樣份數(shù)為100，利用求和函數(shù)算得水質(zhì)是否合格的返回值合計(jì)為80，則該批水樣合格率為80%。

(1)各檢測(cè)指標(biāo)是否合格的判斷

打開(kāi)Excel，在工作薄中插入兩個(gè)工作表，分別命名為“待統(tǒng)計(jì)”和“統(tǒng)計(jì)結(jié)果”。“待統(tǒng)計(jì)”工作表存放導(dǎo)出的原始數(shù)據(jù)，“統(tǒng)計(jì)結(jié)果”工作表中按照擬統(tǒng)計(jì)分析的內(nèi)容設(shè)計(jì)表格用以顯示(記錄)統(tǒng)計(jì)分析結(jié)果。為了避免改動(dòng)原始數(shù)據(jù)，在“待統(tǒng)計(jì)”工作表中，選定Y1(色度):AQ1(耐熱大腸菌群)單元格，復(fù)制此19個(gè)檢測(cè)指標(biāo)到AR1:BJ1。

①色度是否合格的判斷:在“色度”下面的AR2單元格輸入“=if(Y2＞15，0，1)”。說(shuō)明，如果 Y2(色度)的值大于15，則在AR2單元格返回0，否則返回1。

② pH值是否合格的判斷:在“pH”下面的AV2單元格輸入“=if(OR(AC2 ＞8.5，AC2 ＜6.5)，0，1)”。說(shuō)明，如果AC2(pH)的值大于8.5或者小于6.5，則在AV2單元格返回0，否則返回1。

同理，渾濁度、鐵、菌落總數(shù)等其他17個(gè)指標(biāo)是否合格的判斷，也類似處理。

(2)水質(zhì)是否合格的判斷

檢測(cè)的19個(gè)指標(biāo)均合格，水質(zhì)才能判定為合格。也就是說(shuō)，19個(gè)指標(biāo)的返回值合計(jì)等于19，水質(zhì)才能判定為合格。故可用if函數(shù)嵌套sum函數(shù)進(jìn)行判斷，返回值1表示水質(zhì)合格。操作如下:把19個(gè)檢測(cè)指標(biāo)的最后一列，即把BK列的字段名改為“綜合判斷”，在BK2 單元格輸入“=if(SUM(AR2:BJ2)=19，1，0)”。說(shuō)明，如果19個(gè)指標(biāo)(AR2:BJ2)返回值的合計(jì)等于19，則在BK2單元格返回1，否則返回0。

選定AR2:BK2，往下拖動(dòng)填充句柄至最后一行，即可完成各檢測(cè)指標(biāo)及水質(zhì)是否合格的判斷。

(3)多重條件統(tǒng)計(jì)函數(shù)

①COUNTIFS函數(shù)，用于統(tǒng)計(jì)一組給定條件所指定的單元格數(shù)。語(yǔ)法為:

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

criteria_range1在其中計(jì)算關(guān)聯(lián)條件的第一個(gè)區(qū)域。

criteria1條件的形式為數(shù)字、表達(dá)式、單元格引用或文本，可用來(lái)定義將對(duì)哪些單元格進(jìn)行計(jì)數(shù)。例如，條件可以表示為 32、“＞32”、A4、“蘋果”。

criteria_range2，criteria2，…可選。附加的區(qū)域及其關(guān)聯(lián)條件。每一個(gè)附加的區(qū)域都必須與參數(shù) criteria_range1具有相同的行數(shù)和列數(shù)，這些區(qū)域無(wú)需彼此相鄰。

通過(guò)以下示例1有助于理解COUNTIFS函數(shù)。

示例1:求產(chǎn)地為廣州且單價(jià)大于20，銷售額為200的種類數(shù)。

②SUMIFS函數(shù)，用于對(duì)一組給定條件所指定的單元格求和。語(yǔ)法為:

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

sum_range是對(duì)一個(gè)或多個(gè)單元格求和，包括數(shù)字或包含數(shù)字的名稱、區(qū)域或單元格引用。忽略空白和文本值。

criteria_range1是在其中計(jì)算關(guān)聯(lián)條件的第一個(gè)區(qū)域。

criteria1是條件的形式為數(shù)字、表達(dá)式、單元格引用或文本，可用來(lái)定義將對(duì)criteria_range1參數(shù)中的哪些單元格求和。例如，條件可以表示為 32、“＞32”、B4、“蘋果”或“32”。

criteria_range2，criteria2，…可選。附加的區(qū)域及其關(guān)聯(lián)條件。

可以在條件中使用通配符，即問(wèn)號(hào)(?)和星號(hào)(*)。問(wèn)號(hào)匹配任一單個(gè)字符;星號(hào)匹配任一字符序列。

結(jié) 果

節(jié)選1，統(tǒng)計(jì)“待統(tǒng)計(jì)”工作表中不同消毒方式的出廠水的合格率，見(jiàn)表1。

表1 不同消毒方式的水質(zhì)綜合判斷結(jié)果

操作過(guò)程:首先，在“統(tǒng)計(jì)結(jié)果”工作表設(shè)計(jì)如下表格，見(jiàn)表2。然后，在B2單元格輸入“=COUNTIFS(待統(tǒng)計(jì)!L2:L7，“出廠水”，待統(tǒng)計(jì)!M2:M7，A2)”;在C2單元格輸入“=SUMIFS(待統(tǒng)計(jì)!BK2:BK7，待統(tǒng)計(jì)!L2:L7，“出廠水”，待統(tǒng)計(jì)!M2:M7，A2)”;在D2單元格輸入“=C2/B2*100”。最后，選定B2:D2，往下拖動(dòng)填充句柄至“漂白粉”所在的行，即可在表2自動(dòng)記錄統(tǒng)計(jì)結(jié)果。

上述函數(shù)語(yǔ)法中的“待統(tǒng)計(jì)!”表示引用“待統(tǒng)計(jì)”工作表中的數(shù)據(jù)。實(shí)際上，函數(shù)語(yǔ)法中的“待統(tǒng)計(jì)!L2:L7”不需要逐字輸入，只需用鼠標(biāo)在“待統(tǒng)計(jì)”工作表中選擇L列2至7行，即可自動(dòng)輸入。

表2 不同消毒方式出廠水水質(zhì)合格率

節(jié)選2，統(tǒng)計(jì)“待統(tǒng)計(jì)”工作表中供水能力為500～1000的末梢水合格率，見(jiàn)表3。

操作過(guò)程:首先，在“統(tǒng)計(jì)結(jié)果”工作表設(shè)計(jì)如下表格，見(jiàn)表4。然后，在B7單元格輸入“=COUNTIFS(待統(tǒng)計(jì)!L:L，“末梢水”，待統(tǒng)計(jì)!U:U，“＞ =500”，待統(tǒng)計(jì)!U:U，“＜ =1000”)”;在 C7 單元格輸入“=SUMIFS(待統(tǒng)計(jì)!BK:BK，待統(tǒng)計(jì)!L:L，“末梢水”，待統(tǒng)計(jì)!U:U，“＞ =500”，待統(tǒng)計(jì)!U:U，“＜=1000”)”;最后，在 D7 單元格輸入“=C7/B7*100”即可。

表3 不同供水能力的水質(zhì)綜合判斷結(jié)果

上述函數(shù)語(yǔ)法中的“L:L”表示引用L列的數(shù)據(jù)。由于在表3中L列沒(méi)有其他無(wú)相關(guān)數(shù)據(jù)，所以，引用L:L和引用L2:L7的統(tǒng)計(jì)結(jié)果是一樣的。

表4 不同供水能力的末梢水水質(zhì)合格率

討 論

本文只需將系統(tǒng)中導(dǎo)出的數(shù)據(jù)放在“待統(tǒng)計(jì)”工作表，經(jīng)過(guò)簡(jiǎn)單的復(fù)制粘貼及if函數(shù)的判斷操作，利用多重條件函數(shù)在“統(tǒng)計(jì)結(jié)果”工作表即可自動(dòng)記錄統(tǒng)計(jì)結(jié)果，并且只要“待統(tǒng)計(jì)”工作表的數(shù)據(jù)改動(dòng)，統(tǒng)計(jì)結(jié)果隨即有相應(yīng)的變化。Excel有著強(qiáng)大的數(shù)據(jù)統(tǒng)計(jì)分析功能，已逐漸成為應(yīng)用廣泛的數(shù)據(jù)分析軟件。在使用過(guò)程中遇到的疑難問(wèn)題，可以隨時(shí)使用Excel內(nèi)置的聯(lián)機(jī)幫助系統(tǒng)獲得幫助。目前常用的統(tǒng)計(jì)分析軟件有SAS和SPSS，雖然他們的統(tǒng)計(jì)分析功能很強(qiáng)大，但是這些軟件的使用相對(duì)比較復(fù)雜，沒(méi)有經(jīng)過(guò)培訓(xùn)很難運(yùn)用自如〔1〕。對(duì)于數(shù)據(jù)量不是很多的縣級(jí)用戶(疾病預(yù)防控制中心)，沒(méi)必要使用SAS軟件，簡(jiǎn)單易懂的Excel才是首選。需要注意的是，Excel2003版本用戶須將Excel升級(jí)到2007或2010版本，才能使用countifs和sumifs函數(shù)。

1.蔡麗君，溫仲民.Excel數(shù)據(jù)分析在醫(yī)學(xué)統(tǒng)計(jì)中的應(yīng)用.南通醫(yī)學(xué)院學(xué)報(bào)，2009，29(2):134-135.