Generische Formel
=AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
Zusammenfassung
Um die letzten 5 Datenwerte in einem Spaltenbereich zu mitteln, können Sie die Funktion AVERAGE zusammen mit den Funktionen COUNT und OFFSET verwenden. In dem gezeigten Beispiel lautet die Formel in F6:
=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5))
Erläuterung
Die OFFSET-Funktion kann verwendet werden, um dynamische Bereiche unter Verwendung einer Startzelle und vorgegebener Zeilen, Spalten, Höhe und Breite zu erstellen.
Die Zeilen- und Spaltenargumente funktionieren wie "Offsets" von der Startreferenz. Die optionalen Argumente für Höhe und Breite bestimmen, wie viele Zeilen und Spalten der endgültige Bereich enthält. Wir möchten, dass OFFSET einen Bereich zurückgibt, der beim letzten Eintrag beginnt und "rückwärts" erweitert wird, sodass wir folgende Argumente liefern:
Referenz - die Startreferenz ist D5 - die Zelle direkt rechts von der Formel und die erste Zelle im Wertebereich, mit dem wir arbeiten.
Zeilen - Wir verwenden 0 für das Zeilenargument, weil wir in derselben Zeile bleiben möchten.
Spalten - Für das Spaltenargument verwenden wir die COUNT-Funktion, um alle Werte im Bereich zu zählen und dann 5 zu subtrahieren. Dadurch wird der Anfang der Spalten des Bereichs 5 nach links verschoben.
Höhe - Wir verwenden 1, da wir als Endergebnis einen 1-Zeilen-Bereich wünschen.
width - wir verwenden 5, da wir einen endgültigen Bereich mit 5 Spalten wollen.
Für die Formel in C5 gibt OFFSET einen endgültigen Bereich von F5: J5 zurück. Dies geht in die AVERAGE-Funktion, die den Durchschnitt der 5 Werte im Bereich zurückgibt
Weniger als 5 Werte
Wenn weniger als 5 Werte vorhanden sind, gibt die Formel einen Zirkelreferenzfehler zurück, da der Bereich bis in die Zelle zurückreicht, die die Formel enthält. Um diesen Fehler zu vermeiden, können Sie die Formel wie folgt anpassen:
=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))
Hier verwenden wir die MIN-Funktion, um Situationen mit weniger als 5 Werten zu "fangen" und die tatsächliche Anzahl zu verwenden, wenn es solche gibt.
