|
2014年的某一天卢子（@Excel之恋）在微博发了一个VLOOKUP函数的运用，引来33万的阅读量，860次的转发，可见大家对VLOOKUP函数的爱有多深。后面会重点介绍这个函数的用法，这里只做简单的说明。 既然这里提到了2014年，就顺便以IT部落窝的人员作为数据源，作为查找依据，曾经这些人也为IT部落窝做出了很多贡献。如图5-12所示，根据员工姓名，查找职务。
数据查找，首推VLOOKUP函数。 =VLOOKUP(E2,B:C,2,0) VLOOKUP的作用就是：查找某个值，在区域中的对应值，返回区域中第几列，正常情况下都是精确查找，也就是最后一个参数设置为0。 正常顺序查找，VLOOKUP函数很好用，如果要逆序查找就相对比较麻烦，这时他的兄弟LOOKUP函数就派上用场。LOOKUP函数不区分正常顺序跟逆序，如图5-13所示，根据员工姓名，查找员工号，用在这里再合适不过。
LOOKUP函数有一个经典查找公式，这里先记住，至于怎么得到的，暂时可以不用管。 =LOOKUP(1,0/((条件1)*(条件2)*…*(条件n)),返回区域) 根据这条公式，我们可以得到 =LOOKUP(1,0/(E2=B2:B10),A2:A10) 如果公式要下拉的话，记得区域加绝对引用。如果还不会加的朋友可以去看《4.4 相对引用、绝对引用、混合引用》。 既然提到论坛的朋友，怎么可以将真正的朋友忘记，下面来看OFFSET函数之卢子访友。 卢子有三个朋友：无言、安安、胖纸，朋友间就得互相来往。每个朋友的距离都挺远，卢子住在潮州，无言离卢子最近，在汕头那边，安安次之在深圳，胖纸最远在东莞。同属广东，坐车半天内都能到。如图5-14所示。
假如OFFSET函数就是卢子的车，要如何去到每个朋友哪里？ 有人说过OFFSET函数会轻功，那速度当然不比车子慢。先来看看语法： =OFFSET(起点,偏移行,偏移列,行高,列宽) 注：行高、列宽为可选参数。 卢子要去无言那边，只需向右坐2站就到。 =OFFSET(A1,0,2) 如果要去安安那边，只需向下坐5站才能到。 =OFFSET(A1,5,0) 去胖纸那边就稍微麻烦点，要向下坐6站，再向右坐1站才能到。 =OFFSET(A1,6,1) 看到这里大概知道OFFSET函数是干嘛用的，如果偏移的行数为正数就是向下偏移，偏移的列数为正数就是向右偏移。相反，如果偏移的行数为负数就是向上偏移，偏移的列数为负数就是向左偏移。 假如卢子现在在胖纸家里，想要回到自己的家。就得向上坐6站，就是-6，向左坐1站，也就是-1。 =OFFSET(A1,-6,-1) 既然知道怎么去，就得知道怎么回，卢子还不至于路痴到忘记回来的路。 安安跟胖纸离得很近，卢子想知道她们两家合并的范围有多大，也就是深圳跟东莞的范围。卢子就得先到安安这里，然后将这里的行高设置为2，列宽设置为2，这样就知道这两地的范围。 =OFFSET(A1,5,0,2,2) 但这个只是划分个范围，没有统计，统计可以用COUNTA，得到这两地的范围为4。 =COUNTA(OFFSET(A1,5,0,2,2)) 访友总不能两手空空，至少买点水果表示下吧。如图5-15所示，水果种类很多，有苹果、香蕉等等，每一种的单价都是不同的。
现在要获取区域中第3行，第2列的对应值，就可以用，得到3.4。 =INDEX(A1:C10,3,2) 想知道A列第3行的对应水果，就可以用，得到香蕉。 =INDEX(A1:A10,3) 第1行第2列的对应值，就可以用，得到单价。 =INDEX(A1:C1,2) 摸清了水果的情况，才好挑选。不过对于卢子而言，很多时候都只是知道大概要买什么而已，没有实际概念。假如现在要获取最后5种水果的情况，怎么办？ 一般情况下记录都是随时增加的，你不可能去数下哪几条是最后的，数到的数据即使现在可以，过几天就行不通了。最后一条非空记录的行号，可以用COUNTA函数，这个就是统计非空单元格的个数。 =COUNTA(A:A) 现在知道非空一共有10条，那最后5条记录就是，10,9,8,7,6。一般情况下连续数字首先考虑到的就是ROW函数。 =COUNTA(A:A)-ROW(A1) 这样就可以得到9-6这4条记录，但缺少第1条记录，也就是说在第1行的时候只能减去0。 =COUNTA(A:A)-ROW(A1)+1 现在已经知道行号，只需嵌套个INDEX函数就可以。如图5-16所示。
但有的人觉得还是按原来顺序排序好，记录显示是6-10。也就是第1行减去4，第2行减去3……这时只需做小小的变形就行。 =COUNTA(A:A)-5+ROW(A1) 现在就得到6-10，再重新嵌套INDEX函数。 =INDEX(A:A,COUNTA(A:A)-5+ROW(A1)) 如果想要改成引用A:C这种区域的形式，可以用： =INDEX($A:$C,COUNTA(A:A)-5+ROW(A1),COLUMN(A1)) 公众号ID:exceljiaocheng |
|
|
