Excel

Formula drsečega povprečja

Moving Average Formula

Formula Excel: Formula drsečega povprečjaPovzetek

Za izračun drsečega ali tekočega povprečja lahko uporabite preprosto formulo, ki temelji na Funkcija POVPREČNO z relativnimi referencami. V prikazanem primeru je formula v E7:



excel zadnji dan v mesecu
 
= AVERAGE (C5:C7)

Ko se formula kopira, izračuna tridnevno drseče povprečje na podlagi vrednosti prodaje za tekoči dan in dva prejšnja dneva.

Spodaj je bolj prilagodljiv možnost, ki temelji na OFFSET funkcija ki obravnava različna obdobja.





O drsečih povprečjih

Drseče povprečje (imenovano tudi drseče povprečje) je povprečje, ki temelji na podskupinah podatkov v danih časovnih presledkih. Izračun povprečja v določenih časovnih presledkih gladi 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, tem bolj pride do glajenja, saj je v vsako izračunano povprečje vključeno več podatkovnih točk.

Pojasnilo

Formule, prikazane v primeru, vse uporabljajo funkcijo AVERAGE z relativno referenco, nastavljeno za vsak določen interval. Tridnevno drseče povprečje v E7 se izračuna tako, da se v povprečju vnese obseg, ki vključuje trenutni dan in dva prejšnja dneva, kot je ta:



 
= AVERAGE (C5:C7) // 3-day average

5-dnevno in 7-dnevno povprečje se izračuna na enak način. V vsakem primeru se obseg, ki je na voljo za AVERAGE, poveča tako, da vključuje zahtevano število dni:

 
= AVERAGE (C5:C7) // 5-day average = AVERAGE (C5:C11) // 7-day average

Vse formule uporabljajo a relativna referenca za obseg, ki je na voljo funkciji AVERAGE. Ko se formule kopirajo navzdol po stolpcu, se obseg v vsaki vrstici spremeni tako, da vključuje vrednosti, potrebne za vsako povprečje.

Ko so vrednosti vrisane v linijski grafikon, je učinek glajenja jasen:

Primer grafikona drsečega povprečja

Nezadostni podatki

Če zaženete formule v prvi vrstici tabele, prvih nekaj formul ne bo imelo dovolj podatkov za izračun celotnega povprečja, ker se bo obseg razširil zgoraj prva vrstica podatkov:

Težava z drsečim povprečjem

To je lahko ali pa tudi ne, odvisno od strukture delovnega lista in od tega, 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čunala povprečje z manj vrednostmi. Zato 'deluje' v E5 in E6.

Eden od načinov za jasno označevanje nezadostnih podatkov je, da preverite trenutno številko vrstice in prekinete z #NA, če je manj kot n vrednosti. Za tridnevno povprečje lahko na primer uporabite:

 
= IF ( ROW ()- ROW ($C)+1<3, NA (), AVERAGE (C3:C5))

Prvi del formule preprosto ustvari 'normalizirano' številko vrstice, začenši z 1:

 
 ROW ()- ROW ($C)+1 // relative row number

V 5. vrstici je rezultat 1, v 6. vrstici je rezultat 2 itd.

Ko je trenutna številka vrstice manjša od 3, formula vrne #N/A. V nasprotnem primeru formula vrne drseče povprečje kot prej. To posnema obnašanje različice drsečega povprečja orodja za analizo, ki oddaja #N/A, dokler ni doseženo prvo popolno obdobje.

Drseče povprečje z #n/a za premalo podatkov

Ker pa se število obdobij povečuje, vam bo sčasoma zmanjkalo vrstic nad podatki in ne boste mogli vnesti zahtevanega obsega znotraj AVERAGE. Na primer, z delovnim listom, kot je prikazano, ne morete nastaviti drsečega 7-dnevnega povprečja, saj ne morete vnesti obsega, ki sega 6 vrstic nad C5.

Spremenljiva obdobja z OFFSET

Prožnejši način za izračun drsečega povprečja je funkcija OFFSET. OFFSET lahko ustvari dinamični obseg, kar pomeni, da lahko nastavimo formulo, kjer 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 si lahko ogledate to formulo v akciji, kjer je 'n' imenovani obseg E2. Začenši pri celici C5, OFFSET ustvari obseg, ki sega nazaj v prejšnje vrstice. To dosežemo z uporabo višine, ki je enaka minus n. Ko se E5 spremeni na drugo številko, se drseče povprečje preračuna v vseh vrsticah:

kako prešteti število besed v

Drseče povprečje s funkcijo OFFSET

Kopirana formula v E5 je:

 
= AVERAGE ( OFFSET (C5,0,0,-n,1))

Tako kot prvotna formula zgoraj, bo imela različica z OFFSET tudi problem nezadostnih podatkov v prvih nekaj vrsticah, odvisno od tega, koliko pik je podanih v E5.

kako izračunati delovne dni v Excelu, razen vikendov in praznikov

V prikazanem primeru se povprečje uspešno izračuna, ker funkcija AVERAGE samodejno prezre besedilne vrednosti in prazne celice , nad C5 ni drugih številčnih vrednosti. Torej, medtem ko je obseg, ki je bil v E5 povprečen v E5, C1: C5, je v povprečju le ena vrednost, 100. Vendar pa se z naraščanjem obdobij OFFSET še naprej ustvarja obseg, ki se razširi nad začetkom podatkov , sčasoma naletel na vrh delovnega lista in vrnil napako #REF.

Ena od rešitev je omejiti velikost območja na število razpoložljivih podatkovnih točk. To lahko storite s funkcijo MIN, da omejite število, uporabljeno za višino, kot je prikazano spodaj:

Drseče povprečje s funkcijo OFFSET in omejenim obsegom

 
= AVERAGE ( OFFSET (C5,0,0,-( MIN ( ROW ()- ROW ($C)+1,n)),1))

To je videti precej strašljivo, v resnici pa je precej preprosto. Omejujemo višino, preneseno v OFFSET z Funkcija MIN :

 
 MIN ( ROW ()- ROW ($C)+1,n)

Znotraj MIN je prva vrednost a relativna številka vrstice , izračunano z:

 
 ROW ()- ROW ($C)+1 // relative row number..1,2,3, etc.

Druga vrednost, ki je 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 enostavno spremeniti. Če spremenimo n v 7 in narišemo rezultate, dobimo grafikon, kot je ta:

Diagram drsečega povprečja s funkcijo OFFSET

Opomba: Zanimivost zgornjih formul OFFSET je, da ne bodo delovale v Google Preglednicah, ker funkcija OFFSET v Preglednicah ne dovoljuje negativne vrednosti za višino ali širino. Priložena preglednica vsebuje rešitvene formule za Googlove liste.

Priloge mapa exceljet drseče povprečje.xlsx Avtor Dave Bruns


^