在 Excel 裡,可以使用來查詢資料的函數很多,本例要示範以 XLOOKUP 函數如何取代 XLOOKUP、HLOOKUP、INDEX 等函數。

XLOOKUP  VLOOKUP  HLOOKUP  INDEX

1. XLOOKUP 取代 VLOOKUP

Excel-用XLOOKUP取代VLOOKUP,HLOOKUP,INDEX

1. 先選儲存格A3:D3,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名、性別、學號、地點。

2. 由第3欄的學號查詢第1欄的姓名

儲存格G4:=XLOOKUP(F4,學號,姓名)

複製儲存格G4,貼至儲存格G4:G9)

以往,VLOOKUP 函數都必須依賴第1欄裡的內容比對,再取出對應欄位相同位置的儲存格。而 XLOOKUP 已經擺脫這種困境,不論你使用那一個欄位當作比對都可以。

 

3. 利用動態陣列的做法,由第3欄的學號查詢第1欄的姓名。

儲存格G14:=XLOOKUP(F14:F19,學號,姓名)

改成動態陣列的寫法,不需要執行複製/貼上。公式會自動溢出至儲存格對應的位置。

 

2. XLOOKUP 取代 HLOOKUP

Excel-用XLOOKUP取代VLOOKUP,HLOOKUP,INDEX

1. 將儲存格B3:M3定義名稱為:月份,將儲存格A4:A13定義名稱為:人員。

2. 輸入公式:

儲存格B17:=XLOOKUP($B$16:$E$16,月份,INDIRECT(A17))

複製儲存格B17,貼至儲存格B17:B20

這是以水平查詢的概念,結合動態陣列。

在公式中使用 INDIRECT 函數,可以將儲存格內容的字串轉為儲存格位址。

 

3. XLOOKUP 取代 INDEX

Excel-用XLOOKUP取代VLOOKUP,HLOOKUP,INDEX

依「人員」和「月份」來查詢對應的「數量」。

(1) 使用 INDEX 函數

儲存格F16:=INDEX(B4:M13,MATCH(B16,人員,0),MATCH(D16,月份,0))

傳統上使用兩個 MATCH 函數,以取得水平的位置和垂直的位置。再置入 INDEX 函數,查詢對應的資料。

(2) 使用 VLOOKUP 函數

儲存格F16:=XLOOKUP(D16,月份,INDIRECT(B16))

利用 XLOOKUP 函數也可以達到相同的查詢結果。

 

【參考資料】

 XLOOKUP 函數參考微軟提供的說明網頁:XLOOKUP 函數
 INDEX 函數參考微軟提供的說明網頁:INDEX 函數
 VLOOKUP 函數參考微軟提供的說明網頁:VLOOKUP 函數
 HLOOKUP 函數參考微軟提供的說明網頁:HLOOKUP 函數

學不完.教不停.用不盡文章列表

    文章標籤

    Excel 資料處理

    全站熱搜

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