Zhrnutie
Na výpočet kĺzavého alebo kĺzavého priemeru môžete použiť jednoduchý vzorec založený na funkcii PRIEMER s relatívnymi referenciami. V zobrazenom príklade je vzorec v E7:
=AVERAGE(C5:C7)
Keď sa vzorec skopíruje nadol, vypočíta 3-dňový kĺzavý priemer na základe hodnoty predaja pre aktuálny deň a pre dva predchádzajúce dni.
Ďalej je uvedená flexibilnejšia možnosť založená na funkcii OFFSET, ktorá pracuje s premenlivými obdobiami.
O kĺzavých priemeroch
Kĺzavý priemer (nazývaný aj kĺzavý priemer) je priemer založený na podmnožinách údajov v daných intervaloch. Výpočet priemeru v konkrétnych intervaloch vyhladí údaje znížením vplyvu náhodných výkyvov. Toto uľahčuje sledovanie celkových trendov, najmä v grafe. Čím väčší je interval použitý na výpočet kĺzavého priemeru, tým viac dôjde k vyhladeniu, pretože každý vypočítaný priemer obsahuje viac dátových bodov.
Vysvetlenie
Všetky vzorce uvedené v príklade používajú funkciu PRIEMER s nastavením relatívnej referencie pre každý konkrétny interval. 3-dňový kĺzavý priemer v E7 sa počíta z priemeru AVERAGE tak, aby zahŕňal aktuálny deň a dva predchádzajúce dni, ako je tento:
=AVERAGE(C5:C7) // 3-day average
5denné a 7denné priemery sa počítajú rovnakým spôsobom. V obidvoch prípadoch je rozsah poskytnutý pre PRIEMER zväčšený o požadovaný počet dní:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Všetky vzorce používajú relatívnu referenciu pre rozsah dodávaný funkcii PRIEMER. Keď sa vzorce kopírujú nadol do stĺpca, rozsah sa v každom riadku mení, aby obsahoval hodnoty potrebné pre každý priemer.
Keď sú hodnoty vykreslené v čiarovom grafe, efekt vyhladenia je jasný:
Nedostatočné údaje
Ak začnete vzorce v prvom riadku tabuľky, prvých niekoľko vzorcov nebude mať dostatok údajov na výpočet úplného priemeru, pretože rozsah bude presahovať prvý riadok údajov:
To môže alebo nemusí byť problém, v závislosti od štruktúry listu a od toho, či je dôležité, aby všetky priemery vychádzali z rovnakého počtu hodnôt. Funkcia PRIEMER bude automaticky ignorovať textové hodnoty a prázdne bunky, takže bude pokračovať vo výpočte priemeru s menšími hodnotami. Preto to „funguje“ v E5 a E6.
Jedným zo spôsobov, ako jasne označiť nedostatočné údaje, je skontrolovať číslo aktuálneho riadku a prerušiť ho pomocou #NA, ak existuje menej ako n hodnôt. Napríklad pre trojdňový priemer môžete použiť:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
Prvá časť vzorca jednoducho vygeneruje „normalizované“ číslo riadku počnúc 1:
ROW()-ROW($C$5)+1 // relative row number
V riadku 5 je výsledok 1, v riadku 6 je výsledok 2 atď.
Ak je číslo aktuálneho riadku menšie ako 3, vzorec vráti # N / A. V opačnom prípade vráti vzorec kĺzavý priemer ako predtým. Toto napodobňuje správanie verzie kĺzavého priemeru nástroja Analysis Toolpak, ktorá vydáva # N / A až do dosiahnutia prvého úplného obdobia.
Keď sa však zvýši počet období, nakoniec vám dôjdu riadky nad údajmi a nebudete môcť zadať požadovaný rozsah vo vnútri PRIEMERNÉHO. Napríklad nemôžete s pracovným hárkom nastaviť kĺzavý 7-dňový priemer, ako je to znázornené, pretože nemôžete zadať rozsah, ktorý presahuje 6 riadkov nad C5.
Variabilné obdobia s offsetom
Pružnejším spôsobom výpočtu kĺzavého priemeru je funkcia OFFSET. OFFSET môže vytvárať dynamický rozsah, čo znamená, že môžeme nastaviť vzorec, kde je počet období variabilný. Všeobecná forma je:
=AVERAGE(OFFSET(A1,0,0,-n,1))
kde n je počet období, ktoré sa majú zahrnúť do každého priemeru. Ako je uvedené vyššie, funkcia OFFSET vracia rozsah, ktorý sa odovzdáva do funkcie PRIEMER. Ďalej môžete vidieť tento vzorec v akcii, kde „n“ je pomenovaný rozsah E2. Počínajúc bunkou C5, program OFFSET zostrojuje rozsah, ktorý siaha späť k predchádzajúcim riadkom. To sa dosiahne použitím výšky rovnej zápornej hodnote n. Keď sa E5 zmení na iné číslo, kĺzavý priemer sa prepočíta na všetkých riadkoch:
Vzorec v E5, skopírovaný nadol, je:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Rovnako ako pôvodný vzorec vyššie, bude mať verzia s OFFSET tiež problém s nedostatkom údajov v prvých niekoľkých riadkoch, v závislosti od toho, koľko období je uvedených v E5.
V zobrazenom príklade sa priemery vypočítajú úspešne, pretože funkcia AVERAGE automaticky ignoruje textové hodnoty a prázdne bunky a nad C5 nie sú žiadne ďalšie číselné hodnoty. Takže zatiaľ čo rozsah zadaný do PRIEMERNÉHO v E5 je C1: C5, v priemere existuje iba jedna hodnota, 100. Avšak s pribúdajúcimi obdobiami bude OFFSET pokračovať vo vytváraní rozsahu, ktorý presahuje začiatok údajov a nakoniec narazí na horná časť tabuľky a vracia chybu #REF.
Jedným z riešení je „obmedziť“ veľkosť rozsahu na počet dostupných dátových bodov. To je možné dosiahnuť pomocou funkcie MIN na obmedzenie počtu použitého na výšku, ako je vidieť nižšie:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Vyzerá to dosť strašidelne, ale v skutočnosti je to celkom jednoduché. Výšku výšky nastavenú na OFFSET obmedzujeme funkciou MIN:
MIN(ROW()-ROW($C$5)+1,n)
Vo vnútri MIN je prvou hodnotou relatívne číslo riadku vypočítané z:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
Druhá hodnota daná MIN je počet období, n. Ak je relatívne číslo riadku menšie ako n, MIN vráti číslo aktuálneho riadku do výšky OFFSET. Ak je číslo riadku väčšie ako n, MIN vráti n. Inými slovami, MIN jednoducho vráti menšiu z dvoch hodnôt.
Príjemnou vlastnosťou možnosti OFFSET je, že n sa dá ľahko zmeniť. Ak zmeníme n na 7 a vykreslíme výsledky, dostaneme takýto graf:
Poznámka: Vtipom s vyššie uvedenými vzorcami OFFSET je, že nebudú fungovať v Tabuľkách Google, pretože funkcia OFFSET v Tabuľkách neumožňuje zápornú hodnotu výšky alebo šírky. Priložená tabuľka obsahuje vzorce pre riešenie pre tabuľky Google.
