souhrn
K výpočtu klouzavého nebo klouzavého průměru můžete použít jednoduchý vzorec založený na funkci PRŮMĚR s relativními odkazy. V zobrazeném příkladu je vzorec v E7:
=AVERAGE(C5:C7)
Jak je vzorec zkopírován dolů, vypočítá 3denní klouzavý průměr na základě hodnoty prodeje pro aktuální den a dva předchozí dny.
Níže je flexibilnější možnost založená na funkci OFFSET, která zpracovává proměnné období.
O klouzavých průměrech
Klouzavý průměr (nazývaný také klouzavý průměr) je průměr založený na podmnožinách dat v daných intervalech. Výpočet průměru v určitých intervalech vyhladí data snížením dopadu náhodných výkyvů. Díky tomu je snazší vidět celkové trendy, zejména v grafu. Čím větší je interval použitý k výpočtu klouzavého průměru, tím více dochází k vyhlazení, protože v každém vypočítaném průměru je zahrnuto více datových bodů.
Vysvětlení
Všechny vzorce uvedené v příkladu používají funkci PRŮMĚR s relativní referencí nastavenou pro každý konkrétní interval. 3denní klouzavý průměr v E7 se vypočítá krmením PRŮMĚRNÉHO rozsahu, který zahrnuje aktuální den a dva předchozí dny takto:
=AVERAGE(C5:C7) // 3-day average
5denní a 7denní průměry se počítají stejným způsobem. V každém případě se rozsah poskytnutý pro PRŮMĚR zvětší tak, aby zahrnoval požadovaný počet dní:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Všechny vzorce používají relativní odkaz na rozsah dodávaný funkci PRŮMĚR. Jak se vzorce kopírují dolů do sloupce, rozsah se mění na každém řádku tak, aby obsahoval hodnoty potřebné pro každý průměr.
Když jsou hodnoty vyneseny do spojnicového grafu, efekt vyhlazení je jasný:
Nedostatečné údaje
Pokud spustíte vzorce v prvním řádku tabulky, prvních několik vzorců nebude mít dostatek dat k výpočtu úplného průměru, protože rozsah se rozšíří nad první řádek dat:
To může nebo nemusí být problém v závislosti na struktuře listu a na tom, zda je důležité, aby všechny průměry vycházely ze stejného počtu hodnot. Funkce PRŮMĚR bude automaticky ignorovat textové hodnoty a prázdné buňky, takže bude i nadále počítat průměr s méně hodnotami. Proto to „funguje“ v E5 a E6.
Jedním ze způsobů, jak jasně označit nedostatečná data, je zkontrolovat aktuální číslo řádku a přerušit pomocí #NA, pokud existuje méně než n hodnot. Například pro třídenní průměr můžete použít:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
První část vzorce jednoduše generuje „normalizované“ číslo řádku, počínaje 1:
ROW()-ROW($C$5)+1 // relative row number
V řádku 5 je výsledek 1, v řádku 6 je výsledek 2 atd.
Pokud je číslo aktuálního řádku menší než 3, vrátí vzorec # N / A. V opačném případě vrátí vzorec klouzavý průměr jako dříve. To napodobuje chování verze nástroje Moving Average pro analýzu, která vydává # N / A, dokud není dosaženo prvního úplného období.
Jak se však počet období zvyšuje, nakonec vám dojdou řádky nad daty a nebudete moci zadat požadovaný rozsah uvnitř PRŮMĚR. Například nemůžete nastavit klouzavý 7denní průměr s listem, jak je znázorněno, protože nemůžete zadat rozsah, který přesahuje 6 řádků nad C5.
Variabilní období s offsetem
Pružnější způsob výpočtu klouzavého průměru je s funkcí OFFSET. OFFSET může vytvořit dynamický rozsah, což znamená, že můžeme nastavit vzorec, kde je počet period proměnný. Obecná forma je:
=AVERAGE(OFFSET(A1,0,0,-n,1))
kde n je počet období, která mají být zahrnuta do každého průměru. Jak je uvedeno výše, funkce OFFSET vrací rozsah, který je předán do funkce PRŮMĚR. Níže vidíte tento vzorec v akci, kde „n“ je pojmenovaný rozsah E2. Počínaje buňkou C5 vytváří OFFSET rozsah, který sahá zpět k předchozím řádkům. Toho je dosaženo použitím výšky rovné záporné n. Když se E5 změní na jiné číslo, klouzavý průměr se přepočítá na všech řádcích:
Zkopírovaný vzorec v E5 je:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Stejně jako výše uvedený původní vzorec bude mít verze s OFFSETem také problém s nedostatečnými údaji v prvních několika řádcích, v závislosti na tom, kolik období je uvedeno v E5.
V zobrazeném příkladu se průměr vypočítá úspěšně, protože funkce PRŮMĚR automaticky ignoruje textové hodnoty a prázdné buňky a nad C5 nejsou žádné další číselné hodnoty. Zatímco rozsah předávaný do PRŮMĚR v E5 je C1: C5, v průměru existuje pouze jedna hodnota, 100. Avšak s přibývajícími obdobími bude OFFSET pokračovat ve vytváření rozsahu, který sahá nad začátek dat a nakonec narazí na horní část listu a vrácení chyby #REF.
Jedním z řešení je „omezit“ velikost rozsahu na počet dostupných datových bodů. To lze provést pomocí funkce MIN k omezení počtu použitých pro výšku, jak je vidět níže:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Vypadá to docela děsivě, ale ve skutečnosti je to docela jednoduché. Omezujeme výšku předanou do OFSETU funkcí MIN:
MIN(ROW()-ROW($C$5)+1,n)
Uvnitř MIN je první hodnota relativní číslo řádku, počítáno z:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
Druhá hodnota daná MIN je počet období, n. Pokud je relativní číslo řádku menší než n, vrátí MIN aktuální číslo řádku do výšky OFFSET. Když je číslo řádku větší než n, MIN vrátí n. Jinými slovy MIN jednoduše vrátí menší ze dvou hodnot.
Pěknou vlastností možnosti OFFSET je, že n lze snadno změnit. Změníme-li n na 7 a vykreslíme výsledky, dostaneme takový graf:
Poznámka: Tvůrcem výše uvedených vzorců OFFSET je, že nebudou fungovat v Tabulkách Google, protože funkce OFFSET v Tabulkách neumožňuje zápornou hodnotu výšky nebo šířky. V připojené tabulce jsou alternativní vzorce pro listy Google.
