在 Excel 中經常會遇到表格資料轉換的問題,以下圖為例,

*如何將下圖右表中教師在每週中各天授課的班級轉換為下圖左表中?

*如何將下圖左表中班級在每週中各天授課的教師轉換為下圖右表中?

Excel-表格轉換(MATCH,INDIRECT,OFFSET)

 

1. 表格與清單轉換(右表→左表)

Excel-表格轉換(MATCH,INDIRECT,OFFSET)

定義名稱:

選取儲存格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. 表格與清單轉換(左表→右表)

image

定義名稱:

選取儲存格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 函數的應用

參考:本部落格中其他關於 Excel OFFSET 函數的應用

學不完.教不停.用不盡文章列表

arrow
arrow
    文章標籤
    Excel MATCH INDIRECT OFFSET
    全站熱搜

    vincent 發表在 痞客邦 留言(1) 人氣()