如何在 Excel COUNTIF 和 COUNTIFS 中使用多個條件

如何在 Excel COUNTIF 和 COUNTIFS 中使用多個條件

Excel 有許多功能,用戶需要指定一個或多個條件才能獲得結果。例如,如果要根據多個條件對單元格進行計數,可以使用 Excel 中的 COUNTIF 或 COUNTIFS 函數。

本教程介紹了在 Excel 的 COUNTIF 和 COUNTIFS 函數中使用單個或多個條件的各種方法。

雖然我將在本教程中主要關注 COUNTIF 和 COUNTIFS 函數,但所有這些示例也可用於其他將多個條件作為輸入的 Excel 函數(例如 SUMIF、SUMIFS、AVERAGEIF 和 AVERAGEIFS)。

Excel COUNTIF 和 COUNTIFS 函數簡介

讓我們首先了解如何在 Excel 中使用 COUNTIF 和 COUNTIFS 函數。

Excel COUNTIF 函數(採用單一條件)

Excel COUNTIF 函數最適合您希望基於單個條件對單元格進行計數的情況。如果要基於多個條件進行計數,請使用 COUNTIFS 函數。

句法

=COUNTIF(範圍,條件)

輸入參數

  • 範圍 –  您要計算的單元格範圍。
  • 條件必須針對要計數的單元格範圍評估的標準。

Excel COUNTIFS 函數(採用多個條件)

Excel COUNTIFS 函數最適合您要根據多個條件對單元格進行計數的情況。

句法

=COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]…)

輸入參數

  • criteria_range1 – 您要根據標準 1 評估的單元格範圍。
  • criteria1 – 您要評估的標準為 criteria_range1 確定要計算的單元格。
  • [criteria_range2] – 您要根據標準 2 評估的單元格範圍。
  • [criteria2] – 您要評估的條件為 criteria_range2 以確定要計數的單元格。
相關問題  在 Excel 上下顛倒、翻轉數據(反轉列/行中的數據順序)

現在讓我們看一些在 Excel 的 COUNTIF 函數中使用多個條件的示例。

在 Excel COUNTIF 函數中使用 NUMBER 條件

#1 當標準等於一個值時計數單元格

要獲取條件參數等於指定值的單元格計數,您可以直接輸入條件或使用包含條件的單元格引用。

下面是一個示例,我們計算包含數字 9 的單元格(這意味著條件參數等於 9)。這是公式:

=COUNTIF($B$2:$B$11,D3)

在 Excel 函數中使用多個條件 - 數字等於

在上面的示例中(在圖片中),標準位於單元格 D3 中。您也可以將條件直接輸入到公式中。例如,您還可以使用:

=COUNTIF($B$2:$B$11,9)

#2 當條件大於值時計數單元格

要獲取值大於指定值的單元格計數,我們使用大於運算符(“>”)。我們可以直接在公式中使用它,也可以使用具有條件的單元格引用。

每當我們在 Excel 的條件中使用運算符時,我們都需要將它放在雙引號內。例如,如果條件大於 10,那麼我們需要輸入“>10”作為條件(見下圖):

這是公式:

=COUNTIF($B$2:$B$11,”>10″)

在 Excel COUNTIF 函數中使用多個條件 - 大於

您還可以在單元格中包含條件並將單元格引用用作條件。在這種情況下,您不需要將條件放在雙引號中:

=COUNTIF($B$2:$B$11,D3)

在 Excel COUNTIF 函數中使用多個條件 - - 大於單元格引用中的條件

當您希望條件位於單元格中但不希望與操作員一起使用時,也可能存在這種情況。例如,您可能希望單元格 D3 的數字為 10 而不是 >10。

在這種情況下,您需要創建一個條件參數,它是運算符和單元格引用的組合(見下圖):

=COUNTIF($B$2:$B$11,”>”&D3)

在 Excel COUNTIF 函數中使用多個條件 - 大於運算符和單元格引用注意:當您組合運算符和單元格引用時,運算符總是用雙引號引起來。運算符和單元格引用由與號 (&) 連接。

#3 當標準小於一個值時計數單元格

要獲取值小於指定值的單元格計數,我們使用小於運算符(“<”)。我們可以直接在公式中使用它,也可以使用具有條件的單元格引用。

每當我們在 Excel 的條件中使用運算符時,我們都需要將它放在雙引號內。例如,如果標準是數量應該小於 5,那麼我們需要輸入“<5”作為標準(見下圖):

=COUNTIF($B$2:$B$11,”<5”)

