Excel 裡的函數那麼多，依照 20/80 理論，大概只要使用其中的 20% 函數，就可以完成 80% 所有的工作。那麼，總有一些重要的函數要優先學習，其中，SUMPRODUCT 函數應該就是其中非常重要的函數之一了。
SUMPRODUCT 是在計算陣列的乘積和。其語法為：
SUMPRODUCT (array1， [array2]， [array3]， ...)
即在執行array1、array2、…的乘積和。
例如下圖所示，要執行甲欄和乙欄的乘積和，其中四種公式的寫法都在表達相同的概念，
(1) =SUMPRODUCT({1;3;5;7},{2;4;6;8})
(2) =SUMPRODUCT({2;12;30;56})
(3) =SUMPRODUCT(A3:A6,B3:B6)
(4) =SUMPRODUCT(A3:A6*B3:B6)
都是在執行「=A3*B3+A4*B4+A5*B5+A6*B6」運算。
下圖中的運算是「一欄和一欄」執行乘積和：
依此概念，在應用上可以變成「一欄和多欄」執行乘積和。
要注意：欄和欄在對應上必須是相同列數(N)。
這是另一種形式的乘積和例子。
你可以如何應用 SUMPRODUCT 函數？
例如：用來計算在人員清單裡，男生的數量總和。
使用公式：=SUMPRODUCT((A2:A5="男")*B2:B5)」
其中「A2:A5="男"」是條件判斷式，會傳回 TRUE／FALSE 的陣列。
公式中的「*」運算，相當於執行邏輯 AND運算，計算過程中會將 TRUE／FALSE 陣列轉換為 1／0 陣列。
最後由 SUMPRODUCT 函數將 1／0 陣列和數量的陣列執行乘積和運算。
相同原理，可以用來判斷一個數值清單中，偶數／奇數的個數。
先利用 MOD 函數來計算一個數除以2的餘數，若為 0 者是偶數，若為 1 者是奇數。
公式中的「*」運算，相當於執行邏輯 AND 運算，計算過程中會將 TRUE／FALSE 陣列轉換為 1／0 陣列。
最後由 SUMPRODUCT 函數將 1／0 陣列和數量的陣列執行乘積和運算。
有了以上的運算思維了，現在把條件設定為一個以上。
例如，下圖中要計算丙級通過的人數。所以必須判斷二個條件：
條件一：判斷檢定中是「丙級」者，傳回 TRUE／FALSE 陣列。
條件二：判斷成績中是「通過」者，傳回 TRUE／FALSE 陣列。
公式中的「*」運算，相當於執行邏輯 AND 運算，計算過程中會將 TRUE／FALSE 陣列轉換為 1／0 陣列。
最後由 SUMPRODUCT 函數將 1／0 陣列和數量的陣列執行乘積和運算。
有了以上的經驗，你可以擴大使用範圍了，用來計算下圖中的各類人數。
參考下圖，為了解說方便，先設定儲存格範圍名稱。
選取儲存格A1:D21，按 Ctrl+Shift+F3 鍵，勾選「頂端列」。
定義名稱：性別、年級、項目、結果。
(1) 儲存格G2：=SUMPRODUCT((性別=G$1)*(年級=$F2))
複製儲存格G2，貼至儲存格G2:H4。
(2) 儲存格G6：=SUMPRODUCT((項目=G$5)*(年級=$F6))
複製儲存格G6，貼至儲存格G6:H8。
(3) 儲存格G10：=SUMPRODUCT((結果=G$9)*(年級=$F10))
複製儲存格G10，貼至儲存格G10:H12。
(4) 儲存格G14：=SUMPRODUCT((項目=G$13)*(性別=$F14))
複製儲存格G14，貼至儲存格G14:H15。
(5) 儲存格G18：=SUMPRODUCT((項目=G$16)*(結果=$F18))
複製儲存格G17，貼至儲存格G17:H18。
(6) 儲存格G21：=SUMPRODUCT((結果=G$19)*(性別=$F15))
複製儲存格G20，貼至儲存格G20:H21。
留言列表