【Excel高效办公】电脑文件整理费时费力？用Excel轻松又高效！

本文作者：King

本文来源：秋叶PPT(ID：PPT100)

本文编辑：竺兰

实际工作中，制作表格，可能会碰到各种各样的需求。

有些需求，如果没有真正碰到过，根本没法想象。

但是，碰到了，你就得想尽办法去解决问题。

这就是一个典型的文件管理问题。

经常要管理很多档案、商务文件的小伙伴今天有福了。

拉登教你批量管理文件的 3 个妙招，一并解决如下各种文件管理难题：

• 如何为文件清单快速添加分类信息；
• 如何快速统计不同类别的文件数量；
• 如何批量创建带超链接的文件目录。

❶ 插入标题行后打开筛选器；

❷ 按文件名筛选，输入包含的字符；

❸ 选中拟输入分类的区域；

❹ 输入分类名称；

❺ 按【Ctrl+Enter】批量填充到选中区域。

快记笔记！VLOOKUP公式中的IF{1,0}原理是这样的！

【Excel技巧】快记笔记！VLOOKUP公式中的IF{1,0}原理是这样的！

本文作者：拉登 Dony

本文来源：拉小登(ID：ladengchupin)

本文编辑：竺兰

我是拉小登，一个爱梳头的 Excel 老师。

有同学问，VLOOKUP 反向查找姓名，怎么用数组公式解决？ 原理是什么？

不用谢！

夸我，好吗？ 1 个字顶 1 块钱！

反向查找

来看下面这个表格，现在要根据【姓名】查找【员工工号】。

大家都能想到用 VLOOKUP 函数，但是 VLOOKUP 只能从左往右查找，也就是，只能查找【姓名】右边的数据。 【员工工号】在左边就查不了了。

想要查找也行， 把【员工工号】挪到【姓名】列右边去 ，然后正常写 VLOOKUP 函数就可以了。

对应的公式如下：

▋ 方法 2

把【员工工号】挪到【姓名】的右边，这是必须的一步，否则 VLOOKUP 没法计算。

如果不在表格里挪，那就用函数公式挪，所以就有了 IF{1,0} 的公式。

举个例子，下面的表格中，要把 A 和 B 列数据交换顺序，就可以用 IF{1,0}公式。

公式如下：

IF{1,0}是一个数组公式，本质上和下面的 IF 函数是一样的。

如果是 1 就引用 B 列【姓名】，如果是 0，就引用 A 列【员工工号】。

把两个公式合并成一个公式，参数 1 页变成了一个数组区域的引用$D$1:$E$1。

把返回值也改成数组区域引用，即： $B$2:$B$7 和$A$2:$A$7，就可以一个公式反馈两列数据。

最后把 D1:E1 的数值带进去，即{1,0}，就得到了最终的公式。

公式如下：

VLOOKUP 函数中的 IF{1,0}也是一样的道理，用来交换两列的顺序。

明白了吗？ 不要谢，请【点赞】。

INDEX 函数解决

其实，对于大多数人，我不推荐用数组公式，不好理解。

更为常见的方法，是使用 INDEX 和 MATCH 函数来实现。

▋ MATCH 函数

首先用 MATCH 函数查找姓名所在的位置。

公式如下：

▋ INDEX 函数

然后用 INDEX 根据姓名的位置，在【员工工号】列，查找对应位置的工号并返回。

公式如下：

▋ 合并公式

最后把 INDEX 和 MATCH 函数组合到一起，可以实现相同的效果，公式更好理解。

公式如下：

写在最后

函数公式，其实就是把我们在表格里的操作，翻译成两个公式的操作。

这个和写代码是一样的，自己手动做的事情嫌麻烦，那就用代码来做。

无论是代码还是人工手动，要做的事情是一样的，只不过代码做起来效率更高而已。

考考你：

我用 IF{1,0}写了一个公式，猜一猜公式计算的结果，应该是哪一个？

公式如下：

计算结果是哪个？

