參考下圖,網友想要將 Excel 中的表格內容加以轉換(左圖轉換為右圖),該如何處理?

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


【公式設計與解析】

1. 表格轉換前後的欄名/列名一致

儲存格I2:=IFERROR(OFFSET($A$1,MATCH($H2,B$2:B$5,0),0),"")

(1) MATCH($H2,B$2:B$5,0)

利用 MATCH 函數找出儲存格H2內容在儲存格B2:B5中的位置,傳回一個數值。如果儲存格H2內容不在儲存格B2:B5中,則會傳回錯誤訊息。

(2) OFFSET($A$1,MATCH($H2,B$2:B$5,0),0)

將第(1)式的傳回值代入 OFFSET 函數找出以儲存格A1為起點的對應儲存格內容。

(3) IFERROR(OFFSET($A$1,MATCH($H2,B$2:B$5,0),0),"")

因為第(1)式如果傳回錯誤訊息,則以 IFERROR 函數將其轉換為空字串。


2. 表格轉換前後的欄名/列名不一致

因為轉換前後的表格欄名和列名並不一致,所以需要先定義儲存格範圍的名稱。

選取儲存格B12:F16,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:一、二、三、四、五。

儲存格I13:=IFERROR(OFFSET($A$12,MATCH($H13,INDIRECT(I$12),0),0),"")

(1) INDIRECT(I$12)

利用 INDIRECT 函數將儲存格I2的內容,轉換為對應的儲存格範圍(該範圍已先定義名稱)。整個公式都以 INDIRECT 函數來取代固定的儲存格範圍。

(2) MATCH($H13,INDIRECT(I$12),0)

(3) OFFSET($A$12,MATCH($H13,INDIRECT(I$12),0),0)

(4) IFERROR(OFFSET($A$12,MATCH($H13,INDIRECT(I$12),0),0),"")


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

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