分享

PMC函数系列001-XLOOKUP

 leafcho 2022-06-20 发表于浙江

最新版本的Excel更新了好多新函数,新函数在PMC排程中,可以用到地方非常多,特别是XLOOKUP函数,此函数可以向右查询,向左查询,批量查询等;可以替代的函数有:

VLOOKUP、HLOOKUP、以及经典的IFERROR+VLOOKUP 屏蔽错误的经典用法

  • 函数参数

因为功能强大,所以此函数的参数有多个,其中3个必需,3个可选,来自官方的解释:

XLOOKUP 函数搜索区域或数组,然后返回与它找到的第一个匹配项对应的项。 如果不存在匹配项,那么 XLOOKUP 可以返回最接近 () 匹配项。

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

参数

说明

lookup_value

必需*

要搜索的

值 *如果省略,XLOOKUP 将返回在lookup_array中找到
空白单元格。

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 按降序排序的二进制搜索。 如果未排序,将返回无效结果。

上面需要注意的就是,没有VLOOKUP、HLOOKUP 指定列号的参数了,变成直接返回的数组或区域

  • 经典用法01-向右查找

录入公式:=XLOOKUP(L2,B:B,F:F) ,这里为了简单化公式录入,只录入了3个必选参数;和VLOOKUP一样,注意查询的工单号,一定要包含在搜索的区域。

文章图片1

和VLOOKUPR 区别如下:=VLOOKUP(L2,B:F,5,0)

对比下来,VLOOKUP需要时刻关注以搜索区域向右到返回区域的列号

文章图片2
  • 经典用法02-向左查找 (反向查找)

录入公式:=XLOOKUP(J2,B:B,A:A),和向右查找一样的原理,就是需要搜索的内容和搜索区域分别在第1、2参数,返回在第3参数

文章图片3

VLOOKUP 反向查找的话,需要用到嵌套

=VLOOKUP(J2,CHOOSE({1,2},B:B,A:A),2,0)

配合 Choose 强制生成一个新排序的数组,再返回。

文章图片4
  • 经典用法03-向下查找

录入公式:=XLOOKUP(J1,$A$1:$D$1,$A$2:$D$2),这里是多列引用,所以搜索区域和返回区域要锁死,不然向右填充公式的时候,引用区域会偏移;向下查找是行的概念,不是列了,要特别注意;

文章图片5

HLOOKUP 这里也是注意锁定录入公式 =HLOOKUP(J1,$A$1:$D$12,2,0),

文章图片6

如果要返回第多行,可以用ROW(A2)组合一下 =HLOOKUP(J$1,$A$1:$D$12,ROW(A2),0)

文章图片7

XLOOKUP 这边更改一下锁定方式 =XLOOKUP(J$1,$A$1:$D$1,$A2:$D2)

文章图片8
  • 经典用法04-向上查找

向上查找用得非常少,为了演示XLOOKUP 四个方向都可以胜任,这里也演示一下

文章图片9
  • 经典用法05-屏蔽错误

在经典用法01的基础上,录入第4个参数,如果搜索不到返回结果是“找不到“

=XLOOKUP(L2,B:B,F:F,'找不到')

文章图片10

=IFERROR(VLOOKUP(L2,B:F,5,0),'找不到')

如果用VLOOKUP的话,还要用多一个函数,所以说XLOOKUP 这里完胜;

文章图片11
  • 经典用法06-双条件查询及多条件查询

两个条件的查询怎么查?如下图,查询方正电饭煲的数量是多少?

此时用XLOOKUP,只需要用到文本连接符号&就可以了

录入公式:=XLOOKUP(F2&G2,B2:B5&C2:C5,D2:D5)

相当于重新建立一个合并连接区域;

文章图片12

VLOOUP 如果需要多条件查询,需要再次用到CHOOSE 或者 INDEX+MATCH 的经典组合

=VLOOKUP(F2&G2,CHOOSE({1,2},B2:B5&C2:C5,D2:D5),2,0)

文章图片13
  • 经典用法07- 返回多列

录入公式 =XLOOKUP(A9,A2:A5,B2:H5) ,把第三个参数更改为多列,就可以实现多列的批量引用,对于需要连续引用的需求,避免了多次录入公式;

文章图片14

注意结果数组,不能更改,公式为灰色;

文章图片15

如果是VLOOKUP的话,需要配合COLUMN函数来实现,录入公式

=VLOOKUP($A8,$A$2:$H$5,COLUMN(B1),0)

结果如下:

文章图片16
  • 经典用法08- 通配 * 符号查找

有时候我们需要找以什么开头,或者包含什么的时候,XLOOKUP 支持通配 符号来查询的,此时我们需要把第5个参数 录入为2

如下图:

录入公式:=XLOOKUP(A9,A2:A5,B2:B5,,2)

这里要注意,重复值只能返回第1个

文章图片17
文章图片18
  • 经典用法09- 查询最大值或最小值

有时候我们想知道第1个值和最后一个值的时候,比如工序数据库中各种加工产品,第1道工序和最后一道工序是什么?

文章图片19

此时用XLOOKUP可以快速解决这个问题

=XLOOKUP($E2,$A$2:$A$10,$C$2:$C$10,,,1)

=XLOOKUP($E2,$A$2:$A$10,$C$2:$C$10,,,-1)

分别下拉填充公式就可以得到结果

理解 第6个参数 就可以了

1 - 从第一项开始执行搜索。 这是默认选项。

-1 - 从最后一项开始执行反向搜索。

文章图片20

我是古哥:
从事制造行业18年,在企业运营、供应链管理、智能制造系统等方面具有丰富的实战经验。企业智能化,柔性化计划运营管理专家,擅长通过企业流程优化规范,企业管理、导入计划运营提升企业效率;对提高企业准时交货率,降低企业库存,输出智能制造人才有丰富的经验。关注古哥计划,每日分享制造行业,特别是生产计划方面的一些职场干货。

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 全屏 打印 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多