輸入「Excel 計算文字出現次數」 的關鍵字搜尋結果，通常是使用 COUNTIF 函數。但是 COUNTIF 只適用在欄位值是單值的情況。如果欄位值是複選多值，COUNTIF 函數的計算結果會發生錯誤。解決方式是結合 SUBSTITUTE 函數 和 LEN 函數，才能正確地計算文字出現次數。
 |
| Photo by Snufkin on StockSnap |
錯誤狀況
想要計算 D 欄「購買商品」出現「蘿蔔」的次數，儲存格 I2 公式是
= COUNTIF(資料範圍, "搜尋文字的條件")
= COUNTIF(D:D, "蘿蔔")
COUNTIF 計算結果 2 次，而非預期的 5 次。如下圖，只購買「蘿蔔」的 2 筆紀錄。
您可以在 criteria 中使用萬用字元 (問號 (?) 和星號 (*))。 問號可比對任何單一字元。 星號可比對任何一串字元。 如果您要尋找實際的問號或星號，請在該字元前輸入波狀符號 (~)。 例如，=COUNTIF(A2:A5,"apple?") 會計算所有執行個體"apple"最後一個字母不同的例。
實際加上星號 (*) 的不同搜尋文字條件，計算結果都不符合預期。
= COUNTIF(D:D, "蘿蔔*")
= COUNTIF(D:D, "*蘿蔔")
解決方式
增加輔助欄 F 欄，計算該筆交易「蘿蔔」出現次數，儲存格 F2 的公式是：
=(D2儲存格的總字數 - D2儲存格扣掉蘿蔔的字數) / 蘿蔔的字數
=(LEN(D2) - LEN(SUBSTITUTE(D2, "蘿蔔", ""))) / LEN("蘿蔔")
再加總全部交易紀錄「蘿蔔」出現次數，儲存格 G2 的公式是：
= SUM(F2:F11)
如果要查找的文字有很多，輔助欄 F 欄會很累贅，可以省略輔助欄改寫：
(1) 適用 Google 試算表的寫法
=ARRAYFORMULA(SUM((LEN(資料範圍)-LEN(SUBSTITUTE(資料範圍, "蘿蔔", "")))/LEN("蘿蔔")))
=ARRAYFORMULA(SUM((LEN(D:D)-LEN(SUBSTITUTE(D:D, "蘿蔔", "")))/LEN("蘿蔔")))
(2) 適用 Excel 的寫法
= SUM((LEN(資料範圍)-LEN(SUBSTITUTE(資料範圍, "蘿蔔", "")))/LEN("蘿蔔"))
= SUM((LEN(D:D)-LEN(SUBSTITUTE(D:D, "蘿蔔", "")))/LEN("蘿蔔"))
上述公式必須以陣列公式的形式輸入，輸入完公式，Windows 版本 Excel 請按下 CTRL+SHIFT+ENTER。 macOS 版本 Excel 經測試，只要輸入 ENTER (return)
留言
張貼留言