Excel中最常用的查找公式Vlookup及Sumifs

序言

使用Excel做数据分析工作已经快10年了，因为经常要处理几十甚是上百万行的数据，所以常常要用到查找和定位的公式。而平时用的最多的就是两个，也就是，Vlookup以及Sumifs，单独用，搭配着用都有。基本上一套复杂的项目成本核算方案，用这两个公式为主体就能搭建出来。这里呢就和大家分享一下这两个公式的使用技巧，注意事项等等，零零碎碎说的比较多，大家就耐心看吧。

正文

Vlookup以及Sumifs的基础功能相信大家都清楚，这里为了文章的完整性就简单的说一下。

一、VLOOKUP

vlookup，是针对数值的单条件（使用数值可以实现多条件，本文就不展开了）查询；可以根据提供的条件，在指定的区域中返回要查找的值。

有两个功能相近的公式，hlookup和lookup，区别就是：

vlookup是在垂直方向查找（v代表vertical）；

hlookup是在水平方向查找（h代表horizontal）；

xlookup不预设查找方向，比vlookup和hlookup功能更加强大。

这样说可能不太直观，我们来举个栗子，如果我们有如下的数据：

如果我们的条件是姓名，根据姓名，查找成绩，那就是从左往右查（姓名列在左边，成绩列在右边），这样我们要用的就是vlookup，垂直方向查找。

如果我们的数据是这样的：

条件还是姓名，那我们就要用hlookup，水平查找。

至于xlookup就强大了，xlookup可以替代vlookup和hlookup；也就是说vlookup和hlookup能做的，xlookup都能做，反过来则不行。

那为什么反而是vlookup使用的最多呢，道理也很简单，那就是“合适”。这就像找另一半，没有最好的，只有最合适的。

大家想一想，图1的格式比较多见还是图2的格式比较多见？做过数据处理或者接触过数据库的同学肯定知道，必然是图1。所以hlookup的使用频率自然就不高了，而xlookup理解起来更难一点，那么自然而然vlookup就用的多了。如果大家关注一些讲Excel使用技巧或者Excel培训机构的公众号，就可能看到这种标题:

“你还在用VLOOKUP么，有一个公式比它强百倍!!!”

点击去，就会发现基本上都是介绍xlookup的，公式写的花里胡哨，一长串。还是那句话没有最好的，只有最合适的。不过要是大家感兴趣后面可以做一期专门介绍xlookup的，毕竟微软自己都说xlookup是vlookup的改进版。

跑题了，我们言归正传，vlookup有四个参数，lookup_value,table_array,cal_index_num,以及range_lookup，含义分别是：

lookup_value:查找的条件

table_array:查找的范围

cal_index_num:要查找的列相对查找条件所在列的距离（列数）

range_lookup:模糊查找还是精确查找

我们用图1的数据来具体的写一下：

公式很简单，就是在a1-b17这个范围内查找“张三”所在的列（a列）后面一列（参数2）的值，这个公式如果写成：

=VLOOKUP("张三",A2:D17,2,0)

结果也是一样的，虽然后面这个公式里面我们的查找范围扩大到了d列，但是因为第三个参数依旧是2，因此查找的还是第一列后面一列的值。如果我们把第三个参数从2改成3，那么图3里面的公式会报错，因为查找的范围只有两列，而后面一个公式则会返回"数学"。

当然像上面一样的公式在现实工作环境中是毫无意义的，那我们就把数据弄得复杂一点，先看数据：

假如某个学生一个学年的综合得分的算法如下：

综合得分=（学科1得分*学科1权重+学科2*学科2权重）*0.5*MAX（奖项权重1，奖项权重2）

那我们该怎么算第一学年，每个学生的综合得分呢？

这里就要用到vlookup了，我们可以把学科权重lookup到D列后面。这里提醒各位，在做之前记得一定一定要备份一遍原文件，在备份文件里面做修改。

让我们来看公式：

这里涉及到的自动填充方面的知识这边就展开讲了。把权重lookup过来，然后我们就可以写公式计算了。但是到了这一步我们不去计算，来讲一下vlookup的进阶应用。先看一下修改后的数据：

现在的数据中，不同的学年学科的权重不同，这时候如果我们要用vlookup，而且要只写一遍公式的话就要想点办法了。只用vlookup肯定不行，因为现在的查询条件有两个，一个是学科，还有一个是学年。那这里我们就要用到另一个定位函数match，这个函数的作用简单来说就是返回要查找的值在一个数组中的位置。我们想要达到的目的就是在查到一年级的时候vlookup的第三个参数是2，查到二年级的时候第三个参数变为3。所以macth正合适，公式也比较简单，如下：

=VLOOKUP(C2,$G$9:$I$11,MATCH(D2,$G$9:$I$9,0),0)

这样呢我们就实现了写一遍公式就完成把两个学年不同的权重添加到e列。

vlookup和match是绝配，大家可以自己多多操练一下。

二、SUMIFS

sumifs的基础功能呢我们也简单介绍一下。

这个函数的功能是条件求和，比如上面的数据里面，如果我要求张三第一学年语文数学的成绩合计，我们就可以用

sumifs最起码要有三个参数，也就是：

sum_range:要求和的目标所在的列或范围

criteria_range1:第一个条件所在的列或范围

criteria1:第一个条件

然后还可以接着写第二个条件，第三个条件。。。。。

和sumifs相关的函数还有，sumif,averageifs,maxif等等，很多

那为啥一个条件求和的公式会被用来作为查找定位？

首先excel大部分情况下处理的都是数字，其次条件多了，结果就唯一了。

还是比如上面的用vlookup查找张三成绩的语句：

=VLOOKUP("张三",A2:D17,2,0)

这个语句会在找到第一个符合条件的结果后就中止并返回结果，也就是说我们最终得到的是张三在一年级是的数学成绩，如果用sumifs来写的话：

=SUMIFS($B$2:$B$19,$A$2:$A$19,"张三")

这样得到的结果和vlookup是不一样的，得到的是张三所有的成绩的和，如果要达到和上面的vlookup一样的效果我们就需要添加条件：

=SUMIFS($B$2:$B$19,$A$2:$A$19,"张三",$C$2:$C$19,"数学",$D$2:$D$19,"一年级")

这样得到的就是和vlookup一样的结果了。

大家别看好像sumifs写的更复杂，实际应用中要的就是sumifs的效果；vlookup碰到第一个符合的就中止了，结果无法预期，这是个很大的问题。

和sumifs搭配的比较多的是vlookup，index等，比如上面的数据，我们要求第一学年各个同学的综合得分，这就需要vlookup和sumifs搭配使用，我们来看结果：

大家可以看到，加了两个辅助列，这两个辅助列都是使用了vlookup，最终的计算用的是sumifs，搭配了maxifs；当然这个例子里面实际maxifs没有啥作用，完全可以用vlookup替代，但我们要严谨。

三、注意点

用vlookup和sumifs的时候大家特别要注意自动填充的锁定，实际工作中公式不止要能下拉，还需要能向右拉。举个例子，我们来修改一下数据，如果没有奖项权重，没有学科权重，只是求每个学院每个学年的平均得分（也就是说两个学科权重一样），那我们就可以这样做：

=SUMIFS($B$2:$B$19,$A$2:$A$19,$L3,$D$2:$D$19,M$2)/2

这个公式里面有3种锁定方式，全锁，下锁和右锁，大家可以自己理解消化一下。

然后还有两个至关重要的注意点，

一个是vlookup找不到会报错，sumifs找不到会返回0，所以如果你看到sumifs写好之后是0的话要谨慎一点；需要确认一下到底是原始数据就是0还是你写的条件不对。

还有一个就是用sumifs一定要注意原始数据有没有重复值，或者你给的条件够不够。

这里给大家说一个我实际经历过的惨痛教训。楼主当时给一个单位做成本核算，每个月甲方爸爸都会把当月的成本数据给到楼主，楼主呢会简单的整理好后导入到核算模板中，出了结果反馈给甲方，甲方呢用这个成本来做预算和绩效。话说有一个月，公司给楼主塞了个刚培训完的小白，楼主就让他/她做成本的前期清洗汇总。没成想甲方爸爸那边对接人也换成了个小白，给到公司的成本数据中有部分数据是两个月的。然后楼主手下的这位小白同学看都没看，还是用sumifs，而且条件中没限定日期，因为以前给的都是一个月的，不需要。再然后呢，我也是傻乎乎的没有检查小白的作业直接把结果算出来给到了甲方爸爸。而甲方爸爸也没有仔细看，就用这个错误的成本数据给各个部门核定了当月的考核得分，绩效发放总额等等。然后，就没有然后了，一地鸡毛。幸亏楼主平时舔甲方爸爸比较给力，甲方爸爸的总会出面平息的一场风波。

所以，再次提醒大家，看数据很重要，不要一上来就咔咔的写公式，先看数据，找规则

好了这次就讲这么多吧，其实还有很多没有讲，后面有时间再说吧，希望本文能给大家带来点什么吧