Създаване на параметризирана заявка.

Когато задавате данни в Excel, може да поискате да използвате входна стойност – параметър, за да зададете нещо за заявката. За да направите това, създавате заявка за параметър. Начинът, по който създавате заявки за параметри и как те се държат, зависи от това дали използвате Microsoft Query, или Power Query.

Съвет: Параметрите на Power Query са много различни от параметрите, използвани в базирани на SQL заявки. Освен това можете да използвате заявка вместо действително параметър, ако е необходимо само да филтрирате данните. Обмислете прочитането на секциите на Power Query, преди да създадете параметри в Power Query.

Заявка за Microsoft

Power Query

Как параметрите влияят върху заявките

Параметрите се използват в клаузата WHERE на заявката – те винаги функционират като филтър за извлечени данни.

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

Опции за въвеждане на параметър

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

Параметрите не питат за въвеждане. Вместо това можете да промените тяхната стойност, като използвате редактора на Power Query. А вместо да имате недобросъвестен параметър, можете да използвате заявка, която препраща към външно местоположение със стойност, която лесно можете да редактирате.

Обхват на параметъра

Параметърът е част от заявката, която то променя, и не може да се използва повторно в други заявки.

Параметрите са отделени от заявките-веднъж създадени, можете да добавите параметър към заявките, ако е необходимо.

  1. Щракнете върху данни , за > получите & трансформиране на данни , за > получите данни > от други източници , > от Microsoft Query.

  2. Следвайте стъпките в съветника за заявки. В съветника за заявки – край на екрана, изберете Преглед на данни или редактиране на заявка в Microsoft Query и след това щракнете върху Готово. Отваря се прозорецът на Microsoft Query и показва вашата заявка.

  3. Щракнете върху преглед> SQL. В диалоговия прозорец SQL, който се появява, намерете клаузата WHERE – ред, започващ с думата, в която обикновено е краят на SQL кода. Ако няма клауза WHERE, добавете една чрез въвеждане, където на нов ред в края на заявката.

  4. След това въведете името на полето, оператор за сравнение (= <, >, LIKE и т. н.) и едно от следните:

    • За подкана за общи параметри въведете въпросителен знак (?). В подканата се показва не полезна фраза, която се появява, когато заявката се изпълни.

      SQL изглед на MS Query, акцентиращ върху клаузата WHERE

    • За подканване за параметър, което помага на хората да предоставят валиден вход, въведете фраза, оградена с квадратни скоби. Фразата се показва в подканата за параметър, когато заявката се изпълни.

      SQL изглед на MS Query, акцентиращ върху клаузата WHERE

  5. След като приключите с добавянето на условия с параметри към клаузата WHERE, щракнете върху OK , за да изпълните заявката. Excel ви подкани да предоставите стойност за всеки параметър, а след това Microsoft Query показва резултатите.

  6. Когато сте готови да заредите данните, затворете прозореца на Microsoft Query, за да върнете резултатите в Excel. Отваря се диалоговият прозорец Импортиране на данни.

    Диалогов прозорец "Импортиране на данни" в Excel

  7. За да прегледате параметрите си, щракнете върху свойства. След това в диалоговия прозорец Свойства на връзката, в раздела дефиниция щракнете върху параметри.

    Диалогов прозорец "свойства на връзката"

  8. В диалоговия прозорец параметри се показват параметрите, използвани в заявката. Изберете параметър под име на параметър за преглед или промяна на начина, по който се получава стойност на параметър. Можете да промените подканата за параметър, да въведете конкретна стойност или да зададете препратка към клетка.

    Диалогов прозорец за параметър на MS заявка

  9. Щракнете върху OK , за да запишете промените си и да затворите диалоговия прозорец параметри, а след това в диалоговия прозорец Импортиране на данни щракнете върху OK , за да покажете резултатите от заявката в Excel.

Сега вашата работна книга има заявка за параметър. Всеки път, когато изпълнявате заявката или обновявате нейната връзка за данни, Excel проверява параметъра, за да завърши клаузата WHERE на заявката. Ако параметърът подкани за стойност, Excel показва диалоговия прозорец въвеждане на стойност на параметър, за да събере входните данни – можете да въведете стойност или да щракнете върху клетка, която съдържа стойността. Можете също да укажете, че стойността или препращането, които предоставяте, трябва да се използва винаги и ако използвате препратка към клетка, можете да укажете, че Excel трябва автоматично да обновява връзката за данни (т. е. да изпълнява заявката отново), когато стойността на зададената клетка се промени.

Забележка: Тази тема предполага, че знаете как да създадете връзка към база данни на Access с помощта на Power Query. За повече информация вижте Свързване към база данни на Access.

Можете да използвате параметри в повече сценарии за Power Query, отколкото само да филтрирате данни – всяка стъпка от заявка на Power Query може да има параметри. Например можете да използвате параметър, за да укажете части от низ за свързване в стъпката източник, като например име на файл.

