Jak zvýraznit statistické odlehlé hodnoty v Excelu

Při provádění analýzy dat obvykle předpokládáte, že se vaše hodnoty seskupují kolem nějakého centrálního datového bodu (medián). Někdy však některé hodnoty spadají příliš daleko od centrálního bodu. Tyto hodnoty se nazývají odlehlé hodnoty (leží mimo očekávané rozmezí). Odlehlé hodnoty mohou zkreslit vaše statistické analýzy a vést vás k nesprávným nebo zavádějícím závěrům o vašich datech.

Ke zvýraznění odlehlých hodnot ve vašich datech můžete použít několik jednoduchých vzorců a podmíněného formátování.

Prvním krokem při identifikaci odlehlých hodnot je určení statistického středu rozsahu. Chcete-li provést toto přesné určení, začněte nalezením 1. a 3. kvartilu. Kvartil je statistická rozdělení souboru dat do čtyř stejných skupin, přičemž každá skupina tvoří 25 procent dat. Horních 25 procent kolekce je považováno za 1. kvartil, zatímco spodních 25 procent je považováno za 4. kvartil.

V Excelu můžete snadno získat kvartilové hodnoty pomocí funkce QUARTILE. Tato funkce vyžaduje dva argumenty: rozsah dat a požadované kvartilové číslo.

V zobrazeném příkladu jsou hodnoty v buňkách E3 a E4 1. a 3. kvartil pro data v rozsahu B3:B20.

Vezmeme-li tyto dva kvartily, můžete vypočítat statistických 50 procent souboru dat odečtením 3. kvartilu od 1. kvartilu. Těchto statistických 50 procent se nazývá mezikvartilní rozsah (IQR). Obrázek 9-18 zobrazuje IQR v buňce E5.

Nyní je otázkou, jak daleko od středních 50 procent může hodnota sedět a stále být považována za „přiměřenou“ hodnotu? Statistici se obecně shodují, že IQR*1,5 lze použít k vytvoření rozumného horního a dolního plotu:

Dolní plot se rovná 1. kvartilu – IQR*1,5.

Horní plot se rovná 3. kvartilu + IQR*1,5.

Jak vidíte, buňky E7 a E8 vypočítávají konečný horní a dolní plot. Jakákoli hodnota větší než horní ohraničení nebo menší než dolní ohraničení je považována za odlehlou hodnotu.

V tomto okamžiku lze pravidlo podmíněného formátování snadno implementovat.

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ě vzorce, který zadáte. 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 zde zobrazený vzorec.

Všimněte si, že pomocí funkce OR porovnáte hodnotu v cílové buňce (B3) s horním a dolním ohraničením nalezeným v buňkách $E$7 a $E$8. Pokud je cílová buňka větší než horní ohraničení nebo menší než dolní ohraničení, považuje se za odlehlou, a proto bude vyhodnocena jako PRAVDA a spustí se podmíněné formátování.

=OR(B3<$e$8,b3>$E$7)

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.

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.