如果这篇文章对你有帮助，请帮忙「点赞」。

VLOOKUP函数都不会，简历上别再写精通Excel了！

大家好，我是明镜在心。

我们公司最近也正在招聘新员工，我也顺便去网上溜达了下。

看到很多岗位的招聘要求上都写着：「熟练使用 Excel 等办公软件」，而且在面试的时候还都有上机操作的题目。

然后就去网上搜了搜，看看 Excel 面试题都是什么内容、什么水准的题目，测试下自己能不能搞定。

在这个过程中，我发现了一道面试题，挺有意思的，今天分享给大家。

下表记录的是公司客户的每一次来店日期，要求：用函数提取出客户最后一次来店日期。

乍一看好像不难，但是对于小白而言，可能还是需要下一番功夫。

在简历上写着熟练或者精通 Excel 的人，估计有相当一部分是做不出来的

分析问题

我们先来分析下这道题！

这个题目是一个双条件查找引用，而且是查找最后一次的【来店日期】。

比如：客户【张三】有很多重名的，【客户编号】也有重复的。

要求：提取出姓名为【张三】，并且编号为【MD003】的最后一次【来店日期】。

在图中：【张三】【MD003】的【来店日期】一共有两次。

第一次：2022-1-4

第二次（也就是最后一次）：2022-1-8

思考一下，如果是你，你会用什么办法呢？

对于查找，我最先想到的是用 Vlookup 函数（可能有很多人的第一反应也是这个），因为但凡学习 Excel 的人都会接触到它，它也是最常用的查找函数。

这个思路是对的，Vlookup 还真能解决这个问题，下面我们就一起来看看~

解决问题

❶ VLOOKUP 函数

❷ VLOOKUP 函数+辅助列

❸ MAX+IF 函数组合

❹ MAXIFS 函数

❺ 透视表

‍

 

本文作者：小敏

本文编辑：竺兰

我们秋叶 Excel 训练营的同学提了一个问题，看看你知不知道~

截图看不清？

没瓜系，重要的是这个问题：

用 VLOOKUP 从多表中提取信息，怎么弄？

这个问题，嗯，毕竟是从我们训练营的同学口中问出来的，还是很有水准的~

先来简单回顾一下 Vlookup 函数：

VLOOKUP 有四个参数——

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

❶ lookup_value：查找值；

❷ table_array：查找区域（去哪儿找）；

❸ col_index_num：返回的值的列数；

❹ range_lookup：精确查找 or 模糊查找。

四个参数中 1 和 4 是非常明确的，主要问题就在 2 和 3 上，2 的结构如果是一定的，那么 3 也是确定的数值。

了解完 Vlookup 用法，我们来聊一聊如何跨多个表查找值。

一个表一个表找

分别用 Vlookup 公式从两个表里取就好了嘛~

可是，我们童鞋说：我也不知道这些数据它们都在哪个表里啊 

太麻烦了！

好吧，那就看第 2 种方法吧~

合并多表一起找

不知道数据分别在哪个表，那我们干脆把所有的数据都放到一个表里去，让 Excel 自己找！

我们的助教老师们真的是太敬业了，给出了一种又一种办法。

直到帮童鞋解决问题。

甚至还有老师在事后看到，还在想有没有更快的解决方法。

你可别说，还真有！

如果多个数据表不整合在一起，就不能跨多表查找数据了吗？

当然可以啊~

假设我们要从多个部门的分表中，查找到某个工号的员工姓名。

如果这个工号是「人力资源部」的，那我们就可以写公式：

可是，不凑巧，这个工号不是人力资源部的，所以找不到，显示 #N/A 错误。

Vlookup 遇见这种错误不要慌，我们有办法。

用一个屏蔽错误值的函数——Iferror 函数，就能把错误值换成我们要的。

于是乎，我们可以在这个出现错误值的函数外面套一个 IFERROR。

IFERROR(value,value_if_error)

❶ value：需要做判断的值；

❷ value_if_error：如果值错误要返回什么内容。

