Глава 6. Обратный разбор сводной таблицы в Power Query

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава    Содержание    Следующая глава

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

Ris. 6.1. Otchet v formate svodnyj tablitsy

Рис. 6.1. Отчет в формате сводный таблицы (см. файл UnPivot.xlsx); чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Скачать заметку в формате Word или pdf, примеры в формате архива

До появления Power Query не существовало автоматического метода разбора сводной таблицы в вид, пригодный для последующей обработки.

Щелкните любую ячейку в диапазоне А4:I8 и создайте новый запрос: Данные –> Из таблицы/диапазона. (рис. 6.2). Подтвердите диапазон $A$4:$I$7 (без строки итогов). Этот диапазон на исходном листе Excel будет преобразован в Таблицу.

Ris. 6.2. Sozdanie zaprosa iz diapazona

Рис. 6.2. Создание запроса из диапазона

Данные импортируются в Power Query, и теперь их можно обработать. Цель – развернуть сводную таблицу, но прежде нужно удалить столбец с итогами. Щелкните правой кнопкой мыши столбец Total –> Удалить.

Выберите столбец 01.01.2014. Удерживая нажатой клавишу Shift, выберите столбец 01.07.2014. Щелкните правой кнопкой мыши один из выделенных столбцов –> Отменить свертывание столбцов.

Ris. 6.3. Vid zaprosa posle otmeny svertyvaniya stolbtsov

Рис. 6.3. Вид запроса после отмены свертывания столбцов

Отредактируйте запрос:

  • Щелкните правой кнопкой мыши столбец Атрибут –> Тип изменения –> Дата
  • Щелкните правой кнопкой мыши столбец Атрибут –> Переименовать –> Дата
  • Щелкните правой кнопкой мыши столбец Значение –> Переименовать –> Кол-во
  • Щелкните правой кнопкой мыши столбец Значение –> Тип изменения –> Целое число
  • Переименуйте запрос –> Ежедневные продажи
  • Главная –> Закрыть и загрузить

Обратите внимание, что в этом примере нет необходимости применять тип изменения с использованием локали (языкового стандарта, подробнее см. Глава 2. Изменение настроек Power Query, действующих по умолчанию). Поскольку данные находятся внутри Excel, Power Query корректно распознает эти данные независимо от ваших региональных настроек и того в каких настройках был подготовлен исходный файл.

На основании импортированных данных можно заново построить сводные таблицы с нужными вам срезами.

На самом деле Power Query включает три функции отмены свертывания столбцов:

Ris. 6.4. Funktsii otmeny svertyvaniya stolbtsov

Рис. 6.4. Функции отмены свертывания столбцов

В примере выше мы выбирали столбцы с 01.01.2014 по 01.07.2014 (см. рис. 6.2), а могли выбрать столбец Sales Category, и выполнить команду Отменить свертывание других столбцов. Такой подход отлично сработает, если в Таблицу будут добавлены новые столбцы. Обновление запроса «подхватит» новые данные.

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

Исходя из названия команды (Отменить свертывание столбцов), можно было ожидать, что при записи этого шага Power Query будет жестко кодировать названия выбранных столбцов. Однако, Power Query посмотрел на все столбцы в наборе данных и определил, что есть лишь один невыбранный столбец. Вместо команды «отменить свертывание выбранных 7 столбцов», Power Query фактически записала код, который говорит «отменить свертывание всех столбцов, кроме одного невыбранного».

Хотя это изменение кажется весьма тонким, оно имеет довольно большие последствия. Трудно ошибиться и построить сценарий, который перестанет работать при добавлении новых столбцов ежедневных продаж. Если же вы хотите, чтобы новые столбцы (добавленные в Таблицу позже) не попали под команду «отменить свертывание», воспользуйтесь опцией Отменить свертывание только для выбранных столбцов.

Любопытно, что в момент написания книги третья функция отмены свертывания отсутствовала, и авторы посвящают страницу тому, как с помощью редактирования кода можно всё же реализовать такую возможность. – Прим. Багузина.


Прокомментировать