Jiné seřazování a filtrování v Excelu - Užitečné věci pro Office, šablony, Excel, Word, prezentace
Hledání
Novinky
Návštěvy
Dnes: 7
Celkem: 149442

Jiné seřazování a filtrování v Excelu

  • Argumenty funkce Sort
    Argumenty funkce Sort
  • Ukázka seřazení
    Ukázka seřazení
  • Zobrazí se nuly
    Zobrazí se nuly
  • Ošetření prázdných buněk
    Ošetření prázdných buněk
  • Argumenty funkce Sortby
    Argumenty funkce Sortby
  • Řazení podle dvou polí
    Řazení podle dvou polí
  • Argumenty funkce Filter
    Argumenty funkce Filter
  • Výběr podle barvy
    Výběr podle barvy
  • Výběr podle dvou kritérií
    Výběr podle dvou kritérií
  • Výběr dvou barev
    Výběr dvou barev
  • Seřazení výběru
    Seřazení výběru
  • Ukázka práce s funkci Unique
    Ukázka práce s funkci Unique
  • Výběr bude seřazený
    Výběr bude seřazený
  • Ukázka výběru přes dva sloupce
    Ukázka výběru přes dva sloupce
  • Výběr u nesousedních sloupců
    Výběr u nesousedních sloupců

Seřazovat a filtrovat se dalo přece v Excelu vždycky. Stačí jít na kartu Data a člověk má vše pěkně po ruce.  Tyto standardní nástroje změní pořadí řádků přímo v samotném seznamu dat. Ale v aktualizacích Excelu se objevili novinky. Jedná se o nové funkce Sort, Sortby nebo Filter. Zajímavá funkce je i Unigue.

Funkce Sort

Funkce Sort ale pracuje jinak než příkaz Seřadit. Původní seznam zůstane totiž nezměněn a vytvoří se jeho seřazená nová verze v jiné části listu nebo třeba i na jiném listu.  Její tvar je:

=SORT(pole; index_řazení; pořadí_řazení; podle_sloupce),

kde pole je povinný argument a jedná se o oblast nebo pole, které se má seřadit. Druhý argument index_řazení je sice nepovinný, ale určuje sloupec, případně řádek, podle kterého se mají hodnoty seřazovat. Pokud se nezadá, pak se jedná o první sloupec či řádek. Další argument je také nepovinný, má název pořadí_řazení. Určuje, zda se má řadit vzestupně (1) či sestupně (-1). A poslední argument podle_sloupce je logická hodnota, která říká, zda se mají seřazovat řádky (nepravda, nebo bez zadání) či sloupce (pravda).

Existuje jednoduchá tabulka, která má zůstat seřazená podle měsíců v roce, ale bylo by užitečné ji mít vedle seřazenou pro porovnání například podle příjmů. 

K tomu je právě vhodná funkce Sort, která vám toto jednoduše a rychle zařídí. Jen je třeba správně doplnit potřebné argumenty. Pozor, nesmí se označovat záhlaví tabulky, protože by bylo jinak zařazeno do seřazování.

Poznámka: Funkce Sort je zástupcem nového druhu maticových vzorců, které by měly nahradit klasické maticové vzorce, jež bylo nutné potvrzovat klávesami Ctrl+Shift+Enter. U nových maticových vzorců stačí potvrzení běžně klávesou Enter. Stále ale zůstává vlastností základních maticových vzorců, a to nutnost úpravy či mazání v první buňce, tedy v té, ve které byl vzorec vytvořen. Po klepnutí do libovolné buňky v oblasti výsledků dojde k zobrazení modré linky ohraničující celou oblast výsledků.  Jinak když se klepne do jiné buňky než první, vzorec bude sice vidět, ale jen světle šedě. Ještě je zde jedna věc. Výsledky funkce se prakticky jakoby přelívají do dalších buněk. Proto je důležité pamatovat na to, aby tato oblast byla prázdná. Pokud by v oblasti něco bylo, zobrazí se chybové hlášení #PRESAH! Stejná chyba by se objevila i v případě, že by se ve vyplňované oblasti nacházela buňka sloučená.

Nenápadný problém – prázdné buňky

Jestliže jsou ve zdrojovém seznamu prázdné buňky, tak je funkce Sort v seřazeném seznamu standardně převede na nuly. To nemusí být vždy užitečné.

Pokud se má tomuto zabránit, je třeba použít nějaký test, například pomocí funkce Když, do které se funkce Sort vloží. Pokud je výsledek prázdná hodnota, pak se nic nezapíše, jinak se zapíše do nové oblasti výsledek funkce Sort. Na obrázku je tento test hezky vidět. Pouze takto mohou zůstat prázdné buňky i po seřazení.

Funkce Sortby

Funkce Sortby pracuje trochu jinak, i když vychází z principu funkce Sort. Slouží k seřazení obsahu oblasti na základě hodnot v odpovídající jiné oblasti či matici. Jak tomu rozumět? Co umí jinak? Například navíc umí do nové oblasti přenést jen data z potřebného sloupce (řádku). Nebo zvládá také seřazení podle více sloupců zároveň.

Její zápis je:

=SORTBY(pole; podle_pole; pořadí_řazení; …),

kde pole je povinný argument a jedná se o oblast nebo pole, které se má seřadit. Druhý argument podle_pole je pole nebo oblast podle které se hodnoty budou řadit. A argument pořadí_řazení je hodnota určující požadované pořadí řazení, to znamená opět 1 pro vzestupně či -1 pro řazení sestupné.

Je třeba například získat z určitého seznamu pouze města, která budou seřazena podle počtu prodaných kusů v daném městě sestupně. Pak je právě funkce Sortby ideální.

Jako argument pole je odkaz na sloupec, kde je město, argument podle_pole je odkaz na sloupec, kde je počet prodaných kusů. A následující argument je nastaven na -1, to znamená řazení sestupné.

Další využití funkce Sortby je tehdy, když by bylo třeba seřazovat podle více sloupců (řádků) současně. Umí totiž opravdu také zobrazit nejenom daný sloupec, ale i celou oblast (všechny údaje zdrojové oblasti). Třeba by bylo nutné seřazovat podle typu zboží a v případě shodného typu, pak podle barvy.

Jako argument pole je odkaz na celou oblast dat a poté jako , argument podle_pole1 je odkaz na sloupec, kde je zadaný typ výrobku. Potom jako argument podle_pole2 je zadaný sloupec, ve kterém jsou uvedeny barvy výrobků.

Funkce Filter

Podobně jako seřazování je v Excelu běžné, tak je hodně využívané i filtrování dat. Vše je prostě na kartě Data rychle k dispozici. Ale nyní z nově přidanými funkcemi (od verze 365) lze využít i funkci Filter. Ta umožňuje filtrovat oblast dat na základě kritérií, která se nadefinují. Výhodou je to, že vyfiltrovaný seznam je na novém místě.

Tato funkce má tři argumenty, její tvar je

=FILTER(pole; zahrnuje; pokud_prázdné),

kde první argument s názvem pole odkazuje zdrojovou oblast dat, které se mají filtrovat. V druhém argumentu s názvem zahrnuje se určuje podmínka filtrace, a to tak, že se označí celý sloupec, podle kterého se má filtrovat a následně po zápisu relačního operátoru (např. znaku rovná se, větší, menší, …) se zapíše požadovaná hodnota či odkaz na požadovanou hodnotu. Třetí argument pokud_prázdné je nepovinný a říká, jaká hodnota se má vrátit, když nejsou v seznamu žádné takové položky.

Na obrázku je ukázáno použití funkce Filter a hned je vidět i výsledek výběru, to znamená, všechny výrobky zelené barvy.

Lze ve funkci Filter použít více kritérií

