Здравствуйте. Если у вас есть набор числовых данных, соответствующих каким-то датам (например, продажи по дням), часто приходится считать среднее значение для каждого месяца. У многих новичков эта работа перерастает в рутину многократного повторения одних и тех же действий. Сегодня я покажу два способа, как избежать подобной участи.

Вот пример таблицы, на которой мы будем практиковаться. Для упрощения формулы, я присвоил имена столбцам с датами («даты»), и со значениями («значения»).

Среднее по месяцам с помощью формулы

Первый способ – с помощью формулы. Действуем так:

  1. В подходящем месте заготавливаем таблицу с месяцами. Вместо названий месяцев запишите дату первого дня месяца:

  2. Выделите список дат (Е3:Е8), нажмите Ctrl+1 и выберите формат, как на рисунке ниже. Теперь даты выглядят, как названия месяцев

  3. Введите формулу расчёта среднего в первую строку таблицы и скопируйте в остальные строки:

    =СРЗНАЧЕСЛИМН(значения; даты; «>=»& E3; даты; «<=»& КОНМЕСЯЦА(E3;0))

Выполнив лишь три действия, мы получили корректные результаты. Как работает формула, которую мы использовали? Она основана на функции:

СРЗНАЧЕСЛИМН(диапазон_усреднения; диапазон_1; условие_1; диапазон_2; условие_2; … ; диапазон_n; условие_n)

Эта функция считает среднее арифметическое для тех значений, которые соответствуют перечисленным условиям.

Кроме того, мы применили функцию КОНМЕСЯЦА(дата; 0), чтобы получить дату последнего дня текущего месяца.

Если сложить всё это воедино, формула определяет среднее арифметическое значений, для которых даты больше или равны первому дню месяца, и меньше (либо равны) последнему дню месяца.

Среднее арифметическое с помощью сводных таблиц

Здесь вообще всё просто, ведь сводные таблицы берут максимум нашей работы на себя. Действуем так:

  1. Установите курсор в любую ячейку внутри исходного списка данных
  2. Кликните на ленте Вставка – Таблицы – Сводная таблица
  3. В открывшемся окне выберите место вставки сводной таблицы и нажмите ОК. Я помещу её прямо рядом с исходником

  4. В окне настройки таблицы перетяните мышью поле «Дата» в строки, а поле «Значение» — в Значения

  5. Кликните левой кнопкой мыши на надписи «Сумма по полю Значение». Выберите «Параметры полей значений», и в открывшемся меню – «Среднее»

  6. Последний шаг. Кликните правой кнопкой мыши по любой из дат в сводной таблице. В контекстном меню выберите «Группировать», установите шаг – «Месяцы». Нажмите ОК.

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