2017年2月4日 星期六

Excel-表格轉換(比較SUMPRODUCT和SUM+陣列公式)

網友問到:在 Excel 中如何將三欄的資料轉換為表格形式呈現?如下圖,圖左中每一列的資料都沒有重覆,要將其轉換為左上和左下兩種表格呈現,該如何處理?
網友常問到 SUMPRODUCT 函數和陣列的關係,藉這個例子來比較一下。
Excel-表格轉換(比較SUMPRODUCT和SUM+陣列公式)

【公式設計與解析】
(1) 定義名稱
選取儲存格A1:C21,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:人員、季別、業績。

(2) 使用 SUMPRODUCT 函數
儲存格F2:=SUMPRODUCT((人員=$E2)*(季別=F$1)*業績)
在 SUMPRODUCT 函數中使用二個條件:
人員=$E2:判斷人員陣列中的儲存格是否和儲存格E2相同,傳回 TRUE/FALSE 陣列。
季別=F$1:判斷季別陣列中的儲存格是否和儲存格F1相同,傳回 TRUE/FALSE 陣列。
(人員=$E2)*(季別=F$1)中的『*』運算子,相當於執行邏輯 AND 運算。運算時,會將 TRUE/FALSE 陣列轉換為 1/0 陣列。
將上式的 1/0 陣列和業績陣列相乘,只會留下 1 對應的業績(0 和業績相乘會得到 0),也就是二個條件皆成立所對應的業績。最後由 SUMPRODUCT 函數予以加總(本例結果為:66)。
Excel-表格轉換(比較SUMPRODUCT和SUM+陣列公式)
同理:
儲存格F13:=SUMPRODUCT((人員=F$12)*(季別=$E13)*業績)

(3) 使用陣列公式
Excel-表格轉換(比較SUMPRODUCT和SUM+陣列公式)
儲存格F2:{=SUM((人員=$E2)*(季別=F$1)*業績)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
其分析原理和 SUMPRODUCT 函數完全相同。
由於 SUMPRODUCT 函數的參數即是以陣列,所以不需要再使用 Ctrl+Shift+Enter 鍵來讓 Excel 使用陣列運算。而 SUM 函數並非預設以陣列運算,所以要多一個操作來告訴 Excel 要使用陣列運算。當 Excel 會自動產生「{}」時,該公式即會將運算內容以陣列來運算。
同理:
儲存格F13:{=SUM((人員=F$12)*(季別=$E13)*業績)}

沒有留言:

張貼留言

檢視其他文章

好康東東