Excel函數學習38：FREQUENCY函數

使用FREQUENCY函數，可以幫助創建頻率分布。例如，計算某班學生的語文成績的頻率分布，分別在0～59、60～69、70～79、80～89、90分以上的區間的學生數。

圖1

什麼情況下使用FREQUENCY函數?

FREQUENCY函數以一列垂直數組返回一組數據的頻率分布。它能夠：

FREQUENCY函數語法

FREQUENCY函數有2個參數，其語法如下：

FREQUENCY函數陷阱

在參數bins_array中，只需指定每個區間的上限，但不一定需要指定最後一個區間的上限（因為它可能是無窮大），它會自動包括在區間中。由於FREQUENCY函數的返回值是數組，因此輸入完成後應按Ctrl Shift Enter鍵。如果參數bins_array中有n個值，那麼函數輸出的區域應該包括n 1個單元格，否則數據會顯示不全。

FREQUENCY函數統計的結果包括區間的上限值，但不包括區間的下限值。如果想統計的結果不包括區間的上限值但包括區間的下限值，可以使用COUNTIF函數和COUNTIFS函數。

示例1: 創建學生成績的頻率分布

如下圖2的示例工作表，要創建學生語文成績的頻率分布，即分別在0～59、60～69、70～79、80～89、90分以上的區間的學生數，使用數組公式：

=FREQUENCY(B3:B28,D4:D7)

返回數組{4;4;5;6;7}。

圖2

示例2: 創建百分比形式的頻率分布

下圖3所示工作表中，命名區域Data為A1:D9。計算區域Data中分別落在0～20、21～40、41～60、61～80、81～100區間的數值數量的公式為：

=FREQUENCY(Data,G3:G7)

這是一個數組公式，因此輸入完成後要按Ctrl Shift Enter組合鍵。

創建百分比形式的頻率分布的數組公式為：

=FREQUENCY(Data,G3:G7)/COUNT(Data)

圖3

示例3: 統計單元格區域中不重複值的數量

有多種方法可以統計單元格區域中不重複值的數量，使用FREQUENCY函數是其中的一種。如下圖4所示的工作表，需要統計列A中有多少唯一的付款賬戶，數組公式為：

=SUMPRODUCT(--(FREQUENCY(A2:A7,A2:A7)>0))

圖4

也可以使用COUNIF函數進行統計，數組公式為：

=SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7))

如果要統計的不重複值的數據是文本（如圖5）或者文本和數據的混合，例如，統計圖5列D中不同付款人的數量，那麼可以使用數組公式：

=SUMPRODUCT(--(FREQUENCY(MATCH(D2:D7,D2:D7,0),ROW(D2:D7)-ROW(D2) 1)>0))

圖5

當然，此時使用COUNTIF函數的公式將更簡單。

如果要統計滿足多於1個條件的不重複值的數量，例如下圖6所示的工作表，要統計付款日期為2017年9月30日之後且付款金額大於等於1000的唯一付款賬戶數，在單元格D9中輸入指定的日期，單元格D10中輸入金額，那麼數組公式為：

=SUM(IF(FREQUENCY(IF(B2:B7>D9,IF(C2:C7>=D10,MATCH(A2:A7,A2:A7,0))),ROW(A2:A7)-ROW(A2) 1),1))

圖6

示例4: 獲取單元格區域中不重複值

上面的示例統計了單元格區域中不重複值的數量，下面獲取該工作表中不重複的付款人列表。如圖7所示，在單元格B13中輸入數組公式：

=IF(ROWS(B$13:B13)>$B$9,"",INDEX($D$2:$D$7,SMALL(IF(FREQUENCY(IF($D$2:$D$7<>"",MATCH($D$2:$D$7,$D$2:$D$7,0)),ROW($D$2:$D$7)-ROW($D$2) 1),ROW($D$2:$D$7)-ROW($D$2) 1),ROWS(B$13:B13))))

圖7

將單元格B13向下拉獲取不重複的付款人列表。

本文屬原創文章，轉載請聯繫我（xhdsxfjy@163.com）或者註明出處。

歡迎在下面留言，完善本文內容，讓更多的人學到更完美的知識。

推薦閱讀：