Formulas and Functions

选择版本：

修改这个控件会自动更新这一页面

欢迎使用
- DATE
- DATEDIF
- DATEVALUE
- DAY
- DAYNAME
- DAYS360
- EDATE
- EOMONTH
- HOUR
- ISOWEEKNUM
- MINUTE
- MONTH
- MONTHNAME
- NETWORKDAYS
- NOW
- SECOND
- TIME
- TIMEVALUE
- TODAY
- WEEKDAY
- WEEKNUM
- WORKDAY
- YEAR
- YEARFRAC
- BASETONUM
- BESSELJ
- BESSELY
- BIN2DEC
- BIN2HEX
- BIN2OCT
- BITAND
- BITOR
- BITXOR
- BITLSHIFT
- BITRSHIFT
- CONVERT
- DEC2BIN
- DEC2HEX
- DEC2OCT
- DELTA
- ERF
- ERFC
- GESTEP
- HEX2BIN
- HEX2DEC
- HEX2OCT
- NUMTOBASE
- OCT2BIN
- OCT2DEC
- OCT2HEX
- ACCRINT
- ACCRINTM
- BONDDURATION
- BONDMDURATION
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNUM
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENCYCODE
- CURRENCYCONVERT
- CURRENCYH
- DB
- DDB
- DISC
- EFFECT
- FV
- INTRATE
- IPMT
- IRR
- ISPMT
- MIRR
- NOMINAL
- NPER
- NPV
- PMT
- PPMT
- PRICE
- PRICEDISC
- PRICEMAT
- PV
- RATE
- RECEIVED
- SLN
- STOCK
- STOCKH
- SYD
- VDB
- XIRR
- XNPV
- YIELD
- YIELDDISC
- YIELDMAT
- AND
- BYCOL
- BYROW
- FALSE
- IF
- IFERROR
- IFS
- ISBLANK
- ISDATE
- ISERROR
- ISEVEN
- ISNUMBER
- ISNUMBERORDATE
- ISODD
- ISOMITTED
- ISTEXT
- LAMBDA
- LAMBDA.APPLY
- LET
- MAKEARRAY
- MAP
- NOT
- OR
- REDUCE
- SCAN
- SWITCH
- TRUE
- ABS
- CEILING
- COMBIN
- EVEN
- EXP
- FACT
- FACTDOUBLE
- FLOOR
- GCD
- INT
- LCM
- LN
- LOG
- LOG10
- MDETERM
- MINVERSE
- MMULT
- MOD
- MROUND
- MULTINOMIAL
- MUNIT
- ODD
- PI
- POLYNOMIAL
- POWER
- PRODUCT
- QUOTIENT
- RAND
- RANDARRAY
- RANDBETWEEN
- ROMAN
- ROUND
- ROUNDDOWN
- ROUNDUP
- SEQUENCE
- SERIESSUM
- SIGN
- SQRT
- SQRTPI
- SUBTOTAL
- SUM
- SUMIF
- SUMIFS
- SUMPRODUCT
- SUMSQ
- SUMX2MY2
- SUMX2PY2
- SUMXMY2
- TRUNC
- ADDRESS
- AREAS
- CHOOSE
- CHOOSECOLS
- CHOOSEROWS
- COLUMN
- COLUMNS
- DROP
- EXPAND
- FILTER
- FORMULATEXT
- GETPIVOTDATA
- HLOOKUP
- HSTACK
- HYPERLINK
- INDEX
- INDIRECT
- INTERSECT.RANGES
- LOOKUP
- MATCH
- OFFSET
- REFERENCE.NAME
- ROW
- ROWS
- SORT
- SORTBY
- TAKE
- TOCOL
- TOROW
- TRANSPOSE
- UNION.RANGES
- UNIQUE
- VLOOKUP
- VSTACK
- WRAPCOLS
- WRAPROWS
- XLOOKUP
- XMATCH
- AVEDEV
- AVERAGE
- AVERAGEA
- AVERAGEIF
- AVERAGEIFS
- BETADIST
- BETAINV
- BINOMDIST
- CHIDIST
- CHIINV
- CHITEST
- CONFIDENCE
- CORREL
- COUNT
- COUNTA
- COUNTBLANK
- COUNTIF
- COUNTIFS
- COVAR
- CRITBINOM
- DEVSQ
- EXPONDIST
- FDIST
- FINV
- FORECAST
- FREQUENCY
- GAMMADIST
- GAMMAINV
- GAMMALN
- GEOMEAN
- HARMEAN
- INTERCEPT
- LARGE
- LINEST
- LOGINV
- LOGNORMDIST
- MAX
- MAXA
- MAXIFS
- MEDIAN
- MIN
- MINA
- MINIFS
- MODE
- NEGBINOMDIST
- NORMDIST
- NORMINV
- NORMSDIST
- NORMSINV
- PERCENTILE
- PERCENTRANK
- PERMUT
- POISSON
- PROB
- QUARTILE
- RANK
- SLOPE
- SMALL
- STANDARDIZE
- STDEV
- STDEVA
- STDEVP
- STDEVPA
- TDIST
- TINV
- TTEST
- VAR
- VARA
- VARP
- VARPA
- WEIBULL
- ZTEST
- ARRAYTOTEXT
- CHAR
- CLEAN
- CODE
- CONCAT
- CONCATENATE
- COUNTMATCHES
- DOLLAR
- EXACT
- FIND
- FIXED
- LEFT
- LEN
- LOWER
- MID
- PLAINTEXT
- PROPER
- REGEX
- REGEX.EXTRACT
- REPLACE
- REPT
- RIGHT
- SEARCH
- SUBSTITUTE
- T
- TEXTAFTER
- TEXTBEFORE
- TEXTBETWEEN
- TEXTJOIN
- TEXTSPLIT
- TRIM
- UPPER
- VALUE
- ACOS
- ACOSH
- ASIN
- ASINH
- ATAN
- ATAN2
- ATANH
- COS
- COSH
- DEGREES
- RADIANS
- SIN
- SINH
- TAN
- TANH
版权

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：此公式中使用了以下参数：待搜索值：产品::$A2，即“产品”表格中包含“产品 1”的绝对单元格引用。待搜索范围：产品::A，即要搜索“产品 1”的列。待返回范围：宽度，即包含要返回的“产品 1”相关值的列。匹配类型：省略。如果省略匹配类型，XLOOKUP 会默认搜索最佳匹配。

