Amikor Excelben dolgozik az adatokkal, gyakran problémái vannak az adathalmazban lévő kiugró értékek kezelésével.
A kiugró értékek meglehetősen gyakoriak mindenféle adatban, ezért fontos azonosítani és kezelni ezeket a kiugró értékeket, hogy megbizonyosodhasson arról, hogy elemzése helyes és értelmesebb.
Ebben az oktatóanyagban megmutatom hogyan találunk kiugró értékeket az Excelben, és néhány olyan technikát, amelyeket munkám során használtam ezeknek az eltéréseknek a kezelésére.
Mik azok a kiugró értékek és miért fontos ezeket megtalálni?
A kiugró érték olyan adatpont, amely messze túlmutat az adathalmaz többi adatpontján. Ha az adatokban kiugró érték van, az torzíthatja az adatait, ami helytelen következtetésekhez vezethet.
Hadd mondjak egy egyszerű példát.
Tegyük fel, hogy 30 ember utazik busszal az A célállomástól a B célállomásig. Minden ember hasonló súlycsoportba és jövedelmi csoportba tartozik. Ennek az oktatóanyagnak az alkalmazásában tegyük fel, hogy az átlagos súly 220 font, az átlagos éves jövedelem pedig 70 000 dollár.
Most valahol az útvonalunk közepén áll meg a busz, és Bill Gates beugrik.
Mit gondol, ez mit tenne a buszon ülők átlagos súlyával és átlagjövedelmével.
Bár az átlagos súly valószínűleg nem fog sokat változni, a buszon ülők átlagos jövedelme erősen meg fog emelkedni.
Ez azért van, mert Bill Gates jövedelme a csoportunkban kívül esik, és ez rosszul értelmezi az adatokat. A buszon tartózkodók átlagos jövedelme néhány milliárd dollár lenne, ami messze meghaladja a tényleges értéket.
Amikor az Excelben valós adatkészletekkel dolgozik, bármilyen irányú kiugró értékeket kaphat (azaz pozitív vagy negatív kiugró értékeket).
Annak érdekében, hogy az elemzés helyes legyen, valahogy azonosítania kell ezeket a kiugró értékeket, majd el kell döntenie, hogyan kezelje őket a legjobban.
Most nézzünk meg néhány módszert arra, hogyan találjunk kiugró értékeket az Excelben.
Keressen kiugró értékeket az adatok rendezésével
Kis adathalmazok esetén a kiugró értékek azonosításának gyors módja az adatok egyszerű rendezése és a rendezett adatok tetején lévő értékek kézi átnézése.
És mivel mindkét irányban lehetnek kiugró értékek, győződjön meg róla, hogy először növekvő, majd csökkenő sorrendben rendezi az adatokat, majd menjen át a legfelső értékekre.
Hadd mutassak egy példát.
Az alábbiakban van egy adatkészletem, ahol 15 ügyfélszolgálati hívás időtartama (másodpercben) van megadva.
Az alábbiakban bemutatjuk az adatok rendezésének lépéseit, hogy azonosíthassuk a kiugró értékeket az adatkészletben:
- Válassza ki a rendezni kívánt oszlop oszlopfejlécét (ebben a példában a B1 cella)
- Kattintson a Kezdőlap fülre
- A Szerkesztés csoportban kattintson a Rendezés és szűrés ikonra.
- Kattintson az Egyéni rendezés lehetőségre
- A Rendezés párbeszédpanelen válassza a „Időtartam” lehetőséget a Rendezés a legördülő menüből, és a „Legnagyobbtól a legkisebbig” lehetőséget a Rendelés legördülő menüben
- Kattintson az OK gombra
A fenti lépések rendezik a hívás időtartama oszlopot a legmagasabb értékekkel a tetején. Most manuálisan beolvashatja az adatokat, és megnézheti, hogy vannak -e kiugró értékek.
Példánkban látom, hogy az első két érték sokkal magasabb, mint a többi érték (és az alsó kettő sokkal alacsonyabb).
Megjegyzés: Ez a módszer kis adathalmazokkal működik, ahol manuálisan szkennelheti az adatokat. Ez nem tudományos módszer, de jól működik
Kiugró értékek keresése a kvartilis függvények használatával
Most beszéljünk egy tudományosabb megoldásról, amely segít azonosítani, hogy vannak -e kiugró értékek vagy sem.
A statisztikákban a kvartilis az adathalmaz egynegyede. Például, ha 12 adatpontja van, akkor az első kvartilis az alsó három adatpont, a második kvartilis a következő három adatpont, stb.
Az alábbiakban az adatkészlet található, ahol a kiugró értékeket szeretném megtalálni. Ehhez ki kell számolnom az első és a harmadik kvartilt, majd ennek segítségével kiszámítanom a felső és az alsó határt.
Az alábbi képlet az E2 cella első kvartilisének kiszámítására szolgál:
= QUARTILE.INC ($ B $ 2: $ B $ 15,1)
és itt kell kiszámítani az E3 cella harmadik kvartilisét:
= QUARTILE.INC ($ B $ 2: $ B $ 15,3)
Most a fenti két számítást használva megkaphatom az interkvartilis tartományt (ami az adataink 50% -a az első és a harmadik kvartilisben)
= F3-F2
Most az interkvartilis tartományt fogjuk használni, hogy megtaláljuk az alsó és felső határt, amely a legtöbb adatunkat tartalmazza.
Mindaz, ami ezen alsó és felső határon kívül esik, akkor kiugrónak minősül.
Az alsó képlet az alábbi határérték kiszámítására szolgál:
= Kvartilis1 - 1,5*(interkvartilis tartomány)
példánkban ez lesz:
= F2-1,5*F4
És a felső határ kiszámítására szolgáló képlet a következő:
= Kvartilis3 + 1,5*(interkvartilis tartomány)
példánkban ez lesz:
= F3+1,5*F4
Most, hogy adathalmazunkban megvan a felső és alsó korlát, visszatérhetünk az eredeti adatokhoz, és gyorsan azonosíthatjuk azokat az értékeket, amelyek nem tartoznak ebbe a tartományba.
Ennek gyors módja az lenne, ha minden értéket ellenőrizne, és egy IGAZ vagy HAMIS értéket adna meg egy új oszlopban.
Az alábbi VAGY képletet használva IGAZ értéket kaptam azokhoz az értékekhez, amelyek kiugróak.
= VAGY (B2 $ F $ 6)
Most szűrheti az Outlier oszlopot, és csak azokat a rekordokat jelenítheti meg, ahol az érték IGAZ.
Alternatív megoldásként feltételes formázással is kiemelheti az összes cellát, ahol az érték IGAZ
Jegyzet: Míg ez egy elfogadottabb módszer a statisztikák kiugró értékeinek megállapítására. Ezt a módszert kissé használhatatlannak tartom a valós életben. A fenti példában a képlet által kiszámított alsó határ -103, míg a rendelkezésünkre álló adatkészlet csak pozitív lehet. Tehát ez a módszer segíthet abban, hogy az egyik irányba (magas értékek) kiugró értékeket találjunk, haszontalan a másik irányba mutató kiugrók azonosítása.A kiugró értékek keresése a LARGE/SMALL funkciók használatával
Ha sok adattal dolgozik (több oszlopban lévő értékek), akkor kivonhatja a legnagyobb és a legkisebb 5 vagy 7 értéket, és megnézheti, hogy vannak -e benne kiugró értékek.
Ha vannak kiugró értékek, azonosítani tudja őket anélkül, hogy mindkét irányban végig kellene mennie az adatokon.
Tegyük fel, hogy rendelkezünk az alábbi adatkészlettel, és szeretnénk tudni, hogy vannak -e kiugró értékek.
Az alábbi képlet megadja az adathalmaz legnagyobb értékét:
= NAGY ($ B $ 2: $ B $ 16,1)
Hasonlóképpen, a második legnagyobb értéket adja meg
= NAGY ($ B $ 2: $ B $ 16,1)
Ha nem a Microsoft 365 -öt használja, amely dinamikus tömbökkel rendelkezik, akkor használja az alábbi képletet, és egyetlen képlettel megadja az adatkészlet öt legnagyobb értékét:
= NAGY ($ B $ 2: $ B $ 16, SOR ($ 1: 5))
Hasonlóképpen, ha a legkisebb 5 értéket szeretné használni, használja az alábbi képletet:
= KIS ($ B $ 2: $ B $ 16, SOR ($ 1: 5))
vagy a következőket, ha nincs dinamikus tömbje:
= KIS ($ B $ 2: $ B $ 16,1)
Ha megvannak ezek az értékek, nagyon könnyű megtudni az adathalmaz esetleges kiugró értékeit.
Míg én a legnagyobb és legkisebb 5 érték kinyerését választottam, választhat, hogy 7 -et vagy 10 -et kap, attól függően, hogy mekkora az adatkészlete.
Nem vagyok biztos abban, hogy ez elfogadható módszer -e az Excelben a kiugró értékek megállapítására, vagy sem, de ezt a módszert használtam, amikor néhány évvel ezelőtt sok pénzügyi adatot kellett dolgoznom a munkám során. Az oktatóanyag összes többi módszeréhez képest ezt találtam a leghatékonyabbnak.
Hogyan kell helyesen kezelni a kiugró értékeket
Eddig láttuk azokat a módszereket, amelyek segítenek megtalálni a kiugró értékeket az adathalmazunkban. De mit kell tenni, ha tudod, hogy vannak kiugró értékek.
Íme néhány módszer, amellyel kezelheti a kiugró értékeket, hogy az adatelemzés helyes legyen.
Törölje a kiugró értékeket
A legegyszerűbb módja annak, hogy eltávolítsa az eltéréseket az adatkészletből, ha egyszerűen törli azokat. Így nem torzítja el az elemzést.
Ez életképesebb megoldás, ha nagy adathalmazokkal rendelkezik, és néhány kiugró érték törlése nem befolyásolja az általános elemzést. És természetesen az adatok törlése előtt győződjön meg arról, hogy másolatot készít, és vizsgálja meg, mi okozza ezeket az eltéréseket.
A kiugró értékek normalizálása (az érték beállítása)
A kiugró értékek normalizálása az, amit teljes munkaidős munkám során szoktam. Minden kiugró érték esetén egyszerűen módosítanám őket olyan értékre, amely valamivel magasabb, mint az adathalmaz maximális értéke.
Ez biztosította, hogy nem törlöm az adatokat, de ugyanakkor nem hagyom, hogy eltorzítsák az adataimat.
Hogy egy valós példát mondjak, ha olyan vállalatok nettó haszonkulcsát elemzi, ahol a vállalatok többsége -10%és 30%között van, és van néhány 100%fölötti érték, egyszerűen megváltoztatná ezeket a kiugró értékeket 30% -ra vagy 35% -ra.
Tehát ezek azok a módszerek, amelyeket használhat Excel, hogy megtalálja a kiugró értékeket.
Miután azonosította a kiugró értékeket, belemerülhet az adatokba, és megkeresheti, hogy mi okozza ezeket, ugyanakkor kiválaszthatja az egyik módszert a kiugró értékek kezelésére (amely eltávolíthatja vagy normalizálhatja ezeket az értékek módosításával)
Remélem, hasznosnak találta ezt az oktatóanyagot.
