在 Excel 中經常會遇到表格資料轉換的問題,以下圖為例,
*如何將下圖右表中教師在每週中各天授課的班級轉換為下圖左表中?
*如何將下圖左表中班級在每週中各天授課的教師轉換為下圖右表中?
1. 表格與清單轉換(右表→左表)
定義名稱:
選取儲存格I2:M47,按 Ctrl+Shift+F3 鍵,為儲存格範圍定義名稱:週一、週二、週三、週四、週五。
設計公式:
儲存格B2:=OFFSET($H$1,MATCH($A2,INDIRECT(B$1),0),0)
複製儲存格B2,貼至儲存格B2:F22。
(1) INDIRECT(B$1)
利用 INDIRECT 函數將儲存格B1的文字內容轉換為儲存格位址(週一)。
(2) MATCH($A2,INDIRECT(B$1),0)
利用 MATCH 函數比對在儲存格範圍(週一)中,儲存格A2內容對應的位置,傳回一數字。
(3) OFFSET($H$1,MATCH($A2,INDIRECT(B$1),0),0)
利用 OFFSET 函數,以儲存格H1為起點,依據第(2)式傳回值取得對應的儲存格內容。
2. 表格與清單轉換(左表→右表)
定義名稱:
選取儲存格B2:F22,按 Ctrl+Shift+F3 鍵,為儲存格範圍定義名稱:星期一、星期二、星期三、星期四、星期五。
設計公式:
儲存格I2:=IFERROR(OFFSET($A$1,MATCH($H2,INDIRECT(I$1),0),0),"")
複製儲存格I2,貼至儲存格I:F47。
(1) INDIRECT(I$1)
利用 INDIRECT 函數將儲存格B1的文字內容轉換為儲存格位址(星期一)。
(2) MATCH($H2,INDIRECT(I$1),0)
利用 MATCH 函數比對在儲存格範圍(星期一)中,儲存格H2(陳于恩)內容對應的位置。
*若是儲存格內容存在於儲存格範圍中,則傳回一數字。
*若是儲存格內容不存在於儲存格範圍中,則傳回錯誤訊息。
(3) OFFSET($A$1,MATCH($H2,INDIRECT(I$1),0),0)
利用 OFFSET 函數,以儲存格A1為起點,依據第(2)式傳回值取得對應的儲存格內容。
(4) IFERROR(OFFSET($A$1,MATCH($H2,INDIRECT(I$1),0),0),"")
因為第(2)式可能傳回錯誤訊息,以 IFERROR 函數將錯誤訊息以空字串表示。
【延伸閱讀】
參考:本部落格中其他關於 Excel MATCH 函數的應用
參考:本部落格中其他關於 Excel INDIRECT 函數的應用
留言列表