用LEN函数与双重“Month”判断日期所属季度

编按：

Hello小伙伴们，今天是10月中的某一天，属于一年中的第四个季度。在日常工作中，根据日期计算季度，是很多小伙伴经常会遇到的一个问题。解决这个问题的方法也很多，比如使用IF函数、LOOKUP函数、VLOOKUP函数、CHOOSE函数、INT函数等等，都可以实现这种计算。今天，阿硕老师给大家分享两个计算季度的新方法。这两种方法，都是利用数据分布上的某种神奇的“巧合”来完成季度的计算，有点“无巧不成书”的意思。下面就让我们一起来学习吧！

 

方法一：LEN+乘幂法

 

如下图所示：A列中为日期，我们打算在B列中计算A列中日期对应的季度。先给出函数公式：在B2中输入“=LEN(2^MONTH(A2))”，然后下拉复制填充公式至B13，即可得到季度的值。

 

 

下面我们分步骤来看一下这个公式是怎样完成季度计算的？

 

刚才我们所写的函数公式，最内层的函数是MONTH函数，它的作用是提取A列中日期对应的月份值。所以，我们首先在C2中输入“=MONTH(A2)”，然后下拉复制填充公式至C13，得到的结果如下图所示。

 

 

接下来，让我们来写一个乘幂运算的公式。在D2中输入“=2^C2”，然后下拉复制填充公式至D13，得到的结果如下图所示。

 

 

关于乘幂运算，在这里着重给大家讲一下。

（1）乘幂运算的符号是“^”（和数字6在同一个键盘上面哦~）；

（2）乘幂运算的两个参数分别是底数和幂次数，位于“^”前面的是底数，位于“^”后面的是幂次数；

（3）乘幂运算的意义，就是计算底数的N次方（N即为幂次数），也就是N个底数相乘；

（4）综上，我们刚才输入的公式“=2^C2”，它就是计算以2为底、以月份值为幂次数的乘幂运算，也就是计算2的N次方。

 

由上图可见，C2:C13中的数据依次为1、2、3、……、12，所以，D列中的乘幂运算就是依次计算2的1次方、2的2次方、2的3次方……2的12次方，所以，得到的结果就依次为2、4、8……4096。

 

这时，请大家注意观察。有一个非常神奇的“巧合”出现了，如下图所示。

 

 

可以看到，对于月份值为1、2、3的数据，在乘幂运算后，得到的结果分别为2、4、8，这三个数都是1位数；对于月份值为4、5、6的数据，在乘幂运算后，得到的结果分别为16、32、64，这三个数都是2位数；对于月份值为7、8、9的数据，在乘幂运算后，得到的结果分别为128、256、512，这三个数都是3位数；对于月份值为10、11、12的数据，在乘幂运算后，得到的结果分别为1024、2048、4096，这三个数都是4位数。

 

因此，从季度的角度来观察，我们可以发现一个规律：1季度、2季度、3季度、4季度对应的乘幂运算的结果，分别为1位数、2位数、3位数、4位数。

 

所以，接下来，我们只需计算一下D列中的数据是几位数，也就是数据的长度，就可以用这个数据长度来替代季度值了。计算长度的话，我们使用LEN函数。这个函数也比较简单，我们只要在E2中输入 “=LEN(D2)”，就OK了。输入LEN函数公式之后，我们下拉复制填充公式至E13，得到的结果如下图所示。

 

 

大家感受一下，这种方法，是不是很巧妙呢？

 

方法二：双重MONTH法

 

小伙们，如果你还沉浸在刚才那个公式中的话，那么请跳出刚才的思维，让我们再来研究一个新的“巧合”。我们延用上一种方法中的数据进行操作，A列中依旧是刚才那组日期，我们还是在B列中计算季度。现在，我们在B2中输入“=MONTH(MONTH(A2)*10)”，然后下拉复制填充公式至B13，即可得到季度的值。

 

下面我们同样分步骤来看一下这个公式是怎么完成季度计算的。

 

这个公式的最内层函数也是MONTH函数，同样地，我们在C2中输入“=MONTH(A2)”，然后将公式下拉复制填充至C13，就可以把月份值提取出来 ，得到的结果如下图所示。

 

 

接下来，我们将刚才提取出的月份值，乘以10。这个就比较简单啦！我们只需要在D2中输入“=C2*10”，然后下拉复制填充公式至D13即可，得到的结果如下图所示。

 

 

可以看到，D2:D13中的数据是10、20、30……120这样一组以10为单位递增的10的倍数。接下来，我们对D2:D13中的数据进行一下数据格式的修改。我们将这些数据修改为日期格式，修改后得到的结果如下图所示。

 

 

此时，原来的10、20、30……120，就变成了1900/1/1/10、1900/1/20、1900/1/30……1900/4/29这样的日期。这组数据由整数变为日期，其中的逻辑是什么呢？我们现在也来给小伙伴们讲一下。

 

这是因为日期在Excel中的本质是数值，在Excel中的最小的日期是1900/1/1，它对应的数值是1，此后，日期每增加1天，它对应的数值也增加1。反过来说，数值1对应的是日期1900/1/1，此后数值每增加1，日期也增加1天。所以在本例中，D2中的数值10，变成了1900/1/10，D3中的数值20，变成了1900/1/20，D4中的数值30，变成了1900/1/30…… D13中的120，变成了1900/4/29。具体的对应关系，如下图所示。

 

 

在理解了数值和日期的对应关系之后，我们再来看一下D列中的日期和季度有什么样的“巧合”存在？细心的小伙伴们可能已经发现了，月份值为1、2、3的日期，在D列中对应的日期值均出现在1900年1月份，月份值为4、5、6的日期，在D列中对应的日期值均出现在1900年2月份，月份值为7、8、9的日期，在D列中对应的日期值均出现在1900年3月份，月份值为10、11、12的日期，在D列中对应的日期值均出现在1900年4月份。

 

因此，从季度的角度来观察，我们可以发现一个新的规律：1季度、2季度、3季度、4季度在D列中对应的月份值，刚好为1、2、3、4。

 

于是，我们再次通过MONTN函数来提取一下D列中日期的月份值，而这个月份值，刚好就是我们要计算的季度值了。好了，话不多说，现在我们在E2中输入“=MONTH(D2)”，然后下拉复制填充公式到E13，即可得到季度值，如下图所示。

 

 

好了，各位亲爱的小伙伴，今天的学习就到这里，你学会了吗？