Знакомство с Power Query на примере транспонирования Таблицы Excel - statanaliz.info

Знакомство с Power Query на примере транспонирования Таблицы Excel

Power Query – это инструмент MS Excel, предназначенный для импорта из самых различных источников и обработки данных. Впервые появился в 2013 году и был доступен в виде специальной надстройки, которую и сейчас можно скачать с официального сайта Microsoft и установить на Excel 2010-2013. После установки и подключения на ленте Excel появится соответствующая вкладка.

В Excel 2016 Power Query уже встроен в ядро программы. Команды управления запросами находятся во вкладке Данные, в группе Скачать и преобразовать (в английском варианте Get & Transform).

Power Query в Excel 2016

Далее будем использовать привычное название Power Query.

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

Кнопки Получение внешних данных в Excel

Однако их возможности и удобство использования сильно ограничены.

После появления Power Query в среде пользователей Excel произошло потрясение, сравнимое с появлением сводных таблиц. Это не шаг, а прыжок вперед, благодаря которому любой аналитик (и обычный пользователь Excel), имеющий дело с большими и обновляемыми данными из разных источников, может ускорить свою работу в десятки раз. Да, в десятки, если не в сотни. Ведь как раньше делался, скажем, отчет? Импортируются данные (из разных источников), очищаются, связываются вместе с помощью формул типа ВПР, затем делаются необходимые расчеты, все агрегируется с помощью сводных таблиц в краткий отчет. Периодически эти действия нужно повторять, т.к. традиционными методами (без VBA) очень трудно автоматизировать все шаги. Сегодня этому кошмару пришел конец. В Power Query достаточно один раз все настроить и далее все операции импорта, обработки и выгрузки данных повторяются нажатием одной кнопкой обновления.

Power Query работает на специальном языке программирования под названием M, с помощью которого записываются последовательные шаги обработки данных. Однако есть и пользовательский редактор с кнопками, поэтому быть программистом не обязательно. Здесь уместна аналогия с записью обычных макросов. Включили запись, произвели действия, закончили запись. В любой момент запустили выбранный макрос.

Вкратце алгоритм работы Power Query таков:

1. импорт данных из выбранных источников данных

2. обработка полученных данных

3. выгрузка

Список возможных источников довольно разнообразный: от текстовых файлов до внешних баз данных и интернета. Также можно легко присоединиться к данным внутри самого MS Excel.

На этапе обработки производят операции по очистке, связыванию, группировке, математическому преобразованию и т.д. Специфика работы именно с такими, плохо организованными и неочищенными данными, объясняет набор инструментов Power Query. Частично они повторяют то, что есть в Excel, но есть и новые, которые значительно расширяют привычный функционал Эксель. Важнейшей особенности работы в Power Query является то, что все шаги записываются. Это дает возможность затем нажатием одной кнопки повторить все операции. Объединяя возможность подключения к данным внутри Excel и новые методы их обработки, мы получаем дополнительные инструменты, которые делают работу в Excel удобнее и быстрее.

На последнем этапе запроса обработанные данные выгружаются в указанное место либо создается только соединение (часто запросы – это только промежуточный этап обработки данных). Но об этом в другой раз.

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

Таблица для транспонирования

В целом это не проблема, т.к. в Excel существует минимум два способа транспонирования.

Первый и самый быстрый способ – воспользоваться Специальной вставкой, поставив галочку напротив опции транспонировать.

Окно специальной вставки с опцией транспонирования

Отличный вариант, но одноразовый. В смысле, нет никакой связи между результатом и источником. Поэтому при любом изменении данных все нужно повторить снова. Это минус.

Второй способ транспонирования – воспользоваться функций ТРАНСП. Это формула массива, поэтому для ее вставки нужно вначале указать точный диапазон и ввести с помощью комбинации Ctrl + Shift + Enter.
Функция ТРАНСП для транспонирования таблицы Excel

Теперь при изменении данных в источнике автоматически обновится и транспонированный диапазон. Но здесь также есть серьезные недостатки. Во-первых, для вставки формулы ТРАНСП нужно заранее подсчитать, сколько строк и столбцов занимает диапазон, что, мягко говоря, не всегда удобно. Во-вторых, при изменении размера диапазона механизм перестает работать, т.к. транспонированный диапазон зафиксирован и не будет расширяться вслед за источником.

