Vytvoření parametrického dotazu

Při dotazování na data v Excelu můžete chtít použít vstupní hodnotu – parametr – pro určení něčeho o dotazu. K tomu můžete vytvořit parametrický dotaz. Způsob vytváření parametrického dotazu a jeho chování závisí na tom, jestli používáte Microsoft Query nebo Power Query.

Tip: Parametry Power Query se velmi liší od parametrů použitých v dotazech založených na jazyce SQL. Kromě toho můžete použít dotaz místo skutečného parametru, pokud potřebujete filtrovat data. Než začnete vytvářet parametry v Power Query, zvažte možnost číst oddíly příkladů Power Query.

Microsoft Query

Power Query

Jak parametry ovlivňují dotazy

Parametry se používají v klauzuli WHERE dotazu – vždycky fungují jako filtr pro načtená data.

Parametry lze použít v jakémkoli kroku dotazu. Kromě toho, že se jedná o filtr dat, můžete pomocí parametrů určit takové věci, jako je cesta k souboru nebo název serveru.

Možnosti zadávání parametrů

Parametry mohou uživateli při spuštění nebo aktualizaci dotazu zobrazit vstupní hodnotu, jako vstupní hodnotu použijte konstantu nebo jako vstupní hodnotu použijte obsah zadané buňky.

Parametry nevyžadují výzvu k zadání. Místo toho můžete změnit jejich hodnotu pomocí editoru Power Query. Nebo místo parametru v dobré víře můžete použít dotaz, který odkazuje na externí umístění s hodnotou, kterou můžete jednoduše upravit.

Obor parametru

Parametr je součástí dotazu, který upravuje, a nelze ho použít v jiných dotazech.

Parametry jsou oddělené od dotazů – po vytvoření můžete do dotazů přidat parametr.

  1. Klikněte na data > načíst & transformovat data > načíst data > z jiných zdrojů > z Microsoft Query.

  2. Postupujte podle pokynů Průvodce dotazem. Na obrazovce Průvodce dotazem – dokončení vyberte Zobrazit data nebo upravit dotaz v Microsoft Query a potom klikněte na Dokončit. Otevře se okno Microsoft Query a zobrazí se dotaz.

  3. Klikněte na zobrazit> SQL. V dialogovém okně SQL, které se zobrazí, najděte klauzuli WHERE – řádek začínající slovem WHERE, obvykle na konci kódu SQL. Pokud klauzule WHERE neexistuje, přidejte ji zadáním textu na nový řádek na konci dotazu.

  4. Potom zadejte název pole, operátor porovnání (=, <, >, LIKE atd.) a jednu z těchto věcí:

    • U obecných parametrů zadejte otazník (?). V příkazovém řádku, který se zobrazí při spuštění dotazu, se nezobrazí žádná užitečná fráze.

      Zobrazení SQL aplikace MS Query s důrazem na klauzuli WHERE

    • Pokud chcete, aby uživatelé zadali platný vstup, zadejte do hranatých závorek frázi. Po spuštění dotazu se fráze zobrazí v příkazovém řádku.

      Zobrazení SQL aplikace MS Query s důrazem na klauzuli WHERE

  5. Po dokončení přidávání podmínek s parametry do klauzule WHERE kliknutím na OK spusťte dotaz. Excel vás vyzve, abyste pro každý parametr zadali hodnotu, a pak Microsoft Query zobrazí výsledky.

  6. Až budete chtít data načíst, zavřete okno Microsoft Query a vraťte výsledky do Excelu. Otevře se dialogové okno Importovat data.

    Dialogové okno Importovat data v Excelu

  7. Pokud chcete zkontrolovat parametry, klikněte na vlastnosti. Potom v dialogovém okně Vlastnosti připojení na kartě definice klikněte na parametry.

    Dialogové okno Vlastnosti připojení

  8. V dialogovém okně parametry se zobrazí parametry použité v dotazu. Pokud chcete zkontrolovat nebo změnit, jak se hodnota parametru získá, vyberte v části název parametru hodnotu. Výzvu k zadání parametru můžete změnit, zadat konkrétní hodnotu nebo zadat odkaz na buňku.

    Dialogové okno parametr aplikace MS Query

  9. Kliknutím na OK uložte změny a zavřete dialogové okno parametry a pak v dialogovém okně Importovat data kliknutím na OK zobrazte výsledky dotazu v Excelu.

