Формула Excel для расчета среднего числа последовательных 24-часовых блоков?

Question

Microsoft Excel 2010, файл ввода тысяч строк почасовых данных, таким образом, существует 24 значения (строки) в день.

Давайте предположим, что мои данные начинаются с B1, и давайте предположим, что я поместил свое среднее значение для первого дня в C1. Содержимое ячейки C1 будет = СРЕДНЕЕ (A1:A24), пока все хорошо.

Вопрос в том, что мне нужно поместить в ячейку C2, чтобы содержимое ячейки C2 стало средним от A:25 до A:48 таким образом, чтобы я мог скопировать ту же формулу в C столбец, и он соответственно заполняет C3 как среднее от A:49 до A:72 и так далее?

Answer 1

Вариант подхода Excellll .....

Попробуйте эту формулу в C1 скопировать вниз

=AVERAGE(OFFSET(A$1,(ROWS(C$1:C1)-1)*24,0,24))

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

Вставьте эту формулу в C1 и скопируйте

="=AVERAGE(A"&(ROWS(C$1:C1)-1)*24+1&":A"&ROWS(C$1:C1)*24&")"

Это создаст все формулы в виде текста ...

Теперь преобразуйте их в реальные формулы, выполнив это

Выберите столбец> щелкните правой кнопкой мыши> Копировать> щелкните правой кнопкой мыши> Специальная вставка> Значения> ОК> Текст в столбцы> Готово

Answer 2

Если это может работать в вашем контексте, я бы подумал, что для этого нужна сводная таблица.

Предполагая, что ваши часы в правильном формате даты-времени Excel (если нет, создайте новый столбец и создайте формулу для преобразования его в дату-время):

• Выберите от A1 до B5000 (или, насколько вы думаете, вы в конечном итоге зайдете)
• Создайте сводную таблицу и поместите ее в столбец C
• Добавить часы в области меток строк
• Сгруппируйте его по дням (щелкните правой кнопкой мыши дату, выберите «Группировать», затем выберите «Дни»)
• Добавьте B-данные в область значений и установите среднее значение вместо суммы.

Это решение:

• прост в реализации
• Легко понять
• гибкий (можно перемещать, фильтровать, суммировать по месяцам и т. д.)
• безошибочный / беззаботный: если вы пропустили строку или каким-либо образом нарушаете смещение, он все равно будет правильно рассчитывать ваши среднесуточные значения на основе фактических данных, а не полагаться на предположения о количестве строк. Вы даже можете добавить дополнительный счетчик в область значений, чтобы подтвердить, что у вас есть 24 значения на каждый день.