Wie ermittelt man in Excel das korrekte Durchschnittsergebnis, wenn Werte fehlen?
Beim Arbeiten mit Daten in Excel kommt es häufig vor, dass Bereiche fehlende Werte oder Zellen enthalten, die mit Fehlerkennzeichnungen wie #N/A oder #DIV/0! gefüllt sind. Wenn Sie die Standardfunktion AVERAGE von Excel auf einen solchen Bereich anwenden, gibt die Formel einen Fehler zurück, anstatt die problematischen Zellen zu ignorieren. Sobald Ihre Daten unvollständig sind oder Fehler enthalten – wie im folgenden Screenshot dargestellt –, können Ihre Berechnungen ungültig oder unbrauchbar werden. Die Fähigkeit, den Durchschnitt präzise zu berechnen, während fehlende oder fehlerhafte Werte automatisch übersprungen werden, ist daher entscheidend: etwa in Geschäftsberichten, akademischen Forschungsarbeiten oder bei der Analyse großer und komplexer Datensätze. Im Folgenden finden Sie praktische Lösungen und Schritt-für-Schritt-Anleitungen, um in solchen Situationen stets den korrekten Durchschnitt zu ermitteln und so die Integrität Ihrer Datenanalyse sicherzustellen.
Durchschnitt bei fehlenden Werten
Um in Excel einen Durchschnitt zu berechnen und dabei fehlende Werte oder fehlerhafte Zellen zu überspringen, können Sie aus mehreren Formeln wählen, die intelligent nicht-numerische und fehlerhafte Zellen ignorieren. Dies ist beispielsweise bei der Analyse von Umfrageergebnissen (bei denen einige Einträge unvollständig sein können), bei Umsatzdaten über einen Zeitraum (mit leeren Monaten) oder bei der Erfassung wissenschaftlicher Daten (bei denen bestimmte Messwerte fehlen oder ungültig sein könnten) unerlässlich. Im Folgenden finden Sie zwei zuverlässige Formellösungen mit detaillierten Erläuterungen.
Option 1: AGGREGATE-Funktion
Die AGGREGATE-Funktion eignet sich besonders gut für Situationen, in denen Fehler oder ausgeblendete Werte in Ihrem Datenbereich auftreten können – denn Sie legen selbst fest, welche Datentypen ignoriert werden sollen. Das ist besonders hilfreich, wenn Sie Daten aus mehreren Quellen konsolidieren oder Ihr Datensatz regelmäßig mit inkonsistenten Einträgen aktualisiert wird.
1. Wählen Sie die Zelle aus, in der das Durchschnittsergebnis angezeigt werden soll, und geben Sie die folgende Formel ein:
=AGGREGATE(1,6,A2:C2)Hierbei gibt 1 die AVERAGE-Operation an, und 6 weist Excel an, Fehlerwerte im begrenzten Bereich A2:C2 zu ignorieren.
2. Drücken Sie nach Eingabe der Formel Strg + Umschalt + Enter (für ältere Excel-Versionen) oder einfach Enter, wenn Sie Excel 365 oder Excel 2019 verwenden – denn diese Versionen unterstützen dynamische Arrays ohne zusätzliche Tastenkombinationen.
Vorteile: Einfach zu bedienen; ignoriert Fehler direkt und liefert präzise Ergebnisse. Funktioniert zuverlässig bei Bereichen, die Fehlerwerte enthalten.
Einschränkungen: Verfügbar erst ab Excel 2010. Ältere Excel-Versionen unterstützen die AGGREGATE-Funktion nicht.
Option 2: AVERAGE mit IF und ISNUMBER-Arrayformel
Alternativ können Sie den Durchschnitt ausschließlich der numerischen Werte berechnen – Fehler und leere Zellen werden dabei automatisch übersprungen –, indem Sie eine Arrayformel verwenden. Dieser Ansatz ist ideal, wenn sichergestellt sein muss, dass nur gültige Zahlen einfließen, etwa in monatlichen Berichten, Notensystemen oder Finanztabellen.
1. Geben Sie in der Zelle, in der Ihr Durchschnittswert erscheinen soll, diese Formel ein:
=AVERAGE(IF(ISNUMBER(A2:C2),(A2:C2)))Diese Formel prüft jede Zelle im Bereich A2:C2, behält nur die numerischen Werte und berechnet deren Durchschnitt.
2. Halten Sie nach der Eingabe die Tasten Strg und Umschalt gedrückt und drücken Sie anschließend Enter, um sie in älteren Excel-Versionen in eine Arrayformel umzuwandeln (erkennbar an den geschweiften Klammern in der Formelzeile). In Excel 365 und Excel 2019 genügt das einfache Drücken von Enter.
Tipp: Wenn sich Ihre Daten über mehrere Zeilen erstrecken und Sie für jede Zeile ein Ergebnis benötigen, ziehen Sie die Formel einfach mithilfe des Ausfüllkästchens (das kleine Quadrat in der unteren rechten Ecke der markierten Zelle) nach unten – so passen sich die Bezüge automatisch an jede Zeile an.
Vorteile: Funktioniert in allen Excel-Versionen, ignoriert nicht-numerische Werte und Fehler und ist zuverlässig – selbst bei unterschiedlichsten Szenarien mit Fehlern oder fehlenden Werten.
Einschränkungen: In älteren Excel-Versionen muss die Formel als Arrayformel eingegeben werden, was verwirrend sein kann. Wird sie falsch eingegeben, führt dies zu einem #WERT!-Fehler.
Unabhängig davon, welche Methode Sie wählen, stellen Sie sicher, dass der Zielbereich genau nur die gewünschten Daten umfasst – unbeabsichtigte zusätzliche Spalten oder Zeilen können die Ergebnisse verfälschen. Sollten weiterhin Fehler auftreten, überprüfen Sie Ihre Formeleingabemethode (insbesondere bei Arrayformeln in älteren Excel-Versionen), vergewissern Sie sich, dass Sie den richtigen Bereich ausgewählt haben, und prüfen Sie, ob keine verbundenen oder ausgeblendeten Zellen vorliegen, die die Berechnung stören könnten.
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