Exceltip

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

Power Query дает нам новую вкладку в ленточном интерфейсе Excel, где мы сможем импортировать, преобразовывать и объединять данные из различных источников. В дополнение к стандартным источникам данных, таких как Microsoft Access, SQL и текстовым файлам, Power Query позволяет импортировать из Active Directory, Azure, OData и Hadoop.

Power Query – это бесплатная надстройка для Excel 2010 и Excel 2013, которую можно .

Обратите внимание, что Power Query ранее был известен под кодовым именем «Data Explorer”. Аналогично, Power Map ранее была известна как проект «GeoFlow”.

В сегодняшней статье, я покажу вам несколько примеров, которые можно сделать с помощью надстройки Power Query.

1. Основы использования Power Query

После установки надстройки, вы увидите новую вкладку Power Query на ленте Excel.

В группе Get External Data в левой части вкладки можно выбрать источники данных. В этом первом примере я выберу From Web и введу интересующий меня сайт.

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

Вы можете удалить данные из таблицы непосредственно в окне запроса. На картинке внизу, я щелкнул правой кнопкой мыши по заголовку столбца и выбрал Use First Row As Header, что означает Использовать первую строку в качестве заголовка.

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

После того, как запрос будет настроен, можно вернуть данные на рабочий лист Excel. Как вы уже могли догадаться, чтобы обновить данные, щелкните правой кнопкой мыши по таблице и выберите Обновить.

2. Работа с каналами данных

Инструмент Power Query имеет механизм, поиска данных в интернет. К примеру, вам захотелось посмотреть календарь соревнований зимних Олимпийских игр в Сочи. Для этого вы можете воспользоваться Power Query.

Щелкаем по кнопке Online Search, в появившемся диалоговом окне вводим поисковый запрос Sochi. Результатом работы поисковой машины Excel станет список статей, где встречается слово Sochi. Если мы щелкнем по любому из них, данные появятся на рабочем листе.

3. Получаем список файлов в папке

Появлялась ли когда-нибудь у вас нужда выгрузить список файлов в папке? У меня да. И для этого я писал отдельный макрос. Ну, это дела давно минувших дней, теперь Power Query позволяет сделать тоже самое в несколько щелчков.

Выбираем From File в группе Get External Data и определяем папку, из которой мы хотим извлечь список.

Excel активирует окно запроса, где будет виден список всех файлов в папке.

К тому же вы можете добавить колонки с дополнительными атрибутами файла, щелкнув по иконке, находящейся справа от заголовка столбца Attributes.

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

4. Консолидация данных, или как создать одну таблицу из нескольких Excel файлов

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

Чтобы открыть окно с формулами запроса, щелкните по иконке в виде листочка в окне запроса:

Изначально синтаксис выглядит безнадежно непроницаемым. Но немного поигравшись с Power Query, вы поймете, как он работает. Ключ в том, что необходимо сделать какие-либо действия в запросе, а затем посмотреть, как изменится код. Через некоторое время вы сможете создавать собственные запросы для выполнения сложных действий.

Вы также можете посетить страницу формул Power Query для большего понимания.

Для примера предположим, что мне необходимо получить данные с двух файлов Excel и поместить их в одну таблицу. Структура файлов одинакова (в каждом одинаковые колонки).

Банальный копипэйст меня не устраивает, так как эту операцию необходимо выполнять еженедельно. Требуется что-то более автоматизированное. Нам поможет Power Query, с помощью которого мы сможем написать запрос для получения данных.

Для начала необходимо включить расширенные возможности редактирования запросов, во вкладке Power Query в группе Machine Settings щелкаем по кнопке Options -> Enable Advanced Query Editing.

Теперь мы готовы писать запрос. В группе Get External Data выбираем From Other Sourses -> Blank Query.

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

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

Важная мысль: если вы отправите файл содержащий запрос Power Query кому-то у кого он не был установлен, этот пользователь увидит данные таблицы, но не сможет их обновить.

