Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » Query-editor » Jak-radit-filtrovat-Power-Query-Excel
Microsoft Excel logo

Jak řadit a filtrovat v Power Query - Excel

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak na filtrování a řazení v PowerQuery Excel Včetně tiků jak řadit podle dvou a více sloupců současně

Úvodem do řazení a filtrování v PowerQuery

Kromě načtení a úpravy dat potřebujete mnohdy v PowerQuery i filtrovat a řadit. Jak na filtrování a řazení se podíváme v tomto článku. Přepokládám, že v Excel máte k dispozici PowerQuery a umíte data do PowerQuery načíst. V tomto článku se tedy podívám jen na filtrování a řazení, navíc bude článek z důvodu přehlednosti rozdělen na jednotlivé kapitoly


Zdrojová data

Pro řazení potřebujeme tabulku, která obsahuje data. Aby šlo ukázat co nejvíce alternativ, moje demo tabulka sloupec textové, číselný, datumový, časový, dva sloupce obsahující chyby, abecedu, atd.

Ukázka zdrojových data pro Filtrovaní a řazení - PowerQuery - Excel

Tuto tabulku (nebo podobnou tabulku sešit stačí načíst do PowerQuery). Jak na to se můžete dozvědět v článku: Jak načíst excelovský sešit do PowerQuery

Jak řadit

Klasické řazení, které znáte z Excel, asi nemuím připomínat. Podobně můžete řadit i v PowerQuery. Vyberete sloupec a zda chcete seřadit vzestupně (A až Z, 0 až "nekonečno") nebo sestupně (Z - A , "nekonečno" - 0 potažmo "mínus nekonečno"):

Filtrovaní a řazení - PowerQuery - Excel

Následně se seřadí záznamy (celé řádky) dle příslušného sloupce. Řadí se vždy celé řádky (což je logické, ale pro jistotu připomenu).

Poznámka: Lze řadit o podle více sloupců, ale o tom v následující kapitole.

¨

Co abeceda

Co text čísla, co písmenko Ch, co velká a malá písmena. Zkoušejte, klidně si vytvořte sloupec s písmen znaků a čísel a testujte. Případně si můžete nastavit vlastní způsob řazení, ale o tom také v samostatné kapitole.

Filtrovaní a řazení - PowerQuery - Excel

Jak řadit data více sloupců

Jak seřadit více sloupců "nakliknutím" (využitím myši) nevím (snad někdo poradí v komentářích). Protože mám rád M-language (M-jazyk), tak řešení je jednoduché, přidat do kódu další sloupec, podle kterého se bude řadit. Nebo-li do kódu, který se objeví po "nakliknutí" požadavku na seřazení Příjmení:

= Table.Sort(Zdroj,{{"Přijmení", Order.Ascending}})

Stačí doplnit o {"Jméno", Order.Descending} kde je jméno sloupce a způsob řazení sestupně a je hotovo

= Table.Sort(Zdroj,{{"Přijmení", Order.Ascending}, {"Jméno", Order.Descending}}) Filtrovaní a řazení - PowerQuery - Excel

Jak řadit podle svých kritérií

V přípravě. Řazení podle vlastních kritérií (seznamů) je nepatrně složitějši, dokud nebude v Power Query volba řadit dle ... (Na druhou stranu, kdo prakticky využijete? Kdo ano již určitě zvládáte M-language a vlastní seznamy atd.).

Filtrovat číslo

Na první pohled jednoduchá volba, ale na ten druhý, kromě oblíbeného čísla (42) si můžete filtrovat, větší než, menší než atd.

Filtr podle čísla - možností - Filtrovaní a řazení - PowerQuery - Excel

Filtrovat texty - jak nahradit hvězdičky a otazníky

Klasický oblíbený filtr, zaškrtnete si co potřebujete mít zobrazeno. Zde není třeba představovat.

U filtrování textů v Excel možná znáte hvězdičkovou a otazníkovou konvenci (což na první pohled se vám v PowerQuery nepodaří). Jak na to ale v Power Query.

Můžete v menu využít, a možností jako začíná na, obsahuje, atd.

Filtr textů: Filtrovaní a řazení - PowerQuery - Excel

Obdržíte dialogové okno, kde můžete nastavit požadované parametry (případně přidat další za využití logických spojek A a Nebo):

