有網友問到:在 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)

創作者介紹

學不完.教不停.用不盡

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