Выбор параметров в Excel

Обновлено: 21.11.2024

Когда вы создаете запрос Power в Excel для импорта или преобразования данных, Excel создает запрос за кулисами на языке M. Вы можете увидеть этот код M, перейдя в Расширенный редактор из редактора запросов Power. .

При создании кода его элементы будут жестко закодированы. Например, если вы импортируете данные из внешнего источника, путь к папке и имя файла будут жестко заданными (статическими или неизменными) строками текста в M-коде.

Если вы хотите обновить путь к папке или имя файла для импорта, вам нужно перейти в расширенный редактор и обновить путь и имя файла.

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

Видеоруководство

Настройка таблицы параметров

Давайте настроим нашу таблицу параметров. Для этого запроса я хочу импортировать CSV из папки, а затем отфильтровать по заданному продукту и диапазону дат. Я хочу иметь входные параметры, чтобы легко обновлять путь к папке, имя файла, продукт и диапазон дат, на которых будет основываться мой запрос.

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

Нам нужно преобразовать данные параметра в таблицу Excel, перейдя на вкладку «Вставка» и выбрав «Таблица» или используя сочетание клавиш Ctrl + T. Назовите таблицу Параметры, именно так мы будем ссылаться на таблицу в нашем запросе мощности. Чтобы назвать таблицу, выберите ее, перейдите на вкладку «Дизайн» и введите новое имя в поле «Имя таблицы:».

Создайте функцию запроса для ссылки на таблицу параметров

Создайте пустой запрос. Перейдите на вкладку «Данные» на ленте и выберите «Получить данные» в разделе «Получить и преобразовать данные». Выберите «Из других источников», затем выберите «Пустой запрос» в меню.

Назовите запрос fParameters. Именно так вы будете называть значения в таблице параметров.

Откройте расширенный редактор на вкладке "Главная" или на вкладке "Вид" в редакторе запросов. Скопируйте и вставьте следующий код, затем нажмите кнопку "Готово".

Эта функция запроса имеет два входных параметра TableName и RowNumber.

TableName — этот ввод относится к имени таблицы. В нашем случае это будут «Параметры».
RowNumber — этот ввод относится к номеру строки, в которой находится интересующий нас параметр в нашей таблице.

Обратите внимание, что power query будет считать строки с 0, поэтому мы будем использовать RowNumber-1, чтобы ссылаться на наши строки в естественном порядке подсчета от 1 до N.

Теперь сохраните функцию запроса.

Перейдите на вкладку "Главная" в редакторе запросов.
Выберите "Закрыть и загрузить".
Выберите в меню Закрыть и загрузить в.
Выберите «Только создать соединение» в окне «Импорт данных».
Нажмите кнопку "ОК".

Создание исходного запроса импорта и фильтрации

Перейдите на вкладку "Данные" на ленте и выберите "Из текста/CSV" в разделе "Получить и преобразовать данные". Выберите CSV-файл и нажмите кнопку «Изменить» в окне предварительного просмотра результатов запроса.

Теперь добавим фильтр.

Нажмите значок "Фильтр" справа от столбца "Проданный продукт".
Выберите «Текстовый фильтр» в меню.
Выберите в меню "Равно".

Установите фильтр Карандаши и нажмите кнопку ОК.

Теперь мы можем сделать то же самое, чтобы отфильтровать дату заказа между 2017-02-01 и 2017-03-31.

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

Мы можем закрыть и загрузить этот запрос в таблицу на новом листе и просмотреть наши данные.

Замените жестко закодированные элементы нашей функцией запроса

Теперь мы можем заменить любой экземпляр жестко заданной ссылки, которую хотим превратить в параметр в нашем запросе. Замените текст, включая круглые скобки вокруг них, на fParameters("Parameters",N), где N — это индекс в нашей таблице параметров, на который мы хотим ссылаться.

Для дат мне пришлось использовать функцию расширенного запроса Date.Year , Date.Month и Date.Day, чтобы преобразовать даты моих параметров в числа для года, месяца и дня.

Теперь мы можем легко изменить любой из параметров и обновить запрос! С помощью этого метода параметризации мы можем создавать гораздо более гибкие запросы.

Об авторе

Джон МакДугалл

