2016年9月27日 星期二

Excel-SUMIF、SUMIFS、SUMPRODUCT、SUM+IF+陣列公式的互通

有網友問到:在 Excel 中的 SUMIF、SUMIFS、SUMPRODUCT 函數有其共通之處,在使用時如何互相取代?如果使用 SUM+IF+陣列公式,又是如何互相取代?
參考下圖的資料表,運用 SUMIF、SUMIFS、SUMPRODUCT 函數及使用 SUM+IF+陣列公式來計算特定條件下的數量小計和金額小計。
SUM+IF+陣列公式的互通
以下用四個例子來對照 SUMIF、SUMIFS 及 SUMPRODUCT 函數及使用 SUM+IF+陣列公式。
為方便說明,先選取B1:D23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:單價、數量、金額。
陣列公式輸入完成要 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。

(1) 計算單價500以上者數量小計
儲存格F3:=SUMIF(單價,">500",數量)
儲存格F3:=SUMPRODUCT((單價>500)*數量)
儲存格F3:{=SUM(IF(單價>500,數量,))}

(2) 計算單價500以上者金額小計
儲存格F5:=SUMIF(單價,">500",金額)
儲存格F5:=SUMPRODUCT((單價>500)*金額)
儲存格F5:{=SUM(IF(單價>500,金額,))}

(3) 計算單價300~600者數量小計
儲存格F8:=SUMIFS(數量,單價,">=300",單價,"<=600")
儲存格F8:=SUMPRODUCT((單價>=300)*(單價<=600)*數量)
儲存格F8:{=SUM(IF((單價>=300)*(單價<=600),數量,))}

(4) 計算單價300~600者金額小計
儲存格F10:=SUMIFS(金額,單價,">=300",單價,"<=600")
儲存格F10:=SUMPRODUCT((單價>=300)*(單價<=600)*金額)
儲存格F10:{=SUM(IF((單價>=300)*(單價<=600),金額,))}

沒有留言:

張貼留言

好康東東