Wie berechnet man den Mittelwert nicht benachbarter Zellen in Excel, wobei Nullwerte ausgeschlossen werden?
Beim Arbeiten mit Excel-Daten kommt es häufig vor, dass Sie den Mittelwert einer Reihe von Zahlen berechnen müssen, wobei jedoch nicht alle relevanten Zellen direkt nebeneinander liegen und Sie zudem Nullwerte ausschließen möchten (da diese fehlende Daten oder auf null gesetzte Einträge darstellen können). Beispielsweise weisen bestimmte Artikel in einem Verkaufs- oder Lagerbestandsblatt an bestimmten Tagen möglicherweise keinen Lagerbestand oder keine Umsätze auf; in solchen Fällen liefert eine einfache Mittelwertberechnung kein zutreffendes Bild des Datentrends, da Nullen das Ergebnis verfälschen können. Ihr Ausschluss ermöglicht eine Analyse ausschließlich aktiver Datenpunkte.
Angenommen, Sie haben eine Obsttabelle wie im folgenden Screenshot und möchten den Mittelwert aller Zellen in der Spalte „Menge“ berechnen – ohne dabei Nullwerte zu berücksichtigen. Die folgenden Methoden zeigen Ihnen mehrere praktische Ansätze, wie Sie dies in Excel umsetzen können, und bieten Ihnen maximale Flexibilität – egal ob Ihre Daten in nicht benachbarten Zellen stehen, über mehrere Zeilen oder Spalten verteilt sind oder ob Sie lieber integrierte Excel-Funktionen oder einfache Formeln verwenden möchten.
- Mittelwert nicht benachbarter Zellen unter Ausschluss von Nullen mithilfe einer Formel
- Mittelwert nicht benachbarter Zellen unter Ausschluss von Nullen mithilfe eines VBA-Makros
- Mittelwert nicht benachbarter Zellen unter Ausschluss von Nullen mithilfe einer integrierten Excel-Funktion
Mittelwert nicht benachbarter Zellen unter Ausschluss von Nullen mithilfe einer Formel
Diese Methode nutzt eine Matrixformel, um schnell den Mittelwert nicht benachbarter Zellen – etwa jeder zweiten Zelle in einer Zeile oder Spalte – zu berechnen und dabei alle Nullen innerhalb des ausgewählten Bereichs auszuschließen. Sie eignet sich ideal, wenn Ihre Zielzellen einem festen Intervallmuster folgen, und ist besonders effizient bei periodischen Daten wie Werten jedes zweiten Tages oder bei regelmäßig angeordneten, aber nicht direkt benachbarten Datenpunkten.
Einschränkungen: Dieser Ansatz eignet sich am besten, wenn nicht benachbarte Zellen in regelmäßigen Abständen angeordnet sind. Für willkürliche oder manuelle Auswahlmöglichkeiten finden Sie weiter unten die formelbasierte Methode.
1. Wählen Sie eine leere Zelle aus, in der das Ergebnis angezeigt werden soll, und geben Sie die Matrixformel ein:
=AVERAGE(IF(MOD(COLUMN(C2:G2)-COLUMN(C2),2)=0,IF(C2:G2,C2:G2)))Drücken Sie anschließend gleichzeitig die Tasten Strg + Umschalt + Eingabe(für ältere Excel-Versionen; in Excel 365 oder 2019 genügt das Drücken der)Eingabetaste, da diese dynamische Matrizen unterstützen).
Hinweis: In der Formel stehen C2 und G2 für die erste und letzte Zelle Ihres Zielbereichs. Die Zahl „2“ gibt das Spaltenintervall an – passen Sie diesen Wert an, wenn Ihre nicht benachbarten Zellen durch eine andere Anzahl von Spalten getrennt sind. Passen Sie den Bereich (C2:G2) und das Intervall (2) entsprechend Ihrem Datenlayout an.
Tipps:
- Stellen Sie sicher, dass der ausgewählte Bereich alle nicht benachbarten Zellen enthält, deren Mittelwert Sie berechnen möchten.
- Wenn Ihre Datenauswahl nicht auf regelmäßigen Intervallen beruht, nutzen Sie die folgende formelbasierte Lösung für beliebige Zellen.
- Diese Formel ignoriert automatisch Leerzellen und Nullwerte und liefert Ihnen den echten Mittelwert aussagekräftiger Datenpunkte.
- Um Fehler zu vermeiden, überprüfen Sie, ob Ihre Zellbezüge korrekt sind, und stellen Sie sicher, dass Sie in älteren Excel-Versionen die erforderliche Tastenkombination für Matrixformeln verwendet haben.
Mittelwert nicht benachbarter Zellen unter Ausschluss von Nullen mithilfe eines VBA-Makros
Wenn Sie schnell den Mittelwert einer Gruppe nicht benachbarter Zellen berechnen möchten – unter Ausschluss von Nullwerten – und Ihre Auswahl keinem festen Muster folgt (z. B. weil Sie manuell bestimmte Zellen aus verschiedenen Zeilen und Spalten zusammenstellen), bietet ein VBA-Makro eine flexible und leistungsstarke Lösung. Der Code durchläuft Ihre markierten Zellen und ermittelt den Mittelwert, wobei alle Zellen mit dem Wert null automatisch ignoriert werden.
Diese Methode eignet sich hervorragend für:
- Schnelle Verarbeitung großer oder unregelmäßiger Datenauswahlen.
- Speichern Sie Ihre benutzerdefinierte Logik, um sie bei ähnlichen Datensätzen wiederholt nutzen zu können.
- Umgehung der Einschränkungen herkömmlicher Arbeitsblattformeln bei komplexen Auswahlvorgängen.
1. Klicken Sie auf Entwicklertools > Visual Basic. Klicken Sie im sich öffnenden Fenster auf Einfügen > Modul, und fügen Sie den folgenden Code in das Modul ein:
Sub AverageNonAdjacentExcludeZero()
Dim rng As Range
Dim cell As Range
Dim SumVal As Double
Dim CountVal As Long
Dim SelectedRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set SelectedRng = Application.Selection
Set SelectedRng = Application.InputBox("Select non-adjacent cells to average (exclude zeros)", xTitleId, Type:=8)
On Error Resume Next
SumVal = 0
CountVal = 0
For Each cell In SelectedRng
If IsNumeric(cell.Value) And cell.Value <> 0 Then
SumVal = SumVal + cell.Value
CountVal = CountVal + 1
End If
Next
If CountVal > 0 Then
MsgBox "Average (excluding zeros): " & SumVal / CountVal, vbInformation, xTitleId
Else
MsgBox "No non-zero numeric cells selected.", vbExclamation, xTitleId
End If
End Sub2. Klicken Sie auf die 
Schaltfläche „Ausführen“ oder drücken Sie in der VBA-Umgebung F5. Es erscheint ein Dialogfeld, in dem Sie Ihre Zellen auswählen sollen. Halten Sie die Strg-Taste gedrückt und klicken Sie mit der Maus, um nicht benachbarte Zellen auszuwählen, deren Mittelwert Sie berechnen möchten. Klicken Sie auf OK, und das Makro zeigt das Ergebnis unter Ausschluss aller Nullen an.
Mittelwert nicht benachbarter Zellen unter Ausschluss von Nullen mithilfe einer integrierten Excel-Funktion
Excel bietet zudem einige integrierte Methoden, um diese Aufgabe mit wenig oder gar keiner Formelarbeit zu erledigen – ideal für schnelle Einzelberechnungen oder visuelle Prüfungen.
Verwenden Sie die Statusleiste:
- Halten Sie Strg gedrückt und wählen Sie mit der Maus manuell jede nicht benachbarte Zelle aus, deren Mittelwert Sie berechnen möchten – dabei überspringen Sie Zellen mit Nullwerten.
- Sobald Sie eine Auswahl getroffen haben, schauen Sie in die Excel-Statusleiste unten rechts im Fenster – der Mittelwert wird automatisch angezeigt, sobald mehr als eine Zelle ausgewählt ist.
- Diese Methode eignet sich hervorragend für visuelle Überprüfungen und kleine Datensätze, gibt das Ergebnis jedoch nicht in eine Arbeitsblattzelle aus.
Berücksichtigen Sie bei der Wahl einer geeigneten Methode die Größe Ihrer Daten, wie stark Ihre ausgewählten Zellen verstreut sind und ob Sie eine wiederholbare Formelausgabe benötigen oder nur eine schnelle visuelle Prüfung durchführen möchten. Sollten Fehler auftreten (z. B.)#DIV/0!), stellen Sie sicher, dass mindestens ein Wert ungleich null in Ihrer Auswahl enthalten ist. Speichern Sie bei der Verwendung von VBA Ihre Arbeitsmappe stets, bevor Sie neue Makros ausführen – so vermeiden Sie versehentlichen Datenverlust.
Nicht benachbarte Zeilen oder Spalten (Intervalle) schnell in Begrenzter Bereich in Excel auswählen:
Mit der Kutools für Excel-Funktion Zeilen/Spalten in Intervallen auswählen können Sie, wie im folgenden Screenshot gezeigt, mühelos Intervallzeilen oder -spalten in einem begrenzten Bereich in Excel auswählen. Das ist besonders hilfreich, bevor Sie Formeln oder Makros anwenden – so treffen Sie exakt die Zellen, die Sie bearbeiten möchten.
Kutools für Excel– Erweitern Sie Excel mit über 300 unverzichtbaren Tools, um Ihre Arbeit schneller und einfacher zu erledigen, und nutzen Sie KI-Funktionen für intelligentere Datenverarbeitung und höhere Produktivität.Jetzt herunterladen
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