Створення параметризованого запиту

Під час запиту даних у програмі Excel може знадобитися використовувати вхідне значення – параметр, щоб указати щось про запит. Для цього потрібно створити Параметризований запит. Спосіб створення запитів для параметрів і їх поведінка залежить від того, чи використовується Microsoft Query або Power Query.

Порада.: Параметри Power Query дуже відрізняються від параметрів, які використовуються в запитах на основі SQL. Крім того, ви можете використовувати запит замість фактичного параметра, якщо все, що вам потрібно, – фільтрувати дані. Перш ніж створювати параметри в надбудові Power Query, спробуйте прочитати розділи надбудови Power Query.

Microsoft Query

Power Query

Визначення параметрів, які впливають на запити

Параметри використовуються в реченні WHERE – вони завжди функціонують як фільтр для отриманих даних.

Параметри можна використовувати в будь-якому кроці запиту. Крім роботи з фільтром даних, параметри можна використовувати, щоб указати такі речі, як шлях до файлу або ім'я сервера.

Параметри вхідного параметра

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

Параметри не мають запиту на ввід. Натомість можна змінити своє значення за допомогою редактора Power Query. Або замість сумлінного параметра можна використовувати запит, який посилається на зовнішнє розташування з значенням, яке можна легко редагувати.

Область параметрів

Параметр – це частина запиту, яку він змінює, і його не можна використовувати в інших запитах.

Параметри відокремлені від запитів – після створення ви можете додати параметр до запитів за потреби.

  1. Виберіть елемент > дані& перетворення даних > отримання даних > з інших джерел > з Microsoft Query.

  2. Дотримуйтесь інструкцій майстра запитів. На екрані майстер запитів – завершення виберіть пункт переглянути дані або змінити запит у Microsoft Query , а потім натисніть кнопку завершити. Відкриється вікно "запит" у вікні Microsoft Query та відобразиться запит.

  3. Натисніть кнопку переглянути> SQL. У діалоговому вікні SQL, що Відкриється, знайти речення WHERE – рядок, що починається з слова, де зазвичай в кінці коду SQL. Якщо речення немає, додайте його , ввівши на новий рядок у кінці запиту.

  4. Після цього введіть ім'я поля, оператор порівняння (=, <, >, LIKE і т. д.), а також одну з наведених нижче дій.

    • Для загального запиту на введення введіть позначку запитання (?). У запиті, що з'являється під час запуску запиту, відображається фраза "немає корисної фрази".

      Режим SQL для запиту MS Query, який підкреслює речення WHERE

    • Для запиту параметра, який допомагає користувачам вводити припустимий ввід, введіть фразу, укладену квадратні дужки. Фраза відображається в запиті параметра під час запуску запиту.

      Режим SQL для запиту MS Query, який підкреслює речення WHERE

  5. Завершивши додавання умов із параметрами до речення WHERE, натисніть кнопку OK , щоб виконати запит. У програмі Excel буде запропоновано вказати значення для кожного параметра, а потім у Microsoft Query відображаються результати.

  6. Коли все буде готово до завантаження даних, Закрийте вікно Microsoft Query, щоб повернути результати до програми Excel. Відкриється діалогове вікно імпорт даних.

    Діалогове вікно "Імпорт даних" у програмі Excel

  7. Щоб переглянути параметри, натисніть кнопку Властивості. Потім у діалоговому вікні Властивості підключення на вкладці визначення натисніть кнопку Параметри.

    Діалогове вікно "Властивості підключення"

  8. У діалоговому вікні Параметри відображаються параметри, які використовуються в запиті. Виберіть параметр у розділі ім'я параметра , щоб переглянути або змінити спосіб отримання значення параметра. Можна змінити підказку параметра, ввести певне значення або вказати посилання на клітинку.

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

  9. Натисніть кнопку OK , щоб зберегти зміни, і закрийте діалогове вікно Параметри, а потім у діалоговому вікні Імпорт даних натисніть кнопку OK , щоб відобразити результати запиту в програмі Excel.

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

Примітка.: У цій статті припускається, що ви знаєте, як створити підключення до бази даних Access за допомогою надбудови Power Query. Додаткові відомості наведено в статті підключення до бази даних Access.

