Процентное изменение в Сводной таблице Excel

TL;DR

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

Быстрые ссылки

• Форматирование диапазона как таблицы

• Создание сводной таблицы для отображения процентного изменения

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

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

Вот исходный лист данных, который будем использовать:

Это типичный лист продаж: дата заказа, имя клиента, менеджер по продажам, сумма продажи и т. п.

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

Форматирование диапазона как таблицы

Если ваш диапазон ещё не оформлен как таблица, рекомендуется это сделать. Таблицы дают преимущества при работе со сводными таблицами: динамические диапазоны, понятные имена столбцов и простота ссылки в формулах.

1. Выделите диапазон ячеек с данными.
2. В меню выберите Вставка > Таблица.

Убедитесь, что диапазон указан верно и что у вас есть заголовки в первой строке. Нажмите ОК.

Таблица создана. Дайте ей имя: это облегчает ссылку на данные при создании сводных таблиц, диаграмм и формулах. На ленте откройте вкладку Конструктор (Table Tools) и введите имя таблицы в поле слева. В примере таблица называется Sales.

Можно также выбрать стиль таблицы в тех же настройках.

Создание сводной таблицы для отображения процентного изменения

Находясь в таблице, выберите Вставка > Сводная таблица.

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

Группировка дат по месяцам

Перетащите поле с датой (в примере Order Date) в область Строк сводной таблицы. Начиная с Excel 2016, даты по умолчанию группируются по годам, кварталам и месяцам.

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

Выберите нужные группы (в примере — Годы и Месяцы).

После группировки появятся отдельные поля Год и Order Date (месяцы).

Добавление полей значений в сводную таблицу

Перенесите поле Год из области Строк в область Фильтров. Это позволит фильтровать отчет по году, не загромождая сводную таблицу.

Перетащите поле с суммой продаж (Total sales Value) в область Значения дважды — один раз для отображения абсолютных сумм и второй для расчёта процента изменения.

Оба поля по умолчанию агрегируются суммой и пока не отформатированы.

Правый клик по числу в первом столбце > Формат ячеек, выберите формат Бухгалтерский (0 знаков после запятой). Это оставит первый столбец в виде удобных сумм.

Создание столбца с процентным изменением

Щелкните правой кнопкой по значению во втором столбце, наведите на Показать значения как, и выберите опцию % Разница по отношению к.

В качестве Базового элемента выберите Предыдущее (Previous). Это заставит текущий месяц сравниваться с предыдущим.

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

Переименуйте заголовки: в ячейке с метками строк напишите “Месяц”, а для второго столбца значений — “Отклонение” (Variance).

Добавление стрелок отклонения (иконок)

Чтобы визуализировать изменение, можно применить условное форматирование с иконками (зелёные/красные стрелки или треугольники).

1. Выделите любое значение во втором столбце (Отклонение).
2. На ленте выберите Главная > Условное форматирование > Создать правило.
3. В открывшемся окне:
   • В разделе выберите Все ячейки, показывающие значения “Отклонение” для поля Order Date.
   • В Тип формата выберите Набор значков.
   • Выберите набор красный/янтарный/зелёный треугольник.
   • В столбце Тип поменяйте значение на Число, чтобы пороги стали числами (0, 0 и т. п.).

Нажмите ОК — и условное форматирование применится.

Сводные таблицы — простой и эффективный способ показать процентное изменение во времени.

Мини-методология: быстрый набор действий (шпаргалка)

• 1. Оформите данные как таблицу (Вставка > Таблица).
• 2. Вставьте Сводную таблицу (Вставка > Сводная таблица).
• 3. Перетащите дату в Строки и сгруппируйте по месяцам.
• 4. Перетащите поле суммы в Значения дважды.
• 5. Первое значение отформатируйте как валютное, второе — Показать значения как % Разница по отношению к Предыдущему.
• 6. При желании добавьте Условное форматирование с иконками.

Когда этот метод не подойдёт (ограничения и крайние случаи)

• Даты в виде текста: если даты — текстовые строки, Excel не будет группировать. Сначала преобразуйте текст в даты (Текст по столбцам или DATEVALUE).
• Неполные периоды: если месяца отсутствуют, сравнение с предыдущим покажет пропуски или неверные базовые значения.
• Скользящие окна не по предыдущему месяцу: если нужно сравнивать с предыдущим годом или средним скользящим, настройте “Показать значения как” или используйте вычисляемое поле/формулы за пределами сводной таблицы.
• Большие объемы и скорость: при очень больших таблицах сводная таблица может тормозить; рассмотрите Power Pivot или агрегированные представления.

Альтернативные подходы

• Формулы на листе: используйте SUMIFS и формулы для расчёта сумм по месяцам и затем обычную формулу процентного изменения.
• Power Query + Power Pivot: удобнее для ETL-процессов и работы с большими наборами данных.
• Визуализация в диаграммах: после сводной таблицы создайте диаграмму с линиями и добавьте надписи-значения или разницы.

Чеклист ролей (быстрая проверка перед публикацией отчёта)

Аналитик:

• Данные оформлены как таблица и проверены на дубликаты.
• Даты корректно распознаны Excel.
• Сводная таблица показывает оба столбца: суммы и процент.

Менеджер/заказчик:

• Отчёт фильтруется по году.
• Заголовки понятны (Месяц, Сумма, Отклонение).
• Условное форматирование читабельно и соответствует цветам компании.

IT/специалист BI:

• Отслежены источники данных и обновляемость.
• Выполнено тестирование производительности при объёмах данных.

Критерии приёмки

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

Советы и тонкости

• Если Excel скрывает автоматическую группировку дат, убедитесь, что столбец действительно имеет формат Дата и что в сводной таблице нет смешанных типов значений.
• При сравнении с предыдущим периодом учтите сезонность: сравнение январь-январь может быть релевантнее, чем сравнение с предыдущим календарным месяцем.
• Для отображения нулей и пустых значений используйте параметры сводной таблицы — Параметры сводной таблицы > Макет и формат > Для пустых ячеек показать: 0 (или другой знак).

Совместимость и миграция

• Excel 2016 и новее автоматически группируют даты. В Excel 2013 и старше может потребоваться ручная группировка.
• Excel Online поддерживает сводные таблицы, но функциональность группировки/условного форматирования может быть ограничена.
• Для больших наборов данных рассмотрите Power Pivot или Power BI, если нужна более гибкая модель данных.

Глоссарий (одно предложение на термин)

• Сводная таблица — интерактивный инструмент Excel для агрегирования и анализа данных.
• Группировка дат — объединение дат в периоды (месяцы, кварталы, годы).
• Показать значения как — способ представления вычисленного поля в сводной таблице (например, в процентах).

Краткое резюме

• Преобразуйте данные в таблицу и создайте сводную таблицу.
• Добавьте поле сумм дважды: одно для абсолютных значений, второе для % изменения относительно предыдущего.
• Отформатируйте значения и примените условное форматирование для наглядности.

Важно: перед публикацией отчёта проверьте типы данных и отсутствие пропущенных периодов.