試算表 COUNTIF 函數用法教學

COUNTIF 函數是什麼？

在試算表中，COUNTIF 函數用於計算某一範圍內符合特定條件的儲存格數量，可以計算包含數字、日期、文字、符號或空白的儲存格。

COUNTIF函數公式： =COUNTIF(範圍, 標準)

• 範圍 (range)：要計數的儲存格範圍。

• 標準 (criteria)：決定哪些儲存格將被計算的條件。它可以是數字、符號、字串、儲存格參照、或數學式。

請注意：COUNTIF 僅適用於單一標準。如果您需要設定多個標準，請使用 COUNTIFS 函數。

試算表 COUNTIF 函數用法範例

情況一：COUNTIF 大於、等於或小於

假設在 C2～C12 儲存格中有一份銷售數據列表，您想要計算有多少筆訂單的銷售額超過10,000元。

請輸入公式 =COUNTIF(B2:B12,">10,000")。答案為7。

小技巧：與儲存格參照進行比較

當與儲存格參照進行比較時，將運算符號放在兩個引號中間，並在儲存格參照之前加上一個「&」符號。例如，您想計算有多少筆訂單有超過B15儲存格裡的目標銷售額，請使用此公式：=COUNTIF(B2:B12,">"&B15)。答案為4。

情況二：COUNTIF 完全相符的字串

承上題，我們想要計算由 William 的成交訂單數。在 A2～A12 儲存格中有一份名單，我們想要計算此名單中 William 出現的次數。

使用公式：=COUNTIF(A2:A12,"william")，計算範圍 A2:A12 中完全符合字串"william"的儲存格數量。答案為3。

請注意：COUNTIF 函數不在意字母大小寫，也就是說 "william" 和 "WILLIAM" 被視為相同。

情況三：COUNTIF 使用萬用字元的字串

萬用字元可以讓您在設定文字相關的標準時更加靈活。您可以在標準中使用萬用字元來計算包含特定單字、詞組或字母的儲存格數量。

請注意：萬用字元僅能套用於文字，不適用於數字。

星號（*）

只要儲存格中有提到某個單字或詞組，星號（*）都會計算在內，不管它在儲存格內的位置或排序。

範例：假設您有一份顧客評論表單，您想要計算有提到「不滿意（dissatisfied）」這個詞的評論數量。您可以使用公式：

=COUNTIF(A2:A7,"*dissatisfied*")

此公式計算範圍 A2:A7 中有提到"dissatisfied"這個詞的所有儲存格。星號允許這個詞的前面或後面存在其他字元。得出答案為2。

小技巧：計算以特定字詞開始或結束的儲存格

如果您想要計算以某個字詞開頭或結尾的儲存格，而不管這個字詞有幾個字母，可以使用以下公式：
=COUNTIF(範圍,"Pro*") 來計算以"Pro"開頭的儲存格，=COUNTIF(範圍,"*ing") 來計算以"ing"結尾的儲存格。

問號（?）

問號（?）代表著某一個字元，包括空格也算，適用於計算具有明確字元數量的儲存格。

範例：在零售庫存管理的情境，假設您正在尋找有七個字元的產品編號，並且最後兩個字元是BL（代表藍色）。使用公式： =COUNTIF(A2:A11, "?????BL") 。得出答案為4。這樣做可以幫助您有效率地根據顏色管理庫存。

小技巧：要計算「字面上」包含問號（？）或星號（*）這個符號的儲存格，請在公式中的萬用字元前使用波浪號（~）躲避。例如，=COUNTIF(範圍,"~?") 將計算範圍內包含問號符號的所有儲存格。

情況四：COUNTIF 空白和非空白儲存格

您可以在試算表中使用 COUNTIF 函數來計算指定範圍內空白或非空白儲存格的數量。

COUNTIF 計算非空白儲存格

使用公式：=COUNTIF(範圍,"<>")

在試算表中，「<>」這個運算符號代表「不等於」，因此這個公式代表計算「不等於空白」的儲存格，也就是計算指定範圍內包含任何值的所有非空白儲存格，而不管這個值是數字、文字還是日期。

