在 Excel 的工作表中計算個數字的平均值是常見的操作,但是如果要忽略 0 或是空白者不予平圴,該如何處理?如果是要計算奇數儲存格或是偶數儲存格者,要如何處理?

【範例一】

Excel-計算平均時忽略0或是空白儲存格(SUMPRODUCT)

(1)計算平均(利用AVERAGE)

儲存格A4:=AVERAGE(A3:O3)

在使用 AVERAGE 函數時,會自動忽略沒有內容的儲存格,但是會計算儲存格為 0 者。

(2)計算平均(0不採計、空格不採計)

儲存格A10:=SUMPRODUCT(A9:O9)/SUMPRODUCT((A9:O9<>0)*1)

在 SUMPRODUCT 函數中使用條件:A9:O9<>0,會過濾空白和儲存格內容為 0 者。

「*1」運算用以將條件判斷結果 TRUE/FALSE 陣列轉換為 1/0 陣列。

(3)計算平均(0採計、空格不採計)

儲存格A16:=SUMPRODUCT(A15:O15)/SUMPRODUCT((A15:O15<>"")*1)

SUMPRODUCT 函數中使用條件:A9:O9<>"",只會過濾空白儲存格。

 

【範例二】

計算平均時忽略0或是空白儲存格(SUMPRODUCT)

(1)計算平均(利用AVERAGE)

儲存格A4:=AVERAGE(A3:O3)

 

(2)奇數格計算平均

儲存格A10:=SUMPRODUCT(A9:O9*(MOD(COLUMN(A9:O9),2)=1))/
SUMPRODUCT((MOD(COLUMN(A9:O9),2)=1)*1)

① SUMPRODUCT(A9:O9*(MOD(COLUMN(A9:O9),2)=1))

使用條件:MOD(COLUMN(A9:O9),2)=1

COLUMN 函數可以傳回儲存格的欄編號,再利用 MOD 函數求得欄編號除以 2 的餘數,如果餘數為 1,表示該儲存格為奇數欄。

第①式可以求得奇數儲存格的總和。

② SUMPRODUCT((MOD(COLUMN(A9:O9),2)=1)*1)

利用 MOD 函數求得欄編號除以 2 的餘數,如果餘數為 1,表示該儲存格為奇數欄。

第②式可以求得奇數儲存格的個數。

③ 第①式÷第②式,即為所求。

 

(3)偶數格計算平均

儲存格A16:=SUMPRODUCT(A15:O15*(MOD(COLUMN(A15:O15),2)=0))/
SUMPRODUCT((MOD(COLUMN(A15:O15),2)=0)*1)

① SUMPRODUCT(A15:O15*(MOD(COLUMN(A15:O15),2)=0))

利用 MOD 函數求得欄編號除以 2 的餘數,如果餘數為 0,表示該儲存格為偶數欄。

第①式可以求得偶數儲存格的總和。

② SUMPRODUCT((MOD(COLUMN(A15:O15),2)=0)*1)

利用 MOD 函數求得欄編號除以 2 的餘數,如果餘數為 0,表示該儲存格為偶數欄。

第②式可以求得奇數儲存格的個數。

③ 第①式÷第②式,即為所求。

 

【範例三】

計算平均時忽略0或是空白儲存格(SUMPRODUCT)

(1)計算平均(利用AVERAGE)

儲存格A4:=AVERAGE(A3:O3)

(2)奇數格計算平均(0不採計、空格不採計)

儲存格A10:=SUMPRODUCT(A9:O9*(MOD(COLUMN(A9:O9),2)=1))/
SUMPRODUCT((MOD(COLUMN(A9:O9),2)=1)*(A9:O9<>0))

① SUMPRODUCT(A9:O9*(MOD(COLUMN(A9:O9),2)=1))

利用 MOD 函數求得欄編號除以 2 的餘數,如果餘數為 1,表示該儲存格為奇數欄。

② SUMPRODUCT((MOD(COLUMN(A9:O9),2)=1)*(A9:O9<>0))

條件式:A9:O9<>0,表示要過濾儲存格內容為 0 或是空格者。

③ 第①式÷第②式,即為所求。

 

(3)偶數格計算平均(0採計、空格不採計)

儲存格A16:=SUMPRODUCT(A15:O15*(MOD(COLUMN(A15:O15),2)=0))/
SUMPRODUCT((MOD(COLUMN(A15:O15),2)=1)*(A15:O15<>""))

① SUMPRODUCT(A15:O15*(MOD(COLUMN(A15:O15),2)=0))

利用 MOD 函數求得欄編號除以 2 的餘數,如果餘數為 0,表示該儲存格為偶數欄。

② SUMPRODUCT((MOD(COLUMN(A15:O15),2)=1)*(A15:O15<>""))

條件式:A9:O9<>"",表示要過濾儲存格內容為空格者。

③ 第①式÷第②式,即為所求。

【延伸閱讀】

Excel-在不連續的多個空白儲存格填入「X」字元

Excel-計算不包含0及空白儲存格的最小值(SMALL,LARGE,MIN)

Excel-計算不重複項目個數並排除空白(SUMPRODUCT,COUNTIF)

Excel-篩選非空白儲存格並加以排序

Excel-分割字串和串接字串(TEXTSPLIT,TEXTJOIN)

Excel-產生各種連續數列(ROW,INT)

Excel-儲存格為0者不顯示

Excel-自動計算和前一個數字之間的天數

Excel-判斷數值是否為整數

Excel-製作含有儲存格內容的物件並且在儲存格內容變動時連動 

學不完.教不停.用不盡文章列表

arrow
arrow
    文章標籤
    Excel SUMPRODUCT
    全站熱搜

    vincent 發表在 痞客邦 留言(2) 人氣()