Excel万金油套路详解 单条件查找返回多行数据 一对多查找 图文

2020年10月29日16:16:59 1 1,946 views
微信公众号 【EXCEL880】
说出需求一键生成VBA代码 VBA中文编程助手VBAYYDS.COM

混迹各学习群的各位伙伴定然都听过【万金油】这个词,Index Small If的惊艳出场,顿时成为了Excel发烧友们爱不释手的【大众情人】。至于这【情人】到底胜在何处,跟着小编一起走进【万金油】的世界吧!

此处小编用查找返回水果【苹果】的销售明细为例,一对多查找,解析如何用查找水果的名称返回该水果的多行销售数据。

效果图预览:

Excel万金油套路详解 单条件查找返回多行数据

解题步骤:

步骤1. 查找所有【苹果】名称的所在行号;

步骤2. 套路用法:将【苹果】所在的行数由小到大排列提取出来;

步骤3. 将【苹果】所在行数的内容全部提取至需要放置的地方。

步骤1:查找所有【苹果】名称的所在行号。

此处我们用If和Row的双人组,判断【苹果】的所在行数。

公式组合模式:If(条件列=条件,Row(条件区));

即E3单元格=IF($A$3:$A$9=$H$2,ROW(A3),0),其中$A$3:$A$9=$H$2是判断A列水果名称中是【苹果】的数据,如果是,则返回当前行号,为3,依次类推,当【苹果】出现在第七行时,E7单元格显示为7。如下图:

Excel万金油套路详解 单条件查找返回多行数据

步骤2:将【苹果】所在的行数由小到大排列提取出来。

公式组合模式:Small(If(条件列=条件,Row(条件区),大数字),Row(A1)));

科普Small函数的用法:SMALL(array,k)。array为数据的范围,k为返回的数据在数据区域里的位置(从小到大)。举个例子,当=small({1,2,3},1),则是返回数据区域中第一小的数字,为1;当=small({1,2,3},2),则是返回数据区域中第二小的数字,为2。回到本例, E3单元格= {SMALL(IF($A$3:$A$9=$H$2,ROW($A$3:$A$9),10000),ROW(A1))}

1. 即在第一步骤的基础上,判断当A列水果名称为【苹果】时,则返回相应行号,如果不是【苹果】,则返回10000;

2. 再用Small函数以从小到大的顺序查找并提取返回行号,如下图:

Excel万金油套路详解 单条件查找返回多行数据

知识点1.数组函数需要三键回车才可以提取出正确答案,即:Ctrl+Shift+Enter;知识点2.在该步骤中,if函数的第三参数此处我们改为10000是因为数据行数不大,如果大家的数据行数很多,可以随意修改为更大的数据。

步骤3:将【苹果】所在行数的内容全部提取至需要放置的地方。

公式组合模式:Index(结果列,Small(If(条件列=条件,Row(条件区),大数字),Row(A1)))科普Index函数的用法:INDEX(array,row_num,column_num),array为数据区域,row_num为行号,column_num为列号,该函数用于返回表或区域中的值或对值的引用。举个例子,当=index({1,2,3},1),则是返回数据区域中第1个数字,为1;当=index({1,2,3},2),则是返回数据区域中第2个数字,为2。

回到本例, 根据需求,我们需要将商品编码,销售日期,价格分别放置在相应单元格中。此时,F5单元格= =INDEX($B:$B,SMALL(IF($A$3:$A$9=$H$2,ROW($A$3:$A$9),10000),ROW(A1))),其中B列为商品编码,即结果列,如需提取销售日期,则将B列改为销售日期所在列,C列即可,依次,价格则为D列。如下图:

Excel万金油套路详解 单条件查找返回多行数据

更多精彩案例讲解请关注郑广学老师微信公众号EXCEL880

说出需求一键生成VBA代码 VBA中文编程助手VBAYYDS.COM

评论已关闭!

目前评论:1   其中:访客  1   博主  0

    • 孙巨峰18785649588 9

      这就是一对多查找