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

Что такое сводная таблица Excel и зачем она нужна

Сводная таблица (Pivot table) — инструмент Excel, который помогает быстро собрать и проанализировать большие объёмы данных. В обычной таблице они просто хранятся в строках и столбцах — и если вам нужно что-то подсчитать, приходится вручную фильтровать, суммировать или строить формулы. Сводная таблица делает это за вас:

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

• группирует информацию без изменения исходной таблицы;

• позволяет быстро менять настройки и получать новые срезы — без лишних расчётов.

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

Подготовка данных для сводной таблицы

Перед созданием сводной таблицы важно правильно организовать исходную информацию. Все записи должны находиться в едином массиве, без пустых строк, объединённых ячеек и лишних заголовков. У каждого столбца должно быть понятное название, например, «Дата», «Товар», «Количество», «Сумма», а у данных в столбце — один тип (числа, даты или текст). 

Если всё оформлено как «умная таблица», Excel будет автоматически расширять диапазон при добавлении новых строк. Для этого выберите ячейку внутри таблицы, перейдите на вкладку «Вставка» — «Таблица» или выделите диапазон и нажмите Ctrl + T.

Создание сводной таблицы в Excel

После подготовки можно переходить к следующему этапу — созданию сводной таблицы. Разберем этот процесс пошагово.

Шаг 1. Выделение данных

Начните с выделения всего диапазона, в котором находится нужная информация. Воспользуйтесь сочетанием Ctrl + A, чтобы выбрать весь массив, включая заголовки столбцов. Важно, чтобы выборка была полной — именно из неё Эксель будет строить отчёт.

Шаг 2. Вставка сводной таблицы

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

Шаг 3. Настройка полей сводной таблицы

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

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

Работа со сводными таблицами

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

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

Обновление данных в сводной таблице

Если вы меняете исходную таблицу — добавляете новые строки или изменяете значения — сводная не обновляется автоматически. Чтобы получить актуальные данные, выполните одно из следующих действий:

• Через контекстное меню: кликните правой кнопкой мыши по любой ячейке сводной таблицы и выберите команду «Обновить».

• Через ленту инструментов: перейдите во вкладку «Анализ» (или «Конструктор») и нажмите кнопку «Обновить».

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

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

Эксель предлагает дополнительные инструменты, которые делают анализ более гибким. Рассмотрим основные.

Вычисляемые поля

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

• выберите сводную таблицу;

• перейдите на вкладку «Анализ»;

• нажмите «Поле, элементы и наборы» и выберите «Вычисляемое поле»;

• введите название поля и формулу, после чего Excel автоматически рассчитает новое значение для каждой группы.

Рекомендуемые таблицы

Если вы не уверены, какой вид отчёта вам подойдёт, воспользуйтесь функцией рекомендуемых сводных таблиц. Выделите данные, перейдите во вкладку «Вставка» и выберите «Рекомендуемые сводные таблицы». Эксель предложит несколько вариантов, среди которых можно выбрать наиболее удобный и понятный вариант.

Важно: функция «Рекомендуемые сводные таблицы» появляется в Excel 2013.

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

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

• убедитесь, что на всех листах организация одна и та же, с одинаковыми заголовками столбцов;

• при вставке сводной таблицы выберите опцию «Использовать несколько источников данных»;

• добавьте каждый диапазон с нужных листов;

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

Примеры использования сводных таблиц в Эксель

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

• Анализ продаж по регионам. Перетащите поле «Регион» в строки, а «Сумму продаж» — в значения. Вы быстро получите картину, какие регионы приносят наибольшую выручку.

• Подсчёт итогов по категориям товаров. Если в данных есть поле «Категория», переместите его в строки, а количество проданных единиц — в значения. Это позволит сравнить продажи по группам товаров.

• Анализ затрат за период. Перетащите поле «Дата» в строки, сгруппируйте по месяцам, а «Сумму расходов» — в значения. Получится наглядный отчёт о динамике затрат.

• Учёт рабочего времени. В поле строк укажите «Сотрудник», а в значения — суммарные часы работы. Такой отчёт поможет увидеть, кто сколько отработал за выбранный период.

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

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

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

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

• Экспериментируйте с настройками. Перестановка полей и изменение функций (сумма, среднее, счёт) позволят найти наиболее удобное представление информации.

• Преобразуйте диапазон в «умную таблицу». Сможете обновлять данные без лишних усилий.

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

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

• Используйте рекомендуемые таблицы, чтобы ускорить работу.

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

Excel — это не просто таблицы, которые пытаются выбить вас из колеи. Это ключевой навык, который ищут 111 000+ работодателей. Если хотите быстрее справляться с задачами, автоматизировать рутину и зарабатывать больше, лучше раз и навсегда разобраться в Excel и больше не мучиться. 

Академия Eduson подготовила курсы по Excel для разных целей: управление финансами, бухгалтерия, работа с массивами данных. Выберите программу, которая поможет вам работать быстрее, увереннее и эффективнее.

Подписывайтесь на телеграм-канал Академии Eduson, чтобы видеть больше полезных постов о востребованных профессиях и карьерном росте, узнавать про скидки и акции на обучение.