Minuty czytania: 5

Regresja liniowa w Excelu, czyli prognozuj przyszłość

Regresja liniowa w Excelu pozwala na prognozowanie przyszłych wyników. Brzmi fantastycznie? Oczywiście, że tak ale nie jest to takie proste. Jednak warto znać tę metodę i sposób jej wykorzystania w Excelu, aby poszerzyć zakres swoich analitycznych możliwości.

Artykuł i film nie wyjaśnia wszystkich statystycznych zależności metody. Jeśli chcesz wiedzieć więcej o regresji liniowej, od strony teoretycznej odsyłam do innych stron. Tutaj dowiesz się jak szybko i w praktyce zastosować tę metodę w Excelu, aby prognozować przyszłe wyniki.

Regresja liniowa: podstawy

Funkcja regresji liniowej odpowiada na pytanie: „Jaką wartość przyjmie dane zjawisko (zmienna objaśniana), w zależności od wartości innych zjawisk (zmienne objaśniające)” W nazwie mamy pojęcie „liniowa”, zatem metoda zakłada, że zależności pomiędzy zjawiskiem objaśnianym oraz zmiennymi objaśniającymi jest właśnie liniowa.

Tyle teorii 🙂 Przejdźmy do praktyki. Mamy przykład, w którym będziemy chcieli przewidzieć wielkość sprzedaży dla kolejnych okresów, w zależności od nakładów na marketing. Na marginesie zawsze podobał mi się pewien cytat, dotyczący marketingu:

„Połowa pieniędzy wydawanych na reklamę jest wyrzucana w błoto. Problem w tym, że nie wiem, która to połowa”

John Wanamaker

Ponieważ mamy zależność tylko od jednej zmiennej – nakładów na marketing – funkcja przyjmie postać:

y = m1*x1 + b

gdzie m1 to tzw. współczynnik kierunkowy, a b to wyraz wolny.

Regresja liniowa z jedną zmienną

Aby obliczyć teoretyczną wielkość sprzedaży w następnym okresie, ustawiamy się w komórce w której chcemy mieć tę wartość i wykorzystujemy funkcję REGLINX.LINIOWA. 

Funkcja ma 3 parametry:

1. x – w naszym przypadku to nakłady na marketing jakie chcemy ponieść w kolejnym okresie,
2. znane y – to znana przez nas, historyczna wielkość sprzedaży w poprzednich okresach
3. znane x – to znana przez nas, historyczna wielkość nakładów na marketing w poprzednich okresach

Oczywiście podając zakresy komórek, w której znajduje się historyczna sprzedaż oraz nakłady na marketing, użyj adresowania bezwzględnego, aby odwoływać się cały czas do tych samych danych. Funkcja powinna wyglądać następująco:

Jak interpretować wyniki? Przy założeniu, że w styczniu 2019 roku wydamy 5 352 zł na marketing, to nasza przewidywana wielkość sprzedaży wyniesie 4 409 072 zł. Oczywiście dane są całkowicie teoretyczne. W rzeczywistości raczej nie osiągamy takich zwrotów z inwestycji 🙂

Możesz teraz skopiować funkcję na kolejne okresy, aby znaleźć odpowiedź na pytanie jak będzie wyglądała sprzedaż w kolejnych okresach. Poniższy wykres pokazuje zależność pomiędzy nakładami na marketing, a wielkością sprzedaży – właśnie na podstawie powyższych obliczeń.

Na sprzedaż wpływa wiele parametrów

Życie nie jest tak proste, aby sprzedaż zależała tylko od nakładów na marketing. W większości przypadków na dane zjawisko wpływa wiele zmiennych. Dlatego teraz pokażę Ci, jak wykorzystać funkcję regresji liniowej z użyciem wielu zmiennych.

W naszym przykładzie, przyjmiemy że wielkość sprzedaży zależy od następujących zmiennych:

• ilość sklepów 
• ilość promocji w okresie 
• nakłady na marketing 
• ilość pracowników w sklepie 

