Как использовать Power Queries в Excel

Несмотря на то, что технические средства предлагают широкий спектр решений для управления данными, консолидация информации из нескольких источников остается сложной задачей. Чтобы разобраться в разрозненных таблицах данных, часто приходится прикладывать ручные усилия. Именно здесь может помочь power query, объединяющий данные из разных источников в интегрированное представление. Как консультант по маркетингу, я работаю с несколькими командами в рамках бизнеса клиента. Например, чтобы получить истинное представление о том, что происходит с воронкой доходов SaaS-компании, мне обычно нужны данные маркетинга, продаж, успешности клиентов и продукта. «Однако необходимые мне данные обычно собираются в разных местах и в разных форматах. Собрать все воедино означает получить все в одном месте, в одном формате и с возможностью манипулирования в любом случае. Таким образом, на протяжении многих лет мне очень пригодился запрос мощности, и это не так сложно, как вы думаете. С помощью Power query вы можете импортировать, очищать, преобразовывать и объединять наборы данных из разных источников. Как только вы узнаете, как его использовать, сбор и интерпретация данных из различных источников станут намного проще.»

Что такое запрос мощности?

Power query — это технология, которую можно использовать в Excel для соединения наборов данных в электронной таблице Excel. С помощью Power query можно извлекать данные из различных источников, включая веб-страницы, базы данных, другие электронные таблицы и файлы различных типов. После импорта данных вы также можете использовать редактор запросов Power для очистки и преобразования данных. Наконец, вы можете импортировать преобразованные данные в существующую электронную таблицу. Общие действия, которые можно использовать в редакторе запросов после импорта данных, включают фильтрацию, удаление дубликатов, разделение столбцов, форматирование данных и объединение данных. Основное преимущество заключается в том, что задачи, на выполнение которых вручную ушли бы часы, можно выполнить за считанные минуты с помощью Power query. Кроме того, при обновлении источников данных вы можете повторно использовать одни и те же действия по очистке или преобразованию одним нажатием кнопки. Любая расширенная функция в Excel требует некоторого обучения. Но хотя вам может понадобиться несколько попыток, чтобы освоить power query, у него довольно удобный интерфейс, как только вы поймете, где что находится.

Как использовать Power Queries в Excel

Когда вы учитесь использовать что-то вроде power query, самый полезный метод — это пример. Приведенные ниже шаги можно применить к любому типу набора данных, а выделенные функции при необходимости можно заменить другими способами использования запросов. В этом примере я собираю для анализа данные о продажах из двух разных географических регионов. Данные поступают из США и Великобритании, причем в двух электронных таблицах они написаны по-разному и по-разному отформатированы. Моя цель — объединить эти наборы данных в одном листе, очистить их и легко интерпретировать и анализировать в одном месте.

Шаг 1. Откройте Excel и перейдите к запросу power query.

Прежде всего, откройте основную электронную таблицу, с которой вы будете работать. В данном случае я открыл таблицу продаж в США, поскольку именно здесь я хочу очистить и объединить данные. На главной ленте нажмите «Данные». В качестве первого элемента ленты «Данные» вы увидите «Получить данные (Power Query)».

Шаг 2. Импортируйте данные.

Нажмите на выпадающий список, чтобы получить доступ к параметрам запроса мощности.

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

В данном случае мы выбираем «Рабочая книга Excel», чтобы получить доступ к файлу, который хранится локально на моем компьютере. Сделав этот выбор, просто найдите в файлах нужную электронную таблицу и нажмите «Получить данные».

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

Если на этом этапе вы выберете «Загрузить», запрос Power импортирует данные в новую вкладку существующей электронной таблицы. Оттуда можно выбрать «Данные» и «Запустить редактор запросов Power Query Editor», чтобы выполнить массовую очистку или обновление импортированных данных.

Шаг 3. Очистка импортированных данных.

Если вы предпочитаете очистить данные перед импортом, вы можете сделать это на этапе импорта. Вместо того чтобы выбирать «Загрузить» в процессе импорта, вы можете выбрать «Преобразовать данные», что откроет редактор Power Query Editor, как и в предыдущем шаге.

Например, лист продаж в Великобритании содержит те же данные, что и лист продаж в США, но они отформатированы неправильно. Формат валюты не применяется к ячейке валюты. Итак, в Power Query Editor я могу выбрать вкладку «Преобразование», чтобы отредактировать данные до их импорта (я также могу сделать это, если сначала импортирую данные, а затем открою Power Query Editor).

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

Там я могу выбрать опцию «Валюта» и изменить значение на USD.

Шаг 4. Загрузите преобразованные данные.

Последний шаг — добавление этих очищенных и преобразованных данных в существующую электронную таблицу. В Power Query Editor вернитесь на вкладку «Главная» и нажмите «Закрыть и загрузить».

Теперь Excel загрузит этот запрос на новую вкладку вашей электронной таблицы.

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

Примеры Power Query

Консолидация данных из нескольких электронных таблиц

Независимо от того, работаете ли вы с несколькими рабочими книгами собственного создания или получаете файлы от других людей, необходимость объединить данные на одном листе — довольно распространенная проблема. Power query делает эту задачу довольно простой. Во-первых, убедитесь, что все рабочие книги, которые вы хотите объединить, сохранены в одной папке. По возможности убедитесь, что все они совпадают по заголовкам столбцов. Откройте Excel в пустой рабочей книге и обратитесь к запросу power query с помощью опции «Получить данные».

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

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

Теперь оба запроса должны быть доступны в редакторе Power Query Editor на левой боковой панели.

В правой части ленты Power Query Editor вы увидите кнопку «Объединить» с выпадающим меню, содержащим опции для объединения или добавления запросов.

В выпадающем списке выберите «Append queries as new». Выберите таблицы так, как вы хотите добавить одну в конец другой, и нажмите «Ок».

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

Очистка данных перед импортом

Иногда приходится иметь дело с беспорядочной электронной таблицей. Форматирование и стилизация повсюду, и перед импортом нужно убедиться, что все чисто и аккуратно, насколько это возможно. Power query упрощает процесс импорта данных, поэтому вам не придется тратить время на ручную очистку. Простой пример — электронная таблица, в которой часть текста набрана заглавными буквами, а часть — нет. Поэтому перед добавлением в существующий лист необходимо преобразовать все в регистр предложения. Допустим, отдел продаж во Франции прислал нам свой отчет о продажах. Он выглядит отлично, но часть текста в столбце «Идентификатор страны» написана заглавными буквами, а часть — нет. Кроме того, в названии страны появились случайные пробелы, которые тоже нужно исправить, иначе нам будет сложно правильно вывести данные в таблицы pivot и другие отчеты. В своем листе откройте Power query и импортируйте файл с беспорядочными данными. Нажмите «Преобразовать данные», а не импортируйте их сразу. Сначала найдите столбец с перепутанной капитализацией и лишними пробелами. Щелкните правой кнопкой мыши на столбце и выберите «Преобразовать столбец», затем «Преобразование текста» и, наконец, «Обрезать». Это позволит удалить все лишние пробелы, которые могут помешать работе с данными.

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

Развертывание/поворот данных для улучшения возможностей анализа

Еще одна распространенная проблема Excel — получение данных в таком виде, который затрудняет их обработку и анализ, особенно когда требуется переключить столбцы и строки — или «повернуть» данные. В этом примере у нас есть данные опроса, в которых столбцы разбираются респондентами. В идеале мы хотели бы, чтобы столбцы были разобраны как вопросы опроса. Сначала откройте пустую таблицу Excel и импортируйте лист с помощью Power query. Как и в предыдущем примере, вместо того чтобы нажать «Загрузить», нам нужно сначала нажать «Преобразовать данные».

Теперь, когда Power Query Editor открыт, перейдите на вкладку «Преобразование» на ленте. Выберите все столбцы вопросов опроса вместе, нажмите «Развернуть столбцы», а затем выберите «Развернуть только выбранные столбцы».

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

Экспериментируйте с Power Query

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