Splošna formula
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))
Povzetek
Če želite povprečiti zadnje 3 številske vrednosti v obsegu, lahko uporabite matrično formulo na podlagi kombinacije funkcij za podajanje zadnjih n številskih vrednosti v funkcijo AVERAGE. V prikazanem primeru je formula v D6:
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))
kjer je "podatki" imenovano območje B5: B13.
Opomba: to je matrična formula, ki jo je treba vnesti s tipko control + shift + enter.
Pojasnilo
Funkcija AVERAGE bo izračunala povprečje števil, predstavljenih v matriki, zato je skoraj vse delo v tej formuli ustvarjanje matrike zadnjih 3 številskih vrednosti v obsegu. Funkcija IF, ki deluje od znotraj navzven, se uporablja za "filtriranje" številskih vrednosti:
IF(ISNUMBER(data),ROW(data))
Funkcija ISNUMBER vrne TRUE za številske vrednosti in FALSE za druge vrednosti (vključno s praznimi mesti), funkcija ROW pa vrne številke vrstic, zato je rezultat te operacije številke vrstic polja, ki ustrezajo številskim vnosom:
(5;6;FALSE;8;9;10;FALSE;12;13)
Ta matrika gre v funkcijo LARGE s konstanto matrike (1,2,3) za k. LARGE samodejno prezre vrednosti FALSE in vrne matriko z največjimi 3 številkami, ki ustrezajo zadnjim 3 vrsticam s številskimi vrednostmi:
(13,12,10)
Ta matrika gre v funkcijo LOOKUP kot iskalna vrednost. Iskalno polje zagotavlja funkcija ROW, rezultatno polje pa je imenovani obseg "data":
LOOKUP((13,12,10), ROW(data), data))
LOOKUP nato vrne matriko, ki vsebuje ustrezne vrednosti v "podatkih", ki se podajo v AVERAGE:
=AVERAGE((100,92,90))
Obravnava manj vrednosti
Če število številskih vrednosti pade pod 3, bo ta formula vrnila napako #NUM, saj LARGE ne bo mogel vrniti 3 vrednosti, kot je zahtevano. Eden od načinov, kako to rešiti, je zamenjava trdo kodirane konstante matrike (1,2,3) z dinamično matriko, ustvarjeno z uporabo INDIRECT, kot je ta:
ROW(INDIRECT("1:"&MIN(3,COUNT(data))))
Tu se MIN uporablja za nastavitev zgornje meje polja na 3 ali dejanskega števila številskih vrednosti, kar koli je manjše.
Opomba: Ta pametni pristop sem naletel na chandoo.org v odgovoru Sajana na podobno vprašanje.
