Excel中多條件統(tǒng)計(jì)函數(shù)COUNTIFS和FREQUENCY的應(yīng)用

王侃

摘要：Excel是Office軟件中的一個(gè)重要組件，即電子表格軟件。Excel函數(shù)則是Excel中的內(nèi)置函數(shù)，Excel函數(shù)包含統(tǒng)計(jì)函數(shù)，數(shù)學(xué)函數(shù)，邏輯函數(shù)等十一大類，正確應(yīng)用Excel函數(shù)可以提高工作效率。該文主要以函數(shù)COUNTIFS和FREQUENCY為例來探討Excel統(tǒng)計(jì)函數(shù)的應(yīng)用。

關(guān)鍵詞：Excel;COUNTIFS函數(shù);FREQUENCY函數(shù)

中圖分類號：TP311? ? ? ? 文獻(xiàn)標(biāo)識碼：A? ? ? ? 文章編號：1009-3044（2018）31-0210-02

Excel是Microsoft公司出品的辦公套裝軟件的一個(gè)重要組件，可以用它來進(jìn)行數(shù)據(jù)計(jì)算和生成各類統(tǒng)計(jì)圖表等，且界面清晰直觀，使得Excel至面世以來便成為最流行的個(gè)人計(jì)算機(jī)數(shù)據(jù)處理軟件，在各行各業(yè)如教務(wù)教學(xué)、行政管理，財(cái)務(wù)統(tǒng)計(jì)、保險(xiǎn)金融等被廣泛地應(yīng)用。Excel中共包含十一類函數(shù)，隨著版本的不斷更新，函數(shù)數(shù)目也逐步增加。使用Excel的函數(shù)就是使用它預(yù)定義的公式，可以按特定的順序或結(jié)構(gòu)來執(zhí)行計(jì)算。如我們在使用Excel電子表格來統(tǒng)計(jì)處理數(shù)據(jù)的時(shí)候，時(shí)常會(huì)用到統(tǒng)計(jì)函數(shù)如COUNTIF函數(shù)、COUNTIFS函數(shù)和FREQUENCY函數(shù)等來按需自動(dòng)統(tǒng)計(jì)處理表單數(shù)據(jù)，之前常用COUNTIF函數(shù)來統(tǒng)計(jì)滿足某個(gè)條件的單元格的數(shù)量，但若條件不單一，則要多次使用COUNTIF函數(shù)才能完成統(tǒng)計(jì)，煩瑣且容易出錯(cuò)。本文通過探討COUNTIFS函數(shù)和FREQUENCY函數(shù)的應(yīng)用來完成多條件的數(shù)據(jù)統(tǒng)計(jì)。

1 COUNTIFS函數(shù)

1.1功能

COUNTIFS函數(shù)用來統(tǒng)計(jì)一組給定條件所指定的單元格數(shù)目，條件可以為一個(gè)也可為多個(gè)。

1.2語法

COUNTIFS（條件區(qū)域1，條件1，[條件區(qū)域2，條件2]，…）。

1.3參數(shù)

1）條件區(qū)域1：必須項(xiàng)。是要為特定條件進(jìn)行匹配查詢的第一個(gè)區(qū)域。

2）條件1：必須項(xiàng)。其形式可以是數(shù)字、表達(dá)式、單元格引用或者文本，它定義了要統(tǒng)計(jì)的單元格的范圍。 例如，條件可以表示為 75、"<=65"、A6、"中國江蘇"或 "2018"。

3）條件區(qū)域2，條件2， ...：可選項(xiàng)。為第二個(gè)條件區(qū)域和第二個(gè)條件，以此類推。 最多能有一百二十七個(gè)條件區(qū)域和條件對。第二個(gè)及之后的每個(gè)條件匹配查詢區(qū)域都需與參數(shù)條件區(qū)域1具有相同的行數(shù)和列數(shù)，但這些區(qū)域無須彼此相鄰。

1.4注意事項(xiàng)

