Hoe (en waarom) u de functie Uitschieters in Excel gebruikt

• Alan Murray

  @Computergaga1

• Updated January 12, 2019, 5:03am EDT

Een uitschieter is een waarde die aanzienlijk hoger of lager is dan de meeste waarden in uw gegevens. Wanneer u Excel gebruikt om gegevens te analyseren, kunnen uitschieters de resultaten scheeftrekken. Het gemiddelde van een gegevensverzameling kan bijvoorbeeld een getrouwe weergave van uw waarden zijn. Excel biedt een paar handige functies om uitbijters te beheren, dus laten we eens kijken.

Een snel voorbeeld

In de onderstaande afbeelding zijn de uitbijters redelijk gemakkelijk te zien: de waarde twee die is toegewezen aan Eric en de waarde 173 die is toegewezen aan Ryan. In een gegevensverzameling als deze is het eenvoudig genoeg om deze uitschieters handmatig op te sporen en aan te pakken.

In een grotere gegevensverzameling zal dat niet het geval zijn. Het is belangrijk om de uitschieters te kunnen identificeren en ze uit statistische berekeningen te verwijderen, en dat is wat we in dit artikel gaan bekijken.

Hoe vind je uitschieters in je gegevens

Om de uitschieters in een gegevensverzameling te vinden, gebruiken we de volgende stappen:

1. Bereken het 1e en 3e kwartiel (we zullen het zo hebben over wat dat zijn).
2. Bepaal de interkwartielafstand (ook die zullen we verderop uitleggen).
3. Retourneer de boven- en ondergrens van ons gegevensbereik.
4. Gebruik deze grenzen om de buitenste gegevenspunten te identificeren.

Het celbereik aan de rechterkant van de gegevensverzameling in de onderstaande afbeelding zal worden gebruikt om deze waarden op te slaan.

Laten we aan de slag gaan.

Stap één: bereken de kwartielen

Als u uw gegevens in kwartielen verdeelt, wordt elk van die reeksen een kwartiel genoemd. De laagste 25% van de getallen in het bereik vormen het 1e kwartiel, de volgende 25% het 2e kwartiel, enzovoort. Wij doen deze stap eerst omdat de meest gebruikte definitie van een uitbijter een gegevenspunt is dat meer dan 1,5 interkwartielafstanden (IQR’s) onder het 1e kwartiel ligt, en 1,5 interkwartielafstanden boven het 3e kwartiel. Om die waarden te bepalen, moeten we eerst weten wat de kwartielen zijn.

Excel heeft een functie QUARTILE om kwartielen te berekenen. Daarvoor zijn twee gegevens nodig: de array en het kwartiel.

=QUARTILE(array, quart)

De array is het bereik van waarden dat je evalueert. En het kwartiel is een getal dat staat voor het kwartiel dat je wilt teruggeven (bijvoorbeeld 1 voor het 1e kwartiel, 2 voor het 2e kwartiel, enzovoort).

Note: In Excel 2010 heeft Microsoft de functies QUARTILE.INC en QUARTILE.EXC uitgebracht als verbeteringen op de QUARTILE-functie. QUARTILE is beter compatibel met oudere versies van Excel.

Laten we terugkeren naar onze voorbeeldtabel.

Om het 1e kwartiel te berekenen, kunnen we de volgende formule gebruiken in cel F2.

=QUARTILE(B2:B14,1)

Terwijl u de formule invoert, geeft Excel een lijst met opties voor het argument kwartiel.

Om het 3e kwartiel te berekenen, kunnen we een formule als de vorige invoeren in cel F3, maar dan met een drie in plaats van een een.

=QUARTILE(B2:B14,3)

Nu hebben we de kwartiel-gegevenspunten in de cellen weergegeven.

Stap twee: evalueer de interkwartielafstand

De interkwartielafstand (of IQR) is de middelste 50% van de waarden in uw gegevens. Het wordt berekend als het verschil tussen de 1e kwartielwaarde en de 3e kwartielwaarde.

We gebruiken een eenvoudige formule in cel F4 die het 1e kwartiel aftrekt van het 3e kwartiel:

=F3-F2

Nu kunnen we ons interkwartielbereik zien weergegeven.

Stap drie: Geef de onder- en bovengrenzen

De onder- en bovengrenzen zijn de kleinste en grootste waarden van het gegevensbereik die we willen gebruiken. Alle waarden die kleiner of groter zijn dan deze grenswaarden zijn de uitschieters.

We berekenen de ondergrens in cel F5 door de IQR-waarde te vermenigvuldigen met 1.5 en dit vervolgens af te trekken van het Q1-gegevenspunt:

=F2-(1.5*F4)

Note: de haakjes in deze formule zijn niet nodig omdat het vermenigvuldigingsgedeelte vóór het aftrekgedeelte wordt berekend, maar ze maken de formule wel gemakkelijker leesbaar.

Om de bovengrens in cel F6 te berekenen, vermenigvuldigen we de IQR met 1.5, maar deze keer tellen we het op bij het gegevenspunt Q3:

=F3+(1.5*F4)

Stap vier: Identificeer de uitschieters

Nu we al onze onderliggende gegevens hebben ingesteld, is het tijd om onze uitschieters te identificeren – de punten die lager zijn dan de ondergrenswaarde of hoger dan de bovengrenswaarde.

We gebruiken de OR-functie om deze logische test uit te voeren en tonen de waarden die aan deze criteria voldoen door de volgende formule in cel C2 in te voeren:

=OR(B2<$F$5,B2>$F$6)

We kopiëren die waarde vervolgens naar onze cellen C3-C14. Een TRUE-waarde duidt op een uitbijter, en zoals u kunt zien, hebben we er twee in onze gegevens.

Het negeren van de uitbijters bij het berekenen van het gemiddelde

Door de QUARTILE-functie te gebruiken, kunnen we het IQR berekenen en werken met de meest gebruikte definitie van een uitbijter. Wanneer echter het gemiddelde voor een reeks waarden wordt berekend en uitschieters worden genegeerd, is er een snellere en gemakkelijkere functie. Deze techniek zal geen uitbijter identificeren zoals voorheen, maar het zal ons in staat stellen flexibel te zijn met wat we zouden kunnen beschouwen als ons uitbijtergedeelte.

De functie die we nodig hebben heet TRIMMEAN, en je kunt de syntaxis ervan hieronder zien:

=TRIMMEAN(array, percent)

De array is het bereik van waarden waarvan je het gemiddelde wilt berekenen. Het percentage is het percentage gegevenspunten dat moet worden uitgesloten van de boven- en onderkant van de gegevensverzameling (u kunt het invoeren als percentage of als decimale waarde)

Wij hebben in ons voorbeeld de onderstaande formule ingevoerd in cel D3 om het gemiddelde te berekenen en 20% van de uitschieters uit te sluiten.

=TRIMMEAN(B2:B14, 20%)

Daar hebt u twee verschillende functies voor het omgaan met uitschieters. Of u ze nu wilt identificeren voor bepaalde rapportagebehoeften of ze wilt uitsluiten van berekeningen zoals gemiddelden, Excel heeft een functie die aan uw behoeften voldoet.