excel各sheet平均如何避開0值及空白

Question

我想要5個sheet的A1平均，但5個sheet中會有空白或零值，公式應該要如何設定，我一直出現#DIV/0

Answer 1

最佳解答

=IF(SUM(Sheet1:Sheet5!A1),SUM(Sheet1:Sheet5!A1)/COUNT(0/N(INDIRECT("Sheet"&ROW(1:5)&"!A1"))),"")

陣列輸入：

輸入或修改公式完成後，游標留在〔編輯列〕，先不要按Enter，

同時按住〔Shift + Ctrl〕不放，再按〔Enter〕。

2013-04-01 14:59:55 補充：

EXCEL．計算五張工作表Ａ１的〔平均值〕，排除空格及０值

　　　　　　　　　　　　　　　　　　　　　　　　＜．准提部林．＞

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

■公式１：工作表名稱〔Sheet1 ~ Sheet5〕

＜陣列輸入＞

　=IF(SUM(Sheet1:Sheet5!A1),SUM(Sheet1:Sheet5!A1)/COUNT(0/N(INDIRECT("Sheet"&ROW(1:5)&"!A1"))),"")

　　或

　=SUM(Sheet1:Sheet5!A1)/MAX(1,COUNT(0/N(INDIRECT("Sheet"&ROW(1:5)&"!A1"))))

　

■公式２：工作表名稱〔表1 ~ 表5〕

＜陣列輸入＞

　=IF(SUM(N(INDIRECT("表"&ROW(1:5)&"!A1"))),SUM(N(INDIRECT("表"&ROW(1:5)&"!A1")))/COUNT(0/N(INDIRECT("表"&ROW(1:5)&"!A1"))),"")

　　或

　=SUM(N(INDIRECT("表"&ROW(1:5)&"!A1")))/MAX(1,COUNT(0/N(INDIRECT("表"&ROW(1:5)&"!A1"))))

　

■陣列公式輸入法：

　輸入或修改公式完成後，游標留在〔編輯列〕，先不要按Enter，

　同時按住〔Shift + Ctrl〕不放，再按〔Enter〕。

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

＜範例檔＞：

檔案名稱：20130401a01(計算５工作表平均值).rar

下載位址：http://www.funp.net/479004

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

Answer 2

=SUM(Sheet1:Sheet4!A1)/((Sheet1!A1<>0)+(Sheet2!A1<>0)+(Sheet3!A1<>0)+(Sheet4!A1<>0)+(Sheet5!A1<>0))