學不完．教不停．用不盡

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

【範例一】

(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<>""，只會過濾空白儲存格。

【範例二】

(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，表示該儲存格為偶數欄。

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

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

【範例三】

(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-製作含有儲存格內容的物件並且在儲存格內容變動時連動