Povzetek
Za izračun drsečega ali drsečega povprečja lahko uporabite preprosto formulo, ki temelji na funkciji AVERAGE z relativnimi referencami. V prikazanem primeru je formula v E7:
=AVERAGE(C5:C7)
Ko formulo kopiramo navzdol, izračuna 3-dnevno drseče povprečje na podlagi prodajne vrednosti za tekoči dan in dva prejšnja dneva.
Spodaj je bolj prilagodljiva možnost, ki temelji na funkciji OFFSET, ki obravnava spremenljiva obdobja.
O drsečih povprečjih
Drseče povprečje (imenovano tudi drsno povprečje) je povprečje, ki temelji na podmnožicah podatkov v določenih intervalih. Izračun povprečja v določenih intervalih zgladi podatke z zmanjšanjem vpliva naključnih nihanj. Tako je lažje videti splošne trende, zlasti v grafikonu. Večji kot je interval za izračun drsečega povprečja, bolj gladko se zgodi, saj je v vsako izračunano povprečje vključenih več podatkovnih točk.
Pojasnilo
Vse formule, prikazane v primeru, uporabljajo funkcijo AVERAGE z relativno referenco, nastavljeno za vsak določen interval. 3-dnevno drseče povprečje v E7 se izračuna tako, da se povprečno poda povprečje, ki vključuje trenutni dan in dva prejšnja dneva, kot je ta:
=AVERAGE(C5:C7) // 3-day average
Petdnevno in sedemdnevno povprečje se izračuna na enak način. V vsakem primeru je obseg, ki ga dobite AVERAGE, povečan, tako da vključuje zahtevano število dni:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Vse formule uporabljajo relativno referenco za obseg, dobavljen funkciji AVERAGE. Ko se formule kopirajo v stolpec, se obseg spreminja v vsaki vrstici in vključuje vrednosti, potrebne za vsako povprečje.
Ko so vrednosti vrisane v črtni grafikon, je učinek glajenja jasen:
Nezadostni podatki
Če formule začnete v prvi vrstici tabele, prvih nekaj formul ne bo imelo dovolj podatkov za izračun celotnega povprečja, ker se bo obseg razširil nad prvo vrstico podatkov:
To je lahko težava ali ne, odvisno od strukture delovnega lista in ali je pomembno, da vsa povprečja temeljijo na enakem številu vrednosti. Funkcija AVERAGE bo samodejno prezrla besedilne vrednosti in prazne celice, zato bo še naprej izračunavala povprečje z manj vrednostmi. To je razlog, zakaj "deluje" v E5 in E6.
Eden od načinov za jasno označitev nezadostnih podatkov je preverjanje trenutne številke vrstice in prekinitev z #NA, če je vrednosti manj kot n. Na primer, za 3-dnevno povprečje lahko uporabite:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
Prvi del formule preprosto ustvari "normalizirano" številko vrstice, začenši z 1:
ROW()-ROW($C$5)+1 // relative row number
V vrstici 5 je rezultat 1, v vrstici 6 je rezultat 2 itd.
Ko je številka trenutne vrstice manjša od 3, formula vrne # N / A. V nasprotnem primeru formula vrne drseče povprečje kot prej. To posnema vedenje različice Moving Average, ki vsebuje orodje za analizo, ki prikazuje # N / A, dokler ni doseženo prvo popolno obdobje.
Ko pa se bo število obdobij povečalo, vam bo sčasoma zmanjkalo vrstic nad podatki in ne boste mogli vnesti zahtevanega obsega znotraj AVERAGE. Na primer, z delovnim listom ne morete nastaviti drsečega 7-dnevnega povprečja, kot je prikazano, saj ne morete vnesti obsega, ki se razteza 6 vrstic nad C5.
Spremenljiva obdobja z OFFSET
Prilagodljivejši način izračuna drsečega povprečja je s funkcijo OFFSET. OFFSET lahko ustvari dinamični razpon, kar pomeni, da lahko nastavimo formulo, pri kateri je število obdobij spremenljivo. Splošna oblika je:
=AVERAGE(OFFSET(A1,0,0,-n,1))
kjer je n število obdobij, ki jih je treba vključiti v vsako povprečje. Kot zgoraj, OFFSET vrne obseg, ki se prenese v funkcijo AVERAGE. Spodaj lahko vidite to formulo v akciji, kjer je "n" imenovani obseg E2. Od celice C5 OFFSET oblikuje obseg, ki se razteza nazaj na prejšnje vrstice. To dosežemo z uporabo višine, ki je enaka negativnemu n. Ko spremenite E5 na drugo številko, se drseče povprečje preračuna v vseh vrsticah:
Kopirana formula v E5 je:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Tako kot prvotna formula zgoraj, bo tudi pri različici z OFFSET težava v prvih nekaj vrsticah premajhna, odvisno od tega, koliko obdobij je podanih v E5.
V prikazanem primeru povprečja uspešno izračunajo, ker funkcija AVERAGE samodejno prezre besedilne vrednosti in prazne celice, nad C5 pa ni drugih številskih vrednosti. Torej, medtem ko je obseg, ki je v E5 prešel v AVERAGE, C1: C5, je povprečna le ena vrednost, 100. Ko pa se obdobja povečujejo, bo OFFSET še naprej ustvarjal obseg, ki se razteza nad začetkom podatkov in sčasoma naleti na vrhu delovnega lista in vrne napako #REF.
Ena od rešitev je, da velikost obsega omejite na število razpoložljivih podatkovnih točk. To lahko storite z uporabo funkcije MIN, da omejite število uporabljenih višin, kot je prikazano spodaj:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
To se zdi precej zastrašujoče, vendar je pravzaprav precej preprosto. S funkcijo MIN omejujemo višino, ki je bila podana v OFFSET:
MIN(ROW()-ROW($C$5)+1,n)
Znotraj MIN je prva vrednost relativno število vrstic, izračunano z:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
Druga vrednost, dana MIN, je število obdobij, n. Ko je relativna številka vrstice manjša od n, MIN vrne trenutno številko vrstice v OFFSET za višino. Ko je številka vrstice večja od n, MIN vrne n. Z drugimi besedami, MIN preprosto vrne manjšo od obeh vrednosti.
Lepa lastnost možnosti OFFSET je, da je n mogoče enostavno spremeniti. Če spremenimo n na 7 in narišemo rezultate, dobimo tabelo, kot je ta:
Opomba: Zanimiva je zgornja formula OFFSET, da ne bodo delovale v Google Sheets, ker funkcija OFFSET v Sheets ne bo dovoljevala negativne vrednosti za višino ali širino. Priložena preglednica vsebuje formule za rešitev za Googlove liste.
