V dnešním prvním videu z minisérie „Revoluce v Excelu aneb jak dynamická pole změnila práci v Excelu“ se podíváme na první srovnání, jak se zapeklité úkoly řeší ve verzích Excelu bez dynamických polí a jak se naopak řeší v nových Excelech, které mají dynamická pole a nové funkce. A začneme prvním úkolem, který se ve starých Excelech velmi obtížně řeší. A tím problémem je řazení hodnot v tabulce pomocí excelových funkcí. Tedy situaci, kdy chceme hodnoty v tabulce seřadit od nejvyšší hodnoty po nejnižší nebo od nejnižší hodnoty po nejvyšší.
Excelový soubor ke stažení
Revoluce v Excelu – Řazení hodnot
Ukážeme si, jak hodnoty seřadit nejprve ve starých Excelech, pomocí excelových funkcí a následně to srovnáme s řešením v nových dynamických Excelech v Microsoft 365.
Jedním z největších problémů v Excelu vždy bylo, jak seřadit hodnoty v tabulce pomocí excelových funkcí. Samozřejmě máme několik různých způsobů, jak můžeme hodnoty v tabulce seřadit. Můžeme do tabulky pomocí CTRL+SHIFT+L vložit automatický filtr, který vloží filtr do záhlaví, pomocí kterého můžeme mimo jiné řadit hodnoty v tabulce. Co když ale potřebujeme hodnoty seřadit dynamicky a z nějakého důvodu třeba nemůžeme změnit pořadí hodnot ve zdrojové tabulce?
Asi nejjednodušším způsobem, jak hodnoty seřadit je použít pomocné sloupce. V tabulce máme hodnoty, které potřebujeme seřadit od nejvyšší hodnoty po nejnižší. Nejprve úkol vyřešíme pomocí excelových funkcí, které jsou dostupné ve starších verzích Excelu, tedy před licencí Microsoft 365.
Nejprve musíme určit pořadí hodnot v seznamu, následně musíme pomocí vyhledávacích funkcí přiřadit pozici první, druhé, třetí hodnotě v seznamu a následně k tomu přiřadit správnou hodnotu.
Prvním pomocným sloupcem je určení pořadí hodnot. Pořadí hodnot v rámci seznamu určíme pomocí funkce RANK.EQ, ve funkci RANK.EQ nejprve označíme první hodnotu, u které chceme určit pořadí, tedy první hodnotu v seznamu a v parametru odkaz označíme celý sloupec hodnot. Jelikož chceme funkci stahovat dolů, tak musíme buňky správně zafixovat klávesou F4. První buňku fixovat nemusíme, ale musíme zafixovat rozpětí buněk, a to plně. Teď můžeme funkci stáhnout dolů. Funkce RANK.EQ přiřadila každé hodnotě pořadí v rámci seznamu.
Ve druhém pomocném sloupci musíme ručně doplnit pořadí hodnot od jedničky do koncového čísla, v tabulce máme osm řádků, tedy osm hodnot, takže doplníme hodnoty od jedné do osmičky. Na prvním místě chceme mít nejvyšší hodnotu, tedy pozici jedna podle funkce RANK.EQ, na druhém místě v tabulce chceme mít druhou nejvyšší hodnotu podle pozice RANK.EQ a tak dále. Kdybychom chtěli hodnoty seřadit obráceně, tedy od nejnižší po nejvyšší, tak začneme od pozice osm a budeme pokračovat k jedničce, tím pádem bude na prvním řádku osmé nejvyšší číslo a na posledním řádku bude první nejvyšší číslo.
Ve třetím pomocném sloupci musíme teď k jednotlivým pořadovým číslům přiřadit pořadová čísla řádků. K tomu využijeme vyhledávací funkci POZVYHLEDAT, anglicky funkce MATCH, která najde hledanou hodnotu a vrátí pořadové číslo řádku, na kterém se hodnot nachází. Funkce POZVYHLEDAT, kde nejprve označíme co hledáme. Hledáme první pozici ve druhém pomocném sloupci, a kde tuto hodnotu hledáme? Hledáme ji ve sloupci pořadí, které nám určila funkce RANK.EQ. A hledáme přesnou shodu, takže nulu. Ještě musíme správně zafixovat buňky. Opět musíme plně zafixovat rozpětí buněk ve sloupci hodnot. Funkci ukončíme a potvrdíme a pošleme ji dolů. Teď víme, že nejvyšší číslo se nachází na pátém řádku zdrojové tabulky. Druhé nejvyšší číslo je na šestém řádku atd.
V posledním sloupci přiřadíme k řádkům hodnoty. K tomu poslouží funkce INDEX. Napíšeme funkci INDEX, kde nejprve označujeme hodnoty, které má funkce INDEX vrátit, což jsou čísla ze zdrojové tabulky, rovnou je plně zafixujeme klávesou F4. V parametru řádek označíme pořadová čísla řádků z třetího pomocného sloupce. Funkci ukončíme, potvrdíme a pošleme ji dolů. Teď máme hodnoty seřazené od nejvyšší po nejnižší.
Pokud změníme nějaké číslo ve zdrojové tabulce, tak se hodnoty automaticky seřadí.
A jak to vyřeší uživatelé Microsoft 365, kteří mají přístup k dynamickým funkcím?
Pro seřazení hodnot použijeme funkci SORT. Ve funkci SORT se označuje pole hodnot, které chceme seřadit, tedy zdrojový seznam. Máme pouze jeden sloupec hodnot, takže druhá parametr funkce nepotřebujeme a ve třetím parametru si vybereme, zda chceme hodnoty seřadit sestupně nebo vzestupně. My chceme sestupně, takže vybereme -1. Funkci potvrdíme. Hotovo. Vypínáme stopky. Nejen, že ve funkci SORT nemusíte fixovat buňky, ale funkce se rovnou rozlije do všech buněk, je plně dynamická, takže reaguje na jakékoliv změny ve zdroji a jak vidíte, tak si hravě poradí i s duplicitami.
Jedna odpověď
Poznámka: V sešitu ke stažení nejsou stejné “Hodnoty” na jednotlivých listech.