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

Сводные таблицы в Excel — это мощный инструмент для анализа данных (например, о выпуске продукции), который группирует их по типам, периодам и иным аналитикам. Они помогают проводить оперативный и многомерный анализ показателей, автоматизировать формирование отчетности, снизить трудоемкость учета. Профессиональное применение сводных таблиц превращает сырые значения показателей в мощный инструмент принятия управленческих решений. Из статьи вы узнаете, как создать такие таблицы на основе производственных учетных данных, как с их помощью «на лету» перестроить отчеты, детализировать информацию и выявить скрытые закономерности.

Подготовка источника данных

Корректная подготовка исходных данных устраняет распространенные ошибки, допускаемые при формировании в Excel сводных таблиц.

Чтобы подготовить данные для создания сводной таблицы, необходимо сделать следующее[1]:

Шаг 1. Разъедините объединенные ячейки в заголовках, укажите уникальные имена столбцов. Если заголовки объединены или содержат пустоты, Excel напишет: «Недопустимое имя поля сводной таблицы. Чтобы создать сводную таблицу, используйте данные, организованные в виде списка с заголовками столбцов. Для изменения имени поля сводной таблицы введите новое имя».

Шаг 2. Скорректируйте неправильный формат данных (текст в числовых полях и т. п.). На рис. 1 в верхней таблице с оранжевой шапкой можно увидеть пример таких данных — значение себестоимости на объем (14 260) с точкой. Смешанные типы данных дают нулевой результат.

Шаг 3. Удалите пустые строки/столбцы в диапазоне. Если сводная таблица игнорирует или ошибочно группирует данные, удалите лишнее.

Источник данных приводят к виду плоской таблицы в Excel (рис. 1, нижняя таблица с зеленой шапкой).

Шаг 4. Преобразуйте сформированный на шаге 3 источник данных в отформатированную таблицу для автообновления: вкладка «Главная» — Стили — Форматировать как таблицу (рис. 2). Выбранный стиль значения не имеет. Отформатированные таблицы еще называют умными или просто таблицами. До этого действия мы имели дело с диапазоном ячеек.

На вкладке «Конструктор» (группа команд «Свойства») изменяют имя таблицы. В рассматриваемом примере присвоенное таблице по умолчанию имя «Таблица1» заменено на «Производство».

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

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

Создаем сводную таблицу

Чтобы создать сводную таблицу, переходим на вкладку «Вставка» — Таблицы — Сводная таблица (рис. 3). Появится окно «Создание сводной таблицы» (рис. 4), где автоматически выбраны данные для анализа — в рассматриваемом случае ранее созданная отформатированная таблица «Производство». Обратите внимание, что вся таблица «Производство» выделена пунктиром.

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

В окне «Создание сводной таблицы» (рис. 4) также указывают, куда нужно поместить отчет сводной таблицы. По умолчанию он помещается на новый лист. Рекомендуется придерживаться этой настройки чтобы исключить затирание исходных данных.

Для подтверждения нажимаем «ОК», и Excel сформирует новый лист с полями сводной таблицы. 

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

Перетаскиванием полей в нужную область экономист адаптирует сводную таблицу под специфику производства и требования руководства.

Для рассматриваемой сводной таблицы перетаскиваем:

• в область «Фильтры» — поле «Сегмент» (на рис. 5 указано левой красной стрелкой);
• в область «Строки» — поля «Тип», «Рецептура» (указаны зелеными стрелками). От порядка расположения полей в этой области зависит порядок их отражения в отчете — сначала группировка по типам документов (брак, отбор на тестирование), а затем отображение в разрезе детализации по рецептуре. Так, согласно рис. 5 на тестирование отбирали венскую булку, шоколадное печенье и пшеничный хлеб «Кирпичик»;

• в область «Столбцы» — поля «Цех», «Значения». Значения в таблице появятся автоматически в зависимости от полей, указанных в области «Значения» (на рис. 5 указано правой красной стрелкой);
• в область «Значения» — поля «Объем», «Себестоимость производства». Как правило, сводная таблица выполняет суммирование, отсюда и название столбца F («Сумма по полю Объем» и др.). Если пользователю нужно иное действие, то через параметры поля значений он выбирает операцию «Количество», «Максимум», «Среднее» и т. д.