Filtr textů - dialogové okno: Filtrovaní a řazení - PowerQuery - Excel

Pozor PowerQuery rozlišuje malá a velká písmena.

Filtrovat specifické znaky

Jak na hledání specifických znaků se podíváme v samostatném článku. Stejně tak jak využít reguláry. Neboli jak vylepšit klasické filtrování z PowerQuery využitím M-language.

= Table.SelectRows(#"Seřazené řádky", each Text.Contains([Město], "o") or Text.Contains([Město], "O"))

Využít pro speciální filtrování lze o věcí jako přidání sloupce a podmínky, propojení dvou tabulek, funkce, atd. Tyto záležitostí si nechám do samostatného článku (třeba na základě dotazů v komentářích ho připravím přednostně).

Filtrovat chybové hodnoty

Spíše chybové hodnoty chcete odfiltrovat, pracovat můžete s celou tabulkou a pokud se někde chybová hodnota nachází, odfiltruje (schová) se celý řádek. Případně můžete provádět jen nad určitým sloupcem. Podívejme se na oba případy:

  • Odstranit chybové řádky z celé tabulky
  • Odstranit chybové řádky dle vybraného sloupce

Odstranit chybové řádky z celé tabulky

Rychlejší řešení, prostě se skryjí (odfiltrují) všechny řádky obsahující v některém ze sloupců chybovou hodnotu. Nad celou tabulkou a z menu vybrat odebrat chyby.

Filtrovaní a řazení - PowerQuery - Excel

Odstranit chybové řádky dle vybraného sloupce

Vyberete požadovaný sloupce a z kontextového menu (pravý klik nad sloupcem) si vyberete odebrat chyby, skryjí se pak jen řádky které mají chybovou hodnotu ve vybraném sloupci.

Filtrovaní a řazení - PowerQuery - Excel

Ostatní

Co se jinam nevešlo, aneb zajímavé tipy a poznatky, co mě různě napadne, nebo jsem řešil:

  • Více sloupců
  • Přes parametr
  • Neúplný seznam

Více sloupců

Zde není co řešit prostě výběr provedete nad dalším sloupcem a vyberete (vyfiltrujete) si požadované hodnoty. Je jasné, pokud předchozí filtr vyfiltroval hodnoty a již se ve sloupci nenacházejí, tak je nemůžete filtrovat. Pokud máte vyfiltrovány všechny muže (vidíte jen M - nefoli Male), je jasné, že ve sloupci jméno neuvidíte ženské jméno (pominu-li fakt, že by žena náhodou mohla mít mužské jméno).

Přes parametr

Filtrovat, lze i na základě parametru, hodnoty v tabulce Excel a pak jen data aktualizovat. Jak na to jsme sepsal v samostatných článcích, protože způsobů, jak využít volání parametru do PowerQuery (M-language) je několik:

Neúplný seznam

V seznamu nejsou k dispozici všechny položky:

neplný sesznam - Filtrovaní a řazení - PowerQuery - Excel

Podklady pro M-language

M-language je velice zajímavý, jednoduše použitelný a inspirativní, pokud ho umíte použít, dokážete věci, které se "nakliknutím" provést (zatím) nedají. Například řadit, podle více sloupců atd.

= Table.Sort(Zdroj,{{"Přijmení", Order.Ascending}, {"Jméno", Order.Descending}})

Případně hledat služitejší kombinace, hlavně využitím regulárních výrazů:

= Table.SelectRows(#"Seřazené řádky", each Text.Contains([Město], "o") or Text.Contains([Město], "O"))

Případně za využití pomocného sloupce

= Table.AddColumn(#"Text", "Category", each if List.Count(Splitter.SplitTextByAnyDelimiter({"A","B","C","D"})([Description])) > 1 then "Divize" else null)

O M-language mám sepsány samostatné články:

Související články

Související články, které vám pomohou pochopit jak je PowerQuery skvělý nástroj:


¨ Microsoft Excel VBA - stahuj logo

Ke stažení

Soubor ke stažení zdarma je v přípravě. Podpořit zveřejnění můžete podporou na Patreon.


Závěrem

Máte nějaký tip, co se dá k filtrování a řazení doplnit (třeba jak klikáním seřadit dle více sloupců)? Můžete se zmínit v komentářích.

Článek byl aktualizován: 03.12.2019 14:21

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.






Excel


Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2019 | 102

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.