Как создать отчет с годовым сравнением в сводной таблице Excel
Создание отчета с годовым сравнением (year-over-year) в сводной таблице Excel — это мощный метод анализа динамики данных за разные периоды. Этот подход позволяет быстро оценить рост или падение показателей, что критически важно для финансового анализа, продаж и планирования.
Если вы ищете сводные таблицы в Excel без путаницы, здесь собраны приемы, которые помогают собрать сводную таблицу в экселе и получить понятный отчет по данным.
Ниже показана сводная по обычному диапазону или таблице в настольном Excel для Windows. В источнике должны быть настоящие даты Excel без текстовых значений, ошибок и пропусков в поле даты. Для модели данных и OLAP описанная ручная группировка не применяется: используйте календарное поле года в модели.
Пошаговая инструкция по созданию отчета
- Создайте сводную: поместите «Дата» в строки, нужный показатель, например «Выручка», — в значения, а категорию для сравнения, например «Клиент», добавьте в строки перед датой.
- Для группировки дат по годам выполните следующие действия:
- Выберите любую ячейку с датой внутри сводной таблицы.
- Перейдите на вкладку Анализ сводной таблицы на ленте.
- Нажмите кнопку Группировать поле.
- В открывшемся диалоговом окне Группирование снимите выделение со всех остальных интервалов и оставьте только Годы, затем нажмите OK.
- Ежедневные даты будут сгруппированы по годам. Перенесите поле с годами из области строк в область столбцов; в зависимости от версии оно может называться «Годы (Дата)», «Годы» или «Дата». Если остались уровни месяцев или отдельных дат, уберите их из макета. Отсортируйте годы по возрастанию.
- Если общий итог за все годы не нужен, выберите «Конструктор → Общие итоги → Отключить для строк и столбцов». Само отключение итогов не вычисляет процентное изменение.
- Для расчета изменения год к году создайте формулу рядом со сводной таблицей. Важно: чтобы избежать автоматического появления функции
ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ / GETPIVOTDATA, не используйте мышь для выбора ячеек. Вместо этого вручную введите формулу, например, Русская версия:=C5/B5-1English version:=C5/B5-1, и нажмитеEnter. Здесь B5 — прошлый год, C5 — текущий. Задайте результату процентный формат. При B5=0 формула вернёт ошибку деления на ноль; такой случай нужно интерпретировать отдельно. Внешние ссылки по адресам также проверяйте после изменения макета сводной.
Продвинутые советы и альтернативные методы
Особенности группировки и кэша сводных таблиц
Если у вас несколько сводных таблиц, созданных из одного источника, они обычно используют общий кэш сводной таблицы, поэтому группировка в одной может затронуть другую. Чтобы иметь разную группировку в каждой таблице, необходимо создать новый независимый кэш.
- Сохраните копию книги. Создайте новую сводную с независимым кэшем в пустом месте и заменяйте старую только после проверки.
- Создайте ее заново, используя старый мастер сводных таблиц. Добавьте «Мастер сводных таблиц и диаграмм» на панель быстрого доступа через «Все команды». Для английского интерфейса Windows подходит также
Alt,D, затемP. - В диалоговом окне мастера (Шаг 1 из 3) выберите источник «В списке или базе данных Microsoft Excel» и тип отчёта «Сводная таблица», затем нажмите Далее.
- На Шаге 2 подтвердите диапазон данных и нажмите Далее.
- Появится дополнительное окно с вопросом об экономии памяти. Чтобы создать независимую таблицу с возможностью своей группировки, выберите Нет. На шаге 3 задайте место размещения и нажмите «Готово».
Расчет годового изменения внутри сводной таблицы
Вместо внешней формулы вы можете рассчитать процентное изменение непосредственно в сводной таблице, используя встроенную функцию Показать значения как.
- Перетащите поле с данными (например, Выручка) в область Значения второй раз.
- В панели полей в разделе Столбцы перетащите плитку Значения так, чтобы она располагалась ниже поля с годами.
- Дважды щелкните по заголовку нового поля значений (например, Сумма по полю Выручка2), чтобы открыть Параметры поля значений.
- Перейдите на вкладку Отображать значения как.
- В выпадающем списке выберите % разницы от.
- В поле Базовое поле выберите Годы (дата).
- В поле Базовый элемент выберите (предыдущий).
- Задайте понятное имя, например, % Изменения, и нажмите OK.
Для первого года данные в столбце изменения будут пустыми (нечего сравнивать). Если он не нужен, скройте соответствующий столбец листа через его буквенный заголовок. Для базового элемента «предыдущий» сравнение идёт с предыдущим элементом поля: пропущенный год не появится автоматически. При нулевой базе процентное изменение не определено.
Заключение
Использование сводных таблиц Excel для анализа данных год к году предоставляет гибкий и эффективный инструмент для бизнес-аналитики. Вы можете выбрать подход с внешними формулами для максимального контроля или использовать встроенные возможности сводной таблицы для автоматизации. Понимание работы с кэшем позволяет создавать сложные отчетные панели с независимой настройкой каждой таблицы.