網友問到:在 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)*業績)}

arrow
arrow
    文章標籤
    Excel SUMPRODUCT 陣列公式
    全站熱搜

    vincent 發表在 痞客邦 留言(0) 人氣()