新知一下
海量新知
5 9 7 5 0 9 3

学会万金油,一对多不愁

EXCEL应用之家 | 传播EXCEL新技能 2021/11/24 10:15

新知达人, 学会万金油,一对多不愁

小伙伴们啊,我们在工作中经常会遇到这样的情况:同一款物料有多家供应商同时供货。这样对于采购方来讲是有很多益处的。下面就有这样一份清单,列明了物料和供应商的对应关系。

新知达人, 学会万金油,一对多不愁

但左侧清单部分的对应关系看起来不清晰。我们如何将其转换为右侧的格式呢?今天和大家再次分享一下一对多查询,即万金油公式的使用方法。

01

这种格式转化,我们在PQ上也可以完成,今天我们着重介绍公式法。

新知达人, 学会万金油,一对多不愁

在单元格E3中输入公式“=IF(COLUMN()-4>COUNTIF($A$3:$A$12,$D3),"",INDEX($B:$B,SMALL(IF($A$3:$A$12=$D3,ROW($A$3:$A$12)),COLUMN()-4)))”,三键回车并向右向下拖曳即可。

思路:

  • 我们先来看IF($A$3:$A$12=$D3,ROW($A$3:$A$12))部分。它是一个判断过程,在源数据中凡是等于单元格D3的,都返回所对应的行号。请注意公式中单元格D3的书写方式,由于要向右向下拖曳,所以这里使用了混合引用。它的结果是{3;4;5;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},表示第3、4、5行是符合条件的,后面会依次提取这三行所对应的供应商名称

  • 接下来我们看SMALL(IF($A$3:$A$12=$D3,ROW($A$3:$A$12)),COLUMN()-4)部分。利用SMALL函数依次提取第1小、第2小和第3小的值,即3、4、5,并把它们作为INDEX函数的参数。由于公式是在E列,因此COLUMN()的值是5,COLUMN()-4即为1。随着公式向右拖曳,它会依次返回2、3...

  • INDEX($B:$B,SMALL(IF($A$3:$A$12=$D3,ROW($A$3:$A$12)),COLUMN()-4))部分,返回对应的供应商的名称

  • 最后利用IF函数做一个判断,当COLUMN()-4>COUNTIF($A$3:$A$12,$D3)时,表明所有的供应商名称都已经提取完毕了,利用IF函数返回空值。这是一个容错处理方式

02

新知达人, 学会万金油,一对多不愁

这个公式也是可以用OFFSET函数来书写的,这里我们就不再写出来了,有兴趣的朋友们可以动手试一试。

万金油组合是非常重要的一个函数组合应用,熟练应用可以帮我们解决许多工作中遇到的问题!

好了朋友们,今天和大家分享的内容就是这些了!喜欢我的文章请分享、转发、点赞和收藏吧!如有任何问题可以随时私信我哦!

-END-

更多“Excel”相关内容

更多“Excel”相关内容

新知精选

更多新知精选