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

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

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

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

1. Создайте стандартную сводную таблицу из вашего набора данных, где каждая запись имеет ежедневную дату.
2. Для группировки дат по годам выполните следующие действия:
   • Выберите любую ячейку с датой внутри сводной таблицы.
   • Перейдите на вкладку Анализ сводной таблицы на ленте.
   • Нажмите кнопку Группировать поле.

3. В открывшемся диалоговом окне Группирование снимите выделение с Месяцев и выберите Годы, затем нажмите OK.

4. Ежедневные даты будут сгруппированы по годам. Перетащите поле Годы (Дата) из области Строки в область Столбцы панели полей сводной таблицы.

5. Чтобы убрать общий итог и рассчитать процентное изменение, щелкните правой кнопкой мыши по заголовку Общий итог и выберите Удалить общий итог.
6. Для расчета изменения год к году создайте формулу рядом со сводной таблицей. Важно: чтобы избежать автоматического появления функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA), не используйте мышь для выбора ячеек. Вместо этого вручную введите формулу, например, =В5/Б5-1 (=C5/B5-1), и нажмите Enter.

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

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

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

1. Удалите вторую сводную таблицу.
2. Создайте ее заново, используя старый мастер сводных таблиц. Нажмите Alt + D, затем P (классическая комбинация для вызова мастера).
3. В диалоговом окне мастера (Шаг 1 из 3) выберите Сводная таблица и нажмите Далее.

4. На Шаге 2 подтвердите диапазон данных и нажмите Далее.
5. Появится дополнительное окно с вопросом об экономии памяти. Чтобы создать независимую таблицу с возможностью своей группировки, выберите Нет.

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

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

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

Для первого года данные в столбце изменения будут пустыми (нечего сравнивать). Этот столбец можно скрыть, щелкнув по нему правой кнопкой мыши и выбрав Скрыть.

Заключение

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