Как рассчитать среднее значение по дням, месяцам, кварталам или часам с помощью сводной таблицы в Excel?

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

1. Выделите всю исходную таблицу данных (включая заголовки), затем перейдите на вкладку Вставка > Сводная таблица.

2. В появившемся диалоговом окне «Создание сводной таблицы» выберите Существующий лист, если хотите разместить сводную таблицу на текущем листе. Укажите Расположение, щёлкнув по ячейке, где должна появиться сводная таблица, и нажмите ОК.
Примечание: чтобы разместить сводную таблицу на новом листе, выберите опцию Новый лист. Убедитесь, что указанное расположение не пересекается с существующими данными — это поможет избежать предупреждений о перезаписи.

3. В области «Список полей сводной таблицы» (обычно отображается справа) перетащите столбец с датой/временем в область Строки, а столбец с числовыми данными ()Сумма) — в область Значения. Эта начальная настройка агрегирует данные по каждой записанной метке времени.

4. Чтобы структурировать результаты по конкретным периодам, щелкните правой кнопкой мыши любую дату в сводной таблице и выберите в контекстном меню пункт Группировать. Эта функция объединяет данные в удобные интервалы — дни, месяцы, кварталы или даже часы.

5. В диалоговом окне «Группировка» выберите нужный период группировки, установив флажок в поле По(например,)Месяцы). Нажмите ОК, чтобы применить настройки. Затем щелкните правой кнопкой мыши значение Сумма по полю Сумма, выберите Итоги по > Среднее. Теперь сводная таблица отображает среднее значение для каждой временной группы — это упрощает сравнение и анализ.

Если средние значения по периодам нужно обновлять при добавлении новых данных, просто обновите сводную таблицу через вкладку Анализ > Обновить. В итоге сводная таблица обеспечивает эффективный и наглядный способ вычисления и просмотра средних значений за заданные временные интервалы, однако может потребовать ручной настройки при изменении структуры группировки или несогласованности формата данных.

Если вам часто приходится рассчитывать средние значения для конкретных периодов — таких как дни, недели, месяцы или годы, особенно на основе детализированных почасовых данных, — ручная группировка и вычисления быстро становятся утомительными и чреватыми ошибками. Kutools для Excel предлагает специализированные инструменты, которые значительно упрощают этот процесс: функции Преобразовать в фактические значения и Расширенное объединение строк позволяют мгновенно форматировать даты и выполнять пакетную агрегацию, экономя вам массу времени.

1. Выделите ячейки, содержащие дату и время, и отформатируйте их под нужный период. Например, чтобы получить среднесуточные значения, выделите данные и перейдите в раздел Главная > Числовой формат > Краткая дата. Эта функция «Преобразование времени» преобразует метки времени в значения только даты.

Примечание: чтобы усреднить данные по неделям, месяцам или годам, Kutools для Excel предлагает функции Применить формат даты и Преобразовать в фактические значения, которые всего за несколько кликов преобразуют метки времени в нужные форматы. Это обеспечивает согласованную группировку и точные вычисления.

Получите бесплатную пробную версию с полным функционалом на 30 дней прямо сейчас!

2. Выделите весь набор данных (включая отформатированные даты и значения), затем на ленте Excel выберите Kutools > Содержимое > Расширенное объединение строк.

3. В открывшемся диалоговом окне выберите в списке столбец с датой/временем, отметьте его как Первичный ключ, затем выберите столбец со значениями (например, «Сумма») и настройте для него вычисление: Рассчитать > Среднее. Нажмите кнопку ОК — и Kutools немедленно рассчитает средние значения для каждой уникальной даты.

Средние значения для указанных временных периодов рассчитываются мгновенно, что упрощает анализ. Если группировка дат отображает месяцы или годы вместо дней, результаты автоматически агрегируются соответствующим образом. Вы также можете переформатировать даты с помощью команды Kutools>Формат>Применить формат даты, а затем завершить операцию с помощью команды Kutools>Преобразовать в фактические значения.

