Сводные таблицы Excel представляют собой невероятно мощную функцию, и вы часто можете создавать их вместо формул. В этой статье описывается простая задача, и для нее предлагаются три различных решения.
На рис. 103.1 показан набор данных, который содержит оценки студентов по тесту. Цель состоит в вычислении среднего балла для всех студентов, а также среднего балла для каждого пола.
Первое решение включает в себя автоматическую вставку промежуточных итогов. Чтобы можно было использовать этот метод, данные должны быть отсортированы по столбцу, который будет вызывать промежуточные итоги. В данном случае вам нужно сортировать данные в столбце Пол. Выполните следующие действия.
Результат добавления промежуточных итогов показан на рис. 103.2. Обратите внимание, что Excel также создает контур, так что вы можете скрыть подробности и просматривать только итоги. Формулы, добавленные Excel, используют функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ со значением 1 в качестве первого аргумента (1 обозначает среднее). Вот эти формулы:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;B2:B13)
=ПРОМЕЖУТОЧНЫЕ.ИТ0ГИ(1;B15:B28)
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;B2:B28)
Формула в ячейке В30
вычисляет общее среднее и использует диапазон, который включают в себя другие две формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ в ячейках В14
и В29
. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ игнорирует ячейки, которые содержат другие формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Другой способ вычисления средних значений заключается в использовании формул. Формула для расчета среднего для всех студентов простая: =СРЗНАЧ(B2:B27)
.
Чтобы узнать среднее значение в зависимости от пола, можете воспользоваться функцией СРЗНАЧЕСЛИ для создания следующих формул:
=СРЗНАЧЕСЛИ(C2:C27;"Женский";B2:B27)
=СРЗНАЧЕСЛИ(C2:C27;"Мужской";B2:B27)
Функция СРЗНАЧЕСЛИ была введена в Excel 2007. Если вам нужно, чтобы ваша книга была совместима с более ранними версиями, используйте эти формулы:
=СУММЕСЛИ(C2:C27;"Женский";B2:B27)/СЧЕТЕСЛИ(C2:C27;"Женский")
=СУММЕСЛИ(C2:C27;"Мужской";B2:B27)/СЧЕТЕСЛИ(C2:C27;"Мужской")
Третий метод усреднения баллов состоит в создании сводной таблицы. Многие пользователи избегают создания сводных таблиц, поскольку считают эту функци.ю слишком сложной. Однако, как вы сможете видеть, она проста в использовании.
Е1
будет неплохим выбором.В отличие от решения на базе формул, сводная таблица не обновляется автоматически при изменениях данных. Если данные изменились, вам нужно обновить сводную таблицу. Просто щелкните правой кнопкой мыши на любой ячейке сводной таблицы и выберите в контекстном меню пункт Обновить.
Сводная таблица в этом примере очень проста, и се было также очень легко создавать. Сводные таблицы могут быть намного более сложными, обобщая большие объемы данных практически любым образом, о котором вы только могли бы подумать, и без использования формул.