Formulas and Functions
修改此控制項目會使頁面自動更新

XLOOKUP

XLOOKUP 函數會在一個範圍內搜尋指定的值，並傳回另一個直欄中相同橫列的值。

XLOOKUP(搜尋值, 搜尋範圍, 傳回範圍, 若未找到, 相符類型, 搜尋類型)

搜尋值： 在搜尋範圍中搜尋的數值。搜尋數值可包含任何數值或一個 REGEX 字串。

搜尋範圍： 要搜尋的輸入格。

傳回範圍： 要傳回的輸入格。

若未找到： 若找不到相符項目時，用以指定顯示訊息的引數（可留空）。

相符類型： 指定要搜尋的相符類型的引數（可留空）。

完全相同或下一個最小值（-1）： 沒有符合項目時傳回錯誤。

完全符合（0 或省略）： 沒有完全符合時傳回錯誤。

完全相同或下一個最大值（1）： 沒有符合項目時傳回錯誤。

萬用字元（2）： *、? 和 ~ 有特殊的意義。若你使用萬用字元，REGEX 僅可用於 XLOOKUP。

搜尋類型： 指定搜尋範圍順序的引數（可留空）。

二元降冪（-2）： 二元搜尋需要以降冪排序的範圍，否則會傳回錯誤。

最後一個到第一個（-1）： 搜尋從最後一個到第一個範圍。

第一個到最後一個（1 或省略）： 搜尋從第一個到最後一個範圍。

二元升冪（2）： 二元搜尋需要以升冪排序的範圍，否則會傳回錯誤。

附註

  • 若搜尋範圍或傳回範圍為跨越參照（例如「B」），則會自動忽略頁首與頁尾。

範例

下表（標題為「產品」）列出產品和其屬性，例如尺寸和價格：

A

B

C

D

E

1

產品

長度（cm）

寬度（cm）

寬度（kg）

價格

2

產品 1

16

17

10

$82.00

3

產品 2

16

20

18

$77.00

4

產品 3

11

11

15

$88.00

5

產品 4

15

16

20

$63.00

使用 XLOOKUP 搜尋

使用 XLOOKUP，你可以在試算表中插入公式，使用方式為先提供產品名稱，然後提供包含你想傳回的值之直欄，藉此傳回關聯值。例如，如果你想傳回上表中產品 1 的寬度，可以使用下列公式，此公式會傳回 17 cm：

公式編輯器顯示公式 =XLOOKUP(產品::$A2,產品::A,寬度)。

這個公式中使用了以下引數：

  • 搜尋值：產品::$A2，這是包含產品 1 的「產品」表格中輸入格的絕對參照。

  • 搜尋範圍：產品::A，要從中搜尋產品 1 的直欄。

  • 傳回範圍：寬度，此直欄包含要傳回與產品 1 關聯的值。

  • 相符類型：已省略。如果省略相符類型，XLOOKUP 會依預設搜尋完全符合的項目。

設定若未找到字串

如果你想搜尋特定產品長度，並傳回其相符寬度以及沒有找到符合項目時要傳回的字串，你可以使用下列公式，它會傳回「無符合項目」：

公式編輯器顯示公式 =XLOOKUP(13,長度,寬度,"無符合項目",0)。

在此公式中，若未找到引數用於執行更具體的搜尋：

  • 搜尋值：13，要在搜尋範圍所指定範圍中搜尋的值。

  • 搜尋範圍：長度，要從中搜尋的直欄。

  • 傳回範圍：寬度，此直欄包含找到搜尋值的符合項目時要傳回的值。

  • 若未找到："無符合項目"，找不到長度為 13 cm 的產品時要顯示的字串。

  • 相符類型：完全符合 (0)。這只會搜尋長度 13 cm。

尋找第二接近的值

XLOOKUP 也可以根據特定值和接近值提供更廣泛的搜尋。若更改上方公式中的相符類型，便可傳回長度符合 13 cm 的寬度，或第二小的值。下方公式會傳回寬度 11 cm：

