學不完．教不停．用不盡

在 Excel 的工作表中使用COUNTIF函數來計算某些字元的個數是十分平常的事，但是如果依照下圖來求得某個班級的類別，其公式為：

儲存格L2：=COUNTIF($C$2:$C$9,L$1)

複製儲存格L2至儲存格L2:O2。觀察其結果儲存格O2的結果是錯的。

如果你將儲存格O2的公式寫成：=COUNTIF($C$2:$C$9,"*")，答案也是錯的。

因為在COUNTIF函數中使用「*」，是當為萬用字元使用。如果你非得使用「*」，則可以將公式改為：

儲存格O2：=COUNTIF($C$2:$C$9,"'*'")

其中*必須先以左右各一個「'」含括，再以左右各一個「"」含括，可以算到正確的答案。

(如果你有使用另一個萬用字元「?」，也是一樣的觀念和用法。)

其實這個查詢可以使用以下二種方式快速完成：

(一) 陣列公式

儲存格F2：{=SUM(IF($A$2:$A$281=$E2,IF($C$2:$C$281=F$1,1,0)))}

這是陣列公式，輸入完成後要按下 Ctrl+Shift+Enter 鍵。

將儲存格F2複製到儲存格F2:I13。

如此，其中的「*」不會被視為萬用字元。

(二) SUMPRODUCT函數

儲存格F16：=SUMPRODUCT(--($A$2:$A$281=$E16),--($C$2:$C$281=F$15))

將儲存格F16複製到儲存格F16:I27。

(--($A$2:$A$281=$E16)其中的「--」是要將結果之True/Fasle轉換為1/0來運算。

如此，其中的「*」也不會被視為萬用字元。

參考：本部落格中其他關於 Excel COUNTIF 函數的應用

參考：本部落格中其他關於 Excel COUNTIFS 函數的應用