Excel教程下载和软件下载中心,Microsoft技术社区联盟成员,全球极具影响力的Excel门户,Office技术培训社区
广东快乐十分实时开奖

一对多查询,少不了这两刷子

2018-06-23  作者:ExcelHome  阅读:次
我们对数据进行查询时,经?;崾褂肰LOOKUP函数。
但有时,我们会碰到这样的问题,提取符合条件的结果是多个,而不是一个,这时候VLOOKUP就犯难了。
举个例子,如下图,左侧A1:C10是一份学员名单表,现在需要根据F1单元格的“EH图班”这个指定的条件,在F2:F10单元格区域中,提取该班级全部学员名单。

今天说一个函数查询方面的万金油套路:Index+Small。
F2单元格输入以下数组公式,按住Ctrl+Shift键不放,再按回车键,然后向下填充:
=INDEX(B:B,SMALL(IF(A$1:A$10=F$1,ROW($1:$10),4^8),ROW(A1))),"")
这个公式看起来可就比上面那个VLOOKUP的解法苗条养眼多了,坦白的说,很搭俺星光十年后的匪号——小清新。

1、公式讲解
IF(A$1:A$10=F$1,ROW($1:$10),4^8)
这部分,先判断A1:A10的值是否等于F1,如果相等,则返回A列班级相对应的行号,否则返回4^8,也就是65536,一般情况下,工作表到这个位置就没有数据了。
结果得到一个内存数组:
{65536;2;3;65536;65536;65536;65536;8;65536;10}

SMALL函数对IF函数的结果进行取数,随着公式的向下填充,依次提取第1、2、3……n个最小值,由此依次得到符合班级条件的行号。
随后使用INDEX函数,以SMALL函数返回的行号作为索引值,在B列中提取出对应的姓名结果。
当SMALL函数所得到的结果为65536时,意味着符合条件的行号已经被取之殆尽了,此时INDEX函数也随之返回B65536单元格的引用,结果是一个无意义的0,为了避免这个问题,可以在公式后面加上一个小尾巴&""
利用&””的方法,很巧妙的规避了无意义0值的出现,只是当查找结果为数值或日期时,这个方法会把数值转变为文本值,并不利于数据的准确呈现以及再次统计分析。

2、练手题
最后留下一道练手题,如下图,根据A1:C10区域的数据,将E列相关班级的姓名,填充到F2:I5区域。



转载请注明来源"ExcelHome"并保留原文链接。
固定链接://www.x5was.cn/lesson/article/excel/1906.html
分享到:
Copyright 1999 - 2017 Excel Home.All Rights Reserved.
本站特聘法律顾问:徐怀玉律师 李志群律师   沪ICP备11019229号

广东快乐十分实时开奖 www.x5was.cn 沪公网安备 31011702000001号

征信        广东快乐十分实时开奖
  • 20省份平均工资出炉!你为啥老是“被平均”?官方解读来啦 2018-12-08
  • 新疆:喀纳斯湖疑似出现“不明水生物” 2018-09-18
  • 宜春深燃公司首个分布式能源项目正式落地运行(组图) 2018-09-18
  • 正在进入故宫新官网,请稍等 2018-07-26
  • 西部网(陕西新闻网)www.cnwest.com 2018-07-26
  • 《伊阿索密码》曝动作特辑 惊悚悬疑燃爆未来 2018-07-25
  • 【幸福是奋斗出来的】于立洪:小鹌鹑“啄”开致富门 2018-07-25
  • 农业农村部发力打造休闲农业和乡村旅游精品 2018-07-24
  • 河北省对中央环保督察“回头看”问题整改进行部署 强化标本兼治 防止整改问题反弹 2018-07-24
  • 端午天气好 就是有些热 2018-07-23
  • 人人车春季消费报告用户篇 全景呈现消费行为习惯 2018-07-23
  • 中国核电逆袭之路相关新闻 2018-07-23
  • 老年人“犯困” 内情并不那么简单 2018-07-22
  • 与世俱进。已变相为套路贷的广告站取消得好! 2018-07-22
  • 为实现普遍安全贡献中国智慧(钟声) 2018-07-22
  • 290| 716| 61| 472| 580| 451| 222| 116| 438| 923|