A0010 使用 SumProduct 函數統計多重條件的合計

SumProduct 函數的基本功能是做兩個範圍個別儲存格相乘後相加. 利用 Excel 的範圍條件式, 我們可以用 SumProuct 來計算符合特定條件的數值合計.

假設我們有以下的投資組合, 希望計算虧損大於一定金額的股票. 要計算有幾檔股票符合條件, 可以使用 CountIF. 第二個參數 "<1000" 必須要加雙引號.

如果要計算部位的合計, 因為我們原始的資料並沒有持有部位(股數乘市價), 我們可以用 SumProduct 來完成. SumProduct 參數中, (F2:F8<-1000) 會產生一串 True/False 值的範圍, Excel 會把 True 值轉為1, False 轉為 0, 再與後面的範圍 C2:C8 與 E2:E8 相乘後相加.

損益的合計我們可以用  SumIF.

這樣子的試算表很沒有彈性. 我們應該把條件設定為可以變動的. 我們將儲存格 B11的值改為-1000, 後面的公式也需要改一下. 如果條件是在字串裡, 要改為 &B11

第二個條件持有超過3000股的部份可以自行實作, 有差別的部份只有損益, 因為條件判斷的儲存格與要相加的儲存格不同, SumIF 要加第三個參數.

最後我們用 SumProduct 來做需要兩個以上條件的合計. 檔數的部份我們直接將兩個條件寫在參數裡, 就類似 Count 計數的功能.

部位和損益的公式只要在後面乘上要加總的值就可以了

延伸實作

1. 試著加入一個持有部位下限的條件.
2. 如果 B11 輸入的值大於0, 該怎麼處理?