Vyhlazování dat grafu Excel pomocí pevného klouzavého průměru funguje, pokud máte zkušenosti s daty a jejich proměnlivostí. Pokud se však data mění nebo jsou nová, možná budete chtít vy nebo vedení experimentovat s jiným počtem období ve vyhlazovacím průměru.
V tomto příkladu je k vytvoření průměru v dynamickém rozsahu použit vzorec OFFSET. Zadáte počet měsíců, které chcete použít pro vyhlazovací období, a vzorce AVERAGE a OFFSET vypočítají, přes kolik buněk se má průměr vytvořit.
Než vytvoříte dynamický vzorec AVERAGE, začneme tím, že pochopíme, jak funguje vzorec OFFSET. Na následujícím obrázku buňka G26 obsahuje vzorec pro vyhlazování dat v řádku 20,
=AVERAGE(OFFSET(G20,0,0,1,-3))
OFFSET určuje rozsah, přes který bude AVERAGE data vyhlazovat.
Tento vzorec je mezikrok, který vám pomůže pochopit, jak dynamický vzorec funguje. Funkce OFFSET vrací rozsah, na který bude AVERAGE působit. Atributy funkce OFFSET jsou,
OFFSET(odkaz,řádky,sloupce,,)
V tomto příkladu rozsah, který OFFSET vypočítá, začíná na G20 a má nulový posun řádků nebo sloupců. Jinými slovy, levý horní roh rozsahu, který OFFSET vypočítá, je právě G20. Nebude posunut ani posunut o žádný řádek nebo sloupec. Vypočítaný rozsah bude mít výšku 1 řádku a bude zahrnovat aktuální buňku G26 a další dvě vlevo (to je -3). Takže tento vypočtený rozsah bude G20:E20.
Zkopírujte tento vzorec v G26 doprava a uvidíte, že zprůměruje datovou buňku nad ní v řádku 20 a předchozí dvě buňky vlevo.
Tady je tedy trik, který z toho udělá dynamický a průměr v rozsahu, který si vyberete. Co když ve vzorci nahradíte odkaz na buňku -3?“
Na obrázku níže byl vzorec v buňce G26 upraven tak, že je určen číslem v buňce E24,
AVERAGE(OFFSET(G20,0,0,1,-$E$24))
$E$24 je zeleně stínovaná buňka. Číslo, které zadáte do buňky E24, určí šířku buněk použitých k průměrování napříč.
Zadáním hodnoty do buňky E24 se změní rozsah použitý k vyhlazení dat.
V tomto bodě byste mohli použít spinner nebo rozbalovací nabídku, abyste uživateli dali na výběr, jak široký má být průměr pro vyhlazení. Výsledek jejich výběru by měl jít do buňky E24.
Stáhněte si vyhlazování dat z grafu Excel s pevným nebo dynamickým vyhlazováním
Toto uspořádání má jeden problém. Pokud uživatel zadá do E24 větší číslo, než je buněk vlevo, pak OFFSET bude zahrnovat prázdné buňky a popisek. Tím se získá nesprávný průměr. Pokud je E24 tak velké, že OFFSET vyjde mimo list, pak AVERAGE vyprodukuje chybu. Co dělat? Potřebujeme řešení, které nezastaví pouze chybu, jako je ISERROR, potřebujeme řešení, které zastaví nesprávné odpovědi.
Na tomto dalším obrázku je vzorec buňky G26 byl upraven. V řádku 25 je řada čísel, která jsou hranicemi platných rozsahů vlevo. Nový vzorec v buňce G26 se podívá do řádku 25, a pokud je číslo v buňce E24 větší než limit v řádku 25, pak se vytvoří vzorec NA(). Tento nový vzorec pro opravu chyb v G26 je,
=IF($E$24>G25,NA(),AVERAGE(OFFSET(G20,0,0,1,-$E$24)))
Když se graf aplikace Excel odkazuje na NA() v buňce, nezobrazí nic. To zabrání tomu, aby vyhlazovací čára klesla na nulu.
Upravte vzorec dynamického vyhlazování tak, aby nedocházelo k chybám.
Problémy s vyhlazováním dat a jak je obejít
Nyní můžete nechat uživatele vyzkoušet libovolnou periodu vyhlazování. Nicméně (zdá se, že v tomto případě je vždy nějaké „nicméně“ nebo „ale“) existuje problém s vyhlazováním dat. Vyhlazení odstraní volatilitu, ale také znamená, že změny směru, inflexní body nebo nové trendy uvidíte až zhruba v polovině vyhlazovacího období. Jste za křivkou.
Nejlepším řešením z obou světů je použití funkce vyhlazování váženého průměru. Tímto způsobem můžete dát větší váhu blízkým datům, abyste viděli trendy, ale můžete vyhlazovat na základě starších dat, abyste eliminovali část volatility. To zní jako dobrý případ pro další článek Vyhlazování dat grafu Excel pomocí váženého průměru.
Stáhněte si soubor s příkladem vyhlazování dat grafu Excel pomocí klouzavého nebo dynamického vyhlazování
Související články,
- Vyhlazování dat grafu Excel pomocí klouzavého průměru
- Vyhlazování dat grafu Excel pomocí váženého průměru
.