Запросы в эксель как делать
Перейти к содержимому

Запросы в эксель как делать

  • автор:

Создание запроса с параметрами в Microsoft Query

При запросе данных в Excel можно использовать входное значение ( параметр), чтобы указать что-то о запросе. Для этого нужно создать запрос с параметрами в Microsoft Query.

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

Примечание Если вы хотите создать запросы с параметрами другим способом, см. создание запроса с параметрами (Power Query).

Последовательность действий

SQL ms Query, в котором подчеркнуто предложение WHERE

  1. Щелкните Данные >Получить & Преобразование данных >Получить данные >из других источников > из Microsoft Query.
  2. Следуйте шагам мастера запросов. На экране Мастер запросов — готово выберите Просмотр данных или изменение запроса в Microsoft Query и нажмите кнопку Готово. Откроется окно Microsoft Query и отобразит запрос.
  3. Нажмите кнопку>SQL. В диалоговом SQL найдите предложение WHERE — строку, которая начинается со слова WHERE, обычно в конце SQL кода. Если предложение WHERE не существует, добавьте его, введя WHERE в новой строке в конце запроса.
  4. После where введите имя поля, оператор сравнения (=, , LIKE и т. д.) и одно из следующих данных:
  5. Для запроса generic parameter (?) введите вопросии (?). В подсказке, которая появляется при запуске запроса, не отображается полезная фраза.

SQL ms Query, в котором подчеркнуто предложение WHERE

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