Итого получается, что мы не можем сделать динамическое транспонирование данных в изменяющемся диапазоне. Так да не так. С появлением Power Query задача решается быстро, без шума и пыли.

Транспонирование таблицы средствами Power Query

Первым делом нужно сделать запрос на источник данных. Нас интересуют данные из этой же книги Excel. Power Query не видит адреса обычных ячеек, а только именованные диапазоны и Таблицы Excel. Как правило, используют Таблицы Excel. Для преобразования обычного диапазона в таблицу рекомендую горячую комбинацию клавиш Ctrl + T.

Создание Таблицы Excel из диапазона

Теперь активируем любую ячейку Таблицы с данными и нажимаем кнопку Данные – Скачать и преобразовать – Из таблицы.

Создание запроса из Таблицы Excel

Открывается окно редактирования Power Query.

Окно редактирования Power Query

Выглядит, как другая программа, но это только отдельное окно внутри Excel. Интерфейс состоит из пяти частей:

1. Инструменты редактирования – лента, на которой находятся команды Power Query.

2. Строка формул – здесь записывается код языка М для выделенного в данный момент шага обработки.

3. Запросы – скрываемая панель для навигации между запросами текущей книги.

4. Панель результата – место, где отображается результат обработки данных на этапе выделенного шага.

5. Параметры запроса – панель с названием запроса (можно изменять) и перечнем созданных шагов, которые также можно редактировать.

Выделив любой из шагов, мы увидим состояние данных на соответствующем этапе.

Название запроса лучше всего изменить на более говорящее. Довольно часто в книге используют сразу несколько запросов, поэтому в них нужно ориентироваться. Назовем «Транспонирование».

Переименование запроса

Из предыдущего рисунка видно, что мы еще ничего не сделали, а два шага уже записаны. Как так? Все просто. Первый шаг – это обращение к источнику, а второй автоматическое определение типа данных у каждого столбца. Поэтому все в порядке.

Вернемся к условию задачи. Нужно транспонировать вот эту таблицу.

Вид таблицы внутри Power Query

Обратим внимание, что заголовки перенеслись из заголовков Таблицы Excel, которую мы использовали в качестве источника. Однако транспонирование происходит без заголовков. Поэтому, чтобы избежать потери названий столбцов, «опустим» их названия в первую строку таблицы Преобразование – Таблица Использовать первую строку в качестве заголовков Использовать заголовки как первую строку.

Использовать заголовки как первую строку

Таблица с данными получит такой вид.

Таблица с заголовками в первой строке

Теперь можно транспонировать. Используем команду Преобразование – Транспонировать.

Команда транспонирования в Power Query

Таблица мгновенно изменяется.

Транспонированная таблица

Сделаем первую строку назад заголовками. Можно через Преобразование – Таблица — Использовать первую строку в качестве заголовков либо через кнопку в верхнем левом углу от таблицы.

Команда использования первой строки в качестве заголовков

 

Получим конечный результат обработки.

Транспонированная таблица

Задача решена. Все шаги преобразования данных записаны и видны справа.

Шаги преобразования в Power Query

Осталось измененные данные вернуть в Excel с помощью команды Главная – Закрыть – Закрыть и загрузить.

Закрыть и загрузить в Excel

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

Опции выгрузки данных из Power Query

Если выбрать Только создать соединение, выгрузки не произойдет. Такой вариант применяют, если требуется дальнейшая обработка или использование этого запроса. Для выгрузки в Excel можно выбрать Новый лист либо указать конкретный диапазон. Если установить галочку Добавить эти сведения в модель данных, то результат запроса даже без выгрузки в Excel можно будет использовать в модели данных или Power Pivot. Этот вариант позволяет обрабатывать миллионы (миллионы!) строк, т.к. на обработку данных в памяти требуется гораздо меньше ресурсов. Оставляем все по умолчанию и жмем Загрузить. В процессе выгрузки таблица имеет серенький цвет, а когда выгрузка завершена, становится зелененькой.

Выгруженная из Power Query Таблица Excel

Вот и все, дело сделано, мы получили транспонированную таблицу исходных данных.

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

Добавим в исходную таблицу данные о продажах во втором квартале.

Добавление данных в источник

А теперь обновим запрос.

Обновление запроса Power Query для транспонирования таблицы

Это просто праздник какой-то! (с).

Обратим внимание, что справа в окне Excel появляется панель для управления существующими запросами.

Панель управления запросами Power Query

Их может быть много, но у нас только один. Сразу под названием видно, сколько загружено строк. Здесь же указываются ошибки, если они есть. Это важно для контроля. Если подвести курсор мыши к названию, то откроется окно с кратким описанием запроса и командами управления снизу.

Управление заросом

Можно вновь войти в редактирование запроса, удалить его и т.д. Эти же и некоторые другие команды появятся в контекстном меню после кликания по названию запроса правой кнопкой мыши.

Контекстное меню управления запросом

Перечислим наиболее часто используемые среди них.

Изменить – команда открытия окна редактирования. Эквивалентно двойному нажатию левой кнопки мыши по самому запросу.

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

Загрузить в… – изменение места загрузки (в таблицу, модель или создания только соединения)

Дублировать – сделать копию выбранного запроса.

Другие команды не менее важны, но их рассмотрим в другой раз.

Панель Запросы книги можно закрыть или снова отобразить с помощью команды Данные – Скачать и преобразовать – Показать запросы.

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

Серия видеоуроков о Power Query.

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

    Спасибо, хорошо объяснили

  • Аноним

    Здравствуйте!
    Я импортирую данные из таблицы сайта в exel, вот код, который exel сформировал сам (сразу скажу, что Power Query не разбираюсь):
    let
    Источник = Web.Page(Web.Contents(«https://site/tasbles1.html»)),
    Data0 = Источник{0}[Data],
    #»Измененный тип» = Table.TransformColumnTypes(Data0,{{«Название», type text}, {«Высота», type text}, {«Ширина», type text}}),
    #»Переупорядоченные столбцы» = Table.ReorderColumns(#»Измененный тип»,{«Название», «Высота», «Ширина»})
    in
    #»Переупорядоченные столбцы»
    То, что сам сформировал exel, то он только обновляет данные, затирает старые новыми. А мне нужно чтобы для этих полей новые данные записывались в новую строку и не затирали старые.
    Если кто-то знает подскажите пожалуйста, как решить данную задачу.

  • Аноним

    если в исходной таблице некторые данные указаны красным шрифтом, можно сделать так, чтобы и Power Query выгружал его красным шрифтом? Затем в исходной таблице, например, другую цифру отметил зеленым, и в Query она тоже стала зеленым

    • Езепов Дмитрий

      Power Query не видит цвета. Настройки нужно делать на листе Excel.

  • Аноним

    Если в дальнейшем после обработки Querryи нужно добавлять данные в таблицу, и нужно сохранить внесенные данные, чтоб они не сместились при обновлении запроса и догрузке новых данных в эту самую таблицу, как это сделать?

  • https://www.gate.io/tr/signup/XlQVXVo gate.io türkiye

    Your article made me suddenly realize that I am writing a thesis on gate.io. After reading your article, I have a different way of thinking, thank you. However, I still have some doubts, can you help me? Thanks.

  • https://accounts.binance.com/pt-PT/register?ref=T7KCZASX Crie uma conta gratuita

    I don’t think the title of your article matches the content lol. Just kidding, mainly because I had some doubts after reading the article. https://accounts.binance.com/pt-PT/register?ref=T7KCZASX

  • https://accounts.binance.com/sl/register?ref=B4EPR6J0 binance racun

    I don’t think the title of your article matches the content lol. Just kidding, mainly because I had some doubts after reading the article. https://accounts.binance.com/sl/register?ref=B4EPR6J0

  • https://elitepipeiraq.com/ حلول أنابيب uPVC

    obviously like your web-site but you need to test the spelling on quite a few of your posts. Several of them are rife with spelling problems and I to find it very troublesome to inform the reality on the other hand I’ll certainly come back again.

  • https://elitepipeiraq.com/ سمعة المصنع

    hello!,I really like your writing so a lot! share we keep up a correspondence extra approximately your post on AOL? I need an expert in this house to unravel my problem. May be that is you! Taking a look ahead to see you.

  • https://elitepipeiraq.com/ مورد أنابيب uPVC في العراق

    I have been surfing online more than 3 hours today, yet I never found any interesting article like yours. It is pretty worth enough for me. In my opinion, if all web owners and bloggers made good content as you did, the web will be much more useful than ever before.

  • https://bwerpipes.com/ bwer ductile agri tech iraq

    Just wish to say your article is as surprising. The clearness in your post is just cool and i could assume you’re an expert on this subject. Fine with your permission allow me to grab your RSS feed to keep updated with forthcoming post. Thanks a million and please keep up the enjoyable work.

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

    Thank you for the good writeup. It in fact was a amusement account it. Look advanced to far added agreeable from you! However, how could we communicate?

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

    Wonderful web site. Lots of useful info here. I’m sending it to a few friends ans additionally sharing in delicious. And obviously, thanks to your effort!

  • https://bwerpipes.com/ bwer hdpe agriculture 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://bwerpipes.com/ bwer Factory Irrigation Iraq

    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://bwerpipes.com/ bwer Upvc Factory Iraq

    Its like you read my mind! You appear to know so much about this, like you wrote the book in it or something. I think that you can do with a few pics to drive the message home a little bit, but other than that, this is fantastic blog. A great read. I’ll certainly be back.

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

    I was recommended this website by my cousin. I am not sure whether this post is written by him as nobody else know such detailed about my difficulty. You are wonderful! Thanks!

  • https://elitepipeiraq.com HDPE pipe services in Iraq

    I was recommended this website by my cousin. I am not sure whether this post is written by him as nobody else know such detailed about my difficulty. You are wonderful! Thanks!

  • https://www.binance.info/it/join?ref=P9L9FQKY Codice di riferimento binance

    Can you be more specific about the content of your article? After reading it, I still have some doubts. Hope you can help me. https://www.binance.info/it/join?ref=P9L9FQKY

  • https://www.binance.info/pl/join?ref=UM6SMJM3 Tworzenie konta na Binance

    Your point of view caught my eye and was very interesting. Thanks. I have a question for you. https://www.binance.info/pl/join?ref=UM6SMJM3

  • https://upxmail.com/ Temp Mail

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

  • https://upxmail.com/ Temp Mail

    It was great seeing how much work you put into it. Even though the design is nice and the writing is stylish, you seem to be having trouble with it. I think you should really try sending the next article. I’ll definitely be back for more of the same if you protect this hike.

  • https://www.binance.info/sv/join?ref=RQUR4BEO «oppna ett binance-konto

    I don’t think the title of your article matches the content lol. Just kidding, mainly because I had some doubts after reading the article. https://www.binance.info/sv/join?ref=RQUR4BEO

  • https://www.newsbreak.com/health/3317483633830-discover-the-truth-about-puravive-weight-loss-official-website-review puravive review

    What i do not realize is in fact how you are no longer actually much more wellfavored than you might be right now Youre very intelligent You recognize thus considerably in relation to this topic made me in my view believe it from numerous numerous angles Its like men and women are not fascinated until it is one thing to do with Lady gaga Your own stuffs excellent All the time handle it up

  • https://www.binance.info/ph/join?ref=T7KCZASX Lumikha ng Personal na Account

    I don’t think the title of your article matches the content lol. Just kidding, mainly because I had some doubts after reading the article. https://www.binance.info/ph/join?ref=T7KCZASX

  • https://liposlend-weightloss.shop/ LipoSlend

    For the past few days I’ve been a dedicated fan of this amazing site, they deliver superb content for their community. The site owner has real talent engaging audiences. I’m thrilled and hope they maintain their magnificent efforts.

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

    Hi Neat post Theres an issue together with your web site in internet explorer may test this IE still is the marketplace chief and a good component of people will pass over your fantastic writing due to this problem

  • https://puravive.healthmassive.com/ is puravive a scam

    This website page is phenomenal. The wonderful data shows the essayist’s dedication. I’m shocked and envision more such magnificent posts.

  • https://www.bestiptvireland.irish/ bestiptvireland

    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://www.binance.com/join?ref=DB40ITMB 最佳Binance推荐代码

    I don’t think the title of your article matches the content lol. Just kidding, mainly because I had some doubts after reading the article.

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