Импорт и консолидация таблиц Excel через Power Query - statanaliz.info

Импорт и консолидация таблиц Excel через Power Query

Power Query умеет подключаться к разным источникам. Далее рассмотрим, как получить данные из книги Excel.

Таблицы Excel

Лучше всего данные хранить в таблице Excel, это самый удобный и распространенный источник для Power Query. На ленте даже есть специальная кнопка.

Чтобы загрузить таблицу в редактор Power Query, достаточно выделить любую ее ячейку и нажать Данные → Получить и преобразовать данные → Из таблицы/диапазона.

Импорт данных в Power Query из таблицы Excel

Примечание. В вашей версии Excel расположение кнопок и их названия могут отличаться.

Если то же самое проделать с обычным диапазоном, то Excel вначале преобразует диапазон в таблицу Excel, а потом запустит Power Query.

Окно редактора Power Query

Запросу присваивается имя таблицы Excel, которая является источником данных. Поэтому желательно сразу дать таблице говорящее название. Не обязательно, конечно, но желательно. В противном случае рекомендуется переименовать сам запрос, чтобы затем его можно было легко найти среди других запросов книги.

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

Выгрузка данных из Power Query

Именованный диапазон Excel

Источником для Power Query может быть не только таблица Excel. Например, вы получили красивый отформатированный отчет и не хотите вносить в него изменения. Тогда нужно использовать именованный диапазон. Самый простой способ создать именованный диапазон – это выделить область на листе и ввести название в поле Имя.

Создание именованного диапазона через поле Имя

Либо выполнить команду Формулы → Определенные имена → Присвоить имя. В Excel будет создан новый объект, к которому можно обращаться, например, в формулах. Диапазон виден в Диспетчере имен.

Диспетчер имен

Здесь перечислены все именованные диапазоны, формулы и таблицы. Среди них есть и только что созданный Отчет.

Теперь можно стать на любую ячейку внутри именованного диапазона (или выбрать его из выпадающего списка в поле Имя) и вызвать ту же команду: Данные → Получить и преобразовать данные → Из таблицы/диапазона. Произойдет загрузка данных в Power Query.

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 обратится к текущей книге и выведет все объекты, среди которых есть и наш динамический диапазон ДинамОтчет.

Все объекты с данными книги Excel в виде списка

Название запроса не подхватывается, поэтому придется изменить самостоятельно.

Чтобы извлечь содержимое объекта, в этой же строке правой кнопкой мыши кликаем по Table, далее выбираем Детализация.

Детализация

Power Query разворачивает таблицу и даже делает некоторые шаги обработки: повышает заголовки и задает нужный формат для столбцов.

Загрузка динамического диапазона

Теперь в запрос будут попадать новые строки, несмотря на то, что исходные данные не являются таблицей Excel.

Вот такие приемы импорта данных в Power Query из книги Excel. Самый распространенный из них – это импорт из таблицы Excel. Тем не менее, в случае необходимости можно прибегнуть к альтернативам, создав именованный или динамический именованный диапазон.

Консолидация данных из разных таблиц Excel

Одна из насущных задач, с которыми сталкиваются пользователи, – консолидация данных. Под консолидацией понимается объединение нескольких таблиц в одну. До появления Power Query это была довольно трудоемкая операция, особенно, если процесс требовал автоматизации. Хотя в эксель есть специальная команда Данные → Работа с данными → Консолидация, пользоваться ей не удобно. Мне, по крайней мере. Появление Power Query в корне изменило представление о том, как нужно объединять таблицы.

Рассмотрим пример. В некоторый файл каждый месяц вносится отчет о продажах в формате таблицы Excel. Каждая таблица при этом имеет соответствующее название: Январь_2018, Февраль_2018 и т.д. Необходимо объединить все таблицы книги в одну. Как бы скопировать и вставить одну под другой, создав при этом дополнительный столбец, указывающий, к какой таблице принадлежит конкретная строка. Задача не одноразовая, а с заделом на будущее, поэтому нужно предусмотреть появление в этом файле новых таблиц.

Процесс начинается с запуска пустого запроса: Данные → Получить и преобразовать данные → Создать запрос → Из других источников → Пустой запрос

Затем в строке формул вводим знакомую команду

= Excel.CurrentWorkbook()

Power Query показывает все таблицы в текущей книге.

Все таблицы в книге Excel

Их нужно развернуть кнопкой с двумя стрелками в названии поля Content (на скриншоте ниже выделено красным кружком).

Кнопка для разворачивания таблиц

Если есть лишние столбцы, то их можно не выводить, сняв соответствующую галку. Также лучше убрать галку напротив опции Использовать исходное имя столбца как префикс. Нажимаем Ok.

Все таблицы в Power Query

Все таблицы находятся на одном листе, а рядом колонка с названием источника, откуда взята каждая строка.

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

Визуально мы наблюдаем и месяц, и год. Но Power Query такое название воспринимает, как текст. Поэтому делаем следующее.

Удалим нижнее подчеркивание. Правой кнопкой мыши по названию столбца Name → Замена значений.

Замена

