|
送人玫瑰，手有余香，请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径！ 【置顶公众号】或者【设为星标】及时接收更新不迷路
小伙伴们啊，以前我们多次向大家介绍过经典的一对多和多对多查询公式，但是我发现从来没有系统地向大家介绍过这组经典公式的原理。随着EXCEL版本不断的更新，在更高版本中已经出现了功能可以完全替代我们今天要介绍的经典公式的函数了。于是我决定抓紧时间做一期一对多、多对多的经典查询公式的内容。伤心，这些经典公示很快就要退出历史舞台了！ 所谓一对多，就是符合某个指定条件的有多个结果，要把这些结果都提取出来。 下面我们一起来看具体的例子。 如下图，根据著作名筛选出对应的人物。  这是一个典型的一对多查询问题。在单元格E2中输入公式“=IFERROR(INDEX($B$2:$B$17,SMALL(IF($A$2:$A$17=$D$2,ROW($A$2:$A$17)-1),ROW(A1))),"")”，三键回车并向下拖曳即可。 这个公式的原理是什么呢? 简单说，就是在A列中找到符合给定条件的数据，然后返回这些数据所在行的行号。然后再返回这些行所对应的B列中的数据。 怎样判断数据是否等于给定条件呢？我们来看，$A$2:$A$17=$D$2这样就可以了。它的运算结果是{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE}，在最后四项涂了黄色的部分表示源数据等于给定条件。 如何返回行号呢？只需要用IF函数做一个判断就可以了。IF($A$2:$A$17=$D$2,ROW($A$2:$A$17)-1)这部分的含义是，源数据等于给定条件满足时，就返回满足条件的数据所在行的行号。这里我们的源数据是从第二行开始的，而我们希望得到的是一个从自然数1开始的一个序列，所以要ROW($A$2:$A$17)-1。（后面我们还会介绍为什么要从1开始。）这部分的结果是{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;13;14;15;16}。大家可以看到，涂黄的这部分对应的在源数据中的第13-16行，也就是著作《水浒传》所在的行。 这里的IF函数我们省略了它的第三个参数。如果完整地书写公式，那么第三个参数我们可以写一个比较大的数字，来确保他们排在满足条件的数据的行号后面。例如，IF($A$2:$A$17=$D$2,ROW($A$2:$A$17)-1,9^9)，它的结果就是{387420489;387420489;387420489;387420489;387420489;387420489;387420489;387420489;387420489;387420489;387420489;387420489;13;14;15;16} 接下来就可以用INDEX函数来返回在B列中所对应的人物名称了。由于INDEX函数需要给它制定在源数据中的位置，也就是它的第二个参数。但是上面符合条件的结果又四个，如何来指定给INDEX函数呢？这里首先要用SMALL函数来依次提取每一个符合条件的位置信息并返回给INDEX函数，即第一个符合条件的，第二个，第三个等等。这里也说明了为什么上面要ROW($A$2:$A$17)-1了。 接下来INDEX($B$2:$B$17,SMALL(IF($A$2:$A$17=$D$2,ROW($A$2:$A$17)-1),ROW(A1)))依次返回符合条件的数值了。 最后，在公式的最外层嵌套IFERROR函数屏蔽错误。  下面我们来看多对多的例子。多对多查询通常分为两种情况：一是要提取出同时符合多个条件的所有记录。二是要提取出多个条件符合其一的所有记录。这里我们只介绍前面一种情况。  多对多的公式和一对多的公式没有大的区别，只是在IF函数判断部分增加了条件。在单元格G2中输入公式“=IFERROR(INDEX($C$2:$C$17,SMALL(IF(($A$2:$A$17=$E$2)*($B$2:$B$17=$F$2),ROW($A$2:$A$17)-1),ROW(A1))),"")”，三键回车并向下拖曳即可。 其余部分的解析都是一样的，只是在($A$2:$A$17=$E$2)*($B$2:$B$17=$F$2)这部分增加了条件。()*()就表示是条件“且”的意思，即条件都要满足。 如果是满足条件之一的情况，我们只需要将“*”换成“+”就可以了。朋友们有兴趣了可以自己动手试一试。 还有一种更复杂的情况。在多条件下统计不重复的数据个数。这个时候使用万金油就要小心一点了，因为很有可能这种情况下万金油是不适用的。具体的情况请参看帖子多条件统计不重复数据个数，万金油还能包打天下吗？ -END- 长按下方二维码关注EXCEL应用之家 面对EXCEL操作问题时不再迷茫无助 我就知道你“在看”  |
|
|