使用 XLOOKUP 搜索

你可以使用 XLOOKUP 在电子表格中插入函数，通过先后提供产品名称和包含想要的返回值的列来返回任何相关的值。例如，如果你想要返回上表中产品 1 的宽度，可以使用以下公式返回 17 cm：

此公式中使用了以下参数：

待搜索值：产品::$A2，即“产品”表格中包含“产品 1”的绝对单元格引用。
待搜索范围：产品::A，即要搜索“产品 1”的列。
待返回范围：宽度，即包含要返回的“产品 1”相关值的列。
匹配类型：省略。如果省略匹配类型，XLOOKUP 会默认搜索最佳匹配。

设定如果未找到字符串
如果想要搜索特定产品的长度并返回其匹配的宽度，以及在未找到匹配项时返回字符串，你可以使用以下可返回“无匹配项”的公式：在此公式中，如果未找到参数用于执行更具体的搜索：待搜索值：13，即要在搜索范围所指定的范围中搜索的值。待搜索范围：长度，即要搜索的列。待返回范围：宽度，即包含与待搜索值匹配且要返回的值的列。如果未找到："无匹配项"，如果未找到长度为 13 cm 的产品，则显示该字符串。匹配类型：精确匹配 (0)。只搜索长度 13 cm。

设定如果未找到字符串

如果想要搜索特定产品的长度并返回其匹配的宽度，以及在未找到匹配项时返回字符串，你可以使用以下可返回“无匹配项”的公式：

在此公式中，如果未找到参数用于执行更具体的搜索：

