學不完．教不停．用不盡

最近又要為校內同仁上 Excel 的研習課程，做一些講義方便同仁課後參閱。本篇是關於 SUMPRODUCT 函數的說明。

下圖是微軟提供的 SUMPRODUCT 函數說明，主要是執行陣列元素的『乘積和』。

下圖是一個實例，要由『姓名、性別、成績』三個欄位中，求取不同性別的人數，和不同性別的及格和不及格人數。(參考下圖)

為了解說方便，選取儲存格B1:C16，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：性別、成績。

【公式設計與解析】

(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:Ｈ16)

本例會將：{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))

複製儲存格L5，貼至儲存格L5:L6。