Excel CountIf函數(shù)的使用方法,，包含與If,、Sum組合實現(xiàn)統(tǒng)計不重復(fù)與多條件實例

hdzgx 2022-01-02

展開全文

在 Excel 中，CountIf函數(shù)用于統(tǒng)計指定范圍內(nèi)滿足某個條件的單元格個數(shù),；它只有兩個參數(shù),，一個是統(tǒng)計范圍，另一個是條件,，其中條件不能超過 255 個字符,，如果超過需要分開。條件中僅為 *、?,、>,、< 等，可以完成一些特殊的統(tǒng)計,。

CountIf函數(shù)如果要實現(xiàn)多條件的的統(tǒng)計需要與 If,、OffSet、Sum函數(shù)組合,，并且有時條件需用數(shù)組,，例如：Sum + If + CountIf函數(shù)組合實現(xiàn)統(tǒng)計不為空且不包含重復(fù)值的個數(shù)，Sum + CountIf + 數(shù)組條件實現(xiàn)同一范圍內(nèi)滿足兩個條件的統(tǒng)計,。

一,、Excel CountIf函數(shù)語法

1、表達式：COUNTIF(Range, Criteria)

中文表達式：COUNTIF(統(tǒng)計區(qū)域, 條件)

2,、說明：

A,、Range 可以是數(shù)組或單元格的引用。

B,、條件可以是單個數(shù)字或文本,，也可以為表達式或函數(shù)；但條件中不能超過 255 個字符,，否則會返回錯誤,；如果條件超過 255 個字符，可以把它們拆分為多個長字符串,，然后用 Concatenate 函數(shù)或連接運算符 & 把它們連接起來,，例如："長字符中1……"&"長字符串2……”。另外,，條件忽略字母大小寫,。

C、在條件中,，可以使用通配符問號（?）和星號（*）,，問號表示任意一個字符，星號表示任意一個或一串字符,；如果要查找問號或星號,，需要在它們前面加轉(zhuǎn)義字符~；如：查找問號,，表達式應(yīng)該這樣寫 ~?,；查找星號，表達式應(yīng)該這樣寫 ~*,。

D,、統(tǒng)計文本個數(shù)時，不能包含前導(dǎo)與尾部空格，也不能出現(xiàn)單雙引號不一致和非打印字符,；否則,，CountIf函數(shù)可能返回不可預(yù)期的值。統(tǒng)計文本前或后有空格時,，可以用Trim函數(shù)把它們?nèi)サ?；有非打印字符時，可以用Clean函數(shù)把它們?nèi)サ?,。

二,、Excel CountIf函數(shù)的使用方法及實例

（一）條件為文本和統(tǒng)計空值的實例

1,、雙擊 D2 單元格,，把公式 =COUNTIF(C1:C11,"經(jīng)理") 復(fù)制到 D2，按回車,，返回統(tǒng)計結(jié)果 3,；雙擊 D2，把雙引號內(nèi)的“經(jīng)理”去掉,，按回車,，返回統(tǒng)計結(jié)果 1；操作過程步驟,，如圖1所示：

Excel CountIf函數(shù)的使用方法及實例

圖1

2,、公式說明：

在 =COUNTIF(C1:C11,"經(jīng)理") 中，C1:C11 為統(tǒng)計區(qū)域,，"經(jīng)理"為條件,，意思是：在 C1 至 C11 中統(tǒng)計“經(jīng)理”的個數(shù)；當條件為 "" 時,，統(tǒng)計空值的個數(shù),，C1:C11 中只有一個空單元格，因此統(tǒng)計結(jié)果為 1,。

（二）條件為表達式的實例

1,、假如要統(tǒng)計水果銷量大于等于 1000 的個數(shù)。雙擊 D8 單元格,，把公式 =COUNTIF(D2:D7,">=1000") 復(fù)制到 D8,，按回車，返回統(tǒng)計結(jié)果 5,；操作過程步驟,，如圖2所示：

Excel CountIf函數(shù)條件為表達式大于小于等于的實例

圖2

2、公式 =COUNTIF(D2:D7,">=1000") 中的條件為 ">=1000",，意思是：在 D2:D7 中統(tǒng)計數(shù)值大于等于 1000 的個數(shù),；另外，">=1000" 還可以這樣寫 ">"&"1000"，即把大于號與數(shù)字分開寫,，再用連接運算符把它們連接起來,。

（三）條件中有函數(shù)的實例

1、假如要統(tǒng)計水果銷量大于平均值的個數(shù),。雙擊 D8 單元格,，把公式 =COUNTIF(D2:D7,">"&AVERAGE(D2:D7)) 復(fù)制到 D8，按回車,，返回統(tǒng)計結(jié)果 3,；操作過程步驟，如圖3所示：

Excel CountIf函數(shù)條件中有函數(shù)Average的實例

圖3

