VLookup & HLookup

http://hi.baidu.com/cunqiu/blog/item/75bacbf43f67e263dcc47405.html

VLookup & HLookup

大家看到"Lookup"就应该知道这个函数何查找有关系. 没错. 这个函数就是让我们找东西的.

VLookup中的 "V" 是Vertical 的意思, 也就是说这个函数是竖着找; H是Horizontal, 就是横着找. 我们下面就只讲VLookup函数, 大家学会了自然就知道HLookup怎么用了. 而且如果做数据分析, 我们大部分都是用VLookup.

好, 我们先来看看VLookup函数有哪些参数:

= Vlookup (参数1, 参数2, 参数3, 参数4)

例如: B2 = Vlookup(A2, E:F, 2, False)

参数1: (要找什么?) 你要查找的单元格或者内容, 比如下面的A2单元格

参数2: (到哪里找?) 你要到哪里找出你想要的内容, 比如上面的函数我们是希望从E列中找到A2, 然后从F列中把相应的Offer数取出来.

参数3: (列的间隔数) 你"要找的数据"和"希望得出的数据"相距的列数, 比如上面的例子, 参数3是"2", 因为E和F为间隔的两列, 所以是2. 如果是E列到G列, 那么就是3了.

参数4: 有TRUE和FALSE两个选择, FALSE是精确匹配, 只有完全相同的内容他才找出来; TRUE则可以找相似的内容. (建议大家不要用TRUE, 因为不知道他到底能找到什么)

所以上面的函数(Vlookup(A2, E:F, 2, False))的意思就是: 在B2单元格中, 我要把A2单元格的内容拿出来, 到E列里面找有没有和A2中一样的内容, 如果有, 则把E列的这个单元格(E2)所对应的F列(F2) (因为参数3是2)取出来放在单元格B2中. 我们看看结果如下:

这么说估计大家还是不是很理解到底这个函数干吗用. 好, 下面我们开始举例子:

例子1: 公司进行了一次考试, A14-A17是4个不及格的Agent的名单, 老板叫你去把这四人的Team查一查.

这个时候呢, 你当然可以一个个看, 因为才4个人, 但是当人多的时候就要用到函数了. 所以你要先去找HR或者是Reporting Team要一份Agent和Team的对应表. 假设你从HR那里拿到一份表单如E13:G22, 是所有员工的信息.

好, 这样我们就可以开始在B列找出这四个Agent相应的Team了.

以A14(Jack)为例:

- 我们要找什么? 找"Jack", 就是找A14单元格 (参数1 = A14)
- 我们要到哪里找? 我们要从E13:E22里面找A14的内容(Jack), 然后从G13:G22中取出相应行的Team名字 (参数2 = E13:G22)
- 参数2里面的"要找的数据"和"希望得出的数据"相距多少列? E/F/G, 三列 (参数3 = 3)
- 我们要精确匹配 (参数4 = FALSE)

所以B14可以写成: = VLOOKUP(A14,E13:G21,2,FALSE) 大家可以看到结果如下:

好, 接下来我们只要把B14的函数直接往下拖就可以得到B15:B17的结果了. 如下:

大家注意到没有? 前三个Agent所对应的Team都找到了, 为什么第四个Agent - Dibort为什么出错了呢? 我们明明看到E15单元格里面是Dibort啊???

大家注意看上面的图, 注意一下C14 - C17几个函数的区别! 大家看到吗? 这四个函数的第二个参数随着我们刚才拖动函数而变化了. 从B14单元格中的"E13:G21"变成了B17中的"E16:G24". (这个是Excel的自有功能, 函数中的参数会随着我们的拖动或者拷贝而自动变化), 当然参数1也是自动变化的.

其中参数1的变化是我们需要的, 因为我们要找的是A14-A17的内容; 但是参数2我们并不希望他变化, 因为我们要的数据是应该固定在E13:G21不变.

那么我们如何固定参数2呢? 在Excel中, 如果要让参数固定, 则要用到"$"这个符号. 俗话说: 有钱能使鬼推磨, 我们就用"美金"让参数固定.

现在我们把B14改一下: B14 = VLOOKUP(A14,$E$13:$G$21,3,FALSE)

大家看到了. 我们在需要固定的地方加上"$", 当我们拖动函数时这些参数就不会再变了. 对于这个例子, 其实我们可以看到其实只有行号在变, 列号(E,F)其实没有变化, 所以这里我们写成 " E$13:G$21 " 也是可以的.

*** 这里我们讲个小技巧, 其实当我们加"$"挺麻烦的, 因为特殊字符我们都不常用. 这里我们可以点击函数编辑框, 光标移到相应的位置, 然后按"F4", 就可以直接在行和列上加上$了, 再按"F4"就会去掉列的$, 只保留行的$; 再按"F4", 则变成只有列有$; 再按"F4", 所有的$都消失了. (大家自己试一试就明白了)

例子2: 如下表, A列是所有Agent的名单, B列需要找出每个人今年的Bonus. 老板今天和你说: 今年经济危机了, 只有4个人有Bonus(就是E30:E33这4个人), 让你去把B列填清楚. 对于有Bonus的Agent则显示"裁员广进"+Bonus的数目, 没有Bonus的显示"薪饷四成".

那么我们一步步来. 先用Vlookup函数找找看.

B30 = VLOOKUP(A30,$E$29:$F$33,2,FALSE)

结果如图, 我们看到, 其中4个Agent在E列找到了, 所以显示出了相应的Bonus, 其余的Agent因为没有找到所以得出了错误值"#N/A".

但是我们题目的要求不是这么简单, 还要显示出相应的文字, 因此我们要用到 ISNA 函数来判断我们得来的值. 然后用 IF 函数显示相应的文字.

B30 = IF(ISNA(VLOOKUP(A30,$E$29:$F$33,2,FALSE)),"薪饷四成","裁员广进 "&VLOOKUP(A30,$E$29:$F$33,2,FALSE))

对于Vlookup函数的应用, 大家特别要注意的是要加上$以讲查询范围固定, 经常的错误就是这个造成的. (当然, 有的时候反而不需要固定, 要看具体需要而定).

例子3: 其中"F15:G21"是每个Agent的英文成绩; "I15:J21"是法语成绩, 请在B列中显示: 如果总成绩高于100, 则显示Pass, 低于100显示Fail

要计算英文和法语的成绩总和, 则需要分别找到英文的成绩和法语的成绩. 所以函数就是:

查找英文成绩: VLOOKUP(A16,$F$15:$G$21,2,FALSE)
查找法语成绩: VLOOKUP(A16,$I$15:$J$21,2,FALSE)

后面就简单了吧. 直接用IF语句来判断是否这两个成绩的和是否大于100. 因此整个函数就是:

B16 = IF(VLOOKUP(A16,$F$15:$G$21,2,FALSE)+VLOOKUP(A16,$I$15:$J$21,2,FALSE)>=100,"Pass","Fail")

例子4: 让我们来练习一下HLOOKUP函数好了. 如下面的两个表, 请用HLOOKUP函数查找每个人的号码.

这个就不多说了. 按照Vlookup的用法直接套用就可以了.

B31 = HLOOKUP(A31,$E$30:$K$34,5,FALSE)

为什么第三个参数是5? 自己想了~~~ 呵呵

例子5: 下面的B46和B48分别可以选择名字和语言, 请根据这两项的选择自动在B50中显示相应的成绩(成绩在右边的表中)

好, 这题我们要分两步走, 第一步, 我们可以假设没有B48的条件, 而是单纯查找法语成绩, 这样就简单了. 函数可以写成:

B50 = VLOOKUP(B46,$F$47:$H$52,3,FALSE)

很简单, 第一步就完成了. 但是怎么根据B48来决定查找呢? 就是要通过B48来决定上面函数的第三个参数 - 3???? 看到上面的表, 大家可以知道要查找英文则参数3等于2; 要查找法语则参数3等于3. 所以上面的参数3可以替换成:

= IF(VLOOKUP("Name",$F$46:$H$46,2,FALSE)=B48,2,3)

把两个函数合在一起就是:

B50 = VLOOKUP(B46,$F$47:$H$52,IF(VLOOKUP("Name",$F$46:$H$46,2,FALSE)=B48,2,3),FALSE)

结果如下: