2011年12月1日 星期四

Excel-計算奇數和及偶數和(MOD+SUMPRODUCT+陣列)

(一) 資料範圍:一欄中的數列

在 Excel 中有一數列(如下圖左),如果要計算:奇數和、偶數和、奇數列的和、偶數列的和,該如何處理?

【準備工作】

選取儲存格B1:B21,按一下 Ctrl+Shift+Enter 鍵,勾選「頂端列」選項,建立名稱:數列。

【運算公式】

(1) 奇數和

儲存格E3:=SUMPRODUCT(--(MOD(數列,2)=1),數列)

公式中的「--」,是為將公式中的 True/False 陣列,轉換為 1/0 陣列,才能運算。MOD(數列,2)=1 表示該數為奇數。

(2) 偶數和

儲存格E4:=SUMPRODUCT(--(MOD(數列,2)=0),數列)

MOD(數列,2)=0 表示該數為偶數。

(3) 奇數列的和

儲存格E5:=SUMPRODUCT(--(MOD(ROW(數列),2)=0),數列)

MOD(ROW(數列),2)=0 在本例中為奇數列,標示「第1列、第3列、…」(號為2、4、…)

(4) 偶數列的和

儲存格ED6:=SUMPRODUCT(--(MOD(ROW(數列),2)=1),數列)

MOD(ROW(數列),2)=1 在本例中為偶數列,標示「第2列、第4列、…」(號為3、5、…)

 

(二) 資料範圍:多欄中的數列

在 Excel 中有多欄組成的數列(如下圖左),如果要計算:奇數和、偶數和、奇數列的和、偶數列的和,該如何處理?

【準備工作】

選取儲存格H2:J11,在名稱管理員中建立一個名稱:資料。

【運算公式】

(1) 奇數和

儲存格M3:=SUMPRODUCT(--(MOD(資料,2)=1),資料)

(2) 偶數和

儲存格M4:=SUMPRODUCT(--(MOD(資料,2)=0),資料)

(3) 奇數列的和

儲存格M5:=SUMPRODUCT(--(MOD(ROW(資料),2)=0),資料) (X)

如果你使用這個式子,將會發生錯誤訊息「#VALUE!」,所以要改用陣列公式:

儲存格M5:{=SUM(IF((MOD(ROW(資料),2)=0),資料,FALSE))}

這是陣列公式,輸入完成請按 Ctrl+Shift+Enter 鍵。

(4) 偶數列的和

儲存格M6:{=SUM(IF((MOD(ROW(資料),2)=1),資料,FALSE))}

這是陣列公式,輸入完成請按 Ctrl+Shift+Enter 鍵。

沒有留言:

張貼留言

檢視其他文章

好康東東