Excel的vlookup等函数的使用例子及数组公式

时间:2015年09月14日 19:19 星期一 栏目:未分类 作者: 评论:0 点击: 2,891 次

Excel

 

关键字: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))

随机文章

Excel的vlookup等函数的使用例子及数组公式:等您坐沙发呢!

发表评论

您必须 [ 登录 ] 才能发表留言!

Baidu提供的广告

最近访客

    最新评论