對投資新手的簡單易懂教學-Excel在投資分析資料的應用-Sumproduct、SUMIFS的多條件加總

前言

這個EXCEL教學系列就會說明一些我以前常用的函數、結合公式的使用方式、一些理念與經驗談，並會盡量以簡單易懂的方式進行說明，以期讓各位讀者理解。

在各位讀者使用EXCEL進行資料處理或分析時，可能有時會碰到多條件加總的情況，本文就是要介紹如何使用像是Sumproduct、SUMIFS的函數，以處理這樣的問題。

範例說明

下圖是我們的範例，例如投資組合中有8種股票，有分為產業、投資時點(年)與投資部位，

各有不同的今年報酬率與投資損益。

那是否可設計公式，讓我們只要輸入多個不同條件，就能自動帶出正確結果?

答案是可以的，而且可分為2種函數的寫法，結果會相同!

要多條件加總的資料表

我們可填入設計條件的資料欄位

SUMPRODUCT函數

微軟網站對SUMPRODUCT函數的說明如下(摘錄)

SUMPRODUCT函數會返回對應範圍或陣列之產品的總和。 預設運算為乘法，但也可以加法、減法和除法。
語法: 若要使用預設運算 (乘法) ：
語法=SUMPRODUCT (array1， [array2]， [array3]， ...)

SUMPRODUCT寫法

公式內容(由上而下)

=SUMPRODUCT(($B$2:$B$9=I3)*1,($C$2:$C$9=J3)*1,$F$2:$F$9)

這行意思是若B欄範圍若有等於I3(電子)，這列的值等於1，否則等於0 

若C欄範圍若有等於J3(2019)，這列的值等於1，否則等於0

在這些股票中，只有B股票與E股票符合(其他不符合的股票算出結果是0)，

例如B股票是「等於I3(電子)」*「等於J3(2019)」*「B投資損益」=1*1*60=60

E股票是「等於I3(電子)」*「等於J3(2019)」*「E投資損益」=1*1*(350)=(350)

所以損益就是60+(350)=(290)

=SUMPRODUCT(($B$2:$B$9=I4)*1,($D$2:$D$9>=K4)*1,$F$2:$F$9)

=SUMPRODUCT(($C$2:$C$9=J5)*1,($D$2:$D$9>=K5)*1,$F$2:$F$9)
=SUMPRODUCT(($B$2:$B$9=I7)*1,($C$2:$C$9<=J7)*1,($D$2:$D$9>=K7)*1,$F$2:$F$9)

=SUMPRODUCT(($B$2:$B$9=I6)*1,($C$2:$C$9=J6)*1,($D$2:$D$9>=K6)*1,$F$2:$F$9)

SUMIFS函數

微軟網站對SUMIFS函數的說明如下(摘錄)

SUMIFS 函數是數學與三角函數之一，會加總符合多項準則的所有引數。
語法=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

SUMIFS寫法

=SUMIFS($F$2:$F$9,$B$2:$B$9,$I12,$C$2:$C$9,$J12)

這行意思是若B欄範圍若有等於I12(電子)且若C欄範圍若有等於J12(2019)，這F欄的這一列的值就會被加總

在這些股票中，只有B股票與E股票符合會被加總，

所以損益就是60+(350)=(290)。

其實SUMIFS寫起來比SUMPRODUCT好懂一些。

=SUMIFS($F$2:$F$9,$B$2:$B$9,$I13,$D$2:$D$9,">="&$K13)
=SUMIFS($F$2:$F$9,$C$2:$C$9,$J14,$D$2:$D$9,">="&$K14)
=SUMIFS($F$2:$F$9,$B$2:$B$9,$I15,$C$2:$C$9,$J15,$D$2:$D$9,">="&$K15)
=SUMIFS($F$2:$F$9,$B$2:$B$9,$I16,$C$2:$C$9,"<="&$J16,$D$2:$D$9,">="&$K16)