Год-к-году в сводной таблице
Сравнить год-к-году в сводной таблице (в абсолютных или относительных величинах) - одна из типовых ситуаций при анализе данных в Microsoft Excel. Давайте разберём несколько подходов к решению такой задачи и плюсы-минусы каждого из них.
Предположим, что в качестве исходника мы имеем вот такую таблицу с данными по продажам чая и кофе за 2020-2022 гг.:
В данном случае таблица отформатирована в "умную" динамическую командой Главная - Форматировать как таблицу (Home - Format as Table) чтобы иметь возможность в будущем легко добавлять к ней новые строки с продажами, но это не обязательно.
Само собой, сначала давайте построим по ней сводную таблицу стандартным образом, т.е. через вкладку Вставка - Сводная таблица (Insert - Pivot Table) и закинем категорию и наименование в строки, дату продажи - в столбцы и количество проданных штук - в область значений. От общего итога по строкам в последнем столбце можно пока избавиться, щёлкнув по нему правой кнопкой мыши и выбрав команду Удалить общие итоги (Remove grand totals). Получим примерно такую картину:
Наша задача - добавить столбцы, где по каждому товару и категории в процентах (или в штуках - это даже проще) будет вычислена разница в продажах текущего года по сравнению с предыдущим. В финансовой отчетности такой параметр часто называют "Year-Over-Year" = "YoY" (год-к-году).
Способ 1. Внутри сводной дополнительным полем
Этот способ я бы, наверное, назвал самым оптимальным в большинстве случаев. Он состоит в следующем:
- Добавляем в область значений поле Продано ещё раз.
- Щёлкаем по созданному дубликату столбца правой кнопкой мыши и выбираем Дополнительные вычисления - Приведённое отличие (Show Values As - % Difference From), а затем в открывшемся окне задаём в качестве базового поля год, а в качестве базового элемента Назад (Previous).
- Лишний пустой столбец для первого года (у которого нет предыдущего) можно скрыть.
Чтобы убрать ошибки #ПУСТО! появляющиеся напротив товаров, у которых совсем не было продаж, можно щёлкнуть правой кнопкой по сводной и, выбрав команду Параметры сводной таблицы включить флажок Для ошибок отображать (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, чтобы подстраховаться на случай нуля в знаменателе:
Закинув нужные нам меры в сводную получаем результат аналогичный предыдущим способам:
Ссылки по теме
- План-факт анализ в сводной таблице с Power Pivot и Power Query
- Преимущества сводной по Модели данных из Power Pivot по сравнению с обычной сводной таблицей Excel
- Настройка вычислений в сводных таблицах
Очень познавательно и интересно.
Небольшой вопрос. При формировании формулы последней меры в списке рядом с созданной таблицей Calendar был ещё пункт CALENDAR (21:21), как я понимаю, это что-то системное. Можно пару слов об этом? Что это, когда применимо, является ли заменой созданному справочнику? Заранее благодарю!
Огромная благодарность за такие видео уроки!!!