Hoe mediaanwaarden berekenen zonder nullen of fouten in Excel?
In veel data-analysetaken in Excel is het nauwkeurig berekenen van de mediaan essentieel om de centrale tendens van uw dataset te begrijpen. Echter, soms bevat uw dataset nullen of foutwaarden (zoals #DIV/0!, #N/A, etc.), die de berekening van een eenvoudige mediaan kunnen verstoren. Het gebruik van de standaardformule =MEDIAN(range) zal bijvoorbeeld nullen meenemen in de berekening en een fout retourneren als er ongeldige cellen aanwezig zijn in het bereik, wat mogelijk kan leiden tot misleidende resultaten of fouten in de berekening, zoals hieronder geïllustreerd.
Om dit probleem op te lossen, bieden verschillende oplossingen u de mogelijkheid om de mediaan te berekenen terwijl u nullen of fouten negeert, zodat uw analyse nauwkeurig en robuust blijft. Deze oplossingen zijn geschikt voor verschillende scenario's, zoals het opschonen van enquêteresultaten, financiële rapporten of wetenschappelijke metingen waarin nullen of fouten moeten worden verwijderd voor betekenisvolle resultaten. Hieronder vindt u praktische stap-voor-stap handleidingen voor elke methode beschikbaar in Excel, 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 wilt meenemen in de berekening van de mediaan – zoals ontbrekende waarden die als 0 zijn weergegeven – kunt u gebruik maken van een matrixformule om nullen uit te sluiten. Dit is vooral handig in datasets waar nullen tijdelijke aanduidingen zijn voor ontbrekende gegevens in plaats van 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))Na het invoeren van de formule drukt u, in plaats van alleen op Enter, op Ctrl + Shift + Enter om er een matrixformule van te maken (u zult accolades zien verschijnen rond de formule in de formulebalk). Dit zorgt ervoor dat alleen de niet-nulwaarden in A2:A17 worden meegenomen in de berekening van de mediaan. Zie onderstaande schermafbeelding:
Tips:
- Als u Excel 365 of Excel 2021 en hoger gebruikt, is het voldoende om alleen op Enter te drukken, dankzij ondersteuning voor dynamische matrixberekeningen.
- Zorg ervoor dat er ten minste één niet-nul numerieke waarde in het bereik aanwezig is, anders zal de formule een #GETAL! fout retourneren.
- Deze oplossing is ideaal voor het opschonen van enquêteresultaten, onkostenvergoedingen of verkoopdata waarin nullen uit de analyse moeten worden uitgesloten.
Mediaan zonder fouten
Foutwaarden zoals #N/B, #DEEL/0! of #WAARDE! kunnen ervoor zorgen dat de standaard mediaanfunctie een fout retourneert, wat de data-analyse stillegt. Om veilig de mediaan te berekenen door deze fouten te negeren, kunt u de volgende matrixformule gebruiken.
Selecteer een willekeurige cel waarin u het resultaat wilt weergeven en voer de onderstaande formule in:
=MEDIAN(IF(ISNUMBER(F2:F17),F2:F17))Druk na het invoeren van de formule op Ctrl + Shift + Enter (tenzij u Excel 365/Excel 2021 of hoger gebruikt, waar dynamische matrices worden ondersteund). Deze formule neemt alleen waarden in F2:F17 mee die echte getallen zijn – alle foutcellen worden volledig genegeerd.
Tips en waarschuwingen:
- Als alle cellen foutwaarden bevatten, zal het resultaat een #GETAL! fout retourneren – zorg ervoor dat uw data ten minste één geldig getal bevat.
- U kunt uitsluitingscriteria combineren (bijvoorbeeld door zowel nullen als fouten uit te sluiten) door voorwaarden te nesten.
- Deze formule is vooral nuttig bij het werken met geïmporteerde gegevens, enquêteresultaten of financiële overzichten die mogelijkerwijs gedeeltelijke of mislukte berekeningen bevatten.
VBA: Mediaan zonder nullen en fouten (UDF)
Voor scenario's waarin u vaak de mediaan moet berekenen terwijl u zowel nullen als fouten negeert, of waar een oplossing nodig is die het handmatig invoeren van matrixformules vermijdt, kunt u een aangepaste VBA-functie (User-Defined Function, UDF) gebruiken. Deze aanpak biedt extra flexibiliteit omdat de aangepaste functie alle uitsluitingscriteria kan omvatten en net als elke ingebouwde formule kan worden gebruikt, wat het geschikt maakt voor grote of vaak bijgewerkte datasets.
Hoe de UDF instellen:
- Klik op het tabblad Ontwikkelaar in Excel. Als het niet beschikbaar is, schakel het dan in via Bestand > Opties > Lint aanpassen.
- 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 de UDF gebruiken:
Na terugkeer naar Excel voert u eenvoudig de formule =MedianIgnoreZeroError(A2:A17) in een willekeurige cel in (vervang A2:A17 door uw doelbereik). In tegenstelling tot matrixformules hoeft u alleen maar op Enter te drukken – er is geen noodzaak voor Ctrl + Shift + Enter.
- Deze methode werkt goed voor zeer grote datasets, vermijdt eigenaardigheden van matrixformules en kan worden aangepast om andere ongewenste waarden te negeren door de code verder te bewerken.
- Als het bereik alleen nullen of fouten bevat, zal het resultaat #GETAL! tonen.
- Als u een #NAAM? fout ontvangt, controleer dan of de VBA-macro correct is geïnstalleerd en dat macros 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 wanneer uw doel is om grote datasets schoon te maken en voor te verwerken voordat u berekeningen zoals de mediaan uitvoert. Met Power Query kunt u eenvoudig nullen en fouten filteren, zodat alleen geldige getallen overblijven in uw berekening. Deze benadering is vooral voordelig als uw brongegevens regelmatig worden bijgewerkt of geïmporteerd van externe systemen.
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 vervolgens naar het tabblad Gegevens en klik op Van tabel/bereik. Als uw gegevens nog niet in tabelvorm staan, zal Excel u vragen een tabel te maken – klik op OK.
- Het venster Power Query Editor wordt geopend. Klik op de vervolgkeuzepijl voor de relevante kolom en deselecteer 0 om nulwaarden te filteren. (Voor foutfiltering klikt u met de rechtermuisknop op de kolomkop, en kiest u Fouten verwijderen.)
- Nadat u hebt gefilterd, klikt u op Start > Sluiten en laden om de opgeschoonde gegevens terug te sturen naar uw werkblad.
- Pas nu de standaard
=MEDIAAN()formule toe op de kolom met gefilterde waarden alleen, aangezien de gegevens nu alle ongewenste items uitsluiten.
Deze methode zorgt ervoor dat uw originele gegevens ongewijzigd blijven, biedt sterke herhaalbaarheid met nieuwe of bijgewerkte gegevens, en is vooral effectief voor terugkerende rapportagetaken of bij het werken met grote of externe datasets. Power Query workflows kunnen met één klik worden vernieuwd wanneer uw brongegevens veranderen, wat handmatige tussenkomst en risico's op fouten minimaliseert.
- 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 opgeschoonde gegevens, wat meer betrouwbaarheid biedt voor downstreamanalyse.
Als onverwachte resultaten optreden, controleer dan uw filterstappen in Power Query en bevestig dat er geldige numerieke waarden overblijven in uw opgeschoonde gegevens.
Samenvattend biedt Excel meerdere praktische opties om de mediaan te berekenen terwijl u nullen of fouten negeert, of u nu liever rechtstreeks matrixformules gebruikt, een aangepaste VBA-oplossing creëert voor automatisering, of Power Query gebruikt voor grotere workflowautomatisering. Kies de methode die het beste past bij de grootte van uw dataset, de frequentie van updates en uw workflowvoorkeuren voor betrouwbare en nauwkeurige resultaten.
Beste productiviteitstools voor Office
Verbeter je Excel-vaardigheden met Kutools voor Excel en ervaar ongeëvenaarde efficiëntie. Kutools voor Excel biedt meer dan300 geavanceerde functies om je productiviteit te verhogen en tijd te besparen. Klik hier om de functie te kiezen die je het meest nodig hebt...
Office Tab brengt een tabbladinterface naar Office en maakt je werk veel eenvoudiger
- Activeer tabbladbewerking en -lezen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
- Verhoog je productiviteit met50% en bespaar dagelijks honderden muisklikken!
Alle Kutools-invoegtoepassingen. Eén installatieprogramma
Kutools for Office-suite bundelt invoegtoepassingen voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro, ideaal voor teams die werken met Office-toepassingen.
- Alles-in-één suite — invoegtoepassingen voor Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Eén installatieprogramma, één licentie — in enkele minuten geïnstalleerd (MSI-ready)
- Werkt beter samen — gestroomlijnde productiviteit over meerdere Office-toepassingen
- 30 dagen volledige proef — geen registratie, geen creditcard nodig
- Beste prijs — bespaar ten opzichte van losse aanschaf van invoegtoepassingen