统计两个日期之间有多少个星期一在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值的数量，否则会出错。

赞 (0)

相关推荐