如何在Excel中使用XLOOKUP函數

如何在Excel中使用XLOOKUP函數

Excel 中的 Xlookup 函數是什麼?如何在Excel中使用Xlookup?讓我們透過 LuckyTemplates.com 來了解一下吧!

如果您經常使用 Excel,您可能對尋找功能非常熟悉。如果你不知道,你只需要把它簡單地理解為一個Excel函數,用於搜尋電子表格中的行或列上的特定資料或值。

當您必須處理 Excel 電子表格中的大量資料時,此功能特別有用。在 Excel 中,有多種類型的 LOOKUP 函數。XLOOKUP 就是其中之一。那什麼是XLOOKUP函數,XLOOKUP公式是什麼以及如何使用呢?讓我們透過 LuckyTemplates.com 來了解一下吧!

有關 Excel 中 XLOOKUP 函數的知識

Excel中的XLOOKUP函數是什麼?

從本質上講,XLOOKUP 取代了 HLOOKUP、VLOOKUP 和 LOOKUP 等舊函數。它支援垂直和水平查找資料。

換句話說,Excel中的XLOOKUP函數可以幫助您在提供的資料集中快速查找值,無論是水平還是垂直。然後根據不同的行和列傳回對應的值。

有許多方法可以最大限度地利用 XLOOKUP 函數。

如何存取 XLOOKUP 函數

目前,XLOOKUP 僅適用於 Insiders 計畫用戶。加入 Insider 計劃的任何人都可以存取最新的 Excel 功能(當這些功能可用時)。微軟很快就會向所有 Office 365 用戶推出此功能。不幸的是,運行 Office 2010、2013、2016 和 2019 版本的用戶無法使用 XLOOKUP。

如果您的電腦上沒有使用 Microsoft 365 套件,則可能需要升級至 Microsoft 365 才能存取 XLOOKUP 功能。如果您使用的是 Microsoft 365,您將看到 Excel 中啟用了該選項。XLOOKUP 也可在 Office 365 Online 上使用。

XLOOKUP 函數語法

XLOOKUP 函數的語法與 VLOOKUP 和 HLOOKUP 函數的語法類似。如果您以前使用過它們,您會發現使用 XLOOKUP 更方便。以下是 Excel 中 XLOOKUP 函數的語法:

= XLOOKUP (查找值,查找數組,返回數組, [if_not_found], [match_mode], [search_mode])

XLOOKUP 函數最多支援 6 個參數,以下是它們的值。

1.lookup_value (必填):要尋找的值。

2.lookup_array 必需):要搜尋尋找值的陣列。

3. return_array(必要):找到查找值時要從中取得並傳回值的陣列。

4. [if_not_found](可選):當未找到匹配項時,這是傳回的值。

5. [match_mode](可選):此參數可讓您指定要尋找的符合類型。有不同的值來指定它:

  • 0 - 尋找完全匹配,且該值必須​​與lookup_array 中的值完全匹配。當未提及時,該值也設定為預設值。
  • -1 - 尋找完全匹配,一旦找到,將返回下一個較小的值。
  • 1 - 尋找完全匹配,找到後返回下一個較大的值。
  • 2 - 使用通配符執行部分匹配,其中*, ? 有特殊意義。

6. [search_mode](可選):用於指定XLOOKUP在lookup_array中的搜尋模式。有不同的值來指定相同的:

  • 1 - 從第一項開始執行搜尋。當未指定任何內容時,該值設定為預設值。
  • -1 - 從最後一項開始執行反向搜尋。
  • 2 - 在lookup_array中執行二分搜索,其中資料需要按升序排序。如果資料未排序,可能會產生錯誤或錯誤的結果。
  • -2 - 在lookup_array中執行二分搜索,其中資料需要按降序排序。如果資料未排序,可能會產生錯誤或錯誤的結果。

Excel中XLOOKUP函數的優缺點

與VLOOKUP和INDEX/ATCH相比,XLOOKUP仍然是一個有利的功能。但它也有其自身的缺點。

XLOOKUP 函數的優點

  • 下巴可以垂直和水平工作。
  • 只需要 3 個參數,而不是像 VLOOKUP 和 INDEX MATCH 中需要 4 個參數。
  • 始終預設為完全匹配。
  • 可以使用通配符執行部分查找。
  • 可依降序執行查找。
  • 在 INDEX MATCH 中使用一個函數而不是兩個。

XLOOKUP 函數的缺點

  • 對於初學者來說,可選參數可能看起來很複雜。
  • 當選擇兩個範圍並且電子表格中的儲存格過多時,可能會花費更多時間。
  • 尋找並返回長度不同的陣列時傳回錯誤。
  • 記住查找範圍和返回範圍。

使用 XLOOKUP Excel 函數的好處

  • XLOOKUP 讓 Excel 中最常用的公式變得更簡單且不易出錯。您只需寫=XLOOKUP(要尋找的資料、清單、結果清單)即可獲得答案,如果找不到該值,您將獲得答案或#N/A。
  • 預設查找結果是準確的。VLOOKUP 的缺點之一是您必須提及 FALSE 作為最後一個參數才能獲得正確的結果。XLOOKUP 透過預設執行精確匹配來解決這個問題。如果需要,您可以使用匹配模式參數來變更查找行為。
  • 第四個參數支援未找到值的情況。在大多數業務情況下,您將被迫使用 IFERROR 或 IFNA「包裝」來尋找公式以消除錯誤。XLOOKUP 提供了第四個參數,因此如果找不到該值,您可以知道所需的預設結果。
  • XLOOKUP提供選擇參數來尋找特殊情況。您可以透過特殊字元從上方或下方搜索,以及更快的選項來尋找​​已過濾的資料。

如何使用 XLOOKUP 函數

請參閱下面的範例以了解 XLOOKUP 的工作原理。在此範例中,我們需要為 A 列中的每個 ID 傳回 F 列中的部分。

如何在Excel中使用XLOOKUP函數

這是一個尋找精確結果的範例,但 XLOOKUP 函數只需要三種類型的資訊。

下圖顯示了 5 個參數的 XLOOKUP,但需要前三個參數才能得到正確的結果。那麼讓我們來關注它們:

  • Lookup_value:查找值
  • Lookup_array:搜尋範圍
  • Return_array:包含要傳回的值的範圍

如何在Excel中使用XLOOKUP函數

在此範例中,我們將使用以下公式:

=XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

如何在Excel中使用XLOOKUP函數

現在讓我們來探討一下 XLOOKUP 相對於 VLOOKUP 的一些優點。

沒有額外的列索引號

VLOOKUP 的第三個參數是指定從表範圍傳回的資訊列數。這對 XLOOKUP 來說不再是問題,因為該函數允許使用者選擇返回範圍。(本例中的 F 列)。

如何在Excel中使用XLOOKUP函數

並且不要忘記,XLOOKUP 可以看到所選單元格的剩餘數據,這與 VLOOKUP 不同。

新增列時您也不會遇到公式錯誤。如果電子表格中發生這種情況,則返回範圍將自動調整。

如何在Excel中使用XLOOKUP函數

預設精確匹配

使用 VLOOKUP 時,如果需要,使用者必須指定精確匹配。但對於 XLOOKUP,預設值是完全匹配。這減少了第四個參數,並確保新用戶減少錯誤。簡而言之,XLOOKUP 比 VLOOKUP 需要的問題更少,而且用戶友好。

左邊可以查到XLOOKUP

選擇尋找範圍的能力使 XLOOKUP 比 VLOOKUP 更具彈性。使用 XLOOKUP,表中列的順序並不重要。

VLOOKUP 的限制是搜尋表的最左邊的列,然後從右邊指定數量的列傳回。

在下面的範例中,我們需要搜尋 ID(E 列)並傳回人員姓名(D 列)。

如何在Excel中使用XLOOKUP函數

使用下面的公式:

=XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

如何在Excel中使用XLOOKUP函數

使用 XLOOKUP 搜尋範圍

雖然不像精確匹配那麼常見,但您可以使用搜尋公式來尋找範圍內的值。例如,我們希望根據花費的金額返回折扣。

