Функция Excel для агрегирования (средних) своевременных значений (каждую секунду) с интервалами в минуты / часы и т. Д.

Question

Я немного растерялся, пытаясь найти решение, похожее на агрегатную функцию R. Pivot-таблиц или функции группировки Excel не применяются.

Данные представляют собой серию измерений, проводимых почти каждую секунду в течение одного дня. В таблице есть столбцы для даты, времени и соответствующих значений измерений.

В лучшем случае значения должны быть сгруппированы для каждой минуты / часа. В итоге получается более компактная таблица с отметкой времени, уменьшенной до минут, и средним значением как совокупностью измеренных значений.

Есть идеи?

Answer 1

Мы хотим поместить данные в новую таблицу с правильным суммированием. Итак, без изменений существующих данных, мы создадим новую таблицу, скажем, на новом рабочем листе, со временем, которое нас интересует:

Time | Mean Value
12:00|
12:01|
12:02|
12:03|

... и т. д. Вы можете ввести первые два раза, а затем использовать маркер заполнения в правом нижнем углу выделения ячейки, чтобы скопировать их вниз, увеличивая на одну минуту на каждом шаге.

В столбце Среднее значение мы будем использовать функцию AVERAGEIFS для усреднения значений по критериям. Формула будет что-то вроде:
=AVERAGEIFS($B$2:$B$361,$A$2:$A$361,">=" & $D2,$A$2:$A$361, "<" & $D3)

... где B2:B361 - это диапазон, содержащий ваши значения, A2:A361 - это диапазон, содержащий время, D2 - это ячейка, содержащая время начала (в таблице выше это будет 12:00), а D3 - это ячейка. содержащий следующую метку времени (12:01 в таблице). Это усреднит все значения, где отметка времени больше или равна каждой минутной области, но меньше, чем следующая минута.

Вы можете использовать аналогичную структуру, чтобы свернуть минуты до часов.

Обратите внимание, что эта формула даст # DIV/0! ошибка, если нет значений, соответствующих периоду времени. Это можно исправить, включив всю формулу в функцию IFERROR , если это необходимо.