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

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

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

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

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

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

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

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

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

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

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

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

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

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

=COUNTIF(A2:A10, "*")

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

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

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

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

上面討論的公式統(tǒng)計(jì)所有包含任何文本字符的單元格。但是，在某些情況下，這可能會(huì)令人困惑，因?yàn)槟承﹩卧窨赡芸雌饋?lái)為空白，但實(shí)際上包含人眼無(wú)法看到的字符，例如空字符串、撇號(hào)、空格、換行符等。結(jié)果，可視為空白的單元格會(huì)被公式統(tǒng)計(jì)在內(nèi)，導(dǎo)致用戶苦惱地試圖弄清楚原因。

要從計(jì)數(shù)中排除“誤報(bào)”空白單元格，請(qǐng)使用COUNTIFS函數(shù)，並在第二個(gè)條件中使用“排除”字符。

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

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

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

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

用星號(hào)括起來(lái)的問(wèn)號(hào)表示單元格中至少應(yīng)有一個(gè)文本字符。由於空字符串沒(méi)有任何字符，因此它不符合條件，不會(huì)被計(jì)數(shù)。以撇號(hào)(') 開頭的空白單元格也不會(huì)被計(jì)數(shù)。

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

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

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

要精確匹配示例文本，請(qǐng)?jiān)谝?hào)中輸入完整文本：

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

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

=COUNTIF(A2:A7, "bananas")

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

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

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

=COUNTIF(A2:A7, D2)

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

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

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

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

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

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

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

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

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

=COUNTIF(A2:A10, "*")

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

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

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

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

   要識(shí)別所有隱藏的單元格（已篩選出和手動(dòng)隱藏），請(qǐng)?jiān)?em>function_num中輸入103：

   =SUBTOTAL(103, A2)

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

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

   

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

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

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

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

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

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

乘法運(yùn)算符也可以：

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

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

這些公式的工作原理

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

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

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

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

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

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

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

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

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

可下載文件

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

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