V dnešním pokračování z minisérie „Revoluce v Excelu“ si porovnáme, jak se v Excelu vyhledává s duplicitami. Srovnáme si způsob, jak se vyhledává v seznamu, ve kterém máme duplicity v Excelech, které nemají přístup k dynamickým polím a funkcím a následně si ukážeme, jak se stejný úkol vyřeší v Excelech Microsoft 365.
Excelový soubor ke stažení
A jako vždy začneme nejprve řešením v Excelech před dynamickými poli. V příkladu máme tabulku, kde máme pracovníky a pobočky. Naším úkolem je vyselektovat do sloupce jména pracovníků podle pobočky, kterou vybereme. Na každé pobočce pracuje více lidí, takže nemůžeme použít klasické vyhledávací funkce.
Funkci SVYHLEDAT nemůžeme použít už jen proto, že se hledané hodnoty nacházejí nalevo od sloupce s pobočkami. A kdybychom použili vyhledávací duo INDEX & POZVYHLEDAT, tak dostaneme nesprávný výsledek. Všude by se vrátil první pracovník, jelikož stejně jako SVYHLEDAT a XLOOKUP, tak ani INDEX & POZVYHLEDAT neumí vyhledávat v seznamu s duplicitami.
Nejprve musíme identifikovat, na kterých řádcích se vyskytuje pobočka, kterou hledáme. V našem případě tedy, na kterých řádcích zdrojové tabulky je Praha. Napíšeme rovná se a označíme sloupec s pobočkami, který zafixujeme klávesou F4. Napíšeme rovná se porovnáme ji s pobočkou ve výběru. Jelikož budeme funkci stahovat dolů, tak tuto buňku musíme rovněž plně zafixovat klávesou F4.
Zkontrolujeme vzorec klávesou F9 a vidíme, že vzorec vrací sérii pravd a nepravd. Na řádcích, kde je Praha je pravda a na ostatních řádcích je nepravda.
Jelikož celou funkci následně budeme balit do funkce AGGREGATE, tak musíme vzorec vydělit jedničkou, aby se na řádcích s nepravdami vrátila chybová hláška. Funkce AGGREGATE totiž umí ignorovat řádky s chybovými hláškami.
Když vzorec označíme a zmáčkneme klávesu F9, tak se zobrazí jednička na řádcích, kde je pobočka Praha a na ostatních řádcích se zobrazí chybová hláška.
Teď to celé musíme vynásobit kombinací funkcí ŘÁDEK, které každému pravdivému řádku přiřadí pořadové číslo řádku. Takže to vynásobíme, závorka a funkce ŘÁDEK, kde označíme řádky ve zdrojové tabulce, plně zafixované a od toho odečteme funkci ŘÁDEK, kde označím buňku záhlaví, plně zafixovanou.
Když to celé označíme a zmáčkneme F9, tak vidíme, že na řádcích s pravdou jsou pořadová čísla řádků a na řádcích s nepravdou jsou stále chybové hlášky.
Teď to můžeme celé zabalit do funkce AGGREGATE. Ve funkci AGGREGATE vybereme jako funkci SMALL, tedy 15, a chceme ignorovat chybové hlášky, takže šest. Matice je celá funkce a ještě musíme vyplnit parametr k, což je funkce ŘÁDKY, kde vytvoříme dynamické rozpětí z buněk, ve které právě jsme. Přičemž první buňku musíme plně zafixovat. Funkce AGGREGATE je hotová.
Potvrdíme ji a stáhneme dolů Teď máme správně vrácená pořadová čísla řádků, na kterých se vyskytuje Praha.
Teď stačí k řádkům přiřadit jména pomocí funkce INDEX. Takže to zabalíme do funkce INDEX, kde označíme, co chceme vrátit. To jsou jména pracovníků a parametr řádky je celá funkce AGGREGATE.
A jelikož víme, že dole zůstávají chybové hlášky, tak to zabalíme do funkce IFERROR, kde udáme, že v případě chyby se má vrátit nic. Teď je funkce celá, takže ji ukončíme a potvrdíme. Teď máme seznam pracovník z Prahy. Pokud změníme výběr pobočky, tak se změní i seznam pracovníků.
Takto se vyhledávalo s duplicitami před Microsoft 365. A jak si s úkolem poradí ti, co Excel v rámci Microsoft 365 využívají a mají tedy přístup k dynamickým polím a funkcím?
Použijeme funkci FILTER, v parametru pole označíme hodnoty, které chceme filtrovat, což jsou pracovníci. A v parametru zahrnuje je logická podmínka, kde označíme sloupec s pobočkami, rovná se a vybraná pobočka. Ukončíme funkci a potvrdíme. Hotovo. Vypínáme stopky. Jelikož je funkce FILTER dynamická, tak v ní buňky nemusíme fixovat, jelikož se funkce sama rozlije do potřebných buněk. Funkce je rovněž plně dynamická, takže pokud změníme pobočku, tak se funkce samozřejmě přizpůsobí.
2 komentáře
👍😊
Funkci AGGREGATE jsem neznal. Je boží díky moc.
Bohužel excel 365 nemám, tak si můžu nechat o dynamických funkcích jen zdát. Respektive na NTB mám novější excel (ne 365), kde nějaké dynamické funkce jsou. Doma vyzkouším, jestli je mezi nimi i filter.