小技巧：如果您想計算只含有文字（包括空白字元）的非空白儲存格，請使用 =COUNTIF(範圍,"*")。這樣做可以確保排除掉含有數字或日期等值的儲存格。

COUNTIF 計算空白儲存格

相反地，要計算範圍內的所有空白儲存格，您可以使用公式：=COUNTIF(範圍,"")

請注意：COUNTBLANK 函數也可以達到相同目的。只需使用 =COUNTBLANK(範圍) 就可以獲得與 =COUNTIF(範圍,"") 相同的結果。

然而，COUNTIF 和 COUNTBLANK 函數都會把套公式後得到的空白字元（""）視為非空白，這在計算公式結果時可能會出現問題。如果要排除空白字元，請使用 =COUNTIF(範圍,"<>") - COUNTIF(範圍,"")。或者可以根據您的需求考慮使用其他函數，像是 COUNTA 或 SUMPRODUCT 函數。

情況五：COUNTIF 日期

您也可以使用 COUNTIF 來分析日期資料，幫助您有效率地追蹤與管理日期相關資料。

以下是一些常用公式：

• 等於指定日期：=COUNTIF(範圍,"3/1/2024")

• 大於或等於指定日期：=COUNTIF(範圍,">=3/1/2024")

• 今天：=COUNTIF(範圍,TODAY())

• 早於今天：=COUNTIF(範圍,"<"&TODAY())

• 今天起一周內：=COUNTIF(範圍,"="&TODAY()+7)

• 特定日期範圍內：=COUNTIF(範圍,">=3/1/2024")-COUNTIF(範圍,">6/30/2024")

COUNTIF 包含兩個標準

COUNTIF 函數只能指定一個標準。如果您要指定兩個或以上標準，請使用 COUNTIFS 函數。然而，您仍然可以透過將多個 COUNTIF 函數組合在一個公式中簡單地達到目的。

例如，要計算介於兩個數字之間的值的數量，例如大於10但小於50的值，您可以使用公式 =COUNTIF(範圍,">10")-COUNTIF(範圍,">=50")。此外，要計算多個「或」標準，例如在旅遊目的地列表中計算"London"或"Manchester"的出現次數，您可以使用 =COUNTIF(範圍,"London")+COUNTIF(範圍,"Manchester")。

COUNTIF 最佳做法與建議

1. 使用命名範圍：與其每次都手動選取範圍，不如使用命名範圍。不僅可以讓公式更簡單好讀，也能減少錯誤。命名範圍可以來自當前的工作表、同一個活頁簿中的另一個工作表、或來自另一個活頁簿。請確保您所引用的活頁簿是打開的。

小技巧：如何命名範圍

• 方法一（最快）：

(1) 選擇儲存格。
(2) 在名稱欄位中編輯名稱。
(3) 按下“Enter”鍵。

• 方法二：

(1) 選擇儲存格。
(2) 到功能區「公式」分頁，點選「名稱」>「定義名稱」。
(3) 在對話框中，輸入範圍名稱、範圍和儲存格參照，並可寫下註解。
(4) 點擊「確定」。

2. 確保絕對參照：在將 COUNTIF 公式複製到其他儲存格時，請確保範圍參照是絕對位置（例如，$A$1:$A$10），以避免公式跑掉而導致錯誤。

3. 確保標準的格式正確：將標準參數放入兩個引號中間，並使用適當的萬用字元，以避免 COUNTIF 錯誤。

4. 使用樣本數據進行測試：在將 COUNTIF 應用於大型資料集之前，先使用樣本數據進行測試，以確保它能有效產生所需的結果，這樣做有助於及早發現潛在問題。

5. 記錄您的公式：尤其是在設定複雜的標準或處理大型資料集時，紀錄公式有助於清楚理解 COUNTIF 函數背後的邏輯。這樣做也能幫助排除錯誤，也讓自己和他人更容易維護和修正試算表。