在 Excel COUNTIF 函數中使用多個條件 - 小於

您還可以在單元格中包含條件並將單元格引用用作條件。在這種情況下,您不需要將標準放在雙引號中(見下圖):

=COUNTIF($B$2:$B$11,D3)

具有多個條件的 Excel COUNTIF 函數 - 單元格引用中的小於條件

此外,您可能希望條件位於單元格中,但不希望與操作員一起使用。例如,您可能希望單元格 D3 的數字為 5 而不是 <5。

在這種情況下,您需要創建一個條件參數,它是運算符和單元格引用的組合:

=COUNTIF($B$2:$B$11,”<“&D3)

在 Excel COUNTIF 函數中使用小於運算符

注意:當您組合運算符和單元格引用時,運算符總是用雙引號引起來。運算符和單元格引用由與號 (&) 連接。

#4 計算具有多個標準的單元格 – 兩個值之間

要獲得兩個值之間的值的計數,我們需要在 COUNTIF 函數中使用多個條件。

以下是執行此操作的兩種方法:

方法一:使用 COUNTIFS 函數

COUNTIFS 函數可以將多個條件作為參數處理,並且僅當所有條件都為 TRUE 時才對單元格進行計數。要計算兩個指定值之間的值(例如 5 和 10)的單元格,我們可以使用以下 COUNTIFS 函數:

=COUNTIFS($B$2:$B$11,”>5″,$B$2:$B$11,”<10″)

在 Excel COUNTIFS 函數中使用多個條件 - 條件之間

注意:上述公式不計算包含 5 或 10 的單元格。如果要包括這些單元格,請使用大於等於 (>=) 和小於等於 (<=) 運算符。這是公式:

=COUNTIFS($B$2:$B$11,”>=5″,$B$2:$B$11,”<=10″) 

您還可以在單元格中包含這些條件,並將單元格引用用作條件。在這種情況下,您不需要將標準放在雙引號中(見下圖):

具有多個條件的 Excel COUNTIFS 函數 - 單元格引用中的條件之間

您還可以使用單元格引用和運算符的組合(在公式中直接輸入運算符)。將運算符和單元格引用組合在一起時,運算符總是用雙引號引起來。運算符和單元格引用由與號 (&) 連接。

Excel COUNTIFS 函數 - 在條件運算符和單元格引用之間

方法 2:使用兩個 COUNTIF 函數

如果您有多個條件,則可以使用 COUNTIFS 或創建 COUNTIF 函數的組合。下面的公式也會做同樣的事情:

=COUNTIF($B$2:$B$11,”>5″)-COUNTIF($B$2:$B$11,”>10″)

在上面的公式中,我們首先找到值大於 5 的單元格數,然後減去值大於 10 的單元格數。這將使我們得到結果為 5(即具有值大於 5 且小於等於 10)。

在 Excel COUNTIF 函數中使用多個條件 - 在條件兩個 countif 之間

如果您希望公式同時包含 5 和 10,請改用以下公式:

相關問題  如何在 Excel 中將英寸轉換為 MM、CM 或英尺?簡易公式

=COUNTIF($B$2:$B$11,”>=5”)-COUNTIF($B$2:$B$11,”>10”)

如果您希望公式從計數中排除“5”和“10”,請使用以下公式:

=COUNTIF($B$2:$B$11,”>=5″)-COUNTIF($B$2:$B$11,”>10″)-COUNTIF($B$2:$B$11,10)

您可以在單元格中包含這些條件並使用單元格引用,也可以使用運算符和單元格引用的組合。

在 Excel 函數中使用 TEXT 條件

#1 當條件等於指定文本時計數單元格

要計算包含與指定文本完全匹配的單元格,我們可以簡單地使用該文本作為條件。例如,在數據集中(如下圖所示),如果我想計算所有名稱為 Joe 的單元格,我可以使用以下公式:

=COUNTIF($B$2:$B$11,”Joe”)

由於這是一個文本字符串,我需要將文本條件放在雙引號中。

在 Excel COUNTIF 函數中使用多個文本條件

您還可以在單元格中包含條件,然後使用該單元格引用(如下所示):

=COUNTIF($B$2:$B$11,E3)

在 Excel COUNTIFS 函數中使用多個文本條件

注意:如果條件或條件範圍中有前導/尾隨空格,您可能會得到錯誤的結果。確保在使用這些公式之前清理數據。

#2 當條件不等於指定文本時計數單元格

