Formula generică
=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
rezumat
Pentru a medie ultimele 5 puncte de date, puteți utiliza funcția MEDIE împreună cu funcțiile COUNT și OFFSET. Puteți utiliza această abordare pentru a media ultimele N puncte de date: ultimele 3 zile, ultimele 6 măsurători etc. În exemplul prezentat, formula din F6 este:
=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))
Notă: o valoare negativă pentru înălțime nu va funcționa în foile Google. Vezi mai jos pentru mai multe informații.
Explicaţie
Funcția OFFSET poate fi utilizată pentru a construi intervale dreptunghiulare dinamice pe baza unei referințe de pornire și a rândurilor, coloanelor, înălțimii și lățimii 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. Pentru acest exemplu, OFFSET este configurat astfel:
- referință = C3
- rânduri = COUNT (A: A)
- cols = 0
- înălțime = -5
- width = (nu este furnizat)
Referința inițială este furnizată ca C3 celula de deasupra datelor reale. Din moment ce dorim ca OFFSET să returneze un interval provenind de la ultima intrare din coloana C, folosim funcția COUNT pentru a număra toate valorile din coloana C pentru a obține offsetul rândului necesar. COUNT contează doar valorile numerice, astfel că antetul din rândul 3 este ignorat automat.
Cu 8 valori numerice în coloana C, formula OFFSET se rezolvă la:
OFFSET(C3,8,0,-5)
Cu aceste valori, OFFSET începe de la C3, compensează 8 rânduri la C11, apoi folosește -5 pentru a extinde gama dreptunghiulară în sus „înapoi” cu 5 rânduri pentru a crea intervalul C7: C11.
În cele din urmă, OFFSET returnează intervalul C7: C11 la funcția MEDIE, care calculează media valorilor din intervalul respectiv.
Excel vs. Foi de calcul
O ciudățenie ciudată cu această formulă este că nu va funcționa cu Foi de calcul Google, deoarece funcția OFFSET din Foi de calcul nu va permite o valoare negativă pentru argumentele înălțime sau lățime. Documentația Excel afirmă, de asemenea, că înălțimea sau lățimea nu pot fi negative, dar se pare că valorile negative au funcționat bine în Excel încă din anii 1990.
Pentru a evita valorile negative ale înălțimii sau lățimii, puteți utiliza o formulă de genul acesta:
=OFFSET(C4,COUNT(C:C)-5,0,5)
Observația C4 este referința inițială în acest caz. Forma generală este:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
unde A1 este prima celulă din numerele pe care doriți să le mediați.
