Jak zvýraznit data na základě procentuálního pořadí v Excelu

Percentilové pořadí označuje postavení konkrétní hodnoty dat vzhledem k ostatním hodnotám dat ve vzorku. Percentily se nejvíce používají při určování výkonu ve standardizovaných testech. Pokud dítě dosáhne ve standardizovaném testu v 90. percentilu, znamená to, že jeho skóre je vyšší než 90 procent ostatních dětí, které test absolvovaly.

Jiný způsob, jak se na to podívat, je říci, že skóre dítěte je v prvních 10 procentech všech dětí, které test absolvovaly.

Percentily se často používají v analýze dat jako metoda měření výkonu subjektu ve vztahu ke skupině jako celku – například určení percentilového pořadí pro každého zaměstnance na základě ročního příjmu.

V Excelu můžete snadno získat klíčové percentilové pozice pomocí funkce PERCENTILE. Tato funkce vyžaduje dva argumenty: rozsah dat a percentilové skóre, které chcete vidět.

V zobrazeném příkladu je hodnota v buňce D7 výsledkem následujícího vzorce, který vytáhne 75. percentil na základě dat v rozsahu B3:B20:

=PERCENTIL($B$3:$B$20,0,75)

Tento vzorec vám říká, že každý zaměstnanec s příjmem nad 52 651 USD patří mezi 75 procent nejlepších.

Hodnota v buňce D16 je výsledkem následujícího vzorce, který získá 25. percentil na základě dat v rozsahu B3:B20:

=PERCENTIL($B$3:$B$20,0,25)

Tento vzorec vám říká, že každý zaměstnanec s příjmem nižším než 24 656 USD patří mezi spodních 25 procent výkonných pracovníků.

Pomocí těchto značek percentilu tento příklad použije podmíněné formátování, takže jakákoli hodnota v 75. percentilu bude zbarvena zeleně a jakákoli hodnota v 25. percentilu bude zbarvena červeně.

Chcete-li vytvořit toto základní pravidlo formátování, postupujte takto:

Vyberte datové buňky v cílovém rozsahu (v tomto příkladu buňky B3:B20), klikněte na kartu Domů na pásu karet Excelu a poté vyberte Podmíněné formátování → Nové pravidlo.

Otevře se dialogové okno Nové pravidlo formátování.

V seznamu v horní části dialogového okna klikněte na možnost Použít vzorec k určení buněk k formátování.

Tento výběr vyhodnotí hodnoty na základě vámi zadaného vzorce. Pokud se určitá hodnota vyhodnotí jako TRUE, podmíněné formátování se použije na tuto buňku.

Do pole pro zadání vzorce zadejte vzorec zobrazený v tomto kroku.

V tomto vzorci vyhodnotíte, zda jsou data v cílové buňce (B3) v rámci 25. percentilu. Pokud ano, použije se podmíněné formátování.

=B3<>

Všimněte si, že ve vzorci vyloučíte absolutní referenční symboly dolaru ($) pro cílovou buňku (B3). Pokud místo psaní odkazu na buňku klepnete na buňku B3, Excel automaticky nastaví odkaz na buňku jako absolutní. Je důležité, abyste do cílové buňky nezahrnuli absolutní referenční symboly dolaru, protože k použití tohoto pravidla formátování na základě vlastní hodnoty každé buňky potřebujete Excel.

Klepněte na tlačítko Formát.

Otevře se dialogové okno Formát buněk, kde máte úplnou sadu možností pro formátování písma, ohraničení a výplně pro cílovou buňku. Po dokončení výběru možností formátování potvrďte změny kliknutím na tlačítko OK a vraťte se do dialogového okna Nové pravidlo formátování.

Zpět v dialogovém okně Nové pravidlo formátování potvrďte pravidlo formátování kliknutím na tlačítko OK.

V tomto okamžiku byste měli být v dialogovém okně Správce pravidel podmíněného formátování.

Klepněte na tlačítko Nové pravidlo.

Otevře se dialogové okno Nové pravidlo formátování.

V seznamu v horní části dialogového okna klikněte na možnost Použít vzorec k určení buněk k formátování. Tento výběr vyhodnotí hodnoty na základě vámi zadaného vzorce. Pokud se určitá hodnota vyhodnotí jako TRUE, pak se na tuto buňku použije podmíněné formátování.

Do pole pro zadání vzorce zadejte zde zobrazený vzorec.

V tomto vzorci hodnotíte, zda jsou data v cílové buňce (B3) v rámci 75. percentilu. Pokud ano, použije se podmíněné formátování.

=B3>=PERCENTIL($B$3:$B$20,0,75)

Klepněte na tlačítko Formát.

Otevře se dialogové okno Formát buněk, kde máte úplnou sadu možností pro formátování písma, ohraničení a výplně pro cílovou buňku. Po dokončení výběru možností formátování potvrďte změny kliknutím na tlačítko OK a vraťte se do dialogového okna Nové pravidlo formátování.

Zpět v dialogovém okně Nové pravidlo formátování kliknutím na tlačítko OK potvrďte pravidlo formátování.

Pokud potřebujete upravit pravidlo podmíněného formátování, jednoduše umístěte kurzor do libovolné datové buňky ve formátovaném rozsahu a poté přejděte na kartu Domů a vyberte Podmíněné formátování → Spravovat pravidla. Otevře se dialogové okno Správce pravidel podmíněného formátování. Klikněte na pravidlo, které chcete upravit, a poté klikněte na tlačítko Upravit pravidlo.