☰

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

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
Формула Excel для расчета среднего значения последовательных 24-часовых блоков?

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

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

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

решение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&")"

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

Теперь преобразуйте их в реальные формулы, выполнив следующее:

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

решение2

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

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

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

Это решение:

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

Связанный контент