Formula generică
=AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
rezumat
Pentru a medie ultimele 5 valori ale datelor într-un interval de coloane, puteți utiliza funcția MEDIE împreună cu funcțiile COUNT și OFFSET. În exemplul prezentat, formula din F6 este:
=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5))
Explicaţie
Funcția OFFSET poate fi utilizată pentru a construi intervale dinamice utilizând o celulă de pornire și rânduri, coloane, înălțime și lățime date.
Argumentele rândurilor și coloanelor funcționează ca „offsets” din referința de pornire. Argumentele înălțime și lățime, ambele opționale, determină câte rânduri și coloane include intervalul final. Vrem ca OFFSET să returneze un interval care începe la ultima intrare și se extinde „înapoi”, astfel încât să furnizăm argumente după cum urmează:
referință - referința inițială este D5 - celula direct în dreapta formulei și prima celulă din gama de valori cu care lucrăm.
rânduri - folosim 0 pentru argumentul rânduri, deoarece vrem să rămânem în același rând.
coloane - pentru argumentul coloane, folosim funcția COUNT pentru a număra toate valorile din interval, apoi scădem 5. Aceasta deplasează începutul intervalului 5 coloane spre stânga.
înălțime - folosim 1, deoarece dorim un interval de 1 rând ca rezultat final.
lățime - folosim 5, deoarece vrem un interval final cu 5 coloane.
Pentru formula din C5, OFFSET returnează un interval final de F5: J5. Aceasta intră în funcția MEDIE care returnează media celor 5 valori din interval
Mai puțin de 5 valori
Dacă există mai puțin de 5 valori, formula va returna o eroare de referință circulară, deoarece intervalul se va extinde înapoi în celula care conține formula. Pentru a preveni această eroare, puteți adapta formula după cum urmează:
=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))
Aici folosim funcția MIN pentru a „prinde” situații în care există mai puțin de 5 valori și folosim numărul real atunci când există.
