Wie berechnet man den Median, ohne Nullen oder Fehler in Excel zu berücksichtigen?
Bei vielen Aufgaben der Datenanalyse in Excel ist die genaue Berechnung des Medians entscheidend, um die zentrale Tendenz Ihres Datensatzes zu verstehen. Allerdings enthält Ihr Datensatz manchmal Nullen oder Fehlerwerte (wie #DIV/0!, #N/Ausw.), die die direkte Median-Berechnung beeinträchtigen können. Zum Beispiel wird die Verwendung der Standardformel =MEDIAN(range) Nullen in die Berechnung einbeziehen und einen Fehler zurückgeben, wenn im Bereich ungültige Zellen vorhanden sind, was möglicherweise zu irreführenden Ergebnissen oder Berechnungsfehlern führt, wie nachfolgend veranschaulicht.
Um dies zu beheben, gibt es mehrere Lösungen, die Ihnen helfen, den Median zu berechnen, während Sie Nullen oder Fehler ignorieren, wodurch Ihre Analyse sowohl genau als auch robust bleibt. Diese Lösungen eignen sich für verschiedene Szenarien, wie das Bereinigen von Umfrage-Daten, Finanzberichten oder wissenschaftlichen Messungen, bei denen Nullen oder Fehler für aussagekräftige Ergebnisse entfernt werden müssen. Im Folgenden finden Sie praktische Schritt-für-Schritt-Anleitungen für jede in Excel verfügbare Methode – von einfachen Formeln bis hin zu fortgeschrittenen Automatisierungstechniken.
VBA: Median ohne Nullen und Fehler (UDF)
Power Query: Median nach dem Filtern von Nullen/Fehlern
Median ohne Nullen
Wenn Ihr Bereich Nullen enthält, die Sie bei der Berechnung des Medians nicht berücksichtigen möchten – wie fehlende Werte, die als 0 dargestellt werden – können Sie eine Array-Formel verwenden, um Nullen auszuschließen. Dies ist besonders nützlich in Datensätzen, in denen Nullen Platzhalter für nicht verfügbare Daten anstelle tatsächlicher Messungen sind.
Wählen Sie eine Zelle, in der Sie den Median anzeigen möchten (z. B. C2), und geben Sie die folgende Formel ein:
=MEDIAN(IF(A2:A17<>0,A2:A17))Nachdem Sie die Formel eingegeben haben, drücken Sie stattdessen Strg + Umschalt + Enter um daraus eine Array-Formel zu machen (Sie sehen geschweifte Klammern um die Formel in der Formelleiste). Dadurch werden nur die Nicht-Null-Werte in A2:A17 für die Median-Berechnung berücksichtigt. Siehe Screenshot:
Tipps:
- Wenn Sie Excel 365 oder Excel 2021 und höher verwenden, reicht ein einfaches Drücken der Eingabetaste dank dynamischer Array-Unterstützung aus.
- Stellen Sie sicher, dass es mindestens einen Nicht-Null-Zahlenwert im Bereich gibt, sonst gibt die Formel einen #ZAHL!-Fehler zurück.
- Diese Lösung ist ideal zum Bereinigen von Umfrageantworten, Spesenabrechnungen oder Verkaufsdaten, bei denen Nullen von der Analyse ausgeschlossen werden sollen.
Median ohne Fehler
Fehlerwerte wie #NV, #DIV/0! oder #WERT! können dazu führen, dass die Standard-Median-Funktion einen Fehler zurückgibt, was Ihre Datenanalyse unterbricht. Um den Median sicher zu berechnen, indem Sie diese Fehler ausschließen, können Sie die folgende Array-Formel verwenden.
Wählen Sie eine beliebige Zelle, in der Sie Ihr Ergebnis anzeigen möchten, und geben Sie die folgende Formel ein:
=MEDIAN(IF(ISNUMBER(F2:F17),F2:F17))Nachdem Sie die Formel eingegeben haben, drücken Sie Strg + Umschalt + Enter (sofern Sie nicht Excel 365/Excel 2021 oder höher verwenden, das dynamische Arrays unterstützt). Diese Formel berücksichtigt nur diejenigen Werte in F2:F17 die echte Zahlen sind – Fehlerzellen werden vollständig ignoriert.
Tipps und Vorsichtsmaßnahmen:
- Wenn alle Zellen Fehlerwerte enthalten, wird als Ergebnis ein #ZAHL!-Fehler zurückgegeben – stellen Sie sicher, dass Ihre Daten mindestens eine gültige Zahl enthalten.
- Sie können Ausschlusskriterien kombinieren (z. B. Nullen und Fehler gleichzeitig ausschließen) durch verschachtelte Bedingungen.
- Diese Formel ist besonders hilfreich beim Arbeiten mit importierten Daten, Umfrageergebnissen oder Finanzauswertungen, die teilweise oder fehlgeschlagene Berechnungen enthalten könnten.
VBA: Median ohne Nullen und Fehler (UDF)
Für Situationen, in denen Sie häufig den Median berechnen müssen, während Sie sowohl Nullen als auch Fehler ignorieren, oder wenn Sie eine Lösung benötigen, die das manuelle Eingeben von Array-Formeln vermeidet, können Sie eine benutzerdefinierte VBA-Funktion (User-Defined Function, UDF) verwenden. Dieser Ansatz bietet zusätzliche Flexibilität, da die benutzerdefinierte Funktion alle Ignorierungs-Kriterien kapseln und wie jede integrierte Formel verwendet werden kann, was sie ideal für große oder regelmäßig aktualisierte Datensätze macht.
So richten Sie die UDF ein:
- Klicken Sie auf die Entwickler-Registerkarte in Excel. Wenn sie nicht verfügbar ist, aktivieren Sie sie über Datei > Optionen > Menüband anpassen.
- Klicken Sie auf Visual Basic, um den VBA-Editor zu öffnen.
- Klicken Sie im VBA-Editor auf Einfügen > Modul, um ein neues Modul zu erstellen.
- Kopieren Sie den folgenden Code in das Modul:
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 FunctionSo verwenden Sie die UDF:
Gehen Sie nach Excel zurück und geben Sie einfach die Formel =MedianIgnoreZeroError(A2:A17) in eine beliebige Zelle ein (ersetzen Sie A2:A17 durch Ihren Zielbereich). Anders als bei Array-Formeln müssen Sie nur die Eingabetaste drücken – es ist kein Strg + Umschalt + Enter.
- notwendig. Diese Methode funktioniert gut für sehr große Datensätze, vermeidet Eigenheiten von Array-Formeln und kann angepasst werden, um andere unerwünschte Werte zu ignorieren, indem Sie den Code weiter bearbeiten.
- Wenn der Bereich nur Nullen oder Fehler enthält, wird als Ergebnis #ZAHL! angezeigt.
- Wenn Sie einen #NAME?-Fehler erhalten, überprüfen Sie, ob das VBA-Makro korrekt installiert ist und Makros in Ihren Excel-Einstellungen aktiviert sind.
Power Query: Median nach dem Filtern von Nullen/Fehlern
Power Query ist ein leistungsstarkes Werkzeug in Excel zum Importieren, Transformieren und Analysieren von Daten – insbesondere wenn Ihr Ziel ist, große Datensätze vor Durchführung von Berechnungen wie dem Median zu bereinigen und vorzuverarbeiten. Mit Power Query können Sie Nullen und Fehler leicht herausfiltern, sodass nur gültige Zahlen in Ihrer Berechnung verbleiben. Dieser Ansatz ist besonders vorteilhaft, wenn Ihre Quelldaten regelmäßig aktualisiert oder aus externen Systemen importiert werden.
Schritte zur Verwendung von Power Query zur Berechnung des Medians unter Ignorieren von Nullen und Fehlern:
- Wählen Sie eine beliebige Zelle in Ihrem Datenbereich aus, gehen Sie dann zur Registerkarte Daten und klicken Sie auf Aus Tabelle/Bereich. Wenn Ihre Daten noch nicht im Tabellenformat vorliegen, fordert Excel Sie auf, eine Tabelle zu erstellen – klicken Sie auf OK.
- Das Power Query Editor-Fenster wird geöffnet. Klicken Sie auf den Dropdown-Pfeil für die relevante Spalte und deaktivieren Sie 0, um Nullwerte herauszufiltern. (Zur Fehlerfilterung klicken Sie mit der rechten Maustaste auf den Spaltenkopf und wählen Fehler entfernen.)
- Sobald gefiltert wurde, klicken Sie auf Start > Schließen und Laden, um die bereinigten Daten zurück in Ihr Arbeitsblatt zu laden.
- Wenden Sie jetzt die Standard
=MEDIAN()-Formel auf die Spalte mit den gefilterten Werten an, da die Daten nun alle unerwünschten Elemente ausschließen.
Diese Methode stellt sicher, dass Ihre Originaldaten unverändert bleiben, bietet starke Wiederholbarkeit bei neuen oder aktualisierten Daten und ist besonders effektiv für wiederkehrende Berichtsaufgaben oder beim Arbeiten mit großen oder externen Datensätzen. Power Query-Arbeitsabläufe können mit einem einzigen Klick aktualisiert werden, sobald sich Ihre Quelldaten ändern, wodurch manuelle Eingriffe und Fehlerquellen minimiert werden.
- Power Query ist in Excel 2016 und neuer verfügbar (oder als Add-In für Excel 2010 und 2013).
- Nach der Transformation können Berechnungen an den resultierenden sauberen Daten durchgeführt werden, was eine größere Zuverlässigkeit für nachgelagerte Analysen bietet.
Wenn unerwartete Ergebnisse auftreten, überprüfen Sie Ihre Filterungsschritte in Power Query und bestätigen Sie, dass in Ihren bereinigten Daten noch gültige numerische Werte übrig sind.
Zusammengefasst bietet Excel, egal ob Sie lieber direkt Array-Formeln verwenden, eine benutzerdefinierte VBA-Lösung für Automatisierung erstellen oder Power Query für größere Workflow-Automatisierung nutzen, mehrere praktikable Optionen zur Berechnung des Medians, während Nullen oder Fehler ignoriert werden. Wählen Sie die Methode, die am besten zu Ihrer Datensatzgröße, Aktualisierungshäufigkeit und Ihren Workflow-Präferenzen passt, um zuverlässige und genaue Ergebnisse zu erzielen.
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins