409 | COUNTIF函數用法總結 | ||||
示範檔 | 無 | 範例檔 | 409.XLSX | 結果檔 | 無 |
一、求各種類型儲存格的個數
(1) 真空白儲存格個數: =COUNTIF(data,"=")
(2) 真空白+假空白儲存格個數: =COUNTIF(data,"") 相當於Countblank()函數
(3) 非真空白儲存格個數: =COUNTIF(data,"<>") 相當於Counta()函數
(4) 文字型儲存格個數: =COUNTIF(data,"*") 假空儲存格也是文字型儲存格
(5) 範圍內所有儲存格個數: =COUNTIF(data,"<>""")
(6) 邏輯值為TRUE的儲存格數量: =COUNTIF(data,TRUE)
小說明:
EXCEL儲存格內資料主要有以下幾類:數值型、文字型、邏輯型、錯誤值型。其中時間類型也是一種特殊的數值。文字類型的數值是文字型。
空白儲存格:是指什麼內容也沒有的儲存格,姑且稱之為真空。
假空白儲存格:是指0字元的空文字,一般是由網上下載來的或公式得來的,姑且稱之為假空白。
Date是指儲存格範圍,該參數不能是陣列。
二、求><=某個值的儲存格個數
(1) 大於50:=COUNTIF(data,">50")
(2) 等於50:=COUNTIF(data,50)
(3) 小於50 :=COUNTIF(data,"<50")
(4) 大於或等於50:=COUNTIF(data,">=50")
(5) 小於或等於50:=COUNTIF(data,"<=50")
(6) 大於E5儲存格的值:=COUNTIF(data,">"&$E$5)
(7) 等於E5儲存格的值:=COUNTIF(data,$E$5)
(8) 小於E5儲存格的值:=COUNTIF(data,"<"&$E$5)
(9) 大於或等於E5儲存格的值:=COUNTIF(data,">="&$E$5)
(10) 小於或等於E5儲存格的值:=COUNTIF(data,"<="&$E$5)
三、等於或包含某N個特定字元的儲存格個數
(1) 兩個字元:=COUNTIF(data,"??")
(2) 兩個字元並且第2個是B:=COUNTIF(data,"?B")
(3) 包含B:=COUNTIF(data,"*B*")
(4) 第2個字元是B:=COUNTIF(data,"?B*")
(5) 等於「你好」:=COUNTIF(data,"你好")
(6) 包含D3儲存格的內容:=COUNTIF(data,"*"&D3&"*")
(7) 第2字是D3儲存格的內容:=COUNTIF(data,"?"&D3&"*")
注意:Countif()函數對英文字母不區分大小寫,萬用字元只對文字有效。
四、兩個條件求個數
(1) >10並且<=15:=SUM(COUNTIF(data,">"&{10,15})*{1,-1})
(2) >=10並且<15:=SUM(COUNTIF(data,">="&{10,15})*{1,-1})
(3) >=10並且<=15:=SUM(COUNTIF(data,{">=10",">15"})*{1,-1})
(4) >10並且<15:=SUM(COUNTIF(data,{">10",">=15"})*{1,-1})
注意:一般多條件計數使用SUMPRODUCT函數,以上方法較少使用,僅供參考。
補充:三個範圍計數:
三個範圍中>=60:=SUM(COUNTIF(INDIRECT({"a46:a48","b48:b50","c47:c48"}),">=60"))
五、各種特殊統計要求的計算 A2:A32 為存放資料的範圍
(1) 非空文字(僅包括可鍵入文字): =COUNTIF(A2:A32,">=!")
(2) 所有非空文字:=COUNTIF(A2:A32,">=!")+COUNTIF(A2:A32,">="&CHAR(1))-COUNTIF(A2:A32,">= ") 或 {=SUM(COUNTIF(A2:A32,">="&{"!"," "})*{1,-1})+COUNTIF(A2:A32,">="&CHAR(1))}
(3) 全部可見儲存格:{=SUM(N(IF(ISERROR(A2:A32),1,SUBSTITUTE(A2:A32," ",""))<>""))}
(4) 有效可見儲存格:=COUNTIF(A2:A32,">=!")+COUNTIF(A2:A32,">="&CHAR(1))-COUNTIF(A2:A32,">= ")+COUNT(A2:A32)
(5) 全部不見儲存格(真空+空格+空文字):=COUNTIF(A2:A32,"")-COUNTIF(A2:A32,">=!")+COUNTIF(A2:A32,">= ")
(6) 空格:=COUNTIF(A2:A32,">= ")-COUNTIF(A2:A32,">=!")
(7) 空文字:=COUNTIF(A2:A32,"")-COUNTIF(A2:A32,"=")
(8) 邏輯與錯誤:=COUNTIF(A2:A32,"<>")-COUNTIF(A2:A32,"*")-COUNT(A2:A32)
留言列表