學不完．教不停．用不盡

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

【公式設計】

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

儲存格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))}