Создать запрос в Excel
Обновлено: 05.07.2024
Power Query предлагает несколько способов создания и загрузки запросов Power в книгу. Вы также можете установить параметры загрузки запроса по умолчанию в окне Параметры запроса.
Совет. Чтобы узнать, формируются ли данные на листе с помощью Power Query, выберите ячейку с данными, и если появится вкладка ленты контекста запроса, значит, данные были загружены из Power Query.
Знать, в какой среде вы находитесь в Power Query, хорошо интегрировано в пользовательский интерфейс Excel, особенно когда вы импортируете данные, работаете с подключениями и редактируете сводные таблицы, таблицы Excel и именованные диапазоны. Во избежание путаницы важно в любой момент времени знать, в какой среде вы сейчас находитесь, в Excel или Power Query.
Знакомые лист Excel, лента и сетка
Лента редактора Power Query и предварительный просмотр данных
Например, работа с данными на листе Excel принципиально отличается от работы с Power Query. Кроме того, подключенные данные, которые вы видите на листе Excel, могут иметь или не иметь Power Query, работающий за кулисами для формирования данных. Это происходит только при загрузке данных на лист или в модель данных из Power Query.
Переименовывать вкладки рабочего листа Рекомендуется переименовывать вкладки рабочего листа осмысленно, особенно если их много. Особенно важно прояснить разницу между рабочим листом данных и рабочим листом, загруженным из редактора Power Query. Даже если у вас есть только два рабочих листа, один с таблицей Excel с именем Sheet1, а другой с запросом, созданным путем импорта этой таблицы Excel с именем Table1, легко запутаться. Всегда полезно изменить имена вкладок рабочего листа по умолчанию на имена, которые имеют для вас больше смысла. Например, переименуйте Sheet1 в DataTable и Table1 в QueryTable. Теперь понятно, на какой вкладке данные, а на какой запрос.
Вы можете либо создать запрос из импортированных данных, либо создать пустой запрос.
Создайте запрос из импортированных данных
Это наиболее распространенный способ создания запроса.
Импорт некоторых данных. Дополнительные сведения см. в разделе Импорт данных из внешних источников данных.
Выберите ячейку в данных, а затем выберите Запрос > Изменить.
Создайте пустой запрос
Возможно, вы захотите начать с нуля. Есть два способа сделать это.
Выберите Данные > Получить данные > Из других источников > Пустой запрос.
Выберите Данные > Получить данные > Запустить редактор Power Query.
На этом этапе вы можете вручную добавлять шаги и формулы, если хорошо знаете язык формул Power Query M.
Или вы можете выбрать «Главная», а затем выбрать команду в группе «Новый запрос». Выполните одно из следующих действий.
Выберите Новый источник, чтобы добавить источник данных. Эта команда аналогична команде Данные > Получить данные на ленте Excel.
Выберите «Недавние источники», чтобы выбрать источник данных, с которым вы работали. Эта команда аналогична команде Данные > Недавние источники на ленте Excel.
Выберите «Ввести данные», чтобы ввести данные вручную. Вы можете выбрать эту команду, чтобы опробовать редактор Power Query независимо от внешнего источника данных.
Предполагая, что ваш запрос верен и не содержит ошибок, вы можете загрузить его обратно на лист или в модель данных.
Загрузить запрос из редактора Power Query
В редакторе Power Query выполните одно из следующих действий:
Чтобы загрузить на лист, выберите Главная > Закрыть и загрузить > Закрыть и загрузить.
Чтобы загрузить модель данных, выберите Главная > Закрыть и загрузить > Закрыть и загрузить в.
В диалоговом окне "Импорт данных" выберите "Добавить эти данные в модель данных".
Совет. Иногда команда «Загрузить в» неактивна или неактивна. Это может произойти при первом создании запроса в книге. В этом случае выберите «Закрыть и загрузить», на новом листе выберите «Данные» > «Запросы и подключения» > вкладка «Запросы», щелкните запрос правой кнопкой мыши и выберите «Загрузить в». Либо на ленте редактора Power Query выберите "Запрос" > "Загрузить в".
Загрузить запрос с панели "Запросы и подключения"
В Excel может потребоваться загрузить запрос на другой лист или в другую модель данных.
В Excel выберите Данные > Запросы и соединения, а затем перейдите на вкладку Запросы.
В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите Загрузить в. Появится диалоговое окно «Импорт данных».
Решите, как вы хотите импортировать данные, а затем нажмите кнопку ОК. Чтобы получить дополнительные сведения об использовании этого диалогового окна, щелкните знак вопроса (?).
Существует несколько способов редактирования запроса, загруженного на лист.
Редактировать запрос на основе данных на листе Excel
Чтобы изменить запрос, найдите его, ранее загруженный из редактора Power Query, выберите ячейку в данных, а затем выберите "Запрос" > "Изменить".
Редактировать запрос на панели "Запросы и подключения"
Вы можете обнаружить, что панель "Запросы и подключения" удобнее использовать, когда у вас есть много запросов в одной книге, и вы хотите быстро найти один из них.
В Excel выберите Данные > Запросы и соединения, а затем перейдите на вкладку Запросы.
В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите "Изменить".
Редактировать запрос в диалоговом окне "Свойства запроса"
В Excel выберите "Данные" > "Данные и соединения" > вкладка "Запросы", щелкните запрос правой кнопкой мыши и выберите "Свойства", выберите вкладку "Определение" в диалоговом окне "Свойства", а затем выберите "Редактировать запрос".
Совет. Если вы находитесь на листе с запросом, выберите "Данные" > "Свойства", выберите вкладку "Определение" в диалоговом окне "Свойства", а затем выберите "Редактировать запрос".
Модель данных обычно содержит несколько таблиц, связанных между собой. Вы загружаете запрос в модель данных, используя команду «Загрузить в», чтобы отобразить диалоговое окно «Импорт данных», а затем установите флажок «Добавить эти данные в модель данных». Дополнительные сведения о моделях данных см. в статьях Определение источников данных, используемых в модели данных книги, Создание модели данных в Excel и Использование нескольких таблиц для создания сводной таблицы.
Чтобы открыть модель данных, выберите Power Pivot > Управление.
В нижней части окна Power Pivot выберите вкладку рабочего листа нужной таблицы.
Подтвердите, что отображается правильная таблица. В модели данных может быть много таблиц.
Обратите внимание на название таблицы.
Чтобы закрыть окно Power Pivot, выберите «Файл» > «Закрыть». Освобождение памяти может занять несколько секунд.
Выберите "Данные" > "Подключения и свойства" > вкладка "Запросы", щелкните запрос правой кнопкой мыши и выберите "Изменить".
После внесения изменений в редакторе Power Query выберите "Файл" > "Закрыть и загрузить".
Запрос на листе и таблица в модели данных обновляются.
Если вы заметили, что загрузка запроса в модель данных занимает гораздо больше времени, чем загрузка на лист, проверьте шаги Power Query, чтобы узнать, фильтруете ли вы текстовый столбец или столбец со структурой списка с помощью оператора «Содержит». Это действие заставляет Excel снова перебирать весь набор данных для каждой строки. Кроме того, Excel не может эффективно использовать многопоточное выполнение. В качестве обходного пути попробуйте использовать другой оператор, например Equals или Begins With.
Microsoft известно об этой проблеме, и она расследуется.
Вы можете загрузить Power Query:
На лист. В редакторе Power Query выберите Главная > Закрыть и загрузить > Закрыть и загрузить.
К модели данных. В редакторе Power Query выберите Главная > Закрыть и загрузить > Закрыть и загрузить в.
По умолчанию Power Query загружает запросы на новый лист при загрузке одного запроса и одновременно загружает несколько запросов в модель данных. Вы можете изменить поведение по умолчанию для всех своих книг или только для текущей книги. При установке этих параметров Power Query не изменяет результаты запроса на листе или в данных и аннотациях модели данных.
Вы также можете динамически переопределять параметры запроса по умолчанию с помощью диалогового окна "Импорт", которое отображается после выбора "Закрыть и загрузить в".
Глобальные настройки, которые применяются ко всем вашим книгам
В редакторе Power Query выберите "Файл" > "Параметры и настройки" > "Параметры запроса".
В левой части диалогового окна "Параметры запроса" в разделе "ГЛОБАЛЬНЫЕ" выберите "Загрузка данных".
В разделе "Параметры загрузки запросов по умолчанию" выполните следующие действия:
Выберите Использовать стандартные настройки загрузки.
Выберите Указать настраиваемые параметры загрузки по умолчанию, а затем установите или снимите флажок Загрузить на лист или Загрузить в модель данных.
Совет. В нижней части диалогового окна вы можете выбрать Восстановить значения по умолчанию, чтобы удобно вернуться к настройкам по умолчанию.
Настройки книги, которые применяются только к текущей книге
В левой части диалогового окна "Параметры запроса" в разделе ТЕКУЩАЯ РАБОЧАЯ КНИГА выберите "Загрузка данных".
Выполните одно или несколько из следующих действий:
В разделе "Определение типа" установите или снимите флажок "Определять типы столбцов и заголовки для неструктурированных источников".
По умолчанию они обнаруживаются. Снимите этот флажок, если вы предпочитаете формировать данные самостоятельно.
В разделе "Связи" установите или снимите флажок "Создавать связи между таблицами при первом добавлении в модель данных".
Перед загрузкой в модель данных по умолчанию выполняется поиск существующих отношений между таблицами, таких как внешние ключи в реляционной базе данных, и их импорт вместе с данными. Снимите этот флажок, если вы предпочитаете делать это самостоятельно.
В разделе "Отношения" установите или снимите флажок Обновлять отношения при обновлении запросов, загруженных в модель данных.
По умолчанию отношения не обновляются. При обновлении запросов, уже загруженных в модель данных, Power Query находит существующие связи между таблицами, например внешние ключи в реляционной базе данных, и обновляет их.Это может удалить отношения, созданные вручную после импорта данных, или ввести новые отношения. Однако, если вы хотите сделать это, выберите вариант.
В разделе "Фоновые данные" установите или снимите флажок Разрешить предварительный просмотр данных для загрузки в фоновом режиме.
По умолчанию предварительный просмотр данных загружается в фоновом режиме. Снимите этот флажок, если вы хотите сразу просмотреть все данные.
В этом примере показано, как импортировать данные из базы данных Microsoft Access с помощью мастера запросов Microsoft. С помощью Microsoft Query вы можете выбрать нужные столбцы данных и импортировать только эти данные в Excel.
<р>1. На вкладке "Данные" в группе "Получить и преобразовать данные" нажмите "Получить данные".
Появится диалоговое окно "Выбор источника данных".
<р>3. Выберите базу данных MS Access* и установите флажок «Использовать мастер запросов для создания/редактирования запросов».
Эта база данных Access состоит из нескольких таблиц. Вы можете выбрать таблицу и столбцы, которые хотите включить в свой запрос.
<р>6. Выберите «Клиенты» и нажмите символ >.
Чтобы импортировать только определенный набор записей, отфильтруйте данные.
<р>8. Нажмите "Город" в списке "Столбец для фильтрации" и включите только строки, в которых "Город" равен "Нью-Йорк".
При желании вы можете отсортировать данные (здесь мы этого не делаем).
Вы можете использовать Microsoft Query в Excel для извлечения данных из рабочей книги Excel, а также из внешних источников данных с помощью операторов SQL SELECT. Запросы Excel, созданные таким образом, можно обновлять и запускать повторно, что делает их удобным и эффективным инструментом в Excel.
Microsoft Query позволяет использовать SQL непосредственно в Microsoft Excel, рассматривая листы как таблицы, для которых можно запускать операторы Select с JOIN, UNION и т. д. Часто операторы Microsoft Query будут более эффективными, чем формулы Excel или макрос VBA. Microsoft Query (он же MS Query, он же Excel Query) на самом деле является оператором SQL SELECT. Excel и Access используют поставщиков Windows ACE.OLEDB или JET.OLEDB для выполнения запросов. Это невероятный, часто неиспользуемый инструмент, который многие пользователи недооценивают!
Что я могу делать с MS Query?
Используя MS Query в Excel, вы можете извлекать данные из различных источников, таких как:
- Файлы Excel: вы можете извлекать данные из внешних файлов Excel, а также выполнять запрос SELECT в текущей книге.
- Access – вы можете извлекать данные из файлов базы данных Access.
- MS SQL Server — вы можете извлекать данные из таблиц Microsoft SQL Server
- CSV и текст — вы можете загружать CSV или табличные текстовые файлы.
Шаг за шагом — Microsoft Query в Excel
В этом пошаговом руководстве я покажу вам, как создать Microsoft Query для извлечения данных либо из вашей текущей рабочей книги, либо из внешнего файла Excel.
Я буду извлекать данные из внешнего файла Excel с именем MOCK DATA.xlsx. В этом файле у меня есть список макетов клиентов мужского/женского пола. Я хочу создать простой запрос, чтобы вычислить, сколько мужчин и сколько женщин.
Открыть мастер MS Query (из других источников)
Перейдите на вкладку ленты ДАННЫЕ и щелкните Из других источников . Выберите последний вариант From Microsoft Query .
Выберите источник данных
Далее нам нужно указать источник данных для нашего Microsoft Query. Чтобы продолжить, выберите файлы Excel.
Выбрать исходный файл Excel
Теперь нам нужно выбрать файл Excel, который будет источником для нашего Microsoft Query. В моем примере я выберу свою текущую рабочую книгу, ту же, из которой я создаю свой MS Query.
Выберите столбцы для вашего запроса MS
Мастер теперь попросит вас выбрать столбцы для вашего запроса MS. Если вы планируете позже изменить MS Query вручную, просто нажмите OK. В противном случае выберите свои столбцы.
Вернуть запрос или изменить запрос
Теперь у вас есть два варианта:
- Вернуть данные в Microsoft Excel — это вернет результаты вашего запроса в Excel и завершит работу мастера.
- Просмотр данных или редактирование запроса в Microsoft Query. Откроется окно Microsoft Query и вы сможете изменить свой Microsoft Query.
Необязательно: изменить запрос
Если вы выберете параметр «Просмотр данных или редактирование запроса в Microsoft Query», теперь вы можете открыть окно «Редактировать запрос SQL», нажав кнопку SQL. Когда закончите, нажмите кнопку возврата (та, что с открытой дверью).
Импорт данных
Когда вы закончите изменять оператор SQL (как я на предыдущем шаге). Нажмите кнопку «Вернуть данные» в окне Microsoft Query.
При этом должно открыться окно «Импорт данных», в котором можно выбрать, когда данные должны быть выгружены.
Наконец, когда вы закончите, нажмите OK в окне Import Data, чтобы завершить выполнение запроса. Вы должны увидеть результат запроса в виде новой таблицы Excel:
Как и в окне выше, я подсчитал, сколько записей в исходной таблице содержит мужской и женский пол.
КАК вы видите, что для достижения чего-то потенциально довольно простого необходимо выполнить довольно много шагов. Следовательно, есть несколько альтернатив благодаря мощи VBA Macro….
MS Query — создание с помощью VBA
Если вы не хотите использовать надстройку SQL, можно создать эти запросы с помощью макроса VBA. Ниже приведен быстрый макрос, который позволит вам написать запрос в простом поле ввода VBA в выбранном диапазоне на вашем листе.
Просто используйте мой фрагмент кода VBA:
Просто создайте новый модуль VBA и вставьте приведенный выше код. Вы можете запустить его, нажав сочетание клавиш CTRL + SHIFT + S или добавив макрос на панель быстрого доступа.
Изучение SQL с помощью Excel
Создание MS-запросов — это одно, но вам нужно довольно хорошо разбираться в языке SQL, чтобы использовать его истинный потенциал. Я рекомендую использовать простую базу данных Excel (например, Northwind) и практиковаться в различных запросах с JOIN.
Альтернативы в Excel — Power Query
Еще один способ выполнения запросов — использовать Microsoft Power Query (также известный в Excel 2016 и более поздних версиях как Get and Transform). Надстройка, предоставляемая Microsoft, требует знания языка SQL, а позволяет вам нажимать на данные, которые вы хотите преобразовать.
Выводы по MS Query и Power Query
Преимущества MS Query. Power Query — это потрясающий инструмент , однако он не делает Microsoft Queries полностью недействительными. Более того, иногда использование Microsoft Queries быстрее и удобнее, и вот почему:
- Запросы Microsoft более эффективны, если вы знаете SQL. Хотя вы можете перейти к преобразованию данных с помощью Power Query, кто-то, кто знает SQL, вероятно, гораздо быстрее напишет подходящий запрос SELECT
- Вы не можете повторно запустить Power Queries без надстройки. Хотя это, очевидно, станет менее верным утверждением, вероятно, через пару лет (в более новых версиях Excel), в настоящее время, если у вас нет надстройки, вы не сможете редактировать или повторно запускать запросы, созданные в Power Query
Минусы MS Query. Однако Microsoft Query уступает надстройке Power Query в некоторых других аспектах:
- У Power Query более удобный пользовательский интерфейс. В то время как Power Queries относительно легко создавать, мастер MS Query Wizard похож на веб-сайт из 90-х годов.
- Power Query размещает операции друг над другом, что упрощает внесение изменений. В то время как MS Query работает или просто не компилируется, Power Query складывает каждую операцию преобразования, обеспечивая видимость вашей задачи преобразования данных и упрощая добавление/удаление операций.
Короче говоря, я рекомендую изучать Power Query, если вы не чувствуете себя комфортно в SQL. Если вы хорошо разбираетесь в SQL, я думаю, вам будет удобнее использовать старые добрые Microsoft Queries. Я бы сравнил это с извечной дискуссией между разработчиками командной строки и разработчиками графического интерфейса…
Том (AnalystCave)
В этой статье объясняется простой способ создания Microsoft Query из Excel.
Мастер Microsoft Query — это более простой способ быстро создать Microsoft Query прямо из Excel для быстрого доступа к данным Acctivate QuickBooks Inventory Management.
Основные шаги по настройке Microsoft Query с помощью мастера запросов приведены ниже, они могут незначительно отличаться в зависимости от вашей версии Microsoft Excel.
- Во-первых, необходимо установить соединение с базой данных ODBC. Пользователям, размещенным на хостинге, возможно, потребуется обратиться за помощью к хостинг-провайдеру.
- Далее создайте запрос Microsoft Excel.
В Excel:
Выберите источник данных:
<р>4. Введите данные для входа в SQL Server <р>5. Нажмите «Параметры», чтобы развернуть параметры базы данных по умолчанию <р>6. Выберите раскрывающийся список для базы данных и выберите свою базу данных Activate <р>7. Нажмите "ОК". 
Мастер запросов
Выбрать представления базы данных
Теперь вы готовы выбрать представления и поля, которые хотите вернуть в Excel. Вы можете запускать запросы Excel с любой рабочей станции, на которой настроено подключение к базе данных.
Например, допустим, вам нужен список адресов электронной почты клиентов.
- Прокрутите вниз и найдите представление «CustomerEmailAddressList».
- Чтобы выбрать «Все поля» в представлении
- Выберите представление и нажмите стрелку вправо (>).
- Это переместит все столбцы в ваш запрос.
- Нажмите на значок плюса (+), чтобы развернуть и просмотреть доступные поля.
- Выберите поле, которое хотите отобразить, и нажмите стрелку вправо (>).
- Выберите поле, которое хотите использовать в качестве фильтра.
- Выберите критерии фильтра.
- Нажмите "Далее".
4.По завершении нажмите «Далее».
Поздравляем, вы только что создали простой запрос Excel с помощью мастера запросов! Теперь у вас есть список адресов электронной почты клиентов в Excel от Acctivate.
Читайте также: