學不完．教不停．用不盡

在 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。

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