Джон — Microsoft MVP, внештатный консультант и тренер, специализирующийся на Excel, Power BI, Power Automate, Power Apps и SharePoint. Другие интересные статьи Джона можно найти в его блоге или на канале YouTube.

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

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

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

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

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

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

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

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

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

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

Имя

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

Описание

Это может содержать любую информацию, которая поможет людям правильно использовать параметр.

Обязательно

Выполните одно из следующих действий:

Любое значение. В параметре запроса можно ввести любое значение любого типа данных.

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

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

Например, поле статуса "Проблемы" может иметь три значения: . Вы должны создать запрос списка заранее, открыв расширенный редактор (выберите «Главная» > «Расширенный редактор»), удалив шаблон кода, введя список значений в формате списка запроса и выбрав «Готово».

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

Введите

Указывает тип данных параметра.

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

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

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

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

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

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

Чтобы создать параметр, выберите ОК.

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

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

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

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

Введите следующую информацию в диалоговом окне "Управление параметрами":

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

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

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

Параметр является частью изменяемого им запроса и не может использоваться повторно в других запросах.

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

Нажмите Данные > Получить и преобразовать данные > Получить данные > Из других источников > Из Microsoft Query.

Следуйте шагам мастера запросов. На экране «Мастер запросов — Готово» выберите «Просмотреть данные или изменить запрос в Microsoft Query», а затем нажмите «Готово». Откроется окно Microsoft Query, в котором отобразится ваш запрос.

Нажмите Вид > SQL. В появившемся диалоговом окне SQL найдите предложение WHERE — строку, начинающуюся со слова WHERE, обычно в конце кода SQL. Если предложение WHERE отсутствует, добавьте его, введя WHERE в новой строке в конце запроса.

После WHERE введите имя поля, оператор сравнения (=, , LIKE и т. д.) и один из следующих элементов:

Для запроса общего параметра введите вопросительный знак (?). В подсказке, которая появляется при выполнении запроса, не отображается полезная фраза.

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

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

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

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

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

Нажмите "ОК", чтобы сохранить изменения и закрыть диалоговое окно "Параметры", затем в диалоговом окне "Импорт данных" нажмите "ОК", чтобы отобразить результаты запроса в Excel.

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

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

Термин "параметр" может немного сбивать с толку. Мы используем термин переменная при написании VBA или, возможно, термины условия критерии или аргументы при написании формул Excel, все они фактически одинаковы. предмет. Параметры, переменные, условия, критерии и аргументы — это все значения, которые мы можем изменить, чтобы получить другой результат. Microsoft решила использовать термин «параметр» при разработке Power Query.

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

По мере развития Power Query появляются новые и более простые в использовании функции. Параметры — одна из таких развивающихся функций. Метод, который я хочу вам показать, я считаю, что в настоящее время он самый простой и лучший в реализации.

Скачать файл примера

Я рекомендую вам загрузить файл примера для этого поста. Затем вы сможете работать с примерами и увидеть решение в действии, а файл будет полезен для дальнейшего использования.

Загрузите файл: Power Query — примеры файлов

Во всех примерах в этом посте используется пример 8 — Использование Parameters.xlsx из загрузки.

Книга Excel содержит только исходные данные. Мы проработаем все этапы от начала до конца. Откройте файл и приступим.

Создайте запрос

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

Выберите любую ячейку в исходной таблице и нажмите Данные -> Из таблицы/диапазона на ленте.

Откроется редактор Power Query. Сделайте следующие преобразования

Столбец даты

Нажмите значок «Дата и время» рядом с заголовком «Дата», выберите «Дата» в меню.

Выберите заголовок столбца «Дата», затем нажмите «Преобразовать» -> «Дата» -> «Месяц» -> «Конец месяца».

Отфильтруйте столбец «Дата», чтобы включить только 31 января 2019 года. Формат даты может отличаться в зависимости от настроек вашего местоположения.

Убедитесь, что столбец «Дата» по-прежнему выбран, затем нажмите «Главная» -> «Удалить столбцы».

Столбец "Продано"

В столбце "Продавец" щелкните значок фильтра и убедитесь, что выбран только Дэвид.

Выделив столбец "Кем продано", нажмите "Главная" -> "Удалить столбцы".

