2016年10月26日 星期三

Excel-由兩個表格中查詢對應的結果(MATCH,OFFSET,VLOOKUP)

讀者提問:下圖是 Excel 的資料表,如果在綠色區域中的S1~S8欄位中,根據藍色區域中的 S 對照 T 來列出橙色區域中的value。
例如:第2列中的S8位在T3欄位,查表得到T3=0.8,將其填入儲存格E2。
Excel-由兩個表格中查詢對應的結果(MATCH,OFFSET,VLOOKUP)
儲存格E2:=IFERROR(OFFSET($B$12,MATCH(E$1,$A2:$D2,0)-1,0),"")
複製儲存格E2,貼至儲存格E2:L9。
(1) MATCH(E$1,$A2:$D2,0)
找出儲存格E1位於儲存格A2:D2範圍中的那個位置。
(2) OFFSET($B$12,MATCH(E$1,$A2:$D2,0)-1,0)
根據第(1)傳回的位置利用 OFFSET 函數傳回對應的儲存格內容。
(3) IFERROR(第(2)式,"")
若第(2)式的傳回值是錯誤訊息,則顯示空白。

若是原始資料如下安排:(儲存格A1:D1的內容與上圖不同)
Excel-由兩個表格中查詢對應的結果(MATCH,OFFSET,VLOOKUP)
公式調整如下:
儲存格E2:=IFERROR(OFFSET($A$1,0,MATCH(E$2,$A3:$D3,0)-1),"")
複製儲存格E2,貼至儲存格E2:L9。

若是原始資料按排如下:(儲存格A1:D1的內容做了調整)
Excel-由兩個表格中查詢對應的結果(MATCH,OFFSET,VLOOKUP)
儲存格E2:=IFERROR(VLOOKUP(OFFSET($A$1,0,MATCH(E$1,$A2:$D2,0)-1),
$A$12:$B$15,2,FALSE),"")
複製儲存格E2,貼至儲存格E2:L9。
(1) OFFSET($A$1,0,MATCH(E$1,$A2:$D2,0)-1)
找出儲存格E1內容所對應T1~T4中的那一個。
(2) VLOOKUP(第(1)式,$A$12:$B$15,2,FALSE)
根據第(1)式傳回的結果,查詢紅色區域中所對照的value。

沒有留言:

張貼留言

好康東東