與我們在上面的示例中看到的類似,您還可以計算不包含指定文本的單元格。為此,我們需要使用不等於運算符 (<>)。

假設您要計算所有不包含名稱 JOE 的單元格,以下是可以執行此操作的公式:

=COUNTIF($B$2:$B$11,”<>Joe”)

在 Excel COUNTIF 函數中使用多個條件 - 文本條件不等於

您還可以在單元格中包含條件並將單元格引用用作條件。在這種情況下,您不需要將標準放在雙引號中(見下圖):

=COUNTIF($B$2:$B$11,E3)

在 Excel COUNTIF 函數中使用多個條件 - 文本條件不等於單元格引用

當您希望條件位於單元格中但不希望與操作員一起使用時,也可能存在這種情況。例如,您可能希望單元格 D3 具有名稱 Joe 而不是 <>Joe。

在這種情況下,您需要創建一個條件參數,它是運算符和單元格引用的組合(見下圖):

=COUNTIF($B$2:$B$11,”<>”&E3)

在 Excel COUNTIF 函數中使用多個條件 - 文本條件不等於單元格引用和運算符

將運算符和單元格引用組合在一起時,運算符總是用雙引號引起來。運算符和單元格引用由與號 (&) 連接。

在 Excel COUNTIF 和 COUNTIFS 函數中使用 DATE 條件

Excel 將日期和時間存儲為數字。所以我們可以像使用數字一樣使用它。

#1 當條件等於指定日期時計數單元格

要獲取包含指定日期的單元格的計數,我們將使用等於運算符 (=) 以及日期。

要使用日期,我建議使用 DATE 函數,因為它消除了日期值出錯的任何可能性。因此,例如,如果我想使用 2015 年 9 月 1 日的日期,我可以使用如下所示的 DATE 函數:

=DATE(2015,9,1)

儘管存在地區差異,但此公式將返回相同的日期。例如,根據美國日期語法,01-09-2015 將是 2015 年 9 月 1 日,根據英國日期語法,將是 2015 年 1 月 9 日。但是,此公式將始終返回 2105 年 9 月 1 日。

這是計算包含日期 02-09-2015 的單元格數量的公式:

=COUNTIF($A$2:$A$11,DATE(2015,9,2))

Excel COUNTIF 函數 - 使用多個日期條件

#2 當條件在指定日期之前或之後時計數單元格

要計算包含指定日期之前或之後日期的單元格,我們可以使用小於/大於運算符。

例如,如果我想計算所有包含 2015 年 9 月 2 日之後日期的單元格,我可以使用以下公式:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))

在 Excel COUNTIF 函數中使用多個條件 - 之後的日期條件

同樣,您還可以計算指定日期之前的單元格數。如果要在計數中包含日期,請使用“等於”運算符以及“大於/小於”運算符。

您還可以使用包含日期的單元格引用。在這種情況下,您需要使用與號 (&) 將運算符(在雙引號內)與日期結合起來。

請參見下面的示例:

=COUNTIF($A$2:$A$11,”>”&F3)

在 Excel COUNTIF 函數中使用多個條件 - 使用單元格引用和 & 符號的日期條件

#3 計算具有多個條件的單元格 – 兩個日期之間

要獲得兩個值之間的值的計數,我們需要在 COUNTIF 函數中使用多個條件。

我們可以使用兩種方法來做到這一點——一個單獨的 COUNTIFS 函數或兩個 COUNTIF 函數。

方法一:使用 COUNTIFS 函數

COUNTIFS 函數可以將多個條件作為參數,並且僅當所有條件都為 TRUE 時才對單元格進行計數。要計算兩個指定日期(比如 9 月 2 日和 9 月 7 日)之間值的單元格,我們可以使用以下 COUNTIFS 函數:

=COUNTIFS($A$2:$A$11,”>”&DATE(2015,9,2),$A$2:$A$11,”<“&DATE(2015,9,7))

Excel COUNTIF 函數中使用多個條件 - 前後日期條件

上面的公式不計算包含指定日期的單元格。如果您還想包括這些日期,請使用大於等於 (>=) 和小於等於 (<=) 運算符。這是公式:

=COUNTIFS($A$2:$A$11,”>=”&DATE(2015,9,2),$A$2:$A$11,”<=”&DATE(2015,9,7))

您還可以在單元格中包含日期並使用單元格引用作為條件。在這種情況下,您不能讓操作員在單元格中包含日期。您需要在公式中手動添加運算符(用雙引號括起來)並使用與號 (&) 添加單元格引用。見下圖:

相關問題  Windows 10 PC 和筆記本電腦沒有聲音 [已修復]

=COUNTIFS($A$2:$A$11,”>”&F3,$A$2:$A$11,”<“&G3)

在 Excel COUNTIF 函數中使用多個條件 - 單元格引用前後的日期條件

方法 2:使用 COUNTIF 函數

如果您有多個條件,您可以使用一個 COUNTIFS 函數或創建兩個 COUNTIF 函數的組合。下面的公式也可以解決問題:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

Excel COUNTIF 函數中使用多個條件 - 使用 countif 前後的日期條件不等於

在上面的公式中,我們首先找到日期在 9 月 2 日之後的單元格數,然後減去日期在 9 月 7 日之後的單元格數。這將得出結果為 7(即具有日期的單元格數9 月 2 日之後和 9 月 7 日當天或之前)。

如果您不希望公式同時計算 9 月 2 日和 9 月 7 日,請改用以下公式:

=COUNTIF($A$2:$A$11,”>=”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

Excel COUNTIF 函數中使用多個條件 - 使用 countif 前後的日期條件

如果要從計數中排除兩個日期,請使用以下公式:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7)-COUNTIF($A $2:$A$11,日期(2015,9,7)))

此外,您可以在單元格中設置標準日期並使用單元格引用(以及使用 & 符號連接的雙引號中的運算符)。

在 COUNTIF 和 COUNTIFS 函數的條件中使用通配符

Excel中有三個通配符:

  1. *(星號) ——它代表任意數量的字符。例如,ex* 可能表示 excel、excels、example、expert 等。
  2. ? (問號) ——它代表一個單一的字符。例如,Tr?mp 可能意味著 Trump 或 Tramp。
  3. ~(波浪號) ——用於識別文本中的通配符(~、*、?)。

當其他內置計數功能失敗時,您可以使用帶有通配符的 COUNTIF 函數對單元格進行計數。例如,假設您有一個如下所示的數據集:

計算 Excel 數據集中包含文本的單元格

現在讓我們舉幾個例子:

#1 計算包含文本的單元格

要計算其中包含文本的單元格,我們可以使用通配符 *(星號)。由於星號表示任意數量的字符,因此它將計算所有包含任何文本的單元格。這是公式:

=COUNTIFS($C$2:$C$11,”*”)

在 Excel COUNTIF 函數中使用多個條件 - 通配符計數文本

注意:上面的公式會忽略包含數字、空白單元格和邏輯值的單元格,但會計算包含撇號(因此顯示為空白)的單元格或包含可能已作為公式的一部分。

這是有關處理有空字符串或撇號的情況的詳細教程。

#2 計數非空白單元格

如果您正在考慮使用 COUNTA 函數,請三思。

試試看,它可能會讓你失望。COUNTA 還將計算包含空字符串的單元格(通常由公式返回為 =””,或者當人們在單元格中僅輸入撇號時)。包含空字符串的單元格看起來是空白但不是,因此由 COUNTA 函數計數。

COUNTA 還將計算包含空字符串的單元格(通常由公式返回為 =””,或者當人們在單元格中僅輸入撇號時)。包含空字符串的單元格看起來是空白但不是,因此由 COUNTA 函數計數。

計算 Excel 數據集中包含文本的單元格

因此,如果您使用公式 = COUNTA (A1:A11),它會返回 11,而應該返回 10。

這是修復:

=COUNTIF($A$1:$A$11,”?*”)+COUNT($A$1:$A$11)+SUMPRODUCT(–ISLOGICAL($A$1:$A$11))

讓我們通過分解來理解這個公式:

#3 計算包含特定文本的單元格

假設我們要計算銷售代表名稱以 J 開頭的所有單元格。這可以通過在 COUNTIF 函數中使用通配符輕鬆實現。這是公式:

=COUNTIFS($C$2:$C$11,”J*”) 

在 Excel COUNTIF 函數中使用多個條件 - 計算特定文本通配符

條件 J* 指定單元格中的文本應以 J 開頭,並且可以包含任意數量的字符。

如果要計算文本中任何位置包含字母的單元格,請在其兩側加一個星號。例如,如果要計算其中包含字母“a”的單元格,請使用 *a* 作為條件。

與我的其他文章相比,這篇文章異常長。希望你喜歡它。通過發表評論讓我知道您的想法。

哦,您好 👋很高興認識你。

訂閱我們的電子報,定期發送很棒的科技內到您的郵

Post Comment