Создание сводной таблицы из данных с многострочной шапкой в Excel с помощью Power Query
Видео показывает, как использовать Power Query для преобразования таблицы с многострочными заголовками и пустыми ячейками в плоскую таблицу, пригодную для создания сводной таблицы в Excel.
Как преобразовать таблицу с многоуровневыми заголовками в плоскую для построения сводной таблицы в Excel
Как подготовить таблицу с многоуровневыми заголовками для создания сводной таблицы в Excel?
Для этого нужно использовать надстройку Power Query: 1. Создать именованный диапазон для исходной таблицы 2. Загрузить данные в Power Query через Данные ->Из таблицы/диапазона 3. Удалить пустые строки, заполнить пропуски данными из вышестоящих ячеек 4. Объединить первые 3 столбца в один с разделителем (например, ;) 5. Транспонировать таблицу 6. Удалить пустые строки, заполнить пропуски в первых 2 столбцах 7. Развернуть остальные столбцы в строки (Отменить свертывание столбцов) 8. Разделить объединенный столбец обратно на 3 по разделителю 9. Переименовать столбцы 10. Загрузить данные в Excel как сводную таблицу или подключение
Что делает команда Отменить свертывание других столбцов в Power Query?
Эта команда (Unpivot Other Columns в англ. версии) развертывает все столбцы таблицы, кроме выделенных, в строки. Каждый бывший столбец становится двумя колонками: одна с названиями столбцов, другая - со значениями. Это превращает исходную широкую таблицу в плоскую, удобную для дальнейшего анализа с помощью сводных таблиц.
Как загрузить реобразованные данные из Power Query в виде сводной таблицы в Excel?
Есть два варианта: 1. В Excel 2016 и новее: - На вкладке Главная Power Query выбрать Закрыть и загрузить - В окне Импорт данных выбрать Отчет сводной таблицы и указать лист для загрузки 2. В Excel 2013 и старше: - На вкладке Главная Power Query выбрать Закрыть и загрузить - Выбрать Только создать подключение - На вкладке Вставка нажать Сводная таблица - Выбрать Использовать внешний источник данных и найти созданное подключение
Как создать именованный диапазон для исходной таблицы в Excel?
Есть несколько способов: 1. Выделить диапазон и в строке формул ввести имя, нажав Enter 2. Выделить диапазон, вкладка Формулы ->Создать из выделенной области 3. Вкладка Формулы ->Диспетчер имен ->Новое
Зачем нужно транспонировать таблицу в Power Query?
Транспонирование (обмен строк и столбцов) позволяет:
- Легко удалить пустые столбцы, которые превращаются в пустые строки
- Заполнить пропуски в первых двух столбцах (годы и кварталы) данными из вышестоящих ячеек
- Подготовить таблицу для последующего разворачивания остальных столбцов в строки