2011年12月9日 星期五

Excel-SUMPRODUCT練習

下圖左是在 Excel 中常見的一種資料表,想要執行各種運算(如下圖右),而且全部以 SUMPRODUCT 函數來練習。

【準備工作】

選取儲存格A1:D26,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」選項,建立「類別、售價、打折、數量」四個名稱。

【建立公式】

(1) 所有售出的原價總和

儲存格G2:=SUMPRODUCT(售價,數量)

(2) 所有售出折扣後總和

儲存格G3:=SUMPRODUCT(售價,打折,數量)

(3) 售價大於1500的項目數

儲存格G4:=SUMPRODUCT(--(售價>1500))

公式中的「--」,乃是為了將公式中的 True/False 陣列轉換為 1/0 陣列來進行運算。

(4) 售價大於1500的總數量

儲存格G5:=SUMPRODUCT(--(售價>1500),數量)

也可以寫成如下形式:

儲存格G5:=SUMPRODUCT((售價>1500)*數量)

當使用「*」運算時,也會自動將公式中的 True/False 陣列轉換為 1/0 陣列進行運算。

(5) 類別B的銷售數量總和

儲存格G6:=SUMPRODUCT(--(類別="B"),數量)

(6) 類別A的銷售金額總和

儲存格G7:=SUMPRODUCT(--(類別="A"),售價,打折,數量)

(7) 打折不大於80%的銷售總額

儲存格G8:=SUMPRODUCT(--(打折<80%),售價,打折,數量)

(8) 類別D中打折不大於80%的銷售數量

儲存格G9:=SUMPRODUCT(--(類別="D"),--(打折<80%),數量)

(9) 打折50%的銷售總額

儲存格G10:=SUMPRODUCT(--(打折=50%),售價,打折,數量)

(10) 類別C中數量大於10的銷售總額

儲存格G11:=SUMPRODUCT(--(類別="C"),--(數量>10),售價,打折,數量)


 

【延伸學習】

SUMPRODUCT 函數是將參數中的陣列相乘後加總,如果要將以上10個運算,全部改以「陣列公式」來運算,該如何撰寫公式?記得:輸入完陣列公式,要按 Ctrl+Shift+Enter 鍵。

儲存格G2:={=SUM(售價*數量)}

儲存格G3:{=SUM(售價*打折*數量)}

儲存格G4:{=SUM(--(售價>1500))}

儲存格G5:{=SUM((售價>1500)*數量)}

儲存格G6:{=SUM((類別="B")*數量)}

儲存格G7:{=SUM((類別="A")*售價*打折*數量)}

儲存格G8:{=SUM((打折<80%)*售價*打折*數量)}

儲存格G9:{=SUM((類別="D")*(打折<80%)*數量)}

儲存格G10:{=SUM((打折=50%)*售價*打折*數量)}

儲存格G11:{=SUM((類別="C")*(數量>10)*售價*打折*數量)}

 

【補充資料】

相關函數說明,請參閱微軟網站。

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

沒有留言:

張貼留言

好康東東