Для пользователей, предпочитающих прямой расчёт по формулам без использования Сводная таблица или надстроек, встроенные функции Excel — такие как СРЗНАЧЕСЛИМН, СУММЕСЛИМН и СЧЁТЕСЛИМН — обеспечивают гибкий подход на уровне отдельных ячеек для вычисления средних значений за конкретные периоды. Этот метод идеален, когда нужны индивидуальные расчёты, требуется избежать обновления сводных таблиц или важно разместить результаты непосредственно рядом с исходными данными.

Ниже приведен пример использования формул для вычисления средних значений по каждому дню:

1. Предположим, что ваши данные содержат даты в столбце A (A2:A100) и числовые значения в столбце B (B2:B100). В новом столбце (например, в ячейке C2) введите следующую формулу, чтобы рассчитать среднее значение для определённого дня (например, для даты в ячейке A2):

=AVERAGEIFS(B$2:B$100, A$2:A$100, A2)

2. Нажмите клавишу Enter, чтобы применить формулу. Чтобы рассчитать значения для всех дат, скопируйте формулу вниз по столбцу рядом с вашими данными.
Совет: если вы хотите, чтобы среднесуточные значения отображались только один раз для каждой уникальной даты, сначала отсортируйте данные или оставьте только уникальные даты, а затем примените формулу соответствующим образом.

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

1. Чтобы начать, откройте редактор VBA, нажав Инструменты разработчика > Visual Basic. Когда появится окно Microsoft Visual Basic для приложений, щелкните Вставка > Модуль и скопируйте следующий код в модуль:

Sub AverageByPeriod()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim periodCol As String, valueCol As String
    Dim dict As Object
    Dim cell As Range
    Dim periodKey As String
    Dim i As Long, lastRow As Long
    Dim sumDict As Object, countDict As Object
    
    Set ws = ActiveSheet
    periodCol = "A" ' Date/Time column
    valueCol = "B" ' Value column
    lastRow = ws.Cells(ws.Rows.Count, periodCol).End(xlUp).Row
    
    Set dict = CreateObject("Scripting.Dictionary")
    Set sumDict = CreateObject("Scripting.Dictionary")
    Set countDict = CreateObject("Scripting.Dictionary")
    
    For i = 2 To lastRow
        ' Grouping by month example; change to format for day/hour/quarter if needed
        periodKey = Format(ws.Cells(i, periodCol).Value, "yyyy-mm")
        
        If Not dict.Exists(periodKey) Then
            dict.Add periodKey, dict.Count + 1
            sumDict.Add periodKey, ws.Cells(i, valueCol).Value
            countDict.Add periodKey, 1
        Else
            sumDict(periodKey) = sumDict(periodKey) + ws.Cells(i, valueCol).Value
            countDict(periodKey) = countDict(periodKey) + 1
        End If
    Next i
    
    ws.Cells(1, 4).Value = "Period"
    ws.Cells(1, 5).Value = "Average"
    
    i = 2
    Dim k As Variant
    For Each k In dict.Keys
        ws.Cells(i, 4).Value = k
        ws.Cells(i, 5).Value = sumDict(k) / countDict(k)
        i = i + 1
    Next k
End Sub

2. После вставки кода нажмите кнопку , чтобы запустить макрос. Макрос прочитает ваши данные (из столбцов A и B, начиная со строки 2), сгруппирует их по выбранному периоду (в данный момент установлено «месяц») и выведет среднее значение для каждой группы в столбцах D и E.

Советы:

• Чтобы сгруппировать по дням, измените строку Формат(...; "гггг-мм-дд").
• Для группировки по кварталам используйте:periodKey = "Q" & WorksheetFunction.RoundUp(Month(ws.Cells(i, periodCol).Value) /3,0) & "-" & Year(ws.Cells(i, periodCol).Value)
• Всегда проверяйте, чтобы ваши столбцы ()periodCol, valueCol) соответствовали структуре ваших данных.

Меры предосторожности:

• Если возникает ошибка или пустые результаты, убедитесь, что в столбце группировки отсутствуют пустые ячейки или значения, не являющиеся датами.
• При необходимости скорректируйте назначение столбцов: если ваши данные начинаются не со столбцов A и B, обновите значения periodCol и valueCol соответственно.
• Обязательно сохраните свою работу перед запуском макросов, чтобы избежать случайных изменений данных.

Среднее значение по году/месяцу/дате в Excel

Среднее значение временных меток по дням в Excel

Среднее значение по неделям в Excel