相关教程：
经典Excel学习视频及教程推荐： 最新推出 →
Excel优秀工具推荐：
小工具推荐：图解Excel中vlookup函数的使用方法 - Office办公助手
专业的Office办公软件学习和Office办公软件教程网站！
图解Excel中vlookup函数的使用方法
发布时间：02-12　来源：Office办公助手()
日常工作中，Vlookup函数是一个非常有用的Excel函数，它的作用是：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。下面以Excel2007中应用为例，一步一步图解如何使用Vlookup函数。
例如有两个表，分别是：A表
例要求在B表的B26到B31单元格中自动填入A表中A列与B表A列相同值(完全相同，不分大小写)所对应的C列的值
1、先选中B表B26，然后选择公式：
弹出函数编辑框：
2、把这4个自定义项全填上就OK，上往下第一个为：
可用鼠标直接选中B表A26，这是返回B26时赖以遵循的搜索项，编辑框中会自动输入语法。
3、第二个自定义项为：
直接鼠标选择A表中整个A:C列，这是搜索范围。如果要圈定一个特定范围，建议用$限定，以防之后复制公式时出错。
4、第三个为：
本例中要返回的值位于上面圈定的搜索范围中第3列，则键入数字3即可。
5、最后一个：
通常都要求精确匹配，所以应填FALSE，也可直接键入数字0，意义是一样的。
6、确定后，可以看到B表B26中有返回值：
7、最后，向下复制公式即可。大功告成!检查一下看，是不是很完美?
1、在第2项步骤中由于是最终是要返回一个列表，所以也可以直接选中A26:A31甚至整个A列(如果没有其他干扰项的话)。
2、如果是excel2003环境的话，只有步骤1有差别，就是选&插入&-&公式&，在&查找与引用&类别里找到vlookup函数，下面就一样了!
上一篇：没有了 下一篇：电脑教程子分类Excel的vlookup等函数的使用例子及数组公式 &
Excel的vlookup等函数的使用例子及数组公式
Excel的vlookup等函数的使用例子及数组公式
关键字：vlookup,index,match,数组公式,sumproduct
最近做了几道Excel的练习题，做完之后感觉学到一些东西，写下来做个分享。
假设有这么一个表：
只能用公式，不能添加辅助步骤。
1.男生的人数
2.男生数学成绩总和
3.男生数学成绩平均值
4.男生数学成绩最高分
5.男生数学成绩最低分
6.男生中有一门不及格的人数
7.男生中有两门不及格的人数
8.有一列有若干个姓名，求自动找出对应的总分
9.第8题中，同样从姓名求出对应的学号
前三题，分别用到 公式函数 COUNTIF，SUMIF，AVERAGEIF，简单的公式运用而已，Excle自带的提示就可以学会，不讲。
1. =COUNTIF(C2:C21,"男")
2. =SUMIF(C2:C21,"男",E2:E21)
3. =AVERAGEIF(C2:C21,"男",E2:E21)
对了，表中的总分和排名分别用到函数SUM和RANK，
其中排名的公式内容为“=RANK(G2,$G$2:$G$21)”，注意单位格的绝对引用就行。
第4、5题涉及到数组公式的运用。
第4题，“男生数学成绩最高分”，我的答案是
｛=MAX((C2:C21="男")*E2:E21)｝
如果光“=MAX((C2:C21="男")*E2:E21)”回车，是得不到答案的，需要“Ctrl+Shift+Enter”组合键，这样会自动在公式外边加上花括号，表示这是数组公式。（手动输入花括号是不行的）
讲解一下我这个答案，实际上我这个答案并不严谨，后面会讲。
(C2:C21="男") 这一部分是 if(C2:C21="男",True,False) 的简写，因为对于判断式的返回值只有"True"与"False"两种状态，分别对应着1和0，并且可以参与算术运算，这样就产生了一些方便。
前面部分判断为“男”产生的值就是1，其它的就是0；然后再去乘以E列（数学）对应的分数。得到的结果就是一个20个数的数组，这20个数是什么结果呢？
“男”的数值就是1*对应数学分数=对应数学分数，
“女”的数值就是0*对应数学分数=0。
这样的数组中用max函数取出来的值就是“男”中数学分数的最大值。
第5题的“男生数学成绩最低分”答案是否把第4题的max函数改用min就可以了呢？其实不行，直接这样改之后的结果是“0”，因为数组中对应“女”的都变成0了，用min取出来就是0，不是我们要的答案。所以还是要用if函数。
｛=MIN(IF(C2:C21="男",E2:E21))｝ ，这样就对了。
第一次接触到数组公式会很难以入戏，没关系，慢慢理解，不就是数组嘛。回头有个函数讲解，可以帮助理解数组公式。
第6题.“男生中有一门不及格的人数”
同样是用到数组公式，思路是“男”的数值置为1；
然后不及格为1门的值置为1，其它的不及格数为0，2，3门的值置为0。
两者相乘，只和符合题目条件的数才为1，其它的是0，把这些1加起来就是“男生中有一门不及格的人数”
同样前半部分是 (C2:C21="男")，后半部分是 ((D2:D21&60)+(E2:E21&60)+(F2:F21&60))=1)，这个可以接着分解来讲，
D列（语文）小于60分就是1，否则为0。然后E列F列同理。
这样三个数加起来，就是不及格的门数。这个数如果等于1，那就表示有且只有一门不及格。就反回判断值True（算术1），如果不是一门不及格，不等于1，返回值就是False（算术0）。
前后部分相乘的结果就是1或0，1就表示该行是男生有且只有一门不及格。否则就是0。
最后这个数组20个值用sum函数求和就是“男生中有一门不及格的人数”。
答案为：｛=SUM((C2:C21="男")*(((D2:D21&60)+(E2:E21&60)+(F2:F21&60))=1))｝
第7题.“男生中有两门不及格的人数”，其实就是把第6题的答案改个参数就行了。
答案为：｛=SUM((C2:C21="男")*(((D2:D21&60)+(E2:E21&60)+(F2:F21&60))=2))｝
接下来要介绍一个函数来帮忙理解数组公式：SUMPRODUCT
前面6、7题的答案可以改成
6. =SUMPRODUCT((C2:C21="男")*(((D2:D21&60)+(E2:E21&60)+(F2:F21&60))=1))
7. =SUMPRODUCT((C2:C21="男")*(((D2:D21&60)+(E2:E21&60)+(F2:F21&60))=2))
也是正确的。注意这次没有花括号，这个函数是自带数组光环的。那么理解一下这个函数，也就可以帮助解决数组公式。
SUMPRODUCT是这样用的： =SUMPRODUCT（数组1，数组2，数组3……），我们假设只有三组吧，就是数组123，里面运算过程就是数组1的第一个值×数组2的第一个值×数组3的第一个值，然后第二个值×第二个值×第二个值，第三个值×第三个值×第三个值，一直到数组的最后一个值。这样就产生一组乘积，最后把这组乘积相加，就是SUMPRODUCT函数的结果了。
具体的应用，例如你有两列数据，一列是单价，一列是数量。总价就是=SUMPRODUCT（单价列，数量列），一个函数搞定，不需要辅助列。
那么回到6、7题的答案，其实只是用到=SUMPRODUCT（数组1）这样而已，只有数组1，作用就是把数组1的所有数求和而已，因为没有数组2，所以没有相乘的过程，对于SUMPRODUCT函数本身的功能来说Too样！Too森破！
前面这部分，已经是本文最难的部分，想理解就来回操作分析几遍。
下面讲个简单许多的：大名鼎鼎的vlookup函数。
第8题 ,"有一列有若干个姓名，求自动找出对应的总分",用vlookup函数，从指定区域中找到匹配的单元格，然后挑出这一行右边的指定内容。
说到底，这在数据库中，是一个最基本的功能，JueBi样，JubBi森破,但为何vlookup在江湖中名声这么盛呢。我觉得有这么一种可能，数据库技术的应用，是有门槛的，需要专门学习才能上手，没多少人去学这个，属于小众技能。而Excle没有准入门槛，能打字就可以用Excle，随着使用的深入逐渐解锁各种技能，直到有一天，用到vlookup函数，发现以前加班都是白加了，用这个函数秒秒钟搞定无数工作量，而且这个函数的使用相对 “=A1+B1”、sum()、if() 等又复杂了一个等级，于是就成了一个分水领。刚会用的人觉得打通任督二脉，功力大涨。不会用的人就止步在这里仰望星空。
如图所示，N列是王家的6~10号同学，请给出各自的总分。答案也在图中，下面讲解一下。
语法是这样：有4个参数 =VLOOKUP(要查找的值,查找的范围,目标值在第几列,是否模糊匹配)
对比答案，就知道四个参数分别都是哪些内容。例如第一行，
要找的是“王6”，所以第一个参数是N2；
第二个参数是“$B$2:$G$21”，绝对值引用，在后面拉动填充的时候，查找的范围才不会跑偏；
第三个参数是6，在参数二中，范围的第1列“姓名”开始，向右边依次数过去，第6列是“总分”，所以填6；
第四个参数，语法上可以省略，实际运用中不能省，省了容易出一些预料不到的结果。 “0”与“False”表示精确匹配。
第5行，“王9”总分的公式不大一样，“$B$2:$G$21”变成了“B:G”，这表示什么呢？前者“$B$2:$G$21”表示的是一个“6列20行”这样的一个区域，后面“B:G”表示是的“B到G”6整列的范围，假如后续有新的记录添加上去，那么采用后者就不用去改公式内容。
如果数据较多，单独放在一个表，在另一个表引用，要怎么做？
=VLOOKUP(N2,Sheet1!B:G,6,0)，“Sheet1”表名，加上英文感叹号“!”接着单元格地址就行了。
如果是跨文件调用呢？一样没问题：
=VLOOKUP(N2,'C:\Users\KK4836\Desktop\[Book1.xlsx]Sheet1'!$B:$G,6,0)
语法什么的就不多说了，实际上可以不用手动输入，在编辑栏里面先选中第二个参数，然后打开存放数据的文件找到表，直接圈选，参数会自动形成。
新手容易碰到的问题基本就这些了，特别是第4个参数，一定要填上，指定精确匹配。
如果能熟练的使用vlookup函数，只能算上手而已，能快速排除使用中的各种问题才是高手。
“我明明写对了，为啥结果不对？”说出这样的话，一般是新手。因为一般就是语法或者参数用得不对，老手直接自己排除这些问题。但问题依然存在呢？那就很可能是“脏数据”的问题，如果“王8”后面多了个空格变成“王8 ”，或者是某些格式问题，或者数据重复等，总之问题是5花8门的，懂得快速找到问题，正确处理这些问题，这才是高手。所以话说回来，要养成良好录入数据的素质，保持良好的数据形式，不要给自己挖坑，不要给队友埋雷，才是中国好队友。
如果看到这里，还记得有一道没做，说明你思路很清晰。
9.第8题中，同样从姓名求出对应的学号。同样前面的图片已经给出答案了，但这必须讲一讲，因为这不是简单改一个参数的问题。
VLOOKUP是只能指定区域里（第二个参数）里的最左列查找，找到之后只能从最左列开始向右找，第三个参数不能是负数，即不能向左找。
像例子中这种从姓名查找学号，vlookup不能直接做到，需要用到特殊的技巧。
像这种从查找右列，从左列中挑出结果的需求，用vlookpu可以做到。把第三个参数变成这样：IF({1,0},B:B,A:A)，其实这是数组的概念，你可以解理为在一个虚拟空间中构建两列数据，左列就是B列，右列就是A列，vlookup也认就行了，呵呵。用这个方法你可以随便挑一列过来做右列哦。另外，其实可以扩展两列以上，这里就不探讨了。
有人不喜欢这种难嚼的用法，用其它函数也可以实现，例如INDEX函数和MATCH函数共同配合。
MATCH可以返回一个数字，表示要查找的值在第几位。MATCH（要查找的值,数据列,匹配方式），这几个参数是不是很眼熟？跟vlookup差不多，这最后一个参数填“0”也是精确匹配，其它却不是“模糊匹配”，有兴趣自己去看帮助。
INDEX（单列，第几位），就可以挑出所需要的单元格了。（INDEX函数有多列的情况，有兴趣自行看帮助）
=INDEX(A:A,MATCH(N14,B:B,0))
只要MATCH的查找列与INDEX的查找列对齐，就可以找到正确的单元格了。
=VLOOKUP(N2,$B$2:$G$21,6,0)
=VLOOKUP(N3,$B$2:$G$21,6,0)
=VLOOKUP(N4,$B$2:$G$21,6,FALSE)
=VLOOKUP(N5,B:G,6,0)
=VLOOKUP(N6,B:G,6,0)
=VLOOKUP(N9,IF({1,0},$B$2:$B$21,$A$2:$A$21),2,FALSE)
=VLOOKUP(N10,IF({1,0},$B$2:$B$21,$A$2:$A$21),2,FALSE)
=VLOOKUP(N11,IF({1,0},B:B,A:A),2,FALSE)
=INDEX($A$2:$A$21,MATCH(N12,$B$2:$B$21,0))
=INDEX($A$2:$A$21,MATCH(N13,$B$2:$B$21,0))
=INDEX(A:A,MATCH(N14,B:B,0))
随机文章2013 年 03 月 29 日 |
(1)2012 年 12 月 12 日 |
(0)2013 年 09 月 02 日 |
(11)2012 年 09 月 30 日 |
(0)2012 年 04 月 02 日 |
(0)2011 年 10 月 03 日 |
Baidu提供的广告