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

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

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

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

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

С помощью power-query вы можете импортировать, очищать, преобразовывать и объединять наборы данных из нескольких источников. Как только вы научитесь его использовать, вам станет намного проще собирать и интерпретировать данные из разных источников.

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

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

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

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

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

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

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

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

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

Моя цель — объединить эти наборы данных в один лист, чтобы данные были очищены, легко интерпретировались и анализировались в одном месте.

Шаг 1. Откройте Excel и получите доступ к запросу мощности.

Прежде всего откройте основную таблицу, с которой вы будете работать. В данном случае я открыл таблицу продаж в США, так как именно здесь я хочу очистить и объединить данные.

На главной ленте нажмите «Данные». Вы увидите «Получить данные (Power Query)» в качестве первого элемента на ленте «Данные».

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

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

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

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

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

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

Если на этом этапе вы выберете «Загрузить», power-query импортирует данные в новую вкладку существующей электронной таблицы.

Оттуда вы можете выбрать «Данные» и «Запустить редактор Power Query», чтобы выполнить массовую очистку или обновление импортированных данных.

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

Если вы предпочитаете очистить данные перед их импортом, вы можете сделать это на этапе импорта.

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

Например, таблица продаж в Великобритании содержит те же данные, что и таблица в США, но отформатирована неправильно. Формат валюты не применяется к ячейке валюты.

Затем в редакторе Power Query я могу выбрать вкладку «Преобразование», чтобы редактировать данные перед их импортом (я также мог бы сделать это, если бы сначала импортировал данные, а затем открыл редактор Power Query).

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

Оттуда я могу выбрать опцию «Валюта» и изменить значение на доллары США.

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

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

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

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

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

Итак, давайте посмотрим на несколько примеров того, что можно сделать с помощью Power Quest.

Примеры Power Query

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

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

Power Query упрощает эту задачу.

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

Откройте Excel в пустой книге и получите доступ к запросу мощности с помощью опции «Получить данные».

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

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

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

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

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

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

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

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

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

Power Query упрощает процесс импорта данных, поэтому вам не придется тратить время на их очистку вручную после этого.

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

Допустим, отдел продаж во Франции прислал нам отчет о продажах. Выглядит нормально, но часть текста в столбце «Идентификатор страны» написана заглавными буквами, а часть — нет. Кроме того, в названии страны появились случайные пробелы, которые тоже нужно исправить, иначе нам будет сложно подтягивать данные в сводные таблицы и другую отчетность.

На своем листе откройте power-query и импортируйте файл с беспорядочными данными. Нажмите «Преобразовать данные» вместо того, чтобы импортировать их сразу.

Сначала найдите столбец со смешанными заглавными буквами и дополнительными пробелами. Щелкните столбец правой кнопкой мыши и выберите «Преобразовать столбец», затем «Преобразования текста» и, наконец, «Обрезать». Это позволит удалить лишние пробелы, которые могут помешать манипулированию данными.

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

Теперь вы можете нажать «Загрузить», чтобы очищенные данные были импортированы в лист Excel.

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

Еще одна распространенная проблема Excel — получение данных таким образом, что ими сложно манипулировать и анализировать, особенно когда вы хотите переключаться между столбцами и строками или «поворачивать» данные.

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

Сначала откройте пустую таблицу Excel и импортируйте ее с помощью потокового запроса.

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

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

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

Поэкспериментируйте с Power Query

Среди огромного набора инструментов Excel является одним из самых мощных, когда речь идет о сортировке, очистке и преобразовании данных, используемых в повседневных бизнес-операциях в нескольких отделах и командах.

Хотя изучение всех возможностей и функций чего-то вроде power-query может занять некоторое время, оно того стоит.

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