Ako používať funkciu odľahlých hodnôt v Exceli

Keď analyzujete údaje, odľahlé hodnoty môžu skresliť výsledky a znížiť ich spoľahlivosť. Ak je napríklad jedna alebo viacero vašich hodnôt podstatne vyššie ako väčšina údajov, znamená to, že priemer je vyšší, čo nemusí odrážať údaje ako celok. Odstránenie týchto odľahlých údajových bodov je preto dôležitým krokom pri vykonávaní spoľahlivých štatistických výpočtov. Bez vyhradenej funkcie odľahlých hodnôt je najjednoduchším spôsobom vykonania testu odľahlých hodnôt v Exceli použiť medzikvartilový rozsah na identifikáciu odľahlých hodnôt, ale na ich aproximáciu môžete použiť aj funkciu Trimmean výsledok.

Medzikvartilový rozsah údajov je rozsah pokrytý „rámčekom“ na štvorcovom grafe alebo viac presne výsledok odčítania hodnoty prvého kvartilu údajov od hodnoty tretieho kvartilu kvartil. Excel obsahuje vstavanú funkciu na výpočet ľubovoľného kvartilu pre vaše údaje. Nájdite náhradnú bunku a napíšte „=QUARTILE([rozsah údajov], [číslo kvartilu])“ a zadajte rozsah buniek pre vaše údaje, kde je napísané „[rozsah údajov]“ a požadovaný kvartil tam, kde je napísané „[kvartil číslo]."

Napríklad, ak máte údaje v bunkách A2 až A101 a chcete hodnotu pre prvý kvartil, zadajte "=QUARTILE(A2:A101, 1)", aby ste našli hodnotu. V prvej časti argumentu môžete myšou zvýrazniť príslušné bunky, ale za čiarkou musíte napísať číslo požadovaného kvartilu. Pre tretí kvartil s rovnakými údajmi napíšete „=QUARTILE(A2:A101, 3)“, aby ste získali výsledok.

Pomocou ďalšej prázdnej bunky odpočítajte hodnotu bunky prvého kvartilu od hodnoty bunky tretieho kvartilu. Ak je prvý kvartil v bunke C2 a tretí kvartil v bunke D2, napíšte "=D2-C2", aby ste získali výsledok. Toto je medzikvartilný rozsah.

Ak chcete nájsť odľahlé hodnoty, môžete teraz použiť medzikvartilový rozsah vo vzorci odľahlých hodnôt, ktorý uvádza, že horná hranica údajov je hodnota tretí kvartil plus 1,5-násobok medzikvartilového rozpätia a spodná hranica je hodnota prvého kvartilu mínus 1,5-násobok medzikvartilu rozsah.

Ak je hodnota prvého kvartilu v C2, hodnota tretieho kvartilu je v bunke D2 a medzikvartilový rozsah je do bunky E2 by ste zadali „=C2-(1,5 * E2)“, aby ste našli dolnú hranicu, a „=D2+(1,5 * E2)“, aby ste našli hornú hranicu limit. Vo všeobecnosti zadáte „=[prvý kvartil] – (1,5 * [medzikvartilový rozsah])“, aby ste našli spodnú hranicu, a „=[tretí kvartil] + (1,5 * [medzikvartilový rozsah])“, aby ste našli hornú hranicu.

Čokoľvek pod dolnou hranicou alebo nad hornou hranicou je odľahlá hodnota.

Na dokončenie testu odľahlých hodnôt v Exceli použite logickú funkciu „ALEBO“ na efektívne zistenie, ktoré hodnoty vo vašej triede údajov sú odľahlé. Zadajte „=ALEBO([údajová bunka]>[horný limit], [údajová bunka]$F$2, A2

Ak je hodnota v A2 nad horným limitom alebo pod dolným limitom, zobrazí sa „TRUE“, čo znamená, že hodnota je odľahlá. Tento vzorec môžete potiahnuť nadol kliknutím na pravý dolný roh bunky so vzorcom a potiahnite ju nadol tak, aby skončila vedľa poslednej dátovej bunky, aby sa v každej vykonal rovnaký výpočet dátový bod.

Môžete tiež zvýrazniť údaje a prejsť na "Podmienené formátovanie" v sekcii "Štýly" na karte "Domov", ak chcete zmeniť formátovanie pre odľahlé hodnoty. Vyberte "Nové pravidlo" a zvýraznite možnosť "Použiť vzorec na určenie, ktoré bunky sa majú formátovať". Zadajte rovnaký vzorec ako v predchádzajúcom odseku a potom kliknite na možnosť „Formátovať“, aby ste vybrali jedinečné formátovanie pre odľahlé hodnoty.

Funkcia „Trimmean“ predstavuje jednoduchší spôsob identifikácie odľahlých hodnôt. Ak chcete použiť funkciu, zadajte „=TRIMMEAN([rozsah údajov], [pomer k orezaniu])“ s rozsahom buniek obsahujúcich údaje namiesto „[rozsah údajov]“ a desatinnou čiarkou percento, ktoré chcete orezať, kde je napísané „[proporcia k orezaniu]“. Tým sa odstránia extrémne hodnoty v hornej a dolnej časti a potom sa na základe nich vypočíta priemer zostávajúce. Ak by ste teda orezali 10 percent, pred výpočtom priemeru by sa odstránilo horných 5 percent a spodných 5 percent.

Ak sa údaje pohybujú od A2 po A101 a chcete orezať extrémnych 5 percent hodnôt, zadajte „=TRIMMEAN(A2:A101, 0,05)“, aby ste našli upravený priemer. Namiesto toho môžete orezať 15 percent tak, že napíšete „=TRIMMEAN(A2:A101, 0,15)“.