Teď má sešit parametrický dotaz. Při každém spuštění dotazu nebo aktualizaci datového připojení zkontroluje Excel parametr a doplní klauzuli WHERE dotazu. Pokud parametr vyzve hodnotu, zobrazí se dialogové okno zadat hodnotu parametru pro shromáždění vstupu – můžete zadat hodnotu nebo kliknout na buňku obsahující hodnotu. Můžete také určit, že má být zadaná hodnota nebo odkaz vždy použit a v případě, že použijete odkaz na buňku, můžete určit, že se má v Excelu automaticky aktualizovat datové připojení (tzn. spustit dotaz znovu), když se změní hodnota zadané buňky.

Poznámka: V tomto tématu se dozvíte, jak vytvořit připojení k databázi Accessu pomocí Power Query. Další informace najdete v článku připojení k databázi Accessu.

Parametry můžete použít v dalších scénářích Power Query, než jenom filtrováním dat – libovolný krok dotazu Power Query může mít parametry. Pomocí parametru můžete například určit části řetězce připojení ve zdrojovém kroku, třeba název souboru.

Parametry Power Query mají názvy. Pokud chcete použít parametr, přečtěte si ho podle názvu ve vzorci pro krok. Předpokládejme například, že chcete zkontrolovat data webových stránek, které spravujete, a chcete data vyfiltrovat podle data publikování. Ačkoli můžete v náhledu dotazu vždy používat předdefinované filtry, pomocí parametru pro poskytnutí data pro filtrování ušetří čas a poskytne vám větší flexibilitu. Podívejme se na tento příklad.

V prázdném sešitu vytvoříme připojení k databázi Accessu, která má záznamy webového přenosu, včetně polí určujících, kdy byla stránka původně publikována. V Power Query se načte:

Editor Power Query zobrazující načtená data

Protože chceme filtrovat podle data, změníme datový typ sloupce, který používáme, FirstPublishDate. Jedná se o data a čas dat ve zdroji, ale nezáleží na tom, jakou denní dobu se publikace vyskytla, a je to možné, že se to změní na datový typ Date.

Editor Power Query zobrazující výsledky

Potom vytvoříme parametr pro omezení výsledků podle data, kdy byla stránka původně publikována. Kliknutím na parametrydomácí> > Spravovat parametry otevřete dialogové okno parametry.

Dialogové okno parametry Power Query

Klikněte na Novýa formulář zobrazí nový parametr s názvem parametr1 bez dalších informací.

Změnili jsme některé vlastnosti parametrů:

  • Změnit název na FirstPubD

  • Změna popisu na datum, kdy byla stránka poprvé publikována

  • Změna typu na Datum , aby parametr pouze přijal data.

  • Nastavte aktuální hodnotu tak, aby parametr odfiltroval všechny řádky, když jsme neposkytli žádné vstupy – používáme 1/1/2010.

Tip: Název a popis by měl poskytovat dostatečný kontext, který uživatelům pomůže pochopit, jak a proč tento parametr používat. I když jste jedinou osobou, která bude tento parametr používat, možná budete potřebovat připomenutí času.

Kliknutím na OK tento parametr vytvoříte a uvidíte ho v editoru Power Query.

Editor Power Query zobrazující parametr

Teď je náš parametr uvedený v panelu dotazy – můžeme ho vybrat tam, kde ho na hlavním panelu zobrazíme, nebo na něj můžete kliknout pravým tlačítkem a zobrazit další možnosti. Když je vybraný nějaký parametr, můžeme upravit aktuální hodnotu na hlavním panelu nebo můžete změnit její další nastavení kliknutím na Spravovat parametr .

Tento parametr pak můžeme použít v původním dotazu. Kliknutím na původní dotaz na panelu dotazy ho zobrazíte. Abychom mohli použít náš parametr k filtrování výsledků na základě data první publikace, takže se zobrazí sloupec FirstPublishDate , klikněte na šipku filtr/řazení na pravém okraji záhlaví sloupce, přejděte na filtry dataa pak klikněte na za....

Editor Power Query zobrazující nabídku filtru kalendářních dat

V dialogovém okně Filtrovat řádky vyberte v seznamu možností filtru možnost parametr .

Dialogové okno Filtrovat řádky

Zadejte nebo vyberte hodnotu v seznamu dostupných parametrů. Právě jsme vytvořili, FirstPubD.

Dialogové okno Filtrovat řádky se zobrazením vybraného parametru

Vyberte ho a klikněte na OK. Editor Power Query načte dotaz pomocí nového parametru jako filtr.

Editor Power Query zobrazující filtrované výsledky

Pro otestování parametru změníme svou hodnotu na 1/1/2018.

Editor Power Query zobrazující parametr

Aktualizujeme dotaz, ve kterém se teď po 1/1/2018 zobrazí jenom řádky s FirstPublishDate.