這次我們不是在尋找具體的值,而是需要知道B列中的值屬於E列中的什麼範圍來決定折扣。

如何在Excel中使用XLOOKUP函數

XLOOKUP 有一個可選的第四個參數(記住,它預設為絕對匹配),稱為 match_mode。

如何在Excel中使用XLOOKUP函數

您可以看到,XLOOKUP 可以比 VLOOKUP 更好地找到近似匹配。

有一個選項可以找到小於 (-1) 的最接近的匹配項或大於 (1) 搜尋值的最接近的匹配項。還有一個使用通配符 (2) 的選項,例如 ? 或 *。預設情況下,此設定不會像 VLOOKUP 那樣啟用。

如果找不到精確匹配,本範例中的公式將傳回小於搜尋值的最接近值:

=XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,-1)

如何在Excel中使用XLOOKUP函數

但是,C7 單元格出現錯誤,應該返回 0% 折扣,因為它只消費了 64,不符合獲得折扣的標準。

XLOOKUP函數的另一個優點是它不像VLOOKUP那樣需要升序排列的搜尋範圍。

在查找表底部輸入一個新行,然後開啟公式。透過點擊並拖曳角落來擴大使用範圍。

如何在Excel中使用XLOOKUP函數

該公式立即糾正了錯誤。範圍表末尾有 0 沒有問題。

如何在Excel中使用XLOOKUP函數

XLOOKUP 替代 HLOOKUP 函數

如前所述,XLOOKUP 函數也可以取代 HLOOKUP 函數。一個功能可以取代兩個功能,還有什麼更好的呢?

HLOOKUP函數水平查找,用於按行查找。

此函數不像 VLOOKUP 那麼出名,但在標題位於 A 列且資料位於第 4 行和第 5 行的情況下非常有用,如下例所示。

XLOOKUP 可以雙向搜索,向下列和沿行。

在此範例中,使用公式傳回與 A2 列中的名稱關聯的銷售值。它在第 4 行中尋找名稱並傳回第 5 行的值:

=XLOOKUP(A2,B4:E4,B5:E5)

如何在Excel中使用XLOOKUP函數

XLOOKUP可以從下往上查看

通常,您需要在清單中從上到下尋找以查找某個值第一次出現的位置。XLOOKUP 有第五個參數,名為 search_mode。此參數允許將搜尋重定向為從清單底部開始查找最後出現的值。

在下面的範例中,我們需要查找 A 列中每種產品的庫存水準。

查找表按日期順序排列。我們想要返回上次檢查(最後一次出現的產品 ID)的庫存水準。

如何在Excel中使用XLOOKUP函數

XLOOKUP 函數的第五個參數提供四個選項。在這裡,我們將使用“搜尋從最後到第一個”選項。

如何在Excel中使用XLOOKUP函數

本例所使用的公式:

=XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,-1)

如何在Excel中使用XLOOKUP函數

在此公式中,第四個參數被省略。它是可選的,我們希望預設值是完全匹配的。

XLOOKUP 函數是 VLOOKUP 和 HLOOKUP 函數的預期後繼函數。本文使用了一系列範例來示範 XLOOKUP 函數的優點。

XLOOKUP 在尋找功能方面是一個很大的改進。XLOOKUP 函數的唯一限制是它不向後相容。如果您與舊版的 Microsoft Office 套件共用電子表格,則可能會收到錯誤。

但是,您可以從 Office 365 Online 存取 XLOOKUP。XLOOKUP 仍在開發中,因此 Excel 用戶需要一段時間才能採用它。

祝您成功!

XLOOKUP 和 VLOOKUP 的差別

查找表

X查找

預設完全匹配

傳回尋找右側的值

傳回尋找左側的值

按行尋找

傳回多個值

需要對查找列進行分類

僅近似匹配

僅二分查找

從上到下搜尋

從下往上搜尋

二分查找

自訂未找到查找值時的消息

透過通配符搜尋

精確搜尋

近似搜尋傳回下一個較小的值

