Excel公式练习24：统计两个日期之间有多少个星期一在1日

 

本次的练习是：使用公式求两个指定日期之间日期为1日的星期一数，如图1所示。

 图1

 

先不看答案，自已动手试一试。

---

 

公式

在单元格C2中的公式：

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(C2& ':' & C3)),'aaaa d')='星期一 1'))

 

公式解析

公式中的：

ROW(INDIRECT(C2& ':' & C3)

创建一组表示日期的序列号，如图2所示。

 图2

要了解Excel表示日期的原理，详见《Excel提秘5：你看到的不一定是真实的——Excel是怎样表示日期和时间的》。INDIRECT将数据转换成引用，并作为ROW函数的参数生成一系列日期序列号数字。

 

TEXT(ROW(INDIRECT(C2& ':' & C3)),'aaaa d')

TEXT函数将表示日期序列号数字格式成为“星期几 几号”的格式，即显示星期几和数字，若是星期一且为1号则格式为“星期一 1”，如图3所示。

 图3

关于TEXT函数的详细使用详见《Excel函数学习32：TEXT函数》，如何自定义数字格式请参考《Excel揭秘2：自定义数字格式原理与应用》。

 

接着将TEXT函数格式化的数据与“星期一 1”比较，得到一个包含逻辑值TRUE和FALSE的数组，如图4所示。

 图4

 

使用双减号将TRUE转换成1，FALSE转换成0，如图5所示。

 图5

 

SUMPRODUCT函数将数组中的值相加，即得到结果。

 

小结

• SUMPRODUCT函数是一个数组函数，没有使用Ctrl+Shift+Enter键。

• 在Excel中日期是连续的数字。

• ROW函数生成数组。

• 双减号能够将逻辑值转换为数字。

• 不能使用COUNTIF函数来统计TRUE值的数量，否则会出错。

下期预告：

Excel公式练习25：生成在1至10之间且没有重复值的随机数

本次的练习是：使用公式生成位于两个值之间且没有重复值的随机数。如下图所示，生成在1至10之间且没有重复值的随机数。