Sažetak
Da biste izračunali pokretni ili pokretni prosjek, možete upotrijebiti jednostavnu formulu koja se temelji na funkciji PROSJEČNO s relativnim referencama. U prikazanom primjeru formula u E7 je:
=AVERAGE(C5:C7)
Kako se formula kopira, izračunava trodnevni pomični prosjek na temelju vrijednosti prodaje za tekući dan i dva prethodna dana.
Ispod je fleksibilnija opcija koja se temelji na funkciji OFFSET koja obrađuje promjenjiva razdoblja.
O pokretnim prosjecima
Pomični prosjek (koji se naziva i pokretni prosjek) je prosjek zasnovan na podskupovima podataka u određenim intervalima. Izračunavanje prosjeka u određenim intervalima izglađuje podatke smanjenjem utjecaja slučajnih kolebanja. To olakšava uvid u cjelokupne trendove, posebno na grafikonu. Što je veći interval korišten za izračunavanje pomičnog prosjeka, to se više izravnava, jer je u svaki izračunati prosjek uključeno više podatkovnih točaka.
Obrazloženje
Sve formule prikazane u primjeru koriste funkciju AVERAGE s relativnom referencom postavljenom za svaki određeni interval. Trodnevni pomični prosjek u E7 izračunava se hranjenjem PROSJEČNOG raspona koji uključuje tekući dan i dva prethodna dana poput ovog:
=AVERAGE(C5:C7) // 3-day average
Prosjek od 5 dana i 7 dana izračunava se na isti način. U svakom slučaju, raspon koji se pruža PROSJEČNOM povećava se tako da uključuje potreban broj dana:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Sve formule koriste relativnu referencu za raspon isporučen funkciji AVERAGE. Kako se formule kopiraju u stupac, raspon se mijenja u svakom retku tako da uključuje vrijednosti potrebne za svaki prosjek.
Kada su vrijednosti ucrtane u linijski grafikon, učinak zaglađivanja je jasan:
Nedovoljno podataka
Ako formule započnete u prvom retku tablice, prvih nekoliko formula neće imati dovoljno podataka za izračunavanje potpunog prosjeka, jer će se raspon proširiti iznad prvog reda podataka:
To može biti problem ili ne, ovisno o strukturi radnog lista i je li važno da se svi prosjeci temelje na istom broju vrijednosti. Funkcija AVERAGE automatski će zanemariti tekstualne vrijednosti i prazne ćelije, pa će i dalje izračunavati prosjek s manje vrijednosti. Zbog toga to "djeluje" u E5 i E6.
Jedan od načina da se jasno ukaže na nedostatak podataka jest provjera trenutnog broja retka i prekid s #NA kada postoji manje od n vrijednosti. Na primjer, za trodnevni prosjek možete koristiti:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
Prvi dio formule jednostavno generira "normalizirani" broj retka, počevši od 1:
ROW()-ROW($C$5)+1 // relative row number
U retku 5 rezultat je 1, u redu 6 rezultat 2 i tako dalje.
Kada je broj trenutnog retka manji od 3, formula vraća # N / A. Inače, formula vraća pokretni prosjek kao i prije. Ovo oponaša ponašanje verzije Moving Average (Paket alata za analizu) koja daje # N / A dok se ne dosegne prvo cjelovito razdoblje.
Međutim, kako se broj razdoblja povećava, na kraju ćete ostati bez redaka iznad podataka i nećete moći unijeti potreban raspon unutar PROSJEČNO. Na primjer, ne možete postaviti pokretni prosjek od 7 dana s radnim listom kao što je prikazano, jer ne možete unijeti raspon koji se proteže 6 redaka iznad C5.
Varijabilna razdoblja s OFFSET-om
Fleksibilniji način izračuna pokretnog prosjeka je pomoću funkcije OFFSET. OFFSET može stvoriti dinamički raspon, što znači da možemo postaviti formulu u kojoj je broj razdoblja promjenjiv. Opći oblik je:
=AVERAGE(OFFSET(A1,0,0,-n,1))
gdje je n broj razdoblja koja treba uključiti u svaki prosjek. Kao i gore, OFFSET vraća raspon koji se prosljeđuje u funkciju PROSJEČNO. Ispod možete vidjeti ovu formulu na djelu, gdje je "n" imenovani raspon E2. Počevši od ćelije C5, OFFSET konstruira raspon koji se proteže natrag na prethodne retke. To se postiže korištenjem visine jednake negativnom n. Kada se E5 promijeni u drugi broj, pomični prosjek preračunava se u svim redovima:
Kopirana formula u E5 je:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Kao i gornja originalna formula, verzija s OFFSET također će imati problem s nedostatkom podataka u prvih nekoliko redaka, ovisno o tome koliko je razdoblja dato u E5.
U prikazanom primjeru prosjeci se uspješno izračunavaju jer funkcija AVERAGE automatski ignorira tekstualne vrijednosti i prazne ćelije, a iznad C5 ne postoje druge numeričke vrijednosti. Dakle, dok je raspon prosljeđen u PROSJEČNO u E5 C1: C5, postoji samo jedna vrijednost za prosjek, 100. Međutim, kako se razdoblja povećavaju, OFFSET će nastaviti stvarati raspon koji se proteže iznad početka podataka, a na kraju naleti vrh radnog lista i vraćanje pogreške #REF.
Jedno rješenje je "ograničiti" veličinu raspona na broj dostupnih podatkovnih točaka. To se može učiniti upotrebom funkcije MIN za ograničavanje broja koji se koristi za visinu kao što je prikazano u nastavku:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Ovo izgleda prilično zastrašujuće, ali zapravo je prilično jednostavno. Ograničavamo visinu proslijeđenu u OFFSET funkcijom MIN:
MIN(ROW()-ROW($C$5)+1,n)
Unutar MIN, prva vrijednost je relativni broj retka, izračunat sa:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
Druga vrijednost dana MIN-u je broj razdoblja, n. Kada je relativni broj retka manji od n, MIN vraća trenutni broj retka u OFFSET za visinu. Kada je broj retka veći od n, MIN vraća n. Drugim riječima, MIN jednostavno vraća manju od dvije vrijednosti.
Lijepa značajka opcije OFFSET je da se n može lako promijeniti. Ako promijenimo n na 7 i zacrtamo rezultate, dobit ćemo sljedeći grafikon:
Napomena: Čudnost gore navedenih formula OFFSET je da oni neće raditi u Google tablicama, jer funkcija OFFSET u tablicama neće dopustiti negativnu vrijednost za visinu ili širinu. Priložena proračunska tablica sadrži formule za zaobilaženje Googleovih listova.
