Pokročilé tipy a triky funkce Excel

Úvodem do pokročilých tipů

Jak si promrskat znalost funkcí než použít na pokročilejších dotazech. Když jsem viděl že v Google tabulkách jsou funkce jako SORT, FILTER, UNIQUE chtěl jsem je mít v Excel. Než se do Excel dostanou (bohužel stále nejsem ten šťastný, co už je může v testovat) vrátil jsem se k poznámkám a prostě si tyto funkce vytvořil využitím již dostupných funkcí.

Prakticky ukáži jak na řazení a filtrování využít funkce. Pro přehlednost a možnost článek rozšířit případně doplnit je opět rozdělen do kapitol:

• Řazení -
• Filtrování - klasicky
• Filtrování - maticově
• Unikátní hodnoty - klasické vzorce 1
• Unikátní hodnoty - klasické vzorce 2
• Unikátní hodnoty - maticový vzorec

Poznámky

Vím, že do Excel se chystají nové funkce (a odkazování a chybové hodnoty), jen zatím nemám štěstí a nejsem vybrán do testování.

Vím, že se dají využít filtry, kontingenčky (pivotky), odebírat duplicity, ale chtěl jsem si vyzkoušet pomocí funkcí.

Vím, řešit se dá i elegantněji, jen jsme na to nepřišel, možná časem dolním i další řešení nebo rovnou dám odkaz na řešení pomocí příslušných funkcí.

Řazení pomocí funkcí

Pojďme si ukázat prakticky.

Data

V úvodu si úkol na setřídění dat mírně zjednodušíme, potřebujeme seřadit data podle velikosti platu (mzdy). V ukázkovém příkladu využívám zjednodušení, že všechny hodnoty platu jsou unikátní.

Jak na to?

Využijeme funkce RANK, která setřídí data (nebo-li sloupec plat) od nejvyššího po nejnižší (tím získáte čísla). Následně využijeme pomocný sloupec, kde je pouze číselná řada od 1 do x (pokud chcete sestupně, stačí řadu vytvořit od x do 1) - pokud ji nechcete tvořit ručně, lze využít funkci ŘADEK()-x , kde x zajistí, že čísla v řadě budou od 1 .

Přes POZVYHLEDAT (MATCH) přiřadíte příslušný řádek ze skutečného pořadí (neboli, již víte na kterém řádku se nachází maximální plat, atd.). Následně využitím INDEX přiřadíte příslušnou položku (pokud se vám nelíbí funkce INDEX lze využít i funkci POSUN v angličtině OFFSET).

=RANK(D13;$D$13:$D$17) 1 =POZVYHLEDAT(H13;$G$13:$G$17;0) =INDEX($B$13:$B$17;I13) =INDEX($C$13:$C$17;I13) =INDEX($D$13:$D$17;I13) ' pokud se nelíbí funkce INDEX =POSUN($D$12;I13;0)

Jak na to?

Výsledek

Poznámka

Pro stejné platové hodnoty si můžete sloupec modifikovat sami.

Filtrování - klasická funkce

K dispozici máte tabulku fiktivních osob, kdy znáte jejich jméno, pohlaví a počet bytů. Pro ukázku potřebujete vybrat jen ženy, neboli záznamy, které mají ve sloupci pohlaví písmeno F.

Jak na to?

