|
感受数据的魅力，享受学会之后的成就感 你我共勉 有深度的文章，希望遇见能够读懂文章的你 谈到查询，我们很容易想到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进行条件查询 我昨天和一位读者讨论逆序查询的方法，在对于INDEX+MATCH和VLOOKUP+IF的取舍上应该有怎样的价值导向，结果改完学员的作业，发现都是白操心了。 因为学员并没有必要在VI和IM中二选一，而是可以更加包容并蓄的汲取知识的养分。 并且能够利用数据里的一些特征和规律，更加灵活的运用好函数（比如这题中，利用条件求和函数的解题思路） 是我们低估了学员的潜力…… 要跟这些优秀的同学一起学习吗？ 学习氛围和学习环境 此时最佳 就等你加入啦~ |
|
|
