Как в Excel использовать сводную таблицу вместо формул

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

На рис. 103.1 показан набор данных, который содержит оценки студентов по тесту. Цель состоит в вычислении среднего балла для всех студентов, а также среднего балла для каждого пола.

Рис. 103.1. Нужно найти самый лучший способ вычислить средний балл по тесту для парней и девушек

Вставка промежуточных итогов

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

1. Выберите любую ячейку в столбце С.
2. Щелкните правой кнопкой мыши и в контекстном меню выберите Сортировка ► Сортировка от А до Я.
3. Выберите Данные ► Структура ► Промежуточный итог для открытия диалогового окна Промежуточные итоги.
4. В окне Промежуточные итоги выберите в списке При каждом изменении в пункт Пол, в списке Операция — Среднее, а в поле Добавить итоги по установите флажок Оценка.

Рис. 103.2. Excel добавляет промежуточные итоги автоматически

Использование формул

Другой способ вычисления средних значений заключается в использовании формул. Формула для расчета среднего для всех студентов простая: =СРЗНАЧ(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. Выберите любую ячейку из диапазона данных и выполните команду Вставка ► Таблицы ► Сводная таблица для открытия диалогового окна Создание сводной таблицы.
2. В диалоговом окне убедитесь, что Excel выбрал правильный диапазон данных, и укажите ячейку в текущем листе в качестве местоположения. Ячейка Е1 будет неплохим выбором.
3. Нажмите кнопку ОК, и Excel отобразит список полей сводной таблицы.
4. В списке полей перетащите пункт Пол в раздел Названия строк, в самый низ.
5. Перетащите пункт Оценка в раздел Значения. Excel создаст сводную таблицу, но отобразит функцию СУММ, а не СРЗНАЧ.
6. Чтобы изменить используемую итоговую функцию, щелкните правой кнопкой мыши на любом значении в сводной таблице и выберите Итоги по ► Среднее в контекстном меню (рис. 103.3).

Рис. 103.3. Эта сводная таблица вычисляет средние значения без использования формул

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

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

Чтобы получить быстрый доступ к функции, необходимо ввести знак равенства в ячейку (=) и выделить необходимую ячейку в сводной таблице. Excel сгенерирует функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ автоматически.

Сводная таблица с текстовыми полями в области значений

1. Выберите любую ячейку в столбце С.
2. Щелкните правой кнопкой мыши и в контекстном меню выберите Сортировка ► Сортировка от А до Я.
3. Выберите Данные ► Структура ► Промежуточный итог для открытия диалогового окна Промежуточные итоги.
4. В окне Промежуточные итоги выберите в списке При каждом изменении в пункт Пол, в списке Операция — Среднее, а в поле Добавить итоги по установите флажок Оценка.

Далее активировать любую ячейку (достаточно выбрать одно значение). Перейти в «Данные», сделать подсчет промежуточных результатов, используя параметр «Структура».

Сводная таблица Excel обеспечивает формирование итоговой информации различного вида без предварительной сортировки данных.

Источниками данных для формирования сводной таблицы являются:

• • список (БД) Excel;
• • внешний источник данных в любом конвертируемом формате (текстовый файл, содержащий табличные данные, реляционная БД);
• • несколько диапазонов ячеек одного или нескольких рабочих листов для одной или нескольких рабочих книг;
• • другая сводная таблица.

Построение сводной таблицы осуществляется с помощью Мастера сводных таблиц, вызываемого по команде Сводная таблица на панели Вставка.

1. Создать сводную таблицу, выводящую для каждого товара среднюю цену и суммарное количество. Создать 2 группы данных: для 2011 и 2012 годов (Произвести группировку по годам).

2. Создать сводную таблицу, выводящую для каждого товара среднюю цену с НДС (требуется предварительно создать вычисляемое поле — Цена с НДС). Создать группы данных для каждого Продавца.

Выполним 1 задание:

Для этого выберите Вставка -> Сводная таблица. На экране вы увидите Мастера сводных таблиц.

В верхней строчке выделите всю таблицу вместе с названиями полей ‘Исходная таблица’!$A$6:$G$57

У вас получиться разметка по месяцам, а нам нужно по годам.

Выберите первую ячейку с месяцем январь и зайдите в меню раздел Данные -> Группировать

Выполним 2 задание:

В исходной таблице построим новый столбец «Цена с НДС» вбейте формулу =E7*1,13 (цена * 1,13) и растяните по всем ячейкам.

Теперь также строим сводную таблицу как в задании 1, поэтому без картинок.

В Значения разместить (цена с НДС цена, цена с НДС) Сумма по полю цена с НДС, Количество по полю Цена, Среднее по полю цена с НДС

Слева на ячейке с Canon нажмите ПКМ (правой кнопкой мыши) и уберите галку с «Промежуточный итог продавец»

В итоге! Сводная таблица — это способ быстро собрать таблицу с промежуточными вычислениями.

Не забудьте отправить файл с вашим решением мне в электронный журнал или электронную почту. Иииии выполнить домашнее задание. Все в одной книге Excel выполнить нужно вам. Всем спасибо.

Далее активировать любую ячейку (достаточно выбрать одно значение). Перейти в «Данные», сделать подсчет промежуточных результатов, используя параметр «Структура».

Использование ссылок на поле сводной таблицы

• • список (БД) Excel;
• • внешний источник данных в любом конвертируемом формате (текстовый файл, содержащий табличные данные, реляционная БД);
• • несколько диапазонов ячеек одного или нескольких рабочих листов для одной или нескольких рабочих книг;
• • другая сводная таблица.

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