快记笔记！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}写了一个公式，猜一猜公式计算的结果，应该是哪一个？

公式如下：

计算结果是哪个？

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

【Excel技巧】只会Vlookup函数out了！它是Excel中的万能查找函数，可惜90.9%的人没用过！

本文作者：十二

本文审核：小爽

本文编辑：小音、竺兰

查找入职年限大于 10 年或者销售额大于 8000 的员工信息，准备发奖励！（迟早有一天我也会有的）

作者：明镜在心

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

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

嗨！各位小伙伴们好呀！

在我们之前的一篇文章《 WPS 出了一个新功能，太好用啦！但我有更好的建议……》（文章链接见文末）中提到了一个数据处理的问题：

如下图：需要从「基础信息」中提取出「部门」。

如果分隔符是一样的话就好办了，用替换或者简单分列的方法会比较方便点。

但是这里的分隔符大部分都不一样，该怎么办呢？

之前我们介绍了两种非常好用的方法：

❶ 用 WPS 的【高级分列】功能；

 

❷ 用 PowerQuery 中的分列功能。

好用是好用，但是对于没有 WPS 或者 PowerQuery 的小伙伴们，那该怎么办呢？

其实有一种非常简单，且适用于各版本的方法，那就是使用 Word 通配符来协助处理这些数据！

下面就跟我一起来看看如何操作吧！

操作步骤

▋Step 01

将【A2：A13】复制粘贴到一个空白 Word 文档中，效果如下：

PS：在粘贴的时候选择 「只保留文本」 。

▋Step 02

按【Ctrl+H】调出【查找替换】对话框，在【查找内容】中输入：

在【替换为】中输入：

搜索选项中勾选【使用通配符】：

 代码解析：

 我们来总结一下知识要点：

WPS出了一个新功能，太好用啦！但我有更好的建议……

作者：拉登 Dony

来源：秋叶Excel

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

函数公式是 Excel 中最难的部分了。

倒不是因为 Excel 中有 400 多个函数公式，更多情况下出错的都是一些简单的函数公式。

公式没写错，却返回#NAME?错误。

数据没问题，求和结果却是 0。

公式直接照抄的，但是按下回车却不计算等等。

总之 Excel 总是出现各种奇奇怪怪的错误，让人无法静下心来学习。

今天给大家总结了 6 个新手常犯的函数公式错误，希望你的函数学习更加顺风顺水。

公式中包含中文符号

公式计算错误

Excel 函数公式中，所有的计算符号都必须是英文状态的。

如果输入中文的符号，则会出现#NAME?错误。

新手写公式时，很容易写成中文符号导致错误的有：

�� 错误演示：

比如下面的 IF 公式出现了#NAME?的错误，原因就是因为公式中的双引号，写成了中文符号。

把公式当中的引号改成英文双引号，就可以正常计算了。

�� 错误演示：

另外还有一些不太常见的中文符号，在排查公式错误的时候，也要注意。

比如下面的 COUNTIFS 函数为什么求和结果为 0？

排查了很久才发现，公式中的「等于号」写成了全角符号，导致公式无法进行大于等于的判断。

把等号改成半角等于号，公式就恢复正常了。

错误的等号
>＝650

正确的等号
>=650

单元格中的数字，求和为 0

使用 SUM 函数对销量进行求和，单元格里命名有数字，但是求和结果却是 0。

是什么原因呢？

不知道你注意到单元格左上角的【绿色小箭头】了没有？这表示单元格中的数字，被保存成了文本格式，所以导致 SUM 函数求和为 0。

�� 错误演示：

新手的做法，通常非常的低效。

❶ 设置单元格格式为【常规】。

❷ 然后双击一下单元格，公式才能正常计算。

❸ 重复第 2 步，反复双击编辑每个单元格。

高手的做法，是选择这些单元格，单击右上角的感叹号，选择【转换为数字】，就可以了。

公式不计算

显示的是函数公式

公式没有任何的错误，但是按下回车后并没有计算，显示的是原本的函数公式。

这类问题，通常是因为单元格被设置成了【文本】格式，所以单元格把公式直接显示出来了。

�� 错误演示：

用 WEEKNUM 函数公式计算周别，但是公式没有计算，显示的是原本的函数公式。

这类问题，无论是什么函数公式，只要单元格设置成了【文本】格式，都可以百分百复现。

新手的解决方法，通常也是一个一个修改格式：

❶ 设置单元格格式为【常规】。

❷ 然后双击一下单元格，公式才能正常计算。

❸ 重复第 2 步，反复双击编辑每个单元格。

