真伤脑!VLOOKUP、LOOKUP、INDEX+MATCH都解决不了的多行多列查找难题
1月9日
周四
跟着卢子一起成长。...
点击上方蓝字
「Excel不加班」关注
,看下一篇
恭喜下面5位读者获得书籍:淡语嫣然、李婷、睡梦、君子不器、Sunny,加卢子微信chenxilu2019,发送姓名电话地址。
文末点亮在看+留言,随机抽取5名赠送书籍Excel透视表跟卢子一起学 早做完,不加班。
最近一段时间,发现好几个学员这样,做表格,将内容记录成多行多列。或许是为了方便打印,不过这样后期不利于查找,和统计。
这种布局的查找,可以分成2种情况,根据文本查找数值,会比较简单,根据数值查找文本会(文本会,)非常难。
1.根据名称查找编号
可能有的人会用多个VLOOKUP和IFERROR函数嵌套从而找到对应值。不过列数很多,会比较繁琐,而且容易出错。
对于文本查找数值,其实还有一种很简单,的方法。sumif函数除了可以对,一列内容进行求和,同时也能对多列,内容进行求和。这里的编号是数值格式,可以进行求和,因为是唯一值,也等同于查找。
在使用区域的时候,条件区域选择A:g也就是从第1个,名称到最后1个名称的区域,求和区域选择B:h也就是从第1个,编号到最后1个名称的编号,千万别选错。
=SUMIF(A:G,J2,B:H)
不过在使用sumif函数的,时候也有弊端,只能查找数值不能查找,文本。
还有,即使是数值,在下面这种情况,同样会出错。
=SUMIF(A:A,J2,B:H)
每个函数都有本身的,运用场景,并非万能,这点要记住。
=SUMPRODUCT(($A$2:$A$6=J2)*$B$2:$H$6)
2.根据编号查找名称
查找文本,所有求和函数都用不上,文本求和只能得到0。
常用的VLOOKUP、LOOKUP、INDEX+match函数等也派不,上用场,这些都只能针对一列,内容查找。
常规方法都派不上用场,只能用其他特别的方法。
判断编号在第几行和,第几列,确定行列号以后,就可以嵌套,index函数解决。
INDEX函数语法:
=INDEX(区域,第几行,第几列)
比如卢子在第2行,第7列。
=INDEX(A:H,2,7)
编号在第几行,数组公式,需要按Ctrl+Shift+Enter结束。
编号在第几列,数组公式,需要按Ctrl+Shift+Enter结束。=MAX(IF(J2=$A$2:$H$14,ROW($2:$14)))
ROW函数是获取行号,column函数是,获取列号。if函数就是让满足条件,的值返回行、列号,其他的都返回逻辑值,max函数获取最大的,数字,也就是行、列号。=MAX(IF(J2=$A$2:$H$14,COLUMN(A:H)))
这样就可以获得编号,结果跟原来一样,证明公式没问题。
=INDEX(A:H,MAX(IF(J2=$A$2:$H$14,ROW($2:$14))),MAX(IF(J2=$A$2:$H$14,COLUMN(A:H))))
编号前面1列就是名称,只需用COLUMN-1,就可以解决。记得按Ctrl+Shift+Enter结束。
=INDEX(A:H,MAX(IF(J2=$A$2:$H$14,ROW($2:$14))),MAX(IF(J2=$A$2:$H$14,COLUMN(A:H)-1)))
最后,尽量不要使用这种布局,名称、编号各一列就好,这样后期会方便很多。
源文件:
https://pan.baidu.com/s/172mUjMlZPrXj942X0XD2OA
VIP 888 元,一次报名,所有课程,终生免费学,提供一年在线答疑服务。报名后加卢子微信chenxilu2019,发送报名截图邀请进群。
推荐:不可思议,MAX+IF组合居然比VLOOKUP查找更牛逼
上篇:2020年,我连续输入错了7次,你出错了多少次?
作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)
请把「Excel不加班」推荐给你的朋友
无需打赏,请点在看↓↓↓
关注 Excel不加班
微信扫一扫关注公众号