今天要教的函數 frequency 是很多 Excel 高手們的心頭好,因為它實在太強大了,以一己之力,替代了 lookup、mod、sumif、countif、sumproduct 等等多種函數組合的效果。
所以話不多說,直接上案例,今天的幹貨内容非常多,強烈建議收藏保存!
基于下圖的數據表,分别查詢以下數據:
- 查詢出每個指定分數區間的人數
- 統計奇數分的數量
- 統計偶數分的數量
- 統計男、女同學人數
分數區間如下,分别查出各區間對應的人數。
1. 增加一列輔助列,依次列出每個區間的最大值;“600分”以上的就留空
2. 選中 G2:G7 單元格區域
* 請注意:這一步很關鍵,必須同時選中需要統計人數的所有單元格
3. 輸入以下公式,按 Ctrl Shift Enter 使數組函數生效:
=FREQUENCY(C2:C15,F2:F7)
* 請注意:
- 第二個參數,即選中的輔助列區域,必須将 F7 這個空單元格包含在内
- 必須按三鍵使之成為數組函數
在公式釋義前,我們先來學習一下 frequency 函數。
- 計算數值在某個區域内的出現的次數,然後返回一個垂直的數組。
- FREQUENCY(data_array, bins_array)
- data_array:必需,要統計頻率的數組
- bins_array:必需,對 data_array 進行頻率計算的分段點
- 雖然數組公式有點難以理解,但是根據上述描述,就不難知道這段公式的作用就是計算 C2:C15 區域中,以 F2:F7 中的每一個單元格為分段點的數據區間所出現的頻率
如果還是不能理解,那我們來看一下參數說明,就比較清楚了:
- 選中 G2:G7 區域 --> 選擇菜單欄的“公式”-->“插入函數”
- 在彈出的“函數參數”對話框中分别點擊兩個參數,查看其注釋和結果;簡單來說,第一個參數表示在哪裡找,第二個參數表示區間規則。
1. 在 H2 單元格輸入以下公式:
=FREQUENCY(-1^C2:C15,0)
公式釋義:
- -1^C2:C15:将 C2:C15 區域的值分别作為 -1 的幂進行計算;奇數幂的結果為 -1,偶數幂的結果為 1;參見以下運算結果
- =FREQUENCY(-1^C2:C15,0):以 0 為分段點,統計小于 0 的個數,即為奇數個數
偶數的數量,可以用 count 統計出總數,再減去奇數的數量。
但本文是教學貼,所以還是有責任教一下單獨統計偶數的公式。
1. 在 I2 單元格中輸入以下公式:
=FREQUENCY(-(-1^C2:C15),0)
公式釋義:
- -(-1^C2:C15):在 -1 的 n 次幂前面加上負号,結果正好跟上述奇數計算結果相反,即奇數幂的結果為 1,偶數幂的結果為 -1
- =FREQUENCY(-(-1^C2:C15),0):以 0 為分段點,統計小于 0 的個數,即為偶數個數
2. 現在我們随機修改一個分數,看一下奇偶數的計算結果是否會随之正确變化:
将 C2 單元格的分數改為偶數
紅框中奇、偶數統計結果自動重新計算了,公式确認無誤。
給下表增加了“性别”列:需要在 J 列統計出男、女同學的人數。
1. 選中 J2:J3 單元格區域 --> 輸入以下公式 --> 按 Ctrl Shift Enter 使數組函數生效:
=FREQUENCY(CODE(D2:D15),CODE(I2:I3))
公式釋義:
- frequency 函數的用法與“解決方案 1”相同,所以本例主要學會理解 code 函數
- code 函數語法:CODE(text);
- code 函數作用:返回文本字符串中第一個字符的數字代碼
- 從下圖的公式結果可以看出,code 函數将“男”、“女”分别轉換成了對應的數字代碼,從而使得 frequency 可以對兩組數組進行頻率統計
很多同學會覺得 Excel 單個案例講解有些碎片化,初學者未必能完全理解和掌握。不少同學都希望有一套完整的圖文教學,從最基礎的概念開始,一步步由簡入繁、從入門到精通,系統化地講解 Excel 的各個知識點。
現在終于有了,以下專欄,從最基礎的操作和概念講起,用生動、有趣的案例帶大家逐一掌握 Excel 的操作技巧、快捷鍵大全、函數公式、數據透視表、圖表、打印技巧等……學完全本,你也能成為 Excel 高手。
,