当出现错误值的时候，继续在「研发部」里看看能不能找到——

再出现错误值的时候，继续在「财务部」里看看能不能找到——

这样一层层套下去，直到把所有表都查找完，总能找到你要的值。

如果全部找完还是 #N/A 错误，那就是真没数据了。

所以用这样一个多层屏蔽 #N/A 错误值的思路，我们就可以在多个表中进行数据查找啦~

但是！

掰掰手指一算，

3 个表要用 Vlookup 函数 3 次，Iferror（3-1）次；

 

4 个表要用 Vlookup 函数 4 次，Iferror（4-1）次；

 

......

这个公式能不能简化一下呢？

能！

引用 Indirect 函数指定查找区域

如果你嫌上面的公式太长，那可以套用这个公式：

不过这个公式的理解成本比较高，会用就可以了~

你只要会修改这些参数，就可以套用该公式：

A2：Vlookup 的查找值；

 

{}数组里的内容：多个工作表名称，用逗号分隔；

 

A:A：查找值在各个表中的哪一列，需要确定各个表的该列是否存在这个查找值；

 

A:B：vlookup 的查找区域；

 

2：返回值的列数，姓名是在 A:B 区域中的第 2 列。

不过快速的方法也有它的大前提：多个工作表的格式必须一致。

怎么样？解决问题的方法是不是有很多？

小白有小白的解决方法；懂一点 Excel 和懂更多 Excel 的人，解决方法又会有不同。

问题同样都能解决，但是解决问题的速度是大不相同。

作者：明镜在心

现在我们想看下 2020 年研发项目预算费用总共是多少

学完今天这篇文章，想必大家对于隐藏数据的处理有了更深刻的认识吧

作者：明镜在心

大家好，我是明镜在心。

记得大学毕业的时候，我应聘一家单位，当时有上机操作 Excel 的测试题。

除了求和、查找……这些基本的函数应用之外，其中还有一个题目是问，如何实现表格之间的快速跳转。

在日常工作中，别的单位发来的表格里面，也经常有一样的跳转链接的功能，点击一下单元格，就可以跳转到指定的位置或者打开指定文件。

实现这个跳转的函数就是：「HYPERLINK」。

HYPERLINK 函数

它的语法结构是：

=HYPERLINK (link_location，[friendly_name])

里面有两个参数：

第一参数 Link_location，意思是：要打开的文档的路径和文件名。

第二参数 friendly_name，意思是：单元格中显示的跳转文本或数字值。

函数帮助里面的语法解释都是比较晦涩难懂的。

我把它翻译一下，可能会更好理解点：

超链接函数（想要到达的位置或者想要打开的文件，单元格中想要显示的内容）

基础语法想必大家都清楚了~

那下面就跟我一起来学习下，这个函数在实际操作中的神奇用法吧！

同一张工作表中跳转

如下图：我们想链接到同一张工作表中，某一个指定的单元格，比如【B11】单元格。

可以在【E2】输入如下公式：

当我们点击【E2】时，Excel 就会将活动单元格跳转定位到【B11】单元格了。

公式解析：

第一个参数：要链接的位置；

第二个参数：要在单元格中显示的内容。

另外，我们需要注意的是：

「工作表名称」的前面需要加一个「#」井号。

「工作表名称」的后面需要加一个「！」感叹号。

第一参数的通用套路是：「#+工作表名称+！+单元格地址」

如果是在同一个工作表中进行跳转链接的话，「工作表名称和感叹号」也可以省略掉，但是「#」号不能省略。

如下图：

在不同的工作表中跳转

再来介绍，如何在同一个工作簿中的不同工作表中，进行跳转链接。

比如，每个企业每年都要做企业所得税年度纳税申报，我们可以做一个汇总表，然后链接到各个具体的明细表进行查看。

如下图：

我们按照上面的通用套路，在【C3】单元格，输入公式：

公式的意思是：

需要链接到「A100000 中华人民共和国企业所得税年度纳税申报表（A 类）」这张表的 A1 单元格。

