Сводные таблицы

За 10 минут я научу вас пользоваться сводными таблицами Excel
Кем бы вы ни были: начальником отдела продаж, маркетологом, аналитиком, руководителем компании т .д.

Если вы создаёте отчёты в Excel и не пользуетесь сводными таблицами. Вы поступаете плохо.
1
Что такое сводные таблицы Microsoft Excel
Сводные таблицы MS Excel это встроенный в Excel инструмент позволяющий обрабатывать и обобщать табличные данные.

Например
У вас есть огромная таблица с продажами за 10 лет в которой есть всего лишь 4 столбца:
  1. Дата сделки
  2. ФИО менеджера
  3. Тип клиента
  4. Сумма сделки

Благодаря сводным таблицам вы сможете за 2-3 минуты получить следующие обобщённые отчёты:
  1. Сумма продаж по каждому менеджеру
  2. Сумма продаж по типам клиентов
  3. Сумма продаж за каждый месяц
  4. Заработная плата менеджеров в зависимости от объёма продаж
  5. и т.д. и т.п.

Т.е. за 2-3 минуты вы можете переварить до миллиона строк и получить отчёт в нужном разрезе.


Пошаговый алгоритм работы со сводными таблицами
Рассмотрим пример создания сводных таблиц для анализа работы отдела продаж.

Дано
Таблица с итогами работы отдела продаж за 4 месяца 2017 года. Скачать
  1. Дата сделки
  2. ФИО менеджера
  3. Тип клиента
  4. Сумма сделок

Задача

Сформировать следующие отчёты.
  1. Сумма продаж по каждому менеджеру
  2. Сумма продаж по типам клиентов
  3. Заработная плата менеджеров в зависимости от объёма продаж
1. Откройте таблицу
1. Откройте вашу таблицу в MS Excel.
2. Выделите всю таблицу находящуюся на листе Лист1. Нажав сочетание клавиш Ctr+A.
3. Выберите пункт Вставка, далее кнопку Сводная таблица. В появившемся диалоговом окне нажмите кнопку ОК.

В итоге появится новый лист на котором мы будем работать со сводными таблицами. При этом Лист1 будет являться источником данных для наших сводных таблиц.
2. Формируем нашу первую сводную таблицу
А теперь сразу в бой. Попробуем создать нашу первую сводную таблицу.
Сумма продаж по каждому менеджеру.

1. Для этого перетащите Поле Менеджер в квадрат Строки, а поле Сумма в квадрат Значения.

О чудо мы видим объём продаж по каждому менеджеру. Теперь для того чтобы таблица была более информативной. Проделаем следующее.

2. Выделите столбец В, Сумма по полю Сумма и отформатируйте его как Денежный.
3. Нажмите правой кнопкой по любой сумме в столбце В и выберите пункт Сортировка - > Сортировка от Я до А.

Теперь наши менеджеры отсортированы по объёму продаж.



3. Формируем таблицу Сумма продаж по типам клиентов
Усложним задачу. Добавим на этом же листе новую сводную таблицу.
Сумма продаж по типам клиентов.

1. Встаньте на любую ячейку сводной таблицы. Нажмите сочетание клавиш Ctr+A далее Ctr+C, так мы скопировали её в буфер обмена.

2. Встаньте в ячейку F3 и нажмите сочетание клавиш Ctr+V. Так мы создали копию сводной таблицы, которую будем переделывать.

3. Встаньте на любую ячейку второй сводной таблицы. И проделайте следующее.

Перетащите поле Тип клиента в квадрат Строки. А поле Менеджер из этого квадрата удалите, правой кнопкой.

В итоге мы получили вторую сводную таблицу. В этой таблице мы видим объём продаж по типам клиентов.

4. Усложним задачу и построим третий отчёт
Доработаем нашу первую сводную таблицу. Для того чтобы она считала заработную плату менеджеров в зависимости от объёма продаж.

1. Для этого выделите любую ячейку первой сводной таблицы.

2. Выберите пункт Анализ -> Поля, Элементы и наборы -> Вычисляемое поле.

3. Заполним поля в диалоговом окне. В поле Имя введите _ЗП. В поле Формула введите вот такую простую формулу = Сумма* 0,035. И жмём ОК.

Пример создания сводной таблицы
В видеоролике ниже я разобрал 2 примера создания сводных таблиц.

1. Анализ работы отдела продаж.
2. Анализ работы рекламных кампаний.
Выводы и комментарии
1. Представленный выше пример, показал лишь 1% от всех возможностей работы со сводными таблицами.

2. Для полноценного изучения сводных таблиц рекомендую поступить следующим образом.

  • Прочитайте книгу https://www.ozon.ru/context/detail/id/139953683/ Возможно этого вам будет достаточно для освоения сводных таблиц.
  • Если книги было не достаточно и вы решили познать весь дзен сводных таблиц. Тогда пройдите курс Максима Уварова http://learn.needfordata.ru/excel После этого курса вы точно сможете сказать, что немного разбираетесь в Excel и в сводных таблицах в частности.
3. Любите Excel и да прибудут с вами красивые отчёты полные инсайтов.