2,、公式 =COUNTIF(D2:D7,">"&AVERAGE(D2:D7)) 中的條件為 ">"&AVERAGE(D2:D7),，條件中的 AVERAGE(D2:D7) 用于求 D2:D7 的平均值（結(jié)果為 1790.5），然后用 & 把大于號跟它連起來組成條件,，即 ">1790.5",。

（四）條件中有通配符 ? 和 * 的實例

1、假如要統(tǒng)計姓李的員工個數(shù),。雙擊 D2 單元格,，把公式 =COUNTIF(A2:A10,"李??") 復(fù)制到 D2，按回車,，返回統(tǒng)計結(jié)果 2,；雙擊 D2，把公式中的 ?? 改為 *,，按回車,，也返回統(tǒng)計結(jié)果 2；操作過程步驟,，如圖4所示：

Excel CountIf函數(shù)條件中有通配符 ? 和 * 的實例

圖4

2,、公式 =COUNTIF(A2:A10,"李??") 中的條件為 "李??"，意思是“以李字開頭且李字后僅有兩個字”,；當把條件改為"李*"后,，意思是“以李字開頭即可”，李字后可以有一個或多個任意字符,。條件中有通配符 ? 和 * 的情況在前面的篇章中已介紹多次,，如果想了解更多的查找方式，可以參考《Excel HLookUp函數(shù)的使用方法,，包含首行需排序,、返回#N/A及與Match組合/非首行實例》一文。

3,、條件為 * 和 ?* 的實例

A,、條件為 * 的實例,。雙擊 E10 單元格，把公式 =COUNTIF(E1:E9,"*") 復(fù)制到 E10,，按回車,，返回統(tǒng)計結(jié)果 2；雙擊 E9,，把返回空（""）的公式去掉,，按回車，統(tǒng)計結(jié)果變?yōu)?1,；再把 E1 中的文字刪除,，統(tǒng)計結(jié)果變?yōu)?0，演示如圖5所示：

Excel CountIf函數(shù)

圖5

B,、條件為 ?* 的實例,。雙擊 E10 單元格，把公式 =COUNTIF(E1:E9,"?*") 復(fù)制到 E10,，按回車,，返回統(tǒng)計結(jié)果 1,；雙擊 E9,，把 "" 改為" "，統(tǒng)計結(jié)果變?yōu)?2,；再把 E1 中的文字刪除,，統(tǒng)計結(jié)果變?yōu)?1，演示如圖6所示：

Excel CountIf函數(shù)條件為 * 和 ?* 的實例

圖6

* 和 ?* 的區(qū)別：* 可以是一個或多字符（包括空值""）,，?* 至少要有兩個字或空值"",，演示中已經(jīng)說明這兩個問題。

（五）幾個特殊的實例

1,、雙擊 B11 單元格,，把公式 =COUNTIF(B2:B10,">=!") 復(fù)制到 B11，按回車,，返回統(tǒng)計結(jié)果 7,；雙擊 B10，它是一個空單元格,，再雙擊 B9,，里面有一個公式，雙擊 B7,，按空格鍵在“財務(wù)部”前加一個空格,，按回車，統(tǒng)計結(jié)果變?yōu)?7,；把公式 =COUNTIF(B2:B10,"><") 復(fù)制到 B12,，按回車,，返回統(tǒng)計結(jié)果 6，雙擊 B7,，把“財務(wù)部”前的空格刪除,，B11 和 B12 中的統(tǒng)計結(jié)果都變?yōu)?7；雙擊 B13,，把公式 =COUNTIF(B2:B10,">""") 復(fù)制到 B13,，按回車，返回統(tǒng)計結(jié)果 7,，雙擊 B7,，再次在“財務(wù)部”前加一個空格，B11,、B12 和 B13 的統(tǒng)計結(jié)果都變?yōu)?7,；雙擊 C11，把公式 =COUNTIF(B2:B10,"<>") 復(fù)制到 C11,，按回車,，返回統(tǒng)計結(jié)果 8；操作過程步驟,，如圖7所示：

Excel CountIf函數(shù)統(tǒng)計空格,、文本、不包含公式的實例

圖7

2,、公式說明：

公式 =COUNTIF(B2:B10,">=!"),、 =COUNTIF(B2:B10,"><") 和 =COUNTIF(B2:B10,">""") 的統(tǒng)計結(jié)果一樣，它們都不包含有公式的單元格,、空單元格和文字前有空格的單元格,；而公式 =COUNTIF(B2:B10,"<>") 包含有公式的單元格，不包含空單元格,。

三,、Excel CountIf函數(shù)的擴展使用實例

（一）Sum + If + CountIf函數(shù)組合實現(xiàn)統(tǒng)計不為空且不包含重復(fù)值的個數(shù)

1、雙擊 B9 單元格,，把公式 =SUM(IF(B2:B8<>"",1/COUNTIF(B2:B8,B2:B8))) 復(fù)制到 B9,，按 Ctrl + Shift + 回車，返回統(tǒng)計結(jié)果 3,，操作過程步驟,，如圖8所示：

