Hoe (en waarom) om de uitbijtersfunctie in Excel te gebruiken
Een uitbijter is een waarde die aanzienlijk hoger of lager is dan de meeste waarden in uw gegevens. Wanneer Excel wordt gebruikt om gegevens te analyseren, kunnen uitbijters de resultaten scheeftrekken. Het gemiddelde gemiddelde van een gegevensset kan bijvoorbeeld uw waarden werkelijk weergeven. Excel biedt enkele handige functies om uw uitschieters te helpen beheren, dus laten we een kijkje nemen.
Een snel voorbeeld
In de onderstaande afbeelding zijn de uitschieters redelijk eenvoudig te herkennen: de waarde van twee toegewezen aan Eric en de waarde van 173 toegewezen aan Ryan. In een dataset als deze is het eenvoudig genoeg om die uitschieters handmatig op te sporen en aan te pakken.
In een grotere set gegevens is dat niet het geval. Het is belangrijk om de uitschieters te kunnen identificeren en ze uit statistische berekeningen te verwijderen - en daar zullen we in dit artikel naar kijken..
Uitschieters vinden in uw gegevens
Om de uitbijters in een dataset te vinden, gebruiken we de volgende stappen:
- Bereken de 1e en 3e kwartielen (we zullen het hier hebben over wat die precies zijn).
- Evalueer het interkwartielbereik (we zullen deze ook een beetje verder uitleggen).
- Retourneer de boven- en ondergrenzen van ons gegevensbereik.
- Gebruik deze grenzen om de afgelegen gegevenspunten te identificeren.
Het celbereik aan de rechterkant van de gegevensset in de onderstaande afbeelding wordt gebruikt om deze waarden op te slaan.
Laten we beginnen.
Stap één: Bereken de kwartielen
Als u uw gegevens opsplitst in kwartalen, wordt elk van die sets een kwartiel genoemd. De laagste 25% van de getallen in het bereik vormen het 1e kwartiel, het volgende kwart het 2e kwartiel, enzovoort. We nemen eerst deze stap omdat de meest gebruikte definitie van een uitbijter een gegevenspunt is dat meer dan 1,5 interkwartielafstanden (IQR's) onder het 1e kwartiel en 1,5 interkwartielbereiken boven het 3e kwartiel ligt. Om die waarden te bepalen, moeten we eerst uitzoeken wat de kwartielen zijn.
Excel biedt een QUARTILE-functie om kwartielen te berekenen. Het vereist twee stukjes informatie: de array en de quart.
= QUARTILE (array, quart)
De rangschikking is het bereik van waarden die u aan het evalueren bent. En de pot is een getal dat het kwartiel vertegenwoordigt dat u wilt retourneren (bijvoorbeeld 1 voor de 1st kwartiel, 2 voor het 2e kwartiel, enzovoort).
Notitie: In Excel 2010 heeft Microsoft de functies QUARTILE.INC en QUARTILE.EXC vrijgegeven als verbeteringen aan de QUARTILE-functie. QUARTILE is meer achterwaarts compatibel wanneer u meerdere versies van Excel gebruikt.
Laten we terugkeren naar onze voorbeeldtabel.
Om de 1 te berekenenst Quartiel kunnen we de volgende formule gebruiken in cel F2.
= KWARTIEL (B2: B14,1)
Terwijl u de formule invoert, biedt Excel een lijst met opties voor het Quart-argument.
Om de 3 te berekenenrd kwartiel, we kunnen een formule zoals de vorige invoeren in cel F3, maar een drie gebruiken in plaats van een formule.
= KWARTIEL (B2: B14,3)
Nu hebben we de kwartistieke gegevenspunten weergegeven in de cellen.
Stap twee: Evalueer het interkwartielbereik
Het interkwartielbereik (of IQR) is de middelste 50% van de waarden in uw gegevens. Het wordt berekend als het verschil tussen de eerste kwartielwaarde en de derde kwartielwaarde.
We gaan een eenvoudige formule gebruiken in cel F4 die de 1 aftrektst kwartiel van de 3rd kwartiel:
= F3-F2
Nu kunnen we ons interkwartielbereik zien weergegeven.
Stap drie: keer de onder en bovengrenzen terug
De onder- en bovengrenzen zijn de kleinste en grootste waarden van het gegevensbereik dat we willen gebruiken. Alle waarden die kleiner of groter zijn dan deze gebonden waarden zijn de uitschieters.
We berekenen de ondergrens in cel F5 door de IQR-waarde met 1,5 te vermenigvuldigen en vervolgens af te trekken van het Q1-gegevenspunt:
= F2- (1,5 * F4)
Notitie: De haakjes in deze formule zijn niet nodig omdat het vermenigvuldigingsonderdeel vóór het aftrekgedeelte zal berekenen, maar ze maken de formule gemakkelijker leesbaar.
Om de bovengrens in cel F6 te berekenen, vermenigvuldigen we de IQR weer met 1,5, maar deze keer toevoegen het naar het Q3-gegevenspunt:
= F3 + (1,5 * F4)
Stap vier: identificeer de uitschieters
Nu we al onze onderliggende gegevens hebben ingesteld, is het tijd om onze afgelegen gegevenspunten te identificeren, die lager zijn dan de ondergrenswaarde of hoger dan de bovengrenswaarde.
We zullen de OF-functie gebruiken om deze logische test uit te voeren en de waarden te tonen die aan deze criteria voldoen door de volgende formule in cel C2 in te voeren:
= OR (B2 $ F $ 6)
We kopiëren die waarde vervolgens naar onze C3-C14-cellen. Een TRUE-waarde geeft een uitbijter aan, en zoals u kunt zien, staan er twee in onze gegevens.
De uitbijters negeren bij het berekenen van het gemiddelde gemiddelde
Met behulp van de QUARTILE-functie kunnen we de IQR berekenen en werken met de meest gebruikte definitie van een uitbijter. Bij het berekenen van het gemiddelde voor een bereik van waarden en het negeren van uitbijters is er echter een snellere en eenvoudigere functie om te gebruiken. Deze techniek zal een uitbijter niet identificeren zoals eerder, maar het zal ons in staat stellen om flexibel te zijn met wat we zouden kunnen beschouwen als ons uitbijtergedeelte.
De functie die we nodig hebben wordt TRIMMEAN genoemd, en je kunt de syntaxis hieronder zien:
= TRIMMEAN (array, percentage)
De rangschikking is het bereik van waarden dat u wilt gemiddelde. De procent is het percentage gegevenspunten dat moet worden uitgesloten van de boven- en onderkant van de gegevensset (u kunt dit invoeren als percentage of als decimale waarde).
We hebben de onderstaande formule ingevoerd in cel D3 in ons voorbeeld om het gemiddelde te berekenen en 20% uitbijters uit te sluiten.
= TRIMMEAN (B2: B14, 20%)
Daar heb je twee verschillende functies voor het omgaan met uitschieters. Of u ze nu wilt identificeren voor sommige rapporteringsbehoeften of ze uitsluit van berekeningen zoals gemiddelden, Excel heeft een functie die aan uw behoeften voldoet.
