在 Excel 中有個如下圖的工作表,如何依多個欄位的收入和支出做小計?

Excel-計算多欄位的間隔欄位小計(SUMPRODUCT)

【公式設計】

我想你最不想這樣設計公式,但是卻最容易理解。

儲存格N2:=B3-C3+D3-E3+F3-G3+H3-I3+J3-K3+L3-M3

可是一旦月份增加,公式的處理相對較不方便。

如果改用 SUMPRODUCT 函數來設計:

儲存格N2:=SUMPRODUCT(B3:M3*($B$2:$M$2="收"))-SUMPRODUCT(B3:M3*
($B$2:$M$2="支"))

(1) 收入小計:SUMPRODUCT(B3:M3*($B$2:$M$2="收"))

(2) 支出小計:SUMPRODUCT(B3:M3*($B$2:$M$2="支"))

兩者相減即可得到結果。

你想減化公式長度嗎?試試這樣:

儲存格N2:=SUMPRODUCT(B3:M3*-1^($B$2:$M$2<>"收"))

其中條件:$B$2:$M$2<>"收",在陣列公式中會傳回 TRUE/FALSE,運算過程會轉換為 1/0。

因為欄位名稱為:收、支、收、支、收、支、收、支、收、支、收、支

所以得到結果:

{FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE}

運算得到:{0,1,0,1,0,1,0,1,0,1,0,1}

將由運算「*-1^」,得到:{1,-1,1,-1,1,-1,1,-1,1,-1,1,-1}

所以以下這樣也行:

儲存格N2:=SUMPRODUCT(B3:M3*-1^($B$2:$M$2="支"))

利用陣列公式來設計:

儲存格N2:{=SUM(B3:M3*-1^($B$2:$M$2<>"收"))}

儲存格N2:{=SUM(B3:M3*-1^($B$2:$M$2="支"))}

公式輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

以下的陣列公式,結果會相同:

{=SUM(B3:M3*($B$2:$M$2="收"))-SUM(B3:M3*($B$2:$M$2="支"))}

 

如果你不想和儲存格內容產生關聯,則可以利用以下的公式:

儲存格N2:=SUMPRODUCT((B3:M3)*(MOD(COLUMN(B3:M3),2)=0))-
SUMPRODUCT((B3:M3)*(MOD(COLUMN(B3:M3),2)=1))

(1) 收入小計:SUMPRODUCT((B3:M3)*(MOD(COLUMN(B3:M3),2)=0))

(2) 支出小計:SUMPRODUCT((B3:M3)*(MOD(COLUMN(B3:M3),2)=1))

兩者相減即可得到結果。

如果要用陣列公式:

儲存格N2:{=SUM((B3:M3)*(MOD(COLUMN(B3:M3),2)=0))-SUM((B3:M3)*
(MOD(COLUMN(B3:M3),2)=1))}

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

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

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