Диалоговое окно импорта данных в Excel

  • Завершив добавление условий с параметрами в предложение WHERE, нажмите кнопку ОК, чтобы запустить запрос. Excel запрос на в качестве значения для каждого параметра, Microsoft Query отобразит результаты.
  • Когда вы будете готовы загрузить данные, закройте окно Microsoft Query, чтобы вернуться к Excel. Откроется диалоговое окно Импорт данных.

    Диалоговое окно

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

    Диалоговое окно параметра MS Query

    В диалоговом окне Параметры отображаются параметры, используемые в запросе. Выберите параметр в области Имя параметра, чтобы просмотреть или изменить параметр How value is obtained. Вы можете изменить запрос параметра, ввести определенное значение или указать ссылку на ячейку.

    Теперь в книге есть запрос с параметрами. При запуске запроса или обновлении подключения к данным Excel проверяет параметр, чтобы завершить предложение WHERE запроса. Если параметр запросит значение, Excel отобразит диалоговое окно Введите значение параметра для сбора входных данных. Вы можете ввести значение или щелкнуть ячейку со значением. Вы также можете указать, что указанное значение или ссылка всегда должны использоваться, а при использовании ссылки на ячейку можно указать, что Excel должно автоматически обновлять подключение к данным (то есть повторно выполнить запрос) при внесении изменений в указанную ячейку.

    Создание, загрузка и изменение запроса в Excel (Power Query)

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

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

    Выбор ячейки в запросе для вкладки

    Сведения об интеграции Power Query с Excel

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

    Знакомый лист Excel, лента и сетка

    Лента Редактор Power Query и предварительный просмотр данных

    Стандартный Excel

    Обычное представление редактора Power Query

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

    Переименование вкладок листа Рекомендуется осмысленно переименовать вкладки листа, особенно если их много. Особенно важно прояснить разницу между листом данных и листом, загруженным из Редактор Power Query. Даже если у вас есть только два листа, один из которых содержит таблицу Excel с именем Sheet1, а другой — запрос, созданный путем импорта таблицы Excel с именем Table1, легко запутаться. Рекомендуется всегда изменять имена вкладок по умолчанию на имена, которые вам нужны. Например, переименуйте Лист1 в DataTable , а Table1в QueryTable. Теперь ясно, на какой вкладке есть данные, а на какой — запрос.

    Создание запроса

    Можно создать запрос на основе импортированных данных или создать пустой запрос.

    Создание запроса на основе импортированных данных

    Это самый распространенный способ создания запроса.

    1. Импортируйте некоторые данные. Дополнительные сведения см. в разделе Импорт данных из внешних источников данных.
    2. Выделите ячейку в данных, а затем выберите Запрос >Изменить.

    Создание пустого запроса

    Вы можете просто начать с нуля. Это можно сделать двумя способами.

    • Выберите Данные > Получить >данныхиз других источников >пустой запрос.
    • Выберите Данные >Получить данные >запустить Редактор Power Query.

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

    Или можно выбрать Главная , а затем выбрать команду в группе Новый запрос . Выполните одно из указанных ниже действий.

    • Выберите Новый источник , чтобы добавить источник данных. Эта команда похожа на команду Data >Get Data на ленте Excel.
    • Выберите Последние источники, чтобы выбрать источник данных, с которым вы работали. Эта команда похожа на команду Data >Recent Sources на ленте Excel.
    • Выберите Ввести данные , чтобы вручную ввести данные. Вы можете выбрать эту команду, чтобы опробовать Редактор Power Query независимо от внешнего источника данных.

    Загрузка запроса

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

    Загрузка запроса из Редактор Power Query

    В Редактор Power Query выполните одно из следующих действий:

    • Чтобы загрузить на лист, выберите Главная >Закрыть & Загрузить >Закрыть & Загрузить.
    • Чтобы загрузить в модель данных, выберите Главная >Закрыть & Загрузить >Закрыть & Загрузить.

    Совет Иногда команда Load To неактивна или отключена. Это может произойти при первом создании запроса в книге. В этом случае выберите Закрыть & загрузить, на новом листе выберите >запросы & Connections > вкладку Запросы, щелкните запрос правой кнопкой мыши и выберите команду Загрузить в. Кроме того, на ленте Редактор Power Query выберите Запрос > загрузить.

    Загрузка запроса из области «Запросы и Connections»

    В Excel может потребоваться загрузить запрос на другой лист или модель данных.

    1. В Excel выберите Data >Запросы & Connections, а затем перейдите на вкладку Запросы.
    2. В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите команду Загрузить в. Откроется диалоговое окно Импорт данных.
    3. Выберите способ импорта данных, а затем нажмите кнопку ОК. Для получения дополнительных сведений об использовании этого диалогового окна выберите вопросительный знак (?).

    Изменение запроса с листа

    Существует несколько способов изменения запроса, загруженного на лист.

    Изменение запроса из данных на листе Excel

    • Чтобы изменить запрос, найдите ранее загруженный из Редактор Power Query, выделите ячейку в данных, а затем выберите Запрос >Изменить.

    Изменение запроса на панели «Запросы» & Connections

    Вы можете найти область Запросы & Connections удобнее использовать, если в одной книге много запросов и вы хотите быстро найти один.

    1. В Excel выберите Data >Запросы & Connections, а затем перейдите на вкладку Запросы.
    2. В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите изменить.

    Изменение запроса из диалогового окна Свойства запроса

    • В Excel выберите Данные >& Connections > вкладке Запросы, щелкните запрос правой кнопкой мыши и выберите Свойства, выберите вкладку Определение в диалоговом окне Свойства, а затем выберите Изменить запрос.

    Совет Если вы находитесь на листе с запросом, выберите Данные > Свойства, перейдите на вкладку Определение в диалоговом окне Свойства , а затем выберите Изменить запрос.

    Изменение запроса таблицы в модели данных

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

    1. Чтобы открыть модель данных, выберите Power Pivot >Управление.
    2. В нижней части окна Power Pivot выберите вкладку листа нужной таблицы.

    Result (Результат)

    Запрос на листе и таблица в модели данных обновляются.

    Загрузка запроса в модель данных занимает необычно много времени

    Если вы заметили, что загрузка запроса в модель данных занимает гораздо больше времени, чем загрузка на лист, проверка шаги Power Query, чтобы узнать, фильтруется ли текстовый столбец или структурированный столбец списка с помощью оператора Contains. Это действие приводит к повторному перечислению Excel по всему набору данных для каждой строки. Кроме того, Excel не может эффективно использовать многопоточное выполнение. В качестве обходного решения попробуйте использовать другой оператор, например Equals или Begins With.

    Корпорация Майкрософт знает об этой проблеме, и она расследуется.

    Настройка параметров загрузки запроса

    Вы можете загрузить Power Query:

    • На лист. В Редактор Power Query выберите Главная >Закрыть & Загрузить >Закрыть & Загрузить.
    • В модель данных. В Редактор Power Query выберите Главная >Закрыть & Загрузить >Закрыть & Загрузить.

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

    Глобальные параметры, применяемые ко всем книгам

    1. В power Редактор запросов выберите Параметры и параметрыфайла > >Параметры запроса.
    2. В диалоговом окне Параметры запроса в левой части в разделе Global (Глобальный ) выберите Загрузка данных.
    3. В разделе Параметры загрузки запросов по умолчанию выполните следующие действия.
      • Выберите Использовать стандартные параметры загрузки.
      • Выберите Укажите настраиваемые параметры загрузки по умолчанию, а затем выберите или снимите флажок Загрузить на лист или Загрузить в модель данных.

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

    Параметры книги, которые применяются только к текущей книге

    1. В диалоговом окне Параметры запроса в левой части раздела ТЕКУЩАЯ КНИГА выберите Загрузка данных.
    2. Выполните одно или несколько из указанных ниже действий.
    3. В разделе Обнаружение типов выберите или снимите флажок Обнаружение типов столбцов и заголовков для неструктурированных источников.

    Запрос на выборку данных в EXCEL (на основе элементов управления формы)

    Суть запроса на выборку – выбрать из исходной таблицы строки, удовлетворяющие определенным критериям (подобно применению фильтра ). В отличие от фильтра отобранные строки будут помещены в отдельную таблицу.

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

    Задача

    Необходимо отобразить всех сотрудников выбранного отдела.

    Решение с помощью стандартного фильтра

    Это можно легко сделать с помощью стандартного фильтра EXCEL. Выделите заголовки таблицы и нажмите CTRL+SHIFT+L . Через выпадающий список у заголовка Отделы выберите нужный отдел и нажмите ОК.

    Будут отображены все сотрудники выбранного отдела.

    Решение с помощью трехуровневого Связанного списка

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

    Преимущества использования трехуровневого Связанного списка – субъективны. Кому-то нравится работать с фильтром, кому-то со списками. Работать со списками несколько быстрее и информативнее (выбрав дирекцию, автоматически получим список всех ее отделов). Кроме того, в отличие от фильтра отобранные строки будут помещены в отдельную таблицу — своеобразный отчет, который можно форматировать в стиль отличный от исходной таблицы. В этот отчет можно вынести не все столбцы, а только нужные (хотя после применения фильтра ненужные столбцы можно скрыть).

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

    Алгоритм создания запроса на выборку следующий:

    ШАГ 1

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

    Перечень дирекций (столбец А ) будет извлекаться формулой массива из исходной таблицы с перечнем сотрудников:

    Подробности работы этой формулы можно прочитать в статье Отбор уникальных значений .

    Перечень отделов (диапазон B 2: E 8 ) будет извлекаться аналогичной формулой массива в соответствующие столбцы на Листе Списки :

    =ЕСЛИОШИБКА(ИНДЕКС(Сотрудники[Отдел]; ПОИСКПОЗ(0;ЕСЛИ(B$1=Сотрудники[Дирекция];0;1)+ СЧЁТЕСЛИ($B$1:B1;Сотрудники[Отдел]);0));»»)

    ШАГ 2

    Теперь создадим Лист Просмотр , в котором будут содержаться перечень сотрудников выбранного отдела и два списка (дирекции и отделы), сформированных на основе Элемента управления форм Список .

    Первый список создадим для вывода перечня дирекций. Источником строк для него будет созданный ранее динамический диапазон Дирекции. Свяжем его с ячейкой А1 .

    Теперь создадим Динамический диапазон Выбранная_дирекция , который будет содержать название выбранной дирекции:

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

    И, наконец, для вывода фамилий сотрудников (ячейка B 6 ), их номеров телефонов и комнат используем зубодробительную формулу:

    =ЕСЛИОШИБКА(ИНДЕКС(Сотрудники[Сотрудник]; НАИМЕНЬШИЙ(ЕСЛИ((СТРОКА(Сотрудники[Телефон])* (просмотр!$C$1=Сотрудники[Отдел]))=0;»»; СТРОКА(Сотрудники[Телефон])*(просмотр!$C$1=Сотрудники[Отдел])); СТРОКА(Просмотр[[#Эта строка]; [Должность]])-СТРОКА(Просмотр[[#Заголовки]; [Должность]]))-СТРОКА(Сотрудники[[#Заголовки];[Отдел]]));»»)

    Используйте возможности SQL для создания запросов в Excel и напрямую к таблицам Excel

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

    Инструмент XLTools «SQL запросы» расширяет Excel возможностями языка структурированных запросов:

    Создание запросов SQL в интерфейсе Excel и напрямую к Excel таблицам
    Автогенерация запросов SELECT и JOIN
    Доступны JOIN, ORDER BY, DISTINCT, GROUP BY, SUM и другие операторы SQLite
    Создание запросов в интуитивном редакторе с подстветкой синтаксиса
    Обращение к любым таблицам Excel из дерева данных

    Перед началом работы добавьте «Всплывающие часы» в Excel

    «SQL запросы» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

    Начните работу с инструментами XLTools

    Скачать XLTools для Excel
    – пробный период дает 14 дней полного доступа ко всем инструментам.

    Как превратить данные Excel в реляционную базу данных и подготовить их к работе с SQL запросами

    По умолчанию Excel воспринимает данные как простые диапазоны. Но SQL применим только к реляционным базам данных. Поэтому, прежде чем создать запрос, преобразуйте диапазоны Excel в таблицу (именованный диапазон с применением стиля таблицы):

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

    Выберите таблицу Откройте вкладку «Конструктор» Напечатайте имя таблицы. Напр., «КодТовара».

    Повторите эти шаги для каждого диапазона, который планируете использовать в запросах. Напр., «КодТовара», «ЦенаРозн», «ОбъемПродаж», т.д.

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

    XLTools SQL Запросы: подготовка данных и формат таблиц

    Как создать и выполнить запрос SQL SELECT к таблицам Excel

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

    Нажмите кнопку Выполнить SQL на вкладке XLTools Откроется окно редактора.

    В левой части окна находится дерево данных со всеми доступными таблицами Excel. Нажатием на узлы открываются/сворачиваются поля таблицы (столбцы).

  • Добавить комментарий

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