这3个函数都不懂，千万别说自己会Excel！（必学）

发送【UP】

本文作者：明镜在心

本文审核：玛奇鹅

本文编辑：雅梨子、竺兰

嗨，大家好，我是明镜在心。

熟练使用 Excel 电子表格，可以大大提高工作效率，尤其是熟练使用其中的函数功能。

但是对于大部分人来说，一看见函数就比较头痛。

其实也没必要那么恐惧！

对于我来说，在职场中工作了二十几年，使用最多的也就三个函数。

它们分别是：VLOOKUP、SUMIFS 和 COUNTIFS 函数。

下面就跟我一起来看看，在职场中是如何应用它们吧！

热文推荐：同事用Excel做的环形气泡图，为什么这么漂亮？

VLOOKUP 函数

VLOOKUP 函数的作用是：查找。

这个函数应用得非常广泛，经常能在公司里面听见有人说「V 一下就行」。

语法结构如下：

=VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)

它一共有四个参数，用通俗的语言说明如下：

❶ lookup_value：要查找的值，通常是引用某一个单元格。

❷ table_array：在哪个区域中查找，就是将要在哪个单元格区域中查找。

❸ col_index_num：返回查找值对应的列号，如果在查找区域中找到这个值的话，返回需要的列数字。

❹ range_lookup：精确查找还是模糊查找，如果是精确查找，我们使用 0 或者 FALSE，如果是模糊查找我们使用 1 或者 TRUE。

在绝大多数情况下，我们使用 0 或者 FALSE 的精确查找方法。

 

如下图，这是一份工资表，想查找出某位员工（比如：朱兴）的工资。

在【G2】单元格输入如下公式：

=VLOOKUP(F2,A1:D8,4,0)

公式解析：

第一参数：【F2】就是我们需要查找单元格中的朱兴这个人。

第二参数：【A1:D8】就在这个区域中查找。

第三参数：4，表示：如果在姓名这一列查找到朱兴这个人，就返回朱兴这一行对应的第四列的值，就是工资这一列的值（9081）。

第四参数：0，表示精确查找这个朱兴，而不是查找朱兴明，朱一兴，朱朱兴等等。

对于小白来说，需要多看多练几遍才能体会。

大白话就是类似我们平时走路，先向下走几步，再向右走几步，最后返回我们需要的值。

看上去还是比较简单的吧，就跟走路一样！

PS. 这里需要说明下，这个函数只能向右查找，不能向左查找。

如下图，我们需要查找员工编号：

因返回的值不在查找值的右侧，而是在其左侧，会返回一个乱码（即错误值）。

此时可以用最简单的方法解决这个问题，就是把姓名列调到 A 列去，使其返回的值出现在右侧。

另外：第一参数必须在第二参数的首列进行查找，不可以出现在非首列。

比如下图中，第一参数位于 A1 列，第二参数就是 A1:D8 单元格区域。

SUMIFS 函数

SUMIFS 函数的作用是：条件求和。

它有一个兄弟是：SUMIF，只不过，这个只能单条件求和，而 SUMIFS 既可以单条件求和，也可以多条件求和。

所以我们学会 SUMIFS 就可以了。

语法结构如下：

=SUMIFS(sum_range，criteria_range1，criteria1，[criteria_range2，criteria2]，...)

基本套路是：

=SUMIFS（求和区域，条件区域 1，条件 1，条件区域 2，条件 2,…）

其中条件区域和条件需要成对出现，最多可以输入 127 对。

如下图，这是今年公司的收款表，想求出上半年南京阳光科技有限公司的收款金额是多少。

分析一下，上面有几个条件？

2 个。

条件 1：上半年；条件 2：南京阳光科技有限公司。

因此，我们可以在【G2】单元格输入如下公式：

=SUMIFS(C:C,A:A,'<='&E2,B:B,F2)

▲ 左右滑动查看

公式解析：

第一参数：【C:C】是需要求和的金额区域。

 

第二参数：【A:A】是日期条件区域。

第三参数：'<=' & E2 意思是：小于等于【E2】单元格中的值，就是小于等于 2021 年 6 月 30 日。

