Hoe berekent u snel een percentiel of kwartiel in Excel terwijl u nullen negeert?
Bij het toepassen van de functies PERCENTIEL of KWARTIEL in Excel komen gebruikers vaak situaties tegen waarin hun Gegevensbereik Nulwaarden bevat. Standaard nemen deze functies nullen op in hun berekeningen, wat de resultaten aanzienlijk kan beïnvloeden door percentiel- of kwartielwaarden te verlagen, vooral als nul geen zinvolle gegevens vertegenwoordigt in de context. Voor een nauwkeurigere statistische analyse kunt u ervoor kiezen om Nulwaarden volledig te negeren bij het berekenen van percentielen of kwartielen. Deze handleiding laat u verschillende praktische technieken zien om dit probleem efficiënt op te lossen in Excel, waaronder formules met standaardfuncties, VBA-oplossingen en scenario-analyses om u te helpen de beste methode te kiezen voor uw situatie.
PERCENTIEL of KWARTIEL zonder nullen
PERCENTIEL zonder nullen (matrixformule)
Om een percentiel te berekenen terwijl u nullen negeert, gebruikt u een matrixformule die alleen waarden groter dan nul meeneemt.
Selecteer een lege cel waarin u het resultaat wilt weergeven en voer de volgende formule in:
=PERCENTILE(IF(A1:A13>0,A1:A13),0.3)Nadat u de formule hebt ingevoerd, moet u Ctrl + Shift + Enter indrukken (niet alleen Enter), omdat dit een matrixformule is. Excel plaatst accolades rond de formule { }, wat aangeeft dat deze correct is ingevoerd. In deze formule:
- A1:A13 is uw gegevensbereik – pas dit aan naar behoefte voor uw eigen werkblad.
- 0,3 geeft het 30 epercentiel aan. U kunt deze waarde aanpassen naar het percentiel dat u wilt berekenen (bijv. 0,75 voor het 75)e percentiel).
Deze methode is vooral nuttig als u wilt voorkomen dat nullen—zoals ontbrekende of ongeldige metingen—uw statistische resultaten beïnvloeden.
Let op: alleen op Enter drukken werkt niet correct; u moet Ctrl + Shift + Enter gebruiken. Bovendien kunnen formules met ALS(...) binnen aggregatiefuncties minder efficiënt zijn bij grote datasets.
KWARTIEL zonder nullen (matrixformule)
Deze aanpak is vergelijkbaar met die voor kwartielen. Selecteer een cel voor het resultaat en voer het volgende in:
=QUARTILE(IF(A1:A18>0,A1:A18),1)Na het invoeren van de formule drukt u op Ctrl + Shift + Enter om deze te bevestigen als matrixformule.
- A1:A18 is het steekproefgegevensbereik (wijzig indien nodig).
- 1betekent dat u het eerste kwartiel wilt (25)de percentiel). Gebruik 2 voor de mediaan of 3 voor het derde kwartiel (75 de percentiel).
Zorg ervoor dat uw gegevensbereik geen tekst of foutwaarden bevat, want de formule werkt uitsluitend met numerieke waarden. Deze oplossing is ideaal voor datasets van gematigde grootte waarvoor u snel een berekening nodig heeft – zonder VBA of add-ins.
VBA-macro om nullen te filteren en percentiel/kwartiel te berekenen
U kunt ook VBA (Visual Basic for Applications) gebruiken om automatisch nulwaarden te filteren en vervolgens een percentiel of kwartiel te berekenen op basis van de resterende gegevens. Deze aanpak is vooral handig bij het verwerken van grote datasets of wanneer u de procedure regelmatig wilt herhalen zonder handmatig formules te hoeven invoeren.
Toepasselijke scenario’s: Ideaal voor gevorderde gebruikers, herhalende taken of complexe bereiken. Door de code aan te passen, kunt u elk percentiel of kwartielindex en elk gegevensbereik verwerken.
1. Ga naar het tabblad Ontwikkelaarshulpmiddelen in Excel. Als dit niet zichtbaar is, klikt u met de rechtermuisknop op het lint, kiest u Het lint aanpassen en vinkt u Ontwikkelaar aan. Klik vervolgens op Ontwikkelaarshulpmiddelen > Visual Basic.
2. In het venster Microsoft Visual Basic for Applications klikt u op Invoegen > Module.
3. Kopieer en plak de volgende VBA-code in de module:
Sub FilterZeroAndPercentile()
Dim rng As Range
Dim ws As Worksheet
Dim arr As Variant
Dim filteredArr As Variant
Dim i As Long, count As Long
Dim percentileVal As Double
Dim quartileVal As Double
Dim pctl As Double
Dim quartIdx As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the data range (numbers only)", xTitleId, rng.Address, Type:=8)
If rng Is Nothing Then Exit Sub
' Prompt for percentile value (e.g., 0.75 for 75th percentile)
pctl = Application.InputBox("Enter percentile value between 0 and 1 (e.g., 0.75 for 75th percentile)", xTitleId, "0.75", Type:=1)
' Prompt for quartile index (1, 2, 3, 4)
quartIdx = Application.InputBox("Enter quartile index (e.g., 1 for first quartile)", xTitleId, "1", Type:=1)
arr = rng.Value
count = 0
' Count non-zero numbers
For i = 1 To UBound(arr, 1)
If arr(i, 1) > 0 Then
count = count + 1
End If
Next i
If count = 0 Then
MsgBox "No non-zero data found!", vbExclamation, xTitleId
Exit Sub
End If
ReDim filteredArr(1 To count)
count = 0
For i = 1 To UBound(arr, 1)
If arr(i, 1) > 0 Then
count = count + 1
filteredArr(count) = arr(i, 1)
End If
Next i
' Calculate percentile / quartile
percentileVal = Application.WorksheetFunction.Percentile(filteredArr, pctl)
quartileVal = Application.WorksheetFunction.Quartile(filteredArr, quartIdx)
MsgBox "Percentile (" & pctl & "): " & percentileVal & vbCrLf & _
"Quartile (" & quartIdx & "): " & quartileVal, vbInformation, xTitleId
End Sub4. Klik op de knop 
of druk op F5in het VBA-venster om de macro uit te voeren. Er verschijnt een prompt waarin u uw gegevensbereik (alleen getallen) moet selecteren. Vervolgens geeft u het gewenste percentiel op (bijv. 0,3 voor het 30)de percentiel) en de kwartielindex (zoals 1 voor het eerste kwartiel). De macro filtert automatisch alle nulwaarden eruit en toont de resultaten in een berichtvenster.
Voordelen: Verwerkt grote of onregelmatige datasets razendsnel, sluit nulwaarden volledig uit en maakt handmatige formule-invoer overbodig. Ideaal voor herhaald gebruik en eenvoudig aan te passen.
Nadelen: Vereist het inschakelen van macro’s en enige vertrouwdheid met VBA. Niet geschikt voor werkbladformules, tenzij omgezet naar een UDF.
Veelvoorkomende problemen en oplossingen: als u niet-numerieke cellen of foutcellen selecteert, kan de macro deze overslaan of een foutmelding tonen. Zorg ervoor dat de Gegevensbereik alleen getallen bevat met nul en positieve waarden. Als er geen niet-nulwaarden worden gevonden, krijgt u een melding.
Tip: u kunt de VBA-code verder aanpassen om de uitvoer naar een specifieke cel in een werkblad te kopiëren, berekeningsfuncties aan te passen of automatisering toe te passen op meerdere bereiken. Sla uw werkmap altijd op voordat u macro’s uitvoert of bewerkt, zodat u onbedoeld gegevensverlies voorkomt.
Wilt u deze oplossing uitbreiden naar percentiel- of kwartielberekeningen in meerdere kolommen? Pas dan de macro aan door lussen over kolommen of bereiken toe te voegen.
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