Prognozowanie w Excelu

Najprostszą możliwością prognozowania w Excelu jest tworzenie serii liczb. Wystarczy, że napiszemy dwie liczby, np. 1 i 3 (najwygodniej jedna pod drugą). Jeśli zaznaczymy te dwie liczby i złapiemy za prawy dolny róg zaznaczenia lewym przyciskiem myszy i przeciągniemy w dół (rysunek 1)…

…Excel wykryje różnicę pomiędzy liczbami i kolejne (prognozowane) liczby będą powiększane dokładnie o tę różnicę. Jednak prognozowanie z dwóch liczb to słabe prognozowanie. W tym przykładzie chodziło o zademonstrowanie zasady postępowania z przeciąganiem.

W drugim przykładzie powiększymy nasz ciąg do pięciu liczb {2; 6; 7; 10; 17}. Znów zaznaczamy wszystkie komórki z liczbami, łapiemy prawy dolny róg zaznaczenia lewym przyciskiem myszy i przeciągamy w dół (rysunek 2).

Tym razem sposób obliczenia nie wygląda na prosty, ale jeśli przyjrzymy się uważnie, to zauważymy, że od drugiej dołożonej liczby (prognozy) różnica pomiędzy kolejnymi liczbami wynosi 3,4. Jak Excel znalazł (dokonał prognozy) kolejnye liczby? Okazuje się to prostsze, niż wygląda na pierwszy rzut oka. Na podstawie dostępnych liczb Excel wyznaczył równanie liniowe (ax + b) i podstawiał kolejne x, czyli numer liczby.

Później w artykule zobaczymy linię i równanie trendu na wykresie. Operacje, które Excel wykonuje w tym przykładzie, są identyczne, jednak nie są przedstawiane wizualnie, lecz jedynie za pomocą ciągu liczb.

Co w sytuacji, kiedy rozwój twojej firmy albo kolonii bakterii jest bardziej dynamiczny i lepiej odzwierciedlałby go ciąg geometryczny, a nie liniowy? Zasada jest analogiczna, ale przeciągamy zaznaczenie, trzymając prawy, a nie lewy przycisk myszy. Zaczynając od prostego przykładu liczb 1 i 2 (rysunek 3), możemy wyraźnie zobaczyć, że każda kolejna liczba jest dwa razy większa od poprzedniej.

Tym razem Excel w pamięci oblicza parametry funkcji wykładniczej (aebx), które najlepiej przybliżają dostępne punkty. Dla przykładu: z ciągu {1;3;5;7;16} Excel oblicza kolejne liczby jako elementy ciągu wykładniczego/geometrycznego (rysunek 4).

Linia trendu

Trochę bardziej widowiskowa w procesie prognozy jest linia trendu. Ma też więcej możliwości. Można ją dołożyć do większości wykresów, np. kolumnowego, liniowego czy punktowego.

Pokażemy linię trendu na przykładzie – przeanalizujemy, czy liczba godzin, jaką poświęcamy na naukę, wpływa na ocenę z egzaminu. Odpowiedź znajdziemy za pomocą wykresu i linii trendu.

Wstawiamy wykres punktowy (XY) na podstawie naszych danych i dodajemy do niego linię trendu (rysunek 5).

Domyślnie dodaje się prosta linia (równanie liniowe – rysunek 5), ale nie jest to jedyna możliwość i stwierdzenie, że rysujemy „linię” trendu, jest niekiedy mylące, bo Excel umożliwia wyznaczanie trendu za pomocą różnych równań. Wystarczy przejść do większej liczby opcji linii trendu (rysunek 5) lub zaznaczyć linię trendu, naciskając Ctrl + 1. Pojawi się okno (rysunek 6), w którym zobaczymy przede wszystkim, jakich równań/funkcji możemy użyć do wyznaczenia trendu naszych danych.

Linia prosta (równanie liniowe) jest domyślną możliwością, ale nie zawsze najlepszą. Możemy rysować linię trendu jako równanie wykładnicze, logarytmiczne, wielomianowe (od kwadratowego do szóstego poziomu), a nawet potęgowe (średnia ruchoma wyznacza średnią z sąsiadujących punktów i jako taka nie jest linią trendu, bo nie umożliwia przedstawienia prognozy – kolejnych punktów na wykresie).

O prognozie za pomocą linii trendu powiemy w dalszej części tekstu, ale najpierw pomówmy o równaniach, bo tylko narysowana linia trendu ułatwia naszemu umysłowi skupienie się na wyraźnym trendzie. Nasz mózg sam próbuje ten trend (wzorzec) znaleźć. Potrzebujemy czegoś bardziej matematycznego, czyli parametrów równania. Służą do tego dwa check-boksy na dole okna Formatowania linii trendu (rysunek 7).