Год-к-году в сводной таблице


Сравнить год-к-году в сводной таблице (в абсолютных или относительных величинах) - одна из типовых ситуаций при анализе данных в Microsoft Excel.  Давайте разберём несколько подходов к решению такой задачи и плюсы-минусы каждого из них.

Предположим, что в качестве исходника мы имеем вот такую таблицу с данными по продажам чая и кофе за 2020-2022 гг.:

Исходные данные

В данном случае таблица отформатирована в "умную" динамическую командой Главная - Форматировать как таблицу (Home - Format as Table) чтобы иметь возможность в будущем легко добавлять к ней новые строки с продажами, но это не обязательно.

Само собой, сначала давайте построим по ней сводную таблицу стандартным образом, т.е. через вкладку Вставка - Сводная таблица (Insert - Pivot Table) и закинем категорию и наименование в строки, дату продажи - в столбцы и количество проданных штук - в область значений. От общего итога по строкам в последнем столбце можно пока избавиться, щёлкнув по нему правой кнопкой мыши и выбрав команду Удалить общие итоги (Remove grand totals). Получим примерно такую картину:

Готовая сводная для старта

Наша задача - добавить столбцы, где по каждому товару и категории в процентах (или в штуках - это даже проще) будет вычислена разница в продажах текущего года по сравнению с предыдущим. В финансовой отчетности такой параметр часто называют "Year-Over-Year" = "YoY" (год-к-году).

Способ 1. Внутри сводной дополнительным полем

Этот способ я бы, наверное, назвал самым оптимальным в большинстве случаев. Он состоит в следующем:

  1. Добавляем в область значений поле Продано ещё раз.
  2. Щёлкаем по созданному дубликату столбца правой кнопкой мыши и выбираем Дополнительные вычисления - Приведённое отличие (Show Values As - % Difference From), а затем в открывшемся окне задаём в качестве базового поля год, а в качестве базового элемента Назад (Previous)
  3. Лишний пустой столбец для первого года (у которого нет предыдущего) можно скрыть.

Чтобы убрать ошибки #ПУСТО! появляющиеся напротив товаров, у которых совсем не было продаж, можно щёлкнуть правой кнопкой по сводной и, выбрав команду Параметры сводной таблицы включить флажок Для ошибок отображать (Pivot Table Options - For errors show).

Для пущей красоты можно также щёлкнуть правой кнопкой мыши по получившимся значениям процентов и, выбрав команду Числовой формат (Number format), задать пользовательский формат:

Числовой формат для наглядности

Это сделает более наглядным отображение отрицательных и положительных значений:

Год к году в сводной со стрелками

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

Минус же в ограниченности списка возможных вычислений - в контекстном меню есть лишь возможность сравнить текущий период с предыдущим, последующим или произвольно выбранным. Если нужна разница не в относительных единицах (процентах), а в абсолютных (штуках, рублях), то вместо команды Приведённое отличие (% Difference from) можно выбрать Отличие (Difference from). Но никаких других более сложных вычислений сделать не удастся - их тут просто нет.

Способ 2. Снаружи сводной формулами

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

Год к году снаружи сводной формулами

Минус в том, что в случае изменения размеров таблицы после обновления, формулы придётся допротягивать на новые товары или годы (или, наоборот, убирать из лишних ячеек). К плюсам же этого способа можно отнести возможность легко считать не только разницу с прошлым годом, а вообще всё, что угодно: например, сравнить текущий год со средним за два предыдущих, с медианой, задавать веса каждому году и т.д.

Способ 3. Мерами в сводной по Модели Данных (Power Pivot)

Этот вариант чуть сложнее, но намного гибче и в разы мощнее, т.к. мы задействуем встроенную в Excel надстройку Power Pivot, которая, по сути, представляет собой движок базы данных + функционал имеющегося в ней языка для анализа данных - DAX. Минус же в том, что нам потребуется создать ещё одну вспомогательную таблицу - Таблицу Дат или Календарную Таблицу, которая будет использоваться как справочник при расчётах. Выглядеть она должна примерно следующим образом:

