Prognozowanie w programie Excel wymagało od zawsze znajomości teorii, wyboru właściwej funkcji i kalkulacji na poziomie komórek. Rozwój nowych narzędzi na rynku sprawił, że prognozy w prostej, graficznej formie trafiły też do najnowszej wersji programu Excel. Do dyspozycji mamy nowy typ arkusza – Arkusz prognozy. Jakie ma opcje, jakie modele są dostępne oraz jak można z niego skorzystać?

Arkusz prognozy w programie Excel 2016

W skrócie, dostępne na karcie Dane > Prognoza > Arkusz prognozy.

Prognoza w wersji 2016 dostępna jest w postaci przycisku. Tworzy wizualizację prognozy na podstawie serii danych. Dzięki dostępnym opcjom zaprezentowaną prognozę możemy dostosować zmieniając jej sezonowość (wykrywana i ustawiana jest automatycznie na podstawie serii danych, bądź ustawiana ręcznie) i interwały ufności, czas prognozy – początek i koniec. Poniżej przykład na podstawie kursu euro w roku 2016. Prognoza średniego kursu na kolejne miesiące tego roku. Utworzony wykres zostaje zamieszczony w nowym arkuszu wraz ze skopiowanymi danymi historycznymi, prognozą, granicami ufności i prezentującym je wszystkie wykresem.

Tworzenie prognozy w Excelu 2016

By przygotować prognozę, dane na postawie których będę ją przygotowywać muszą mieć wymiar czasowy – godzinę/datę i przypisaną im wartość. Ważne by odstępy między punktami danych na osi czasu były ze sobą równe.

Zaznaczam obie serie danych > Dane > Prognoza > Arkusz Prognoza. W oknie Tworzenie arkusza prognozy mogę wybrać wykres liniowy lub kolumnowy dla zwizualizowania prognozy. Aby stworzyć podstawowy wykres (z ustawieniami domyślnymi) muszę wprowadzić datę końcową prognozy. By wygenerować wykres, wciskam Utwórz.

Wykres oraz tabela z danymi pierwotnymi oraz prognozowanymi przez program będzie umieszczona w nowym arkuszu, który pojawi się przed arkuszem z danymi historycznymi.

Opcje zaawansowane przy tworzeniu prognozy

Przestawiony sposób prezentowania prognozy jest wersją podstawową, bez zmiany możliwych opcji. Oto jakie możliwości dostosowania prognozy daje nam to narzędzie.

Moment rozpoczęcia prognozy – mogę wybrać moment (datę) od której zaczynamy prognozę, co oznacza np. mając dane (historyczne, rzeczywiste) od 01.01.2016 do 01.08.2016 mogę już zacząć prognozować od 01.06.2016. Wówczas wynik prognozy nakłada nam się na dane historyczne, ale przez to mogę sprawdzić dokładność prognozy – jak bliska jest rzeczywistości w przedziale od 01.06.2016 – 01.08.2016.

Przedział ufności – jest to procent, który mówi jak często mam rację, im poziom ufności bliższy 100% tym częściej mam rację co do szacowanego parametru. Domyślnie jest to 95% , jednak można go zwiększyć lub zmniejszyć.

Sezonowość – podaje ile cykli (sezonów) znajduje się w danych historycznych. Przy zaznaczonej opcji wykryj automatycznie Excel sam znajduje sezonowość, oczywiście sam również mogę ręcznie podać liczbę sezonów.

Zakres osi czasu, Zakres wartości – muszą sobie wzajemnie odpowiadać co do długości.

Uzupełnij brakujące punkty przy użyciu … – do wyboru mam zero lub interpolacja,  o ile wersja zero jest jasna, to interpolacja wymaga wyjaśnienia. Oznacza, że brakujące punkty będą wypełnione średnią ważoną punktów sąsiednich.

Agreguj duplikaty przy użyciu – jeżeli  w danych dla tej samej daty występuje kilka wartości, w tym miejscu mogę wybrać opcje jakie działanie zostanie wykonane dla zduplikowanych przedziałów czasowych.

Dołącz statystykę prognozy – przy zaznaczeniu tego pola wraz z prognozą  w nowym arkuszu pojawią się również dodatkowe informacje statystyczne (poniżej).

Formuły używane w prognozowaniu danych

W nowym arkuszu z prognozą zawarte są dane historyczne – czas i wartości. Dodatkowo wyliczone są wartości prognozowane przy pomocy funkcji REGLINX.ETS. Dwie kolumny prezentują przedział ufności (obliczony funkcją REGLINX.ETS.CONFINT) – które są wyświetlane, jeżeli wcześniej w opcjach zaznaczyliśmy pole przedziału ufności.

Przykłady

Spróbuję zaprezentować prognozę opartą na danych dla wskaźnika cen produkcji sprzedanej przemysłu w roku 2015 i 2016. Dane są miesięczne – więc mam regularne odstępy czasowe pomiędzy kolejnymi pomiarami. Prognoza będzie obejmować okres od 07.2016 do końca roku 2016.

Zaznaczam dane: wymiar czasowy oraz wartość wskaźnika cen Dane > Prognoza > Arkusz prognozy.

W wyniku otrzymuję poniższy wykres (przy ustawieniach domyślnych).

W lewym górnym rogu widać dwa przyciski, pozwalają one na zmianę wykresu z liniowego na słupkowy. Poniżej efekt zmiany:

Według domyślnych ustawień data rozpoczęcia prognozy,  to data z której pochodzi ostatnia dana historyczna. Spróbuję zaprezentować jak wygląda prognoza zaczynająca się wcześniej – zaprognozuję dane historyczne np. od 01.03.2016 (tę datę ustawiam jako początek prognozy).

Widać jak daleko i w jak zupełnie innym kierunku zmierza prognoza (grubsza pomarańczowa linia) w porównaniu do danych rzeczywistych w okresie 01.03.2016-01.07.2016.

Wyłączając opcję przedziału ufności – nie zobaczymy na wykresie linii tworzących przedział ufności (cieńsze pomarańczowe) . Wartość prognozy pozostanie bez zmian.

Spróbowałem również usunąć w danych historycznych część wartości by sprawdzić działanie interpolacji. Poniżej efekt użycia interpolacji oraz drugiej z opcji , czyli dla braku danych na wykresie wstawiane jest zero. Interpolacja zdecydowanie wygładza wykres.

Efekt dla interpolacji:

Efekt dla wypełnienia pustych wartości zerami:

Plik do pobrania

Pobierz plik Prognozowanie w programie Excel 2016.xlsx i wstaw swoje dane.