2015年11月19日 星期四

Excel-SUMPRODUCT函數範例與說明(研習範例)

最近又要為校內同仁上 Excel 的研習課程,做一些講義方便同仁課後參閱。本篇是關於SUMPRODUCT 函數的說明。
下圖是微軟提供的 SUMPRODUCT 函數說明,主要是執行陣列元素的『乘積和』。
Excel-SUMPRODUCT函數範例與說明
下圖是一個實例,要由『姓名、性別、成績』三個欄位中,求取不同性別的人數,和不同性別的及格和不及格人數。(參考下圖)
為了解說方便,選取儲存格B1:C16,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:性別、成績。
Excel-SUMPRODUCT函數範例與說明
【公式設計與解析】
(1)
儲存格K2:=SUMPRODUCT((性別=K1)*1)
(性別=K1):判斷『性別』陣列中和儲存格K1是否相同。(觀察儲存格F2:F16)
本例傳回:{TRUE,FALSE,TRUE,TRUE,TRUE,FALSE, ... , TRUE,FALSE,TRUE,TRUE}
(性別=K1)*1:將上式轉換為{1,0,1,1,1,0, …, 1,0,1,1}
SUMPRODUCT 函數再將上式結果的 1/0 陣列執行『乘積和』,即把所有的 1 予以加總,即為所求。
複製儲存格K2,貼至儲存格K2:L2。
(2)
儲存格K5:=SUMPRODUCT((性別=$J5)*(成績>=60))
(性別=$J5)*(成績>=60):觀察儲存格F2:G16,公式中的『*』乃將F欄和G欄的內容相乘,執行過程會將邏輯值 TRUE/FALSE 轉換為數學值 1/0。(觀察儲存格F2:H16)
本例會將:{TRUE,FALSE,TRUE,TRUE,TRUE,FALSE, ... , FALSE,FALSE,TRUE,FALSE}
轉換為:{1,0,1,1,1,1,0, … , 0,0,1,0}
SUMPRODUCT 函數再將上式結果的 1/0 陣列執行『乘積和』,即把所有的 1 予以加總,即為所求。
複製儲存格K5,貼至儲存格K5:K6。
同理,
儲存格L5:=SUMPRODUCT((性別=$J5)*(成績<60 p="">
複製儲存格L5,貼至儲存格L5:L6。

沒有留言:

張貼留言

好康東東