☰

在某个时间范围内对值求和

tag-icon • tag-icon microsoft-excel-2010
在某个时间范围内对值求和

在此处输入图片描述我有 550 行数据，代表每日航班时间。根据飞机类型，每次航班都需要一定数量的人员参加。这项任务需要 45 分钟。因此，在任何给定的飞行时间，都会有人忙于该航班以及之前 45 分钟内的任何航班。我想要获得任何飞行时间所需的总人数总数。我还想将 45 分钟作为输入变量，以查看此任务时间的变化如何影响每次飞行时间的人力需求。

以下是数据模型：

     A            B         C.        D
1   2:30          4        0:45       4
2   2:45          3.                  7
3   3:15          2.                  9
4   3:30          4.                  9
5   3:35          5.                 11

A 列包含航班时间。B 列包含该航班所需的人数。单元格 C1 包含任务时间的值。我想在 D 列中填写每次航班所需的员工总数。示例显示已填写的结果。

示例计算如下所示。以第 4 行为例。飞行时间为 3:30。基于 45 分钟的任务时间，2:45 或之后的所有航班都需要同时活跃的人员。有三个这样的航班，分别是 2:45、3:15 和 3:30。B 列中这些航班的人员配备分别为 3、2 和 4，因此 3:30 航班时的总人员配备需求为 9。这就是 D4 中的内容。

如果我将 C1 中的任务时间改为 30 分钟，则只有 3:15 和 3:30 的航班符合条件，因此 D4 中的总数将是 6。

我尝试过SUMIF和SUMIFS。我添加了一行数据（时间）减去，C1然后尝试对该时间范围内的所有内容求和B。我似乎无法获取B要添加数据的列。

我试过

    =sumif(A:A,"<=A4",B:B)-sumif(A:A,"<=A4-C1",B:B)

答案1

这是一个解决方案。这是 D1 的公式。输入它，然后向下复制以用于 D 中的其他单元格：

    =SUM(INDIRECT("b"&IF(ISNA(MATCH(A1-C$1,A$1:A1,0)),IF(ISNA(MATCH(A1-C$1,A$1:A1,1)),1,MATCH(A1-C$1,A$1:A1,1)+1),MATCH(A1-C$1,A$1:A1,0))):B1)

这基本上就是逻辑。它会找到符合条件的第一行，然后对该行和当前行之间的 B 值求和。找到第一行的方法是使用 MATCH 函数将 A 列中的时间减去 C1 中的时间与当前行之前行中的时间值进行比较。MATCH 没有按升序查找值的 GE，因此它使用 LE 和 EQ 的组合。

  • 如果没有 LT，则意味着第 1 行是 GE，所以这是第一行。
  • 如果某一行是 EQ，则该行就是起始行。
  • 如果一行是 LE 但不是 EQ，则意味着下一行是第一个 GE，因此这是第一行。

相关内容