☰

如何计算平均睡觉和醒来的时间？

tag-icon • tag-icon microsoft-excel worksheet-function sleep
如何计算平均睡觉和醒来的时间？

我有一张包含两列的电子表格。A 列包含就寝时间（我上床睡觉的时间），B 列包含起床时间（我醒来的时间）。我该如何计算就寝时间和起床时间的平均值？

例如;

A = 晚上 11:45、晚上 11:00、凌晨 12:15、凌晨 12:45、凌晨 12:00

B = 早上 6:00、早上 5:00、早上 5:30、早上 7:00、早上 6:00

另外，如何计算两行之间的总睡眠时间？即使我转换为 24 小时格式，Excel 中的典型平均公式也会失效。任何想法都将不胜感激。

答案1

如果将日期和时间包括在内，则可以像普通数字一样简单地进行减法和平均。但是，数字格式对于数据正确显示非常重要。

例如：

在此处输入图片描述

单元格中的公式C2是=B2-A2单元格中的平均数公式A7是=平均（A2:A6）

自定义数字格式C 列：用途h“小时” mm“分钟”和AB 柱使用m/d/yyyy h:mm 上午/下午。 细胞A7和B7使用标准时间数字格式。

答案2

要准确地做到这一点而不必添加日期：

不使用日期来计算睡眠时间和平均值。

作为@巴里·胡迪尼在评论中提到，要使“睡眠时间”发挥作用，您需要使用，MOD因为时间“回溯”在 12 PM/24:00，这在电子表格世界中等于 1。要实现这一点：

C3：=mod(B3-A3,1)

一旦有了持续时间，平均值就很简单：

C2：=AVERAGE(C3:C7)

对于实际时间的平均值，您需要使用ARRAYFORMULA是否有可能任何一列中的时间需要“环绕”午夜（即可能一侧有一些值，另一侧有一些值）。为此，我们有：

A2：=ARRAYFORMULA(AVERAGE(if(A3:A7>0.5,A3:A7,A3:A7+1)))

上述公式只是取范围内的每个值，检查它是否超过 0.5（中午），如果它不是加 1 将其移至第二天，然后计算所有值的平均值。0.5 假设您中午前不睡觉，但如果您的作息时间不规律，您显然可以更改它。

对于平均醒来时间，上述内容对大多数人来说可能没有必要，因为一般来说，您醒来的时间是在一天的午夜和第二天的午夜之间，因此您的任何值都不需要“回绕”。话虽如此：

B2：=AVERAGE(B3:B7)

要准确地使用日期：

使用日期计算睡眠时间和平均值。

添加日期实际上只能掩盖计算平均值的问题，因为它同时使用日期和时间来计算平均值。它计算的是特定日期的时间，即所有日期和时间值的平均值。这会导致两个问题：

  1. 如果你错过任何一天的数据，你的平均值就会被完全抛弃
  2. 如果你有偶数天的数据，它会给你相反的 AM/PM 时间值

您真正想要平均的是TIMEVALUES，因此我们只需将其添加到上述公式中的每个范围中：

A2：=ARRAYFORMULA(AVERAGE(if(TIMEVALUE(A3:A7)>0.5,TIMEVALUE(A3:A7),TIMEVALUE(A3:A7)+1)))

B2：=ARRAYFORMULA(AVERAGE(TIMEVALUE(B3:B7)))

C2 不需要改变，因为它仍然只处理持续时间而不是日期。

相关内容