2010年10月19日 星期二

Excel-在COUNTIF中計算*個數

在 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來運算。

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

關於SUMPRODUCT函數的說明,請參考微軟網站:

http://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

沒有留言:

張貼留言

好康東東