Pokud je třeba udělat výběr s více kritérii, například zboží typu A zelené barvy, pak lze v argumentu zahrnuje zadat obě podmínky spojené operátorem pro násobení (*). Bude to znamenat a zároveň. Pozor, tyto dvě podmínky musí být uzavřeny v samostatných závorkách.

Jestliže by se mělo jednat o dvě podmínky se vztahem nebo, pak se nepoužije operátor pro násobení, ale sčítání (+). Na obrázku jsou vybírány dvě barvy ze stejného sloupce, ale mohou se vybírat podmínky i v rámci různých sloupců.

A co vnoření funkce

Výběr je určitě užitečný, ale někdy by bylo dobré, kdyby daný výběr byl zároveň nějakým způsobem i seřazený, například podle počtu prodaných kusů. Pak se dá využít toho, že se funkce Filter vloží do funkce Sort . Tím budou pak vybrané záznamy i seřazené podle zvoleného pole. Na obrázku je ukázáno na vybraných položkách z Brna, které jsou seřazeny vzestupně podle počtu prodaných kusů.

Důležité je, že žádné z funkcí nepotřebují absolutní adresy. Je to z toho důvodu, že vlastně existují jenom v jedné buňce a jejich výsledky přesahují do buněk sousedních.

Funkce Unique

Na kartě Data se nachází příkaz Odebrat duplicity, který pomůže vyčistit seznam dat například od omylem vícekrát napsaných položek. Je určitě užitečný, ale někdy je potřeba jen rychle zjistit, která data jsou v určitém sloupci databáze jedinečná. Jen je prostě vypsat z databáze bokem a dál s nimi už nějak pracovat. A k tomu je právě užitečná funkce Unique, která umí vyhledat a vybrat tyto hodnoty a zároveň zajistit, aby se každá hodnota v novém seznamu vyskytovala pouze jednou. Její tvar je:

= UNIQUE(pole; sloupce; právě_jednou),

kde pole je oblast, ve které má funkce hodnoty vyhledávat (většinou se jedná o sloupec). Druhý argument s názvem sloupce určuje, zda se bude pracovat v rámci sloupce (nezadá se nic nebo argument Nepravda) nebo v rámci řádku (zadá se hodnota Pravda).  A třetí argument se nazývá právě_jednou a slouží k zadání toho, zda je třeba vybírat všechny údaje, které se v oblasti nachází alespoň jednou (bez zadání nebo Nepravda), nebo pouze právě jednou (argument Pravda).

Jestliže se předpokládá, že bude v seznamu více hodnot, které by bylo užitečné mít i seřazené, pak lze tuto funkci vložit do již známé funkce Sort.

Jak je to s více sloupci? 

Logicky vznikne otázka, jestli by mohla funkce takto pracovat i s více sloupci současně?  Zde závisí na tom, zda jsou tyto sloupce vedle sebe nebo ne. Jednodušší je, když vedle sebe jsou. Potom stačí jen dané sloupce do argumentu pole zadat a po potvrzení je hned vidět výsledek.

Trochu složitější situace je tehdy, když sloupce spolu nesousedí. Potom je třeba tyto sloupce jakoby sloučit. Dělá se to pomocí znaku ampersand (&). Hledání a výběr unikátní kombinace ze zadaných , například sloupců A a G pak bude mít následující tvar: =UNIQUE(A2:A65 & " " & G2:G65).

Zobrazené údaje se ukážou v tomto případě pohromadě sloučené v jednom sloupci. Ale u vybraných dat se dá opravdu zjistit, že každá jednotlivá kombinace údajů je jedinečná.

Řadit podle
  • Excel v příkladech

    Excel v příkladech

    Co byste říkali knížce o Excelu, ve které by byly většinou jen řešené příklady nebo ukázky využití některých funkcí? Prostě kniha bez zbytečné teorie. Tak se zkuste podívat dál.

    Cena:160.00