Frankie 發問時間: 電腦與網際網路軟體 · 8 年前

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

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

2 個解答

評分
  • 8 年前
    最佳解答

    =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.計算五張工作表A1的〔平均值〕,排除空格及0值

                            <.准提部林.>

    --------------------------------

    ■公式1:工作表名稱〔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"))))

     

    ■公式2:工作表名稱〔表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(計算5工作表平均值).rar

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

    --------------------------------

  • 鬍鬚
    Lv 7
    8 年前

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

還有問題?馬上發問,尋求解答。