Лучшие 25 приемов Power Query для экономии времени в повседневной работе

Теперь пришло время изучить все эти советы по Power Query. Так что давайте начнем.

Заменить значения

У нас есть список с некоторыми значениями, и нам нужно заменить определенное значение или некоторые значения чем-то другим.

С помощью Power Query мы можем создать запрос и заменить эти конкретные значения очень быстро. В приведенном ниже списке я хочу заменить имя «Алена» на «Алёна».

Давайте сделаем:

  • Прежде всего, загрузите список в редактор Power Query.
  • После этого перейдите на вкладку «Преобразование» и нажмите «Замена значений».

  • Теперь в поле «Значение для поиска» введите «Алена», а в поле «Заменить на» введите «Алёна» и после этого нажмите ОК.

  • После того, как вы нажмете OK, все значения будут заменены новыми. Теперь можно нажать «Закрыть и загрузить», чтобы загрузить данные в таблицу.

А вот и лучшая часть: Вы только что создали запрос в режиме реального времени. При повторном обновлении запроса он заменит все вновь введенные значения.

Сортировка — по возрастанию и по убыванию

Как и при обычной сортировке, вы можете сортировать данные, используя Power Query. Я использую тот же список имен, который мы использовали выше. Вот, что нужно сделать.

  • Прежде всего, загрузите данные в редактор
  • В редакторе Power Query у вас есть две кнопки сортировки (по возрастанию и по убыванию).
  • Нажмите на любую из этих кнопок, чтобы отсортировать.

  • В конце нажмите «Закрыть и загрузить», чтобы загрузить данные в таблицу.

Вы, наверное, удивлены: «Зачем мне использовать Power Query, если я могу использовать обычную сортировку на листе?»

Как я уже писал, Power Query — работает в реальном времени. Вы можете создать запрос автообновления (Совет № 26), который будет обновляться через определенное время и автоматически сортировать ваши данные.

Удалить столбцы

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

Но с Power Query вы можете создать запрос.

Вот шаги:

  • Прежде всего, откройте данные в редакторе
  • После этого выберите столбец или несколько столбцов

  • Теперь щелкните правой кнопкой мыши и выберите «Удалить».

  • В конце нажмите «Закрыть и загрузить», чтобы загрузить данные.

Совет. Также имеется опция «Удалить другие столбцы», в которой можно удалить все невыбранные столбцы.

Разделить столбец

Точно так же как опция как «Текст по столбцам» есть в запросе: «Разделить столбец». Сейчас я расскажу, как это работает.

В приведенном ниже списке у вас есть имя и фамилия с дефисом между ними.

Теперь вам нужно разделить их на две колонки. Используйте эти шаги:

  • Прежде всего, откройте список в редакторе
  • После этого выберите столбец и перейдите на вкладку «Преобразование» ➜ «Разделить столбец» ➜ «по разделителю».
  • Выберите «Пользовательский» из выпадающего списка и введите «-» в него.
  • Теперь у вас есть три варианта, как разбить столбец.
  1. Самый левый разделитель
  2. Самый правый разделитель
  3. По каждому вхождению разделителя

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

  • В конце нажмите OK и нажмите «Закрыть и загрузить», чтобы загрузить данные.

Переименовать столбец

Вы можете просто переименовать столбец, щелкнув правой кнопкой мыши, а затем нажмите «Переименовать».

Совет: допустим, у вас есть запрос на переименование столбца, а кто-то другой переименовал его по ошибке. Вы можете восстановить это имя одним щелчком мыши.

Дубликат столбца

В Power Query есть простой способ создать дубликат столбца.

Все, что вам нужно сделать, это щелкнуть правой кнопкой мыши столбец, для которого вам нужен дубликат, а затем нажать «Создать дубликат столбца».

Объединить столбец

Обычно для объединения столбцов и ячеек мы используем формулы в Excel, но с Power Query это можно сделать намного проще.

Помните, мы разделили список сотрудников (Совет № 4). Теперь, давайте объединим его, используя пробел.

Следуй этим шагам:

  • Как только вы добавите данные в редактор, выберите оба столбца.
  • После этого щелкните по ним правой кнопкой мыши и выберите «Объединить столбцы».
  • Теперь в окне слияния столбцов выберите разделитель из выпадающего списка (здесь мы используем пробел) и добавьте имя для нового объединенного столбца.
  • Нажмите OK и загрузите данные в таблицу.

Совет: вы также можете использовать собственный разделитель для объединения двух столбцов.

Транспонировать столбец или строку

В Power Query транспонирование — это проще простого. Да, всего один клик.

  • Как только вы загрузите данные в редактор, вам просто нужно выбрать столбец (столбцы) или строку (и).
  • Перейдите на вкладку «Преобразование» ➜ Таблица ➜ «Транспонировать».

И все.

Заменить / удалить ошибки

Это крутая вещь. Обычно для замены или удаления ошибок в Excel вы можете использовать опцию поиска и замены или код VBA. Но в Power Query все намного проще.

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

Когда вы щелкнете правой кнопкой мыши по столбцу, у вас будет два варианта, как с ними справиться.

  • Заменить ошибки
  • Удалить ошибки

Изменить тип данных

Посмотрите, это обычное дело: У вас есть данные в столбце, но они не в нужном формате. Поэтому каждый раз нужно менять его формат.

В приведенном выше примере у нас есть столбец дат, но мы видим просто числа. Чтобы преобразовать их в дату, вы можете использовать Power Query. Это очень просто.

  • Прежде всего, откройте данные в редакторе
  • После этого выберите столбец и перейдите на вкладку Преобразование.
  • Теперь из типа данных выберите «Дата».

Вот, что получилось.

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

Добавить столбец из примеров

Вот в чем дело: в Power Query есть возможность добавить образец столбца, который на самом деле не является образцом, связанным с текущим столбцом. Позволь мне привести пример:

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

Вместо использования формулы или любого другого параметра, в Power Query мы можем использовать параметр «Добавить столбец из примеров».

Вот как это сделать:

  • После того, как вы отправите свои данные в редактор, выберите столбец.
  • Потом щелкните по нему правой кнопкой мыши и выберите «Добавить столбец из примеров».
  • Здесь вы получите пустой столбец. Нажмите на первую ячейку столбца, чтобы получить список значений, которые вы можете вставить.
  • Выберите «Название дня недели с Дата» и нажмите «ОК».

Все! Столбец заполнен.

Отменить таблицу

Я написал полное пошаговое руководство, чтобы отменить вывод данных в виде таблицы с помощью Power Query:

  • Прежде всего, выберите данные кросс-таблицы и перейдите на вкладку «Данные».
  • На вкладке данных перейдите к Скачать & Преобразовать → Из таблицы.
  • Когда вы щелкнете по нему, он преобразует ваши данные кросс-таблицы в таблицу Excel (если это уже не так).
  • Данные мгновенно загрузятся в редактор Power Query.
  • Отсюда нам нужно выбрать все столбцы, которые мы хотим отключить.
  • Для этого выберите столбец Янв, нажмите и удерживайте клавишу Shift и выберите столбец Дек.
  • После этого щелкните по нему правой кнопкой мыши и выберите «Отменить свертывание столбцов».
  • Теперь все значения 12-ти столбцов разделены на два столбца. В одном — месяц, в другом — сумма.
  • Последнее, что вам нужно сделать, это переименовать столбцы. Для этого щелкните правой кнопкой мыши по столбцу и переименуйте их.
  • В конце нажмите «Закрыть и загрузить».

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

Изменить регистр

Как в функциях, которые вы используете в Excel, в Power Query есть пакет опций для изменения регистра текста.

  • нижний регистр
  • ВЕРХНИЙ РЕГИСТР
  • Каждое Слово С Прописной

Вы можете сделать это, щелкнув правой кнопкой мыши по столбцу и выбрать любой из трех указанных выше вариантов. Или перейдите на вкладку «Преобразование» ➜ «Столбец Текст» ➜ «Формат».

Усечь и Очистить

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

Шаги просты:

  • Щелкните правой кнопкой мыши по столбцу или выберите все столбцы, если у вас несколько столбцов.
  • Перейдите к опции преобразования и выберите любую из опций:
  1. Усечь: для удаления лишних пробелов из ячейки.
  2. Очистить: для удаления непечатаемых символов из ячейки.

Добавить префикс / суффикс

Итак, у вас есть список значений, и в этот список вы хотите добавить префикс / суффикс в каждую ячейку. В Excel вы можете использовать функцию СЦЕПИТЬ, но в Power Query есть более простой способ.

  • Прежде всего, выберите столбец, в который нужно добавить префикс / суффикс.
  • Затем перейдите на вкладку «Преобразование» ➜ Столбец Текст ➜ Формат ➜ Добавить префикс / Добавить суффикс.
  • Как только вы нажмете одну из опций, откроется диалоговое окно для ввода текста.
  • После ввода текста нажмите ОК.

Опция работает одинаково для чисел, текста и дат.

Извлечь значения

Если вы разбираетесь в формулах, то вы согласитесь со мной, что для извлечения текста или числа из ячейки необходимо комбинировать несколько функций. Power Query решает много подобных задач. У вас есть семь способов извлечь значения из ячейки. И да, одним щелчком мыши.

Просто посмотрите на варианты, которые у вас есть.

Эти опции закрыли все основные вещи задачи, которые вы привыкли решать с помощью формул.

Только дата или время

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

Для этого в Power Query вам нужен всего лишь один клик, вот шаги.

  • Выберите столбец, где у вас есть дата и время вместе.
  • Если вам нужна Дата: щелкните правой кнопкой мыши ➜ Преобразование ➜ Только дата;
  • Если хотите Время: щелкните правой кнопкой мыши ➜ Преобразование ➜ Только время.

Объедините дату и время

Теперь у вас есть отдельно дата и время. Значит, пора узнать, как их объединить.

Это довольно просто.

  • Прежде всего, загрузите ваши данные в редактор.
  • После этого выберите оба столбца (Дата и время) и перейдите на вкладку преобразования.
  • Теперь из группы Столбец «Дата и время» перейдите к «Дата» и нажмите «Объединить дату и время».

Теперь у вас есть новый столбец с объединенным данными.

Округление чисел

У нас есть функции для округления чисел в Excel, но также есть и Power Query.

Вот варианты:

  1. Округление с увеличением.
  2. Округление с уменьшением.
  3. Округление: Вы можете выбрать, до какого знака после запятой округлить.

Шаги:

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

Примечание. Когда вы выбираете опцию «Округление», вам нужно ввести количество десятичных знаков для округления.

Расчеты

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

  • Стандартный
  • Статистика
  • Научный
  • Тригонометрические
  • Округление
  • Информация

Вы можете найти все эти опции на вкладке Преобразование.

Для выполнения любого из этих расчетов вам нужно выбрать столбец и выбрать опцию.

Группировка

Как и сводные таблицы, Power Query — отличный вариант для группировки. Вы можете найти эту опцию на вкладке Преобразование.

Допустим, у вас большой набор данных и вы хотите создать сводную таблицу. Вот что вам нужно сделать:

  • На вкладке «Преобразование» нажмите «Группировать по», откроется диалоговое окно.
  • Теперь в этом диалоговом окне выберите столбец, который вы хотите сгруппировать.

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

  • В конце нажмите ОК.

Примечание. В параметре «Группировать по» также есть несколько расширенных опций, которые можно использовать для создания многоуровневой групповой таблицы.

Удалить знак отрицания

Есть много методов удаления отрицательного знака, и один из них — это Power Query.

Щелкните правой кнопкой мыши по столбцу и перейдите в Преобразование, а затем нажмите на Абсолютное значение.