Ви можете використовувати параметри в кількох сценаріях Power Query, ніж просто фільтрувати дані – будь-який крок запиту Power Query може мати параметри. Наприклад, можна використати параметр, щоб указати частини рядка підключення на вихідному кроці, як-от ім'я файлу.

Параметри надбудови Power Query мають імена. Щоб використовувати параметр, ви посилаєтеся на нього за іменем у формулі для кроку. Наприклад, припустімо, що потрібно переглянути дані про веб-сторінки, які ви підтримуєте, і потрібно відфільтрувати дані за датою публікації. Хоча ви завжди можете просто використовувати вбудовані фільтри в попередньому перегляді запиту, використовуючи параметр, щоб надати дату для фільтрування, заощадите час і забезпечить більшу гнучкість. Розглянемо цей приклад.

У пустій книзі створюється підключення до бази даних Access, яка має потрібні записи веб-трафіку, включно з полями, які вказують на те, що спочатку було опубліковано кожну сторінку. Завантажується в надбудові Power Query, вона виглядає наступним чином:

Редактор надбудови Power Query, що відображає завантажені дані

Оскільки ми хочемо фільтрувати за датою, ми змінюємо тип даних стовпця, який ми використовуємо, FirstPublishDate. Це дані про дату й час у джерелі, але ми не дбаємо про те, який час доби публікацію стався, і потрібно вказати, що це може бути втомлює – тому ми змінюємо її до типу даних Date.

Відображення результатів у редакторі надбудови Power Query

Далі ми створимо параметр для обмеження результатів на дату, коли сторінка була спочатку опублікована. Щоб відкрити діалогове вікно Параметри, натисніть кнопку основне> Параметри > Керування параметрами .

Діалогове вікно параметрів надбудови Power Query

Натисніть кнопку створити, а форма відображає новий параметр з іменем Parameter1 без іншої інформації.

Ми змінюємо деякі властивості параметра:

  • Змінення імені в firstpubd

  • Змінення описудати, коли сторінка вперше опублікована.

  • Змінити тип на дату , щоб параметр прийняв лише значення дати

  • Установлення поточного значення таким чином, щоб параметр не відфільтровував всі рядки, коли ми не надали введені дані, ми використовуємо 1/1/2010.

Порада.: Ім'я та опис мають достатньо контексту для того, щоб користувачі могли знати, як і чому використовувати цей параметр. Навіть якщо ви єдиний користувач, який використовуватиме цей параметр, може знадобитися нагадування час від часу.

Натисніть кнопку OK , щоб створити параметр і побачити його в редакторі Power Query.

Редактор надбудови Power Query для відображення параметра

Тепер наш параметр відображається на панелі "запити" – ми можемо вибрати його, щоб відобразити його на головній панелі, або ми можемо клацнути її правою кнопкою миші, щоб отримати додаткові параметри. Коли вибрано параметр, можна відредагувати поточне значення на головній панелі або клацнути керувати параметром , щоб змінити інші параметри.

Тепер ми можемо використовувати цей параметр у вихідному запиті. Ми клацайте початковий запит на панелі "запити", щоб відобразити його. Ми хочемо використовувати наш параметр, щоб відфільтрувати результати на основі дати першої публікації, а потім вибравши стовпець FirstPublishDate , клацніть стрілку фільтра та сортування в правому краї заголовка стовпця, наведіть вказівник на пункт фільтри дат, а потім натисніть кнопку після....

Редактор надбудови Power Query, у якому відображається меню фільтра дати

У діалоговому вікні фільтрування рядків виберіть параметр зі списку варіантів у фільтрі.

Діалогове вікно "Фільтр рядків"

Укажіть або виберіть значення, яке замінюється на список доступних параметрів. Є тільки один, який ми щойно створили, FirstPubD.

Діалогове вікно "фільтрування рядків" із вибраним параметром

Виберіть його та натисніть кнопку OK. Редактор надбудови Power Query завантажує запит за допомогою нового параметра як фільтра.

Редактор надбудови Power Query, що відображає відфільтровані результати

Щоб перевірити параметр, ми змінюємо його значення на 1/1/2018.

Редактор надбудови Power Query для відображення параметра

Ми оновлюємо запит, який тепер відображає лише рядки з FirstPublishDate після 1/1/2018.

Редактор надбудови Power Query, що відображає відфільтровані результати

Тепер у нас є запит, який фільтрує за датою за допомогою параметра. Щоб відфільтрувати результати в FirstPublishDate, ми більше не повинні шукати поле, клацніть стрілку фільтра та сортування, а потім виберіть пункт після... тип фільтра та введіть значення дати – ми можемо просто змінити значення FirstPubD і оновити наш запит. Крім того, ми можемо повторно використовувати новий параметр, наприклад, якщо ми вирішили вивести інший набір полів з вихідного джерела даних до нового аркуша, але все одно потрібно додати FirstPubDate і використати його для фільтрування результатів.

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

На аркуші, де наш запит завантажується, ми вставляємо кілька рядків над імпортованими даними. Після цього ми створимо таблицю Excel з одним рядком, щоб утримувати значення параметра.

Книга Excel, яка відображає таблицю параметрів і дані, завантажені з надбудови Power Query

Щоб використати нову таблицю для фільтрування запитів, потрібно підключитися до неї в надбудові Power Query. Ви створюєте підключення до таблиці, вибравши його, а потім клацнувши з таблиці або діапазону на вкладці дані . Відкриється нове підключення та відобразиться нова таблиця в редакторі Power Query.

Дані таблиці Excel, завантажені в редакторі Power Query

Оскільки дані, завантажені як тип даних "Дата й час", потрібно змінити його на тип даних "Дата", щоб відповідає нашому параметру, щоб ми на вкладці основне > перетворити > тип даних > дата.

Наведіть вказівник миші на команду типу даних у групі трансформування на вкладці Основне на стрічці редактора Power Query.

Ми також перейменуємо наш запит на щось значуще, ніж таблиця2. Щоб чітко переконатися в тому, що це за, ми назвемо його FirstPubDate.

Редактор надбудови Power Query з виділеним полем Name

Оскільки ми хочемо передати значення, а не саму таблицю, нам потрібно деталізувати значення дати. Щоб зробити це, клацніть правою кнопкою миші значення в області попереднього перегляду, а потім виберіть пункт деталізація.

Контекстне меню редактора Power Query для значення поля

Тепер у попередньому перегляді відобразиться значення замість таблиці.

Редактор надбудови Power Query, що відображає єдине значення дати

Не потрібно, щоб дані нового запиту завантажувалися в будь-якому місці – дані вже містяться на аркуші, де ми її хочемо. Ми просто потребуємо підключення, щоб надбудова Power Query могла отримати значення параметра. Отже, ми клацайте файл> закрити & завантажити... , щоб відкрити діалогове вікно "Імпорт даних", а потім вибрати лише команду " створити підключення".

Діалогове вікно "Імпорт даних" із вибраним параметром "створити підключення"

Тепер у нас є запит "FirstPubDate", який тягне за собою єдине значення дати з таблиці на аркуші над тим, де основним запитом завантажується основний запит. Тепер ми просто повинні використовувати цей запит як параметр для фільтрування основного запиту. Таким чином, ми відкриваємо основний запит і редагуємо крок, який фільтрує рядки, використовуючи стовпець FirstPublishDate. Ми розширюємо рядок формул і виберіть параметр, який ми створили раніше (FirstPubD). Після цього введіть "a" після Firstpubd – оскільки ім'я нового запиту почнеться з тих самих букв, що й параметр, надбудова Power Query відображає його як параметр для вибору.

Розширена панель формул редактора Power Query

Виберіть його, а потім клацніть за межі рядка формул, щоб використати крок.

Редактор Power Query з даними, завантаженими

Все виглядає правильно, тому ми виходимо з редактора Power Query та зберемо наші зміни. Щоб перевірити параметр, на аркуші звіту ми змінюємо значення клітинок у таблиці вгорі до 5/4/2019, а потім оновіть підключення, щоб переглянути відфільтровані дані.

Відфільтровані дані в програмі Excel

