2017年8月30日 星期三

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

參考下圖,網友想要將 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),"")

沒有留言:

張貼留言

檢視其他文章

好康東東