2015年5月16日 星期六

Excel-查表的應用(INDEX,OFFSET,MATCH,VLOOKUP)

網友想要根據一個 Excel 的資料表中的資料(下圖上),藉由查表方式填入另一個資料表中(下圖下),問到該如何處理?

在 Excel 中,可以使用來做為查表的函數有好幾個,例如:INDEX, OFFSET, MATCH, VLOOKUP 等,請者可以將函數名稱在我的部落格中查到很多的相關範例。

【準備工作】

選取儲存格A2:A15,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:編號。

選取儲存格B1:I1,在[公式/名稱管理員]功能表中,自行定義名稱:代號。

 

【設計公式】

查表的公式通常是利用水平和垂直交會的位置來取得查表結果,因此可以使用 MATCH 函數來求得要查表的資料位於表格的第幾欄和第幾列。

求得資料在第幾列:

公式:MATCH(C18,編號,0),找出儲存格C18的內容在編號陣列中位於第幾個。

求得資料在第幾欄:

公式:MATCH(B18,代碼,0),找出儲存格B18的內容在代碼陣列中位於第幾個。

將資料在第幾欄和第幾列代入以下的各個函數中。

 

(1)使用 INDEX 函數

儲存格G18:=INDEX($B$2:$I$15,MATCH(C18,編號,0),MATCH(B18,代碼,0))

簡化公式可看成:INDEX(資料表,第幾列,第幾欄),欄列交會處即為所求。

 

(2)使用 OFFSET 函數

儲存格G18:=OFFSET($A$1,MATCH(C18,編號,0),MATCH(B18,代碼,0))

簡化公式可看成:OFFSET(起始位置,第幾列,第幾欄),欄列交會處即為所求。

 

(3)使用 VLOOKUP 函數

儲存格G18:=VLOOKUP(C18,$A$2:$I$15,MATCH($B18,代碼,0)+1,FALSE)

VLOOKUP 函數中由儲存格C18內容找出位於表格第一欄中的第幾列,再對應 MATCH($B18,代碼,0)+1 求得的第幾欄,欄列交會處即為所求。

 

(4)使用 INDIRECT、ADDRESS 函數

儲存格G18:
=INDIRECT(ADDRESS(MATCH(C18,編號,0)+1,MATCH(B18,代碼,0)+1,1))

簡化公式可看成:=INDIRECT(ADDRESS(第幾列+1,第幾欄+1,,1))

因為 ADDRESS 函數求得的結果為一個欄名列號的位址,所以再透過 INDIRECT 函數取得儲存格內容。

沒有留言:

張貼留言

好康東東