Полное руководство по инструменту Power Query в Excel
4 ноября 2021 г.
Использование Excel для выполнения задач — это навык, который многие специалисты по данным используют в своей работе и личной жизни. Одной из функций, которые Excel предлагает для более эффективного импорта данных, является мощный запрос. Знание того, что такое мощный запрос и как его использовать, может улучшить ваши навыки работы с компьютером, ускорить рабочие процессы и помочь вам лучше управлять данными. В этой статье мы обсудим, что такое мощный запрос, функции, которые он предоставляет для Excel, фазы, которые он использует, и пример того, как он работает.
Что такое силовой запрос в Excel?
Power query — это инструмент в Excel, который можно использовать для импорта данных из разных файлов и сортировки этих данных в удобном для использования формате. Запрос в Excel — это набор правил, которые позволяют сортировать данные, и, в отличие от других фильтров в Excel, мощные запросы необходимо настроить только один раз, чтобы они выполняли свои функции. Мощные запросы также позволяют изменять необработанные данные вне Excel и использовать их в диаграммах и отчетах в программе.
Power query может быть полезен, если вы работаете с большим количеством строк данных, поскольку он фильтрует данные, чтобы отображать только то, что вам нужно. Он также позволяет объединять таблицы и получать доступ к другим ресурсам, таким как CSV-файлы, текстовые файлы и другие книги Excel. Поскольку вы вводите функцию расширенного запроса только один раз, вы можете написать ее для книги Excel один раз и обновить данные, когда вам это нужно. Например, вы можете обновлять данные один раз в неделю, один раз в год или чаще, если вы обращаетесь к данным нечасто.
Программы для Windows, мобильные приложения, игры - ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале - Подписывайтесь:)
Функции мощного запроса в Excel
Инструмент мощных запросов может получить доступ к любой функции, которую использует Excel. Одной из наиболее распространенных функций, которые он использует, является ВПР, которую вы можете использовать для поиска определенной точки данных в указанном диапазоне ячеек, например, в ячейках с A1 по Z26. Этот метод ссылки на ячейку в Excel обычно использует обозначение «имя ячейки: имя ячейки», которое сообщает программе, какие ячейки включить в функцию. Другими распространенными функциями мощного запроса являются математические функции СУММ, СРЗНАЧ, ВЫЧИТАНИЕ и ПРОИЗВЕД.
Этапы мощного запроса в Excel
Когда вы используете мощный инструмент запросов в Excel, он выполняет четыре этапа пользовательского ввода. Описания того, что делает каждая фаза запроса мощности и что вы вводите:
1. Подключить
Первый этап запроса мощности — это подключение к данным, которыми вы хотите управлять. Чтобы подключиться к данным из другого файла на вашем компьютере, выполните следующие действия:
Выберите вкладку «Данные» на ленте в верхней части программы Excel.
Выберите раскрывающийся вариант «Получить данные» на вкладке «Получить и преобразовать данные».
Наведите указатель мыши на параметр «Из файла», чтобы открыть другое меню.
Выберите расположение, из которого вы хотите получить данные, например «Из папки», «Из книги» или «Из текста/CSV».
Нажмите «Обзор», чтобы найти нужный файл.
Выберите «ОК».
После того, как вы нажмете «ОК», появится диалоговое окно, в котором вы можете выбрать, как взаимодействовать с данными. Три варианта, которые он имеет для вас на выбор:
«Объединить и преобразовать данные»: этот параметр позволяет преобразовать данные в более чистую и организованную версию и может быть полезен, если вам нужно, чтобы данные выглядели иначе, чем в исходном источнике.
«Объединить и загрузить»: этот параметр импортирует данные непосредственно из первого файла в Excel и может быть полезен, если вы уже упорядочили данные так, как хотите.
«Объединить и загрузить в…»: этот параметр позволяет экспортировать данные из исходного источника и импортировать их в другое место из книги Excel.
2. Преобразование
Если вам нужно изменить способ отображения данных в Excel, например объединить или разделить столбцы или упорядочить данные в алфавитном порядке, вы можете использовать этот этап расширенного запроса, чтобы применить эти изменения. Ниже приведены шаги, которые вы можете выполнить, чтобы преобразовать данные для Excel:
Выберите «Преобразовать образец файла».
Внесите свои коррективы в данные.
Нажмите «Применить», чтобы запрос изменил данные.
3. Объединить
После изменения данных в одном файле вы можете импортировать другие файлы данных в новую книгу Excel. Запрос обновляет новые файлы и импортирует их с преобразованиями, которые вы сделали в первом файле. Это полезно, потому что вы можете изменить данные в одном месте, а Excel автоматически обновит их в подключенных местах.
4. Загрузка
После написания запроса и импорта данных вы можете сохранить запрос и книгу. Это позволяет вам позже получить доступ к изменениям, внесенным в данные, и к запросу питания. Это также этап, на котором вы можете решить, куда загружаются данные при их импорте. Следующие шаги могут помочь вам загрузить данные в определенную диаграмму:
Выберите «Закрыть и загрузить в…» в левом верхнем углу ленты Excel.
Выберите место для новых данных.
Нажмите «ОК», чтобы Excel импортировал данные в это место.
Пример использования мощного запроса в Excel
Ниже приведен пример использования мощного запроса в Excel для подключения, преобразования, объединения и загрузки данных о дочерних компаниях за 2018, 2019 и 2020 годы, записанных в разные файлы:
1. Подключитесь к файлам данных
Сначала подключитесь к файлам данных, используя ярлык ленты Excel для импорта данных с вкладки данных. Открыв диалоговое окно, вы можете выбрать файлы. Например, если вы сохранили 2018 год в виде файла CSV, а файлы за 2019 и 2020 годы вы сохранили в виде книг Excel, вы можете выбрать все файлы. Следующие шаги являются примером подключения к файлам:
Перейдите на вкладку «Данные» на ленте Excel.
Выберите «Получить данные».
Выберите «Из папки».
Перейдите в папку, в которой вы сохранили файлы данных.
Выберите «ОК», чтобы подключиться к данным.
2. Измените данные
После того, как вы откроете новое диалоговое окно с подключением данных, вы сможете произвести с ним преобразования. Если вы отсортировали данные в файлах по-разному, например, заголовки отображаются в другом порядке, рассмотрите возможность их настройки на этом шаге. Вы можете использовать инструменты в диалоговом окне, чтобы убедиться, что это правильно, поскольку запрос питания выполняет свои задачи для одного файла. Это важно для объединения данных на следующем шаге.
3. Объедините данные
После преобразования данных вы можете объединить каждый из файлов данных в рабочую книгу, которую вы используете. В более новых версиях Excel этот шаг выполняется автоматически при подключении к файлам данных. Это полезно, потому что вам не нужно выбирать файлы во второй раз. Если вы хотите включить четвертый файл, например данные за 2017 год, вы можете выбрать этот файл на этом шаге, и Excel включит его в объединенную таблицу.
4. Загрузите данные
Теперь вы можете загрузить данные в определенное место. В этом примере данные загружаются в сводную таблицу Excel. Ниже приведены шаги, которые могут помочь вам создать сводную таблицу из запроса мощности:
Выберите «Закрыть и загрузить в…» в левом верхнем углу ленты Excel.
Выберите «Сводная таблица»
Нажмите «ОК»
После того, как вы нажмете «ОК», Excel заполнит сводную таблицу данными, которые вы включили в этапы подключения и объединения, создавая единую таблицу, к которой вы можете получить доступ или добавить новые данные, например данные за 2021 год, как только вы их получите.
Обратите внимание, что ни один из продуктов или компаний, упомянутых в этой статье, не связан с компанией Indeed.