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

Сводные таблицы помогают работать с большими массивами данных в Excel. Например, строительный магазин выгрузил список продаж за месяц — вышло 5 000 строк. Чтобы вручную посчитать выручку в каждой категории товаров, нужно потратить несколько часов. А сводная таблица покажет результат за пару минут.

Зачем нужны сводные таблицы и когда их используют

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

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

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

Как сделать сводную таблицу в Excel

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

• подготовить исходную таблицу;
• создать сводную таблицу;
• настроить структуру отчета.

Шаг 1. Подготовить исходную таблицу. Чтобы сводная таблица сформировалась корректно, нужно проверить эти моменты:

• у каждого столбца есть заголовок;
• нет пустых строк. Если они будут, программа составит таблицу до первой пустой строки, остальное данные не войдут;
• в одном столбце лежит один тип данных — например, только числа, дата, слова или проценты;
• формат ячеек с датами — «Дата», а не «Текст»;
• у чисел формат данных «Число», а не «Текст».

Шаг 2. Создать сводную таблицу. Для этого:

1. Выделите информацию, которая попадет в таблицу, — поставьте курсор в любую ячейку с данными и нажмите Ctrl + A.
2. Откройте вкладку «Вставка» — «Сводная таблица».
3. Укажите, куда поместить отчет: на новый лист или текущий. Если выбрали текущий, программа попросит выбрать ячейку, где будет начало сводной таблицы.

Работает это так:

• «строки» — данные размещаются в строках одного столбца;
• «столбцы» — данные размещаются в столбцах в одной строке;
• «значения» — программа применяет формулу к выбранному диапазону данных. По умолчанию это сложение. Например, если перетащить в эту область «Выручку», программа посчитает сумму выручки по городам. Еще можно узнать среднее значение — то есть среднюю выручку в каждом городе. Или посчитать повторения значений — скажем, сколько продаж сделал каждый менеджер. Всего доступно 11 формул;
• «фильтры» — помогают выбирать данные, которые нужно отобразить или скрыть. Например, можно отобразить продажи только в одном городе или только у двух менеджеров из четырех.

Директор Технохауса хотел узнать выручку по каждому городу. Для этого:

• перетащил поле «Город» в раздел «Строки». Получил список городов в столбик;
• перетащил поле «Выручка» в раздел «Значения». По умолчанию была рассчитана сумма.

Теперь можно усложнить отчет, например:

• добавить менеджеров в столбцы;
• показать средний чек вместо суммы. Для этого в поле «Значения» нужно выбрать формулу AVERAGE;
• учесть продажи всей техники, кроме кофемашин. Для этого нужно добавить поле «Товар» в область фильтров. Над сводной таблицей появится выпадающее меню — в нем нужно снять галочку с поля «Кофемашины».

Панель настройки сводной таблицы можно закрыть. Чтобы открыть снова, достаточно нажать на сводную таблицу или кнопку «Изменить» под таблицей — в зависимости от версии Excel.

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

Полезные функции сводных таблиц

Excel позволяет тонко настраивать таблицу. Расскажем о нескольких полезных функциях для бизнеса:

• группировка данных;
• срезы;
• временная шкала;
• вычисляемые поля.

Чтобы сгруппировать даты:

1. Нажмите правой кнопкой по любой дате в сводной таблице.
2. Выберите «Сгруппировать».
3. Укажите нужный период.

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

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

1. Зажать Ctrl и кликнуть на ячейки, которые помещаете в одну группу.
2. Нажать правой кнопкой по одной из выбранных ячеек и кликнуть «Сгруппировать».

Названия таких произвольных групп можно менять. Для этого выделите ячейку с названием и введите нужный заголовок.

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

Чтобы добавить срез, нужно:

1. Выбрать любую клетку сводной таблицы → нажать «Анализ сводной таблицы» в меню сверху → «Вставить срез».
2. Выбрать категории данных, по которым нужны срезы.

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

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

Чтобы вставить временную шкалу, нужно:

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

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

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

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

• какой филиал приносит больше денег, а где падают продажи;
• какие менеджеры работают лучше или хуже остальных;
• какие товары чаще покупают онлайн.

Нужно построить сводную таблицу:

1. В строки добавить города и менеджеров.
2. В столбцы добавить каналы продаж — магазин и онлайн‑заказ.
3. В значения — сумму по полю «Выручка».

В результате уже через пару минут руководитель увидел:

• онлайн‑продажи в Томске выросли на 40%, а офлайн‑магазин в Самаре просел;
• один из менеджеров почти не продает дорогие товары;
• кабель и крепеж чаще покупают через сайт, а краску — офлайн.

Без сводной таблицы на такой анализ ушел бы не один час. Со сводным отчетом руководитель получает картину бизнеса за несколько минут и быстрее принимает решения.