Excel 查询值的日期范围

Question

尝试查询 Excel 文档以确定某个值出现的次数，以便我可以添加条件格式和热图来确定某个日期内资源繁忙的次数。

我在单个单元格中有 Jan1-Dec31 我需要查询分为三列的数据，如下所示

       A       B       C
      Jan1    Jan12   Bob
      Jan2    Jan10   Roger
      Jan11   Jan14   Bob

该公式需要使用 A 列中的开始日期和 B 列中的结束日期，并计算 C 列中的资源名称出现次数。

因此我的输出将进入我的热图，如下所示。

        Bob  Roger
 Jan1    1    0

 Jan2    1    1

 Jan3    1    1

 Jan4    1    1

 Jan5    1    1

 Jan6    1    1

 Jan7    1    1

 Jan8    1    1

 Jan9    1    1

 Jan10   1    1

 Jan11   2    0

 Jan12   2    0

 Jan13   1    0

 Jan14   1    0

如果可以扩展而不必为每种资源制定 365 个公式，那就太好了。

Answer 1

我思考我理解你的问题是正确的。我的理解是，你正在寻找一种方法来扩展你的“热图”输入，但我可能理解错了。如果我错了，请告诉我。

这是 SUMPRODUCT 出色的经典案例。

=SUMPRODUCT(--($E2>=$A$2:$A$4)*($E2<=$B$2:$B$4)*(F$1=$C$2:$C$4))

您可以将其复制过来或复制下来（见图）。显然，您需要相应地编辑范围以适合您的实际数据。

它的工作原理如下。考虑 Bob 的 2016 年 1 月 1 日。它获取日期并将该日期与开始日期进行比较，并根据日期是否大于或等于开始日期数组中的开始日期创建一个 true/false 数组。然后它执行相同的操作，小于或等于结束日期数组中的结束日期。然后它检查 Bob 的资源数组。最后，您有三个数组：

{TRUE, FALSE, FALSE} * {TRUE, TRUE, TRUE} * {TRUE, FALSE, TRUE} -> {TRUE, FALSE, FALSE}。* 是 AND 运算符，因此，任何出现 TRUE、TRUE、TRUE 的地方都是 1，如果出现任何 FALSE，则为零。然后，它将您的 TRUE、FALSE、FALSE 数组强制转换为 1、0、0，并对结果求和！

编辑：这是解决我们在评论中讨论的问题的一种方法。