На данный момент достаточно преобразований. Нажмите Главная -> Закрыть и загрузить.

Таблица должна выглядеть следующим образом:

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

Создайте параметры

Проще говоря, параметр — это обычный запрос, в котором мы углубляемся в само значение и загружаем его как соединение.

В этом примере мы будем использовать таблицу Excel в качестве источника, но это также может быть именованный диапазон, CSV или любой другой источник данных, который мы можем получить в Power Query.

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

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

Первую таблицу я назвал Date, а вторую SoldBy.

Создание параметра Text

Сначала мы создадим параметр для изменения имени.

Выберите ячейку в таблице SoldBy и создайте запрос, нажав Данные -> Из таблицы/диапазона.

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

Итак, нажмите на значок рядом с заголовком Sold By, выберите в меню Text.

Затем в Power Query щелкните значение правой кнопкой мыши и выберите в меню "Детализация".

Окно изменится на вид, которого мы раньше не видели, окно инструментов для работы с текстом:

Запишите имя запроса: SoldBy (без пробела), как показано на снимке экрана выше.

Нажмите «Файл» -> «Закрыть и загрузить в…»

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

В меню "Запросы и подключение" теперь отображаются два запроса: исходные данные, называемые "Данные о продажах", и текстовый параметр "Продано".

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

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

Итак, нажмите на значок "Дата и время" рядом с заголовком "Дата", выберите "Дата" в меню

Далее щелкните значение правой кнопкой мыши и выберите Детализация. Вместо текстовых инструментов это будет представление инструментов DateTime.

Запишите имя запроса, в данном случае это Дата.

Как и раньше, нажмите «Закрыть и загрузить в…», затем выберите «Только создать подключение» и нажмите «ОК».

Теперь у нас должно быть создано два параметра: SoldBy в качестве текстового типа и Date в качестве типа даты.

Вставить параметры в запрос

Создав параметры, давайте их использовать. Для этого мы внесем некоторые базовые изменения в М-код. Мы могли бы использовать расширенный редактор или панель формул. Для простоты в этом примере я буду использовать панель формул.

Важная информация: M-код чувствителен к регистру (SoldBy и soldby — это не одно и то же).

Откройте исходный запрос (в нашем примере запрос SalesData).

Если панель формул не отображается, нажмите Вид -> Панель формул.

Найдите шаг, на котором мы жестко закодировали значение Дэвид.

Замените «Дэвид» в параметре SoldBy.

Далее мы применим параметр Date. Найдите шаг, в котором мы жестко закодировали 31 января 2019 г. в качестве даты.

Это все, теперь мы применили параметры. Нажмите Главная -> Закрыть и загрузить, чтобы загрузить изменения в Excel.

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

Теперь, когда мы вернулись в Excel, мы можем изменить значения ячеек «Дата» и «Кем продано», а затем нажать «Данные» -> «Обновить все».

Вау! Магия, а? Запрос обновится и покажет только значения выбранных нами параметров.

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

Содержание серии Power Query

Получите БЕСПЛАТНУЮ электронную книгу VBA с 30 наиболее полезными макросами Excel VBA.

Автоматизируйте Excel, чтобы сэкономить время и перестать выполнять работу, которую могла бы выполнять обученная обезьяна.

Не забывайте:

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

Вам нужна помощь в адаптации этого к вашим потребностям?

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

Читайте другие блоги или смотрите видео на YouTube по той же теме. Вы получите гораздо больше пользы, найдя собственные решения.
Спросите «Excel Ninja» в вашем офисе. Удивительно, какие вещи знают другие люди.
Задайте вопрос на форуме, таком как Mr Excel, или в сообществе Microsoft Answers. Помните, что люди на этих форумах обычно отдают свое время бесплатно. Поэтому постарайтесь сформулировать свой вопрос, убедитесь, что он четкий и лаконичный. Перечислите все, что вы пробовали, и предоставьте скриншоты, сегменты кода и примеры книг.
Используйте Excel Rescue, моего партнера-консультанта. Они помогают решить небольшие проблемы с Excel.

Что дальше?
Пока не уходите, в Excel Off The Grid есть чему поучиться. Ознакомьтесь с последними сообщениями:

Читайте также: