Усреднение значений для данного месяца и года (Microsoft Excel)

У Джорджа есть рабочий лист, который включает даты (в столбце A) и значения, связанные с этими датами (в столбце B). Рабочий лист включает значения за последние несколько лет. Он хотел бы вычислить среднее значение всех значений для данного месяца в данном году. Например, Джордж хотел бы вычислить среднее значение всех значений за февраль 2020 года.

Есть несколько разных подходов к решению этой проблемы. Один из способов — создать сводную таблицу на основе ваших данных. (Сводные таблицы отлично подходят для агрегирования и анализа огромных объемов данных.) Вы можете легко установить в поле значения значение Среднее (вместо суммы по умолчанию) и сгруппировать столбец Даты по вашему желанию.

Если вы не хотите использовать сводную таблицу, вы можете добавить любое количество формул на свой рабочий лист. Например, следующая формула использует функцию СУММПРОИЗВ для вычисления среднего значения:

Формула предполагает, что ваши даты и значения начинаются со строки 2 (для заголовков) и не выходят за строку 1000. Если в данных нет дат, относящихся к февралю 2020 года, формула возвращает #DIV / 0! ошибка.

Другой подход — использовать формулу массива, например следующую:

Этот подход короче, чем формула СУММПРОИЗВ, но вы должны помнить, что при вводе формулы нужно удерживать нажатыми клавиши Ctrl + Shift + Enter.

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

Еще один подход — использовать одну из функций Excel, DAVERAGE. Все, что вам нужно сделать, это создать таблицу критериев, которая определит, что вы ищете. Предположим, например, что заголовки в столбцах являются чем-то оригинальным, например «Дата» (ячейка A1) и «Значение» (ячейка B1).

Вы можете настроить таблицу критериев в другом месте, например, D1: E2. Таблица может выглядеть так:

В таблице критериев указано, что вы хотите, чтобы DAVERAGE использовал все, в чем столбец Date содержит дату больше 31.01.20 и дату меньше 3/1/20. Вот формула:

Первый параметр определяет вашу базу данных, второй параметр указывает, что вы хотите усреднить информацию в столбце «Значение» (столбец B), а третий параметр сообщает DAVERAGE, где находится ваша таблица критериев.

Один довольно простой способ — применить фильтрацию дат и использовать функцию ПРОМЕЖУТОЧНЫЙ ИТОГ. Введите в ячейку следующую формулу:

Выберите ячейку в диапазоне данных и отфильтруйте данные (откройте вкладку «Данные» на ленте и щелкните инструмент «Фильтр»). Щелкните стрелку фильтрации вверху столбца A и выберите «Фильтры даты | Пользовательский фильтр из раскрывающегося списка. Excel отображает диалоговое окно «Пользовательский автофильтр».

(См. Рис. 1.)

Рисунок 1. Диалоговое окно Custom AutoFilter.

Используйте элементы управления в диалоговом окне, чтобы указать, что вам нужны записи больше 1/31/20 и меньше 3/1/20. Когда вы нажимаете ОК, отображаются только записи за февраль 2020 года, а формула промежуточных итогов показывает среднее значение этих видимых записей.

Однако, возможно, самый простой подход — использовать функцию СРЗНАЧЕСЛИМН. Он позволяет вам вычислять среднее значение некоторых значений в зависимости от того, соответствует ли связанное значение (в данном случае дата) указанным вами критериям. Вот как выглядит формула:

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

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (10671) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Averaging_Values_for_a_Given_Month_and_Year [Усредненные значения для данного месяца и года].