Mittelwert wenn nicht leer – Excel & Google Sheets
Die Beispielarbeitsmappe herunterladen
In diesem Tutorial zeigen wir Ihnen, wie Sie den Mittelwert einer Reihe von Zahlen berechnen können und dabei leere Werte oder Kategorien in Excel und Google Sheets ignorieren.
Leere Werte mit der MITTELWERT-Funktion ignorieren
Die MITTELWERT-Funktion ignoriert automatisch alle Zellen, die leer sind oder Text enthalten.
In diesem Beispiel wird die MITTELWERT-Funktion zur Berechnung des Mittelwerts verwendet, wobei der Text und die leeren Werte in Spalte C ignoriert werden:
=MITTELWERT(C3:C7)
Textwerte mit der MITTELWERTA-Funktion als Null behandeln
Um Textwerte bei der Berechnung als Null zu behandeln, verwenden Sie die MITTELWERTA-Funktion.
Beachten Sie, wie der Textwert „No Data“ in diesem Beispiel in die Mittelwertberechnung einbezogen wird.
=MITTELWERTA(C3:C7)
In diesem Beispiel wird der Mittelwert der Werte 4, 0, 5 und 3 berechnet.
Ignorieren der Leerwertkategorie mit MITTELWERTWENNS
In diesem Beispiel wird die MITTELWERTWENNS-Funktion verwendet, um den Mittelwert für Teams mit nicht leeren Namen zu berechnen, wobei Werte, die Textwerten entsprechen, ignoriert werden.
=MITTELWERTWENNS(C3:C7;B3:B7;"<>")
Leerwertkategorie ignorieren – Array
Wenn Sie keinen Zugriff auf die MITTELWERTWENNS-Funktion haben, können Sie eine verschachtelte MITTELWERT- und WENN-Funktion verwenden.
Dieses Beispiel berechnet den Mittelwert für Teams mit nicht leeren Namen und ignoriert jeden Wert, der einem Text entspricht.
{=MITTELWERT(WENN(B3:B7<>"";C3:C7))}
Diese Formel verwendet die WENN-Funktion, um festzustellen, ob der Name jedes Teams nicht leer ist und verwendet in der MITTELWERT-Funktion nur die Punktzahlen von Teams mit nicht leeren Namen. Der Wert 100 wird ignoriert, da der Team-Name leer ist. Die Punktzahl „No Data“ wird ignoriert, da es sich um keinen numerischen Wert handelt.
In Excel-Versionen von 2019 und früher muss diese Formel als Array-Formel eingegeben werden, indem Sie STRG + UMSCHALTTASTE + EINGABE drücken. In späteren Versionen ist dies nicht erforderlich.
Dieses Beispiel kann auch mit einer MITTELWERTWENNS-Funktion, wie unten gezeigt, berechnet werden.
Leerwertkategorie ignorieren und Text als 0 behandeln
Um Textwerte als 0 zu behandeln und gleichzeitig Teams mit leeren Namen zu ignorieren, können wir eine verschachtelte MITTELWERTA- und WENN-Funktion verwenden:
{=MITTELWERTA(WENN(B3:B7<>"";C3:C7))}
Diese Formel verwendet die WENN-Funktion, um festzustellen, ob der Name jedes einzelnen Teams nicht leer ist, und verwendet nur die Ergebnisse von Teams mit nicht leeren Namen in der MITTELWERTA-Funktion. Der Wert 100 wird ignoriert, da der Team-Name leer ist. Die Punktzahl ‚No Data‘ wird von der MITTELWERTA-Funktion als 0 behandelt, so dass sich ein Mittelwert von 3,0 ergibt.
In Excel-Versionen von 2019 und früher muss diese Formel als Array-Formel eingegeben werden, indem Sie STRG + UMSCHALTTASTE + EINGABE drücken. In späteren Versionen ist dies nicht erforderlich.
Mittelwert wenn nicht leer – Google Sheets
Die meisten dieser Formelbeispiele funktionieren in Google Sheets auf die gleiche Weise wie in Excel, allerdings mit einigen Ausnahmen:
Immer wenn eine WENN-Funktion in einer anderen Funktion verschachtelt ist und auf einen Zellenbereich verweist, muss Google Sheets die Formel als eine Array-Formel behandeln. Daher müssen Sie die ARRAYFORMULA-Funktion verwenden:
=ArrayFormula(MITTELWERT(WENN(B3:B7<>"";C3:C7)))
Die Funktion MITTELWERTA funktioniert in Google Sheets anders und daher kann das Beispiel von =MITTELWERTA(WENN(B3:B7<>““;C3:C7)) nicht verwendet werden. Das liegt daran, dass die WENN-Funktion einen Nullwert erzeugt, wenn sie einen leeren Wert im Bereich B3:B7 findet. Google Sheets zählt diesen als 0, anstatt ihn wie in Excel zu ignorieren.
