Generische Formel
=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
Zusammenfassung
Um die letzten 5 Datenpunkte zu mitteln, können Sie die Funktion AVERAGE zusammen mit den Funktionen COUNT und OFFSET verwenden. Mit diesem Ansatz können Sie die letzten N Datenpunkte mitteln: letzte 3 Tage, letzte 6 Messungen usw. In dem gezeigten Beispiel lautet die Formel in F6:
=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))
Hinweis: Ein negativer Wert für die Höhe funktioniert in Google Sheets nicht. Weitere Informationen finden Sie weiter unten.
Erläuterung
Die OFFSET-Funktion kann verwendet werden, um dynamische rechteckige Bereiche basierend auf einer Startreferenz und gegebenen Zeilen, Spalten, Höhe und Breite zu erstellen. Die Zeilen- und Spaltenargumente funktionieren wie "Offsets" von der Startreferenz. Die Argumente für Höhe und Breite (beide optional) bestimmen, wie viele Zeilen und Spalten der endgültige Bereich enthält. In diesem Beispiel ist OFFSET folgendermaßen konfiguriert:
- Referenz = C3
- Zeilen = COUNT (A: A)
- cols = 0
- Höhe = -5
- width = (nicht angegeben)
Die Startreferenz wird als C3 die Zelle über den tatsächlichen Daten bereitgestellt. Da OFFSET einen Bereich zurückgeben soll, der vom letzten Eintrag in Spalte C stammt, verwenden wir die COUNT-Funktion, um alle Werte in Spalte C zu zählen und den erforderlichen Zeilenversatz zu erhalten. COUNT zählt nur numerische Werte, daher wird die Überschrift in Zeile 3 automatisch ignoriert.
Mit 8 numerischen Werten in Spalte C wird die OFFSET-Formel wie folgt aufgelöst:
OFFSET(C3,8,0,-5)
Mit diesen Werten beginnt OFFSET bei C3, versetzt 8 Zeilen nach C11 und erweitert den rechteckigen Bereich mit -5 um 5 Zeilen "rückwärts", um den Bereich C7: C11 zu erstellen.
Schließlich gibt OFFSET den Bereich C7: C11 an die Funktion AVERAGE zurück, die den Durchschnitt der Werte in diesem Bereich berechnet.
Excel vs. Sheets
Eine merkwürdige Besonderheit dieser Formel ist, dass sie mit Google Sheets nicht funktioniert, da die OFFSET-Funktion in Sheets keinen negativen Wert für Höhen- oder Breitenargumente zulässt. In der Excel-Dokumentation heißt es auch, dass Höhe oder Breite nicht negativ sein dürfen, aber es scheint, dass negative Werte in Excel seit den 90er Jahren gut funktioniert haben.
Um negative Höhen- oder Breitenwerte zu vermeiden, können Sie eine Formel wie die folgende verwenden:
=OFFSET(C4,COUNT(C:C)-5,0,5)
Hinweis C4 ist in diesem Fall die Ausgangsreferenz. Die allgemeine Form ist:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
Dabei ist A1 die erste Zelle in den Zahlen, die Sie mitteln möchten.
