Generische Formel
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))
Zusammenfassung
Um die letzten 3 numerischen Werte in einem Bereich zu mitteln, können Sie eine Array-Formel verwenden, die auf einer Kombination von Funktionen basiert, um die letzten n numerischen Werte in die AVERAGE-Funktion einzugeben. In dem gezeigten Beispiel lautet die Formel in D6:
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))
wobei "Daten" der benannte Bereich B5: B13 ist.
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden.
Erläuterung
Die AVERAGE-Funktion berechnet einen Durchschnitt der in einem Array dargestellten Zahlen, sodass fast die gesamte Arbeit in dieser Formel darin besteht, ein Array der letzten 3 numerischen Werte in einem Bereich zu generieren. Die IF-Funktion wird von innen nach außen verwendet, um numerische Werte zu "filtern":
IF(ISNUMBER(data),ROW(data))
Die ISNUMBER-Funktion gibt TRUE für numerische Werte und FALSE für andere Werte (einschließlich Leerzeichen) zurück, und die ROW-Funktion gibt Zeilennummern zurück. Das Ergebnis dieser Operation sind Array-Zeilennummern, die numerischen Einträgen entsprechen:
(5;6;FALSE;8;9;10;FALSE;12;13)
Dieses Array geht mit der Array-Konstante (1,2,3) für k in die LARGE-Funktion. LARGE ignoriert automatisch die FALSE-Werte und gibt ein Array mit den größten 3 Zahlen zurück, die den letzten 3 Zeilen mit numerischen Werten entsprechen:
(13,12,10)
Dieses Array geht als Suchwert in die LOOKUP-Funktion. Das Sucharray wird von der ROW-Funktion bereitgestellt, und das Ergebnisarray ist der benannte Bereich "data":
LOOKUP((13,12,10), ROW(data), data))
LOOKUP gibt dann ein Array mit entsprechenden Werten in "data" zurück, das in AVERAGE eingespeist wird:
=AVERAGE((100,92,90))
Mit weniger Werten umgehen
Wenn die Anzahl der numerischen Werte unter 3 fällt, gibt diese Formel den Fehler #NUM zurück, da LARGE nicht wie angefordert 3 Werte zurückgeben kann. Eine Möglichkeit, dies zu handhaben, besteht darin, die fest codierte Array-Konstante (1,2,3) durch ein dynamisches Array zu ersetzen, das mit INDIRECT wie folgt erstellt wurde:
ROW(INDIRECT("1:"&MIN(3,COUNT(data))))
Hier wird MIN verwendet, um die Obergrenze des Arrays auf 3 oder die tatsächliche Anzahl der numerischen Werte festzulegen, je nachdem, welcher Wert kleiner ist.
Hinweis: Ich bin auf chandoo.org auf diesen cleveren Ansatz gestoßen, als Sajan auf eine ähnliche Frage antwortete.
