Excel中如何統(tǒng)計包含文本的單元格？

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

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

如何在Excel中統(tǒng)計包含文本的單元格數(shù)量

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

使用COUNTIF公式統(tǒng)計所有包含文本的單元格

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

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

使用SUMPRODUCT公式統(tǒng)計包含任何文本的單元格

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

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

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

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

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

=COUNTIF(A2:A10, "*")

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

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

下圖顯示了結(jié)果：

忽略空格和空字符串，統(tǒng)計包含文本的單元格

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

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

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

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

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

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

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

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

如何在Excel中統(tǒng)計包含特定文本的單元格

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

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

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

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

=COUNTIF(A2:A7, "bananas")

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

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

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

=COUNTIF(A2:A7, D2)

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

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

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

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

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

有關(guān)更多信息，請參閱如何在Excel中統(tǒng)計包含特定文本的單元格。

如何在Excel中統(tǒng)計篩選後的包含文本的單元格

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

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

=COUNTIF(A2:A10, "*")

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

要統(tǒng)計篩選後的包含文本的單元格，您需要執(zhí)行以下操作：

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

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

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

   =SUBTOTAL(103, A2)

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

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

   

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

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

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

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

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

=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)))

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

這些公式的工作原理

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

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

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

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

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

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

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

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

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

可下載文件

Excel中統(tǒng)計包含文本的單元格的公式

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