За 10 минут я научу вас пользоваться сводными таблицами Excel

Что такое сводные таблицы 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, нажав сочетание клавиш Ctrl+A.
3. Выберите пункт “Вставка”, далее кнопку “Сводная таблица”. В появившемся диалоговом окне нажмите кнопку ОК.

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

Шаг 2. Формируем нашу первую сводную таблицу

А теперь сразу в бой. Попробуем создать нашу первую сводную таблицу: “Сумма продаж по каждому менеджеру”.

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

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

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

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

Шаг 3. Формируем таблицу “Сумма продаж по типам клиентов”

Теперь усложним задачу: добавим на этом же листе новую сводную таблицу — “Сумма продаж по типам клиентов”.

1. Встаньте на любую ячейку сводной таблицы. Нажмите сочетание клавиш Ctrl+A, далее Ctrl+C, так мы скопировали ее в буфер обмена.
2. Встаньте в ячейку F3 и нажмите сочетание клавиш Ctrl+V. Так мы создали копию сводной таблицы, которую будем переделывать.
3. Встаньте на любую ячейку второй сводной таблицы. Перетащите поле “Тип клиента” в квадрат “Строки”. А поле “Менеджер” из этого квадрата удалите правой кнопкой мыши.

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

Шаг 4. Усложним задачу и построим третий отчет

А давайте-ка доработаем нашу первую сводную таблицу! Сделаем так, чтобы она считала заработную плату менеджеров в зависимости от объема продаж.

1. Для этого выделите любую ячейку первой сводной таблицы.
2. Выберите пункт “Анализ” -> “Поля”, “Элементы и наборы” -> “Вычисляемое поле”.
3. Заполним поля в диалоговом окне. В поле “Имя” введите _ЗП. В поле “Формула” введите вот такую простую формулу = Сумма* 0,035. И нажмите ОК.

Выводы и комментарии

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

1. Прочитайте книгу  Сводные таблицы в Microsoft Excel 2016. Возможно, этого вам будет достаточно для освоения сводных таблиц.
2. Если книги было недостаточно и вы решили познать весь дзен сводных таблиц, пройдите курс Максима Уварова  «Excel (for Windows) для интернет-маркетинга». После этого курса вы точно сможете сказать, что немного разбираетесь в Excel и в сводных таблицах в частности.
3. Любите Excel — и да пребудут с вами красивые отчеты, полные инсайтов!

Еще больше статей вы можете прочитать в блоге Рустама Гизатуллина. Делайте репосты, пишите автору — Рустам ответит на ваши вопросы!