Általános képlet
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))
Összegzés
A tartomány utolsó 3 numerikus értékének átlagolásához a függvények kombinációján alapuló tömbképletet használhatja az utolsó n numerikus értéknek az ÁTLAG függvénybe történő bevitelére. A bemutatott példában a D6 képlete a következő:
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))
ahol az "adat" a B5: B13 elnevezett tartomány.
Megjegyzés: ez egy tömbképlet, amelyet a control + shift + enter billentyűkombinációval kell megadni.
Magyarázat
Az ÁTLAG függvény kiszámítja a tömbben megjelenített számok átlagát, így ebben a képletben szinte az összes munka egy tartomány utolsó 3 numerikus értékének tömbjének létrehozása. Belülről kifelé haladva az IF függvény segítségével numerikus értékeket "szűrhet":
IF(ISNUMBER(data),ROW(data))
Az ISNUMBER függvény a TRUE értéket adja vissza a numerikus értékeknél, a FALSE értéket pedig az egyéb értékeknél (beleértve az üreseket is), a ROW függvény pedig a sorok számát adja vissza, így ennek a műveletnek az eredménye egy tömb sorszám, amely megfelel a numerikus bejegyzéseknek:
(5;6;FALSE;8;9;10;FALSE;12;13)
Ez a tömb belemegy a LARGE függvénybe a k tömb konstansával (1,2,3). A LARGE automatikusan figyelmen kívül hagyja a FALSE értékeket, és egy olyan tömböt ad vissza, amelynek a legnagyobb 3 száma van, amelyek megfelelnek az utolsó 3 sornak numerikus értékekkel:
(13,12,10)
Ez a tömb a LOOKUP függvénybe kerül keresési értékként. A keresési tömböt a ROW függvény biztosítja, és az eredménytömb az elnevezett "data" tartomány:
LOOKUP((13,12,10), ROW(data), data))
A LOOKUP ezután egy tömböt ad vissza, amely az "adatok" mezőben tartalmazza a megfelelő értékeket, és amelyet az ÁTLAG:
=AVERAGE((100,92,90))
Kevesebb érték kezelése
Ha a numerikus értékek száma 3 alá csökken, ez a képlet a #NUM hibát adja vissza, mivel a LARGE nem lesz képes 3 értéket visszaadni a kért módon. Ennek egyik módja az, hogy a keményen kódolt tömbállandót (1,2,3) lecseréljük az INDIRECT használatával létrehozott dinamikus tömbre, mint ez:
ROW(INDIRECT("1:"&MIN(3,COUNT(data))))
Itt a MIN a tömb felső határának 3-ra vagy a numerikus értékek tényleges számának beállítására szolgál, amelyik kisebb.
Megjegyzés: Szembesültem ezzel az okos megközelítéssel a chandoo.org oldalon, Sajan válaszában egy hasonló kérdésre.
