在 Excel 的工作表中有一個含有類別和項目的清單,如何改列出矩陣表格式的呈現?

Excel-將清單改成矩陣表格式呈現(SUMPRODUCT,OFFSET)

 

【公式設計與解析】

先選儲存格A1:B23,按Ctrl+Shift+F3鍵,勾選「頂端列」,定義名稱:類別、項目。

來看以下的分解作法。

Excel-將清單改成矩陣表格式呈現(SUMPRODUCT,OFFSET)

1. 使用輔助欄位

儲存格C2:=COUNTIF($A$2:A2,A2)

計算A欄第一個儲存格至對應儲存格中,和對應儲存格相同者出現的次數。

複製儲存格C2,貼至儲存格C2:C23。

 

2.利用輔助欄位將清單轉換為表格

儲存格E2:=SUMPRODUCT((類別=$D2)*(輔助=COLUMN(A:A))*ROW(項目))

複製儲存格E2,貼至儲存格E2:J6。

SUMPRODUCT函數中使用條件:(類別=$D2)*(輔助=COLUMN(A:A))

判斷「類別」陣列中和儲存格D2相同者,及「輔助」陣列中分別和第1,2,3,...項相同者,傳回 TRUE/FALSE 陣列。其中『*』相當於執行邏輯 AND 運算。

COLUMN(A:A)向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(B:B)=3→...。

在上述的TRUE/FALSE 陣列中,只有一個是 TRUE,其餘均為 FALSE。再乘以「ROW(項目)」時,將會得到一個數值(TRUE/FALSE 陣列中唯一的 TRUE 者所對應的列號),其餘均為0的結果。即傳回項目陣列對應的儲存格列號。

 

3.利用OFFSET函數取得對應的儲存格內容

儲存格E2:{=IFERROR(OFFSET($B$1,SUMPRODUCT((類別=$D2)*(輔助=COLUMN(A:A))*ROW(項目))-1,0),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

將第(2)式代入OFFSET函數取得對應的儲存格內容,並利用IFERROR函數將傳回錯誤的儲存格內容以空字串顯示。

複製儲存格E2,貼至儲存格E2:J6。

Excel-將清單改成矩陣表格式呈現(SUMPRODUCT,OFFSET)

類別和項目的內容任意排列,公式仍適用。

Excel-將清單改成矩陣表格式呈現(SUMPRODUCT,OFFSET)

    文章標籤

    Excel SUMPRODUCT OFFSET

    全站熱搜

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