2017年2月10日 星期五

Excel-取代VLOOKUP函數查詢資料(INDIRECT,INDEX,MATCH)

在 Excel 中有一個資料表(如下圖左),想要根據『座號』來取得姓名、成績和名次的資料,該如何處理?(如下圖右)
通常你會使用 VLOOKUP 函數或 HLOOKUP 函數來執行查詢工作,在本例中先觀察這個資料表,是由姓名、座號、成績、名次等四個欄位所組成,若要使用 VLOOKUP 函數來查詢,當取用資料在A欄~D欄時,因為A欄是姓名而非座號,所以無法使用。若取資料B欄~D欄,可以順利用座號查詢,但是無法查詢姓名,因為姓名欄位不在資料範圍。而且查詢結果的顯示順序和原資料的排列順序並不相同。
如何以一個公式便能完成多個儲存格的查詢?還有其他方式可以達到查詢結果,來練習看看。
取代VLOOKUP函數查詢(INDIRECT,INDEX,MATCH)

【公式設計與解析】
(1) 定義名稱
若要讓公式精簡易讀,可以藉助定義儲存格範圍的名稱。選取A欄~D欄中有資料的儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名、座號、成績、名次。

(2) 使用 INDEX 函數+MATCH 函數
儲存格F4:=INDEX(INDIRECT(F3),MATCH($G$1,座號,0))
INDIRECT(F3):利用 INDIRECT 函數將儲存格F3的內容(例如:姓名),轉換為儲存格範圍(已定義名稱:姓名)。
MATCH($G$1,座號,0):利用 MATCH 函數取得儲存格G1的內容位於座號陣列中的那一個位置(傳回列號)。
最後,透過 INDEX 函數以查表方式取得座號對應的其他欄位值。
複製儲存格F4,貼至儲存格F4:H4。

沒有留言:

張貼留言

好康東東