要求:根据职级和业绩查询职级的升降(一级一级升降,不跳级别)。 简单讲解此题目要用到的函数用法以及做法1、LOOKUP函数LOOKUP查询里,数值作为查询条件的,LOOKUP如果找不到精确的数值,就会返回比该数值小的最后一条数据。如果找到,就返回数值所对应的结果。 如下图中,LOOKUP不能精确查找到4,所以返回比4小的3对应的结果2。 公式:=LOOKUP(R23,O23:O26,P23:P26)
若增加一个条件,LOOKUP的用法变为:
=LOOKUP(1,0/((N23:N30=R23)*(O23:O30<=S23)),P23:P30) 若不能理解公式,按语法套用即可。 2、MATCH函数
返回查找条件在查找的单元格区域中的位置,参数0为精确查找。 这里高级顾问在蓝色的单元格区域的第2个位置,所以返回2。 3、OFFSET函数OFFSET在这里用作单元格区域的偏移。 当一个单元格偏移的时候,返回单元格里的数据。 如下图,从U1单元格向下移动2行,向右移动0列,偏移到U3单元格,并返回其值'高级顾问'。 当单元格区域便宜的时候,不能返回单元格区域的数据,只返回单元格区域。 又如,下图,Y1:AA1单元格区域,向下移动2行,向右移动0列,返回Y3:AA3单元格,并用SUM函数对该单元格进行求和。 4、修改数据源初始的数据源是不规范的。不规范的数据往往造成函数公式更加复杂,难以解决且效率低下。 因此,一个规范的数据源是函数查询的最佳助力。数据源的更改如下: 分析:职级的变化有3个分区,降--不变--升。同时要注意数值区间的变化。如'见习顾问'中8000的业绩还达不到升职的条件,在制作数据的时候,可以将8000改为8000.0001这样比8000大一点点,又足够小不影响实际业绩的数值。因为8000职级不变。这样制作的原因是LOOKUP函数的查找原理,在上面已有讲述。 上面的区间划分为,'见习顾问'为例,其他同理: 0=<'降职'<3000 ; 3000=<'不变'<=8000 ; 8000<'升职' 5、写上最后公式:=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 |
|