要求:根据职级和业绩查询职级的升降(一级一级升降,不跳级别)。
LOOKUP查询里,数值作为查询条件的,LOOKUP如果找不到精确的数值,就会返回比该数值小的最后一条数据。如果找到,就返回数值所对应的结果。
如下图中,LOOKUP不能精确查找到4,所以返回比4小的3对应的结果2。
公式:=LOOKUP(R23,O23:O26,P23:P26)
此法的前提是:红色方框中的0、3、6、9数值必须升序排列。
若增加一个条件,LOOKUP的用法变为:
语法为:=LOOKUP(1,0/((条件1)*(条件2)*(条件3)...),结果)
=LOOKUP(1,0/((N23:N30=R23)*(O23:O30<=S23)),P23:P30)
若不能理解公式,按语法套用即可。
公式:=MATCH(J12,H12:H15,0)
返回查找条件在查找的单元格区域中的位置,参数0为精确查找。
这里高级顾问在蓝色的单元格区域的第2个位置,所以返回2。
OFFSET在这里用作单元格区域的偏移。
当一个单元格偏移的时候,返回单元格里的数据。
如下图,从U1单元格向下移动2行,向右移动0列,偏移到U3单元格,并返回其值'高级顾问'。
当单元格区域便宜的时候,不能返回单元格区域的数据,只返回单元格区域。
又如,下图,Y1:AA1单元格区域,向下移动2行,向右移动0列,返回Y3:AA3单元格,并用SUM函数对该单元格进行求和。
初始的数据源是不规范的。不规范的数据往往造成函数公式更加复杂,难以解决且效率低下。
因此,一个规范的数据源是函数查询的最佳助力。数据源的更改如下:
分析:职级的变化有3个分区,降--不变--升。同时要注意数值区间的变化。如'见习顾问'中8000的业绩还达不到升职的条件,在制作数据的时候,可以将8000改为8000.0001这样比8000大一点点,又足够小不影响实际业绩的数值。因为8000职级不变。这样制作的原因是LOOKUP函数的查找原理,在上面已有讲述。
上面的区间划分为,'见习顾问'为例,其他同理:
0=<'降职'<3000 ; 3000=<'不变'<=8000 ; 8000<'升职'
=LOOKUP(C2,OFFSET($H$9:$J$9,MATCH(B2,$G$10:$G$13,0),0),$H$9:$J$9)
OFFSET($H$9:$J$9,MATCH(B2,$G$10:$G$13,0),0)
根据职级(MATCH函数确定职级位置)返回红色方框的单元格区域(一行)
如''见习顾问'例子,则返回H10:J10单元格区域,然后LOOKUP公式变为相当于这样的公式:
=LOOKUP(C2,H10:J10,$H$9:$J$9),最后得出查询结果。
以上,几个函数的用法都应用广泛,也难全面讲述其基础用法,弱基础的朋友可在网络上了解更多。
Excel文件下载:
链接: https://pan.baidu.com/s/1-cAV0JW-jlYXLCk1uVM2fw 密码: ea74
联系客服