近似搜尋傳回下一個較大的值

如果查找值未排序,可能會傳回不正確的值

Y - 近似值

N - 近似值,

Y - 二進位

新增列來尋找表格可能會破壞公式

為什麼 XLOOKUP 比 VLOOKUP 更好?

儘管VLOOKUP有其自身的用途,但它比XLOOKUP的功能更有限。這使得 XLOOKUP 成為需要處理大量資料時最受歡迎的選擇。因此,它運行得更快並且錯誤更少。

  • XLOOKUP不限制資料範圍:XLOOKUP比VLOOKUP更有彈性。VLOOKUP 可以尋找僅位於表格最左側欄位的值,並從右側對應欄位傳回值。同時,XLOOKUP只需要更簡單的步驟,並且可以在任一方向傳回任何列的值。
  • XLOOKUP的預設結果是最準確的值
  • 預設情況下,Excel 中的 VLOOKUP 函數總是會尋找近似結果。但是,如果您需要精確的結果,則應在 range_lookup 參數中輸入 FALSE。
  • XLOOKUP 函數在特定範圍內搜尋查找值,並且始終預設為精確值。如果找不到,您可以編輯參數以查找小於或大於查找值的近似匹配。

其他原因:

  • XLOOKUP可以處理列插入或刪除。
  • XLOOKUP 實作垂直和水平查找。
  • XLOOKUP 提供更聰明的近似匹配。
  • XLOOKUP 需要較少的儲存格參考。

Excel中的IFERROR函數、公式及用法

Excel中的IFERROR函數、公式及用法

學習如何在 Excel 中使用非常常用的 IFERROR 函數,提升您的數據處理效率。

如何在 Excel 中使用 VSTACK 和 HSTACK 函數

如何在 Excel 中使用 VSTACK 和 HSTACK 函數

想知道如何在 Excel 中堆疊和附加資料嗎?讓我們一起學習如何使用 Microsoft Excel 中的 VSTACK 和 HSTACK 函數!

SUBTOTAL 函數:公式以及如何在 Excel 中使用 SUBTOTAL 函數

SUBTOTAL 函數:公式以及如何在 Excel 中使用 SUBTOTAL 函數

了解 Excel 中的 SUBTOTAL 函數及其用途,讓計算更簡單有效!

Excel中的除法函數:MOD函數(求餘數)與QUOTIENT函數(求整數部分)

Excel中的除法函數:MOD函數(求餘數)與QUOTIENT函數(求整數部分)

MOD函數是Excel中除法求餘的函數。學習如何輕鬆使用MOD函數和QUOTIENT函數來計算餘數和整數部分,提升您的Excel技能。

在 Excel 上玩“拼圖”

在 Excel 上玩“拼圖”

Reddit 用戶 MehmetSalihKoten 在 Microsoft Excel 中創建了俄羅斯方塊的全功能版本。

如何使用Excel中的轉換功能

如何使用Excel中的轉換功能

要轉換 Excel 中的測量單位,我們將使用 Convert 函數。

Excel中HLOOKUP函數的使用方法

Excel中HLOOKUP函數的使用方法

HLOOKUP 函數是什麼?如何在Excel中使用HLOOKUP函數?讓我們來和 LuckyTemplates 一起找出答案吧!

如何在 Excel 中使用 VALUE 函數

如何在 Excel 中使用 VALUE 函數

Excel中的VALUE函數是什麼?Excel中的數值公式是什麼?讓我們透過 LuckyTemplates.com 來了解一下吧!

Excel EOMONTH函數,EOMONTH函數如何使用

Excel EOMONTH函數,EOMONTH函數如何使用

Excel中的EOMONTH函數用於顯示給定月份的最後一天,實作非常簡單。然後,您將根據我們輸入的數據獲得特定月份的最後一天。

如何在 Excel VBA 中使用 do-while 循環

如何在 Excel VBA 中使用 do-while 循環

想要在 Excel VBA 中自動執行重複任務嗎?因此,讓我們學習如何使用 Do-While 迴圈來實作一系列重複操作,直到滿足條件。