Narzędzia do prognozowania w Microsoft Excel

Prognozowanie jest bardzo ważnym elementem w prawie każdej dziedzinie działalności, od gospodarki po inżynierię. Istnieje duża liczba oprogramowania, które specjalizuje się właśnie w tym kierunku. Niestety, nie wszyscy użytkownicy wiedzą, że zwykły arkusz kalkulacyjny Excel ma w swoich arsenale narzędzia do prognozowania, które ze względu na swoją skuteczność nie są gorsze od profesjonalnych programów. Dowiedzmy się, jakie są te narzędzia i jak przewidzieć w praktyce.

Procedura prognozowania

Celem każdej prognozy jest identyfikacja obecnego trendu i określenie oczekiwanego wyniku w odniesieniu do badanego obiektu w pewnym momencie w przyszłości.

Metoda 1: Trendline

Jednym z najpopularniejszych typów graficznego prognozowania w Excelu jest ekstrapolacja przeprowadzona przez zbudowanie linii trendu.

Spróbujmy przewidzieć zysk przedsiębiorstwa w ciągu 3 lat na podstawie danych tego wskaźnika z ostatnich 12 lat.

1. Sporządzamy zależność na podstawie danych tabelarycznych składających się z argumentów i wartości funkcji. Aby to zrobić, wybierz obszar tabeli, a następnie, będąc w zakładce "Wstaw" , kliknij ikonę żądanego rodzaju diagramu, który znajduje się w bloku "Diagramy" . Następnie wybierz odpowiedni typ dla konkretnej sytuacji. Najlepiej jest wybrać bitmapę. Możesz wybrać inny widok, ale wtedy, aby dane były wyświetlane poprawnie, będziesz musiał przeprowadzić edycję, w szczególności usunąć linię argumentu i wybrać inną skalę dla osi poziomej.
2. Teraz musimy zbudować linię trendu. Kliknij prawym przyciskiem myszy dowolny punkt na mapie. W aktywowanym menu kontekstowym wybierz "Dodaj linię trendu" .
3. Otworzy się okno do formatowania linii trendu. Może wybrać jeden z sześciu typów aproksymacji:
   • Liniowy ;
   • Logarytmiczny ;
   • Wykładniczy ;
   • Stopień mocy ;
   • Wielomian ;
   • Filtrowanie liniowe .

   Najpierw wybierzmy przybliżenie liniowe.

   W bloku ustawień "Prognoza" w polu "Przekaż do" ustaw liczbę "3.0" , ponieważ musimy przygotować prognozę na trzy lata. Ponadto można sprawdzić ustawienia "Pokaż równanie na schemacie" i "Umieścić wartość przybliżonej dokładności (R ^ 2) na schemacie" . Ostatni wskaźnik odzwierciedla jakość linii trendu. Po wprowadzeniu ustawień kliknij przycisk "Zamknij" .

4. Linia trendów jest budowana i na niej możemy określić przybliżoną kwotę zysku za trzy lata. Jak widzimy, do tego czasu powinno przekroczyć 4500 tysięcy rubli. Współczynnik R2 , jak już wspomniano powyżej, odzwierciedla jakość linii trendu. W naszym przypadku R2 wynosi 0,89 . Im wyższy współczynnik, tym wyższa niezawodność linii. Jego maksymalna wartość może wynosić 1 . Ogólnie przyjmuje się, że przy współczynniku powyżej 0,85 linia trendu jest niezawodna.
5. Jeśli nie jesteś zadowolony z poziomu niezawodności, możesz powrócić do okna formatu linii trendu i wybrać inny rodzaj przybliżenia. Możesz wypróbować wszystkie dostępne opcje, aby znaleźć najbardziej dokładne.

   Należy zauważyć, że efektywna prognoza wykorzystująca ekstrapolację za pośrednictwem linii trendu może być, jeśli okres prognozy nie przekracza 30% analizowanej bazy okresu. Oznacza to, że analizując okres 12 lat, nie możemy przedstawić skutecznej prognozy na więcej niż 3-4 lata. Ale nawet w tym przypadku będzie on względnie niezawodny, jeśli w tym czasie nie wystąpi działanie siły wyższej lub, przeciwnie, nadzwyczaj korzystne okoliczności, które nie występowały w poprzednich okresach.

