У меня есть таблица из двух столбцов. A содержит время сна (время, когда я ложился спать), а B содержит время бодрствования (время, когда я проснулся). Как я могу рассчитать среднее время сна, а также время бодрствования?
Например;
A = 11:45, 11:00, 12:15, 00:45, 12:00.
B = 6:00, 5:00, 5:30, 7:00, 6:00
Кроме того, как рассчитать общее время сна между рядами? Типичная средняя формула в Excel сбрасывается, даже если я конвертирую в 24-часовой формат. Любые идеи будут высоко ценится.
Чтобы сделать это точно без добавления даты:
Как упомянуто в комментарии @ barry-houdini , чтобы « Спящие часы» работали, вам нужно использовать MOD так как время "оборачивается" в 12:00 /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)
Чтобы сделать это точно с датами:
Добавление дат действительно только маскирует проблемы с вычислением средних значений, потому что оно использует и дату, и время для среднего. Это вычисление времени в определенный день, которое является средним значением всех значений даты и времени. Это приводит к двум проблемам:
То, что вы действительно пытаетесь усреднить, это TIMEVALUES , поэтому мы просто добавим это к каждому из диапазонов в формулах сверху:
A2: =ARRAYFORMULA(AVERAGE(if(TIMEVALUE(A3:A7)>0.5,TIMEVALUE(A3:A7),TIMEVALUE(A3:A7)+1)))
B2: =ARRAYFORMULA(AVERAGE(TIMEVALUE(B3:B7)))
C2 не нужно менять, так как он все еще имеет дело с продолжительностью, а не с датами.
Если вы включите дату со временем, вы можете просто вычесть и усреднить, как обычные числа. Форматирование чисел, однако, важно для правильного отображения данных.
Например:
Формула в ячейке C2 := B2-A2, а формула для средних значений в ячейке A7 := AVERAGE(A2:A6).
Пользовательское форматирование чисел для столбца C: использует h "часы" мм "min", а в столбцах AB используется m/d/ гггг h: мм AM/ PM. Ячейки A7 и B7 используют стандартный формат числа времени.