Параметрите на Power Query имат имена. За да използвате параметър, можете да препращате към него по име във формулата за стъпка. Например, Представете си, че искате да прегледате данните за поддържаните уеб страници и искате да филтрирате данните по дата на публикуване. Въпреки че винаги можете просто да използвате вградените филтри в предварителния преглед на заявката, като използвате параметър, за да предоставите дата за филтрирането, ще ви спести време и по-голяма гъвкавост. Нека прегледаме този пример.

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

Изображение на бутон

Тъй като искаме да филтрираме по дата, ние променяме типа на данните на колоната, която използваме, FirstPublishDate. Това е данни за дата и час в източника, но не ни пука кога е възникнала публикацията за деня и е необходимо да уточните, че може да бъде изморителна – така че ще я променим към типа данни за дата.

Изображение на бутон

След това създаваме параметър за ограничаване на резултатите по дата на публикуване на страницата. Щракнете върху начало> параметри > управление на параметри , за да отворите диалоговия прозорец параметри.

Диалогов прозорец ' ' параметри на Power Query ' '

Щракнете върху Създайи формулярът показва нов параметър с име Parameter1 без друга информация.

Ние променяме някои свойства на параметъра:

  • Промяна на името на FirstPubD

  • Промяна на Описание към датата, на която страницата е публикувана за първи път.

  • Промяна на типа към днешна дата , така че параметърът да приема само стойности на дати

  • Задаване на текуща стойност , така че параметърът да не филтрира всички редове, когато не сме предоставили въвеждане – използваме 1/1/2010.

Съвет: Името и описанието трябва да предоставят достатъчно контекст, за да помагат на хората да разберат как и защо да използват параметъра. Дори ако сте единственият човек, който ще използва параметъра, може да ви е необходимо напомняне от време на време.

Щракнете върху OK , за да създадете параметъра и да го видите в редактора на Power Query.

Изображение на бутон

Сега нашият параметър е показан в панела "заявки" – можем да го изпробваме там, за да го покажем в основния панел или да щракнете с десния бутон върху него за още опции. Когато е избран параметър, ние можем да редактираме текущата стойност в основния панел или да щракнете върху управление на параметър , за да промените другите му настройки.

Сега можем да използваме този параметър в първоначалната ни заявка. Ние щракваме върху първоначалната заявка в панела за заявки, за да я покажем. Ние искаме да използваме нашите параметри, за да филтрираме резултатите на базата на датата на първото публикуване, така че след това ще изберем колоната FirstPublishDate , щракнете върху стрелката за филтриране/сортиране в десния ръб на заглавието на колоната, посочете филтри за датаи след това щракнете върху след...

Изображение на лентата на Power Query

В диалоговия прозорец "филтриране на редове" избираме параметър от списъка с възможности за избор във филтъра.

Диалогов прозорец "филтриране на редове"

ENTER или изберете стойност се замества със списък с наличните параметри. Има само един, този, който току-що създадохме, FirstPubD.

Диалогов прозорец "филтриране на редове", показващ избрания параметър

Ние го изберете и щракнете върху OK. В редактора на Power Query се зарежда заявката с помощта на новия параметър като филтър.

Изображение на бутон

За да тествате параметъра, ние променяме стойността му на 1/1/2018.

Изображение на бутон

Обновяваме заявката, която сега показва само редове, които имат FirstPublishDate след 1/1/2018.

Изображение на бутон

Сега имаме заявка, която филтрира по дата с помощта на параметър. За да филтрирате резултатите по 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 с осветено поле за име

Тъй като искаме да предадем стойност, а не самата таблица, трябва да задълбаем към стойността на датата. За да направите това, трябва да щракнете с десния бутон върху стойността в визуализираните данни и след това да щракнете върху детайлизиране.

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

Визуализацията сега показва стойността вместо таблицата.

Изображение на бутон

Нямаме нужда от данните за новата заявка, за да се зареждаме навсякъде – нейните данни вече са в работния лист, в който го искаме. Ние просто трябва връзката, така че Power Query да може да получи стойността на параметъра. И така, ние щракваме върху файл> Затвори & натоварване на... , за да отворите диалоговия прозорец Импортиране на данни, и след това изберете само създаване на връзка.

Диалогов прозорец "Импортиране на данни" с избрана опция "само създаване на връзка"

Сега имаме заявка, наречена "FirstPubDate", която изтегля единична стойност за дата от таблица в работния лист точно над мястото, където се зарежда нашата основна заявка. Сега трябва само да използваме тази заявка като параметър за филтриране на нашата основна заявка. И така, отваряме основната заявка и редактираме стъпката, която филтрира редовете с помощта на колоната FirstPublishDate. Разширяваме лентата за формули и избираме параметъра, който създадохме преди това (FirstPubD). След това въвеждаме "а" след FirstPubD -защото името на новата заявка започва със същите букви като параметъра, Power Query го показва като опция за избиране.

Разгъната лента на редактора за формули на Power Query