Наш новий фільтр працює! Тому ми зберемо та закриваємо книгу. Тепер будь-який користувач, який використовує книгу, може вказати дату першої публікації, яка використовується як фільтр запиту – на тому ж аркуші, де запит завантажується.

  1. Натисніть кнопку дані > отримати & перетворення даних > отримати дані > Запуск редактора Power Query.

  2. У редакторі Power Query клацніть елемент основне > Параметри > Керування параметрами.

  3. У діалоговому вікні Параметри натисніть кнопку створити.

  4. Задайте потрібні дії:

    • Name (ім'я ), щоб відобразити функцію параметра, але якомога коротроль.

    • Опис -це може містити будь-які відомості, які допоможуть користувачам належним чином використовувати параметр.

    • Обов'язковий параметр – виберіть, щоб цей параметр мав значення.

    • Type (тип ) визначає тип даних, який потрібно вказати.

    • Рекомендовані значення – Якщо потрібно, додайте список значень або вкажіть запит на надання пропозицій для введеного запиту.

    • Значення за замовчуванням – відображається лише в тому випадку, якщо для параметра "Пропоновані значення" установлено список значень і вказано, який елемент списку використовується за замовчуванням.

    • Поточне значення – залежно від того, де використовується параметр, якщо цей запит пустий, можливо, результат не повернеться. Якщо вибрано параметр " обов'язковий ", поточне значення не може бути пусте.

  5. Натисніть кнопку OK , щоб створити параметр.

  1. Відкрийте запит у редакторі Power Query.

  2. Клацніть стрілку праворуч від заголовка стовпця, який потрібно використати, щоб відфільтрувати дані, а потім у меню, що Відкриється, виберіть потрібний фільтр.

  3. У діалоговому вікні фільтр рядків натисніть кнопку праворуч від умови фільтра, а потім виконайте одну з наведених нижче дій.

    • Щоб скористатися наявним параметром, натисніть кнопку параметр, а потім виберіть потрібний параметр зі списку, який відображається справа.

    • Щоб використати новий параметр, натисніть кнопку створити параметр..., а потім створіть параметр.

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

  2. Клацніть значення, а потім виберіть Data > дані & перетворення даних > з таблиці або діапазону.

  3. У редакторі Power Query внесіть будь-які зміни до зв'язку між таблицями (наприклад, Змінення типу даних або імені), а потім натисніть кнопку основне > закрити > закрити & завантажити > закрити & завантаження до....

  4. У діалоговому вікні Імпорт даних виберіть пункт лише створити підключення, а потім виберіть Додати до моделі даних, а потім натисніть кнопку OK.

  5. Відкрийте запит, який потрібно відфільтрувати в редакторі Power Query.

  6. Клацніть стрілку праворуч від заголовка стовпця, який потрібно використати, щоб відфільтрувати дані, а потім у меню, що Відкриється, виберіть потрібний фільтр.

  7. Виконайте одну з таких дій:

    • Виберіть значення з розкривного списку значень (ці дані надходять від даних про запит).

    • Виберіть значення, використовуючи кнопку на правому краю умови фільтра.

  8. Клацніть стрілку в правому краї рядка формул, щоб відобразити весь запит.

  9. Умова фільтра має таке слово:

    • Ім'я стовпця, який фільтрується, відображається в квадратних дужках.

    • Оператор порівняння одразу слідує за іменем стовпця.

    • Значення фільтра безпосередньо наслідує оператор порівняння, і закінчується на закривну дужку. Виберіть це ціле значення.

  10. Почніть вводити ім'я щойно створеного підключення до таблиці, а потім виберіть його зі списку, що Відкриється.

  11. Натисніть кнопку основне > закрити > закрити & завантажити.

    Тепер запит використовує значення в таблиці, яку ви створили, щоб відфільтрувати результати запиту. Щоб використати нове значення, відредагуйте вміст, а потім оновіть запит.

Додаткові відомості

Створення розкривного списку

Примітка.:  Цю сторінку перекладено за допомогою засобу автоматичного перекладу, тому вона може містити смислові, синтаксичні або граматичні помилки. Ми вважаємо, що цей вміст стане вам у пригоді. Повідомте нас, чи була ця інформація корисною. Для довідки цю статтю можна переглянути англійською мовою.

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо, що знайшли час і надіслали нам відгук! Можливо, у нас не буде часу відповісти на кожен коментар, але докладемо максимум зусиль, щоб переглянути їх усі. Вас цікавить, як ми використовуємо ваші відгуки?

×