Excel公式與文本計算細胞：任何特定或過濾的單元格

Excel中如何統計包含文本的單元格？

Excel最初設計用於處理數字，但如今我們也經常使用它來存儲和處理文本。想知道工作表中包含文本的單元格有多少個？ Microsoft Excel 提供了幾個函數來實現此目的。應該使用哪個函數呢？這取決於具體情況。在本教程中，您將找到各種公式以及每個公式的最佳使用時機。

• 統計包含任何文本的單元格
• 忽略空字符串和空格，統計包含文本的單元格
• 統計包含特定文本的單元格
• 統計篩選後的包含文本的單元格

如何在Excel中統計包含文本的單元格數量

有兩種基本的公式可以查找給定範圍內包含任何文本字符串或字符的單元格數量。

使用COUNTIF公式統計所有包含文本的單元格

當您希望查找Excel中包含文本的單元格數量時，在條件參數中使用星號的COUNTIF函數是最佳且最簡單的解決方案：

COUNTIF(*范围*, "\*")由於星號(*) 是一個通配符，它匹配任何字符序列，因此該公式將統計所有包含任何文本的單元格。

使用SUMPRODUCT公式統計包含任何文本的單元格

另一種獲取包含文本的單元格數量的方法是組合使用SUMPRODUCT和ISTEXT函數：

SUMPRODUCT(--ISTEXT(*范围*))或

SUMPRODUCT(ISTEXT(*范围*)*1) ISTEXT函數檢查指定範圍內的每個單元格是否包含任何文本字符，並返回一個TRUE（包含文本的單元格）和FALSE（其他單元格）值的數組。雙重一元運算符(--) 或乘法運算將TRUE和FALSE分別強制轉換為1和0，從而生成一個由1和0組成的數組。 SUMPRODUCT函數對數組的所有元素求和，並返回1的數量，即包含文本的單元格數量。

為了更好地理解這些公式的工作原理，請查看哪些值被計數，哪些值未被計數：

計數內容	未計數內容
- 包含任何文本的單元格 - 特殊字符 - 格式化為文本的數字 - 可視為空白但包含空字符串("")、撇號(')、空格或不可見字符的單元格	- 數字 - 日期 - TRUE 和FALSE 的邏輯值 - 錯誤 - 空白單元格

例如，要統計A2:A10範圍中包含文本的單元格（不包括數字、日期、邏輯值、錯誤和空白單元格），可以使用以下公式之一：

=COUNTIF(A2:A10, "*")

=SUMPRODUCT(--ISTEXT(A2:A10))

=SUMPRODUCT(ISTEXT(A2:A10)*1)

下圖顯示了結果：

忽略空格和空字符串，統計包含文本的單元格

上面討論的公式統計所有包含任何文本字符的單元格。但是，在某些情況下，這可能會令人困惑，因為某些單元格可能看起來為空白，但實際上包含人眼無法看到的字符，例如空字符串、撇號、空格、換行符等。結果，可視為空白的單元格會被公式統計在內，導致用戶苦惱地試圖弄清楚原因。

要從計數中排除“誤報”空白單元格，請使用COUNTIFS函數，並在第二個條件中使用“排除”字符。

例如，要統計A2:A7範圍內包含文本的單元格（忽略包含空格字符的單元格），請使用以下公式：

=COUNTIFS(A2:A7,"*", A2:A7, " ")

如果目標範圍包含任何公式驅動的數，則某些公式可能會導致空字符串("")。要忽略包含空字符串的單元格，請在criteria1參數中將"*"替換為"*?*"：

=COUNTIFS(A2:A9,"*?*", A2:A9, " ")

用星號括起來的問號表示單元格中至少應有一個文本字符。由於空字符串沒有任何字符，因此它不符合條件，不會被計數。以撇號(') 開頭的空白單元格也不會被計數。

在下圖中，A7中有一個空格，A8中有一個撇號，A9中有一個空字符串（=""）。我們的公式忽略了所有這些單元格，並返回文本單元格計數為3：

如何在Excel中統計包含特定文本的單元格

要獲取包含特定文本或字符的單元格數量，只需在COUNTIF函數的條件參數中提供該文本即可。以下示例解釋了細微之處。

要精確匹配示例文本，請在引號中輸入完整文本：

COUNTIF(*范围*, "*文本*")要統計部分匹配的單元格，請將文本放在兩個星號之間，這兩個星號代表文本之前和之後任意數量的字符：

COUNTIF(*范围*, "\**文本*\*")例如，要查找A2:A7範圍內有多少個單元格完全包含單詞“bananas”，請使用以下公式：

=COUNTIF(A2:A7, "bananas")

要統計所有包含“bananas”作為其內容一部分的單元格（無論位置如何），請使用以下公式：

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

為了使公式更易於使用，您可以將條件放在預定義的單元格中，例如D2，並將單元格引用放在第二個參數中：

=COUNTIF(A2:A7, D2)

根據D2中的輸入，公式可以完全或部分匹配示例文本：

• 對於完全匹配，請鍵入與源表中顯示的完整單詞或短語相同的單詞或短語，例如Bananas 。
• 對於部分匹配，請鍵入用通配符字符包圍的示例文本，例如*Bananas* 。

由於該公式不區分大小寫，因此您不必擔心字母大小寫，這意味著*bananas*也可以。

或者，要統計部分匹配的單元格，請連接單元格引用和通配符字符，例如：

=COUNTIF(A2:A7, "*"&D2&"*")

有關更多信息，請參閱如何在Excel中統計包含特定文本的單元格。

如何在Excel中統計篩選後的包含文本的單元格

當使用Excel篩選器僅顯示給定時刻的相關數據時，有時您可能需要統計可見的包含文本的單元格。遺憾的是，此任務沒有一鍵式解決方案，但以下示例將引導您完成步驟。

假設您有一個如下圖所示的表格。一些條目是使用公式從更大的數據庫中提取的，並且在此過程中發生了一些錯誤。您希望查找A列中項目的總數。當所有行都可見時，我們用於統計包含文本的單元格的COUNTIF公式非常有效：

=COUNTIF(A2:A10, "*")

現在，您根據某些條件縮小列表範圍，例如篩選出數量大於10的項目。問題是——還剩下多少個項目？

要統計篩選後的包含文本的單元格，您需要執行以下操作：

1. 在源表中，使所有行可見。為此，請清除所有篩選器並取消隱藏隱藏的行。

2. 添加一個輔助列，其中包含SUBTOTAL公式，該公式指示行是否已篩選。要處理篩選後的單元格，請對function_num參數使用3： =SUBTOTAL(3, A2)

   要識別所有隱藏的單元格（已篩選出和手動隱藏），請在function_num中輸入103：

   =SUBTOTAL(103, A2)

   在此示例中，我們希望統計可見的包含文本的單元格，而不管其他單元格是如何隱藏的，因此我們在A2中輸入第二個公式，並將其複製到A10。

   對於可見單元格，公式返回1。一旦您篩選出或手動隱藏某些行，公式將為它們返回0。 （您不會看到這些零，因為它們是為隱藏行返回的。要確保它以這種方式工作，只需將包含Subtotal公式的隱藏單元格的內容複製到任何可見的單元格，例如=D2，假設第2行是隱藏的。）

   

3. 使用COUNTIFS函數和兩個不同的criteria_range / criteria對來統計可見的包含文本的單元格：

   • 條件1 - 在A2:A10範圍內搜索包含任何文本("*")的單元格。
   • 條件2 - 在D2:D10範圍內搜索1，以檢測可見單元格。

   =COUNTIFS(A2:A10, "*", D2:D10, 1)

現在，您可以根據需要篩選數據，公式將告訴您A列中包含文本的已篩選單元格的數量（在本例中為3）：

如果您不想在工作表中插入額外的列，那麼您將需要更長的公式來完成任務。只需選擇您更喜歡的那個即可：

=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A10))), --(ISTEXT(A2:A10)))

=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A10, ROW(A2:A10) - MIN(ROW(A2:A10)),,1)), -- (ISTEXT(A2:A10)))

乘法運算符也可以：

=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A10))) * (ISTEXT(A2:A10)))

=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A10, ROW(A2:A10)-MIN(ROW(A2:A10)),,1)) * (ISTEXT(A2:A10)))

使用哪個公式取決於您的個人喜好——無論如何結果都是相同的：

這些公式的工作原理

第一個公式使用INDIRECT函數將指定範圍中所有單元格的單個引用“提供”給SUBTOTAL。第二個公式使用OFFSET、ROW和MIN函數的組合來實現相同的目的。

SUBTOTAL函數返回一個由1和0組成的數組，其中1代表可見單元格，0代表隱藏單元格（如上面的輔助列）。

ISTEXT函數檢查A2:A10中的每個單元格，如果單元格包含文本則返回TRUE，否則返回FALSE。雙重一元運算符(--) 將TRUE和FALSE值強制轉換為1和0。此時，公式如下所示：

=SUMPRODUCT({0;1;1;1;0;1;1;0;0}, {1;1;1;0;1;1;0;1;1})

SUMPRODUCT函數首先將兩個數組中相同位置的元素相乘，然後對結果數組求和。

由於乘以零的結果為零，因此只有在兩個數組中都由1表示的單元格在最終數組中才有1。

=SUMPRODUCT({0;1;1;0;0;1;0;0;0})

上述數組中1的數量就是包含文本的可見單元格的數量。

這就是如何在Excel中統計包含文本的單元格的方法。感謝您的閱讀，希望下週能在我們的博客上見到您！

可下載文件

Excel中統計包含文本的單元格的公式

以上是Excel公式與文本計算細胞：任何特定或過濾的單元格的詳細內容。更多資訊請關注PHP中文網其他相關文章！