Как сделать сводные таблицы в Excel: пошаговая инструкция

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

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

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

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

Разберём, как сделать сводную таблицу на примере. Директору магазина ковров нужно рассчитать премию менеджерам по продажам. В таблице в Экселе хранятся данные по всем заказам:
● модель ковра;
● цвет;
● размер;
● цена;
● дата;
● имя менеджера.

Данные по заказам магазина за месяц

Для расчёта ежемесячной премии директор магазина должен знать, кто из менеджеров продал больше ковров и принёс больше прибыли.

Данные для сводной таблицы в Экселе должны соответствовать трём правилам:
1. В таблице не должно быть пустых ячеек.
2. Ячейки в одной колонке необходимо заполнять в едином формате.
3. У каждой колонки должен быть заголовок.

Вот пошаговая инструкция, которая поможет создать сводную таблицу в Excel.

Найти в меню кнопку «Сводная таблица»

Она в разделе «Вставка»

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

Указать расположение сводной таблицы — место, где она появится в Excel. Лучше выбрать новый лист, чтобы сохранить исходные данные.

Заполнив параметры, нажать «Ок»

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

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

1. Фильтры. Помогают настраивать вид таблицы, выбирая данные, которые необходимо отобразить, и то, что нужно скрыть.

2. Значения. Область отвечает за вычисление данных. Обычно Excel их суммирует, при числовых значениях можно получить общую сумму, а при текстовых — количество. Можно настроить другие вычисления, например, выделить максимальное значение.

Добавив в области «Фильтры» поля «Менеджер» и «Цена», а в «Значение» только «Цена», можно увидеть общую сумму заказов и фильтровать её по каждому продавцу

3. Колонны. Вариант демонстрации данных, при котором они будут разделены по колонкам.

При выборе варианта «Колонны» данные отображаются в отдельных столбцах, а для каждого параметра исходной таблицы выделяется отдельная строка

4. Строки. Вариант демонстрации данных, при котором они будут разделены по строкам.

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

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

Чтобы настроить сводную таблицу в Экселе, нужно перетаскивать поля в области. Или можно отмечать нужные для отображения параметры, и программа самостоятельно распределит их по областям. Такую таблицу, автоматически созданную Экселем, пользователь потом может скорректировать для себя, например, переместив поле из области «Колонны» в область «Строки».

Для расчёта премии за ноябрь нужны поля «Менеджер» и «Цена». Работать в сводной таблице в Excel будет удобнее, если отображать поля «Менеджер» построчно, а «Цена» — по колоннам и также переместить его в графу «Значение».

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

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

Чтобы посмотреть, как в ноябре продавалась модель ковра Rabbit Rex, нужно кликнуть по стрелке рядом с нужным фильтром и выбрать модель. Информация в таблице сразу обновится.

Чтобы увидеть сумму и количество проданных ковров, необходимо указать в фильтре конкретную модель

Так можно фильтровать данные по одному или нескольким параметрам и видоизменять таблицу при необходимости. Например, можно убрать галку с поля «Менеджер», и таблица покажет только количество проданных моделей и общую сумму. Или посмотреть продажи по конкретному цвету ковра Rabbit Rex, добавив его в фильтре.

В целом возможности сводных таблиц в Экселе можно разделить на три категории.

1. Формирование стиля таблицы.
Для удобства восприятия сводной таблицы в разных форматах, можно переносить поля в области «Колонны» и «Строки». Например, для презентаций на десктопе может быть удобно строить данные по колоннам. А для просмотра на мобильных устройствах больше подойдет построчное отображение.

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

3. Анализ данных.
Сводные таблицы помогают быстро обрабатывать большой объём данных: сравнить данные и провести сложные расчеты. К примеру, с их помощью можно собрать показатели по разным рекламным каналам и выявить наиболее эффективные. А если прописать в таблице формулу расчета ДРР ― доли рекламных расходов, можно определить не только канал, который принес больше всего продаж, а найти те, которые в итоге обошлись компании дешевле.

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