Editor Power Query zobrazující filtrované výsledky

Teď máme dotaz, který filtruje podle data pomocí parametru. Pokud chcete výsledky filtrovat podle FirstPublishDate, nemusíme pole Hledat, klikněte na šipku filtrovat/seřadit. typ filtru a zadejte hodnotu kalendářního data – stačí změnit hodnotu FirstPubD a aktualizovat náš dotaz. Kromě toho můžeme novému parametru znovu použít, například pokud se rozhodnete, že se přidají do nového listu jiná sada polí z původního zdroje dat, ale přesto chcete zahrnout FirstPubDate a použít ho k filtrování výsledků.

Parametry jsou jasně velmi užitečné, ale i přesto je potřeba změnit hodnotu parametru pomocí editoru Power Query Editor. Chtěli bychom být schopni změnit hodnotu filtru bez otevření editoru Power Query Editor. Chcete-li to udělat, vytvoříme tabulku na listu, kde je dotaz načtený, a na nové připojení Power Query a pak použijte nový dotaz k filtrování našeho hlavního dotazu.

Na listu, na kterém je náš dotaz načten, vložíme řádky nad importovaná data. Pak vytvoříte excelovou tabulku s jedním řádkem, abyste podrželi naši hodnotu parametru.

Excelový sešit zobrazující tabulku parametrů a data načtená z Power Query

Abyste mohli pomocí nové tabulky filtrovat dotazy, musíme se k ní připojit v Power Query. K tabulce vytvoříte připojení tak, že ji vyberete a kliknete na z tabulky/oblasti na kartě data . Nové připojení se otevře a zobrazí novou tabulku v editoru Power Query.

Data tabulky aplikace Excel načtená v editoru Power Query

Vzhledem k tomu, že data zavedená jako datový typ datum a čas potřebujeme změnit na datový typ Date, aby odpovídala parametru, takže jsme na kartě domů > transformovat > datový typ > Datum.

Najeďte myší na příkaz datový typ ve skupině transformace na kartě Domů na pásu karet editoru Power Query.

Tento dotaz také přejmenujeme na něco výstižnější než Tabulka2. Pokud chcete, aby bylo jasné, co je pro vás důležité, pojmenuje IT FirstPubDate.

Editor Power Query se zvýrazněným polem název

Vzhledem k tomu, že chceme předat hodnotu, ne samotnou tabulku, potřebujeme přejít k podrobnostem na hodnotu Date. Chcete-li to provést, klikněte pravým tlačítkem na hodnotu v zobrazeném náhledu dat a pak klikněte na Přejít k podrobnostem.

Kontextová nabídka editoru Power Query pro hodnotu pole

V náhledu se teď místo tabulky zobrazuje hodnota.

Editor Power Query zobrazující jednu hodnotu data

Nepotřebujeme data nového dotazu načíst všude – data jsou už na listu, kde je chcete mít. Abychom mohli Power Query zjistit hodnotu parametru, stačí připojení. Když kliknete na soubor> Zavřít & načíst do... otevřete dialogové okno Importovat data a vyberete jenom připojení vytvořit.

Dialogové okno Importovat data s vybranou možností pouze vytvořit připojení

Teď máme dotaz s názvem "FirstPubDate", který vyhodnotí jedinou hodnotu kalendářního data z tabulky na listu přímo nad tím, kde se načítá náš hlavní dotaz. Teď je potřeba tento dotaz použít jako parametr pro filtrování našeho hlavního dotazu. Proto otevřeme hlavní dotaz a upravte krok, který filtruje řádky pomocí sloupce FirstPublishDate. Rozbalíme řádek vzorců a vyberete parametr, který byl dřív vytvořen (FirstPubD). Potom zadáte "a" po FirstPubD – protože název nového dotazu začíná stejnými písmeny jako parametr, Power Query ho zobrazí jako možnost výběru.

Rozbalený řádek vzorců editoru Power Query

Vyberte ji a potom kliknutím mimo řádek vzorců použijte tento krok.

Editor Power Query s načtenými daty

Vše se zobrazí správně, takže Editor Power Query ukončíte a změny uložíte. K otestování parametru v listu sestavy jsme změnili hodnotu buňky v tabulce v horní části na 5/4/2019, ale aktualizace připojení se zobrazí, abyste viděli filtrovaná data.

Filtrovaná data v Excelu

