Użyj funkcji prognozowania programu Excel, aby zamienić dane historyczne w prognozy

Microsoft nie może przewidzieć przyszłości - ale może spróbować. Zdolności Excela do prognozowania szeregów czasowych pobierają dostarczone dane i ekstrapolują potencjalne przyszłe dane. Nie jest idealny, ale może dać ci wyobrażenie o tym, co naprawdę mówią twoje dane. Małe spojrzenie w przyszłość, jeśli chcesz.

Istnieją dwa różne sposoby prognozowania w programie Excel. Po pierwsze, możesz użyć kilku funkcji prognozowania bezpośrednio w komórkach arkusza kalkulacyjnego. Druga metoda, która wykorzystuje interfejs graficzny, jest łatwiejsza w użyciu, więc skupimy się na tym.

Jeśli naprawdę chcesz wiedzieć, jak korzystać z funkcji, zapoznaj się z przewodnikiem Microsoft dotyczącym funkcji prognozowania w programie Excel. Zalecamy jednak pozostanie przy przycisku przycisku prognozy.

W jaki sposób Excel prognozuje dane?

Zanim zaczniemy, ważne jest, aby zrozumieć, co otrzymujesz, gdy używasz Excela do prognozowania danych.

Funkcja prognozowania wykorzystuje proces zwany potrójnym wygładzaniem wykładniczym (ETS), aby pobrać istniejące dane i dokonać prognoz na podstawie obecnych wzorców. ETS waży poprzednie wartości z serii, aby nowsze wartości były ważone, a dalsze wartości mniej ważone.

Wyjaśnienie Gregory'ego Trubetskoya dotyczące wykładniczego wygładzania jest jednym z najbardziej niematycznych czytelników, jakie znalazłem. Jest to skomplikowany proces, ale sprowadza się to do tego, że ostatnie wartości z twojej serii mają silny wpływ na wartości prognozowane.

Excel oferuje wiele opcji do poprawiania działania ETS, a my zajmiemy się nimi za chwilę.

Formatowanie danych do prognozowania

Funkcje prognozowania w programie Excel wymagają poprawnego działania danych w określonym formacie. Potrzebujesz co najmniej dwóch kolumn.

Pierwszy to oś czasu. Musi zawierać daty, godziny lub serię liczb, które określają zmianę czasową. Jak używać Excela do obliczania między datami Jak używać Excela do obliczania między datami Ile dni jest między 9 stycznia 2015 r. A 12 czerwca 2017 r.? Przestań się męczyć, gdy Excel wykona za ciebie ciężką pracę! .

Ważne jest tutaj to, że odstępy czasu muszą być spójne. Nie możesz mieć sprzedaży od pierwszego stycznia, pierwszego lutego, pierwszego marca, pierwszego kwietnia, piętnastego kwietnia i pierwszego maja. Chociaż można zastąpić te daty liczbami, prognozowanie może nie być tak dokładne, jeśli zostanie wykonane w ten sposób.

Druga kolumna to twoje dane. Tego właśnie prognozujesz w przyszłości.

Aby podać przykład prognozowania danych, utworzyłem arkusz kalkulacyjny z fikcyjnymi miesięcznymi liczbami sprzedaży.

Proste prognozowanie

Aby rozpocząć prognozę programu Excel, zaznacz dane, a następnie przejdź do Dane> Arkusz prognozy.

Po kliknięciu przycisku Arkusz prognozy zobaczysz ładnie sformatowany wykres, który zawiera dane w kolorze niebieskim wraz z danymi predykcyjnymi w kolorze pomarańczowym.

Trafienie Stwórz w nowym oknie, a otrzymasz bardziej trwałą wersję tego wykresu wraz z tabelą przewidywanych wartości. Ta tabela zawiera również 95% przedziały ufności (które są również przedstawione na wykresie).

To już jest bardzo cenne. Ale poprawiając niektóre ustawienia, możesz dostosować otrzymywane dane, aby uzyskać jeszcze lepsze prognozy.

Dostosowywanie prognozy programu Excel

w Utwórz arkusz prognozy w oknie domyślnie widzisz tylko jedno menu rozwijane: Koniec prognozy.

Data zakończenia prognozy pozwala wybrać, jak daleko w przyszłość ma być prognozowana. Pamiętaj, że im dalej jedziesz, tym więcej błędów możesz zobaczyć.

Na powyższym obrazku widać rozszerzoną prognozę z przedziałem ufności powiększającym się w czasie.

Aby uzyskać dostęp do innych opcji prognozowania, kliknij Opcje w dolnej części okna.

Omówimy każdą opcję osobno.

Rozpoczęcie prognozy pozwala wybrać, kiedy Excel zacznie prognozować dane. Prawie w każdym przypadku chcesz, aby było to od ostatniego punktu na osi czasu.

Możesz ustawić to wcześniej, aby sprawdzić, czy prognozy Excela pasują do rzeczywistych danych, aby to sprawdzić. Ale ogólnie możesz bezpiecznie zignorować tę opcję.

Przedział ufności pozwala włączyć lub wyłączyć linie przedziału ufności i wybrać, czy ma zostać obliczony. W większości przypadków 95 procent jest wartością standardową dla przedziału ufności.

Oznacza to, że Excel ma 95 procent pewności, że przewidywana wartość spadnie między tymi dwiema liniami.

Sezonowość określa powtarzalny charakter osi czasu. W większości przypadków Excel obliczy to automatycznie. W innych przypadkach będziesz chciał to zmienić samodzielnie.

Na przykład w naszym arkuszu kalkulacyjnym zmiana sezonowości na 4 daje znacznie lepsze dane (wykorzystaliśmy 4, ponieważ w każdym powtarzającym się cyklu są cztery punkty). Oto jak wyglądała prognoza z automatycznym wykrywaniem sezonowości:

A oto jak to wygląda po zmianie sezonowości na 4:

To lepiej odzwierciedla typowy spadek sprzedaży około października. Warto spróbować we własnym arkuszu kalkulacyjnym, aby upewnić się, że otrzymujesz najlepsze możliwe dane.

Tylko upewnij się, że Twój numer sezonowości jest dokładny. Jeśli korzystasz z danych miesięcznych, sezonowość wynosi 12. Dane tygodniowe w ciągu roku wyniosą 52. Jeśli dane tygodniowe trwają ponad pół roku, użyj 26.

Jeśli sprawdzisz Uwzględnij statystyki prognoz, Excel zapewni dodatkowe podstawowe statystyki w końcowym arkuszu. Oto statystyki, które otrzymasz:

Jeśli nie wiesz, co oznaczają te statystyki, nie przejmuj się nimi.

Zakres osi czasu i Zakres wartości to kolumny, w których wpisałeś swoje dane. Możesz je dostosować, jeśli Excel zbiera nieprawidłowe dane, ale prawdopodobnie nie będziesz tego potrzebować.

Zanim porozmawiamy Uzupełnij brakujące punkty za pomocą, musimy mówić o brakujących danych w prognozowaniu. Funkcja prognozowania będzie działać, nawet jeśli nie masz wszystkich punktów danych na osi czasu. Nawet jeśli brakuje niektórych informacji, program Excel nadal uruchomi prognozę.

Standardową metodą wypełniania brakujących punktów jest interpolacja, która wykorzystuje średnie ważone wartości sąsiednich brakujących punktów. To dobra opcja, zwłaszcza biorąc pod uwagę, że jest to jedyna opcja.

Jeśli nie chcesz interpolować programu Excel, możesz wybrać Zera w menu rozwijanym, aby wstawić zera w brakujących obszarach danych. Ale jeśli te brakujące wartości prawdopodobnie nie będą faktycznie zerami, zrzucisz swoją prognozę.

The Agreguj zduplikowane wartości lista dotyczy konkretnego przypadku w Twoich danych. Jeśli dane zawierają dwa wpisy dla jednego punktu czasowego, program Excel uśrednia je w celu ustalenia końcowej wartości. Możesz to zmienić, licząc, medianę, sumę lub wiele innych operacji.

Po ustawieniu wszystkich opcji tak, jak chcesz, naciśnij Stwórz a otrzymasz najlepsze zgadywanie z Excela, jak będą wyglądać twoje dane w przyszłości, z wykresem i tabelą pełną przewidywanych wartości.

Przewiduj przyszłość dzięki funkcji prognozy programu Excel

Teraz, gdy wiesz, jak prognozować w programie Excel na podstawie danych historycznych, możesz zacząć korzystać z czcigodnego programu arkuszy kalkulacyjnych do przewidywania przyszłości! Lub przynajmniej dowiedzieć się, jak może wyglądać na podstawie przeszłości.

Prognozowanie programu Excel nie jest doskonałe, ale jest doskonałym narzędziem do posiadania w swoim arsenale programu Excel Power Up Excel z 10 dodatkami do przetwarzania, analizy i wizualizacji danych jak profesjonalista Power Up Excel z 10 dodatkami do przetwarzania, analizy i wizualizacji Dane jak profesjonalny Vanilla Excel jest niesamowity, ale możesz go jeszcze zwiększyć dzięki dodatkom. Bez względu na dane, które musisz przetworzyć, istnieje szansa, że ​​ktoś stworzył dla nich aplikację Excel. Oto wybór. . Budowanie modelu prognozy w Excelu jest łatwe i będzie dużą pomocą, gdy prowadzisz statystyki biznesowe.

Czy korzystasz z prognozy programu Excel? Jakie masz jeszcze wskazówki dla nowych użytkowników tej funkcji? Podziel się swoimi przemyśleniami w komentarzach poniżej!