室内设计培训
平面设计培训
部落窝教育
网站首页 >> Excel教程 >> 文章内容

Excel Lookup函数查找区间案例公式解疑

[日期:2016-01-06]   来源:IT部落窝  作者:IT部落窝   阅读:481[字体: ]
内容提要:本文的Lookup函数查找区间案例来源于卢子老师的微博粉丝提问,老师不仅给出了答案,更是对公式进行详尽的剖析.

  这道题是前几天一粉丝在微博私信今朝的:大于10000按百位数进行四舍五入,大于5000按十位数进行舍入,大于100按个位数进行舍入。如果是你会怎么做呢?注:不考虑小于100这种情况。

  提示:Lookup函数查找区间是非常好用的。
lookup查找区间
 
粗略看了题目,大部分人都会使用IF函数,包括今朝在内。但今朝有一习惯,就是IF函数超过两个就放弃使用,改用别的函数取代。
 
稍加思考,立即可以看到,这里一共有三个区间,100-4999,5000-9999,10000以上,而每个区间都会对应一个值。从小到大,依次是个十百,ROUND函数的第二参数设置为-1,-2,-3就是根据个十百舍入。现在我们可以根据上面的条件模拟一个对应表。
 
按某区间查找对应值可以利用Vlookup的模糊查找,当然用Lookup会更好。
excel lookup 区间
 
为什么要搞这个对应表呢?因为这样看起来会清晰很多,特别是初学者。公式引用后如果觉得对应表在这里会占地方,这时可以对公式进行一些小修改。对引用的区域依次按【F9】键抹黑,修改后按回车。
lookup区间
 
最终公式为:=ROUND(A2,LOOKUP(A2,{100;5000;10000},{-1;-2;-3}))
 
这时就可以把对应表删除,因为这时的公式已经不依靠对应表存在。以前今朝就是经常采用这种变形,不过当你越来越熟练这种形式后,就不能老依靠对应表。这时对应表是在心中,而不在表格中,然后按心中所想设置公式。
 
在这以字符长度论英雄的时代,这个公式实在太长了,必须再精简。将相同部分提取出来,如下
  =ROUND(A2,-LOOKUP(A2,{1;50;100}*100,{1;2;3}))
 
这个是今朝最初的公式,但仅仅是得到这个公式今朝并不满足。玉不琢不成器,公式不琢磨不成经典。Lookup的对应值是1,2,3,好有规律好不好?如果换成排位会怎样?
 
一起来了解下Match,这个函数是获取查找值在区域中的排位,语法为:
  MATCH(查找值,区域,查找模式)
 
查找模式有三种:
  0就是精确查找,数据源不做要求
  1就是模糊查找,数据源升序
  -1也是模糊查找,数据源降序
 
一般情况下,参数为-1可以不用,效率很低下。现在来看前两种,重点放在模糊查找上。
 
  =MATCH(3,{1,3,5},0),用3在区域中精确查找,刚好是第二个,也就是得到排位2。
  =MATCH(2,{1,3,5},0),用2在区间查找对应值,没有对应值就返回错误。
  =MATCH(2,{1,3,5}),用2在区域中模糊查找,因为找不到对应值,就查找小于2的最大值1的位置,也就是得到排位为1。
 
说明:第三参数省略也是模糊查找。
 
  =MATCH(2,{1;50;100}),排位为1
  =MATCH(51,{1;50;100}),排位为2
  =MATCH(100,{1;50;100}),排位为3
 
到这里可以知道,Match可以取代Lookup获取1,2,3,所以最终公式为:=ROUND(A2,-MATCH(A2,{1;50;100}*100))
 
温馨提示:学习阶段要多从各个角度思考问题,争取找到最合适的方法。但工作的时候,要以自己做擅长的方式来思考,只要能达到效果就行,字符长度这些都是浮云。
IT部落窝PS,CDR,213班 分享到: QQ空间 新浪微博 腾讯微博 人人网
photoshop教程
Photoshop教程
平面设计教程
Photoshop教程