公式編輯器顯示公式 =XLOOKUP(13,長度,寬度,"無符合項目",1,-1)。

在此公式中，引數跟上方一樣，不同之處是用於相符類型以改變表格搜尋方式的值：

  • 相符類型： 完全相同或下一個最小值（-1）。此公式會搜尋長度 13 cm，如果找不到該值，則會尋找「長度」直欄中下一個最小值。

更改搜尋順序

某些情況下，改變使用 XLOOKUP 搜尋的表格順序可能很好用。例如，在上表中，有兩樣產品的長度為 16 cm，因此如果使用搜尋值和搜尋範圍在「長度」直欄中搜尋 16 cm，可能會出現兩個符合項目。你可以使用公式設定搜尋順序，類似以下傳回 20 cm 的公式：

公式編輯器顯示公式 =XLOOKUP(16,長度,寬度,"無符合項目",1,-1)。

在此公式中，搜尋類型引數用於設定 XLOOKUP 搜尋表格以找到符合項目的順序：

  • 搜尋值：16，要在搜尋範圍所指定範圍中搜尋的值。

  • 搜尋範圍：長度，要從中搜尋的直欄。

  • 傳回範圍：寬度，此直欄包含找到搜尋值的符合項目時要傳回的值。

  • 若未找到："無符合項目"，找不到長度為 16 cm 的產品時要顯示的字串。

  • 相符類型：完全相同或下一個最大值（1）。此公式會搜尋長度 16 cm，如果找不到該值，則會尋找「長度」直欄中下一個最大值。

  • 搜尋類型：最後一個到第一個（-1）。這個公式會搜尋直欄的最後一個值到第一個值。

搭配其他函數使用 XLOOKUP

XLOOKUP 也可與其他函數搭配使用，例如 SUM。例如，你可以使用類似下方的公式來傳回 $247，也就是產品 1、2 和 3 的價格加總：

公式編輯器顯示公式 =SUM(XLOOKUP(產品::$A2,產品::A,Price):XLOOKUP(產品::$A4,產品::A,價格))。

在此範例中，第一個 XLOOKUP 會搜尋產品 1 的價格，第二個 XLOOKUP 則搜尋產品 3 的價格。XLOOKUP 函數之間的冒號（:）表示 SUM 不只應傳回產品 1 和產品 3 的總價格，也應傳回兩者之間的任何值。

在下方公式中，XLOOKUP 搭配使用 REGEX，傳回了產品 2，這是寬度開頭為「2」的第一個產品：

公式編輯器顯示公式 =XLOOKUP(REGEX("^2.*"), 產品::C2:C5, 產品::A2:A5, FALSE,2)。

在此範例中，"wildcard (2)" 用於相符類型，目的是在 REGEX 函數中使用萬用字元。

其他範例

給定下列表格：

A

B

C

1

姓名

年齡

薪資

2

Amy

35

71000

3

Matthew

27

81000

4

Chloe

42

86000

5

Sophia

51

66000

6

Kenneth

28

52000

7

Tom

49

62000

8

Aaron

63

89000

9

Mary

22

34000

10

Alice

29

52000

11

Brian

35

52500

=XLOOKUP(49,B2:B11,C2:C11) 會傳回「62000」，即第一個 49 歲員工的薪資。

=XLOOKUP(60000,C2:C11,B2:B11,"無符合項目") 會傳回「無符合項目」，因沒有薪資為 $60,000 的員工。

=XLOOKUP(REGEX("^C.*"), A2:A11, B2:B11, FALSE, 2) 會傳回「42」，即「Chloe」的年齡，此為範圍內第一個姓名以「C」開頭的員工。

也請參閱XMATCH
Morty Proxy This is a proxified and sanitized view of the page, visit original site.
有幫助？
字元限制： 250
最大字元限制為 250。
感謝您的寶貴意見。
Morty Proxy This is a proxified and sanitized view of the page, visit original site.