部落窩函數(shù)課堂第十一課:COUNTIF用法詳解,含條件寫法
?
作者:逍遙來源:部落窩教育發(fā)布時間:2023-06-26 16:46:10點擊:1714
編按:
歡迎來到部落窩函數(shù)課堂第十一課,今天介紹COUNTIF函數(shù)。很多人初學(xué)COUNTIF容易在寫條件參數(shù)的時候出錯。教程解讀了COUNTIF函數(shù)公式中條件參數(shù)的各種寫法,以及COUNTIF函數(shù)的經(jīng)典應(yīng)用,不管你是新手還是高手,都將在這里學(xué)到COUNTIF的新知識。
今天我們介紹按條件計數(shù)之一COUNTIF。
它由COUNT和IF函數(shù)組合而來:COUNT用于統(tǒng)計個數(shù);IF則為邏輯函數(shù),表達(dá)條件。
一、COUNTIF作用和參數(shù)
COUNTIF按條件統(tǒng)計單元格數(shù)量,會忽略區(qū)域中的錯誤值。
函數(shù)語法:
=COUNTIF(統(tǒng)計區(qū)域,條件)
◎統(tǒng)計區(qū)域:引用單元格區(qū)域、名稱;不支持輸入數(shù)據(jù)、數(shù)組,以及結(jié)果為數(shù)據(jù)或數(shù)組的函數(shù)公式。
◎條件:支持文字、數(shù)字、單元格引用、數(shù)組、函數(shù)公式。
第一參數(shù)統(tǒng)計區(qū)域受規(guī)則限制,通常直接引用單元格區(qū)域,不多說;
第二參數(shù)條件,不受限制,變化多樣,需重點學(xué)習(xí)怎么寫。
二、條件參數(shù)的常規(guī)寫法完成單條件計數(shù)
COUNTIF不同于COUNTIFS,屬于單條件統(tǒng)計。下面具體看看條件的表達(dá)方法。
1.借用邏輯運(yùn)算符“>”、“<”、“>=”、“<=”、“<>”表達(dá)條件
邏輯運(yùn)算符表達(dá)條件的完整結(jié)構(gòu):
"邏輯運(yùn)算符號"&數(shù)據(jù)
◎數(shù)據(jù)可以是文本、數(shù)字,也可以是引用的單元格,還可以是函數(shù)公式
◎當(dāng)是等號時,可以省略"邏輯運(yùn)算符號"和&,只保留數(shù)據(jù),如:500、"一組"、A5
◎當(dāng)數(shù)據(jù)是數(shù)字的時候,可以省略&,同時數(shù)字進(jìn)入引號內(nèi),如:">500"、"<>500"
如下圖所示:
特列:數(shù)據(jù)為空,可只保留帶引號的邏輯運(yùn)算符
如統(tǒng)計空單元格或非空單元格,數(shù)據(jù)為空"",除了上面的傳統(tǒng)表達(dá)方式,也可以省略數(shù)據(jù)只保留"邏輯運(yùn)算符號"。
2.條件中可以使用通配符進(jìn)行模糊統(tǒng)計
常用通配符有“?”和“*”, “*”代表任意數(shù)量的任意字符;“?”代表任意單個字符。
比如,當(dāng)需要查找王姓員工數(shù)量,可以輸入公式=COUNTIF(A2:A14,"王*"),然后回車。
如果要查找姓名為三個字的員工數(shù)量,可以輸入公式=COUNTIF(A2:A14,"???"),然后回車。
注意:如果統(tǒng)計包含符號“?”或“*”的單元格個數(shù),需要在在它們前面加波形符~,例如統(tǒng)計含“?”的單元格個數(shù),表達(dá)式應(yīng)該寫成“*~?*”。
3.條件中可以嵌入函數(shù)公式
比如統(tǒng)計業(yè)績大于平均值的人數(shù),輸入公式=COUNTIF(D2:D14,">="&AVERAGE(D2:D14))即可。
三、COUNTIF條件參數(shù)數(shù)組/區(qū)域?qū)懛梢酝瓿苫驐l件計數(shù)
在前方我們統(tǒng)計了銷售一部的人數(shù),若要一并統(tǒng)計銷售一部和銷售二部的人數(shù),公式怎么寫?
當(dāng)前的條件就是或關(guān)系,統(tǒng)計等于銷售一部或者銷售二部的人數(shù)。按照前方所講的條件表達(dá),公式如下:
=COUNTIF(B2:B14,"銷售一部")+ COUNTIF(B2:B14,"銷售二部")
其實,條件參數(shù)支持?jǐn)?shù)組、單元格區(qū)域,搭配SUM函數(shù)等可以更簡便地實現(xiàn)或關(guān)系計數(shù)。
1.在條件中使用數(shù)組
如上圖所示,{"銷售一部","銷售二部"}是數(shù)組,作為COUNTIF的條件,返回兩個值{5,4},然后再用SUM函數(shù)進(jìn)行相加,得到結(jié)果。
2.在條件中引用單元格區(qū)域
可以直接引用F8:F9作為或關(guān)系條件,如下圖:
四、COUNTIF函數(shù)的部分經(jīng)典應(yīng)用
1.按類別或組別編號
譬如按部門編號,如下:
2.多條件計數(shù)
譬如統(tǒng)計業(yè)績>5000,<10000的人數(shù),輸入公式=SUM(COUNTIF(D24:D36,{">5000",">10000"})*{1,-1})即可。
公式說明:
先用COUNTIF分別統(tǒng)計>5000、>10000的人數(shù),得到數(shù)組{8,2},再與數(shù)組{1,-1}相乘得到{8,-2},最后相加完成多條件計數(shù)。
3.統(tǒng)計不重復(fù)個數(shù)
如下圖所示,需要統(tǒng)計有多少個銷售部門。
輸入公式=SUM(1/COUNTIF(B2:B13,B2:B13)) 或者=SUMPRODUCT(1/COUNTIF(B2:B13,B2:B13))
公式說明:
統(tǒng)計區(qū)域和條件區(qū)域都是B2:B13,得到B2:B13中每個值的出現(xiàn)次數(shù){5;5;5;5;5;4;4;4;4;3;3;3},然后用1除以它們,最后相加。
公式運(yùn)用了一個數(shù)學(xué)邏輯,即任意一個數(shù)據(jù)重復(fù)出現(xiàn)N次,那么N個1/N的和為1。譬如B2:B13中包含5個銷售一部,每個銷售一部統(tǒng)計的次數(shù)都是5(對應(yīng)前方數(shù)組中的藍(lán)色數(shù)據(jù)),5個1/5得到1,實現(xiàn)了去重統(tǒng)計。
4.統(tǒng)計只出現(xiàn)過一次的數(shù)據(jù)個數(shù)
增加IF判斷,輸入公式=SUM(IF(COUNTIF(A2:A14,A2:A14)=1,1,0))。
公式說明:
COUNTIF得到一組次數(shù),用IF函數(shù)判斷,次數(shù)為1則返回1,否則返回0,最后用SUM函數(shù)求和。
5.妙用*統(tǒng)計超過15位的數(shù)字出現(xiàn)次數(shù)
統(tǒng)計身份證號或者銀行卡號等超過15位數(shù)字的次數(shù)時,應(yīng)加上通配符*即&"*",告訴Excel查找整個單元格文本,否則Excel只查找前15位數(shù)進(jìn)行統(tǒng)計,就會發(fā)生錯誤。
以上就是部落窩函數(shù)課堂第十一課——COUNTIF的所有內(nèi)容,感謝觀看。
本文配套的練習(xí)課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
MATCH:函數(shù)哲學(xué)家,找巨人做伴。新出道必學(xué)!
八大查找函數(shù)公式,輕松搞定數(shù)據(jù)中的多條件查找
版權(quán)申明:
本文作者逍遙;同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集!(建議收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!