Wanneer u met gegevens in Excel werkt, zult u vaak problemen hebben met het omgaan met uitschieters in uw gegevensset.
Het hebben van uitbijters komt vrij vaak voor in allerlei soorten gegevens, en het is belangrijk om deze uitbijters te identificeren en te behandelen om ervoor te zorgen dat uw analyse correct en zinvoller is.
In deze tutorial laat ik het je zien uitbijters vinden in Excel, en enkele van de technieken die ik in mijn werk heb gebruikt om met deze uitbijters om te gaan.
Wat zijn uitschieters en waarom is het belangrijk om deze te vinden?
Een uitbijter is een datapunt dat ver boven de andere datapunten in de dataset ligt. Wanneer u een uitbijter in de gegevens heeft, kan dit uw gegevens scheeftrekken, wat kan leiden tot onjuiste gevolgtrekkingen.
Laat me je een eenvoudig voorbeeld geven.
Laten we zeggen dat 30 mensen in een bus van bestemming A naar bestemming B reizen. Alle mensen zitten in een vergelijkbare gewichts- en inkomensgroep. Laten we voor het doel van deze tutorial beschouwen dat het gemiddelde gewicht 220 pond is en het gemiddelde jaarlijkse inkomen $ 70.000.
Nu ergens in het midden van onze route stopt de bus en stapt Bill Gates in.
Wat denk je dat dit zou doen met het gemiddelde gewicht en het gemiddelde inkomen van de mensen in de bus?
Hoewel het gemiddelde gewicht waarschijnlijk niet veel zal veranderen, zal het gemiddelde inkomen van de mensen in de bus flink omhoogschieten.
Dat komt omdat het inkomen van Bill Gates een uitbijter is in onze groep, en dat geeft ons een verkeerde interpretatie van de gegevens. Het gemiddelde inkomen voor elke persoon in de bus zou een paar miljard dollar zijn, wat ver boven de werkelijke waarde ligt.
Wanneer u met werkelijke gegevenssets in Excel werkt, kunt u uitbijters in elke richting hebben (d.w.z. een positieve uitbijter of een negatieve uitbijter).
En om er zeker van te zijn dat uw analyse correct is, moet u deze uitschieters op de een of andere manier identificeren en vervolgens beslissen hoe u ze het beste kunt behandelen.
Laten we nu een aantal manieren bekijken om uitbijters in Excel te vinden.
Vind uitbijters door de gegevens te sorteren
Met kleine datasets is een snelle manier om uitbijters te identificeren, eenvoudig de gegevens te sorteren en handmatig enkele van de waarden bovenaan deze gesorteerde gegevens te doorlopen.
En aangezien er in beide richtingen uitschieters kunnen zijn, moet u ervoor zorgen dat u de gegevens eerst in oplopende volgorde en vervolgens in aflopende volgorde sorteert en vervolgens de bovenste waarden doorloopt.
Laat me je een voorbeeld laten zien.
Hieronder heb ik een dataset met gespreksduur (in seconden) voor 15 klantenservicegesprekken.
Hieronder vindt u de stappen om deze gegevens te sorteren, zodat we de uitbijters in de gegevensset kunnen identificeren:
- Selecteer de kolomkop van de kolom die u wilt sorteren (cel B1 in dit voorbeeld)
- Klik op het tabblad Start
- Klik in de groep Bewerken op het pictogram Sorteren en filteren.
- Klik op Aangepast sorteren
- Selecteer in het dialoogvenster Sorteren 'Duur' in de vervolgkeuzelijst Sorteren op en 'Grootste naar kleinste' in de vervolgkeuzelijst Volgorde
- Klik OK
De bovenstaande stappen sorteren de kolom gespreksduur met de hoogste waarden bovenaan. Nu kunt u de gegevens handmatig scannen en kijken of er uitschieters zijn.
In ons voorbeeld kan ik zien dat de eerste twee waarden veel hoger zijn dan de rest van de waarden (en de onderste twee zijn veel lager).
Opmerking: deze methode werkt met kleine datasets waar u de gegevens handmatig kunt scannen. Het is geen wetenschappelijke methode, maar werkt goed
Uitbijters vinden met behulp van de kwartielfuncties
Laten we het nu hebben over een meer wetenschappelijke oplossing die u kan helpen bepalen of er uitbijters zijn of niet.
In de statistiek is een kwartiel een kwart van de dataset. Als u bijvoorbeeld 12 gegevenspunten hebt, is het eerste kwartiel de onderste drie gegevenspunten, het tweede kwartiel de volgende drie gegevenspunten, enzovoort.
Hieronder staat de dataset waar ik de uitbijters wil vinden. Om dit te doen, moet ik het 1e en het 3e kwartiel berekenen en vervolgens de boven- en ondergrens berekenen.
Hieronder staat de formule om het eerste kwartiel in cel E2 te berekenen:
=KWARTIEL.INC($B$2:$B$15,1)
en hier is degene die het derde kwartiel in cel E3 berekent:
=KWARTIEL.INC($B$2:$B$15,3)
Nu kan ik de bovenstaande twee berekeningen gebruiken om het interkwartielbereik te krijgen (dat is 50% van onze gegevens binnen het 1e en het 3e kwartiel)
=F3-F2
Nu zullen we het interkwartielbereik gebruiken om de onder- en bovengrens te vinden die de meeste van onze gegevens zou bevatten.
Alles wat buiten deze onder- en bovengrenzen valt, wordt dan als uitbijters beschouwd.
Hieronder staat de formule om de ondergrens te berekenen:
=Kwartiel1 - 1,5*(interkwartielbereik)
wat in ons voorbeeld wordt:
=F2-1.5*F4
En de formule om de bovengrens te berekenen is:
=Kwartiel3 + 1.5*(Interkwartielbereik)
wat in ons voorbeeld wordt:
=F3+1.5*F4
Nu we de boven- en ondergrens in onze dataset hebben, kunnen we teruggaan naar de oorspronkelijke gegevens en snel die waarden identificeren die niet in dit bereik liggen.
Een snelle manier om dit te doen is door elke waarde te controleren en een TRUE of FALSE in een nieuwe kolom te retourneren.
Ik heb de onderstaande OR-formule gebruikt om TRUE te krijgen voor die waarden die uitbijters zijn.
=OF(B2$F$6)
Nu kunt u de kolom Uitbijter filteren en alleen de records weergeven waarvan de waarde WAAR is.
Als alternatief kunt u ook voorwaardelijke opmaak gebruiken om alle cellen te markeren waar de waarde WAAR is
Opmerking: Hoewel dit een meer geaccepteerde methode is om uitbijters in statistieken te vinden. Ik vind deze methode een beetje onbruikbaar in real-life scenario's. In het bovenstaande voorbeeld is de ondergrens berekend door de formule -103, terwijl de dataset die we hebben alleen positief kan zijn. Dus deze methode kan ons helpen uitbijters in de ene richting te vinden (hoge waarden), het is nutteloos om uitbijters in de andere richting te identificeren.De uitschieters vinden met behulp van de LARGE/SMALL-functies
Als je met veel data werkt (waarden in meerdere kolommen), kun je de grootste en de kleinste 5 of 7 waarden extraheren en kijken of er uitschieters in zitten.
Als er uitbijters zijn, kunt u deze identificeren zonder dat u alle gegevens in beide richtingen hoeft te doorlopen.
Stel dat we de onderstaande dataset hebben en we willen weten of er uitschieters zijn.
Hieronder vindt u de formule die u de grootste waarde in de dataset geeft:
=GROOT ($B$2:$B$16,1)
Evenzo wordt de op één na grootste waarde gegeven door
=GROOT ($B$2:$B$16,1)
Als u Microsoft 365, dat dynamische matrices heeft, niet gebruikt, kunt u de onderstaande formule gebruiken en krijgt u de vijf grootste waarden uit de gegevensset met één enkele formule:
=GROOT ($B$2:$B$16,RIJ($1:5))
Evenzo, als u de kleinste 5 waarden wilt, gebruikt u de onderstaande formule:
=KLEIN ($B$2:$B$16,RIJ($1:5))
of het volgende voor het geval je geen dynamische arrays hebt:
=KLEIN ($B$2:$B$16,1)
Zodra u deze waarden hebt, is het heel eenvoudig om eventuele uitbijters in de dataset te achterhalen.
Hoewel ik ervoor heb gekozen om de grootste en kleinste 5 waarden te extraheren, kunt u ervoor kiezen om 7 of 10 te krijgen op basis van hoe groot uw dataset is.
Ik weet niet zeker of dit een acceptabele methode is om uitschieters in Excel te vinden of niet, maar dit is de methode die ik gebruikte toen ik een paar jaar geleden in mijn werk met veel financiële gegevens moest werken. Vergeleken met alle andere methoden die in deze tutorial worden behandeld, vond ik deze het meest effectief.
Uitschieters op de juiste manier behandelen?
Tot nu toe hebben we de methoden gezien die ons zullen helpen de uitbijters in onze dataset te vinden. Maar wat te doen als je eenmaal weet dat er uitschieters zijn.
Hier zijn een aantal methoden die u kunt gebruiken om uitbijters te behandelen, zodat uw gegevensanalyse correct is.
Verwijder de uitschieters
De eenvoudigste manier om uitbijters uit uw dataset te verwijderen, is door ze simpelweg te verwijderen. Op deze manier wordt uw analyse niet vertekend.
Het is een meer haalbare oplossing als je grote datasets hebt en het verwijderen van een aantal uitbijters geen invloed heeft op de algehele analyse. En natuurlijk, voordat u de gegevens verwijdert, moet u ervoor zorgen dat u een kopie maakt en ingaat op de oorzaak van deze uitschieters.
Normaliseer de uitschieters (pas de waarde aan)
De uitbijters normaliseren is wat ik deed toen ik fulltime werkte. Voor alle uitbijterwaarden zou ik ze gewoon veranderen in een waarde die iets hoger is dan de maximale waarde in de dataset.
Dit zorgde ervoor dat ik de gegevens niet verwijder, maar tegelijkertijd laat ik mijn gegevens niet scheef trekken.
Om u een realistisch voorbeeld te geven, als u de nettowinstmarge van bedrijven analyseert, waar de meeste bedrijven binnen -10% tot 30% liggen, en er zijn een aantal waarden die meer dan 100% zijn, dan zou deze uitbijterwaarden eenvoudigweg veranderen in 30% of 35%.
Dit zijn dus enkele van de methoden die u kunt gebruiken in: Excel om uitbijters te vinden.
Zodra u de uitbijters hebt geïdentificeerd, kunt u zich verdiepen in de gegevens en zoeken naar de oorzaak hiervan, en tegelijkertijd een van de technieken kiezen om met deze uitbijters om te gaan (die deze uitbijters kunnen verwijderen of normaliseren door de waarde aan te passen)
Ik hoop dat je deze tutorial nuttig vond.