Добавить настраиваемый столбец

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

  • Прежде всего, перейдите на вкладку Добавление столбца ➜ Настраиваемый столбец.
  • Здесь у вас появится диалоговое окно для создания формулы для использования в столбце (сейчас я хочу умножить количество на цену). Ведите формулу в поле формулы.

Совет. Когда вы вводите формулу, в диалоговом окне появится сообщение, если в формуле есть какая-то ошибка.

Автообновление запроса

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

Вот шаги:

  • На вкладке «Данные» нажмите «Существующие подключения»
  • Теперь щелкните правой кнопкой мыши по нужному запросу, далее Изменить свойства подключения.
  • Введите минуты в открывшемся диалоговом окне.

  • В конце нажмите ОК.

Создайте сводную таблицу из нескольких рабочих книг.

Иногда мы получаем или собираем данные из разных книг. И в этом случае создание сводной таблицы потребует дополнительных усилий для объединения этих нескольких рабочих книг в одну.

Но вы можете сделать это радостью с помощью Power query. Выполните эти три простых шага, чтобы создать сводную таблицу из разных рабочих книг.

У меня есть четыре книги с данными о продажах для разных отделений.

Убедитесь, что все эти файлы в одной папке.

Шаг 1 — Объедините файлы с помощью Power Query

Прежде всего, нам нужно объединить все файлы в одну таблицу с Power Query.

  • Перейдите на вкладку «Данные» ➜ «Скачать & Преобразовать» ➜ «Создать запрос» ➜ «Из файла» ➜ «Из папки».
  • Теперь в окне выбора папки нажмите «Обзор» и выберите папку, в которой находятся все файлы.
  • Нажмите ОК.
  • Вы увидите окно «Объединить файлы».
  • В этом окне выберите лист с вашими данными во всех книгах. Важно: Убедитесь, что во всех книгах указано одинаковое имя листа!
  • После того, как вы нажмете OK, Power Query отправит все данные из рабочих книг в редактор.

Шаг 2 — Подготовка данных для сводной таблицы

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

  • Щелкните правой кнопкой мыши по этому столбцу и выберите «Разделить столбец» ➜ «По разделителю».
  • В окне разделителя выберите «Пользовательский», добавьте «.» в качестве разделителя и выберите «Самый левый разделитель».
  • Нажмите ОК.
  • После этого удалите второй столбец.
  • Теперь переименуйте первый столбец.
  • Теперь ваши данные готовы. Нажмите на закрыть и загрузить.

Шаг 3 — Вставьте сводную таблицу

На данный момент у нас есть новая рабочая таблица в рабочей книге с объединенными данными из всех четырех файлов. Теперь пришло время создать из них сводную таблицу.

  • Выберите таблицу и перейдите на вкладку «Вставка» и нажмите кнопку «Сводная таблица».
  • Откроется окно создания сводной таблицы. Нажмите OK, и вы получите новую сводную таблицу в своей книге.

Поздравляю! Вы успешно создали новую сводную таблицу из разных файлов.

Вы можете не пользоваться POWER QUERY. Но… Многие вещи, которые мы делаем с формулами и функциями или кодами VBA, могут быть автоматизированы с помощью Power Query.

Я надеюсь, что приведенные выше советы вдохновляют вас использовать его все больше и больше.

Почему я должен использовать Power Query?

Делюсь с вами некоторыми серьезными причинами, так как хочу, чтобы вы в дальнейшем изучали Power Query.

Самый простой способ преобразовать ваши данные

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

Power Query в реальном времени

Это вторая важная причина, по которой Power Query выполняется в реальном времени, как разовая настройка.

Напишите запрос один раз, и вы можете обновлять его каждый раз, когда происходит изменение данных, также вы можете определить время автоматического обновления (Совет № 26).

Нужно просто несколько кликов

Как я уже сказал, обычно вы используете формулы и сводные таблицы для преобразования данных, но с POWER QUERY вы можете многое сделать, просто щелкнув мышью.

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

Оставить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *