學不完．教不停．用不盡

有網友問到：在 Excel 中如果有資料是以「清單」形式列表(如下圖左)，想要改以「矩陣」表格列表，該如何轉換？反之，又該如何轉換？

(一) 清單資料轉換為矩陣表格資料(下圖左→下圖右)

儲存格E2：{=IFERROR(INDIRECT(ADDRESS(SMALL(IF(組別=$D2,ROW(組別),FALSE),COLUMN(A:A)),2)),"")}

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

SMALL(IF(組別=$D2,ROW(組別),FALSE),COLUMN(A:A))：在陣列公式中找出和儲存格D2相同組別的第 1 個「列號」，例如：第 1 個「A」在第 5 列。

INDIRECT(ADDRESS(SMALL(IF(組別=$D2,ROW(組別),FALSE),COLUMN(A:A)),2))：透過 INDIRECT 和 ADDRESS 函數查表得到姓名。

因為公式查表可能會產生錯誤訊息，以 IFERROR 函數將錯誤的結果改以空白顯示。

複製儲存格E2，貼至儲存格E2：J7。

 

(二) 矩陣表格資料轉換為清單資料(下圖右→下圖左)-1

儲存格A2：=OFFSET($D$1,MOD(ROW(A1)-1,6)+1,0,,)

MOD(ROW(A1)-1,6)+1：往下複製公式時，可以產生 1, 2, 3, 4, 5, 6 (列)。

透過 OFFSET 函數，將上式之1, 2, 3, 4, 5, 6 代入得到 A, B, C, D, E, F。

儲存格B2：=OFFSET($D$1,MOD(ROW(A1)-1,6)+1,INT((ROW(A1)-1)/6)+1,,)

INT((ROW(A1)-1)/6)+1：往下複製公式時，可以產生 1, 2, 3, 4, 5 (欄)。

透過 OFFSET 函數，，將上式之1, 2, 3, 4, 5 代入得到每一列之各欄對應的姓名。

複製儲存格A2:B2，貼至儲存格A2:B30。

 

(三) 矩陣表格資料轉換為清單資料(下圖右→下圖左)-2

儲存格A2：=OFFSET($D$1,INT((ROW(A1)-1)/5)+1,0,,)

INT((ROW(A1)-1)/5)+1：當向下複製公式時，產生 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, ….。

將上式代入 OFFSET 函數得到 A, A, A, A, A, B, B, B, B, B, C, …。

儲存格B2：=OFFSET($D$1,INT((ROW(A1)-1)/5)+1,MOD((ROW(A1)-1),5)+1,,)

MOD((ROW(A1)-1),5)+1：向下複製公式時產生 1, 2, 3, 4, 5 (欄)。

透過 OFFSET 函數，將上式之1, 2, 3, 4, 5 代入得到每一列之各欄對應的姓名。

複製儲存格A2:B2，貼至儲存格A2:B30。