Excel中多條件統計函數COUNTIFS和FREQUENCY的應用

王侃

摘要：Excel是Office軟件中的一個重要組件，即電子表格軟件。Excel函數則是Excel中的內置函數，Excel函數包含統計函數，數學函數，邏輯函數等十一大類，正確應用Excel函數可以提高工作效率。該文主要以函數COUNTIFS和FREQUENCY為例來探討Excel統計函數的應用。

關鍵詞：Excel;COUNTIFS函數;FREQUENCY函數

中圖分類號：TP311? ? ? ? 文獻標識碼：A? ? ? ? 文章編號：1009-3044（2018）31-0210-02

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

1 COUNTIFS函數

1.1功能

COUNTIFS函數用來統計一組給定條件所指定的單元格數目，條件可以為一個也可為多個。

1.2語法

COUNTIFS（條件區域1，條件1，[條件區域2，條件2]，…）。

1.3參數

1）條件區域1：必須項。是要為特定條件進行匹配查詢的第一個區域。

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

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

1.4注意事項

1）COUNTIFS函數的用法與COUNTIF函數類似，但它既能統計單個條件的計數，此時相當于COUNTIF函數的應用;同時又能實現統計多個條件的計數。

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

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

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

2 FREQUENCY函數

2.1 功能

FREQUENCY函數即頻率函數，可以計算某個區域中某值的出現頻率，并返回一個垂直數組。例如，使用函數FREQUENCY可以統計計算分數區域中每個分數段出現的次數。由于REQUENCY返回的是一個數組，所以輸入時也必須為數組公式的形式。

2.2 語法

FREQUENCY（數組數據，數據接收區域）。

2.3 參數

1）數組數據：必需項。需要對其進行計算頻率的一個數組或對一組數值的引用，即數據源。如果數組數據中不包含任何數值，則FREQUENCY函數返回一個零數組。

2）數據接收區域：必需項。要將數組數據中的數值分組插入到一個區間數組或對區間的引用，即統計區間的分段點。 如果數組接收區域中不含有任何數值，則FREQUENCY函數的返回值與數組數據中元素的個數相同。

2.4注意事項

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

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

3）函數FREQUENCY會自動忽略非數值的數據。

4）對于返回結果為數組的公式，必須以數組公式的形式輸入。

3 應用解析

以學生成績統計表為例，對于教師，這是期末考試結束后必不可少的工作之一，利用Excel函數進行統計既快捷方便，又不易出錯，極大提高了統計的準確率。

示例：求出以下四門學科各分數段的人數，如圖1所示。

3.1利用COUNTIFS 函數求解

以統計課程高等數學各分數段人數為例，步驟如下：

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

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

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

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

其他各門課的統計均可參照以上COUNTIFS函數的使用方法。

3.2利用FREQUENCY函數求解

以統計課程大學英語各分數段人數為例，步驟如下：

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

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

3）選用單元格D21右下角的智能填充柄，向下拖動填充單元格D22：D25即可得出其他分數段的統計人數。

4 結束語

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

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

參考文獻：

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

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

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