Excel Sum + If + CountIf函數(shù)組合實現(xiàn)統(tǒng)計不為空且不包含重復(fù)值的個數(shù)

圖8

2、公式 =SUM(IF(B2:B8<>"",1/COUNTIF(B2:B8,B2:B8))) 說明：

A,、B2:B8 用于以數(shù)組形式返回 B2 到 B8 中的所有值,，即 {"財務(wù)部";"銷售部";"行政部";"銷售部";"行政部";"財務(wù)部";"行政部"}。

B,、COUNTIF(B2:B8,B2:B8) 執(zhí)行時,，第一次從 B2:B8 中取出 B2（即“財務(wù)部”）,，然后統(tǒng)計 B2 在 B2:B8 中的個數(shù)，結(jié)果為 2,；第二次統(tǒng)計 B3 在 B2:B8 中的個數(shù),，結(jié)果為 2；其它的以此類推,，最后返回 {2;2;3;2;3;2;3},。

C、1/COUNTIF(B2:B8,B2:B8) 變?yōu)?1/{2;2;3;2;3;2;3},，接著用 1 除以數(shù)組中的每一個元素,，結(jié)果返回{0.5;0.5;0.333333333333333;0.5;0.333333333333333;0.5;0.333333333333333}；這一步的作用是把重復(fù)值的總數(shù)看為 1,，例如重復(fù)次為 2 的,，每個 1/2 等于 0.5，進一步計算再把兩個 0.5 加起來,，恰好是 1,，即去掉一個重復(fù)項。

D,、B2:B8<>"" 意思是：每次從 B2:B8 中取出一個元素,，如果不為空，返回 True,，否則返回 False,；最后返回{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}。

E,、則公式變?yōu)?=SUM(IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE},{0.5;0.5;0.333333333333333;0.5;0.333333333333333;0.5;0.333333333333333}))，接著從 If 的條件數(shù)組中取出第一個元素 TRUE,，由于為真,，所以返回第二個數(shù)組中對應(yīng)的元素 0.5；直到取完條件數(shù)組中的每個,，最后返回{0.5;0.5;0.333333333333333;0.5;0.333333333333333;0.5;0.333333333333333},；則公式變?yōu)?=SUM({0.5;0.5;0.333333333333333;0.5;0.333333333333333;0.5;0.333333333333333})，最后用 Sum 對數(shù)組中的每個元素求和,，結(jié)果為 3,。

（二）OffSet + CountIf函數(shù)組合使用實現(xiàn)分段統(tǒng)計

OffSet + CountIf 實現(xiàn)分段統(tǒng)計請查看《Excel OffSet函數(shù)的使用方法，含與Sum,、Match,、CountIf、If,、Or,、Row組合實現(xiàn)動態(tài)求和,、一個重復(fù)多個、分段統(tǒng)計和斷碼檢查》一文,。

（三）CountIf函數(shù)多條件統(tǒng)計

1,、假如要統(tǒng)計銷售額在 5 萬到 8 萬的銷售員個數(shù)。雙擊 C9 單元格,，把公式 =SUM(COUNTIF(C2:C8,{">=50000",">80000"})*{1,-1}) 復(fù)制到 C9,，按回車，返回統(tǒng)計結(jié)果 3,，操作過程步驟,，如圖9所示：

Excel CountIf函數(shù)多條件統(tǒng)計實例

圖9

2、公式 =SUM(COUNTIF(C2:C8,{">=50000",">80000"})*{1,-1}) 說明：

A,、C2:C8 為 CountIf 的統(tǒng)計范圍,，{">=50000",">80000"} 為條件，該條件為數(shù)組,，共包含兩個條件,，第一個條件為 ">=50000"，第二個條件為 ">80000",，意思是：先在 C2:C8 中統(tǒng)計出大于等于 50000 的個數(shù),，結(jié)果為 4；再統(tǒng)計出大于 80000 的個數(shù),，結(jié)果為 1,。

B、則公式變?yōu)?=SUM({4,1}*{1,-1}),，接著,，把兩個數(shù)組的對應(yīng)元素相乘，公式變?yōu)?=SUM({4,-1}),，最后對數(shù)組求和,，結(jié)果為 3。這個方法的關(guān)鍵為：先統(tǒng)計出所有大于等于 5 萬的個數(shù),，再統(tǒng)計出大于 8 萬的個數(shù),，然后用前者減后者。

3,、統(tǒng)計其它范圍的公式分別為：

A,、大于 50000 且小于 80000：=SUM(COUNTIF(C2:C8,{">50000",">=80000"})*{1,-1})

B、大于等于 50000 且小于 80000：=SUM(COUNTIF(C2:C8,{">=50000",">=80000"})*{1,-1}) 或 =SUM(COUNTIF(C2:C8,">="&{50000,80000})*{1,-1})

C,、大于 50000 且小于等于 80000：=SUM(COUNTIF(C2:C8,{">50000",">80000"})*{1,-1}) 或 =COUNTIF(C2:C8,">50000"）- COUNTIF(C2:C8,">80000"）