Jak vytvořit předpovědní list v Excelu 2016

Nová funkce Forecast Sheet v Excelu 2016 velmi usnadňuje přeměnu listu obsahujícího historická finanční data na pozoruhodný vizuální list prognózy. Jediné, co musíte udělat, je otevřít list s historickými daty, umístit kurzor buňky do jedné z jeho buněk a potom kliknout na tlačítko List s prognózou na kartě Data na pásu karet (Alt+AFC).

Excel poté vybere všechna historická data v okolní tabulce listu (která obvykle obsahuje sloupec pro data a také jeden pro související finanční data) a současně otevře dialogové okno Vytvořit list s prognózou (podobné tomu, které je zde znázorněno). .

Definování možností pro nový spojnicový graf, který zobrazuje historické a předpokládané prodeje v dialogovém okně Vytvořit sešit prognózy.

Ve výchozím nastavení Excel vybere spojnicový graf, aby vizuálně zobrazil trendy ve své prognóze, ale pokud si přejete, můžete místo toho nechat program použít sloupcový graf. Jednoduše klikněte na ikonu sloupcového grafu napravo od ikony spojnicového grafu v horní části dialogového okna Vytvořit předpovědní list.

Kromě výběru typu grafu, který se má použít v listu prognózy, můžete také řídit datum, kdy graf prognózy končí. To provedete výběrem nového data v textovém poli Konec prognózy, které se zobrazí pod náhledem vloženého spojnicového nebo sloupcového grafu. Například tabulka historických dat zahrnuje roční prodeje za roky 2000 až 2015 a Excel tato data automaticky použije k prodloužení data ukončení prognózy o čtyři roky do roku 2019. Chcete-li prognózu prodloužit za toto koncové datum, jednoduše zadejte nový rok do textové pole Forecast End (Konec prognózy) (nebo jej vyberte pomocí tlačítek číselníku).

Pod textovým polem Forecast End v dialogovém okně Create Forecast Worksheet najdete tlačítko Možnosti, které po kliknutí (jak je znázorněno) rozbalí dialogové okno tak, aby zahrnovalo následující možnosti:

• Forecast Start umožňuje vybrat počáteční datum v historických datech později, než které Excel automaticky vybere (což je první datum v tabulce listu).

• Interval spolehlivosti umožňuje vybrat nový stupeň spolehlivosti, který Excel použije k nastavení dolní a horní hranice spolehlivosti v prognóze při použití výchozího spojnicového grafu.

• Sezónně se změní z možnosti Zjistit automaticky na Nastavit ručně, kde můžete zadat nebo vybrat novou hodnotu označující počet bodů v rozsahu hodnot vaší datové tabulky, které jsou součástí opakujícího se sezónního vzoru. Když Excel nemůže automaticky detekovat sezónnost v datech listu, zobrazí se varování, které vám to radí a navrhuje, abyste vybrali možnost Nastavit ručně (a ponechali výchozí nastavení na nule), abyste získali lepší výsledky v prognóze.

• Funkce Include Forecast Statistics umožňuje Excel zahrnout do výsledného listu prognózy tabulku metrik přesnosti prognózy a vyhlazovacích faktorů.

• Rozsah časové osy t upravuje rozsah buněk obsahující hodnoty data ve vašich historických datech (ve výchozím nastavení Excel vybere všechny tyto hodnoty, které identifikuje v aktuální tabulce listu).

• Rozsah hodnot upravuje oblast buněk obsahující finanční hodnoty ve vašich historických datech (ve výchozím nastavení Excel vybere všechny tyto hodnoty, které identifikuje v aktuální tabulce listu).

• Vyplnit chybějící body Pomocí této funkce Excel automaticky vyplní všechny chybějící datové body, které najde v tabulce listu v grafu prognózy pomocí interpolace nebo nul.

• Aggregate Duplicates Použití t upravuje statistickou funkci.

Po výběru všech požadovaných možností v dialogovém okně Vytvořit předpovědní list stačí kliknout na tlačítko Vytvořit, aby Excel vytvořil nový předpovědní list. Tento nový list obsahuje formátovanou datovou tabulku, která kombinuje vaše historická data s hodnotami prognózy a také vložený spojnicový nebo sloupcový graf (v závislosti na vašem výběru) zobrazující trend v historických i prognózovaných hodnotách.

Následující obrázek ukazuje zbrusu nový pracovní list prognózy, který pro mě funkce Excel Forecast Sheet vytvořila pomocí původních historických dat prodeje zobrazených na předchozím obrázku po výběru tlačítka volby Nastavit ručně s nastavením na nulu a zaškrtnutím políčka Zahrnout statistiku prognózy před klepnutím na tlačítko Vytvořit v dialogovém okně Vytvořit sešit prognózy. Jak můžete vidět, Excel umístil tento nový pracovní list s prognózou (pojmenovaný List 1) před list obsahující moje historická data (pojmenovaný Roční prodej).

Pracovní list s vloženým spojnicovým grafem znázorňujícím historické a prognózované prodeje vytvořený pomocí funkce Předpovědní list.

Všimněte si, že za účelem zobrazení všech hodnot prognózy v zobrazené nové formátované datové tabulce se vložený spojnicový graf před pořízením snímku obrazovky přemístí a změní jeho velikost tak, aby se vešel do pravého dolního rohu. Před uložením nové tabulky a grafu prognózy jako součásti sešitu Historie a prognóza prodeje můžete tento graf přesunout na jeho vlastní list s grafem.