Formula Excel: mediați ultimele 3 valori numerice -

Formula generică

(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))

rezumat

Pentru a medie ultimele 3 valori numerice dintr-un interval, puteți utiliza o formulă matrice bazată pe o combinație de funcții pentru a introduce ultimele n valori numerice în funcția MEDIE. În exemplul prezentat, formula din D6 este:

(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))

unde „date” este intervalul numit B5: B13.

Notă: aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter.

Explicaţie

Funcția MEDIE va calcula o medie a numerelor prezentate într-o matrice, deci aproape toată activitatea din această formulă este de a genera o matrice a ultimelor 3 valori numerice dintr-un interval. Funcționând din interior spre exterior, funcția IF este utilizată pentru a „filtra” valorile numerice:

IF(ISNUMBER(data),ROW(data))

Funcția ISNUMBER returnează TRUE pentru valorile numerice și FALSE pentru alte valori (inclusiv spațiile goale), iar funcția ROW returnează numerele de rând, astfel încât rezultatul acestei operații este un număr de rânduri de matrice care corespund intrărilor numerice:

(5;6;FALSE;8;9;10;FALSE;12;13)

Această matrice intră în funcția LARGE cu constanta matricei (1,2,3) pentru k. LARGE ignoră automat valorile FALSE și returnează o matrice cu cele mai mari 3 numere, care corespund ultimelor 3 rânduri cu valori numerice:

(13,12,10)

Această matrice intră în funcția LOOKUP ca valoare de căutare. Matricea de căutare este furnizată de funcția ROW, iar matricea de rezultate este intervalul numit „date”:

LOOKUP((13,12,10), ROW(data), data))

LOOKUP returnează apoi o matrice care conține valorile corespunzătoare din „date”, care este introdusă în MEDIE:

=AVERAGE((100,92,90))

Gestionarea mai puține valori

Dacă numărul de valori numerice scade sub 3, această formulă va returna eroarea #NUM deoarece LARGE nu va putea returna 3 valori așa cum a fost solicitat. O modalitate de a rezolva acest lucru este înlocuirea constantei matricei codificate (1,2,3) cu o matrice dinamică creată folosind INDIRECT astfel:

ROW(INDIRECT("1:"&MIN(3,COUNT(data))))

Aici, MIN este utilizat pentru a seta limita superioară a matricei la 3 sau numărul real al valorilor numerice, oricare dintre acestea este mai mică.

Notă: Am întâlnit această abordare inteligentă pe chandoo.org, într-un răspuns al lui Sajan la o întrebare similară.

Articole interesante...