Power Query умеет подключаться к разным источникам. Далее рассмотрим, как получить данные из книги Excel.
Таблицы Excel
Лучше всего данные хранить в таблице Excel, это самый удобный и распространенный источник для Power Query. На ленте даже есть специальная кнопка.
Чтобы загрузить таблицу в редактор Power Query, достаточно выделить любую ее ячейку и нажать Данные → Получить и преобразовать данные → Из таблицы/диапазона.
Примечание. В вашей версии Excel расположение кнопок и их названия могут отличаться.
Если то же самое проделать с обычным диапазоном, то Excel вначале преобразует диапазон в таблицу Excel, а потом запустит Power Query.
Запросу присваивается имя таблицы Excel, которая является источником данных. Поэтому желательно сразу дать таблице говорящее название. Не обязательно, конечно, но желательно. В противном случае рекомендуется переименовать сам запрос, чтобы затем его можно было легко найти среди других запросов книги.
Данные находятся в Power Query. Новые значения, внесенные в исходную таблицу, автоматически попадут в запрос после его обновления. Далее в редакторе Power Query делают обработку данных и выгружают либо в виде таблицы Excel, либо оставляют в памяти Excel в виде подключения.
Именованный диапазон Excel
Источником для Power Query может быть не только таблица Excel. Например, вы получили красивый отформатированный отчет и не хотите вносить в него изменения. Тогда нужно использовать именованный диапазон. Самый простой способ создать именованный диапазон – это выделить область на листе и ввести название в поле Имя.
Либо выполнить команду Формулы → Определенные имена → Присвоить имя. В Excel будет создан новый объект, к которому можно обращаться, например, в формулах. Диапазон виден в Диспетчере имен.
Здесь перечислены все именованные диапазоны, формулы и таблицы. Среди них есть и только что созданный Отчет.
Теперь можно стать на любую ячейку внутри именованного диапазона (или выбрать его из выпадающего списка в поле Имя) и вызвать ту же команду: Данные → Получить и преобразовать данные → Из таблицы/диапазона. Произойдет загрузка данных в Power Query.
Такой способ позволяет «не портить» исходные данные. Но у него есть и очевидный недостаток: новые строки, которые выйдут за пределы именованного диапазона, не попадут в запрос.
Динамический именованный диапазон Excel
Решить данную проблему можно, создав динамический именованный диапазон. Это такой диапазон, который задается формулой и автоматически расширяется до последней заполненной ячейки.
Внести статичное имя в поле Имя на этот раз не получится. Поэтому заходим в Формулы → Определенные имена → Задать имя (или нажимаем Создать в Диспетчере имен), указываем название будущего динамического диапазона ДинамОтчет и внизу вместо ссылки записываем формулу:
=$A$2:ИНДЕКС($B:$B;ПОИСКПОЗ(99^99;$B:$B))
Ко всем ссылкам этой формулы Excel еще автоматически добавит название листа.
Смысл формулы следующий. Верхняя левая ячейка диапазона фиксируется ($A$2), а правая нижняя определяется формулой, которая возвращает адрес последней заполненной строки в столбце B.
Но не все так просто. Excel видит это имя лишь как формулу, а не диапазон. Как же его увидит Power Query? Делаем ход конем.
Создаем пустой запрос Power Query Данные → Получить и преобразовать данные → Получить данные → Из других источников → Пустой запрос. Открывается пустой запрос, где в строке формул нужно ввести:
= Excel.CurrentWorkbook()
После ввода формулы (нажатием Enter) Power Query обратится к текущей книге и выведет все объекты, среди которых есть и наш динамический диапазон ДинамОтчет.
Название запроса не подхватывается, поэтому придется изменить самостоятельно.
Чтобы извлечь содержимое объекта, в этой же строке правой кнопкой мыши кликаем по Table, далее выбираем Детализация.
Power Query разворачивает таблицу и даже делает некоторые шаги обработки: повышает заголовки и задает нужный формат для столбцов.
Теперь в запрос будут попадать новые строки, несмотря на то, что исходные данные не являются таблицей Excel.
Вот такие приемы импорта данных в Power Query из книги Excel. Самый распространенный из них – это импорт из таблицы Excel. Тем не менее, в случае необходимости можно прибегнуть к альтернативам, создав именованный или динамический именованный диапазон.
Консолидация данных из разных таблиц Excel
Одна из насущных задач, с которыми сталкиваются пользователи, – консолидация данных. Под консолидацией понимается объединение нескольких таблиц в одну. До появления Power Query это была довольно трудоемкая операция, особенно, если процесс требовал автоматизации. Хотя в эксель есть специальная команда Данные → Работа с данными → Консолидация, пользоваться ей не удобно. Мне, по крайней мере. Появление Power Query в корне изменило представление о том, как нужно объединять таблицы.
Рассмотрим пример. В некоторый файл каждый месяц вносится отчет о продажах в формате таблицы Excel. Каждая таблица при этом имеет соответствующее название: Январь_2018, Февраль_2018 и т.д. Необходимо объединить все таблицы книги в одну. Как бы скопировать и вставить одну под другой, создав при этом дополнительный столбец, указывающий, к какой таблице принадлежит конкретная строка. Задача не одноразовая, а с заделом на будущее, поэтому нужно предусмотреть появление в этом файле новых таблиц.
Процесс начинается с запуска пустого запроса: Данные → Получить и преобразовать данные → Создать запрос → Из других источников → Пустой запрос
Затем в строке формул вводим знакомую команду
= Excel.CurrentWorkbook()
Power Query показывает все таблицы в текущей книге.
Их нужно развернуть кнопкой с двумя стрелками в названии поля Content (на скриншоте ниже выделено красным кружком).
Если есть лишние столбцы, то их можно не выводить, сняв соответствующую галку. Также лучше убрать галку напротив опции Использовать исходное имя столбца как префикс. Нажимаем Ok.
Все таблицы находятся на одном листе, а рядом колонка с названием источника, откуда взята каждая строка.
Данные загружены. Можно приступать к их обработке. Ограничимся преобразованием названий таблиц в настоящую дату, чтобы затем использовать для сведения данных по месяцам.
Визуально мы наблюдаем и месяц, и год. Но Power Query такое название воспринимает, как текст. Поэтому делаем следующее.
Удалим нижнее подчеркивание. Правой кнопкой мыши по названию столбца Name → Замена значений.
В следующем окне настроек указываем, что меняем _ на пусто, то есть в нижнем поле ничего не указываем.
Подчеркивание удаляется из названия.
Поиск и замена здесь работает так же, как и в обычном Excel.
Далее запускаем команду Преобразование → Столбец «Дата и время» → Дата → Выполнить анализ.
Power Query распознает дату и меняет формат колонки. Мы также переименовываем столбец на Период.
Полученную таблицу можно использовать для анализа данных. Выгрузим ее на лист Excel.
Главная → Закрыть и загрузить.
Но что-то пошло не так. Во-первых, внизу таблицы пустая строка; во-вторых, при выгрузке произошла одна ошибка. Обновим запрос (справа от названия запроса значок обновления).
Что-то еще больше пошло не так. Даты исчезли, снизу таблицы добавились новые строки, а количество ошибок уже 19. Спокойствие, только спокойствие! Дело вот в чем.
Помните, на первом шаге мы получили все таблицы из файла? Так ведь и выгруженная таблица – это тоже таблица! Получается, Power Query взял 3 исходных таблицы, обработал, выгрузил на лист Excel и на следующем круге видит уже 4 таблицы!
При повторном обновлении запрос захватывает их все, а т.к. таблица выхода имеет другую структуру, то возникают ошибки.
Короче, из запроса нужно исключить таблицу, которая получается на выходе (Запрос1). Есть разные подходы, самый простой – это добавить шаг фильтрации. Выделяем в правой панели первый шаг Источник, открываем фильтр в колонке с названиями, снимаем галку с таблицы Запрос1 → Ok.
Снова выгружаем таблицу в Excel и на этот раз все в порядке.
Сделаем с помощью сводной таблицы маленький отчет по месяцам.
Прошло время, и в файл добавили новую таблицу с продажами за апрель.
Требуется обновить сводный отчет. Представьте на минуту, как это происходит в обычном Эксель: таблица копируется в самый низ общего источника, продлевается колонка с датой, изменяется диапазон для сводной таблицы, обновляется весь отчет.
А вот, как это выглядит при использовании Power Query.
Достаточно два раза нажать кнопку Обновить все (первый раз – для обновления запроса, второй – для сводной таблицы).
На добавление в отчет новых данных вместе с их обработкой потребовалось несколько секунд.
Вот за это мы так любим Power Query.
Добрый день, подскажите пожалуйста, название кнопки «из таблицы» присутствует. Пошагово выполнил все инструкции по созданию динамического именованного диапазона, но при попытке запроса не выдаёт в списке динамический диапазон, а таблицу видит. В чем может быть причина? Так же скачал ваш пример эффект тотже..
Подскажите, Power Query не видит созданный динамический диапазон в чем может быть проблема?
Спасибо!
А что делать если «Закрыть и загрузить в» неактивна? Работает только закрыть и загрузить. Как выгрузить табличку не через сводную?
Подскажите, пожалуйста, если необходимо создать таблицу с помощью обработки Power Querry, потом добавить в нее колонки и вносить данные вручную. Как при использовании команды «обновить» или «обновить все» и добавлении новых данных в таблицу сохранить данные в строках, заполненных ранее?