这时我们点击【C3】单元格，可以直接跳转到指定工作表的指定位置了。

但是点击之后，出现如下错误提示：「引用无效」。

这是怎么回事呢？

问题就出现在这个符号()括号上面，此时，我们需要对公式进行一下加工。

在工作表的名称前后各加一个单引号。

公式如下：

注意：单引号一定是加在工作表名称的前后。

这样点击【C3】单元格，就不会出现错误提示了。

提示：

如果在工作表名称中有特殊字符或者有空格等字符的话，提示「引用无效」，这时记得一定要加单引号噢！

在不同的工作簿中跳转

有时我们在制表的过程中，想查看相关文件的内容。

比如：我们想打开财务报表 2020 年 12 月这个工作簿文件，它的保存位置是在 E 盘-我的文件夹中，

我们在【D3】单元格中输入如下公式：

像上面这样，只要把目标文件的「完整地址+文件名+扩展名」做为第一参数，就可以直接打开对应的工作簿文件了。

当然，我们还可以指定工作簿中某一张工作表的具体位置，查看数据会很方便。

比如，我们想打开「E 盘-我的文件夹-财务报表 2020 年 12 月」这张工作簿，并且把活动单元格定位在「资产负债表」这张工作表中的【A10】单元格。

公式如下：

这里的第一参数是：

「完整地址+工作簿文件名+扩展名+#+工作表名称+！+单元格地址」

注意：

如果工作表名称中有空格或者特殊字符的话，还需要在工作表名称的前后各加一个单引号。

链接到不同的文件

有时我们在表格中，想要打开非 Excel 文件，比如：打开 Word 文档等。

这也是可以实现的！

公式：=HYPERLINK("E:\我的文件夹\2020 年审计报告.docx","2020 年审计报告")

公式中的第一参数，跟上面思路一样，

也是：「完整路径+文件名+扩展名」

这里不仅仅可以链接打开 Word 文档，还可以打开 PDF\JPG\TXT 等各种类型文件。

只要按照上面的思路更改下「路径+文件名+扩展名」就可以了，使用起来非常方便。

链接到指定网页内容

外贸公司或者有外币业务的公司，经常需要查询人民币汇率中间价。

如下图：是 2021 年 1 月 29 日的人民币汇率中间价公告。

我们可以把每个月最后一天的人民币汇率中间价做成一个跳转链接。

点击对应的单元格，就可以跳转到指定日期的中间价公告了。

比如：我们可以在【B2】单元格中输入如下公式：

公式如下：

第一参数就是具体的网址。可以直接从浏览器中的地址栏复制过来。

【B3:B5】单元格中的公式跟上面类似，只是第一参数的具体网址不同。

除了查询网页上人民币中间价之外，小伙伴还可以根据实际情况，做成自己需要链接的任意网址，将此运用在工作中还是很方便的。

总结一下

今天，我们学习了这个神奇的跳转链接函数（HYPERLINK），

通常会用于以下五个方面：

❶ 同一个工作表内的跳转链接。

❷ 不同工作表之间的跳转链接。

❸ 不同工作簿之间的跳转链接。

❹ 跳转打开其他类型的文件。

❺ 跳转链接到指定网页内容。

在日常工作中，你最喜欢使用哪一种呢，欢迎在留言交流噢！

【Excel函数】这是我目前见过最硬核的最值函数合集，让我的效率翻了10倍！

作者：明镜在心

嗨，大家好！我是明镜在心，很高兴又一次与大家分享 Excel 函数相关知识！

记得上学的时候，每次考试过后，学校都要统计这次考试的最高分和最低分分别是多少。

在 90 年代计算机和办公软件并不普及的时候，只能人工手动统计最高分和最低分。

但随着时代的进步，现在也就不用去手动统计了，利用软件工具，可以轻松完成数据的统计。

今天我们就来看下，如何使用 Excel 中函数快速求出的一组数据中的最高分和最低分。

如下图，是某所学校的一次考试成绩(数据已简化)：

