Üldine valem
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))
Kokkuvõte
Vahemiku kolme viimase numbrilise väärtuse keskmistamiseks võite funktsioonide kombinatsioonil põhineva massiivivalemi abil sisestada viimased n arvväärtust funktsiooni AVERAGE. Näidatud näites on valem D6-s:
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))
kus "andmed" on nimega vahemik B5: B13.
Märkus: see on massiivi valem ja see tuleb sisestada juhtklahviga + shift + enter.
Selgitus
Funktsioon AVERAGE arvutab keskmise massiivi arvude arvu, nii et peaaegu kogu selle valemi töö seisneb selles, et genereerida vahemiku viimase kolme arvväärtuse massiiv. Seestpoolt välja töötades kasutatakse IF-funktsiooni arvväärtuste "filtreerimiseks":
IF(ISNUMBER(data),ROW(data))
Funktsioon ISNUMBER tagastab arvväärtuste puhul TÕENE ja muude väärtuste (sh tühjad) puhul VÄÄR ning funktsioon ROW tagastab rea numbrid, nii et selle toimingu tulemuseks on massiivirea numbrid, mis vastavad numbrikirjetele:
(5;6;FALSE;8;9;10;FALSE;12;13)
See massiiv läheb funktsiooni LARGE k massiivi konstandiga (1,2,3). LARGE ignoreerib FALSE väärtusi automaatselt ja tagastab massiivi, millel on suurimad kolm numbrit, mis vastavad kolmele viimasele arvväärtusega reale:
(13,12,10)
See massiiv läheb otsingu väärtusena funktsiooni LOOKUP. Otsimismassiivi pakub funktsioon ROW ja tulemuste massiiv on nimega vahemik "andmed":
LOOKUP((13,12,10), ROW(data), data))
Seejärel tagastab LOOKUP massiivi, mis sisaldab vastavaid väärtusi jaotises "andmed", mis sisestatakse keskmisele:
=AVERAGE((100,92,90))
Vähem väärtusi käsitleda
Kui arvväärtuste arv langeb alla 3, tagastab see valem tõrke #NUM, kuna LARGE ei saa kolme väärtust vastavalt soovile tagastada. Üks viis selle lahendamiseks on asendada kõvakodeeritud massiivikonstant (1,2,3) dünaamilise massiiviga, mis on loodud INDIRECT-i abil:
ROW(INDIRECT("1:"&MIN(3,COUNT(data))))
Siin kasutatakse MIN massiivi ülemise piiri määramiseks 3 või tegelike arvväärtuste arvu, olenevalt sellest, kumb on väiksem.
Märkus: Sajanni vastuses samalaadsele küsimusele sattusin selle nutika lähenemisviisi juurde saidil chandoo.org.
