在 Excel 的工作表中有一個含有類別和項目的清單,如何改列出矩陣表格式的呈現?
【公式設計與解析】
先選儲存格A1:B23,按Ctrl+Shift+F3鍵,勾選「頂端列」,定義名稱:類別、項目。
來看以下的分解作法。
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。
類別和項目的內容任意排列,公式仍適用。
留言列表