Ve videu Jak určit pořadí hodnot v Excelu vám ukáži, jak přiřadit k hodnotám v Excelu správné pořadí. A to za pomocí funkce RANK a její novější verze RANK.EQ. Tyto funkce se skvěle hodí pro určení pořadí, pokud v seznamu nemáte duplicitní záznamy. Pokud ovšem v seznamu máte duplicitní záznamy, tak funkce RANK i funkce RANK.EQ není pro určení pořadí ideální. Ukážeme si jene proč, ale i to, jak to obejít.
Cvičný excelový soubor ke stažení:
Jak určit pořadí hodnot v Excelu
Ve zdrojové tabulce máme několik společností a jejich tržby a naším úkolem je určit u těchto společností pořadí dle výše tržeb. Jelikož v hodnotách tržeb nemáme duplicitní záznamy, tak můžeme použít funkci RANK/RANK.EQ.
Pořadí hodnot v Excelu bez duplicitních hodnot
Pro určení pořadí se v Excelu využívá funkce RANK, respektive RANK.EQ.
Poznámka: Funkce RANK.EQ nahradila funkci RANK v Excelech 2010. Funkce RANK v Excelu zůstává kvůli zajištění kompatibility s excelovými verzemi 2007. Pokud používáte novější Excely, tak doporučuji pro určení pořadí využívat funkci RANK.EQ.
= RANK.EQ(číslo; odkaz; [pořadí])
Funkce RANK.EQ má dva povinné a jeden nepovinný argument. Prvním povinným parametrem je číslo, což je první číslo, pro které chceme určit pořadí. Druhým parametrem funkce je odkaz. Je to trochu nešťastný název, ale nejedná se o nic jiného než o oblast čísel, u kterých určujeme pořadí. Třetí nepovinný argument je typ pořadí. V základu je nastavena nula pro sestupné pořadí. Pokud chceme hodnoty seřadit vzestupně, tak jako parametr pořadí vyplníme jedničku.
Do buňky C4 napíšeme funkci RANK.EQ, kde jako číslo označíme první číslo v tabulce (B4), následně označíme celou oblast čísel, pro která chceme určit pořadí (B4:B14). Jelikož máme v plánu vzorec stahovat směrem dolů, tak nesmíme zapomenout zafixovat klávesou F4 rozpětí buněk $B$4:$B$14. Hodnoty chceme seřadit sestupně, což je základní nastavení, takže parametr pořadí nemusíme vyplňovat. Funkce RANK.EQ vrátila pořadí hodnot.
Funkce RANK.EQ skvěle funguje na tabulku s hodnotami, ve které se nevyskytují duplicitní záznamy. Funkce RANK.EQ vrátí správné pořadí hodnot na základě toho, že po jednotlivých řádcích v tabulce posuzuje, zda je právě označené číslo větší nebo menší než všechna ostatní čísla ve vyznačené oblasti.
Pořadí hodnot v Excelu včetně duplicitních hodnot
Co kdyby se ale ve zdrojové tabulce vyskytovaly duplicitní záznamy? V dalším příkladu máme tabulku se společnostmi, u nichž se výše tržeb opakuje. Pokud bychom na tabulku s duplicitními záznamy použili funkci RANK.EQ, tak jako vidíme na obrázku č.2, tak funkce RANK.EQ u duplicitních záznamů vrátí stejné pořadové číslo. To znamená, že nám některá pořadová čísla budou chybět a některá budeme mít dvakrát.
Pokud si přejete přiřadit k hodnotám pořadí bez přeskakování pořadových čísel, tak k tomu paradoxně funkci RANK.EQ vůbec nepoužijete.
Pro určení pořadí hodnot s duplicitami se použije funkce SOUČIN.SKALÁRNÍ v kombinaci s funkcí COUNTIF.
=SOUČIN.SKALÁRNÍ((První buňka v oblasti buněk<=Celá oblast buněk)/COUNTIF (oblast buněk:oblast buněk))
Vzorec vysvětlím na stejném příkladu. Začneme tím, že označíme první hodnotu v sérii čísel, pro která chceme určit pořadí. V našem případě se jedná o buňku B4. Tuto buňku porovnáme vůči celé sérii buněk ($B$4:$B$14), a zajímá nás, zda je buňka B4 menší nebo rovna celé sérii buněk. Tuto celou podmínku následně vydělíme funkcí COUNTIF, kde je jako oblast i jako kritérium označená celá série buněk ($B$4:$B$14). Nakonec celý tento vzorec zabalíme do funkce SOUČIN.SKALÁRNÍ nebo funkce SUMA. Pokud použijete funkci SUMA, tak starší verze Excelu budou funkci SUMA muset potvrdit kombinací kláves CTRL+SHIFT+ENTER, jelikož se jedná o maticový vzorec. Uživatelům Office 365 stačí potvrdit vzorec klávesu ENTER.
Podrobné vysvětlení vzorce:
Pokud vám použití výše uvedeného vzorce není jasné, tak si to rozložíme po jednotlivých krocích.
Vedle tabulky si ukážeme jednotlivé kroky. Do buňky E4 napíšeme první část vzorce, tedy, že je první buňka v sérii hodnot (B4) menší nebo rovna celé sérii buněk (B4:B14). Starší Excely musí vzorec potvrdit kombinací kláves CTRL+SHIFT+ENTER. Po potvrzení vzorce se na jednotlivých řádcích vrátila série PRAVD a NEPRAVD. Tato série PRAVD a NEPRAVD je z pohledu první buňky B4. Na řádcích, kde máme PRAVDU je splněna podmínka, že číslo na řádku v sérii čísel je větší nebo rovno hodnotě v první buňce. Na řádcích s NEPRAVDOU tato podmínka splněná není.
Druhou část vzorce tvořila funkce COUNTIF. Funkce COUNTIF se klasicky používá k počítání hodnot s podmínkou, využít ji nicméně můžete ale i pro to, abyste zjistili, zda se vám v seznamu vyskytují duplicity. Do buňky F4 tedy napíšeme funkci COUNTIF, kde jako oblast i jako kritérium označíme celou sérii buněk (B4:B14). Starší verze Excelů opět musí funkci potvrdit kombinací kláves CTRL+SHIFT+ENTER. Funkce COUNTIF bude schopná určit, zda se prvně označená hodnota již v seznamu vyskytla. U prvního výskytu vrátí funkce COUNTIF jedničku, u každého dalšího výskytu hodnoty vrátí pořadové číslo. Jak je z tabulky na obrázku č.5 vidět, funkce COUNTIF vrátila u duplicitních záznamů dvojky, u jedinečných záznamů vrátila jedničky.
Nakonec jsme tyto dva vzorce mezi sebou vydělili, tak jak vidíme v buňce G4. Jelikož PRAVDA je v Excelu vždy jedničkou a NEPRAVDA nulou, tak po vydělení dostaneme čísla ve sloupci G4:G14. Říkáte si, že tato čísla nedávají žádný smysl? Pokud teď čísla v buňkách G4:G14 sečteme, dají dohromady číslo 6, což je pořadové číslo tržeb společnosti Tesla. Teď nezbývá nic jiného než tuto funkci zabalit do nějaké agregátní funkce, která sčítá. A to může být buď funkce SUMA, kterou starší Excely potvrdí stisknutím CTRL+SHIFT+ENTER a nebo použijeme funkci SOUČIN. SKALÁRNÍ, která si poradí i s maticovým vzorcem.
Pokud by vás zajímalo, jak můžete k pořadí následně přiřadit všechny hodnoty ze seznamu, tak jako na obrázku č.7, tak se podívejte na navazující bonusové video Jak vyhledávat v seznam s duplicitami.
2 komentáře
Dobrý den,
dalo by se nějak vyřešit pořadí s tím rozdílem, že by se jednalo o pořadí
příklad : 1.místo – 30kč , 1.místo – 30kč, ale pak by následovalo 3.místo – 20kč (je to vlastně třetí položka)
nebo kdyby byly na prvním místě 4 shodné hodnoty, tak by následovalo 5.místo
Děkuji.
Milan
Dobrý den, už jsem bezradný, na začátku videa ukazujete jak udělat to pořadí tak jsem to dělal podle vás =RANK.EQ($A$1;$A$1:$A$13) ale místo pořadí 1-13 mi to všude píše 1. Moc prosím o radu, předem děkuji, s pozdravem p.Boršík