Nejprve zjistíme, zda je daná osoba, žena (a pokud je tak vypíšu číslo řádku na kterém se nachází). Následně si pomohu číselnou řadou od 1 do x (pro jednoduchost jsem použil pouze čísla, ale můžete využít i funkci ŘADEK(). Následně si pomocí funkce SMALL seřadím (využívám skutečnosti, že NEPRAVDA je vlastně nula a projeví se ve funkci SMALL jako chyba). Takto mám setříděny řádky na kterých se nacházejí ženy od nejnižšího po nejvyšší. Zbytek je hračka, pomocí funkce INDEX (případně posun v EN OFFSET) přiřadím jméno a další záznamy z daného řádku. Pokud budete chtít ještě ošetřit to, že na posledních řádcích je chybová hodnota (už nejsou záznamy, které odpovídají hledané podmínce) jednoduše využijete funkci IFERROR. Máte hotovo.

=KDYŽ(B2="F";ŘÁDEK(B2)-1) 1 =SMALL($I$2:$I$8;J2) =INDEX($A$2:$A$8;K2) =IFERROR(L2;"")

Výsledek

Možný výsledek

Filtrování - maticové funkce

Pokud chcete vložit vzorec do jedné funkce za využití maticového vzorce. Předpokládám, že víte jak matice pracují, že pro vkládání je potřeba zadat klávesovou zkratku Ctrl + Shift + Enter.

Jak na to?

Pro sloupec Jméno se využije následující maticový vzorec a je hotovo.

=KDYŽ(ŘÁDKY(E$2:E2)>COUNTIF($B$2:$B$8;"F");"";INDEX(A$2:A8;SMALL(KDYŽ($B$2:$B$8="F";ŘÁDEK($B$2:$B$8)-ŘÁDEK($B$1));ŘÁDKY(E$2:E2))))

Pro ostatní sloupce obdobně.

Výsledek pro maticový vzorec

Možný výsledek

Unikátní hodnoty - klasické vzorce 1

Pro ruční výpočet (získání) unikátních /jedinečných/ hodnot, když funkci UNIQUE zatím v Excelu zatím nemáte a nechcete použít klasický filtr s využitím podmíněného formátování na duplicitní hodnoty, nebo odebírat duplicity.

Můžete využít funkce KDYŽ, SMALL, COUNTIF(S), INDEX (případně POSUN).

Data

Jak na to?

Přes KDYŽ a COUNTIF(S) zjistím, zda je položka unikání, pokud ano vypíši, následně řádek na kterém se položka nachází, jinak nic. Využiji číslenou řadu 1 až x. Přes SMALL seřadím od nejmenšího. Tím pádem mám řadky pěkně za sebou a mohu přes INDEX nebo přes POSUN (OFFSET v angličtině) přiřadit jméno.

=KDYŽ(COUNTIF($B$5:B5;B5)>1;"";B5) =KDYŽ(D5<>"";ŘÁDEK();"") 1 =SMALL($E$5:$E$11;F5) =INDEX($B$5:$B$11;G5-4) 'nebo místo INDEX =POSUN($B$4;G5-4;0)

Ukázka pro angličtinu

=IF(COUNTIF($B$5:B5;B5)>1;"";B5) =IF(D5<>"";ROW();"") 1 =SMALL($E$5:$E$11;F5) =INDEX($B$5:$B$11;G5-4) ' nebo =OFFSET($B$4;G5-4;0)

Výsledek pro klasicky vzorec 1

Možný výsledek

Unikátní hodnoty - klasické vzorce 2

Pro ruční výpočet (získání) unikátních /jedinečných/ hodnot, můžete využít funkce VVYHLEDAT a COUNTIF(S).

Data

Jak na to?

Využít funkce COUNTIF, VVYHLEDAT a ošetřit s IFERROR:

=IFERROR(VYHLEDAT(2;1/(COUNTIF($I$4:I4;$B$5:$B$11)=0);$B$5:$B$11);"")

Výsledek pro klasicky vzorec

Možný výsledek

Unikátní hodnoty - maticový vzorec

Pokud máte rádi maticové vzorce, víte že je potřeba stisk Ctrl+ Shift + Enter:

=INDEX($B$5:$B$11;POZVYHLEDAT(0;COUNTIF($D$4:D4;$B$5:$B$11);0))

Doplněno o ošetření chyb:

=IFERROR(INDEX($B$5:$B$11;POZVYHLEDAT(0;COUNTIF($F$4:F4;$B$5:$B$11);0));"") případně v angličtině =IFERROR(INDEX($B$5:$B$11;MATCH(0;COUNTIF($F$4:F4;$B$5:$B$11);0));"")

Nebo i podobná šílenost, které je k dohledání v různých nápovědách ;) Spíše pro inspiraci a přemýšlení.

=INDEX($B$3:$B$15; POZVYHLEDAT(0; ČETNOSTI(KDYŽ(STEJNÉ($B$3:$B$15; TRANSPOZICE($D$2:D2)); POZVYHLEDAT(ŘÁDEK($B$3:$B$15); ŘÁDEK($B$3:$B$15)); ""); POZVYHLEDAT(ŘÁDEK($B$3:$B$15); ŘÁDEK($B$3:$B$15))); 0))

Výsledek pro maticový vzorec

Možný výsledek v poznámce jsou funkce.

Závěrem

Máte nějaký tip? Vylepšení můžete zmínit v komentářích.

Článek byl aktualizován: 19.09.2020 10:57

Pavel Lasák - autor webu

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ů.

           

Komentáře zatím nejsou

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