2014年8月6日 星期三

Excel-排除含錯誤訊息的儲存格計算小計(陣列公式,SUMPRODUCT)

如果你在一個 Excel 的資料表中,參考下圖,如果小計欄位是甲除以乙的結果,如果想要取出日期介於 6/5 至 6/15 之間的小計來加總,而在第 8 列出現了一個錯誤訊息,該如何在加總時能排除不計含有錯誤訊息的儲存格呢?

【準備工作】

選取儲存格A1:D21,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、甲、乙、小計。

【輸入公式】

(1) 含錯誤

儲存格G4:=SUMPRODUCT((日期>=G1)*(日期<=G2)*小計)

(日期>=G1)*(日期<=G2):指定日期區間的儲存格陣列。

因為其中有一個錯誤訊息「#DIV/0!」,所以計算結果也是出現錯誤訊息。

 

(2) 不含錯誤

如果想要挑除這些錯誤訊息儲存格,可以使用 ISERR 函數來判斷:

儲存格G5:=SUMPRODUCT((日期>=G1)*(日期<=G2)*NOT(ISERR(小計))*小計)

NOT(ISERR(小計)):傳回是否含有錯誤訊息的儲存格陣列(是/否對照 FALSE/TRUE)。

結果還是會出現錯誤訊息!

改用陣列公式來處理,就不會有問題了:

儲存格G5:{=SUM(IF((日期>=G1)*(日期<=G2)*NOT(ISERR(小計)),小計,FALSE))}

這是陣列公式,輸入完成要按 Ctrl+Shift+F3 鍵。

沒有留言:

張貼留言

好康東東