В следующем окне настроек указываем, что меняем _ на пусто, то есть в нижнем поле ничего не указываем.

Заполнение окна для замены

Подчеркивание удаляется из названия.

Удаленное подчеркивание

Поиск и замена здесь работает так же, как и в обычном Excel.

Далее запускаем команду Преобразование → Столбец «Дата и время» → Дата → Выполнить анализ.

Анализ текста для преобразования в дату

Power Query распознает дату и меняет формат колонки. Мы также переименовываем столбец на Период.

Поле с датой

Полученную таблицу можно использовать для анализа данных. Выгрузим ее на лист Excel.
Главная → Закрыть и загрузить.

Выгруженная таблица с ошибками

Но что-то пошло не так. Во-первых, внизу таблицы пустая строка; во-вторых, при выгрузке произошла одна ошибка. Обновим запрос (справа от названия запроса значок обновления).

После обновления запроса ошибок еще больше

Что-то еще больше пошло не так. Даты исчезли, снизу таблицы добавились новые строки, а количество ошибок уже 19. Спокойствие, только спокойствие! Дело вот в чем.

Помните, на первом шаге мы получили все таблицы из файла? Так ведь и выгруженная таблица – это тоже таблица! Получается, Power Query взял 3 исходных таблицы, обработал, выгрузил на лист Excel и на следующем круге видит уже 4 таблицы!

Таблица выхода в общем запросе

При повторном обновлении запрос захватывает их все, а т.к. таблица выхода имеет другую структуру, то возникают ошибки.

Короче, из запроса нужно исключить таблицу, которая получается на выходе (Запрос1). Есть разные подходы, самый простой – это добавить шаг фильтрации. Выделяем в правой панели первый шаг Источник, открываем фильтр в колонке с названиями, снимаем галку с таблицы Запрос1 → Ok.

Выгруженная таблица без ошибок

Снова выгружаем таблицу в Excel и на этот раз все в порядке.

Выгруженная таблица с запросом без ошибок

Сделаем с помощью сводной таблицы маленький отчет по месяцам.

Сводная таблица по результатам запроса Power Query

Прошло время, и в файл добавили новую таблицу с продажами за апрель.

Продажи за следующий месяц

Требуется обновить сводный отчет. Представьте на минуту, как это происходит в обычном Эксель: таблица копируется в самый низ общего источника, продлевается колонка с датой, изменяется диапазон для сводной таблицы, обновляется весь отчет.

А вот, как это выглядит при использовании Power Query.

Обновление отчета из Power Query

Достаточно два раза нажать кнопку Обновить все (первый раз – для обновления запроса, второй – для сводной таблицы).

Обновленный отчет на основе запроса Power Query

На добавление в отчет новых данных вместе с их обработкой потребовалось несколько секунд.

Вот за это мы так любим Power Query.

↓ Скачать файл с примером ↓

Поделиться в социальных сетях:
  • Аноним

    Добрый день, подскажите пожалуйста, название кнопки «из таблицы» присутствует. Пошагово выполнил все инструкции по созданию динамического именованного диапазона, но при попытке запроса не выдаёт в списке динамический диапазон, а таблицу видит. В чем может быть причина? Так же скачал ваш пример эффект тотже..

  • Аноним

    Подскажите, Power Query не видит созданный динамический диапазон в чем может быть проблема?

  • Аноним

    Спасибо!

  • Alisa Antonova

    А что делать если «Закрыть и загрузить в» неактивна? Работает только закрыть и загрузить. Как выгрузить табличку не через сводную?

  • Аноним

    Подскажите, пожалуйста, если необходимо создать таблицу с помощью обработки Power Querry, потом добавить в нее колонки и вносить данные вручную. Как при использовании команды «обновить» или «обновить все» и добавлении новых данных в таблицу сохранить данные в строках, заполненных ранее?

  • https://www.gate.io/signup/612995 gate.io app

    Great article! Your article helped me a lot. Thanks! will you allow it I want to share your article to my website:
    gate.io app

  • https://www.binance.com/de-CH/register?ref=E9W2VJFS Binance

    Thank you for your sharing. I am worried that I lack creative ideas. It is your article that makes me full of hope. Thank you. But, I have a question, can you help me?

  • https://www.gate.io/tr/signup/XlQVXVo gate io giriş

    After reading your article, I have some doubts about gate.io. I don’t know if you’re free? I would like to consult with you. thank you.

  • https://lookbook.nu/20betpt 20bet

    Your article gave me a lot of inspiration, I hope you can explain your point of view in more detail, because I have some doubts, thank you.

  • https://elitepipeiraq.com/ مصنع HDPE

    Excellent blog here! Also your website loads up very fast! What web host are you using? Can I get your affiliate link to your host? I wish my web site loaded up as quickly as yours lol

  • https://elitepipeiraq.com/ Pipe fittings supplier

    Hello my loved one! I want to say that this post is amazing, great written and include almost all significant infos. I would like to look extra posts like this.

  • https://bwerpipes.com/ bwer irrigation system iraq

    Normally I do not read article on blogs, however I would like to say that this write-up very forced me to try and do so! Your writing style has been amazed me. Thanks, quite great post.

  • https://bwerpipes.com/ bwer Upvc Weld Iraq

    Nice blog here! Also your site loads up fast! What host are you using? Can I get your affiliate link to your host? I wish my web site loaded up as quickly as yours lol

  • https://bwerpipes.com/ bwer Ductile Irrigation Iraq

    certainly like your website but you need to take a look at the spelling on quite a few of your posts. Many of them are rife with spelling problems and I find it very troublesome to inform the reality nevertheless I will definitely come back again.

  • https://bwerpipes.com/ bwer Hdpe Factory Iraq

    Normally I do not read article on blogs, however I would like to say that this write-up very forced me to try and do so! Your writing style has been amazed me. Thanks, quite great post.

  • https://bwerpipes.com/ bwer Factory Iraq

    Its like you read my mind! You appear to know a lot about this, like you wrote the book in it or something. I think that you could do with some pics to drive the message home a little bit, but instead of that, this is fantastic blog. An excellent read. I will certainly be back.

  • https://bwerpipes.com/ bwer Ductile Pipe Iraq

    you are truly a just right webmaster. The site loading speed is incredible. It kind of feels that you’re doing any distinctive trick. In addition, The contents are masterwork. you have done a great activity in this matter!

  • https://bwerpipes.com/ bwer Fittings Iraq

    I do not even know how I ended up here, but I thought this post was great. I don’t know who you are but definitely you’re going to a famous blogger if you aren’t already 😉 Cheers!

  • https://bwerpipes.com/ bwer Ductile Weld Iraq

    I do not even know how I ended up here, but I thought this post was great. I do not know who you are but certainly you’re going to a famous blogger if you are not already 😉 Cheers!

  • https://bwerpipes.com/ bwer Factory Fittings Iraq

    I do agree with all the ideas you have introduced on your post. They are very convincing and will definitely work. Still, the posts are very short for newbies. May just you please prolong them a little from subsequent time? Thank you for the post.

  • https://bwerpipes.com/ bwer Agri Pipe Iraq

    Hi, Neat post. There is a problem along with your website in internet explorer, would test this텶E still is the market chief and a good section of other folks will pass over your magnificent writing due to this problem.

  • https://bwerpipes.com/ bwer Upvc Iraq

    Thanks, I have recently been looking for info about this subject for a while and yours is the greatest I have discovered so far. However, what in regards to the bottom line? Are you certain in regards to the supply?

  • https://bwerpipes.com/ bwer Ductile Upvc Iraq

    Hello my loved one! I want to say that this post is amazing, great written and include almost all significant infos. I would like to look extra posts like this.

  • https://bwerpipes.com/ bwer hdpe iraq

    Nice blog here! Also your site loads up very fast! What host are you using? Can I get your affiliate link to your host? I wish my site loaded up as quickly as yours lol

  • https://elitepipeiraq.com منتجات HDPE

    Fantastic site. A lot of helpful info here. I’m sending it to some buddies ans additionally sharing in delicious. And naturally, thanks on your sweat!

  • https://www.binance.info/uk-UA/join?ref=OMM3XK51 бнанс рестраця

    Thanks for sharing. I read many of your blog posts, cool, your blog is very good. https://www.binance.info/uk-UA/join?ref=OMM3XK51

  • https://www.qweqt.com/ qweqt

    Hello i think that i saw you visited my weblog so i came to Return the favore Im trying to find things to improve my web siteI suppose its ok to use some of your ideas

  • https://firestickdownloader.co.uk/ firestickdownloader

    Thank you for the auspicious writeup It in fact was a amusement account it Look advanced to more added agreeable from you By the way how could we communicate

  • https://taxtmail.com/ temp mail

    Hello, my dear one, I would like to express my admiration for this exceptionally well-written post that encompasses nearly all pertinent information. I eagerly await further postings of the same caliber.

  • https://accounts.binance.com/ro/register?ref=OMM3XK51 Bonus de recomandare Binance

    Your point of view caught my eye and was very interesting. Thanks. I have a question for you. https://accounts.binance.com/ro/register?ref=OMM3XK51

  • https://bestiptv-smarters.co.uk/ iptv subscription

    I do trust all the ideas youve presented in your post They are really convincing and will definitely work Nonetheless the posts are too short for newbies May just you please lengthen them a bit from next time Thank you for the post

  • https://www.binance.info/ru/join?ref=B4EPR6J0 Регистрация на binance

    Your article helped me a lot, is there any more related content? Thanks! https://www.binance.info/ru/join?ref=B4EPR6J0

  • https://fitspresso-reviews.shop/ Temp mail

    I enjoyed it just as much as you will be able to accomplish here. You should be apprehensive about providing the following, but the sketch is lovely and the writing is stylish; yet, you should definitely return back as you will be doing this walk so frequently.

Комментарии для сайта Cackle
Пролистать наверх

40 приемов и трюков Excel

Чтобы работать, как профессионал

Конфиденциальность данных гарантирована.
Никакого спама.