☰

统计日期范围内的单元格 比较两个可变日期

tag-icon • tag-icon microsoft-excel worksheet-function
统计日期范围内的单元格 比较两个可变日期

为了简单起见，我尝试将两个可变日期之间的差异划分为几个时间范围类别。我将提供一些示例数据：

   A        B           C           D
1  Account# Name        Start Date  End Date   
2  24621    Customer1   11/17/21    11/17/2021
3  24621    Customer2   09/17/21    11/17/2021
4  24621    Customer3   06/17/21    11/17/2021
5  24621    Customer4   03/17/21    11/17/2021
6  24521    Customer5   11/17/20    11/17/2021
7  24321    Customer5   10/17/20    11/17/2021

然后，我想将开始日期和结束日期之间的差异统计到以下类别中（因为它们会出现在单独的表格中）：

Total   Time Period
1       < 1 Month
1       < 3 Months
1       < 6 Months
1       < 1 Year
2       >= 1 Year

我尝试过使用=SUMIFS(C2:C7, D2:D7, (D2-C2)<=31)和，=COUNTIF(C2:C7, (D2-C2)<=31)但两个返回值都是 0。减去日期会返回正确的天数，所以我觉得我应该能够计算出 0 到 31、32 到 93 等之间的值，而不需要添加辅助列来运行计数公式。

我还曾经=SUMPRODUCT(--(C:C<>D:D))-1计算过列出相同或不同日期的单元格的数量 - 我怀疑它是否适用于这个问题，但是我今天已经摆弄这些公式几个小时了，但没有任何进展。

编辑：用于故障排除帮助的额外信息。不得不对数据进行一些清理，但我相信所有需要的信息都在这里。右下角是我正在使用的公式；正如您所看到的，该公式适用于前 17 行，但将其应用于超过 18 行的任何内容似乎都会破坏它。我相当确定所有内容都正确转录，但我不熟悉这些公式（除了 Count），所以不确定是否有限制或我在某处有拼写错误。 excel 截图

答案1

您可以使用以下公式：

=XLOOKUP(DATEDIF(C2,D2,"M"),{0,3,6,12},{"< 1 Month","1-3 Months","3-6 Months","6 Months - 1 Year"},"> 1 Year",1)

在此处输入图片描述

DATEDIF 计算日期之间的月份。XLOOKUP 在第一个数组中查找每个月份或下一个最大项（由最后一个参数指示），然后从第二个数组返回相应位置。如果找不到日期之间的月份（即大于 12），则返回 XLOOKUP 中第四个参数指定的默认值。

编辑：

顺便说一下，您可能希望将第三个范围的定义更改为 4-6 个月。

编辑2：

阅读您编辑的帖子，您可以执行以下操作：

输入统计表的行标题，然后使用以下公式：

=COUNT(FILTER($A$2:$A$7,XLOOKUP(DATEDIF($C$2:$C$7,$D$2:$D$7,"M"),{0,3,6,12},{"< 1 Month","1-3 Months","4-6 Months","7 Months - 1 Year"},"> 1 Year",1)=$A12))

在此公式中，我们筛选 A 列（实际上可以是任何列），查找原始 XLOOKUP 公式（调整为使用 DATEDIF 中的范围）返回相邻单元格中的值的行。然后，我们计算筛选后的行数，以获得符合该条件的行数。

在此处输入图片描述

编辑3：

关于 A2：A18 的问题，问题出现的原因是第 18 行的开始日期（2021 年 11 月 7 日）晚于结束日期（2021 年 6 月 22 日）。

因此，DATEDIF 函数返回 #NUM！错误（见下面的单元格 G18）。因此，XLOOKUP 也返回 #NUM！错误（见下面的单元格 I18）。FILTER 也返回 #NUM！错误（单元格 L2）。出现零值是因为 COUNT 只计算数字。它不能计算文本或错误。因此它返回零（单元格 L6）。您将看到，如果您将其更改为使用 COUNTA 而不是 COUNT，它将返回 1，因为它计算了 1 个错误。

在此处输入图片描述

为了解决这个问题，您可以：

  1. 确保每一行的开始日期都晚于结束日期，或者
  2. 通过将 DATEDIF 包装在 IFERROR 调用中，让公式忽略错误行。

像这样：

IFERROR(DATEDIF(E2,F2,"M"),-1)

我们说的是，如果 DATEDIF 返回错误，则返回 -1 而不是错误。然后我们可以向 XLOOKUP 数组添加一个元素来处理 -1。

像这样：

XLOOKUP(G2,{-1,0,3,6,12},{"Error","< 1 Month","1-3 Months","3-6 Months","6 Months - 1 Year"},"> 1 Year",1)

如您所见，通过进行这些更改，第 18 行的 DATEDIF 返回值为 -1，XLOOKUP 的返回值为“Error”。

在此处输入图片描述

您的完整公式将是：

=COUNT(FILTER($A$2:$A$7,XLOOKUP(IFERROR(DATEDIF($C$2:$C$7,$D$2:$D$7,"M"),-1),{-1,0,3,6,12},{"Error","< 1 Month","1-3 Months","4-6 Months","7 Months - 1 Year"},"> 1 Year",1)=$A12))

相关内容