Hoe berekent u de mediaan in Excel terwijl u nullen of foutwaarden negeert?
Bij veel data-analysetaken in Excel is het nauwkeurig berekenen van de mediaan essentieel om de centrale tendens van uw dataset te begrijpen. Soms bevat uw dataset echter nullen of foutwaarden (zoals)#DELING/0!, #N/B, enzovoort), die een eenvoudige mediaanberekening kunnen verstoren. Bijvoorbeeld: het gebruik van de standaardformule =MEDIAAN(bereik) neemt nullen mee in de berekening en retourneert een fout zodra er ongeldige cellen in het bereik aanwezig zijn—wat kan leiden tot misleidende resultaten of foutmeldingen, zoals hieronder wordt geïllustreerd.
Om dit aan te pakken, biedt Excel diverse oplossingen om de mediaan te berekenen terwijl u nullen of foutwaarden uitsluit — voor een analyse die zowel nauwkeurig als robuust is. Deze methoden zijn ideaal voor verschillende scenario’s, zoals het opschonen van enquêtedata, financiële rapporten of wetenschappelijke metingen waarin nullen of fouten moeten worden uitgesloten om zinvolle resultaten te verkrijgen. Hieronder vindt u praktische, stapsgewijze handleidingen voor elke beschikbare methode, variërend van directe formules tot geavanceerde automatiseringstechnieken.
VBA: Mediaan zonder nullen en fouten (UDF)
Power Query: Mediaan na filteren van nullen/fouten
Mediaan zonder nullen
Wanneer uw bereik nullen bevat die u niet in de mediaanberekening wilt opnemen—bijvoorbeeld ontbrekende waarden die als 0 worden weergegeven—kunt u een matrixformule gebruiken om die nullen uit te sluiten. Dit is vooral handig in datasets waar nullen fungeren als tijdelijke plaatshouders voor ontbrekende gegevens, in plaats van als daadwerkelijke metingen.
Selecteer een cel waarin u de mediaan wilt weergeven (bijvoorbeeld C2) en voer de volgende formule in:
=MEDIAN(IF(A2:A17<>0,A2:A17))Nadat u de formule hebt ingevoerd, drukt u niet gewoon op Enter, maar op Ctrl + Shift + Enter om er een matrixformule van te maken (u ziet accolades verschijnen rond de formule in de formulebalk). Hierdoor worden alleen de waarden ongelijk aan nul in A2:A17 meegenomen in de mediaanberekening. Zie screenshot:
Tips:
- Als u Excel 365 of Excel 2021 (of nieuwer) gebruikt, hoeft u alleen op Enter te drukken, dankzij de ondersteuning voor dynamische matrices.
- Zorg ervoor dat het bereik ten minste één numerieke waarde bevat die ongelijk is aan nul; anders retourneert de formule een #GETAL!-fout.
- Deze oplossing is ideaal om enquêteresultaten, declaratieformulieren of verkoopgegevens op te schonen, waarbij nullen buiten beschouwing worden gelaten tijdens de analyse.
Mediaan zonder fouten
Foutwaarden zoals #N/B, #DELING/0! of #WAARDE! kunnen ervoor zorgen dat de standaard mediaanfunctie een fout retourneert, waardoor uw data-analyse wordt onderbroken. Gebruik de volgende matrixformule om veilig de mediaan te berekenen terwijl u deze fouten uitsluit.
Selecteer een willekeurige cel waarin u het resultaat wilt weergeven en voer de onderstaande formule in:
=MEDIAN(IF(ISNUMBER(F2:F17),F2:F17))Nadat u de formule hebt ingevoerd, drukt u op Ctrl + Shift + Enter (tenzij u Excel 365/Excel 2021 of nieuwer gebruikt, dat dynamische matrices ondersteunt). Deze formule neemt alleen echte getallen in F2:F17 mee en negeert foutcellen volledig.
Tips en waarschuwingen:
- Als alle cellen foutwaarden bevatten, krijgt u een #GETAL!-fout. Zorg ervoor dat uw gegevens ten minste één geldig getal bevatten.
- U kunt uitsluitingscriteria combineren—bijvoorbeeld zowel nullen als fouten uitsluiten—door voorwaarden te nesten.
- Deze formule is bijzonder handig bij het werken met geïmporteerde gegevens, enquêteresultaten of financiële overzichten die gedeeltelijke of mislukte berekeningen kunnen bevatten.
VBA: Mediaan zonder nullen en fouten (UDF)
Voor scenario’s waarin u regelmatig de mediaan moet berekenen terwijl u zowel nullen als foutwaarden negeert – of wanneer u een oplossing zoekt die het handmatig invoeren van matrixformules overbodig maakt – is een aangepaste VBA-functie (User-Defined Function, UDF) de ideale keuze. Deze aanpak biedt extra flexibiliteit, omdat de functie alle uitsluitingscriteria in één keer kan verwerken en net als een ingebouwde Excel-functie eenvoudig toepasbaar is. Daardoor is deze methode perfect geschikt voor grote datasets of gegevens die regelmatig worden bijgewerkt.
Hoe stelt u de UDF in:
- Klik op het tabblad Ontwikkelaar in Excel. Als dit niet beschikbaar is, schakel het dan in via Bestand > Opties > Aanpassen Lint.
- Klik op Visual Basic om de VBA-editor te openen.
- Klik in de VBA-editor op Invoegen > Module om een nieuwe module te maken.
- Kopieer en plak de volgende code in de module:
Function MedianIgnoreZeroError(rng As Range) As Variant
Dim cell As Range
Dim tempList() As Double
Dim count As Integer
count = 0
On Error Resume Next
xTitleId = "KutoolsforExcel"
For Each cell In rng
If IsNumeric(cell.Value) Then
If cell.Value <> 0 And Not IsError(cell.Value) Then
count = count + 1
ReDim Preserve tempList(1 To count)
tempList(count) = cell.Value
End If
End If
Next cell
On Error GoTo 0
If count = 0 Then
MedianIgnoreZeroError = CVErr(xlErrNum)
Else
MedianIgnoreZeroError = Application.WorksheetFunction.Median(tempList)
End If
End FunctionHoe gebruikt u de UDF:
Nadat u naar Excel bent teruggekeerd, typt u eenvoudig de formule =MediaanNegeerNulFout(A2:A17)in een willekeurige cel (vervang)A2:A17 door uw eigen bereik). In tegenstelling tot matrixformules hoeft u alleen op Enter te drukken—Ctrl + Shift + Enter is niet nodig.
- Deze methode is uitstekend geschikt voor zeer grote datasets, omzeilt de eigenaardigheden van matrixformules en kan eenvoudig worden aangepast om andere ongewenste waarden te negeren door de code verder te personaliseren.
- Als het bereik alleen nullen of fouten bevat, wordt #GETAL!
- Als u een #NAAM?-fout krijgt, controleer dan of de VBA-macro correct is geïnstalleerd en of macro’s zijn ingeschakeld in uw Excel-instellingen.
Power Query: Mediaan na filteren van nullen/fouten
Power Query is een krachtig hulpmiddel in Excel voor het importeren, transformeren en analyseren van gegevens – vooral ideaal wanneer u grote datasets wilt opschonen en voorbewerken vóór berekeningen zoals de mediaan. Met Power Query filtert u eenvoudig nullen en fouten, zodat alleen geldige getallen overblijven voor uw berekening. Deze aanpak is bijzonder voordelig als uw brongegevens regelmatig wordt bijgewerkt of uit externe systemen wordt geïmporteerd.
Stappen om Power Query te gebruiken voor het berekenen van de mediaan terwijl u nullen en fouten negeert:
- Selecteer een willekeurige cel binnen uw gegevensbereik, ga naar het tabblad Gegevens en klik op Van tabel/bereik. Als uw gegevens nog niet in tabelvorm zijn, vraagt Excel u om een tabel te maken – klik gewoon op OK.
- Het venster Power Query Editor wordt geopend. Klik op de vervolgkeuzepijl van de relevante kolom en schakel 0uit om nulwaarden te filteren. (Voor foutfiltering: klik met de rechtermuisknop op de kolomkop en kies)Fouten verwijderen.)
- Nadat u heeft gefilterd, klikt u op Start > Sluiten en laden om de opgeschoonde gegevens terug te sturen naar uw werkblad.
- Pas nu de standaardformule
=MEDIAAN()toe op de kolom met alleen de gefilterde waarden, aangezien de gegevens nu alle ongewenste items uitsluiten.
Deze methode houdt uw oorspronkelijke gegevens intact, garandeert sterke herhaalbaarheid bij nieuwe of bijgewerkte gegevens en is uiterst effectief voor terugkerende rapportagetaken of bij het werken met grote of externe datasets. Power Query-workflows vernieuwen met één klik zodra uw brongegevens wijzigen, waardoor handmatige tussenkomst en het risico op fouten tot een minimum worden beperkt.
- Power Query is beschikbaar in Excel 2016 en nieuwer (of als invoegtoepassing voor Excel 2010 en 2013).
- Na transformatie kunnen berekeningen worden uitgevoerd op de resulterende schone gegevens, voor nog betrouwbaardere analyses.
Bij onverwachte resultaten: controleer uw filterstappen in Power Query en zorg dat er nog geldige numerieke waarden overblijven in uw opgeschoonde gegevens.
Kort samengevat: of u nu liever direct matrixformules gebruikt, een aangepaste VBA-oplossing maakt voor automatisering of Power Query inzet voor grootschalige workflowautomatisering – Excel biedt meerdere praktische opties om de mediaan te berekenen terwijl nullen of foutwaarden worden genegeerd. Kies de methode die het beste aansluit bij de omvang van uw dataset, de updatefrequentie en uw workflowvoorkeuren voor betrouwbare en nauwkeurige resultaten.
Beste Office-productiviteitshulpmiddelen
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie zoals nooit tevoren.Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en Tijd besparen.Klik hier om de functie te krijgen die u het meest nodig heeft...
Office Tab brengt een tabbladinterface naar Office en maakt uw werk veel eenvoudiger
- Schakel tabbladbewerking en -lezen in voor Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten aan in nieuwe tabbladen binnen hetzelfde venster, in plaats van in afzonderlijke vensters.
- Verhoogt uw productiviteit met 50 % en bespaart u dagelijks honderden muisklikken!
Alle Kutools-add-ins in één installatieprogramma.
Kutools for Office bundelt add-ins voor Excel, Word, Outlook en PowerPoint, plus Office Tab Pro—ideaal voor teams die met meerdere Office-apps werken.
- Alles-in-één suite— add-ins voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro
- Één installatieprogramma, één licentie— binnen enkele minuten klaar (MSI-geschikt)
- Werkt beter samen— gestroomlijnde productiviteit in alle Office-apps
- 30 dagen volledig functionele proefversie— geen registratie, geen creditcard
- Beste prijs-kwaliteitverhouding— bespaar ten opzichte van het afzonderlijk kopen van add-ins