Excel: hoe het gemiddelde te berekenen exclusief uitschieters

Er zijn twee manieren om een gemiddelde te berekenen zonder uitschieters in Excel:

1. Bereken het gemiddelde en gebruik TRIMMEAN om uitschieters uit te sluiten

2. Bereken het gemiddelde en gebruik het interkwartielbereik om uitschieters uit te sluiten

We gebruiken de volgende gegevensset in Excel om te illustreren hoe u beide methoden kunt gebruiken:

Methode 1: Bereken het gemiddelde en gebruik TRIMMEAN om uitschieters uit te sluiten

De TRIMMEAN -functie in Excel kan worden gebruikt om het gemiddelde van een reeks waarden te berekenen, terwijl een bepaald percentage waarnemingen aan de boven- en onderkant van de dataset wordt uitgesloten.

We kunnen bijvoorbeeld de volgende formule gebruiken om de gemiddelde waarde in kolom A te berekenen, exclusief een totaal van 20% van de waarnemingen (bovenste 10% en onderste 10%):

 =TRIMMEAN( A2:A16 , 20%)

Omdat we 15 waarden in onze dataset hebben, is 10% gelijk aan 1,5, wat wordt afgerond op 1. Deze formule berekent dus het gemiddelde van de waarden in het bereik, terwijl de kleinste waarde en de grootste waarde worden uitgesloten:

Het gemiddelde, exclusief uitschieters, blijkt 58,30769 te zijn.

Methode 2: Bereken het gemiddelde en gebruik het interkwartielbereik om uitschieters uit te sluiten

De interkwartielafstand (IQR) is het verschil tussen het 75e percentiel (Q3) en het 25e percentiel (Q1) in een dataset. Het meet de verdeling van de gemiddelde 50% van de waarden.

We kunnen een waarneming als een uitbijter definiëren als deze 1,5 keer de interkwartielafstand boven het derde kwartiel (Q3) of 1,5 keer de interkwartielafstand onder het eerste kwartiel (Q1) ligt.

We kunnen de volgende formule gebruiken om het interkwartielbereik van onze dataset in Excel te berekenen:

 =QUARTILE( A2:A16,3 )-QUARTILE( A2:A16,1 )

De volgende schermafbeelding laat zien hoe u deze formule gebruikt:

Vervolgens kunnen we de volgende formule gebruiken om de IQR te gebruiken om uitbijters te identificeren en een „1“ toe te kennen aan elke waarde die een uitbijter in de gegevensset is:

 =IF(OR( A2 <QUARTILE( $A$2:$A$16 ,1)-1.5* $B$18 , A2 >QUARTILE( $A$2:$A$16 ,3)+1.5* $B$18 ),1, 0)

De volgende schermafbeelding laat zien hoe u deze formule gebruikt:

We zien dat slechts één waarde – 164 – een uitbijter blijkt te zijn in deze dataset.

Ten slotte kunnen we de volgende formule gebruiken om het gemiddelde te berekenen van alle waarden in de dataset die geen uitschieters zijn:

 =AVERAGEIF( B2:B16 , 0, A2:A16 )

De volgende schermafbeelding laat zien hoe u deze formule gebruikt:

Het gemiddelde, exclusief uitschieters, blijkt 55,42857 te zijn.

Aanvullende bronnen

In de volgende zelfstudies wordt uitgelegd hoe u andere veelvoorkomende taken in Excel kunt uitvoeren:

Hoe het interkwartielbereik (IQR) in Excel te berekenen
Hoe het gemiddelde per groep in Excel te berekenen
Hoe gewogen voortschrijdende gemiddelden in Excel te berekenen