现在需要求出，本次考试成绩总分中，最高分和最低分分别是多少？

一共有 3 种求解方法，下面就来看看。

MAX 函数与 MIN 函数

MAX 函数的意思是就是求取最大值。

如下图：

在【K2】单元格输入以下公式：

公式的意思是：

求出【I2:I7】这个单元格区域中的最大值是多少。

它的参数非常简单，直接是引用单元格区域就可以了。

MIN 的意思就是求取最小值。

如下图：

在【I2】单元格输入以下公式：

公式的意思是：

求出【I2:I7】引用单元格区域中的最小值。

可以看出：MAX 函数和 MIN 函数两者使用方法完全一样，可以直接求出单元格区域中的最大值或者最小值。

LARGE 函数与 SMALL 函数

如果要求单元格区域中的第几个最大值，我们可以用 LARGE 函数。

如下图：

在【K3】单元格输入以下公式：

公式的意思是：

求出【I2:I7】单元格区域中的第一个最大值。

它的参数有两个，

=LARGE(单元格区域,第几个最大值)

意思是：求出单元格区域中的第几个最大值，比如写上数字 1，就是第一大，写上数字 2，就是第二大，依次类推。

如果要求单元格区域中的第几个最小值，我们可以用 SMALL 函数。

如下图：

在【L3】单元格输入以下公式：

公式的意思是：

求出【I2:I7】单元格区域中的第一个最小值。

=SMALL(单元格区域,第几个最小值)

这里的 LARGE 与 SMALL 的使用方法也是完全一样的，也比较好理解。

我们从这两个函数的参数和用法上可以看出，他们比 MAX 和 MIN 要灵活很多，可以求出区域中第几个最大值或者第几个最小值。

SUBTOTAL 函数与 AGGREGATE 函数

SUBTOTAL 函数，是一个多功能函数，它可以用来求和、求平值、求最大值、求最小值等，共包含 11 种统计汇总功能。

先来看下 SUBTOTAL 函数是如何统计最大值的。

在【K4】单元格输入以下公式：

公式的意思是：

求出【I2:I7】单元格区域中的最大值。

这里的第一个参数是数字 4，代表最大值的意思。

当然在这里也可以输入数字 104，结果都是一样的。

他们的区别在于：如果区域中手动隐藏了某些行的情况下，结果可能会不同。

下面是该函数的第一参数的取值，供大家参考下。

如果想求出最小值，只要把第一个参数改成 5 或者 105 就可以了。

OR：

AGGREGATE 函数，它也是一个多功能函数。

是在 Excel2010 或者之后的版本中新增加的。共包含 19 种统计汇总的功能。

一起来看看 AGGREGATE 函数的使用方法：

在【K5】单元格输入以下公式：

公式的意思也是求出区域中的最大值。

如果求最小值，把第一参数的数字 4 改成 5 就可以了。

如下图：

这个函数功能非常强大，比如，它可以在隐藏行的条件下或者单元格中存在错误值的情况下，求取最大值或者最小值。

以下是它的第一参数和第二参数的具体的取数，供大家参考下。

知识拓展：

其实，我们在平时的工作中，也有可能不是简单的求取最大值和最小值，在很多情况中，是要在满足某些条件下，求取最大值和最小值。

比如，还是以上面的例子为例，想求出一班中总分最高和最低分别是多少：

在【L2】单元格输入以下公式：

公式的意思是：

先用 IF 函数判断【C2:C7】这个单元格区域等于【K2】单元格中的班级，如果相等就返回【I2:I7】区域中对应的值，如果不相等就返回 FALSE。

最后用 MAX 求出这个结果数组中的最大值。

此处 MAX 函数会忽略 FALSE，因此最大值就是 400。

另外，需要提醒大家的是：这个是数组公式，需要按三键结束【Ctrl+Shift+Enter】。

MIN 函数也有同样的用法。

当然还可以用 LARGE、SMALL、AGGREGATE 等函数，按条件求取最大值和最小值。

