Примеры работы со сводными таблицами в Excel

Работать со сводными таблицами Excel приходится в разных сферах. Можно быстро обрабатывать большие объемы информации, сравнивать, группировать данные. Это значительно облегчает труд менеджеров, продавцов, руководителей, маркетологов, социологов и т.д.

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

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

У нас есть тренировочная таблица с данными:

Тренировочная таблица.

Каждая строка дает нам исчерпывающую информацию об одной сделке:

  • в каком магазине были продажи;
  • какого товара и на какую сумму;
  • кто из продавцов постарался;
  • когда (число, месяц).

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

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

  1. Выберите «Файл»-«Параметры»-«Панель быстрого доступа».
  2. В выпадающем списке левой колонки: «Выбрать команду из» укажите «Все команды».
  3. В левой колонке найдите по алфавитному порядку и выделите: «Мастер сводных таблиц и диаграмм». Нажмите на кнопку между колонками: «Добавить» чтобы инструмент переместился в правую колонку и нажмите ОК.
Мастер в настройках.

Теперь инструмент находится в панели быстрого доступа, а значит всегда под рукой.

  1. Ставим курсор в любом месте таблицы с данными. Вызываем мастер сводных таблиц, нажимая на соответствующий инструмент, который теперь уже расположенный напанели быстрого доступа.
  2. На первом шаге выбираем источник данных для формирования сводной таблицы. Нажимаем «Далее». Чтобы собрать информацию в сводный отчет из нескольких листов, выбираем: «в списке или базе данных Microsoft Excel».
  3. Окно мастера.
  4. На втором шаге определяем диапазон данных, на основании которых будет строиться отчет. Так как у нас стоит курсор в таблице, диапазон обозначится автоматически.
  5. Определение диапазона.
  6. На третьем шаге Excel предлагает выбрать, куда поместить сводную таблицу. Жмем «Готово» и открывается макет.
  7. Сводный макет.
  8. Нужно обозначить поля для отображения в отчете. Допустим, мы хотим узнать суммы продаж по каждому продавцу. Ставим галочки – получаем:
Результат сводной таблицы.

Готовый отчет можно форматировать, изменять.

Как обновить данные в сводной таблице Excel?

Это можно сделать вручную и автоматически.

Вручную:

  1. Ставим курсор в любом месте сводной таблице. В результате становится видна вкладка «Работа со сводными таблицами».
  2. В меню «Данные» жмем на кнопку «Обновить» (или комбинацию клавиш ALT+F5).
  3. Обновление данных сводной таблицы.
  4. Если нужно обновить все отчеты в книге Excel, выбираем кнопку «Обновить все» (или комбинацию клавиш CTRL+ALT+F5).

Настройка автоматического обновления при изменении данных:

  1. На вкладке «Работа со сводными таблицами» (необходимо щелкнуть по отчету) выбираем меню «Параметры».
  2. Открываем «Дополнительные параметры сводной таблицы». Открывается мастер.
  3. В разделе «Данные» устанавливаем галочку напротив пункта «Обновить при открытии файла».
Параметры сводной таблицы.

Теперь каждый раз при открытии файла с измененными данными будет происходить автоматическое обновление сводной таблицы.

Некоторые секреты форматирования

Когда мы сводим в отчет большой объем данных, для выводов и принятия каких-то решения может понадобиться группировка. Допустим, нам нужно увидеть итоги за месяц или квартал.

Группировка по дате в сводной таблице Excel:

  1. Источник информации – отчет с данными.
  2. Так как нам нужна группировка по дате, выделяем любую ячейку с соответствующим значением. Щелкаем правой кнопкой мыши.
  3. Промежуточный итог.
  4. Из выпавшего меню выбираем «Группировку». Откроется инструмент вида:
  5. Группировка.
  6. В полях «Начиная с» и «По» Excel автоматически проставил начальную и конечную даты диапазона данных. Определяемся с шагом группировки. Для нашего примера – либо месяцы, либо кварталы. Остановимся на месяцах.
Результат промежуточных итогов.

Получаем отчет, в котором четко видны суммы продаж по месяцам. Поэкспериментируем и установим шаг – «Кварталы». Результат – сводная таблица вида:

Результат поквартальных итогов.

Если фамилия продавцов для анализа деятельности сети магазинов не важна, можем сформировать отчет с поквартальной прибылью.

Итоги поквартальной прибыли.

Чтобы убрать результаты группировки, необходимо щелкнуть по ячейке с данными правой кнопкой мыши и нажать разгруппировать. Либо выбрать данный параметр в меню «Структура».

Разгруппировать структуру.

Работа с итогами

У нас есть сводный отчет такого вида:

Исходная сводная таблица.

Видны итоги по месяцам (сделано «Группировкой») и по наименованиям товаров. Сделаем отчет более удобным для изучения.

Как в сводной таблице сделать итоги сверху:

  1. «Работа со сводными таблицами» - «Конструктор».
  2. На вкладке «Макет» нажимаем «Промежуточные итоги». Выбираем «Показывать все промежуточные итоги в заголовке группы».
  3. Показывать промежуточные итоги.
  4. Получается следующий вид отчета:
  5. Результат настройки итогов.

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

Как удалить промежуточные итоги? Просто на вкладке макет выбираем «Не показывать промежуточные суммы»:

Не показывать промежуточные суммы.

Получим отчет без дополнительных сумм:

Итоги без промежуточных сумм.

Детализация информации

Огромные сводные таблицы, которые составляются на основании «чужих» таблиц, периодически нуждаются в детализации. Мы не знаем, откуда взялась сумма в конкретной ячейке Excel. Но можно это выяснить, если разбить сводную таблицу на несколько листов.

  1. В марте продано двуспальных кроватей на сумму 23 780 у.е. Откуда взялась эта цифра. Выделяем ячейку с данной суммой и щелкаем правой кнопкой мыши и выбираем опцию:
  2. Показать детали.
  3. На новом листе откроется таблица с данными о продажах товара.
Данные о продажах товара.

Мы можем переместить всю сводную таблицу на новый лист, выбрав на вкладке «Действия» кнопку «Переместить».

Действия - переместить.

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

Список полей.

В нашем примере – ВСЕ товары, ВСЕ даты, ВСЕ суммы и магазины. Возможно, пользователю не нужны некоторые элементы. Они просто загромождают отчет и мешают сосредоточиться на главном. Уберем ненужные элементы.

  1. Нажимаем на стрелочку у названия столбца, где будем корректировать количество информации.
  2. Убираем ненужные элементы.
  3. Выбираем из выпадающего меню название поля. В нашем примере – это название товара или дата. Мы остановимся на названии.
  4. Выберите поле.
  5. Устанавливаем фильтр по значению. Исключим из отчета информацию по односпальным кроватям – уберем флажок напротив названия товара.
Фильтр по значению.

Жмем ОК – сводная таблица меняется.