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

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

Зачем нужны сводные таблицы в Excel

Сводные таблицы в Excel - это инструмент для обобщения и анализа данных. Они позволяют за считанные секунды подвести итоги и создать отчетность по тысячам и миллионам строк.

В отличие от обычных таблиц, сводные:

• Автоматически группируют и суммируют числовые значения
• Показывают итоги и промежуточные подведения
• Позволяют быстро менять ракурс отчета перетаскиванием полей мышью

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

• Сколько продано каждого товара по регионам?
• Какая категория принесла наибольшую выручку?
• Как менялись продажи по месяцам за последние 3 года?

Где применяются сводные таблицы

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

• Продажи и маркетинг
• Бухгалтерия и финансы
• Склад и логистика
• Производство и планирование
• Медицина и наука

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

Чтобы сводная таблица работала корректно, исходные данные должны соответствовать определенным требованиям:

1. Наличие заголовков над каждым столбцом
2. Отсутствие дубликатов и пустых ячеек
3. Правильное форматирование чисел, дат, текста
4. Разбиение данных по столбцам вместо групп

Для удобства лучше преобразовать диапазон данных в Таблицу Excel (команда Формат как таблица). Это решит сразу несколько вопросов:

• Автоматическое добавление новых данных
• Удобство ссылок на ячейки
• Структурированность информации

Пример подготовки данных

Допустим, у нас есть такие неструктурированные исходные данные о продажах:

Их нужно привести к такому виду:

• Убрать дубли и пустые ячейки
• Добавить заголовки ко всем столбцам
• Преобразовать диапазон в таблицу Excel
• Даты и числа форматировать правильно

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

После подготовки исходных данных перейдем непосредственно к созданию отчета. Алгоритм такой:

1. Выделяем ячейку внутри таблицы
2. Переходим на вкладку Вставка и нажимаем Сводная таблица
3. В появившемся окне ничего менять не надо, жмем ОК
4. Выбираем поля и настраиваем макет отчета

Более подробно этот процесс мы рассмотрим на примере отчета о продажах по товарам и регионам.

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

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

1. Выделяем любую ячейку исходных данных и выбираем Сводная таблица
2. В появившемся диалоговом окне ничего менять не нужно. Нажимаем ОК.
3. Справа появится область "Поля сводной таблицы". Ставим галочки напротив полей Товар, Регион, Сумма.
4. Перетаскиваем Товар и Регион в строки и столбцы соответственно. А Сумму - в Значения.

На такие простые операции уходит меньше минуты. И мы получаем нужный отчет о продажах каждого товара по регионам. При этом данные автоматически сгруппированы и просуммированы.

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

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

1. Добавим в отчет разбивку по кварталам: перетаскиваем поле Квартал в область Столбцов
2. Разделим выручку на план и факт: создаем вычисляемое поле в исходных данных со значением 1 и перетаскиваем его в Строки

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

Фильтрация данных сводной таблицы

Что если нам нужно посмотреть информацию только по конкретному региону? Для этого воспользуемся фильтрами.

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

Аналогично можно фильтровать отчет по любому измерению: категории, бренду, дате и т.д. При этом фильтры можно комбинировать друг с другом.

Добавление диаграммы в сводную таблицу

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

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

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

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

Данные в исходном диапазоне могут дополняться или изменяться - новые заказы, возвраты, корректировки. Чтобы это отразилось в отчете, его необходимо обновлять.

Обновление сводной таблицы двумя способами:

• Нажать кнопку Обновить данные во вкладке Анализ
• Щелкнуть правой кнопкой мыши по сводной таблице и выбрать Обновить

Если возникли ошибки в увязке данных, то можно также нажать кнопку Обновить все во вкладке Данные.

Группировка данных в сводной таблице

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

Это можно сделать с помощью кнопки Группировка полей:

1. Выделяем поле, которое нужно сгруппировать (например, Товар)
2. Вызываем контекстное меню, выбираем Группировка полей
3. Указываем условия группировки (по первой букве, интервалам и т.д.)

Теперь сводная таблица позволяет свернуть/развернуть сгруппированные данные, что повышает наглядность отчетности.

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

Версии Excel 2007 и более поздних имеют расширенный набор возможностей для сводных таблиц:

• Рекомендуемые таблицы для ускорения создания
• Режимы проектирования для настройки макета отчета
• Срезы OLAP для вычислений по иерархии данных
• Наглядное фильтры с визуальным выбором значений

К сожалению, в более старых версиях Excel таких опций нет.

Развертывание сводной таблицы в разрезе одного показателя

Интересная возможность сделать сводную таблицу ms excel - развернуть часть данных в отдельную таблицу с большей детализацией. Это удобно для сквозного анализа по одному измерению:

1. Выделяем соответствующие ячейки сводной таблицы
2. Выбираем команду Развернуть/Свернуть
3. Данные по одному признаку скопируются на новый лист

После этого мы получаем два представления: общий свод и детализированную таблицу по выбранному измерению.

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