Таблица дат или Календарная таблица

В первом столбце здесь идут даты с шагом один день, без разрывов и повторов. Интервал дат обычно выбирается под задачу, т.е. в нашем случае можно взять с 1 января 2020 г. до 31 декабря 2022 г., т.к. все наши сделки были сделаны в этот период. Также для удобства будущего анализа при построении сводной, в таблицу дат можно добавить колонки с номером года, месяца, квартала, днем недели и т.п., но это уже опционально - обязательна лишь первая колонка с непрерывной последовательностью дней.

После создания календарной таблицы дадим ей имя (например, Calendar) на вкладке Конструктор (Design) и затем её можно загружать в Модель Данных на вкладке Power Pivot при помощи кнопки Добавить в Модель данных (Power Pivot - Add to Data Model).

Добавление таблицы в Модель Данных

Затем в открывшемся окне Power Pivot на вкладке Конструктор (Design) жмём на кнопку Пометить как таблицу дат (Mark as Date Table).

Совершенно аналогично грузим в Модель данных и нашу исходную таблицу продаж, только назовем её предварительно Sales.

После этого в окне Power Pivot переключаемся в Режим диаграммы (Diagram View) и создаём связь между таблицами, перетаскивая поле Дата продажи из таблицы Sales на соответствующее ему по смыслу поле Дата в таблице Calendar (или наоборот - это роли не играет):

Связываем таблицы в Модели Данных

Теперь, когда таблицы связаны, по ним можно строить сводную как по единому целому, нажав на вкладке Главная - Сводная таблица (Home - Pivot Table). В полученной сводной, как и ранее, закидываем категорию и товар в область строк, а поле Год из таблицы Calendar в область столбцов. А дальше самое интересное - нам нужно будет создать парочку мер (measures), которые и должны выполнять необходимые вычисления в области значений.

Для этого на вкладке Power Pivot жмём на кнопку Меры - Создать меру (Measures - New measure) и в открывшемся окне выбираем таблицу для хранения меры, а также вводим имя меры и её формулу. Нам потребуется создать три меры:

Мера для вычисления продаж в текущем году - просто суммируем все значения по столбцу Продано штук в таблице Sales:

Мера продаж за текущий год

Мера для вычисления продаж за предыдущий относительно текущего год - тут используем функцию CALCULATE, чтобы вычислить предыдущую меру с дополнительным фильтром в виде дат предыдущего года, которые нам даёт функция PREVIOUSYEAR:

Мера продаж за предыдущий год

Мера, которая будет вычислять разницу в процентах между двумя предыдущими мерами - здесь для безопасного деления используем функцию DIVIDE, чтобы подстраховаться на случай нуля в знаменателе:

Мера год к году в процентах

Закинув нужные нам меры в сводную получаем результат аналогичный предыдущим способам:

Результат в сводной

Ссылки по теме


08.08.2023 09:01:18
Николай, спасибо за статью!
Очень познавательно и интересно.
Небольшой вопрос. При формировании формулы последней меры в списке рядом с созданной таблицей Calendar был ещё пункт CALENDAR (21:21), как я понимаю, это что-то системное. Можно пару слов об этом? Что это, когда применимо, является ли заменой созданному справочнику? Заранее благодарю!
31.08.2023 11:31:07
Подскажите пожалуйста, как добавить Меру нарастающий итог, с начала проекта и до конца. У меня продажи идут с 2019 по 2023
20.09.2023 17:20:07
Да хранит вас Господь, Николай! Восхищаюсь вами, всем русскоговорящим вас рекомендую. Большей частью моих ексель-познаний я обязан вам! Благодарность и уважение. И привет из Баку :)
30.11.2023 16:11:21
Николай, как всегда, Вы на высоте! Всё понятно, просто и доступно, без лишней воды!
Огромная благодарность за такие видео уроки!!!
Наверх