V dnešním videu se podíváme na to, jak můžeme k hodnotám v Excelu přiřadit pořadí. Háček je ovšem v tom, že u shodných hodnot nechceme zobrazit stejné pořadové hodnoty, ale rozmezí hodnot oddělené pomlčkou. Takže pokud máme shodnou hodnotu tak místo toho, aby u těchto hodnot byla shodně dvojka, tak u nich chceme uvést rozmezí 2-3, stejně tak u dalších hodnot nechceme shodné páté místo, ale rozmezí 5-7. Jak tohoto zobrazení pořadí dosáhnout si ukážeme v tomto videu.
Excelový soubor ke stažení:
V příkladu máme několik studentů a jejich počet bodů z testu. Naším úkolem je přiřadit ke studentům pořadí, podle toho, jak se umístili s výsledkem testu. Máme ovšem speciální požadavek, a to je ten, že pokud mají někteří studenti shodný počet bodů, tak u nich chceme uvést rozmezí pořadí oddělené pomlčkou, tak jako to máme v příkladu.
Abychom dosáhli požadovaného zobrazení, tak budeme muset vytvořit několik pomocných sloupců. První hodnotu z rozmezí určíme jednoduše, a to pomocí funkce RANK, pomocí které můžeme určovat pořadí hodnot. Nejprve pomocí funkce RANK zjistíme pořadí podle výsledku testu. A aby se nám výsledek lépe kontroloval, tak máme studenty v tabulce rovnou seřazené podle výše testu.
Takže v prvním pomocném sloupci nejprve určíme pořadí pomocí funkce RANK.EQ. V této funkci označíme první hodnotu testu a porovnáme ji vůči všem hodnotám všech studentů. Jelikož budeme funkci stahovat směrem dolů, tak hodnoty zafixujeme klávesou F4. Funkci potvrdíme a stáhneme ji dolů. Funkce vrátila pořadí hodnot. U shodných hodnot vrátila funkce stejné pořadové číslo a následné pořadové číslo přeskočila. Takže zde máme dva testy s pořadím dva a třetí pořadí je přeskočené a následuje čtvrté atd. Tato čísla jsou základem pro rozpětí hodnot před pomlčkou.
Háček je v tom, jak vyřešit, aby se za pomlčkou zobrazilo správné pořadové číslo. V podstatě jde o to, že pokud je pořadové číslo jedinečné, tak chceme vrátit toto pořadové číslo. Pokud se ale pořadové číslo opakuje, tak potřebujeme vrátit nejen rozpětí, ale abychom vrátili správné hodnoty rozpětí, tak musíme vědět, kolikrát se dané pořadové číslo opakuje.
K tomu vytvoříme další pomocný sloupec.
V dalším pomocném sloupci spočítáme, kolikrát se stejné pořadí vyskytuje v tomto pomocném sloupci. To uděláme pomocí funkci COUNTIF. Ve funkci COUNTIF spočítáme výskyt duplicit tak, že nejprve označíme všechny hodnoty, které zafixujeme klávesou F4 a následně jako kritérium označíme první hodnotu. Funkci ukončíme a pošleme ji dolů pro všechny řádky a tato funkce spočítala, kolikrát se dané pořadí vyskytuje v pomocném sloupci.
K čemu je nám tento pomocný sloupec dobrý si ukážeme za minutku. Nejprve vytvoříme poslední pomocný sloupec. A v tomto pomocném sloupci sečteme hodnoty z předchozích dvou pomocných sloupců a odečteme jedničku. Když tento vzorec pošleme dolů, tak se nám vrátí správné druhé pořadové číslo, které chceme za pomlčkou. Právě druhý pomocný sloupec, ve kterém jsme spočítali počet duplicit zajistil, že jsme po odečtení jedničky správně identifikovat druhé pořadové číslo.
A teď stačí vytvořit finální sloupec s pořadím. V tomto sloupci použijeme podmínkovou funkci KDYŽ neboli anglicky funkci IF. Ve funkci KDYŽ ověříme podmínku. Podmínka je, že pokud je hodnota duplicity ve druhém pomocném sloupci jedna, tak to znamená, že se jedná o jedinečné pořadí a v takovém případě u pořadí nebude pomlčka, takže v takovém případě chceme vrátit hodnotu z prvního pomocného sloupce, které jsme vrátili pomocí funkce RANK.EQ.
A pokud podmínka splněná nebude, tak chceme vrátit rozmezí s pomlčkou. První hodnota pořadí je opět hodnota, kterou vrátila funkce RANK.EQ, následuje pomlčka s mezerami v uvozovkách a jako druhá hodnota je hodnota z posledního pomocného sloupce. Ukončíme funkci KDYŽ a pošleme ji dolů. A teď máme správné pořadí, přičemž u shodných výsledků testu máme pořadí oddělené pomlčkami.
Pomocné sloupce můžeme ve finální tabulce skrýt a zůstane nám viditelný pouze sloupec s rozpětím hodnot.
