Wie berechnet man in Excel schnell Perzentile oder Quartile, ohne Nullwerte zu berücksichtigen?
Beim Einsatz der Funktionen **PERCENTIL** oder **QUARTIL** in Excel stoßen Anwender häufig auf Datenbereiche, die Nullwerte enthalten. Standardmäßig berücksichtigen diese Funktionen Nullwerte in ihren Berechnungen – was die Ergebnisse erheblich verfälschen kann, indem Perzentil- oder Quartilwerte künstlich gesenkt werden, insbesondere wenn die Null im jeweiligen Kontext keine aussagekräftigen Daten darstellt.
Für eine präzisere statistische Analyse möchten Sie Nullwerte daher möglicherweise vollständig ignorieren, wenn Sie Perzentile oder Quartile berechnen. Dieses Tutorial zeigt Ihnen mehrere praktische Methoden, um dieses Problem effizient in Excel zu lösen – von nativen Formellösungen über VBA-Ansätze bis hin zu passenden Szenariobeschreibungen, damit Sie stets die optimale Methode für Ihre Anforderungen wählen können.
PERCENTIL oder QUARTIL ohne Berücksichtigung von Nullwerten
PERCENTIL ohne Berücksichtigung von Nullwerten (Matrixformel)
Um ein Perzentil unter Ausschluss von Nullwerten zu berechnen, verwenden Sie eine Matrixformel, die ausschließlich Werte größer als null berücksichtigt.
Wählen Sie eine leere Zelle aus, in der das Ergebnis angezeigt werden soll, und geben Sie die folgende Formel ein:
=PERCENTILE(IF(A1:A13>0,A1:A13),0.3)Nach der Eingabe der Formel müssen Sie Strg + Umschalt + Enter drücken (nicht nur Enter), da es sich um eine Matrixformel handelt. Excel umschließt die Formel automatisch mit geschweiften Klammern { }, was anzeigt, dass sie korrekt eingegeben wurde. In dieser Formel:
- A1:A13 ist Ihr Datenbereich – passen Sie diesen Bereich bei Bedarf an Ihr eigenes Arbeitsblatt an.
- 0,3 gibt das 30 tePerzentil an. Sie können diesen Wert auf das gewünschte Perzentil ändern (z. B. 0,75 für das 75)te Perzentil).
Diese Methode ist besonders nützlich, wenn Sie verhindern möchten, dass Nullwerte – etwa fehlende oder ungültige Messwerte – Ihre statistischen Ergebnisse verfälschen.
Beachten Sie, dass das bloße Drücken der Enter-Taste nicht funktioniert – Sie müssen unbedingt Strg + Umschalt + Enter verwenden. Außerdem können Formeln mit WENN(...) innerhalb von Aggregatfunktionen bei großen Datensätzen weniger effizient sein.
QUARTIL ohne Berücksichtigung von Nullwerten (Matrixformel)
Dieser Ansatz ist für Quartile ähnlich: Wählen Sie eine Zelle für das Ergebnis aus und geben Sie Folgendes ein:
=QUARTILE(IF(A1:A18>0,A1:A18),1)Drücken Sie nach der Eingabe der Formel Strg + Umschalt + Enter, um sie als Matrixformel zu bestätigen.
- A1:A18 ist der Stichproben-Datenbereich (bei Bedarf anpassen).
- 1bedeutet, dass Sie das erste Quartil (25). Perzentil) berechnen möchten. Verwenden Sie 2 für den Median oder 3für das dritte Quartil (75). Perzentil).
Stellen Sie sicher, dass Ihre Datenbereich weder Text noch Fehlerzellen enthält, da die Formel nur mit numerischen Werten funktioniert. Diese Lösung eignet sich am besten für mittelgroße Datensätze, bei denen eine schnelle Berechnung ohne VBA oder Add-Ins erforderlich ist.
VBA-Makro zum Filtern und Berechnen von Perzentil/Quartil unter Ausschluss von Nullwerten
Sie können auch VBA (Visual Basic for Applications) verwenden, um automatisch Nullwerte herauszufiltern und anschließend ein Perzentil oder Quartil auf den verbleibenden Daten zu berechnen. Dieser Ansatz ist besonders praktisch bei großen Datensätzen oder wenn Sie den Vorgang häufig wiederholen möchten, ohne jedes Mal Formeln manuell eingeben zu müssen.
Anwendungsszenarien: Ideal für fortgeschrittene Anwender, sich wiederholende Aufgaben oder komplexe Bereiche. Passen Sie den Code einfach an, um beliebige Perzentil- oder Quartilindizes sowie jeden Datenbereich zu verarbeiten.
1. Wechseln Sie zur Entwicklertools-Registerkarte in Excel. Falls diese nicht sichtbar ist, klicken Sie mit der rechten Maustaste auf das Menüband, wählen Sie Das Menüband anpassen und aktivieren Sie das Kontrollkästchen Entwickler. Klicken Sie anschließend auf Entwicklertools > Visual Basic.
2. Klicken Sie im Fenster Microsoft Visual Basic for Applications auf Einfügen > Modul.
3. Kopieren Sie den folgenden VBA-Code in das Modul:
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. Klicken Sie auf die Schaltfläche 
oder drücken Sie F5im VBA-Fenster, um das Makro auszuführen. Es erscheint eine Eingabeaufforderung, in der Sie Ihren Datenbereich (nur Zahlen) auswählen und anschließend das gewünschte Perzentil angeben (z. B. 0,3 für das 30)te-Perzentil) sowie den Quartilindex (z. B. 1 für das erste Quartil). Das Makro filtert automatisch alle Nullwerte heraus und zeigt die Ergebnisse in einem Meldungsfeld an.
Vorteile: Verarbeitet große oder unregelmäßige Datensätze blitzschnell, schließt Nullwerte vollständig aus und erspart Ihnen die manuelle Eingabe komplexer Formeln. Ideal für wiederholte Nutzung und flexible Anpassung.
Nachteile: Erfordert die Aktivierung von Makros sowie grundlegende VBA-Kenntnisse. Für herkömmliche Arbeitsblattformeln nicht geeignet – es sei denn, das Makro wird in eine benutzerdefinierte Funktion (UDF) umgewandelt.
Häufige Probleme und Fehlersuche: Wenn Sie nicht-numerische Zellen oder Fehlerzellen auswählen, überspringt das Makro diese möglicherweise oder zeigt einen Fehler an. Stellen Sie sicher, dass der Datenbereich ausschließlich Zahlen mit Null- und positiven Werten enthält. Falls keine von Null verschiedenen Daten gefunden werden, erhalten Sie eine entsprechende Meldung.
Tipps: Sie können den VBA-Code weiter anpassen, um die Ausgabe in eine bestimmte Arbeitsblattzelle zu kopieren, andere Berechnungsfunktionen zu verwenden oder die Automatisierung auf mehrere Bereiche auszudehnen. Speichern Sie Ihre Arbeitsmappe stets vor dem Ausführen oder Bearbeiten von Makros, um versehentlichen Datenverlust zu vermeiden.
Wenn Sie diese Lösung auf Perzentil- oder Quartilberechnungen in mehreren Spalten ausdehnen müssen, sollten Sie das Makro so modifizieren, dass es über Spalten oder Bereiche hinweg iteriert.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins