Applies ToExcel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета

Возможно, вы хорошо знакомы с запросами параметров, которые используются в SQL или Microsoft Query. Однако Power Query параметры имеют ключевые отличия:

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

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

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

Примечание    Если вам нужен другой способ создания запросов параметров, см. статью Создание запроса параметров в Microsoft Query.

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

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

  2. В Редактор Power Query выберите Главная > Управление параметрами > Новые параметры.

  3. В диалоговом окне Управление параметром выберите Создать.

  4. При необходимости задайте следующие параметры:

    Имя    

    Это должно отражать функцию параметра, но держать ее как можно короче.

    Описание    

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

    Обязательные    

    Выполните одно из следующих действий:Любое значение. В запросе к параметру можно ввести любое значение любого типа данных.Список значений    Значения можно ограничить определенным списком, введя их в небольшую сетку. Также необходимо выбрать значения по умолчанию и Текущее значение ниже.Запроса Выберите запрос списка, который похож на структурированный столбец списка , разделенный запятыми и заключенный в фигурные скобки.Например, поле состояния проблем может содержать три значения: {"Новый", "Текущий", "Закрытый"}. Необходимо создать запрос списка заранее, открыв Расширенный редактор (выберите Главная > Расширенный редактор), удалив шаблон кода, введя список значений в формате списка запросов и выбрав Готово.После создания параметра запрос списка отобразится в значениях параметров.

    Тип    

    Это указывает тип данных параметра .

    Предлагаемые значения    

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

    Значение по умолчанию

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

    Текущее значение    

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

  5. Чтобы создать параметр, нажмите кнопку ОК.

Вот способ управления изменениями в расположениях источников данных и предотвращения ошибок обновления. Например, предполагая аналогичную схему и источник данных, создайте параметр, чтобы легко изменять источник данных и предотвращать ошибки обновления данных. Иногда изменяется сервер, база данных, папка, имя файла или расположение. Возможно, диспетчер баз данных иногда переключает сервер, ежемесячное удаление CSV-файлов попадает в другую папку или вам нужно легко переключаться между средой разработки, тестирования и рабочей среды.

Шаг 1. Создание запроса параметров

В следующем примере у вас есть несколько CSV-файлов, импортируемых с помощью операции импорта папки (выбор данных > Получение данных > из файлов > из папки) из папки C:\DataFilesCSV1. Но иногда в качестве расположения для удаления файлов иногда используется другая папка C:\DataFilesCSV2. Параметр в запросе можно использовать в качестве замены значения для другой папки.

  1. Выберите Главная > Управление параметрами > Новый параметр.

  2. В диалоговом окне Управление параметром введите следующие сведения:

    Имя

    CSVFileDrop

    Описание

    Альтернативное расположение удаления файла

    Обязательные

    Да

    Тип

    Текст

    Предлагаемые значения

    Любое значение

    Текущее значение

    C:\DataFilesCSV1

  3. Нажмите кнопку ОК.

Шаг 2. Добавление параметра в запрос данных

  1. Чтобы задать имя папки в качестве параметра, в разделе Параметры запроса в разделе Шаги запроса выберите Источник, а затем — Изменить параметры.

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

  3. Нажмите кнопку ОК.

Шаг 3. Обновление значения параметра

Расположение папки только что изменилось, поэтому теперь можно просто обновить запрос параметров.

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

  2. Введите новое расположение в поле Текущее значение , например C:\DataFilesCSV2.

  3. Выберите Главная > Закрыть & Загрузить.

  4. Чтобы подтвердить результаты, добавьте новые данные в источник данных, а затем обновите запрос данных с обновленным параметром (Выберите данные > Обновить все).

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

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

  2. Щелкните стрелку фильтра в заголовке любого столбца, чтобы отфильтровать данные, а затем выберите команду фильтра, например Фильтры даты и времени > After. Откроется диалоговое окно Фильтрация строк .Ввод параметра в диалоговом окне "Фильтр"

  3. Нажмите кнопку слева от поля Значение и выполните одно из следующих действий:

    • Чтобы использовать существующий параметр, выберите Параметр, а затем выберите нужный параметр в списке справа.

    • Чтобы использовать новый параметр, выберите Создать параметр и создайте параметр.

  4. Введите новую дату в поле Текущее значение , а затем выберите Главная > Закрыть & загрузить.

  5. Чтобы подтвердить результаты, добавьте новые данные в источник данных, а затем обновите запрос данных с обновленным параметром (Выберите данные > Обновить все). Например, измените значение фильтра на другую дату, чтобы увидеть новые результаты.

  6. Введите новую дату в поле Текущее значение .

  7. Выберите Главная > Закрыть & Загрузить.

  8. Чтобы подтвердить результаты, добавьте новые данные в источник данных, а затем обновите запрос данных с обновленным параметром (Выберите данные > Обновить все).

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

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

    MyFilter

    G

  2. Выберите ячейку в таблице Excel, а затем выберите Данные > Получить данные > из таблицы или диапазона. Появится Редактор Power Query.

  3. В поле Имя области Параметры запроса справа измените имя запроса на более понятное, например FilterCellValue. 

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

    Обратите внимание, что формула изменена на = #"Changed Type"{0}[MyFilter]

    При использовании таблицы Excel в качестве фильтра на шаге 10 Power Query ссылается на значение Таблицы в качестве условия фильтра. Прямая ссылка на таблицу Excel приведет к ошибке.

  5. Выберите Главная > Закрыть & Загрузить > Закрыть & Загрузить. Теперь у вас есть параметр запроса с именем FilterCellValue, который вы используете на шаге 12.

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

  7. Откройте запрос, который требуется отфильтровать с помощью значения в таблице FilterCellValue, которая была загружена ранее из Редактор Power Query, выбрав ячейку в данных, а затем выберите Запрос > Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.

  8. Щелкните стрелку фильтра в заголовке любого столбца, чтобы отфильтровать данные, а затем выберите команду фильтра, например Текстовые фильтры > начинается с. Откроется диалоговое окно Фильтрация строк

  9. Введите любое значение в поле Значение , например "G", а затем нажмите кнопку ОК. В этом случае значение является временным заполнителем значения в таблице FilterCellValue, которое вы вводите на следующем шаге.

  10. Щелкните стрелку в правой части строки формул, чтобы отобразить всю формулу. Ниже приведен пример условия фильтра в формуле: = Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))

  11. Выберите значение фильтра. В формуле выберите "G".

  12. С помощью M Intellisense введите первые несколько букв созданной таблицы FilterCellValue, а затем выберите ее в появившемся списке.

  13. Выберите Главная > Закрыть > Закрыть & загрузить.

Результат

Теперь запрос использует значение в созданной таблице Excel для фильтрации результатов запроса. Чтобы использовать новое значение, измените содержимое ячейки в исходной таблице Excel на шаге 1, измените значение "G" на "V", а затем обновите запрос.

Вы можете указать, разрешены ли запросы параметров.

  1. В Редактор Power Query выберите Параметры> файлов и Параметры > Параметры запроса > Редактор Power Query.

  2. В области слева в разделе Глобальный выберите Редактор Power Query.

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

См. также

Справка по Power Query для Excel

Использование параметров запроса (docs.com)

Нужна дополнительная помощь?

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

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