高手会使用【分列】功能，一键让公式恢复计算：

❶ 选择所有公式列的数据。

❷ 在【数据】选项卡中，单击【分列】。

❸ 在弹出的对话框中，直接点击【完成】即可。

效果如下：

计算时忘记了有隐藏行

没有经验的新手，照搬公式的时候，眼睛看到的结果，和公式计算的结果不同，会让人晕头转向。

如果表格中有【隐藏的行/列】，就经常会出现这样的困扰。

�� 错误演示：

比如下面的表格中，SUM 函数求和结果为 67,762,900。

但是选择单元格后，右下角状态栏显示的却是 15,949,900。

错误的原因是 19 和 25 行之间，有隐藏的数据。

而右下角状态栏里的求和，通常是对可见单元格的求和。

把 19~25 行之间的数据取消隐藏，两个方法的求和结果就一致了。

公式出现了循环引用

公式中可以引用其他单元格的数据，但是通常不会引用公式当前所在单元格，否则就会出现循环引用。

�� 错误演示：

比如下面单元格上的蓝色箭头，这就是告诉我们，单元格出现了循环引用。

循环引用的意思就是：

A 的公式引用了 B，B 的公式又引用了 A，然后 A 又指向了 B，B 又指向了 A，一直循环计算下去。

这样会导致公式计算的死循环。

解决的方法很简单，双击编辑一下公式，修改公式引用的区域，排除掉当前公式所在单元格，就可以了。

公式中出现了#SPILL!错误

一般能出现#SPILL!错误的用户，都是 Office365 的版本。

#SPILL!是填充错误的意思，即公式计算出来有多个数据，需要自动向下填充，而下方单元格并不是空白单元格，导致填充错误。

�� 错误演示：

比如下面的表格中，E2 单元格使用 VLOOKUP 函数查询「部门4」对应的销量。

但是因为引用区域不正确，导致出现了#SPILL!错误。

公式如下：

正常情况下，VLOOKUP 的第 1 个参数是一个数值，而这里选择了 D:D 整列，所以计算结果是对 D 列每个单元格的查询，因此有大量的值。

而这些查询结果反馈时没有足够的空间填充，就出现了#SPILL!错误。

考考你：

公式中，经常出现一些陌生的符号，让人忍不住想爆粗口。

比如下面这个同学，在写公式的时候，会自动出现@这玩意，是什么原因，怎么取消？

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

先来看问题，要根据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单元格的值时，也会以小于查询值的最大值进行匹配。

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

图文制作：祝洪忠

数据源区域选择：$B$3:$B$6；

 

单元格链接：$B$8；

 

为了让【组合框】更有立体感可以勾选【三维阴影】。

虽然目前数据分析的工具很多，但据我在培训中了解到的情况，在日常的经营数据分析中，传统的数据透视表仍然是大多数人的主要工具，所以近期会再介绍一些数据透视表的相关内容。

在零售行业中，客单价是一个重要的指标，用来判断客人(订单)的平均成交金额，可以用于销售人员间的横向比较销售能力，也可以用于不同时间的纵向比较看趋势……所以我们必须要学会怎么去计算它，当然我们说的还是自动化报表的方向！

在零售行业，我们是这样去做客单价的数学算法：

客单价=销售额/订单数

很简单的算法，那么难点在哪里呢？看看我们从系统里导出来的数据源：

数据源分析：

① 销售额字段是现成的，生成透视表后，进行合计就行

② 订单数的字段没有，需要在数据源中增加该字段

所以难点是第②步，因为可以看到「订单号」这一列是有重复值的，怎么去计算订单数就是我们要攻克的问题。

处理方法：

当然我们要新增一个辅助列，接下来就是怎么根据「订单号」去算出「订单数」

① 首先我们可以统计一下【同一个订单号】出现的次数，用这样的公式就能实现：

这一列的结果果是指：Order-001订单号只出现了1次，Order-002订单号只出现了1次，Order-003订单号出现了4次，Order-004订单号出现了4次，Order-005订单号只出现了1次，Order-006订单号只出现了1次……

但这只是识别了重复项，如果生成透视表后，对于「只出现了1次」的订单号，是可以实现汇总统计；但是对于出现了1次以上的订单号，则汇总的结果就会多倍计算。

所以我们还要处理重复订单号的问题，希望在后续的透视表汇总中，重复订单号的订单数累加时，结果是1（就是说无论同一订单号重复了多少次，在求和时只算1次）

② 然后我们就用1除的办法去解决就ok了

可以看到，其实1除的意思就是要把1分成多少份？这样的话，在后续的透视表中，当你汇总累加时，即使是重复多次出现的同一订单号，对应的「辅助订单数」列的求和结果都是1，所以这就是我们要的结果！

③ 这时就可以生成数据透视表了，把「辅助订单数」、「销售额」放一起

经过核对，你可以发现S001门店是3张订单，S002是4张订单，S003是2张订单，这个订单数的结果正是我们想要的！

既然有了「订单数」、「销售额」，计算「客单价」就简单了！

④ 「客单价」=「销售额」／「订单数」，分子和分母都有了，所以我们直接进行字段间的计算即可：

通过以上操作，即可在透视表中新增一个「客单价」字段，接下来我们只需要把这个字段拉到透视表中，即可解决问题了！

再来回顾一下：

通过在数据源中构造函数辅助列，实现了重复项的正确汇总统计，数据源增加时，也很方便能实现动态扩展（此处建议把数据源变成表，这样函数也能动态扩展，自动化程度更高）；

处理好数据源后，在透视表中通过计算字段，生成了「客单价」字段。

这其实就是一个浓缩版的自动化报表思路，我们是希望：做好报表后，只需要刷新数据源，报表就自动地ok了！

高阶的自动化分析报表，可以全面立体地构造好分析逻辑，然后同样以自动化报表的思路，去制作一个综合分析的Excel模板出来！

  好，关于数据透视表的自动化客单价计算就到这里，希望能对大家的工作能带来一些效率的提升。

其实只要两个快捷键：【Ctrl+R】向下填充，【Ctrl+D】向右填充。

 操作步骤：

 操作步骤：

 先来看第一个图的制作步骤：

 接下来讲解第二个图：

到这里，两个图的制作方法就讲解完啦！

【Excel技巧】天天都在用的Excel分列，还藏着这个你不知道的神技？！

作者 | 农夫

大家好，我是农夫，专治疑难杂「数」的农夫~

昨天同事突然扔给了我这样一个数据~

让我把它分成下面这样的四列~

这还能难倒我这数据小能手吗？

废话不多说，盘它~

拿到这样的数据，大部分小伙伴脑袋里面率先想到的肯定是 Excel 的【分列】功能。

但是，实际操作发现，无论是使用【分隔符号】还是【固定宽度】都无能为力呀！

于是~

咳咳！说回正题，在正式处理这个数据之前，我们先来分析下当前数据有什么特征。

数据分析

我们观察一下数据，发现有以下几个特征：

首先，第一列红色列全都是数字，第二列蓝色列全都是文字。

Excel 中的分列功能需要一个标识符，比如空格。

那么，我们只要把数字和文字中间，插入一个标识符就可以分列了嘛！

其次，第二列蓝色列和第三列红色列和前面的特征一样，只不过是文字和数字换了位置；

最后，我们来看第三列红色列和第四列绿色列。我们发现两列都是数字。

但是，仔细分析就会发现第四列——绿色列都存在一个小数点，而小数点前面都是一位数字。

这样，我们不就可以，将小数点加前面的一位数字进行特征识别了吗？

好了，既然思路都有了，那么，接下来我们要怎么做呢？

数据处理

对于数据处理，我们可以使用 Word 通配符来帮忙。

比如：你要检索「农」字开头的单词，写入【农[一-龟]{1,}】，就可以匹配到农夫、农夫好帅、农民、农村、农业、农业经济、农村合作社、农田；

而如果写入【农*】，只会搜到农字这个单字。

这其实就是利用正则表达式实现的。

然而，正则表达式又是啥？

就相当于通配符的升华。

通俗理解是文本数据的超级匹配模式，用更简洁的方法来指定所需的文本。类似于一对多模式。

其中，一就是由元字符组成的通用模式；多则是文本中符合该模式的文本数据。

就好比：水果（你写的正则表达式）包括苹果、梨、香蕉、车厘子、柚子……（匹配上的文本数据。）

既然是处理文本数据，那我们就不能仅局限在 Excel 了，毕竟 Office 三剑客都各有各的看家本领。

其中，Word 在文本领域可是三剑客中的扛把子呀！

❶ Word 数据处理

① 先将数据粘贴进入 Word 中，操作过程中选择「选择性粘贴」-【只保留文本】。

② 序号与部门之间添加分隔符。

按【Ctrl+H】快捷键进入替换页面，在查找内容输入：【([0-9])([一-龟])】。

【([0-9])】：

选中 0 到 9 的任意一个数字，并定为一组。

【([一-龟])】：

