2016年3月5日 星期六

Excel-儲存格為0和除數為0錯誤訊息不併入計算(SUMPRODUCT,ISERR)

在 Excel 的工作表中有時候對於顯示 0 的數字,想要讓它不顯示,有時在計算時可能遇到除數為0產生的除法錯誤(#DIV/0),如何要把它視為 0,再來計算,這兩個問題該如何解決呢?
在下圖的C欄中,所以內容為0者全都顯示了空白。在儲存各E9產生了除數為 0 的錯誤訊息,所以在計算平均時,也會得到一個錯誤結果。
Excel-儲存格為0和除數為0錯誤訊息不併入計算(SUMPRODUCT,ISERR)

【公式設計與解析】
以上圖為例,要將 10 個儲存格的內容計算平均,但是不想將儲存格為0者併入計算。

1. 不要將 0 併入計算平均值
這個問題無法以 AVERAGE 函數來計算,因為 AVERAGE 函數可以自動省略沒有內容的儲存格,但不會跳過儲存格為 0 者。
儲存格A14:
=SUMPRODUCT((A2:A11<>0)*(A2:A11))/SUMPRODUCT(1*(A2:A11<>0))
(1) SUMPRODUCT(1*(A2:A11<>0)):計算不為 0 者儲存格的個數。條件 A2:A11<>0 傳回成立與否的 TRUE/FALSE 陣列。『1*』運算可以將 TRUE/FALSE陣列轉換為 1/0 陣列。
(2) SUMPRODUCT((A2:A11<>0)*(A2:A11)):計算不為 0 者儲存格的總和。
不要將 0 併入計算平均值:=第(2)式/第(1)式
複製儲存格A14,貼至儲存格C14、儲存格E14、儲存格G14。

2. 設定儲存格為 0 者不顯示
這個問題要透過數值格式設定來完成。先選取儲存格C2:C11,在[儲存格格式]對話框中,切換至[數值]標籤,選取「自訂」,輸入格式『0;0;』。
image]

3.  將儲存格中除數為 0 的錯誤訊息(#DIV/0!)顯示為0(空白)
儲存格G2:=IF(ISERR(E2),0,E2)
ISERR(E2):ISERR 函數可以傳回儲存格是否有錯誤訊息。
儲存格G2也可以改成:=IFERROR(E2,0)
儲存格G2:G11套用和儲存格C2:C11相同的格式。
您或許對這些文章有興趣:

沒有留言:

張貼留言

好康東東