1）COUNTIFS函數(shù)的用法與COUNTIF函數(shù)類似，但它既能統(tǒng)計(jì)單個(gè)條件的計(jì)數(shù)，此時(shí)相當(dāng)于COUNTIF函數(shù)的應(yīng)用;同時(shí)又能實(shí)現(xiàn)統(tǒng)計(jì)多個(gè)條件的計(jì)數(shù)。

2）如果條件中需要引用單元格，正確寫法是“符號條件”&單元格地址。

3）如果條件參數(shù)是對空值的引用，COUNTIFS將該單元格的值視為0。

4）條件中可以使用星號（*）和問號（？）這兩個(gè)文本通配符。星號代表任意字符串，也可以代表零個(gè)字符，問號則代表任意一個(gè)字符。如果要查找實(shí)際的星號或問號，則必須在字符前輸入波形符 （～）。

2 FREQUENCY函數(shù)

2.1 功能

FREQUENCY函數(shù)即頻率函數(shù)，可以計(jì)算某個(gè)區(qū)域中某值的出現(xiàn)頻率，并返回一個(gè)垂直數(shù)組。例如，使用函數(shù)FREQUENCY可以統(tǒng)計(jì)計(jì)算分?jǐn)?shù)區(qū)域中每個(gè)分?jǐn)?shù)段出現(xiàn)的次數(shù)。由于REQUENCY返回的是一個(gè)數(shù)組，所以輸入時(shí)也必須為數(shù)組公式的形式。

2.2 語法

FREQUENCY（數(shù)組數(shù)據(jù)，數(shù)據(jù)接收區(qū)域）。

2.3 參數(shù)

1）數(shù)組數(shù)據(jù)：必需項(xiàng)。需要對其進(jìn)行計(jì)算頻率的一個(gè)數(shù)組或?qū)σ唤M數(shù)值的引用，即數(shù)據(jù)源。如果數(shù)組數(shù)據(jù)中不包含任何數(shù)值，則FREQUENCY函數(shù)返回一個(gè)零數(shù)組。

2）數(shù)據(jù)接收區(qū)域：必需項(xiàng)。要將數(shù)組數(shù)據(jù)中的數(shù)值分組插入到一個(gè)區(qū)間數(shù)組或?qū)^(qū)間的引用，即統(tǒng)計(jì)區(qū)間的分段點(diǎn)。 如果數(shù)組接收區(qū)域中不含有任何數(shù)值，則FREQUENCY函數(shù)的返回值與數(shù)組數(shù)據(jù)中元素的個(gè)數(shù)相同。

2.4注意事項(xiàng)

1）FREQUENCY函數(shù)的參數(shù)設(shè)定完之后必須以數(shù)組公式的形式輸入。

2）數(shù)組返回中的元素個(gè)數(shù)比數(shù)組數(shù)據(jù)中的元素多一個(gè)。返回的數(shù)組中的額外元素返回超過最大間隔以上的任何值的計(jì)數(shù)。例如，要對60以下、60-85及85以上這三個(gè)區(qū)域進(jìn)行計(jì)數(shù)時(shí)，我們只需設(shè)置60和85兩個(gè)值，即可確保將FREQUENC函數(shù)輸入到結(jié)果的三個(gè)單元格。額外的單元格將返回?cái)?shù)組數(shù)據(jù)中大于第二個(gè)間隔值的值的數(shù)量。

3）函數(shù)FREQUENCY會(huì)自動(dòng)忽略非數(shù)值的數(shù)據(jù)。

4）對于返回結(jié)果為數(shù)組的公式，必須以數(shù)組公式的形式輸入。

3 應(yīng)用解析

以學(xué)生成績統(tǒng)計(jì)表為例，對于教師，這是期末考試結(jié)束后必不可少的工作之一，利用Excel函數(shù)進(jìn)行統(tǒng)計(jì)既快捷方便，又不易出錯(cuò)，極大提高了統(tǒng)計(jì)的準(zhǔn)確率。

示例：求出以下四門學(xué)科各分?jǐn)?shù)段的人數(shù)，如圖1所示。

3.1利用COUNTIFS 函數(shù)求解