这些留给小伙伴们自行尝试。

总结

今天，我们一共学习了六个函数，可以求最大值或者最小值。

但是每个函数的应用场景又有所区别噢。

比如：要是无条件的情况下，求取最大值和最小值，以上六个函数都可以。

如果是有条件下的情况下，求最大值和最小值，只有：

MAX\MIN\LARGE\SMALL\AGGREGATE 这五个函数可以。

如果有隐藏行的情况下，SUBTOTAL 和 AGGREGATE 这两个函数可以。

如果区域中有错误值的情况下，只有 AGGREGATE 这个函数可以直接忽略错误值求出。

而 MAX\MIN\LARGE\SMALL 函数需要结合 IFERROR 等容错函数来求出。

（完）

小伙伴们好啊，多区间判断的问题想必大家都遇到过，比如成绩评定、业绩考核等等。今天老祝就和大家分享一个多区间判断的函数公式套路。

先来看问题，要根据A1单元格中的业绩给出对应的等级，划分规则是：

<60，等级为“F”。

60~69，等级为“E”。

70~79，等级为“D”。

80~89，等级为“C”。

90~99，等级为“B”。

>=100，等级为“A”。

下面咱们就简单汇总一下常用的解决方法和思路。

一、IF函数

=IF(A1>=100,"A",IF(A1>=90,"B",IF(A1>=80,"C",IF(A1>=70,"D",IF(A1>=60,"E","F")))))

通过IF函数嵌套，像剥洋葱一样逐层判断A1数值所在的区间，并返回对应的结果。

大于等于100，返回“A”，大于等于90，返回“B”，大于等于80，返回“C”……

这个公式的优点是易于理解，缺点是如果有多个判断条件，公式会变得越来越长了。

还有一个问题，使用IF函数进行多个区间的判断时，小伙伴们可以记住一个窍门，就是可以从最高的规则部分开始，逐级向下判断。也可以从最低的规则部分开始，逐级向上判断。

刚刚这个公式，就可以写成：

=IF(A1<60,"F",IF(A1<70,"E",IF(A1<80,"D",IF(A1<90,"C",IF(A1<100,"B","A")))))

如果你使用的是Office 2019或是Office 365，还可以使用IFS函数，让公式短一截：

=IFS(A1<60,"F",A1<70,"E",A1<80,"D",A1<90,"C",A1<100,"B",1,"A")

二、LOOKUP

=LOOKUP(A1,{0,60,70,80,90,100},{"F","E","D","C","B","A"})

这种写法是多区间判断并返回对应值的模式化公式，是IF函数逐层判断的升级版，比上面的公式略短一些。

注意，LOOKUP第二参数要升序处理{0,60,70,80,90,100}。

LOOKUP函数以A1为查找值，返回第二参数中查找这个数值，如果找不到，就以小于A1的最大数值进行匹配，并返回第三参数{"F","E","D","C","B","A"}中对应位置的字符串。

三、VLOOKUP

先在Excel中建立一个对照表：

然后使用VLOOKUP函数，在对照表中执行近似匹配的查询：

=VLOOKUP(A1,D2:E7,2)

公式够短吧？这里有两点需要注意：

1、是对照表中的首列使用升序排序；

2、是VLOOKUP函数省略第四参数，返回精确匹配值或近似匹配值。如果找不到精确匹配值，则返回小于待查询内容（A1）的最大值。

相对于其他公式，建立对照表的方法看似繁琐，但是在实际应用中更便于修改标准，而不必重新编辑公式。

建立对照表之后，使用下面的公式就更短了：

=LOOKUP(A1,D:E)

LOOKUP函数在D:E列的首列查找A1单元格的值，并返回与之对应的E列中的内容。当查找不到A1单元格的值时，也会以小于查询值的最大值进行匹配。

今天的内容就是这些，祝各位小伙伴们一天好心情！

图文制作：祝洪忠

作者 · 子书

哈喽~大家好！我是一只正在疯狂赶稿的可爱书~