Ние го изберете и след това щракнете извън лентата за формули, за да приложите стъпката.

Редактор на Power Query с заредени данни

Всичко изглежда правилно, за да излезете от редактора на Power Query и да запишете нашите промени. За да тествате параметъра, в работния лист на отчета ние променяме стойността на клетката в таблицата в горния край на 5/4/2019, след което обновяваме връзката, за да видим филтрираните данни.

Филтрирани данни в Excel

Новият ни филтър работи! Така че записваме и затваряме работната книга. Сега всеки, който използва работната книга, може да посочи дата на първата публикация, която да се използва като филтър за заявки – точно там в същия работен лист, в който се зарежда заявката.

  1. Щракнете върху данни , за > получите & трансформиране на данни , за > получите данни > стартирате редактора на Power Query.

  2. В редактора на Power Query щракнете върху начало > параметри > управление на параметри.

  3. В диалоговия прозорец параметри щракнете върху Създай.

  4. Задайте следното, ако е необходимо:

    • Name – това трябва да отразява функцията на параметъра, но да е възможно най-кратко.

    • Описание – това може да съдържа всякакви подробности, които ще помогнат на хората да използват правилно параметъра.

    • Задължително -изберете, за да направите този параметър изисква стойност.

    • Тип -това указва типа данни, който изисква параметърът.

    • Предложени стойности – ако желаете, добавете списък със стойности или укажете заявка, за да предоставите предложения за въвеждане.

    • Стойност по подразбиране – това се показва само ако предлаганите стойности е зададено на списък със стойности и указва кой елемент от списък е този по подразбиране.

    • Текуща стойност – в зависимост от това къде използвате параметъра, ако това е празно, заявката може да върне никакви резултати. Ако е избрана, тази стойност не може да бъде празна.

  5. Щракнете върху OK , за да създадете параметъра.

  1. Отворете заявка в редактора на Power Query.

  2. Щракнете върху стрелката в десния ръб на заглавката на колоната, която искате да използвате за филтриране на данните, и след това изберете филтър от менюто, което се появява.

  3. В диалоговия прозорец Филтриране на редове щракнете върху бутона отдясно на състоянието на филтъра и след това направете едно от следните неща:

    • За да използвате съществуващ параметър, щракнете върху параметъри след това изберете желания от вас параметър от списъка, който се появява отдясно.

    • За да използвате нов параметър, щракнете върху нов параметър..., след което създайте параметър.

  1. В работния лист, където се зарежда заявката, която искате да филтрирате, създайте таблица с две клетки: горен колонтитул и стойност.

  2. Щракнете върху стойността, след което щракнете върху данни , за > получите & трансформиранена > на данни от таблица/диапазон.

  3. В редактора на Power Query направете каквито и да е промени в връзката към таблицата (например промяна на типа на данните или името), след което щракнете върху начало > затвори > затвори & натоварване > Затвори & зареждане към....

  4. В диалоговия прозорец Импортиране на данни щракнете върху само създаване на връзка, по желание изберете Добавяне към модела на данни, след което щракнете върху OK.

  5. Отворете заявката, която искате да филтрирате, в редактора на Power Query.

  6. Щракнете върху стрелката в десния ръб на заглавката на колоната, която искате да използвате за филтриране на данните, и след това изберете филтър от менюто, което се появява.

  7. Направете едно от следните неща:

    • Изберете стойност от падащия списък със стойности (тези идват от заявените данни).

    • Изберете стойност с помощта на бутона на десния ръб на състоянието на филтъра.

  8. Щракнете върху стрелката в десния край на лентата за формули, за да се покаже цялата заявка.

  9. Условието на филтъра следва думата всеки:

    • Името на колоната, която се филтрира, се показва в квадратни скоби.

    • Операторът за сравнение веднага следва името на колоната.

    • Стойността на филтъра веднага следва оператора за сравнение и завършва на затваряща скоба. Изберете цялата стойност.

  10. Започнете да въвеждате името на връзката към таблицата, която току-що създадохте, и след това я изберете от списъка, който се появява.

  11. Щракнете върху начало > затвори > Затвори & натоварване.

    Вашата заявка сега използва стойността в таблицата, която създадохте, за да филтрирате резултатите от заявката. За да използвате нова стойност, редактирайте съдържанието на клетката и след това обновете заявката.

Вж. също

Създаване на падащ списък

Забележка:  Тази страница е преведена чрез автоматизация и може да съдържа граматически грешки и несъответствия. Нашата цел е това съдържание да ви бъде полезно. Можете ли да ни кажете дали информацията е била полезна за вас? Ето статията на английски за справка.

Разширете уменията си в Office
Преглед на обучението
Получавайте първи новите функции
Присъединете се към участниците в Office Insider

Беше ли полезна тази информация?

Благодарим ви за обратната връзка!

Благодарим ви за вашата обратна връзка. Изглежда, че ще бъде полезно да ви свържем с един от нашите агенти по поддръжката на Office.

×