选中文档中的任意一个汉字，并定为一组。

【([0-9])([一-龟])】：

只有前面是数字，后面是汉字才能被选中，同时将其分为前后两组~

在替换内容中输入：【\1|\2】

【\1】代表第一组[0-9]模式中查找到的任意一个数字；【\2】代表第二组[一-龟]模式中查找到的任意一个文字；并在两组数据间加入分隔符【|】。

之后，点击【更多>>】，然后勾选【使用通配符】，否则，会查找失败~

因为通配符是文本值中代替未知字符的特殊字符，可以方便查找基于特定模式匹配的数据。

以【*?】为例，勾选了通配符，会让 Word 认识到【*?】不再是普通字符，而是一种特殊匹配符号。

动图走一波~

③ 部门与工作量之间添加分隔符。

按【Ctrl+F】快捷键进入替换页面，在查找内容输入：【([一-龟])([0-9])】。

【([一-龟])】：

选中文档中的任意一个汉字，并定为一组。

【([0-9])】：

选中 0 到 9 的任意一个数字，并定为一组。

【([一-龟])([0-9])】：

只有前面是汉字，后面是数字才能被选中，同时将其分为前后两组~

在替换内容中输入：【\1|\2】

【\1】代表第一组[一-龟]模式中查到的任意一个文字，

【\2】代表第二组[0-9]模式中查到的任意一个数字，

并在两组数据间加入分隔符【|】。

④ 工作量与工作周期之间添加分隔符。

按【Ctrl+F】快捷键进入替换页面，在查找内容输入：【([0-9])([0-9])(.)】。

【([0-9])】：

选中 0 到 9 的任意一个数字，并定为一组。

【(.)】：

选中符号【.】，并定为一组。

【([0-9])([0-9])(.)】：

只有第一个是数字，第二个数字，第三个是点号才能被选中，同时将其分为前中后三组~

在替换内容中输入：【\1|\2\3】

【\1】代表第一组【[0-9]】模式中查到的任意一个数字；

【\2】代表第二组【[0-9]】模式中查到的任意一个数字；

【\3】代表第三组中的点号，并在第一和第二组间加入分隔符【|】。

以上设置好后，就可将数据粘回 Excel 中处理了~

▋Excel 数据处理

⑤ 用 Excel 进行分列。

分隔符添加完毕后，就可以轻松的使用【分列】功能进行数据分列了~

再用【分列】的【固定宽度】处理下列名就可以了~

最后我们就得到了这样的规范数据了~

知识总结

一定要牢记，拿到数据不要着急。

首先要分析数据特征；根椐数据特征使用相应的软件进行初步处理。

Word+Excel 处理：

PQ 处理：

只要在 E3 单元格中输入公式：

公式：

 公式解析：

 Find 函数基本语法：

 Filter 函数基本语法：

 公式套路：

 具体操作：

 动图如下：

最终，我们在 E3 单元格输入如下公式：

 效果如下：

作者 · 子书

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

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

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

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

手动计算当然容易出错，但是使用 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次方结果值的长度，正好与对应季度数一样。

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

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

写公式就是写逻辑！

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

【Excel技巧】核对两表数据，这招超简单，我不允许你还不会！

每天学点 Excel，工作效率 up~up~

哈喽大家好！

我是长小安，一名和秋叶一起学 Excel 多年、现已出道投稿的同学~

相信在工作中大家都遇到过核对两张表格数据的情况：

有两张结构相同的表格，只有个别数据不同，我们需要把不同之处找出来：

▲数据列数多，只展示一部分

据说昨晚下班前，老板要求新来的同事核对两表数据，这位同事辛辛苦苦工作了一晚上，结果......

Emmm，好吧，这不重要！

核对两表数据，小安这里有一个小妙招：利用【条件格式】突出显示两表中不相等的数据。

 操作步骤：

  

今天就教大家 4 种方法，给数据戴口罩！

 示例数据：

 操作方法：

 结果展示：

 示例数据：

 操作方法：

 结果展示：

 示例数据：

 操作方法：

 结果展示：

 示例数据：

 公式：

 结果展示：

华夫饼图，这样做超简单！

I never recommend the use of pie charts.（没错，书中原话）

Pie charts are evil.（没错，也是书中原话）

当单元格中的值大于  ( 1 - $A$2 ) * 100 时，

 

将单元格的填充颜色和字体颜色都填充为橙色。

【Excel技巧】学会这1招，数据录入不再愁，效率甩同事一条街！

作者 | 竺兰

小 Tips：省市区可以拆开登记~