在 Excel 的範圍內計算特定單詞的數量

本文解釋了一個公式，用於計算特定單詞在 Excel 範圍內出現的次數。

如何在 Excel 的範圍內計算特定單詞的數量？

通用公式

=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))

參數

如何使用這個公式？

假設您想計算單詞“tool”在範圍 B5:B8 中的出現次數，您可以按照以下步驟操作。

選擇一個空白單元格，將以下公式輸入其中，然後按 Enter 鍵。

提示：您可以直接複製並將公式粘貼到選定的單元格中。

=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")))/LEN("tool"))

注意：

• 1. 請根據需要更改範圍 B5:B8 和單詞“tool”；
• 2. 如果找不到該單詞，則返回 0；
• 3. 該公式區分大小寫；
• 4. 如果您想在範圍內計算特定單詞並忽略大小寫，請在 SUBSTITUTE 函數內部使用 UPPER 函數，如下列公式所示：
• =SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(UPPER(B5:B8),UPPER("tool"),"")))/LEN("tool"))
• 

這個公式是如何工作的？

=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")))/LEN("tool"))

• 1. LEN(B5:B8)：LEN 函數計算範圍 B5:B8 中每個單元格的總字符串長度，結果將以數組形式返回 {47;23;23;15}；
• 2. LEN(SUBSTITUTE(B5:B8,"tool",""))：這裡 SUBSTITUTE 函數從範圍 B5:B8 中的每個單元格移除特定單詞“tool”，然後 LEN 函數計算範圍 B5:B8 中每個單元格不含特定單詞“tool”的長度。它返回的數組為 {35;19;19;15}；
• 3. (LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool",""))：這部分公式現在變為 {47;23;23;15}-{35;19;19;15}。減去後，結果為 {12;4;4;0}；
• 4. LEN("tool")：這裡的 LEN 函數計算“tool”的長度，並返回結果 4；
• 5. LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")))/LEN("tool")：如上所述，這部分將顯示為 {12;4;4;0}/4。將數組中的每個數字除以 4，得到結果 {3;1;1;0}；
• 6. SUMPRODUCT({3;1;1;0})：SUMPRODUCT 函數對數組中的數字求和，並返回範圍 B5:B8 中“tool”的總計數。

相關函數

相關公式