Pomocí funkce Prognóza aplikace Excel převeďte historická data na předpovědi

Microsoft nemůže předvídat budoucnost - ale může to určitě zkusit. Schopnosti předpovědi časové řady aplikace Excel brát data, která jste poskytli, a extrapolovat potenciální budoucí data. Není to dokonalé, ale může vám poskytnout představu o tom, co vaše data skutečně říkají. Malý pohled do budoucnosti, pokud chcete.

V Excelu lze předpovědět dvěma různými způsoby. Nejprve můžete použít několik funkcí pro předpovědi přímo v buňkách tabulky. Druhá metoda, která používá grafické rozhraní, se snadněji používá, proto se na to zaměříme.

Pokud opravdu chcete vědět, jak tyto funkce používat, můžete se podívat na průvodce společnosti Microsoft týkající se předpovědních funkcí aplikace Excel. Doporučujeme však držet se tlačítka Forecast Sheet.

Jak Excel předpovídá data?

Než začneme, je důležité porozumět tomu, co získáváte, když používáte Excel k předpovídání dat.

Prognóza používá proces zvaný trojité exponenciální vyhlazování (ETS), aby se vaše stávající data a předpovědi na základě přítomných vzorů. ETS váží předchozí hodnoty ve vaší sérii, takže novější hodnoty jsou váženy vážněji a další minulé hodnoty jsou méně váženy.

Gregory Trubetskoyovo vysvětlení exponenciálního vyhlazování je jedním z nejmatematičtějších, co jsem našel. Je to komplikovaný proces, ale jde o to, že nedávné hodnoty vaší řady mají silný vliv na předpovídané hodnoty.

Excel nabízí řadu možností pro vyladění toho, jak ETS funguje, a my je za chvíli překonáme.

Formátování dat pro předpovědi

Prognostické funkce Excel vyžadují, aby vaše data byla ve specifickém formátu, aby správně fungovaly. Budete potřebovat alespoň dva sloupce.

První je časová osa. To musí obsahovat data, časy nebo řadu čísel, která určují časové změny. Jak používat Excel pro výpočet mezi daty Jak používat Excel pro výpočet mezi daty Kolik dní je mezi 9. lednem 2015 a 12. červnem 2017? Přestaňte bojovat, když vám Excel dokáže udělat těžkou práci! .

Důležité je, že intervaly musí být konzistentní. Nemůžete mít prodej od prvního ledna, prvního února, prvního března, prvního dubna, patnáctého dubna a prvního května. Ačkoli byste mohli tato data nahradit čísly, prognóza nemusí být při provádění tohoto postupu tak přesná.

Druhý sloupec jsou vaše data. To je to, co budete předpovídat do budoucna.

Jako příklad předpovědi dat jsem vytvořil tabulku s fiktivními měsíčními prodejními čísly.

Jednoduché předpovědi

Chcete-li začít s předpovědí aplikace Excel, zvýrazněte svá data a přejděte na Data> Prognóza.

Jakmile kliknete na tlačítko Prognostický list, uvidíte pěkně formátovaný graf, který obsahuje vaše data v modré barvě a prediktivní data v oranžové barvě.

Udeřil Vytvořit v novém okně a získáte trvalejší verzi tohoto grafu spolu s tabulkou předpokládaných hodnot. Tato tabulka také obsahuje 95% intervaly spolehlivosti (které jsou také znázorněny v grafu).

To je již velmi cenné. Vyladěním některých nastavení však můžete přizpůsobit data, která získáte, pro ještě lepší předpovědi.

Přizpůsobení předpovědi Excelu

V Vytvořit předpovědní list V okně se ve výchozím nastavení zobrazí pouze jedna rozbalovací nabídka: Předpověď konce.

Datum předpovědi konce vám umožňuje zvolit, jak daleko do budoucnosti předpovídat. Mějte na paměti, že čím dále jdete, tím více chyb pravděpodobně uvidíte.

Na obrázku výše vidíte rozšířenou předpověď, s intervalem spolehlivosti, který se v průběhu času rozšiřuje.

Chcete-li získat přístup k dalším možnostem prognózy, klikněte na Možnosti ve spodní části okna.

Každou možnost projdeme individuálně.

Prognóza Start umožňuje zvolit, kdy Excel začne předpovídat data. Téměř v každém případě budete chtít, aby to bylo od posledního bodu vaší časové osy.

Možná budete chtít nastavit dříve, abyste zjistili, zda se předpovědi aplikace Excel shodují se skutečnými daty, stačí zkontrolovat. Obecně však můžete tuto možnost bezpečně ignorovat.

Interval spolehlivosti umožňuje zapnout nebo vypnout řádky intervalu spolehlivosti a vybrat, zda se má vypočítat. Většinu času je 95 procent standardní hodnotou intervalu spolehlivosti.

To znamená, že Excel je 95 procent přesvědčen, že předpovídaná hodnota klesne mezi tyto dva řádky.

Sezónnost definuje opakující se charakter vaší časové osy. Excel to většinou vypočítá automaticky. V ostatních případech to budete chtít změnit sami.

Například v naší ukázkové tabulce, změna sezónnosti na 4 vede k mnohem lepším datům (použili jsme 4, protože v každém opakujícím se cyklu jsou čtyři body). Pro automatickou detekci sezónnosti vypadala předpověď takto:

A jak to vypadá, když jsem změnil sezónnost na 4:

To lépe odráží typický pokles prodeje kolem října. Stojí za to vyzkoušet si ve své vlastní tabulce, abyste se ujistili, že získáváte nejlepší možná data.

Jen se ujistěte, že vaše sezónní číslo je přesné. Pokud používáte měsíční údaje, sezónnost je 12. Týdenní údaje v průběhu roku budou 52. Pokud je týdenní více než půl roku, použijte 26.

Pokud to zkontrolujete Zahrnout statistiku prognózy, Excel vám poskytne další základní statistiky v závěrečném listu. Zde jsou statistiky, které získáte:

Pokud nevíte, co tyto statistiky znamenají, nebojte se o ně.

Rozsah časové osy a Rozsah hodnot jsou sloupce, do kterých jste zadali své údaje. Můžete je upravit, pokud Excel shromažďuje nesprávná data, ale pravděpodobně nebudete muset.

Než budeme mluvit Vyplňte chybějící body pomocí, v prognózách musíme mluvit o chybějících datech. Funkce prognózy bude fungovat, i když na časové ose nemáte všechny datové body. I když vám některé informace chybí, Excel bude stále zobrazovat předpověď.

Standardní metodou pro vyplnění chybějících bodů je interpolace, která používá vážené průměry sousedních hodnot chybějícího bodu. To je dobrá volba, zejména s ohledem na to, že je to vaše jediná.

Pokud nechcete, aby aplikace Excel interpolovala, můžete si vybrat Nuly v rozevíracím seznamu vložte nuly do chybějících datových prostorů. Pokud ale tyto chybějící hodnoty pravděpodobně nebudou ve skutečnosti nuly, odhazujete svou prognózu.

Agregované duplicitní hodnoty rozbalovací nabídka se týká konkrétního případu v datech. Jsou-li v datech dva údaje pro jeden časový bod, Excel jejich průměr stanoví a určí konečnou hodnotu. Můžete to změnit tak, aby se počítal, medián, součet nebo řada dalších operací.

Jakmile nastavíte všechny možnosti tak, jak chcete, stiskněte Vytvořit a získáte nejlepší odhad Excelu, jak budou vaše data vypadat v budoucnu, s grafem a tabulkou plnou předpokládaných hodnot.

Předpovídejte budoucnost pomocí funkce předpovědi aplikace Excel

Nyní, když víte, jak předpovídat excel na základě historických dat, můžete začít používat program ctihodných tabulek k předpovídání budoucnosti! Nebo alespoň získat představu o tom, jak by to mohlo vypadat na základě minulosti.

Prognóza Excelu není dokonalá, ale je to skvělý nástroj mít ve svém arzenálu Excel Power Up Excel s 10 doplňky pro zpracování, analýzu a vizualizaci dat jako Pro Power Up Excel s 10 doplňky pro zpracování, analýzu a vizualizaci Data Like Pro Pro Vanilla Excel je úžasná, ale můžete si ji doplnit ještě výkonnějším. Ať už potřebujete data zpracovat, je pravděpodobné, že pro ně někdo vytvořil aplikaci Excel. Zde je výběr. . Vytváření předpovědního modelu v Excelu je snadné a bude velkou pomocí, když provozujete obchodní statistiky.

Používáte prognózu Excelu? Jaké další tipy máte pro nováčky na funkci? Podělte se o své myšlenky v komentářích níže!