Excel計算特定文字出現次數的完整教學
在日常數據處理、問卷統計、銷售分析或客服紀錄整理時，經常需要統計某個文字或詞語在Excel資料表中出現的次數。這不僅有助於快速掌握數據分布，也能協助自動生成報表或進行決策分析。本文將深入介紹Excel計算文字出現次數的多種方法，並比較各自的適用情境與優缺點，幫助你根據實際需求選擇最合適的解法。
常見應用情境
- 統計問卷調查中某答案的出現次數
- 分析銷售清單中某品項的銷量
- 計算客服回覆中關鍵字的頻率
- 檢查資料表中重複項目或異常值
方法摘要表格
| 方法 | 適用情境 | 優點 | 缺點 | 公式範例 |
|---|---|---|---|---|
| COUNTIF | 單一條件、單一範圍 | 簡單易用 | 只支援單一條件 | =COUNTIF(A:A, “蘋果”) |
| COUNTIFS | 多條件、單一或多範圍 | 支援多條件 | 條件設計需注意 | =COUNTIFS(A:A, “蘋果”, B:B, “紅”) |
| SUMPRODUCT | 多條件、進階運算 | 彈性高、支援複雜條件 | 公式較長、需理解陣列 | =SUMPRODUCT((A:A=”蘋果”)*(B:B=”紅”)) |
| LEN+SUBSTITUTE | 單儲存格內多次出現 | 可計算部分匹配 | 需搭配SUMPRODUCT批次 | =(LEN(A1)-LEN(SUBSTITUTE(A1,”蘋果”,””)))/LEN(“蘋果”) |
COUNTIF函數：最直觀的單一條件計數
COUNTIF是Excel中最常用的統計函數之一，適合用於計算某個文字在一個範圍內出現的次數。例如，統計A欄中「蘋果」的出現次數：
=COUNTIF(A:A, "蘋果")
使用步驟
- 選擇一個空白儲存格。
- 輸入公式：
=COUNTIF(範圍, "文字")。 - 按下Enter，即可得到結果。
實用技巧
- 萬用字元：若需部分匹配（如包含「蘋果」的所有內容），可用星號(*)：
excel
=COUNTIF(A:A, "*蘋果*") - 常見錯誤提醒：COUNTIF預設為不區分大小寫，且僅能處理單一條件。
適用場景
- 統計單一關鍵字在一欄中出現的次數。
- 快速檢查資料表中某值的數量。
COUNTIFS函數：多條件計數的進階應用
當你需要根據多個條件進行計數時，COUNTIFS是最佳選擇。例如，統計A欄為「蘋果」且B欄為「紅」的資料筆數：
=COUNTIFS(A:A, "蘋果", B:B, "紅")
使用步驟
- 選擇一個空白儲存格。
- 輸入公式：
=COUNTIFS(範圍1, 條件1, 範圍2, 條件2, ...)。 - 按下Enter。
適用場景
- 需同時符合多個條件的統計需求（如品項+顏色、姓名+地區等）。
- 分析複雜資料表時的多維度篩選。
SUMPRODUCT函數：彈性最高的多條件與多範圍計算
SUMPRODUCT能進行更複雜的條件統計，尤其適合多條件、跨多欄或需進行邏輯運算時。例如，統計A欄或B欄中「葡萄」的出現次數：
=SUMPRODUCT(--((A:A="葡萄")+(B:B="葡萄")))
多條件範例
若需同時滿足多條件（如A欄為「蘋果」且B欄為「紅」）：
=SUMPRODUCT((A:A="蘋果")*(B:B="紅"))
SUMPRODUCT與COUNTIF比較
| 特色 | SUMPRODUCT | COUNTIF/COUNTIFS |
|---|---|---|
| 條件彈性 | 高（可自訂邏輯運算） | 低（格式固定） |
| 多範圍支援 | 可（需陣列運算） | COUNTIFS可多範圍 |
| 公式複雜度 | 較高 | 較低 |
| 性能（大量資料） | 大量資料時較慢 | 較快 |
適用場景
- 需進行多條件、跨欄位或自訂邏輯的統計。
- 批次計算多個不連續範圍。
LEN與SUBSTITUTE：計算單儲存格內重複出現次數
當你要計算一個儲存格內某個文字出現幾次（如「香蕉香蕉蘋果香蕉」中「香蕉」出現幾次），可用LEN與SUBSTITUTE組合：
=(LEN(A1)-LEN(SUBSTITUTE(A1, "香蕉", "")))/LEN("香蕉")
批次計算多個儲存格
若需統計一整欄每個儲存格內「香蕉」出現的總次數，可結合SUMPRODUCT：
=SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10, "香蕉", "")))/LEN("香蕉"))
適用場景
- 單一儲存格內有多次重複文字。
- 需計算部分匹配、非全欄匹配的情境。
多範圍與多條件計算：進階應用解析
在實務上，常需同時統計多個不連續範圍或多條件。以SUMPRODUCT最為彈性，COUNTIFS則適合條件明確且資料結構規律時。
多範圍計算
統計A欄與B欄中「葡萄」的總次數：
=SUMPRODUCT(--((A:A="葡萄")+(B:B="葡萄")))
多條件計算
統計A欄為「蘋果」且B欄為「紅」的筆數：
=SUMPRODUCT((A:A="蘋果")*(B:B="紅"))
或
=COUNTIFS(A:A, "蘋果", B:B, "紅")
應用場景比較
| 需求類型 | 建議方法 | 說明 |
|---|---|---|
| 單一條件 | COUNTIF | 最簡單直接 |
| 多條件 | COUNTIFS/SUMPRODUCT | COUNTIFS較直觀，SUMPRODUCT更彈性 |
| 多範圍 | SUMPRODUCT | 可跨多欄、複雜邏輯 |
| 單儲存格內 | LEN+SUBSTITUTE | 計算部分匹配 |
方法比較與選擇建議
| 方法 | 適用情境 | 優點 | 缺點 |
|---|---|---|---|
| COUNTIF | 單一條件 | 簡單、快速 | 只支援單一條件 |
| COUNTIFS | 多條件 | 支援多條件 | 條件設計需注意 |
| SUMPRODUCT | 複雜邏輯、多範圍 | 彈性高 | 公式較長 |
| LEN+SUBSTITUTE | 單儲存格內重複 | 可部分匹配 | 批次需SUMPRODUCT |
常見問題Q&A
Q1：COUNTIF能區分大小寫嗎？
A：COUNTIF、COUNTIFS皆不區分大小寫。如需區分，需用SUMPRODUCT結合EXACT函數。
Q2：COUNTIF能計算部分匹配嗎？
A：可用萬用字元*，如=COUNTIF(A:A, "*蘋果*")。
Q3：如何統計多個不連續範圍？
A：SUMPRODUCT最適合，COUNTIF需多公式相加。
Q4：COUNTIF/SUMPRODUCT遇到大量資料會很慢怎麼辦？
A：可考慮將資料整理於Monday.com等專業管理工具，支援自動化統計與團隊協作，減少Excel運算壓力。
Q5：有沒有更自動化的解決方案？
A：若需要跨部門、多人協作或自動生成報表，建議評估Monday.com、ClickUp等專案管理工具，能整合多來源資料並自動統計，提升效率。
進階資源推薦
- 若Excel公式難以滿足團隊自動化或跨部門協作需求，Monday.com 提供可視化報表、流程自動化與多用戶協作，適合大規模資料管理。
- 需彈性任務追蹤與AI協作，ClickUp 支援多維度統計與自訂報表。
- 偏好靈活筆記與資料整合，Notion 適合個人與小型團隊。
- 想系統性學習Excel進階技巧，Coursera 提供專業課程，從基礎到進階全面提升你的數據處理能力。
總結
Excel計算特定文字出現次數的方法多元，從COUNTIF、COUNTIFS、SUMPRODUCT到LEN+SUBSTITUTE，各有適用情境。建議根據資料結構、條件複雜度與自動化需求選擇最合適的方式。若遇到Excel效能瓶頸或需團隊協作，亦可考慮Monday.com、ClickUp等專業工具，讓統計與管理更高效。若想進一步精通Excel，推薦善用Coursera等線上課程，持續提升你的數據分析力。
