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

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

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

=SUMPRODUCT((MONTH(A2:A1000)=5)(YEAR(A2:A1000)=2011)(B2:B1000)) / (SUMPRODUCT((MONTH(A2:A1000)=5)(YEAR(A2:A1000)=2011)1))

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

ошибка.

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

=AVERAGE(IF((MONTH(A2:A1000)=5)*(YEAR(A2:A1000)=2011),B2:B1000))

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

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

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

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

Date         Date >4/30/11     <6/1/11

В таблице критериев указано, что вы хотите, чтобы DAVERAGE использовал все, что в столбце «Дата» содержит дату более 30 апреля 2011 г. и дату менее 01.06.11. Вот формула:

=DAVERAGE(A1:B1000,"Value",D1:E2)

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

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

=SUBTOTAL(101,B2:B1000)

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

image

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

Используйте элементы управления в диалоговом окне, чтобы указать, что вам нужны записи более 4/30/11 и менее 6/1/11. Когда вы нажимаете OK, отображаются только записи за май 2011 г., а формула промежуточных итогов показывает среднее значение этих видимых записей.

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

Этот совет (10670) применим к Microsoft Excel 97, 2000, 2002 и 2003.

Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

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