☰

计算给定日期范围内另一张表中日期重复 x 次的次数

tag-icon • tag-icon microsoft-excel worksheet-function
计算给定日期范围内另一张表中日期重复 x 次的次数

在一张表中，我有一组日期（在 A 列中）。每个日期条目都使用不同的变量值记录一次执行：

第 1 页：

05/03/2021
05/03/2021
05/03/2021

05/04/2021
05/04/2021

05/05/2021
05/05/2021
05/05/2021

05/06/2021
05/06/2021

05/07/2021

在另一张表中，我有 2 个命名单元格：from_date 和 to_date（用于报告范围）

第 2 页：

from_date = 05/03/2021
to_date = 05/07/2021

在 Sheet 2 中，我想要统计日期范围内执行 1、2、3、4 次等的所有次数。

例如，如果某项执行在 4 个不同的日期发生 3 次，则每天执行 3 次，总执行次数为 12 次。

预期输出：

| Executions |   Total    |
|   Per Day  | Executions |
|------------|------------|
|      1     |      1     |
|      2     |      4     |
|      3     |      6     |
|      4     |      0     |

答案1

这是一个有趣的问题，并且有很多方法可以做到这一点，所以我很有兴趣看看其他的答案。

方法 1 - 根据您的屏幕截图，可能不是您想要的格式

这是一个需要 Excel 2019 或 Excel 365 的公式。我将以下公式放入工作表中的单元格 A2 中：

=LET( xdates, Sheet2!A:A,
       fdates, FILTER( xdates, (xdates >= from_date)*(xdates <= to_date) ),
       udates, UNIQUE( fdates ),
       frq, FREQUENCY( fdates, udates ),
       bins, SORT( UNIQUE( FILTER( frq, frq > 0 ) ) ),
       ffrq, FREQUENCY( frq, bins ),
       IFERROR( INDEX( bins, SEQUENCE( ROWS( bins ) ), {1,2} ), FILTER( ffrq, ffrq > 0 ) ) )

我放在哪里日期，您将其放入包含执行日期的列中。这将生成一个动态数组，其中左列包含每日执行频率，右列包含总执行次数。

结果截图

此公式首先根据您的日期和日期过滤日期日期。然后创建一个唯一日期列表（更新) 从过滤结果中抽取。然后计算 fdates 与 udates 的频率频率并制作一个频率排序列表，称为垃圾箱然后计算这些区间的频率频率韋爾。

最后，通过在左列中使用 SEQUENCE 创建带有 bin 的索引来组装结果，以根据 bin 的数量动态调整表中的行数SEQUENCE( ROWS( bins ) )。它使用数组创建第二列，{1,2}该列将强制第二列出现错误 - 这是 IFERROR 介入并用FILTER( ffrq, ffrq > 0 )已被过滤以排除最终零频率行的 ffrq 替换错误的地方。

方法 2 - 这看起来更像是你的截图

这里有一个替代方案 - 如果您希望每天的执行次数从 1 迭代到最大频率，而不跳过任何出现次数为零的频率，您可以改用这个：

=LET( xdates, Sheet2!A:A,
       fdates, FILTER( xdates, (xdates >= from_date)*(xdates <= to_date) ),
       udates, UNIQUE( fdates ),
       frq, FREQUENCY( fdates, udates ),
       mxfrq, MAX( frq ),
       bins, SEQUENCE( mxfrq ),
       ffrq, FREQUENCY( frq, bins ) - ( bins = 1 ),
       IFERROR( INDEX( bins, bins, {1,2} ), INDEX( ffrq, bins ) ) )

注意：我发现此版本在每天 1 次的计数中添加了 1，因此我通过删除额外的计数来更正 ffrq。

从 1 迭代到最大箱数

方法 3 - 每日静态执行次数

此版本无法发现每天的执行情况。它依赖于您将它们放入 A 列（在此示例中）：

=LET( xdates, Sheet2!A2:A30,
      xPerDay, A2:A6,
       fdates, FILTER( xdates, (xdates >= from_date)*(xdates <= to_date) ),
       INDEX( FREQUENCY( COUNTIF( xdates, UNIQUE( fdates ) ), xPerDay), xPerDay, ) )

这大大简化了公式。就像之前将执行日期加载到日期（本例中为 Sheet2!A2:A30）。但这也将“每日执行次数”数组作为输入每日（在此示例中为 A2:A6）。

如果要延长“每日执行次数”，则需要延长 xPerDay。您可以在单元格 A2 中将“每日执行次数”设置为 =SEQUENCE( n )，并将 xPerDay 更改为 A2#，这样您就可以更轻松地更改“每日执行次数”的静态列表，而无需编辑 B2 中的公式。

静态频率的屏幕截图

相关内容