Если директору магазина понадобится информация о выручке, которую принёс каждый менеджер в процентах от общих продаж, достаточно кликнуть правой кнопкой на цену и выбрать «Дополнительные вычисления» → «% от общей суммы».

Благодаря сводной таблице директор узнает, что большую часть продаж компании принесла Демиденко Д.А.

Также можно посмотреть, сколько товаров продали за месяц. Для этого нужно во всплывающем списке выбрать поле «Итоги по» и «Количество».

Изменение итоговых подсчётов с суммы на количество позволит увидеть общее количество продаж, а также данные по каждому продавцу и модели ковра

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

Кнопка «Обновить» находится в главном меню в разделе «Анализ»

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

Для обновления сводной таблицы придётся опять обратиться к источнику данных в разделе «Анализ»

По клику на «Источник данных» откроется первоначальная таблица со всплывающим окном, в котором необходимо указать диапазон. Здесь также можно выделить мышкой всю таблицу и нажать «Ок». Excel автоматически откроет сводную таблицу с обновлёнными данными.

После обновления исходной таблицы распределение процентов от общей суммы продаж изменилось

Помимо общих настроек и фильтров, в сводных таблицах Excel есть еще несколько полезных функций:

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

Кнопка «Рекомендуемые таблицы» располагается в разделе «Вставка» верхнего меню

● Срезы. Помогают настроить отчёт под нужды конкретного пользователя и быстро менять отображение данных. Например, таблицу по продажам ковров можно дополнить срезом по цветам, чтобы была возможность быстро просматривать данные по любому цвету. Для этого в разделе меню «Анализ» нужно выбрать «Вставить срез». Во всплывающем окне указать параметр «Цвета» и нажать «Ок». Срез появится рядом со списком значений поля. По клику на цвет в сводной таблице будут отображаться данные по количеству проданных ковров этого цвета.

Если необходимо сделать срез по нескольким параметрам, их можно выбрать, зажав на клавиатуре Ctrl (Windows ОС) или Cmd (Mac ОС). Сбросить срез можно по клику на иконку фильтра рядом с названием поля

● Временная шкала. Позволяет посмотреть данные за конкретный период. Создать шкалу в сводной таблице можно во вкладке «Аналитика» → «Вставить временную шкалу». Откроется выбор полей, в значениях которых указаны даты. При настройках шкалы можно выбрать годы, кварталы, месяцы или дни. Это удобно, когда нужно получить данные по квартальным продажам.

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

● Вычисляемые поля. Эта функция пригодится, если нужно провести дополнительные математические действия с данными сводной таблицы в Excel. К примеру, добавить налог или вычислить процент премии от продаж. Чтобы сделать такую сводную таблицу в Экселе, нужно зайти во вкладку «Анализ», нажать кнопку «Поля, элементы и наборы» и выбрать «Вычисляемое поле». При создании поля «Премия» нужно добавить формулу. Если менеджеры получают 5% от стоимости проданных ковров, её можно рассчитать по формуле:
Премия = Цена х 0,05.

Чтобы добавить поле в формулу, достаточно выбрать её из меню полей и нажать «Добавить поле»

После клика на кнопку «Ок» в таблице автоматически появится колонка с суммой премии.

Если колонка «Премия» не появилась автоматически, нужно поставить галку рядом с полем «Премия» в меню справа, и она отобразится в таблице

Сводные таблицы в Excel пригодятся для любых расчётов и аналитики данных.

1. С помощью сводной таблицы можно анализировать закупки магазина.

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

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

Сводная таблица рассчитала сумму, потраченную на покупку мяса, и поставщиков, у которых был закуплен этот товар

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

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

Используя сводные таблицы, производитель адресников может проанализировать множество данных: какие модели самые популярные и какие размеры чаще заказывают. Допустим, для продвижения мастеру важно понимать самые популярные породы собак, которые есть у заказчиков. Точные результаты можно получить в пару кликов.

Для определения самой популярной среди клиентов породы достаточно добавить поле «Порода собаки» в области «Строки» и «Значения»