待搜索值：13，即要在搜索范围所指定的范围中搜索的值。
待搜索范围：长度，即要搜索的列。
待返回范围：宽度，即包含与待搜索值匹配且要返回的值的列。
如果未找到："无匹配项"，如果未找到长度为 13 cm 的产品，则显示该字符串。
匹配类型：精确匹配 (0)。只搜索长度 13 cm。

查找下一个最接近值
XLOOKUP 还可基于特定相近值提供广泛搜索。如果你更改了上述公式的匹配类型，可以返回匹配长度 13 cm 的宽度或下一个最小值。以下公式返回宽度 11 cm：在此公式中，除了匹配类型中用于更改表格搜索方式的值不同之外，参数均与上述相同：匹配类型：精确匹配或下一个最小项 (-1)。搜索长度 13 cm，或者如果未找到该值，则在“长度”列中查找下一个最小值。

查找下一个最接近值

XLOOKUP 还可基于特定相近值提供广泛搜索。如果你更改了上述公式的匹配类型，可以返回匹配长度 13 cm 的宽度或下一个最小值。以下公式返回宽度 11 cm：

公式编辑器显示公式 =XLOOKUP(13,长度,宽度,"无匹配项",1,-1)。

在此公式中，除了匹配类型中用于更改表格搜索方式的值不同之外，参数均与上述相同：

匹配类型： 精确匹配或下一个最小项 (-1)。搜索长度 13 cm，或者如果未找到该值，则在“长度”列中查找下一个最小值。

更改搜索顺序
在某些情况下，使用 XLOOKUP 更改表格的搜索顺序可能会很有用。例如，上表中有两个长度 16 cm 的产品，因此如果使用待搜索值和待搜索范围在“长度”列中搜索 16 cm，会有两个可能匹配项。你可以使用类似的公式设定搜索顺序来返回 20 cm：在此公式中，搜索类型参数用于设定 XLOOKUP 搜索表格中匹配项的顺序：待搜索值：16，即要在搜索范围所指定的范围中搜索的值。待搜索范围：长度，即要搜索的列。待返回范围：宽度，即包含与待搜索值匹配且要返回的值的列。如果未找到："无匹配项"，如果未找到长度为 16 cm 的产品，则显示该字符串。匹配类型：精确匹配或下一个最大项 (1)。搜索长度 16 cm，或者如果未找到该值，则在“长度”列中查找下一个最大值。搜索类型：从最后一个到第一个 (-1)。从后到前搜索列的值。

更改搜索顺序

在某些情况下，使用 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 的价格总和：在本示例中，第一个 XLOOKUP 搜索产品 1 的价格，第二个 XLOOKUP 搜索产品 3 的价格。XLOOKUP 函数之间的冒号 (:) 表示 SUM 应返回的总价不只是产品 1 加上产品 3，还应加上位于这两者之间的任何值。
在以下公式中，XLOOKUP 与 REGEX 配合使用可返回宽度以“2”开头的第一个产品，即产品 2。在本示例中，匹配类型使用“通配符 (2)”以在 REGEX 函数中使用通配符。

配合其他函数使用 XLOOKUP

XLOOKUP 也可与其他函数配合使用，如 SUM。例如，你可以使用以下类型的公式来返回￥247，即产品 1、2、3 的价格总和：

公式编辑器显示公式 =SUM(XLOOKUP(产品::$A2,产品::A,价格):XLOOKUP(产品::$A4,产品::A,价格))。

在本示例中，第一个 XLOOKUP 搜索产品 1 的价格，第二个 XLOOKUP 搜索产品 3 的价格。XLOOKUP 函数之间的冒号 (:) 表示 SUM 应返回的总价不只是产品 1 加上产品 3，还应加上位于这两者之间的任何值。

在以下公式中，XLOOKUP 与 REGEX 配合使用可返回宽度以“2”开头的第一个产品，即产品 2。

公式编辑器显示公式 =XLOOKUP(REGEX("^2.*"), 产品::C2:C5, 产品::A2:A5, FALSE,2)。

在本示例中，匹配类型使用“通配符 (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”，即范围内第一位名字以“C”开头的员工“Chloe”的年龄。

另请参阅XMATCH

有帮助?

公式与函数帮助

XLOOKUP

注释