以統(tǒng)計(jì)課程高等數(shù)學(xué)各分?jǐn)?shù)段人數(shù)為例，步驟如下：

1）先求90分以上或60分以下的人數(shù)，在單元格C21中，輸入公式“=COUNTIFS（C2：C17，">=90"）”，即可求出90分及以上的人數(shù)，如圖2所示;同理60分以下的人數(shù)可以在C25單元格內(nèi)輸入公式“=COUNTIFS（C2：C17，"<60"）”求出。此時(shí)的統(tǒng)計(jì)人數(shù)均只有一個(gè)條件，使用COUNTIFS 函數(shù)相當(dāng)于COUNTIF函數(shù)的功能。

2）再求80分段的人數(shù)，在單元格C22中輸入公式“=COUNTIFS（C2：C17，"<90"，C2：C17，">=80"）”，如圖3所示;

3）用同樣的方法求出70分段的人數(shù)，在單元格C23中輸入公式“=COUNTIFS（C2：C17，"<80"，C2：C17，">=70"）”;

4）再用同樣的方法求出60分段的人數(shù)，在單元格C24中輸入公式“=COUNTIFS（C2：C17，"<70"，C2：C17，">=60"）”。

其他各門課的統(tǒng)計(jì)均可參照以上COUNTIFS函數(shù)的使用方法。

3.2利用FREQUENCY函數(shù)求解

以統(tǒng)計(jì)課程大學(xué)英語各分?jǐn)?shù)段人數(shù)為例，步驟如下：

1）首先確定間隔分值，定義區(qū)間數(shù)組，在單元格H21：H25內(nèi)分別輸入“100，89，79，69，59”，區(qū)間分割值含義如圖4所示;

2）然后在單元格D21內(nèi)輸入公式“=FREQUENCY（D2：D17，H21：H25）”，如圖5所示。此時(shí)要注意的是，由于FREQUENCY函數(shù)的結(jié)果是返回一個(gè)數(shù)組，要想得到數(shù)組的結(jié)果，則必須以數(shù)組公式的形式輸入，方法是按下<Ctrl+Shift+Enter>組合鍵來確定，得到公式顯示為“{=FREQUENCY（D2：D17，H21：H25）}”，特別提醒此處的大括號手動(dòng)輸入是無效的;

3）選用單元格D21右下角的智能填充柄，向下拖動(dòng)填充單元格D22：D25即可得出其他分?jǐn)?shù)段的統(tǒng)計(jì)人數(shù)。

4 結(jié)束語

通過以上案例我們可以看出，使用COUNTIFS函數(shù)和FREQUENCY函數(shù)均能完成對成績分?jǐn)?shù)段的統(tǒng)計(jì)，其中COUNTIFS函數(shù)在多條件的情況下比COUNTIF函數(shù)的輸入公式要簡潔一些，且該函數(shù)比較容易理解，但每個(gè)分?jǐn)?shù)段需要分別輸入公式，還是比較煩瑣，效率并不高。而使用FREQUENCY函數(shù)是專門用于數(shù)據(jù)頻度分析的函數(shù)，能快速方便地完成統(tǒng)計(jì)，但輸入時(shí)需要注意必須以數(shù)組公式的形式輸入。

總之通過對以上兩個(gè)函數(shù)的應(yīng)用實(shí)例剖析，我們能了解到COUNTIFS函數(shù)和FREQUENCY函數(shù)的作用及使用方法，在實(shí)際應(yīng)用時(shí)根據(jù)需要選擇相應(yīng)函數(shù)即可。

參考文獻(xiàn)：

[1] 神龍工作室.Excel高效辦公.行政與人力資源管理[M].北京：人民郵電出版社，2006.

[2] 胡國民.《辦公軟件高級應(yīng)用》課堂教學(xué)之我見[J].辦公自動(dòng)化，2013（2）：56-58.

[3] 教育部考試中心.全國計(jì)算機(jī)等級考試二級教程——MS Office高級應(yīng)用[M].北京：高等教育出版社，2015.