☰

如何在 Excel 中将时间段划分为相等的时间段

tag-icon • tag-icon microsoft-excel worksheet-function date-time
如何在 Excel 中将时间段划分为相等的时间段

我想找到一种方法将一段时间划分为 4 个小时时区。例如，如果一台设备在 09:00 至 17:00 之间不可用，即 8 小时，但我需要将其显示为 4 小时时段：00 - 04:00、04:00 - 08:00、08:00 - 12:00 等，这样设备在 08:00 - 12:00 的时间段内不可用 3 小时，在 12:00 - 16:00 的时间段内不可用 4 小时，在 16:00 - 20:00 的时间段内不可用 1 小时。设备可能在 24 小时以上的任何时间段不可用，目前我正在费力地手动划分时间，希望能有更简单的方法。

答案1

我决定回答这个问题，尽管你真的应该展示你所做的任何尝试以及它是如何设置的。

以下是我的设置。您将“发件人”和“收件人”分别放在 A 列和 B 列中，然后系统会将其拆分为所示的箱子。

在此处输入图片描述

在单元格 C4 中，公式为：

=IF($B4>$A4,
    IF(OR(AND($A4<C$2,$B4<C$2),AND($A4>C$3,$B4>C$3)),0,MIN($B4,C$3)-MAX($A4,C$2)),
    IF(OR(AND($B4<C$2,$A4<C$2),AND($B4>C$3,$A4>C$3)),(C$3-C$2),(C$3-C$2)+(MIN($B4,C$3)-MAX($A4,C$2)))
)

或者用细胞名称来表示的话，它会是这样的：

=IF(FROM>TO,
    IF(OR(AND(TO<BIN_FROM,FROM<BIN_FROM),AND(TO>BIN_TO,FROM>BIN_TO)),0,MIN(FROM,BIN_TO)-MAX(TO,BIN_FROM)),
    IF(OR(AND(FROM<BIN_FROM,TO<BIN_FROM),AND(FROM>BIN_TO,TO>BIN_TO)),(BIN_TO-BIN_FROM),(BIN_TO-BIN_FROM)+(MIN(FROM,BIN_TO)-MAX(TO,BIN_FROM)))
)

细分如下：

第一个 if 语句检查它是否在一个 24 小时内发生（To 在 from 之后），否则它必须转到第二天。

第二行检查两个时间是否超出了 bin 范围（要么都较小，要么都较大），如果是，则该 bin 中没有数据。如果都不在范围之外，则其中有数据。通过从 to 值的最小值中减去 from 值的最大值，可以得出 bin 中有多少时间。

第三行与第二行类似，但它计算的是不是在箱子里并从整个箱子宽度中减去这个值。

答案2

这里有一个解决方案，可以显示“时区”（箱）内的时间：

        24小时

（即，设备在 09:00 至 12:00、12:00 至 16:00 以及 16:00 至 17:00 之间不可用）。单元格中的公式D5为D7：

D5 = =IF(AND($B5>D$2,$B5<D$3), $B5, IF(AND($B5<D$2,$B6>D$2), D$2, ""))
D6 = =IF(AND($B6>D$2,$B6<D$3), $B6, IF(AND($B5<D$3,$B6>D$3), D$3, ""))
D7 = =IF(OR(D5="",D6=""), "", D6-D5)

解释：

  • =IF(AND($B5>D$2,$B5<D$3), $B5, …– 如果从时间（设备不可用的开始时间）在此范围内，则显示从时间。
  • … IF(AND($B5<D$2,$B6>D$2), D$2, …– 如果该 bin 的开始时间在设备不可用时间内，则显示该 bin 的开始时间。
  • … ""))– 否则为空白。

如上所示，这不能直接处理多日的设备不可用情况。但你可以通过明确设置到第二天的时间：

48小时

相关内容