Lekcja: Jak zbudować linię trendów w Excelu

Metoda 2: PREZENTACJA operatora

Ekstrapolacja danych tabelarycznych może odbywać się za pomocą standardowej funkcji PRESCASE Exxel . Ten argument należy do kategorii narzędzi statystycznych i ma następującą składnię:

=ПРЕДСКАЗ(X;известные_значения_y;известные значения_x)

"X" jest argumentem, którego wartość funkcji należy określić. W naszym przypadku argumentem będzie rok, w którym ma nastąpić prognozowanie.

"Znane wartości y" są podstawą znanych wartości funkcji. W naszym przypadku rola zysku za poprzednie okresy jest w jego roli.

"Znane wartości x" są argumentami, które odpowiadają znanym wartościom funkcji. W swojej roli jesteśmy policzeni przez lata, dla których zebrano informacje o zyskach z poprzednich lat.

Oczywiście linia czasu niekoniecznie musi być argumentem. Na przykład może to być temperatura, a wartością funkcji może być poziom rozszerzania się wody po podgrzaniu.

Podczas obliczania tej metody używana jest metoda regresji liniowej.

Przyjrzyjmy się niuansom używania operatora PRESCASE do określonego przykładu. Weź ten sam stół. Będziemy musieli poznać prognozę zysku na 2018 rok.

1. Wybierz pustą komórkę na arkuszu, na której chcesz wydrukować wynik przetwarzania. Kliknij przycisk "Wstaw funkcję" .
2. Kreator funkcji otworzy się. W kategorii "Statystyczne" wybierz nazwę "PRESCASE" , a następnie kliknij przycisk "OK" .
3. Otworzy się okno argumentów. W polu "X" określamy wartość argumentu, do którego chcemy znaleźć wartość funkcji. W naszym przypadku jest to 2018. Dlatego wpisujemy hasło "2018" . Ale lepiej jest określić ten wskaźnik w komórce na arkuszu, a w polu "X" wystarczy podać link do niego. Umożliwi to w przyszłości automatyzację obliczeń i, jeśli to konieczne, łatwą zmianę roku.

   W polu "Znane wartości y" podajemy współrzędne kolumny "Zysk przedsiębiorstwa" . Można to zrobić, ustawiając kursor w polu, a następnie przytrzymując lewy przycisk myszy i wybierając odpowiednią kolumnę na arkuszu.

   Podobnie w polu "Znane wartości x" wpisujemy adres kolumny "Rok" z danymi za miniony okres.

   Po wprowadzeniu wszystkich informacji kliknij przycisk "OK" .

4. Operator oblicza na podstawie wprowadzonych danych i wyświetla wynik na ekranie. Na 2018 r. Planuje się zysk w wysokości 4566,7 tys. Rubli. Na podstawie wynikowej tabeli możemy wykreślić wykres za pomocą omówionych powyżej narzędzi do tworzenia wykresów.
5. Jeśli zmienisz rok w komórce, która została użyta do wprowadzenia argumentu, wynik zmieni się odpowiednio, a wykres zostanie automatycznie zaktualizowany. Na przykład według prognoz w 2019 r. Kwota zysku wyniesie 4637,8 tys. Zł.

Nie zapominaj jednak, że tak jak przy konstruowaniu linii trendu, przedział czasu do okresu prognozy nie powinien przekraczać 30% całego okresu, dla którego baza danych została zgromadzona.

Lekcja: Ekstrapolacja w programie Excel

Metoda 3: TRENDY operatora

Do prognozowania możesz użyć jeszcze jednej funkcji - TRENDY . Należy również do kategorii podmiotów statystycznych. Jego składnia pod wieloma względami przypomina składnię narzędzia PRESCASE i wygląda tak:

=ТЕНДЕНЦИЯ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

Jak widać, argumenty "Znane wartości y" i "Znane wartości x" w pełni odpowiadają podobnym elementom operatora PRESCASE , a argument "Nowe wartości x" odpowiada argumentowi "X" poprzedniego narzędzia. Ponadto TRENDY mają dodatkowy argument "Stały" , ale nie jest to obowiązkowe i jest stosowany tylko wtedy, gdy występują stałe czynniki.

Ten operator jest najskuteczniej wykorzystywany w obecności liniowej zależności funkcji.

Zobaczmy, jak to narzędzie będzie działać z tą samą tablicą danych. W celu porównania uzyskanych wyników określimy punkt prognostyczny w 2019 roku.

1. Oznaczamy komórkę jako wynik wyniku i uruchamiamy Kreatora funkcji w zwykły sposób. W kategorii "Statystyczne" znajdujemy i wybieramy nazwę "TRENDY" . Kliknij przycisk "OK" .
2. Otwiera pole argumentu operatora TENDENCY. W polu "Znane wartości y" wpisujemy współrzędne kolumny "Przedsiębiorstwo Profit " w sposób opisany powyżej. W polu "Znane wartości x" wpisz adres kolumny "Rok" . W polu "Nowe wartości x" wpisujemy odwołanie do komórki, w której znajduje się numer roku, do którego ma zostać określona prognoza. W naszym przypadku jest to 2019. Pole "Stałe" pozostaje puste. Klikamy przycisk "OK" .
3. Operator przetwarza dane i wyświetla wynik na ekranie. Jak widać, suma prognozowanego zysku na 2019 r., Obliczona metodą zależności liniowej, spowoduje, podobnie jak w poprzedniej metodzie obliczania, 4637,8 tys. Rubli.

Metoda 4: WZROST GOSPODARKI

Inną funkcją, dzięki której można tworzyć prognozy w Excelu, jest WZROST GOSPODARKI. Odnosi się także do grupy statystycznej narzędzi, ale w przeciwieństwie do poprzednich metoda jest stosowana nie metodą zależności liniowej, ale metodą wykładniczą. Składnia tego narzędzia wygląda następująco:

=РОСТ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

Jak widać, argumenty tej funkcji dokładnie powtarzają argumenty operatora TENDENCJI , więc nie będziemy ich omawiać po raz drugi, ale od razu przejdziemy do zastosowania tego narzędzia w praktyce.

1. Wybierz wyjściową komórkę wyjściową i wywołaj Kreatora funkcji w zwykły sposób. Na liście operatorów statystycznych znajdź pozycję "GROWTH" , zaznacz ją i kliknij przycisk "OK" .
2. Okno argumentu powyższej funkcji jest aktywne. Wprowadzamy dane w polach tego okna w taki sam sposób, w jaki wpisaliśmy je w oknie argumentu operatora TENDENCY. Po wprowadzeniu informacji kliknij przycisk "OK" .
3. Wynik przetwarzania danych jest wyświetlany na monitorze w komórce określonej powyżej. Jak widać, tym razem wynik to 4682,1 tysięcy rubli. Różnice w wynikach przetwarzania danych przez operatora TENDENCY są nieistotne, ale są dostępne. Wynika to z faktu, że narzędzia te wykorzystują różne metody obliczeń: metodę zależności liniowej i metodę zależności wykładniczej.

Metoda 5: Operator REGLINP

Operator REGLINP korzysta z metody aproksymacji liniowej. Nie należy go mylić z metodą zależności liniowej używaną przez narzędzie TRENDY . Jego składnia jest następująca:

=ЛИНЕЙН(Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

Dwa ostatnie argumenty są opcjonalne. Z pierwszymi dwoma znamy poprzednie metody. Ale prawdopodobnie zauważyłeś, że nie ma argumentu w tej funkcji, która wskazuje na nowe wartości. Faktem jest, że to narzędzie określa jedynie zmianę w przychodach na jednostkę czasu, która w naszym przypadku wynosi jeden rok, ale sumę należy obliczyć osobno, dodając do ostatniej faktycznej wartości zysku wynik obliczenia operatora REGURA pomnożony przez liczbę lat.

1. Wybieramy komórkę, w której będą wykonywane obliczenia, i uruchomimy Kreatora funkcji. Wybierz nazwę "REGLINP" w kategorii "Statystyczne" i kliknij przycisk "OK" .
2. W polu "Znane wartości y" , w otwartym oknie argumentu wprowadź współrzędne kolumny "Zysk przedsiębiorstwa". W polu "Znane wartości x" wpisujemy adres kolumny "Rok" . Pozostałe pola pozostaną puste. Następnie kliknij przycisk "OK" .
3. Program oblicza i wyświetla wartość trendu liniowego w wybranej komórce.
4. Teraz musimy ustalić wartość prognozowanego zysku na 2019 rok. Ustaw znak "=" na dowolną pustą komórkę na arkuszu. Klikamy na komórkę, która zawiera rzeczywistą kwotę zysku za ostatni studiowany rok (2016). Umieść znak "+" . Następnie kliknij komórkę zawierającą poprzednio obliczony trend liniowy. Umieszczamy znak "*" . Ponieważ od ostatniego roku okresu studiów (2016) do roku, w którym ma nastąpić prognoza (2019), okres wynosi trzy lata, a następnie liczbę "3" w komórce. Aby wykonać obliczenia, kliknij przycisk Enter .

Jak widać prognozowany zysk, obliczony metodą przybliżenia liniowego, w 2019 r. Wyniesie 4614.9 tys. Rubli.

Metoda 6: operator LGRFPRIBL

Ostatnim narzędziem, które rozważymy będzie LGRF . Ten operator wykonuje obliczenia na podstawie wykładniczej metody aproksymacji. Jego składnia ma następującą strukturę:

= ЛГРФПРИБЛ (Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

Jak widać, wszystkie argumenty całkowicie powtarzają odpowiednie elementy poprzedniej funkcji. Algorytm do obliczania prognozy zmieni się nieco. Funkcja obliczy trend wykładniczy, który pokaże, ile razy zmieni się kwota przychodu na jeden okres, czyli na rok. Musimy znaleźć różnicę w zysku między ostatnim faktycznym okresem a pierwszym planowanym pomnożeniem przez liczbę planowanych okresów (3) i dodaniem do wyniku sumy ostatniego faktycznego okresu.

1. Na liście operatorów Kreatorów funkcji wybierz nazwę "LGRFI" . Kliknij przycisk "OK" .
2. Otworzy się okno argumentów. W nim wprowadzamy dane dokładnie tak, jak zrobiliśmy to za pomocą funkcji REGLINP . Klikamy przycisk "OK" .
3. Wynik trendu wykładniczego jest obliczany i wysyłany do wyznaczonej komórki.
4. Umieść "=" w pustej komórce. Otwórz nawiasy i wybierz komórkę zawierającą wartość przychodów za ostatni faktyczny okres. Umieść znak "*" i wybierz komórkę zawierającą wykładniczy trend. Umieszczamy znak minus i ponownie klikamy element, w którym znajduje się kwota przychodu za ostatni okres. Zamykamy nawias i przesuwamy znaki "* 3 +" bez cudzysłowów. Ponownie kliknij tę samą komórkę, która została wybrana po raz ostatni. Aby wykonać obliczenia, naciśnij przycisk Enter .

Prognozowana kwota zysku w 2019 r., Obliczona metodą przybliżenia wykładniczego, wyniesie 4 639,2 tys. Rubli, co również nie odbiega zbytnio od wyników uzyskanych w kalkulacji poprzednimi metodami.

Lekcja: Inne funkcje statystyczne w Excelu

Dowiedzieliśmy się, w jaki sposób można tworzyć prognozy w programie Excel. Graficznie można to zrobić za pomocą linii trendu i analitycznej - za pomocą wielu wbudowanych funkcji statystycznych. W wyniku przetwarzania identycznych danych przez te podmioty mogą powstać różne wyniki. Ale nie jest to zaskakujące, ponieważ wszystkie one używają różnych metod obliczeniowych. Jeśli fluktuacja jest niewielka, wszystkie te opcje, mające zastosowanie do konkretnego przypadku, można uznać za względnie niezawodne.

• ← Transponuj macierz w programie Microsoft Excel
• Obliczanie NPV w Microsoft Excel →