感受数据的魅力,享受学会之后的成就感
你我共勉
有深度的文章,希望遇见能够读懂文章的你
谈到查询,我们很容易想到VLOOKUP
但VLOOKUP也有一个致命的缺陷,就是“查找值必须在首列”
意思是:我们可以根据姓名查询工资,因为我们选择区域的时候可以选择F:G列,而姓名就在F:G的首列
但我们不能通过姓名查询工号,因为E:F,首列是工号,VLOOKUP不能完成这个查询
当然,我们有很多的变通办法:
在作业发出后的24小时内提交的12份作业样本中,我进行了统计
数据样本为12份,但由于可一题多解(多选),故相加之和超过100%
学员共提交了9种不同的答案:其中采用次数大于1次的有5种,名单如下:
其中仅采用1次的有4种,名单如下:
(一)选用LOOKUP进行查询的有6个,占50%
公式:=IFERROR(LOOKUP(1,0/($F$53:$F$62=B52),$E$53:$E$62),'')
关于LOOKUP的原理,参考:号称最强查找函数,解释清楚LOOKUP的二分法要多久?
点评:由于数组运算效率较低,并且也不太容易理解,所以,虽然这种方案采用率高,但不推荐在这里使用
(二)选用INDEX+MATCH进行查询的有5个,占41.67%
公式:=IFERROR(INDEX($E$53:$E$62,MATCH(J53,$F$53:$F$62,0)),'')
点评:由于INDEX+MATCH的解决方案,运算效率较高,扩展性也比较强,故推荐
扩展:MATCH支持数据升序、降序查询,INDEX可以引用的区域有行、列两个方向,所以可以嵌套2个MATCH,适用度超级广……
(三)选用VLOOKUP+IF进行查询的有5个,占41.67%
公式:=IFERROR(VLOOKUP(B52,IF({1,0},$F$52:F$62,E$52:E$62),2,0),'')
点评:IF{1,0}段相对难理解一些,扩展性也不如INDEX+MATCH,故不推荐
从传播性上考虑:“VLOOKUP原来可以这样用”比“INDEX+MATCH查找函数万能组合”更能吸引眼球,并且VLOOKUP的用户基数大一些,所以,这种过度“炫技”的方法才会有这么高的采用率
(四)选用OFFSET+MATCH进行查询的有4个,占33.33%
公式:=IFERROR(OFFSET($E$52,MATCH($J53,$F$53:$F$62,0),0),'')
点评:OFFSET函数在大部分使用能跟INDEX调换使用,所以出现这种解法并不奇怪。但OFFSET不可替代的地方在于,OFFSET是实现各种动态效果不可缺少的函数。比如:动态图表、多级动态下拉菜单
(五)选用SUMIF进行查询的有2个,占16.67%
公式:=SUMIF($F$53:$F$62,J53,$E$53:$E$62)
点评:巧妙的利用了工号是数值,并且姓名唯一的特性,避开了逆序查询的命题,可谓是巧妙,函数以及规律应用独到
(六)选用SUMPRODUCT进行查询的有1个,占8.33%
公式:=SUMPRODUCT(($F$53:$F$62=J53)*$E$53:$E$62)
点评:同SUMIF,利用工号是数值的特性,进行条件求和
(七)选用VLOOKUP+CHOOSE进行查询的有1个,占8.33%
公式:=IFERROR(VLOOKUP(J53,CHOOSE({1,2},$F$53:$F$62,$E$53:$E$62),2,0),'')
点评:原理同VLOOKUP+IF
(八)选用INDIRECT+MATCH进行查询的有1个,占8.33%
公式:=INDIRECT('e'&MATCH(B52,F:F,))
点评:原理同INDEX/OFFSET+MATCH
(九)选用MMULT+TRANSPOSE进行查询的有1个,占8.33%
公式:=MMULT(TRANSPOSE(N($F$53:$F$62=$B52)),$E$53:$E$62)
点评:原理同按条件求和
整体来说,解决方案是4种思路:
1.使用LOOKUP进行条件查询
2.通过MATCH定位,利用INDEX/OFFSET/INDIRECT返回单元格信息
3.VLOOKUP+IF/CHOOSE内存数组构建
4.利用工号是数值的特点使用SUMIF/SUMPRODUCT/MMULT进行条件求和
我昨天和一位读者讨论逆序查询的方法,在对于INDEX+MATCH和VLOOKUP+IF的取舍上应该有怎样的价值导向,结果改完学员的作业,发现都是白操心了。
因为学员并没有必要在VI和IM中二选一,而是可以更加包容并蓄的汲取知识的养分。
并且能够利用数据里的一些特征和规律,更加灵活的运用好函数(比如这题中,利用条件求和函数的解题思路)
是我们低估了学员的潜力……
要跟这些优秀的同学一起学习吗?
学习氛围和学习环境
此时最佳
就等你加入啦~
联系客服