Az Outliers függvény használata (és miért) az Excelben

A kiugró érték olyan érték, amely lényegesen magasabb vagy alacsonyabb, mint az adatokban szereplő legtöbb érték. Amikor az Excel-t használja az adatok elemzésére, a kívülállóak torzíthatják az eredményeket. Például egy adatkészlet átlagos átlaga valóban tükrözi az Ön értékeit. Az Excel néhány hasznos funkciót kínál a kiugró értékek kezeléséhez, ezért nézzük meg.

Egy gyors példa

Az alábbi képen a kivételeket meglehetősen könnyű észrevenni - kettő értéke Ericnek és 173 értéke Ryannek van rendelve. Egy ilyen adatkészletben elég könnyű manuálisan észlelni és kezelni ezeket a kiugró értékeket.

Nagyobb adatsorban ez nem így lesz. Fontos, hogy azonosítsuk a kiugró értékeket és eltávolítsuk őket a statisztikai számításokból - és ezt fogjuk megvizsgálni ebben a cikkben.

Hogyan találhatunk ki kiemelkedő adatokat az adatokból

A kiugró értékek megtalálásához az adatkészletben a következő lépéseket tesszük:

  1. Számolja ki az 1. és a 3. kvartilit (csak egy kicsit beszélünk ezekről).
  2. Értékelje az interkvartilis tartományt (ezeket kicsit lejjebb elmagyarázzuk).
  3. Adja vissza adattartományunk felső és alsó határait.
  4. Ezekkel a határokkal azonosíthatja a külső adatokat.

Az alábbi képen látható adatsor jobb oldalán található cellatartományt ezen értékek tárolására használják.

Kezdjük el.

Első lépés: Számítsa ki a kvartiliseket

Ha adatait negyedekre osztja, akkor ezeket a halmazokat kvartilisnek nevezzük. A tartomány legalacsonyabb 25% -a az 1. kvartilit, a következő 25% a 2. kvartilit alkotja, és így tovább. Ezt a lépést először azért tesszük meg, mert a legszélesebb körben használt outlier definíció olyan adatpont, amely több mint 1,5 interkvartilis tartomány (IQR) az 1. kvartilis alatt, és 1,5 interkvartilis tartomány a 3. kvartilis felett van. Ezen értékek meghatározásához először ki kell derítenünk a kvartiliseket.

Az Excel QUARTILE függvényt biztosít a kvartilisek kiszámításához. Két információra van szükség: a tömbre és a kvartra.

= QUARTILE (tömb, liter)

A tömb az értékelt tartomány, amelyet értékel. A kvart pedig egy olyan szám, amely a visszatérni kívánt kvartilt jelöli (pl. 1 az 1. kvartilisért, 2 a 2. kvartilisért stb.).

Megjegyzés: Az Excel 2010-ben a Microsoft kiadta a QUARTILE.INC és a QUARTILE.EXC függvényeket a QUARTILE funkció fejlesztéseként. A QUARTILE visszafelé kompatibilis, ha az Excel több verzióján dolgozik.

Térjünk vissza a példatáblázatunkhoz.

Az 1. kvartilis kiszámításához az F2 cellában a következő képletet használhatjuk.

= NEGYEDÉV (B2: B14,1)

A képlet beírásakor az Excel felsorolja a quart argumentum opcióit.

A 3. kvartilis kiszámításához olyan képletet írhatunk be, mint az előző az F3 cellában, de egy helyett hármat használunk.

= NEGYEDÉV (B2: B14,3)

Most megkapjuk a cellákban a kvartilis adatpontokat.

Második lépés: Értékelje az interkvartilis tartományt

Az interkvartilis tartomány (vagy IQR) az adatok középső 50% -a. Kiszámítása az 1. kvartilis és a 3. kvartilis érték közötti különbségként történik.

Egy egyszerű képletet fogunk használni az F4 cellába, amely kivonja az 1. kvartilt a 3. kvartilisből:

= F3-F2

Most láthatjuk az interkvartilis tartományt.

Harmadik lépés: Adja vissza az alsó és a felső határt

Az alsó és a felső határ az adattartomány legkisebb és legnagyobb értéke, amelyet használni akarunk. A kötött értékeknél kisebb vagy nagyobb értékek a kiugró értékek.

Kiszámítjuk az alsó határértéket az F5 cellában úgy, hogy megszorozzuk az IQR értékét 1,5-vel, majd kivonjuk a Q1 adatpontról:

= F2- (1,5 * F4)

Megjegyzés: Ennek a képletnek a zárójelére nincs szükség, mert a szorzási rész kiszámítja a kivonási rész előtt, de könnyebben olvashatóvá teszi a képletet.

Az F6 cella felső határának kiszámításához az IQR-t megint megszorozzuk 1,5-vel, de ezúttal hozzáadjuk a Q3 adatponthoz:

= F3 + (1,5 * F4)

Negyedik lépés: azonosítsa a kiugró értékeket

Most, hogy minden alapadatunk be van állítva, itt az ideje, hogy meghatározzuk a külsõ adatpontjainkat - azokat, amelyek alacsonyabbak, mint az alsó határértékek vagy magasabbak, mint a felsõ határértékek.

Az OR függvény segítségével elvégezzük ezt a logikai tesztet, és megmutatjuk az ezeknek a feltételeknek megfelelő értékeket azáltal, hogy a következő képletet írjuk be a C2 cellába:

= VAGY (B2 $ F $ 6)

Ezután átmásoljuk ezt az értéket a C3-C14 celláinkba. Az IGAZ érték egy kiugró értéket jelez, és amint láthatja, kettőnk van adatunkban.

A kiugró értékek figyelmen kívül hagyása az átlagos átlag kiszámításakor

A QUARTILE függvény segítségével számítsuk ki az IQR-t, és dolgozzunk a legszélesebb körben alkalmazott outlier definícióval. Azonban egy értéktartomány átlagának kiszámításakor és a szélsőértékek figyelmen kívül hagyásakor gyorsabb és könnyebb funkciót kell használni. Ez a technika nem azonosítja a kiugró értékeket, mint korábban, de lehetővé teszi számunkra, hogy rugalmasak legyünk azzal kapcsolatban, hogy mit tekinthetünk kiugró részünknek.

A szükséges funkciót TRIMMEAN-nek hívják, és a szintaxist alább láthatja:

= TRIMMEAN (tömb, százalék)

A tömb az átlagolni kívánt értéktartomány. A százalékos az aránya adatpontok kizárni a tetején és alján az adathalmaz (akkor adjuk meg, százalékban vagy decimális érték).

Az alábbi képletet a példánkban a D3 cellába írtuk be, hogy kiszámítsuk az átlagot és kizárjuk a kiugró értékek 20% -át.

= TRIMMEAN (B2: B14, 20%)

Itt két különböző funkcióval rendelkezik a kiugró értékek kezelésére. Függetlenül attól, hogy egyes jelentési igényekhez szeretné-e azonosítani őket, vagy kizárja őket a számításokból, például az átlagokból, az Excel funkcióval rendelkezik, amely megfelel az Ön igényeinek.