Excel параметры в запросе
Обновлено: 02.07.2024
Параметр Microsoft Query можно запрос путем изменения запроса, использования данных из ячейки в качестве запроса или константы.
Дополнительные сведения о создании запросов с параметрами см. в записи Использование Microsoft Query для извлечения внешних данных.
Примечание: Следующие процедуры не применимы к запросам, созданным с помощью Power Query.
Изменение настраиваемой подсказки для запроса с параметрами
На компьютере щелкните ячейку в любом месте диапазона внешних данных, созданного с помощью запроса с параметрами.
На вкладке Данные в группе Запросы & Connections нажмитекнопку Свойства.
В диалоговом окне Свойства нажмите кнопку Свойства подключения .
В диалоговом окне Свойства подключения перейдите на вкладку Определение и выберите параметры.
В диалоговом окне Параметры в списке Имя параметра выберите параметр, который вы хотите изменить.
В поле Запрос значения с использованием следующей строки введите текст запроса и нажмите кнопку ОК. Настраиваемая подсказка может содержать до 100 символов.
Чтобы использовать новую настраиваемую подсказку и обновить данные, щелкните стрелку рядом с кнопкой Обновить все на вкладке Данные в группе Запросы & Connections и выберите обновить .
В диалоговом окне Введите значение параметра отобразиться новое окно.
Примечание: При желании, чтобы избежать повторного запроса во время каждой операции обновления, можно выбрать вариант Использовать это значение или ссылку для будущих обновлений. Если в качестве значения параметра используются данные из ячейки, выберите автоматически обновлять при смене значения ячейки.
Использование данных из ячейки в качестве значения параметраНа компьютере введите значения, которые вы хотите использовать в запросе в качестве условия.
Щелкните ячейку в любом месте диапазона внешних данных, созданного с помощью запроса.
На вкладке Данные в группе Запросы & Connections нажмитекнопку Свойства.
В диалоговом окне Свойства нажмите кнопку Свойства подключения .
В диалоговом окне Свойства подключения перейдите на вкладку Определение и выберите параметры.
В диалоговом окне Параметры в списке Имя параметра выберите параметр, который вы хотите изменить.
Щелкните Получить значение из следующей ячейки.
Щелкните на нем ячейку со значением, которое вы хотите использовать.
Если вы хотите обновлять данные при изменении значения в ячейке, выберите автоматически обновлять при изменении значения ячейки.
Чтобы обновить данные, щелкните стрелку рядом с кнопкой Обновить все на вкладке Данные в группе Запросы & Connections и выберите обновить.
Использование константы значения параметра для запросаЩелкните на своем компьютере ячейку в диапазоне внешних данных, созданном с помощью запроса.
На вкладке Данные в группе Запросы & Connections нажмитекнопку Свойства.
В диалоговом окне Свойства нажмите кнопку Свойства подключения .
В диалоговом окне Свойства подключения перейдите на вкладку Определение и выберите параметры.
В диалоговом окне Параметры в списке Имя параметра выберите параметр, который вы хотите изменить.
Щелкните Использовать следующее значение.
Введите значение параметра и нажмите кнопку ОК.
Чтобы обновить данные, щелкните стрелку рядом с кнопкой Обновить все на вкладке Данные в группе Запросы & Connections и выберите обновить.
Изменение настраиваемой подсказки для запроса с параметрами
Щелкните ячейку на своем компьютере в любом месте диапазона внешних данных, созданного с помощью запроса с параметрами.
На вкладке Данные в группе Подключения нажмите кнопку Свойства.
В диалоговом окне Свойства нажмите кнопку Свойства подключения .
В диалоговом окне Свойства подключения перейдите на вкладку Определение и выберите параметры.
В диалоговом окне Параметры в списке Имя параметра выберите параметр, который вы хотите изменить.
В поле Запрос значения с использованием следующей строки введите текст запроса и нажмите кнопку ОК. Настраиваемая подсказка может содержать до 100 символов.
Чтобы использовать новый запрос и обновить данные, щелкните стрелку рядом с кнопкой Обновить все на вкладке Данные в группе Подключения. Затем нажмите кнопку Обновить.
В диалоговом окне Введите значение параметра отобразиться новое окно.
Примечание: При желании, чтобы избежать повторного запроса во время каждой операции обновления, можно выбрать вариант Использовать это значение или ссылку для будущих обновлений. Если в качестве значения параметра используются данные из ячейки, выберите автоматически обновлять при смене значения ячейки.
Примечание: Если вы хотите сохранить изменения в настраиваемом запросе, сохраните книгу. Вы также можете изменить запрос, чтобы изменить его. Дополнительные сведения о редактировании запроса Microsoft Query см. в справке Microsoft Query.
Использование данных из ячейки в качестве значения параметраНа компьютере введите значения, которые вы хотите использовать условия в запросе.
Щелкните ячейку в любом месте диапазон внешних данных, созданной с помощью запроса с параметрами.
На вкладке Данные в группе Подключения нажмите кнопку Свойства.
В диалоговом окне Свойства нажмите кнопку Свойства подключения .
В диалоговом окне Свойства подключения перейдите на вкладку Определение и выберите параметры.
В диалоговом окне Параметры в списке Имя параметра выберите параметр, который вы хотите изменить.
Щелкните Получить значение из следующей ячейки.
На компьютере щелкните ячейку со значением, которое вы хотите использовать.
Если вы хотите обновлять данные при изменении значения в ячейке, выберите автоматически обновлять при изменении значения ячейки.
Чтобы обновить данные, щелкните стрелку рядом с кнопкой Обновить все на вкладке Данные в группе Подключения и выберите обновить.
Использование константы значения параметра для запросаВ Excel щелкните ячейку в диапазон внешних данных, созданной с помощью запрос с параметрами.
На вкладке Данные в группе Подключения нажмите кнопку Свойства.
В диалоговом окне Свойства нажмите кнопку Свойства подключения .
В диалоговом окне Свойства подключения перейдите на вкладку Определение и выберите параметры.
В диалоговом окне Параметры в списке Имя параметра выберите параметр, который вы хотите изменить.
Щелкните Использовать следующее значение.
Введите значение параметра и нажмите кнопку ОК.
Чтобы обновить данные, щелкните стрелку рядом с кнопкой Обновить все на вкладке Данные в группе Подключения и выберите обновить.
Изменение настраиваемой подсказки для запроса с параметрами
Щелкните ячейку на своем компьютере в любом месте диапазона внешних данных, созданного с помощью запроса с параметрами.
На вкладке Данные в группе Подключения нажмите кнопку Свойства.
В диалоговом окне Свойства нажмите кнопку Свойства подключения .
В диалоговом окне Свойства подключения перейдите на вкладку Определение и выберите параметры.
В диалоговом окне Параметры в списке Имя параметра выберите параметр, который вы хотите изменить.
В поле Запрос значения с использованием следующей строки введите текст запроса и нажмите кнопку ОК. Настраиваемая подсказка может содержать до 100 символов.
Чтобы использовать новый запрос и обновить данные, щелкните стрелку рядом с кнопкой Обновить все на вкладке Данные в группе Подключения. Затем нажмите кнопку Обновить.
В диалоговом окне Введите значение параметра отобразиться новое окно.
Примечание: При желании, чтобы избежать повторного запроса во время каждой операции обновления, можно выбрать вариант Использовать это значение или ссылку для будущих обновлений. Если в качестве значения параметра используются данные из ячейки, выберите автоматически обновлять при смене значения ячейки.
Примечание: Если вы хотите сохранить изменения в настраиваемом запросе, сохраните книгу. Вы также можете изменить запрос, чтобы изменить его. Дополнительные сведения о редактировании запроса Microsoft Query см. в справке Microsoft Query.
Использование данных из ячейки в качестве значения параметраНа компьютере введите значения, которые вы хотите использовать условия в запросе.
Щелкните ячейку в любом месте диапазон внешних данных, созданной с помощью запроса с параметрами.
На вкладке Данные в группе Подключения нажмите кнопку Свойства.
В диалоговом окне Свойства нажмите кнопку Свойства подключения .
В диалоговом окне Свойства подключения перейдите на вкладку Определение и выберите параметры.
В диалоговом окне Параметры в списке Имя параметра выберите параметр, который вы хотите изменить.
Щелкните Получить значение из следующей ячейки.
На компьютере щелкните ячейку со значением, которое вы хотите использовать.
Если вы хотите обновлять данные при изменении значения в ячейке, выберите автоматически обновлять при изменении значения ячейки.
Чтобы обновить данные, щелкните стрелку рядом с кнопкой Обновить все на вкладке Данные в группе Подключения и выберите обновить.
Использование константы значения параметра для запросаВ Excel щелкните ячейку в диапазон внешних данных, созданной с помощью запрос с параметрами.
На вкладке Данные в группе Подключения нажмите кнопку Свойства.
В диалоговом окне Свойства нажмите кнопку Свойства подключения .
В диалоговом окне Свойства подключения перейдите на вкладку Определение и выберите параметры.
В диалоговом окне Параметры в списке Имя параметра выберите параметр, который вы хотите изменить.
Щелкните Использовать следующее значение.
Введите значение параметра и нажмите кнопку ОК.
Чтобы обновить данные, щелкните стрелку рядом с кнопкой Обновить все на вкладке Данные в группе Подключения и выберите обновить.
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
При запросе данных в Excel можно использовать входное значение ( параметр), чтобы указать что-то о запросе. Для этого нужно создать запрос с параметрами в Microsoft Query.
Параметры используются в предложении WHERE запроса— они всегда работают в качестве фильтра для извлечения данных.
Параметры могут запрашивать у пользователя входное значение при запуске или обновлении запроса, использовать константы в качестве входного значения или использовать содержимое указанной ячейки в качестве входного значения.
Параметр является частью запроса, который он изменяет, и его нельзя повторно использовать в других запросах.
Примечание Если вы хотите создать запросы с параметрами другим способом, см. создание запроса с параметрами (Power Query).
Последовательность действий
Щелкните Данные > Получить & Преобразование данных > Получить данные > из других источников > из Microsoft Query.
Следуйте шагам мастера запросов. На экране Мастер запросов — готово выберите Просмотр данных или изменение запроса в Microsoft Query и нажмите кнопку Готово. Откроется окно Microsoft Query и отобразит запрос.
После where введите имя поля, оператор сравнения (=, <, >, LIKE и т. д.) и одно из следующих данных:
Для запроса generic parameter (?) введите вопросии (?). В подсказке, которая появляется при запуске запроса, не отображается полезная фраза.
Введите фразу, заключенную в квадратные скобки, для запроса параметра, который помогает вводить допустимые данные. Фраза отображается в запросе с параметрами при запуске запроса.
Завершив добавление условий с параметрами в предложение WHERE, нажмите кнопку ОК, чтобы запустить запрос. Excel запрос на в качестве значения для каждого параметра, Microsoft Query отобразит результаты.
Когда вы будете готовы загрузить данные, закройте окно Microsoft Query, чтобы вернуться к Excel. Откроется диалоговое окно Импорт данных.
Чтобы просмотреть параметры, нажмите кнопку Свойства. Затем в диалоговом окне Свойства подключения на вкладке Определение нажмите кнопку Параметры.
В диалоговом окне Параметры отображаются параметры, используемые в запросе. Выберите параметр в области Имя параметра, чтобы просмотреть или изменить параметр How value is obtained. Вы можете изменить запрос параметра, ввести определенное значение или указать ссылку на ячейку.
Теперь в книге есть запрос с параметрами. При запуске запроса или обновлении подключения к данным Excel проверяет параметр, чтобы завершить предложение WHERE запроса. Если параметр запросит значение, Excel отобразит диалоговое окно Введите значение параметра для сбора входных данных. Вы можете ввести значение или щелкнуть ячейку со значением. Вы также можете указать, что указанное значение или ссылка всегда должны использоваться, а при использовании ссылки на ячейку можно указать, что Excel должно автоматически обновлять подключение к данным (то есть повторно выполнить запрос) при внесении изменений в указанную ячейку.
Параметр служит способом простого хранения и управления значением, которое можно использовать повторно.
Параметры обеспечивают гибкость для динамического изменения выходных данных запросов в зависимости от их значения и могут использоваться для:
Вы можете легко управлять параметрами в окне Управление параметрами . Вы можете открыть окно Управление параметрами , выбрав пункт Управление параметрами в меню Управление параметрами на вкладке Главная .
Создание параметра
Power Query предоставляет два простых способа создания параметров:
Из существующего запроса — Можно легко щелкнуть правой кнопкой мыши запрос, значение которого является простой неструктурированной константой, такой как, но не ограничена, датой, текстом или числом, и выбрать команду преобразовать в параметр.
Можно также преобразовать параметр в запрос, щелкнув правой кнопкой мыши параметр и выбрав преобразовать в запрос, как показано на следующем рисунке.
Использование окна — "Управление параметрами" Можно выбрать параметр новый параметр в раскрывающемся меню окна Управление параметрами на вкладке Главная . Кроме того, можно открыть окно Управление параметрами и нажать кнопку создать в верхней части окна, чтобы создать параметр. Вы можете заполнить эту форму и нажать кнопку ОК , чтобы создать новый параметр.
После создания параметра вы всегда можете вернуться в окно Управление параметрами , чтобы изменить параметры в любой момент.
Свойства параметра
Параметр сохраняет значение, которое можно использовать для преобразований в Power Query. Помимо имени параметра и значения, которое он хранит, у него также есть другие свойства, которые предоставляют метаданные. Ниже приведены свойства параметра.
Имя — Укажите имя этого параметра, которое позволит легко распознать его и отличать от других параметров, которые вы можете создать.
Описание — Описание отображается рядом с именем параметра при отображении сведений о параметрах, что помогает пользователям, которые задают значение параметра, понять его назначение и его семантику.
Обязательное требование — Флажок указывает, могут ли следующие пользователи указывать, должно ли быть указано значение параметра.
Тип — Рекомендуется всегда настраивать тип данных параметра. Дополнительные сведения о важности типов данных можно узнать из статьи типы данных .
Предлагаемые значения — Предоставляет пользователю предложение выбрать значение для текущего значения из доступных параметров:
Любое значение — Текущим значением может быть любое значение, указанное вручную.
Список значений — Предоставляет простой интерфейс, похожий на таблицу, позволяющий определить список предлагаемых значений, которые позже можно выбрать для текущего значения. При выборе этого параметра становится доступным новый параметр с именем значение по умолчанию . Здесь можно выбрать значение, которое должно быть значением по умолчанию для этого параметра, которое будет значением по умолчанию, отображаемым пользователю при обращении к параметру. Это значение не совпадает с текущим значением, которое хранится в параметре и может быть передано в качестве аргумента в преобразованиях. Использование списка значений позволит отобразить раскрывающееся меню в полях значения по умолчанию и текущие значения , где можно выбрать одно из значений из предложенного списка значений.
Вы по-прежнему можете вручную ввести любое значение, которое требуется передать в параметр. Список предлагаемых значений служит только в качестве простых предложений.
Запрос — Использует запрос списка (запрос, вывод которого представляет собой список) для предоставления списка предлагаемых значений, которые позже можно выбрать для текущего значения.
Текущее значение — Значение, которое будет храниться в этом параметре.
Где использовать параметры
Параметр может использоваться различными способами, но чаще всего используется в двух сценариях:
- Аргумент Step — Параметр можно использовать в качестве аргумента для нескольких преобразований, управляемых из пользовательского интерфейса.
- Аргумент — пользовательской функции Можно создать новую функцию на основе запроса и ссылочные параметры в качестве аргументов пользовательской функции.
В следующих разделах вы увидите пример для этих двух сценариев.
Аргумент Step
Чтобы включить эту функцию, сначала перейдите на вкладку вид в редакторе Power Query и включите параметр всегда разрешать в группе Параметры .
В настоящее время эта функция недоступна в Power Query в сети.
Например, можно просмотреть следующие запросы заказов с полями OrderID, Units и Margin.
Можно создать новый параметр с минимальным полем имени с типом десятичного числа и текущим значением 0,2, как показано на следующем рисунке.
Можно открыть запрос заказы и в поле поля выбрать параметр больше чем фильтр.
В окне Фильтрация строк вы увидите кнопку с типом данных для выбранного поля. В раскрывающемся меню этой кнопки можно выбрать параметр параметр . В области Выбор поля справа рядом с кнопкой тип данных можно выбрать параметр, который необходимо передать в этот аргумент. В данном случае это минимальный параметр поля .
После нажатия кнопки ОК можно увидеть, что таблица отфильтрована с использованием текущего значения параметра.
Если изменить Текущее значение параметра минимального поля на 0,3, можно сразу же увидеть, как обновляется запрос заказов и отображаются только те строки, в которых маржа превышает 30%.
Множественные преобразования в Power Query предлагают эту возможность выбора параметра из раскрывающегося списка. Поэтому рекомендуется всегда искать и использовать преимущества параметров, которые могут предложить вам.
Аргумент пользовательской функции
С помощью Power Query можно создать пользовательскую функцию из существующего запроса с помощью простого щелчка. После предыдущего примера можно щелкнуть правой кнопкой мыши запрос заказы и выбрать команду создать функцию, которая запустит новое окно Создание функции . В этом окне можно присвоить имя новой функции, и она сообщит вам о параметрах, на которые указывают ссылки в запросе. Эти параметры будут использоваться в качестве параметров для пользовательской функции.
Вы можете присвоить имя этой новой функции. В демонстрационных целях имя этой новой функции будет иметь значение MyFunction. После нажатия кнопки ОК в области запросы будет создана новая группа с именем новой функции. В этой группе вы найдете параметры, используемые для функции, запрос, который использовался для создания функции, и саму функцию.
Вы можете проверить эту новую функцию, введя значение, например 0,4, в поле под меткой минимального поля . Затем нажмите кнопку Invoke (вызвать ). При этом будет создан новый запрос с именем вызванная функция, которая фактически передает значение 0,4 для использования в качестве аргумента функции и предоставляет только те строки, в которых поле превышает 40%.
Дополнительные сведения о создании пользовательских функций см. в статье Создание пользовательской функции.
Возможно, вы хорошо знакомы с запросами с параметрами, которые используются в SQL или Microsoft Query. Однако параметры Power Query имеют основные различия.
Параметры можно использовать на любом шаге запроса. Помимо использования в качестве фильтра данных, параметры можно использовать для указания таких параметров, как путь к файлу или имя сервера.
Параметры не будут подсказок на ввод данных. Вместо этого вы можете быстро изменить их значение с помощью Power Query. Вы даже можете хранить и извлекать значения из ячеек в Excel.
Параметры сохраняются в простом запросе с параметрами, но не в запросах данных, в которые они используются. После создания вы можете добавить параметр в запросы.
Примечание Если вы хотите создать запросы с параметрами другим способом, см. создание запроса с параметрами в Microsoft Query.
С помощью параметра можно автоматически изменять значение в запросе и не изменять его каждый раз. Просто измените значение параметра. После создания параметра он будет сохранен в специальном запросе, который можно легко изменить непосредственно из Excel.
Выберите Данные >получить данные >другие источники >запуск редактора Power Query.
В редакторе Power Query выберите Главная > Параметры > Параметры.
В диалоговом окне Управление параметрами выберите Новый.
При необходимости за установите следующее:
Это должно отразить функцию параметра, но оставить его как можно более кратким.
Описание
Они могут содержать любые сведения, которые помогут правильно использовать параметр.
Обязательные
Сделайте одно из следующих значений: Любое значение. В запросе с параметрами можно ввести любое значение любого
Список значений Вы можете ограничить значения определенным списком, введите их в небольшую сетку. Необходимо также выбрать значения по умолчанию иТекущее значение ниже.
Запрос Выберите запрос списка, который напоминает структурированный столбец списка, разделенный запятой и заключенный в скобки.
Например, поле "Состояние вопросов" может иметь три значения: . Запрос списка необходимо заранее создать, открыв расширенный редактор (выберите Главная> Расширенный редактор),удалив шаблон кода, введите список значений в формате списка запросов и выберите Готово .
После создания параметра запрос списка отобразится в его значениях.
Этот параметр определяет тип данных параметра.
Предлагаемые значения
При желании добавьте список значений или укажите запрос, чтобы предоставить предложения по входным данным.
Значение по умолчанию
Это отображается только в том случае, если для значения "Предлагаемые значения" установлено значение "Список значений" иуказано, какой элемент списка является элементом по умолчанию. В этом случае необходимо выбрать значение по умолчанию.
Текущее значение
В зависимости от того, где используется параметр, если он пустой, запрос может не возвращать никаких результатов. Если выбрано значение Обязательно, текущее значение не может быть пустым.
Чтобы создать параметр, выберите ОК.
Вот как можно управлять изменениями в расположениях источников данных и предотвратить ошибки обновления. Например, предположим, что схема и источник данных похожи, создайте параметр, чтобы легко изменить источник данных и предотвратить ошибки обновления данных. Иногда изменяется сервер, база данных, папка, имя файла или расположение. Возможно, руководитель базы данных время от времени меняет сервер, ежемесячный выпуск CSV-файлов переходит в другую папку или вам нужно легко переключаться между средами разработки, тестирования и работы.
Шаг 1. Создание запроса с параметрами
В следующем примере вы импортируете несколько CSV-файлов с помощью операции импорта папки (выберите Данные > Получить данные >Из файлов > из папки )из папки C:\DataFilesCSV1. Но иногда в качестве расположения для передачи файлов иногда используется другая папка C:\DataFilesCSV2. Вы можете использовать параметр в запросе в качестве замены значения для другой папки.
Выберите Главная> Управление параметрами >Новый параметр.
В диалоговом окне Управление параметрами введите следующие сведения:
Читайте также: