2016年11月17日 星期四

Excel-取出固定間隔列的數值予以加總(SUMPRODUCT,MOD,ROW)

有網友問到:在 Excel 的工作表中有一個數值清單,如何取出固定間隔列的數值予以加總?
參考下圖,如何取出間隔 1, 2, 3, 4, 5, 6, 7, 8, 9 列的數值來加總?
Excel-取出固定間隔列的數值予以加總(SUMPRODUCT,MOD,ROW)
【公式設計與解析】
儲存格E2:=$B$2+SUMPRODUCT((MOD(ROW($A$3:$A$25)-2,ROW(2:2))=0)*$B$3:$B$25)
複製儲存格E2,貼至儲存格E2:E10。
(1) ROW($A$3:$A$25)
在 SUMPRODUCT 函數中取得儲存格A3:A25的列號,傳回 3, 4, 5, ..., 25。
(2) MOD(ROW($A$3:$A$25)-2,ROW(2:2))
將第(1)式的傳回值減2後再除以2,傳回 1, 0, 1, 0, ..., 0, 1。
(3) MOD(ROW($A$3:$A$25)-2,ROW(2:2))=0
判斷第(2)式的傳回值是否為 0,傳回 TRUE/FALSE 陣列。
(4) (MOD(ROW($A$3:$A$25)-2,ROW(2:2))=0)*$B$3:$B$25
式子中的『*』運算子相當於執行 AND 邏輯運算,執行時 TRUE/FALSE 陣列會轉換為 1/0 陣列。

公式結果相當於:
儲存格E2:=B2+SUMPRODUCT((MOD(ROW(A3:A25)-2,2)=0)*B3:B25)
儲存格E3:=B2+SUMPRODUCT((MOD(ROW(A3:A25)-2,3)=0)*B3:B25)
儲存格E4:=B2+SUMPRODUCT((MOD(ROW(A3:A25)-2,4)=0)*B3:B25)
儲存格E5:=B2+SUMPRODUCT((MOD(ROW(A3:A25)-2,5)=0)*B3:B25)

沒有留言:

張貼留言

好康東東