Excel中關於統計分析的問題一直是工作中的一上經典的案例,所以,小必老師這次給大家整理了關於分面統計的三個方法。
下圖是一份某公司與其他的公司簽訂合同的次數的明細表:
現要求統計以下几上情形的資料:
1-5次(不含),5-12次(不含),12-20次(含)這三個次數個數。
注意:為了每個函式都能一次性或者批次地完成統計,下面的每個例子將建立一定的輔助列用於批次完成統計。
01
COUNTIF函式統計
使用COUNTIF函式統計的時候先建立一個對應的間隔點的輔助列。
在H5單元格中輸入公式:
=COUNTIF($C$2:$C$20,">="&F5)-COUNTIF($C$2:$C$20,">"&G5)
按Enter鍵完成後向下填充。
注意
:該套路是一個經典的統計套路。其原理通俗地來說,:比如統計1-100之間的1-50的個數,可以先統計出大於1的個數,然後再減去統計出大於50的個數,就可以得到一個1-50的統計的個數。
當然,此類問題也可以使用COUNTIFS函式去完成。
02
COUNTIFS函式
在H5單元格中輸入公式:
=COUNTIF($C$2:$C$20,">="&F5)-COUNTIF($C$2:$C$20,">"&G5)
按Enter鍵完成後向下填充。
注意
:在使用多條件計數的時候一定要注意區分是大於還是小於的條件。
03
FREQUENCY函式
同樣地建立一個具有分隔點的輔助列。
選中G5:G7單元格區域,輸入公式:
{=FREQUENCY(C2:C20,F5:F7)}
按組合鍵完成。
注意
:該公式兩邊的花括號是按組合鍵後自動加上的,不是拖動輸入的。同時,在使用該公式的時候一定要注意分隔點的判別。
04
SUMPRODUCT函式
同樣地建立一個具有分隔點的輔助列以便於公式可以下拉填充。
在H5單元格中輸入公式:
=SUMPRODUCT(($C$2:$C$20>=F5)*($C$2:$C$20
按Enter鍵完成後向下填充。
注意
:該公式使用了兩上條件共同成立時的邏輯值轉換為0與1,然後相加的計數的原理。
如果想練手的小夥伴可以將下面的地址複製到瀏覽器中開啟:
連結:https://pan。baidu。com/s/1IdVTORTcvs2LUyRFZowpjw
提取碼:g7py