说起计算日期间隔，可是愁坏了不少职场人士。

比如，需要计算两个日期之间相差多少年，或多少月，或多少天。

听说我的好同事昨天对着这份数据算了一个晚上，到最后还因为算错被老板训斥了一顿……

手动计算当然容易出错，但是使用 Datedif 函数，这个问题就可以轻松解决！

函数介绍

作用：计算两个日期之间相隔的天数、月数或年数。

Datedif 是 Excel 中的隐藏函数，功能非常强大。

PS：WPS 中这个函数不属于隐藏函数，直接在插入公式中就可以找到，但是在 Excel 当中，这个函数是属于隐藏函数，需要我们手动输入。

语法规则：

=DATEDIF(开始日期,结束日期,计算规则)

计算规则有六种，分别是：Y、M、D、MD、YM、YD。

❶ Y：计算两个日期相差的年数；

❷ M：计算两个日期相差的月数；

❸ D：计算两个日期相差的天数；

❹ MD：计算两个日期的同月间隔天数，忽略日期中的月份和年份。

❺ YM：计算两个日期的同年间隔月数，忽略日期中的天和年份。

❻ YD：计算两个日期的同年间隔天数，忽略日期中的年份。

好啦，现在了解了函数的使用规则，一起来看看案例展示吧！

案例展示

接下来我们只需要按照指定的计算规则输入函数就可以啦！

计算起始日期和截止日期相差的年数，参数三使用 Y 即可。

 操作步骤：

❶ 输入函数：

❷ 双击填充公式。

计算起始日期和截止日期相差的月数，参数三使用 M 即可。

 操作步骤：

❶ 输入函数：

❷ 双击填充公式。

计算起始日期和截止日期相差的天数，参数三使用 D 即可。

 操作步骤：

❶ 输入函数：

❷ 双击填充公式。

是不是感觉这个函数既简单又实用呢？

其实 Datedif 函数最重要的是记住计算规则，最常见的就是 Y、M、D 三种，根据工作中遇到的实际情况应用就好啦！

在评论区大声说出你的答案吧~

我们在使用Excel时，函数是最基本的知识，玩好函数就可以帮我们快速解决很多问题了！

在我们制作分析模板的时候，学会灵活地构造日期维度，是最基础的技能，更是必须掌握的技能。但是对于日期函数，大家都觉得看起来都很简单，用起来却需要有清晰的逻辑。

当你把所有的逻辑组合在一起时，你就能做出以下这样的智能对话提示，在模板中用于提醒、业务预警是非常好的！

上图中的“你好，今天是X年X月X日，星期X”，这个的实现相信大家都会，但是这里我给大家补充一个更快捷的方法，只用到2个函数就实现了！

="你好，今天是"&TEXT(TODAY(),"yyyy年mm月dd日 | aaaa")

当然，常用的一些日期函数，我们也是必须要掌握使用的：

year， month，day，date， today， eomonth，weekday  

以上的这些日期函数，都觉得很简单吧？我相信大家在单独地使用它们时，都可以玩得很熟练！

但是要玩转函数，关键的是函数的嵌套，需求的逻辑。

下面就以几个和日期相关的案例，看完你肯定会有深刻的感受～

1、求所在月的第1天

在我们的日报中，经常会看本月截止到昨天的销售额，所以我们要求出本月的第一天是哪一天，然后才方便后续借助sumifs等函数进行汇总计算。

① 最基本的思路

所在月的月份数：month(today())，所在月的年份数：year(today())，再用date函数，把年月日组合成标准日期：

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)

② 用今天的日期减去今天的天数再加1

例如今天是2019-11-17，用今天的日期再减去17，则得到2019-11-00（实际上没这样的显示），实际的结果则是2019-10-31，然后再加上1之后就是2019-11-01了

=TODAY()-DAY(TODAY())+1

③ 用eomonth算出上月的最后一天再加1

=EOMONTH(TODAY(),-1)+1

