手机赚钱资讯-手机赚钱,赚零花钱、每天领个红包赚零花钱。

 找回密码
 立即注册

手机号码,快捷登录



[Excel函数与公式] 学Excel函数公式,怎能不会这个组合套路?

[复制链接]
小美 发表于 2019-5-14 15:34:37 | 显示全部楼层 |阅读模式
  我们对数据进行查询时,经常会使用VLOOKUP函数。但有时我们提取符合条件的结果是多个,而不是一个,这时候VLOOKUP就犯难了。举个例子
  如下图,左侧A1:C10是一份学员名单表,现在需要根据F1单元格的“EH图班”这个指定的条件,在F2:F10单元格区域中,提取该班级全部学员名单。
  
  F1的值是“EH图表班”,需要在F2:F10单元格区域得到图表班相关成员的人名。
  接下来就分享一个函数查询方面的万金油套路:
  INDEX+SMALL+IF
  F2单元格输入以下数组公式,按住Ctrl+Shift键不放,再按回车键,然后向下填充:
  =IFERROR(INDEX(B:B,SMALL(IF(A$1:A$10=F$1,ROW($1:$10)),ROW(A1))),"")1. 公式讲解
  IF(A$1:A$10=F$1,ROW($1:$10))
  这部分,先判断A1:A10的值是否等于F1,如果相等,则返回A列班级相对应的行号,否则返回FALSE,结果得到一个内存数组:
  {FALSE;2;3;FALSE;FALSE;FALSE;FALSE;8;FALSE;10}
  再来看这部分:
  SMALL(IF(A$1:A$10=F$1,ROW($1:$10)),ROW(A1))
  SMALL函数对IF函数的结果进行取数,随着公式的向下填充,依次提取第1、2、3……n个最小值,由此依次得到符合班级条件的行号。
  随后使用INDEX函数,以SMALL函数返回的行号作为索引值,在B列中提取出对应的姓名结果。
  当SMALL函数所得到的结果为错误值#NUM时,意味着符合条件的行号已经被取之殆尽了,此时INDEX函数也随之返回一个错误值,为了避免公式返回一个错误值,最后使用IFERROR函数进行规避,使之返回一个空文本""。2. 其它说明
  很多时候,一些朋友喜欢把INDEX+SMALL+IF的套路写成:
  =INDEX(B:B,SMALL(IF(A$1:A$10=F$1,ROW($1:$10),4^8),ROW(A1)))&""
  或
  =INDEX(B:B,SMALL((A$1:A$10<>F$1)/1%+ROW($1:$10),ROW(A1)))&""
  这两个套路,通过引值真空单元格搭配&””的方法,很巧妙的规避了错误值的出现,而且公式的长度得到了精简,是IFERROR函数未出现前处理错误值的常用技巧。
  只是当公式的查找结果为数值或者日期时,这个方法会把数值变成文本值,并不利于数据的准确呈现以及再次统计分析。
  比如一个简单的SUM求和,对于此类文本数据的统计都是麻烦的,原因是大部分统计函数都忽略文本值,不予计算。
  所以通常还是建议大家使用IFERROR函数来处理错误值。3. 练手题
  最后留下一道练手题,如下图,根据A1:C10区域的数据,将E列相关班级的姓名,填充到F2:I5区域。
  
  示例文件下载:
  http://pan.baidu.com/s/1nuDoYFZ
  细分:
  函数
  转载请注明来源"ExcelHome"并保留原文链接。
  固定链接:http://www.excelhome.net/lesson/article/excel/1914.html
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表