Реклама
Этот сайт использует Яндекс.Метрику для анализа посещаемости. Подробнее
Таблицы и сводные таблицы

Как создать отчет с годовым сравнением в сводной таблице Excel

forest_paw 25.10.2025 968

Как создать отчет с годовым сравнением в сводной таблице Excel

Создание отчета с годовым сравнением (year-over-year) в сводной таблице Excel — это мощный метод анализа динамики данных за разные периоды. Этот подход позволяет быстро оценить рост или падение показателей, что критически важно для финансового анализа, продаж и планирования.

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

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

Пошаговая инструкция по созданию отчета

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

Продвинутые советы и альтернативные методы

Особенности группировки и кэша сводных таблиц

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

  1. Сохраните копию книги. Создайте новую сводную с независимым кэшем в пустом месте и заменяйте старую только после проверки.
  2. Создайте ее заново, используя старый мастер сводных таблиц. Добавьте «Мастер сводных таблиц и диаграмм» на панель быстрого доступа через «Все команды». Для английского интерфейса Windows подходит также Alt, D, затем P.
  3. В диалоговом окне мастера (Шаг 1 из 3) выберите источник «В списке или базе данных Microsoft Excel» и тип отчёта «Сводная таблица», затем нажмите Далее.
Мастер сводных таблиц и диаграмм в стиле Excel 2003, отображающий шаг 1 из 3.
Мастер сводных таблиц и диаграмм в стиле Excel 2003, отображающий шаг 1 из 3.
  1. На Шаге 2 подтвердите диапазон данных и нажмите Далее.
  2. Появится дополнительное окно с вопросом об экономии памяти. Чтобы создать независимую таблицу с возможностью своей группировки, выберите Нет. На шаге 3 задайте место размещения и нажмите «Готово».
Дополнительное диалоговое окно мастера с вопросом об экономии памяти.

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

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

  1. Перетащите поле с данными (например, Выручка) в область Значения второй раз.
  2. В панели полей в разделе Столбцы перетащите плитку Значения так, чтобы она располагалась ниже поля с годами.
  3. Дважды щелкните по заголовку нового поля значений (например, Сумма по полю Выручка2), чтобы открыть Параметры поля значений.
  4. Перейдите на вкладку Отображать значения как.
  5. В выпадающем списке выберите % разницы от.
  6. В поле Базовое поле выберите Годы (дата).
  7. В поле Базовый элемент выберите (предыдущий).
  8. Задайте понятное имя, например, % Изменения, и нажмите OK.
Настройка отображения значений как '% разницы от' в параметрах поля значений сводной таблицы.
Настройка отображения значений как '% разницы от' в параметрах поля значений сводной таблицы.

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

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

Заключение

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

Поделиться:

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