ps：很多人觉得eomonth不好理解，其实它就是 End of Month的缩写，表示一个月的最后一天

以上三种办法都可以实现同一个结果，①是最基本的思路；②③的方法其实思路是一样的，公式也更简洁，殊路同归！

2、求上周的第一天和最后一天

先来理解一下，“上周”是什么意思？

今天是2020年11月5日（周四），那么上周就是11.2（周一）～11.8（周日）这个星期；如果今天是2020年11月16日（周一），则上周就是11.9（周一）～11.15（周日）

同时我们是要结合today()去计算的（这样才是动态的），所以是必须用today()去减一个数！！！

那么应该减去多少呢？？

用上面的案例来尝试一下，若今天是2020年11月8日（周日），要求上一个周日就要减去7，今天是2020年11月9日（周一），要求上一个周日就要减去1。所以你就能发现：要求得上一个周日，是要减去当天的星期数！

所以「求上周日的日期」的公式就能写出来了：

=TODAY()-WEEKDAY(TODAY(),2)

接下来如果要求上一个周一，就用上面这个结果减去6即可得出！

看完这个案例，大家有没有真切地觉得：写公式其实就是写逻辑！

3、根据日期算出所在季度

Excel没有现成的季度函数，所以我们要想办法去梳理逻辑，才能把求季度的公式写出来！

季度，就是跟月份有关系，其中的关系是：

1、2、3月→1 季度，  4、5、6月→ 2 季度

7、8、9月→ 3 季度，  10、11、12月→ 4 季度

① 看着上面的关系，最笨的方法应该要有思路了，因为以上就是所有的关系了！

直接用choose穷举出来：

=CHOOSE(MONTH(A1),1,1,1,2,2,2,3,3,3,4,4,4)

意思就是，根据日期算出月份，然后如果月份是1，2，3的话，我都通过choose选择前3项，返回来都是1，这样就解决问题了！

方法太low？！其实不是，以上这种方式还很方便你去设置【财季】，有些不是自然季度的话，你就可以通过上面的公式来实现，例如假设每年的10月1日是财年的起始日，则你要算财季的话，可以这样用：

=CHOOSE(MONTH(A1),4,4,4,1,1,1,2,2,2,3,3,3)

因为只有12个月，所以穷举出来，就可以充分的自定义呀～

② 再换一个方向：1，2，3怎么能变成1？1就是1，不用想；2变成1，没思路；3变成1，3除以3。然后再想1/3，2/3怎么变成1？这里就还需要结合到roundup函数，可以把小数点向上进位的特点，就可以把零点几的小数也变成1了。

当然直接在Excel里进行推算，会更容易让你发现规律！

然后因为3个月为一个季度，正好后面的月份数，也是符合这样的规律，所以就可以写出公式：

=ROUNDUP(MONTH(A1)/3,0)

③ 还有没有其他解法？当然有！而且是最简短，最巧妙的公式写法！直接看答案：

=LEN(2^MONTH(A1))

例如=LEN(2^MONTH("2019-9-22"))，返回的结果就是3

解释：2的1～12次方结果值的长度，正好与对应季度数一样。

这种方法确实是非常巧妙，其实是源自于对数据的敏感，对数据的观察，然后爱钻研的精神！

好的，今天关于日期相关的公式就分享到这里，希望大家能明白到：

写公式就是写逻辑！

当你想写某公式没思路时，不妨先多花时间来整理你的需求，梳理清楚逻辑后，相信写起公式来就能得心应手了～

本文作者：赵骄阳&小爽

在我百般追着赵老师继续写 Frequency 函数的延伸做法的「高压」之下，今天他终于「熬」出来了！！

一群学生在排队，连续排在一起的相同性别的学生，最多不能超过 N 个人，不然就要重新调整位置；

 

在考核中，如果员工连续出现 N 次客户投诉，就需要对该员工进行警告；

 

在比赛场上，有时候需要看最大连胜场数，用来判断成员的发挥情况。

 

……

=IF(C3="胜",G3)

=IF(C3="败",G3)