網友問到：在 Excel 中如何將三欄的資料轉換為表格形式呈現？如下圖，圖左中每一列的資料都沒有重覆，要將其轉換為左上和左下兩種表格呈現，該如何處理？
網友常問到 SUMPRODUCT 函數和陣列的關係，藉這個例子來比較一下。
【公式設計與解析】
(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)。
同理：
儲存格F13：=SUMPRODUCT((人員=F$12)*(季別=$E13)*業績)
(3) 使用陣列公式
儲存格F2：{=SUM((人員=$E2)*(季別=F$1)*業績)}
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動產生「｛｝」。
其分析原理和 SUMPRODUCT 函數完全相同。
由於 SUMPRODUCT 函數的參數即是以陣列，所以不需要再使用 Ctrl+Shift+Enter 鍵來讓 Excel 使用陣列運算。而 SUM 函數並非預設以陣列運算，所以要多一個操作來告訴 Excel 要使用陣列運算。當 Excel 會自動產生「｛｝」時，該公式即會將運算內容以陣列來運算。
同理：
儲存格F13：{=SUM((人員=F$12)*(季別=$E13)*業績)}
留言列表