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

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

前言

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

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

範例說明

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

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

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

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

要多條件加總的資料表

這個EXCEL教學系列就會說明一些我以前常用的函數、結合公式的使用方式、一些理念與經驗談,並會盡量以簡單易懂的方式進行說明,以期讓各位讀者理解。在各位讀者使用EXCEL進行資料處理或分析時,可能有時會碰到多條件加總或多條件比對的情況,本文就是要介紹如何使用適合的函數處理這樣的問題。


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

這個EXCEL教學系列就會說明一些我以前常用的函數、結合公式的使用方式、一些理念與經驗談,並會盡量以簡單易懂的方式進行說明,以期讓各位讀者理解。在各位讀者使用EXCEL進行資料處理或分析時,可能有時會碰到多條件加總或多條件比對的情況,本文就是要介紹如何使用適合的函數處理這樣的問題。

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)


免責聲明:以上資料僅供網友參考,若任何人依此資料進行投資,請自行承擔後果。

延伸閱讀
pattra的大頭照
貝多羅

曾在上市櫃財務會計單位服務多年,經歷財務、徵信與經營分析等工作,喜歡爬山、旅遊與閱讀。 取筆名「貝多羅」是因最初的佛經是刻寫在貝多羅葉上,希望我的文章能有文以載道之效。

留言