今天我们继续. 今天我们要学习的是我个人认为Excel中最重要的一个函数 (至少我的工作是这样). 隆重介绍:
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\il\")
例子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\ 把两个函数合在一起就是:
B50 =
VLOOKUP(B46,$F$47:$H$52,IF(VLOOKUP(\"Name\
结果如下:
LOOKUP查找函数（一） 2010年02月12日 8:25
含义：返回向量或数组中的数值。函数 LOOKUP 有两种语法形式：向量和数组。函数 LOOKUP 的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值；函数 LOOKUP 的数组形式在数组的第一行或第一列查找指定的数值，然后返回数组的最后一行或最后一列中相同位置的数值。
函数 LOOKUP 有两种语法形式：向量和数组。 提示： LOOKUP_vector 的数值必须按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否则，函数 LOOKUP 不能返回正确的结果。文本不区分大小写。
一、语法 1（向量）
向量为只包含一行或一列的区域。函数 LOOKUP 的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值。如果需要指定包含待查找数值的区域，则可以使用函数 LOOKUP 的这种形式。函数 LOOKUP 的另一种形式为自动在第一列或第一行中查找数值。
LOOKUP(lookup_value,lookup_vector,result_vector)
Lookup_value 为函数 LOOKUP 在第一个向量中所要查找的数值。lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用。
Lookup_vector 为只包含一行或一列的区域。lookup_vector 的数值可以为文本、数字或逻辑值。
Result_vector 只包含一行或一列的区域，其大小必须与 lookup_vector 相同。
说明
• 如果函数 LOOKUP 找不到 lookup_value，则查找 lookup_vector 中小于或等于 lookup_value 的最大数值。
• 如果 lookup_value 小于 lookup_vector 中的最小值，函数 LOOKUP 返回错误值 #N/A。
示例
如果您将示例复制到空白工作表中，可能会更易于理解该示例。 A B
1 频率 颜色 2 4.14 幽红 3 4.91 轻橙 4 5.17 艳黄 5 5.77 湖青 6 6.39 醺蓝 公式 说明（结果） =LOOKUP(4.91,A2:A6,B2:B在A列中查找4.91，并返回同一行B6) 列的值(轻橙)
=LOOKUP(5.00,A2:A6,B2:B在A列中查找5.00（最接近的下一个6) 值为4.91），并返回同一行B列的值(轻橙)
=LOOKUP(7.66,A2:A6,B2:B在A列中查找7.66（最接近的下一个6) 值为6.39），并返回同一行B列的值(醺蓝)
在A列中查找0，由于0小于查找向量
=LOOKUP(0,A2:A6,B2:B6)
A2:A7中的最小值，所以返回错误值(#N/A)
LOOKUP查找函数（二）
2010年02月12日 8:29
二、语法 2（数组）
函数 LOOKUP 的数组形式是在数组的第一行或第一列中查找指定数值，然后返回最后一行或最后一列中相同位置处的数值。如果需要查找的数值在数组的第一行或第一列，就可以使用函数 LOOKUP 的这种形式。当需要指定列或行的位置时，可以使用函数 LOOKUP 的其他形式。
LOOKUP(lookup_value,array)
Lookup_value 为函数 LOOKUP 在数组中所要查找的数值。LOOKUP_value 可以为数字、文本、逻辑值或包含数值的名称或引用
• 如果函数 LOOKUP 找不到 lookup_value，则使用数组中小于或等于 lookup_value 的最大数值。
• 如果 lookup_value 小于第一行或第一列（取决于数组的维数）的最小值，函数 LOOKUP 返回错误值 #N/A。
Array 为包含文本、数字或逻辑值的单元格区域，它的值用于与
lookup_value 进行比较。函数 LOOKUP 的数组形式与函数 HLOOKUP 和函数 VLOOKUP 非常相似。不同之处在于函数 HLOOKUP 在第一行查找
lookup_value，函数 VLOOKUP 在第一列查找，而函数 LOOKUP 则按照数组的维数查找。
• 如果数组所包含的区域宽度大，高度小（即列数多于行数），函数 LOOKUP 在第一行查找 lookup_value。
• 如果数组为正方形，或者所包含的区域高度大，宽度小（即行数多于列数），函数 LOOKUP 在第一列查找 lookup_value。
• 函数 HLOOKUP 和函数 VLOOKUP 允许按行或按列索引，而函数 LOOKUP 总是选择行或列的最后一个数值。
示例
如果您将示例复制到空白工作表中，可能会更易于理解该示例。 公式 说明（结果）
在数组的第一行中查找
=LOOKUP(\"C\ “C”，并返回同一列中最后一行
的值(3)
在数组的第一行中查找
=LOOKUP(\"bump\ “bump”，并返回同一行中最后一
列的值(2)
http://hi.baidu.com/cpze/blog/category/office应用
VLOOKUP与HLOOKUP都是查找函数，不过VLOOKUP是按列查找的，HLOOKUP是按行查找的。具体使用方法如下：
一、VLOOKUP函数【按列查找】
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
VLOOKUP（查找值，区域，列序号，逻辑值）
例如：=VLOOKUP($C5,记录单!$C$3:$D$5000,2,FALSE)
1）lookup_value（查找值）：为需要在表格数组第一列中查找的数值。Lookup_value可以为数值或引用。若lookup_value小于table_array第一列中的最小值，VLOOKUP返回错误值#N/A。
2）table_array（区域）：为两列或多列数据。使用对区域或区域名称的引用。table_array第一列中的值是由lookup_value搜索的值。这些值可以是文本、数字或逻辑值。文本不区分大小写。
3）col_index_num（列序号）：为table_array中待返回的匹配值的列序号。col_index_num为1时，返回table_array第一列中的数值；col_index_num为2，返回table_array第二列中的数值，以此类推。如果col_index_num小于1，VLOOKUP返回错误值#VALUE!。大于table_array的列数，VLOOKUP返回错误值#REF!。
4）range_lookup（逻辑值）：指定希望VLOOKUP查找精确的匹配值还是近似匹配值。
如果为TRUE（或为1），则返回精确匹配值或近似匹配值。也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值。table_array第一列中的值必须以升序排序；否则VLOOKUP可能无法返回正确的值。有关详细信息，请参阅排序数据。
如果为FALSE（或为0，或省略），则只寻找返回精确匹配值。在此情况下，table_array第一列的值不需要排序。如果table_array第一列中有两个或多个值与lookup_value匹配，则使用第一个找到的值。如果找不到精确匹配值，则返回错误值#N/A。
二、HLOOKUP函数【按行查找】
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
HLOOKUP（查找值，区域，行序号，逻辑值） 例如：=HLOOKUP($A5,'原始'!$C$3:$X$11,3,FALSE)
1）lookup_value（查找值）：为需要在表格数组第一列中查找的数值。Lookup_value可以为数值或引用。若lookup_value小于table_array第一列中的最小值，HLOOKUP返回错误值#N/A。
2）table_array（区域）：为两列或多列数据。使用对区域或区域名称的引用。table_array第一列中的值是由lookup_value搜索的值。这些值可以是文本、数字或逻辑值。文本不区分大小写。
3）row_index_num（行序号）：为table_array中待返回的匹配值的列序号。row_index_num为1时，返回table_array第一列中的数值；row_index_num为2，返回table_array第二列中的数值，以此类推。如果row_index_num小于1，HLOOKUP返回错误值#VALUE!。大于table_array的列数，HLOOKUP返回错误值#REF!。
4）range_lookup（逻辑值）：指定希望HLOOKUP查找精确的匹配值还是近似匹配值。
如果为TRUE（或为1），则返回精确匹配值或近似匹配值。也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值。table_array第一列中的值必须以升序排序；否则HLOOKUP可能无法返回正确的值。有关详细信息，请参阅排序数据。
如果为FALSE（或为0，或省略），则只寻找返回精确匹配值。在此情况下，table_array第一列的值不需要排序。如果table_array第一列中有两个或多个值与lookup_value匹配，则使用第一个找到的值。如果找不到精确匹配值，则返回错误值#N/A。
http://hi.baidu.com/cpze/blog/category/office应用
因篇幅问题不能全部显示，请点此查看更多更全内容