也可以写成这样：'<=2021-6-30'。

第四参数：【B:B】就是在付款单位列。

第五参数：【F2】就是在付款单位列中，查找等于南京阳光科技有限公司。

 

如果条件不是两个，小伙伴可以根据实际情况增加或者减少条件对。

COUNTIFS 函数

COUNTIFS 函数的作用是：条件计数。

它也有一个兄弟是：COUNTIF，只不过，这个只能单条件计数，而 COUNTIFS 可以单条件计数，也可以多条件计数。

所以，我们也是学会 COUNTIFS 就可以了。

 

语法结构如下：

=COUNTIFS(criteria_range1，criteria1，criteria_range2，criteria2,…)

 

基本套路是：

=COUNTIFS（条件区域 1，条件 1，条件区域 2，条件 2,…）

 

其中条件区域和条件需要成对出现，最多可以输入 127 个区域/条件对。

 

如：在工作中，通常需要填写一些关于公司人员性别的数据，比如女性多少人，男性多少人。

 

来看看下图的案例：

在【G2】单元格输入如下公式：

=COUNTIFS(D:D,F2)

公式解析：

第一参数：【D:D】就是性别列。

 

第二参数：【F2】就是在性别列里面统计男女人数。

 

还可以统计某个时间段的性别人数。

比如上半年的女性人数。

 

公式可以写成：

=COUNTIFS(B:B,'<='&F2,D:D,G2)

这是两个条件的应用情景，公式解析可以参照上面的 SUMIFS 理解下。

 

知识扩展

👉 互相检查核对数据：

 

在我们做好表格之后，最最重要的一件事情就是检查核对数据是否正确。

如果提交上去的数据有错误，轻则会被领导骂，重则有可能会丢掉饭碗。

 

所以大家千万要记住检查数据的正确性。

来看下面两个图，我们想查找朱兴这个人的工资是多少？

 

【G2】公式如下：

=VLOOKUP(F2,A1:D8,4,0)

【H2】公式如下：

=SUMIFS(D:D,A:A,F2)

但是，两个公式返回的结果不一样，应进一步查明原因是什么。

 

是数据本身有错误？

还是我们对公式理解不到位导致的应用错误？

排查手段：可以用 COUNTIFS 统计下人数。

=COUNTIFS(A:A,F2)

通过 COUNTIFS 的辅助排查，我们发现，姓名为朱兴的员工一共有两名，这就是导致我们上面结果出现不同的原因。

最后，我们将朱兴筛选出来，然后进一步处理。

如果是姓名相同，可以通过唯一值来进行区分。

比如：员工编号等。

 

如果是输入错误，改成正确的即可。

 

👉 返回的结果值不同：

 

用 VLOOKUP 查找数据时，查找不到会返回错误值（#N/A）。

 

用 SUMIFS 或者 COUNTIFS 时，如果找不到数据时会返回 0，不会返回错误值。

 

如下图：我们想统计朱晓兴这位员工的工资以及是否存在姓名相同的情况。

显然，查找区域没有朱晓兴这个人，所以 VLOOKUP 返回错误值。

SUMIFS 和 COUNTIFS 返回 0。

PS. 如果需要屏蔽错误值的话，使用 IFERROR 函数套上外衣即可。

比如想将错误值显示为空，公式如下：

=IFERROR(VLOOKUP(F2,A1:D8,4,0),'')

基本套路是：

=IFERROR（原公式, 出现错误值时想要返回的内容）

 

其中：第二参数输入一对英文半角双引号表示返回空白单元格。

总结一下

今天我们学习了工作中最常用的三个函数，分别是：

 

❶ VLOOKUP 查找引用函数。

❷ SUMIFS 条件求和函数。

❸ COUNTIFS 条件计数函数。

学好这三个函数，就可以解决日常工作中的大部分问题了。

另外小伙伴们还会用到哪些常用函数或者还希望学习哪些函数，可以在文末给我们留言哦！

2 分钟、3 步骤、秒懂一个 Office 新技能！

秋叶家爆款好书《秒懂 Word/Excel/PPT》全彩版，原价 149.7 元，现在三本低至 69.9 元！