excel如何根据员工ID快速获取员工姓名,XLOOKUP函数使用技巧
小编:思敏 6892阅读 2020.06.09
注意: 此函数当前可用于每月频道中的 Microsoft 365 订阅者。 将在2020年7月开始的半年频道中 Microsoft 365 订阅者提供。
-----------------------------------------------------------------------------------------
语法
XLOOKUP 函数搜索区域或数组,并返回一个与它找到的第一个匹配项相对应的项。 如果不存在匹配项,则 XLOOKUP 可返回最接近(近似值)的匹配项。
= XLOOKUP (lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])
参数 |
说明 |
---|---|
lookup_value 必需 |
查找值 |
lookup_array 必需 |
要搜索的数组或区域 |
return_array 必需 |
要返回的数组或区域 |
[if_not_found] 可选 |
如果找不到有效的匹配项,则返回你提供的 [if_not_found] 文本。 如果找不到有效的匹配项,并且缺少 [if_not_found],则会返回 #N/A。 |
[match_mode] 可选 |
指定匹配类型: 0 - 完全匹配。 如果未找到,则返回 #N/A。 这是默认选项。 -1 - 完全匹配。 如果没有找到,则返回下一个较小的项。 1 - 完全匹配。 如果没有找到,则返回下一个较大的项。 2 - 通配符匹配,其中 *, ? 和 ~ 有特殊含义。 |
[search_mode] 可选 |
指定要使用的搜索模式: 1 - 从第一项开始执行搜索。 这是默认选项。 -1 - 从最后一项开始执行反向搜索。 2 - 执行依赖于 lookup_array 按升序排序的二进制搜索。 如果未排序,将返回无效结果。 2 - 执行依赖于 lookup_array 按降序排序的二进制搜索。 如果未排序,将返回无效结果。 |
示例 1
此示例使用简单的 XLOOKUP 查找国家/地区名称,然后返回其电话国家/地区代码。 它仅包括 lookup_value(单元格 F2)、lookup_array(范围 B2:B11)和 return_array(范围 D2:D11)参数。 它不包括 match_mode 参数,因为 XLOOKUP 默认为完全匹配。
注意: XLOOKUP 与 VLOOKUP 的不同之处在于,它使用单独的查找并返回数组,而 VLOOKUP 使用一个表数组,后跟列索引号。 在本例中,等效的 VLOOKUP 公式是:=VLOOKUP(F2,B2:D11,3,FALSE)
示例 2
在此示例中,我们将根据员工 ID 编号查找员工信息。 与 VLOOKUP 不同,XLOOKUP 可以返回具有多个项的数组,这允许单个公式从单元格 C5: D14 返回员工姓名和部门。
示例 3
此示例将if_not_found参数添加到上面的示例。
示例 4
以下示例在列 C 中查找在单元格 E2 中输入的个人收入,并在列 B 中查找匹配的税率费率。如果未找到任何内容,则将 if-not_found 参数设置为返回0。 Match_mode 参数设置为1,这意味着该函数将查找精确匹配,如果找不到它,它将返回下一个较大的项。 最后,search_mode 参数设置为1,这意味着该函数将从第一个项搜索到最后一个项。
注意: 与 VLOOKUP 不同,lookup_array 列位于 return_array 列的右侧,而 VLOOKUP 只能从左到右查找。
示例 5
接下来,我们将使用嵌套的 XLOOKUP 函数执行垂直和水平匹配。 在这种情况下,它将首先查找 B 列中的毛利润,然后查找表格首行(范围 C5:F5)中的第一季度,并返回两者交集的值。 这类似于结合使用 INDEX 和 MATCH 函数。 你也可以使用 XLOOKUP 替换 HLOOKUP 函数。
单元格 D3:F3 中的公式是:=XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))。
示例 6
此示例使用 SUM 函数,并嵌套两个 XLOOKUP 函数对两个范围之间的所有值求和。 在这种情况下,我们希望对葡萄、香蕉和 include 梨的值进行求和,这些值位于两个值之间。
单元格 E3 中的公式为: = SUM (XLOOKUP (B3,B6: B10,E6: E10): XLOOKUP (C3,B6: B10,E6: E10))
它如何工作? XLOOKUP 返回一个区域,因此当它计算时,该公式最后看起来如下所示: = SUM ($E $7: $E $9)。 可通过选择包含与此类似的 XLOOKUP 公式的单元格来查看其工作原理,然后转到公式 > 公式审核 > 公式求值,再按“求值”按钮逐步执行计算。
相关推荐
- excel如何快速获取整组数据中的最小值,SMALL函数怎么用 本文介绍 Microsoft Excel 中 SMALL 函数的公式语法和用法。说明返回数据集中的第 k 个最小值。 使用此函数以返回在数据集内特定相对位置上的值。语法SMALL(array,k)SMALL 函数语法具有下列参数:Array 必需。 需要找到第 k 个最小值的数组或数值数据区域。K …
- excel如何快速获取单元格数值,CHOOSE函数怎么用 本文介绍 Microsoft Excel 中 CHOOSE 函数的公式语法和用法。说明使用 index_num 返回数值参数列表中的数值。 使用 CHOOSE 可以根据索引号从最多 254 个数值中选择一个。 例如,如果 value1 到 value7 表示一周的 7 天,那么将 1 到 7 之间的数字用作 index_nu…
- excel如何根据条件快速获取指定单元格内容,INDEX函数使用技巧 INDEX 函数返回表格或区域中的值或值的引用。数组形式说明返回表或数组中元素的值,由行号和列号索引选择。当函数 INDEX 的第一个参数为数组常量时,使用数组形式。语法INDEX(array, row_num, [column_num]) INDEX 函数的数组形式具有下列参数:数组 必需。…
- excel SUMPRODUCT函数使用技巧,如何获取数组乘积之和 SUMPRODUCT函数返回对应的区域或数组的乘积之和。 默认运算是乘法,但加、减和除也可能。在此示例中,我们将使用 SUMPRODUCT 返回给定项目和大小的总销售额:SUMPRODUCT 匹配项目 Y/Size M 和 sum 的所有实例,因此,本示例21加41等于62。语法若要使用默认操作…
- 腾讯文档支持批量删除或移动文档吗? 1)电脑web端:①进入https://docs.qq.com/desktop后--按住「 ctrl/shift 」键--选中需要删除或移动的文档;②点击右键--在弹出的界面选择「 移动到/删除 」即可。2)手机端:①进入文档列表--点击界面右上角的「 」--「 批量操作 」;②依次选中需要移动/删除…
- excel怎么快速计算样本标准偏差,STDEV.S函数实用技巧 基于样本估算标准偏差(忽略样本中的逻辑值和文本)。标准偏差可以测量值在平均值(中值)附近分布的范围大小。语法STDEV.S(number1,[number2],...)STDEV.S 函数语法具有下列参数:Number1 必需。 对应于总体样本的第一个数值参数。 也可以用单一数组或对某个…
- 3DMAX提示和技巧 本主题标识使用 Civil View 的一些重要提示和技巧。常规使用屏幕分辨率至少为 1280x1024 的 Civil View。低于此分辨率时,一些面板将占用过多屏幕空间。 将视口设置为线框显示以达到最佳性能。 要尽可能简化用户界面,请在单个视口中工作并关闭 3ds Max 命令面…