2013年9月26日 星期四

Excel-清單資料和矩陣表格資料互換

有網友問到:在 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。

沒有留言:

張貼留言

檢視其他文章

好康東東