Почему матрицы ломают сводные таблицы — и как одним действием Unpivot в Power Query исправить это. С примерами из бюллетеней ЦБ, данных по добыче нефти и корпоративных выгрузок.
Матрица — таблица, в которой измерение (месяц, год, город) разнесено по столбцам. Корпоративные выгрузки, бюллетени, статистика по отраслям — всё это обычно приходит именно так. На занятиях мы называем эту ситуацию «измерение оказалось в столбцах». Сводные таблицы с матрицей не работают, им нужна плоская таблица с одним значением в строке. Power Query решает это одним действием — через рассведение.
На английском сводная таблица — pivot table, а обратный инструмент называется unpivot. В русском интерфейсе его перевели как «Отменить свёртывание столбцов» — не самое интуитивное название. Дальше в статье будем использовать термин рассведение.
Почему матрица не подходит для сводной
Возьмём данные по добыче нефти из Википедии — добыча по странам в тысячах баррелей в день.
| Страна | 2020 | 2021 | 2022 |
|---|---|---|---|
| США | 11 283 | 11 254 | 11 911 |
| Саудовская Аравия | 9 213 | 9 122 | 10 591 |
| Россия | 9 460 | 9 876 | 9 941 |
Для сводной это три отдельных столбца с цифрами. Поле для сравнения назначить не получается — «2020» не является полем, это заголовок. Нужно преобразовать к виду, где страна, год и значение добычи стоят в отдельных столбцах.
| Страна | Год | Добыча |
|---|---|---|
| США | 2020 | 11 283 |
| США | 2021 | 11 254 |
| США | 2022 | 11 911 |
| Саудовская Аравия | 2020 | 9 213 |
| … | … | … |
Теперь сводная может считать добычу по стране, по году, сравнивать периоды — и всё это без переработки исходника.
Пошаговая инструкция
Шаг 1. Загружаем данные в Power Query
Вкладка «Данные» → «Создать запрос» → «Из других источников» → «Из интернета». Вставляем ссылку на страницу Википедии, выбираем нужную таблицу и нажимаем «Преобразовать данные» — в разных версиях кнопка может называться также «Преобразовать» или «Изменить». Открывается редактор Power Query.
Шаг 2. Повышаем заголовки
Если первая строка содержит названия столбцов, на вкладке «Преобразование» нажимаем «Использовать первую строку в качестве заголовков».
Шаг 3. Рассведение
Выделяем столбцы с годами — те, которые нужно «развернуть». Вкладка «Преобразование» → «Отменить свёртывание столбцов». Иконка — из 6 ячеек в 9.
В меню три варианта.
- «Отменить свёртывание столбцов» — рассводит выделенные столбцы. Если в источнике позже появятся новые, они тоже будут рассведены автоматически.
- «Отменить свёртывание других столбцов» — оставляет выделенные как колонки, все остальные рассводит. Самый частый вариант на практике. Когда оставляемых столбцов мало (скажем, один «Страна»), а разворачиваемых много и они уходят за горизонт — проще выделить «Страна» и нажать эту кнопку.
- «Отменить свёртывание только выделенных столбцов» — рассводит только конкретные выбранные столбцы. Новые столбцы в источнике останутся колонками.
После рассведения появляются столбцы «Атрибут» и «Значение». Переименовываем их двойным кликом на заголовок — «Год» и «Добыча».
Шаг 4. Типы данных и индекс
Столбец «Год» сейчас текст. Меняем на «Целое число». Столбец «Добыча» — на «Десятичное число». Типы данных назначаются сразу после рассведения, иначе формулы в дальнейшем могут не работать.
Если нужна нумерация строк — вкладка «Добавление столбца» → «Столбец индекса» → стрелочка вниз → «От 1». По умолчанию Power Query нумерует с нуля, как программисты — для аналитики это неудобно.
Последний шаг — «Главная» → «Закрыть и загрузить». Данные уезжают в Excel.
Не путать с транспонированием
Визуально рассведение похоже на транспонирование — была широкая таблица, стала высокая. Но это совершенно разные операции. При транспонировании таблица просто «падает» на бок. Строки становятся столбцами и наоборот, данные остаются в матричном виде, работать с ними в сводной по-прежнему нельзя. Рассведение переводит измерение из столбцов в строки и создаёт нормальную плоскую таблицу.
Объединённые ячейки и «Заполнить вниз»
Бюллетени ЦБ, статистика Переписи, отраслевые сводки часто приходят с объединёнными ячейками — название банка занимает одну ячейку, а под ней данные по нескольким показателям. Объединённая ячейка — неверный способ хранения. Старайтесь такого в принципе не делать. Но файл уже пришёл в таком виде.
Power Query при загрузке разбивает объединение. Текст остаётся в первой строке, остальные — пустые. Решается через «Заполнить вниз». Выделяем столбец с пропусками → вкладка «Преобразование» → «Заполнить» → «Вниз». Каждая пустая ячейка получит значение из ближайшей заполненной выше. После этого можно применять рассведение.
Комбо. Рассведение + сведение
Бывает, что в одном столбце оказались и рубли, и проценты, и портфель, и ставка. Работать с такими данными невозможно — ни среднее посчитать, ни сумму. Нужно развести значения по отдельным столбцам.
Типичная цепочка действий на примере банковской таблицы. Сначала заполняем вниз столбец с объединёнными ячейками. Затем выделяем столбцы городов → Преобразование → Отменить свёртывание столбцов. Получается четыре столбца. банк, показатель, город, значение. Затем выделяем столбец «показатель» → Преобразование → «Столбец сведения». Power Query спрашивает, из какого столбца брать значения — ставим «значение», нажимаем ОК. Каждое уникальное значение из столбца «показатель» (портфель, ставка, резерв) становится отдельным столбцом.
Сведение. Обратная операция
Если нужно обратное преобразование — из плоской таблицы в матрицу — используется «Столбец сведения» (Pivot Column) на вкладке «Преобразование». Иконка обратная рассведению. Из 9 ячеек в 6. Выбираем столбец, уникальные значения которого станут заголовками, указываем столбец значений — и Power Query разворачивает данные обратно в широкую таблицу.
Двухуровневая шапка. Когда объединены не строки, а столбцы
Бывает ситуация ещё хуже — объединена шапка. Год тянется по строке, банки — по столбцам. «Заполнить вниз» здесь не поможет, потому что заполнение по строкам в Power Query не предусмотрено.
Шаг-костыль. Преобразование → Транспонировать. Грубо говоря, «роняем таблицу» — строки и столбцы меняются местами. Теперь год стал столбцом, и «Заполнить вниз» работает. Дальше повышаем заголовки, выделяем столбцы банков → Преобразование → Отменить свёртывание столбцов. Переименовываем, добавляем индекс от 1.
Для разовых преобразований не всегда имеет смысл записывать цепочку в Power Query. Если вы не знаете про трюк с транспонированием — не сможете. Иногда проще поправить шапку руками за пару минут.
Куда двигаться дальше
Рассведение — одна из самых частых операций при работе с корпоративными данными. На неё приходится до 90% всех преобразований матричных выгрузок. В сочетании с очисткой текста, merge-запросами, импортом из папки Power Query позволяет обрабатывать практически любые выгрузки без макросов.
Все эти приёмы разбираются на курсе Power Query и входят в программу Excel Эксперт.