Podobnie jak wcześniej, na podstawie historycznych danych będziemy chcieli wyliczyć wielkość sprzedaży w kolejnych okresach. Aby tego dokonać, wykorzystamy inną funkcję – REGLINP.

Funkcja przyjmuje postać:

y = m1*x1 + m2*x2 + … + mn*xn + b

Regresja liniowa z wieloma zmiennymi

Aby łatwiej korzystać z tej funkcji, przygotuj sobie tabelę pomocniczą. Wypisz w niej wszystkie zmienne ale w odwrotnej kolejności niż są umieszczone w tabeli z danymi. To znaczy, że jeżeli mamy zmienną: “ilość sklepów” na pierwszym miejscu w tabeli z danymi, to w tabeli pomocniczej jest ona na ostatnim miejscu. I tak dalej.

Teraz za pomocą funkcji REGLINP wyliczymy współczynniki kierunkowe dla poszczególnych zmiennych. Ponieważ funkcja ma zwrócić nam wektor ze współczynnikami kierunkowymi i wyrazem wolnym (b) – zaznacz cały obszar w tabeli pomocniczej i wpisz funkcję. W przypadku naszych danych przykładowych, przyjmie ona taką postać:

{=REGLINP(D4:D27;E4:H27;PRAWDA;FAŁSZ)}

Pierwsza zmienna to znane y, czyli wielkości sprzedaży w poprzednich okresach. Następnie znane x – tutaj możesz podać cały zakres danych, gdzie znajdują się dane o: ilości sklepów, promocji, nakładów na marketing i pracownikach. 

Trzeci argument funkcji to tzw. stała, gdzie mówimy w jaki sposób ma być obliczana funkcja. Wpisz PRAWDA (1), co oznacza normalne obliczanie funkcji (bez wchodzenia w szczegóły – na tym nam w tym przypadku zależy)

Ostatni parametr mówi nam czy funkcja ma zwracać nam dodatkowe statystyki. Nie skorzystamy z opcji dodatkowych statystyk, dlatego wybieramy FAŁSZ (0)

Całą formułę musimy zatwierdzić kombinacją klawiszy CTRL + SHIFT + ENTER, ponieważ jest to formuła tablicowa. W ten sposób obliczyliśmy współczynniki kierunkowe naszej funkcji.

Teraz możemy je wykorzystać w kolejnym okresie, aby przewidzieć teoretyczną wartość sprzedaży. W tym celu podstawiamy dane do wzoru i tworzymy formułę:

=$I$36+E28*$H$36+F28*$G$36+G28*$F$36+H28*$E$36

Czyli mnożymy w niej poszczególne współczynniki kierunkowe, ze znanymi wartościami poszczególnych zmiennych, takich jak: ilość sklepów, pracowników itd.

Finalnie otrzymamy teoretyczną wielkość sprzedaży w kolejnych miesiącach uzależnioną od czterech zmiennych. Wpisana przez nas formuła jest oczywiście dynamiczna – czyli możesz zmieniać wartości poszczególnych zmiennych, co wpłynie na przewidywaną wartość sprzedaży.

Możesz teraz symulować jak zmieni się sprzedaż, w zależności od wielkości pewnych zjawisk w Twojej firmie.

---

---

Podsumowanie

Czy musisz znać skomplikowane teorie, aby w praktyczny sposób wykorzystywać różne metody statystyczne? Stawiam tezę, że nie. Na pewno powinieneś wiedzieć jak interpretować wyniki i jakie są ograniczenia danej metody.

W omawianym w artykule przykładzie pokazałem Ci, jak w praktyczny sposób zastosować regresję liniową w Excelu. Możesz szybko wykorzystać tę wiedzę w swojej pracy. Oczywiście o ile rozumiesz, że nie warto iść do Twojego szefa z tezą, że wzrost nakładów na marketing o x zł, spowoduje wprost wzrost sprzedaży do y zł 😉

Jeżeli uznasz wiedzę zawartą w tym artykule za przydatną – udostępnij proszę artykuł w swoich social mediach, używając przycisku poniżej.