Náš nový filtr funguje! Uložte a zavřete sešit. Teď může kdokoli, kdo používá sešit, určit datum první publikace, které se použije jako filtr dotazu – přímo na stejném listu, kde je dotaz načtený.

  1. Klikněte na data > načíst & transformovat data > načíst data > Spusťte Editor Power Query.

  2. V editoru Power Query klikněte na parametry pro domácí > > Spravovat parametry.

  3. V dialogovém okně parametry klikněte na Nový.

  4. Podle potřeby nastavte následující:

    • Name (název ): funkce parametru by měla odrážet funkci, ale zachovat ji co nejkratší.

    • Popis – Tato možnost může obsahovat všechny podrobnosti, které uživatelům pomůžou správným způsobem použití tohoto parametru.

    • Required – Pokud chcete, aby tento parametr vyžadoval hodnotu, zaškrtněte toto políčko.

    • Type -Určuje datový typ, který parametr vyžaduje.

    • Navrhované hodnoty : Pokud chcete, přidejte seznam hodnot nebo zadejte dotaz, který vám poskytne návrhy na zadání.

    • Výchozí hodnota : zobrazí se jenom v případě, že jsou navrhované hodnoty nastavené na seznam hodnot a určí, která položka seznamu je výchozí.

    • Aktuální hodnota : v závislosti na tom, kde se tento parametr používá, se může stát, že dotaz nevrátí žádné výsledky. Pokud je zaškrtnutá možnost, nesmí být aktuální hodnota prázdná.

  5. Kliknutím na OK tento parametr vytvořte.

  1. Otevřete dotaz v editoru Power Query.

  2. Klikněte na šipku na pravém okraji záhlaví sloupce, který chcete použít k filtrování dat, a v zobrazené nabídce zvolte filtr.

  3. V dialogovém okně Filtrovat řádky klikněte na tlačítko napravo od podmínky filtru a pak proveďte jednu z následujících akcí:

    • Pokud chcete použít existující parametr, klikněte na parametra vyberte požadovaný parametr ze seznamu vpravo.

    • Pokud chcete použít nový parametr, klikněte na Nový parametra pak vytvořte parametr.

  1. Na listu, kde je načten dotaz, který chcete filtrovat, vytvořte tabulku se dvěma buňkami: záhlaví a hodnota.

  2. Klikněte na hodnotu a potom klikněte na data > načíst & transformovat data > z tabulky nebo oblasti.

  3. V editoru Power Query proveďte jakékoli úpravy připojení k tabulkám (například změnou datového typu nebo názvu) a potom klikněte na domů > zavřít > zavřít & načíst > Zavřít & načíst do....

  4. V dialogovém okně Importovat data klikněte na pouze vytvořit připojení, volitelně vyberte Přidat do datového modelua potom klikněte na OK.

  5. Otevřete dotaz, který chcete filtrovat, v editoru Power Query.

  6. Klikněte na šipku na pravém okraji záhlaví sloupce, který chcete použít k filtrování dat, a v zobrazené nabídce zvolte filtr.

  7. Proveďte jednu z následujících akcí:

    • V rozevíracím seznamu hodnot vyberte hodnotu (ty pocházejí z dotazovaných dat).

    • Vyberte hodnotu pomocí tlačítka na pravém okraji podmínky filtru.

  8. Kliknutím na šipku na pravém okraji řádku vzorců zobrazíte celý dotaz.

  9. Podmínka filtru za slovem:

    • Název filtrovaného sloupce se zobrazí v hranatých závorkách.

    • Operátor porovnání bezprostředně následuje za názvem sloupce.

    • Hodnota filtru bezprostředně za relačním operátorem a končí pravou závorkou. Vyberte celou hodnotu.

  10. Začněte psát název spojení tabulky, které jste právě vytvořili, a pak ho vyberte ze seznamu, který se zobrazí.

  11. Klikněte na domů > zavřít > Zavřít &.

    Dotaz teď používá hodnotu v tabulce, kterou jste vytvořili, k filtrování výsledků dotazu. Chcete-li použít novou hodnotu, upravte obsah buňky a aktualizujte dotaz.

Viz také

Vytvoření rozevíracího seznamu

Poznámka:  Tato stránka byla přeložena automaticky a může obsahovat gramatické chyby nebo nepřesnosti. Naším cílem je to, aby pro vás byl její obsah užitečný. Mohli byste nám prosím dát vědět, jestli vám informace pomohly? Pokud chcete, můžete se podívat na anglickou verzi článku.

Rozšiřte své dovednosti s Office
Projít školení
Získejte nové funkce jako první
Připojte se k účastníkům programu Office Insiders

Byly tyto informace užitečné?

Děkujeme vám za zpětnou vazbu.

Děkujeme vám za váš názor! Pravděpodobně bude užitečné, když vás spojíme s některým z našich agentů podpory Office.

×