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

Jste zde: Úvodní stránka » excel » funkce » pokrocile-tipy-triky-funkce-Excel
Microsoft Excel logo

Pokročilé tipy a triky funkce Excel

Videokurzy Excel

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

Netradiční použití funkcí v Excel na řazení, filtrování, jedinečné hodnoty

Aneb síla spojení.

Ú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:

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í

Než budou fungovat v Excel 2019 (Office 365) nové funkce SORT, potažmo SORTBY, můžete k setřídění dat stále využívat funkce, které v Excel již jsou. RANK spolu s POZVYHLEDAT a INDEX (případně POSUN).

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

Excel funkce RANK - data

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) Excel funkce RANK - funkce data

Jak na to?

Výsledek

Excel funkce RANK - vysledek řazení

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.

Excel funkce pro filtrování

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;"") Excel funkce pro filtrování - ukázka

Výsledek

Možný výsledek

Excel funkce pro filtrování - řešení

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

Excel funkce pro filtrování - řešení

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

Excel funkce UNIQUE - základ

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) Excel funkce unikáty pomocí funkcí bez UNIQUE - základ

Výsledek pro klasicky vzorec 1

Možný výsledek

Excel funkce pro unikáty bez funkce UNIQUE - řešení

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

Excel funkce UNIQUE - základ

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);"") Excel funkce UNIQUE - základ

Výsledek pro klasicky vzorec

Možný výsledek

Excel funkce pro unikaty - řešení

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.

Excel funkce pro unikátny - funkce použití

Závěrem

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

Článek byl aktualizován: 06.10.2018 09:41

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


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 - 2018 | 1121

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