Regresja to narzędzie analityczne, którego używamy do analizowania dużych ilości danych oraz tworzenia prognoz i przewidywań w programie Microsoft Excel.
Chcesz przewidzieć przyszłość? Nie, nie będziemy uczyć się astrologii. Jesteśmy w liczbach i dzisiaj nauczymy się analizy regresji w Excelu.
Aby przewidzieć przyszłe szacunki, przeanalizujemy:
- ANALIZA REGRESJI Z WYKORZYSTANIEM FUNKCJI EXCEL (RĘCZNE WYKRYWANIE REGRESJI)
- ANALIZA REGRESJI Z WYKORZYSTANIEM DODATKU EXCEL ANALYSIS TOOLPAK
- WYKRES REGRESJI W EXCELU
Zróbmy to…
Scenariusz:
Załóżmy, że sprzedajesz napoje bezalkoholowe. Jak fajnie będzie, jeśli potrafisz przewidzieć:
- Ile napojów bezalkoholowych zostanie sprzedanych w przyszłym roku na podstawie danych z poprzedniego roku?
- Na jakich polach należy się skoncentrować?
- A jak możesz zwiększyć sprzedaż, zmieniając strategię?
To będzie z zyskiem niesamowite. Prawda?… Wiem. Więc zacznijmy.
Masz 11 rekordów sprzedanych sprzedawców i napojów bezalkoholowych.
Teraz na podstawie tych danych chcesz przewidzieć liczbę sprzedawców potrzebną do osiągnięcia 2000 sprzedaży napojów bezalkoholowych.
Równanie regresji jest narzędziem do tak dokładnych szacunków. Aby to zrobić, musimy najpierw poznać regresję.
ANALIZA REGRESJI Z WYKORZYSTANIEM FUNKCJI EXCEL (RĘCZNE WYKRYWANIE REGRESJI)
Ta część sprawi, że lepiej zrozumiesz regresję niż tylko omówienie procedury regresji w programie Excel.
Wstęp:
Prosta regresja liniowa:
Badanie związku między dwiema zmiennymi nazywa się Prostą Regresją Liniową. Gdzie jedna zmienna zależy od drugiej zmiennej niezależnej. Zmienna zależna jest często nazywana nazwami takimi jak Driven, Response i Target variable. A zmienna niezależna jest często wymawiana jako zmienna prowadząca, predyktor lub po prostu zmienna niezależna. Te nazwy jasno je opisują.
Teraz porównajmy to z twoim scenariuszem. Chcesz poznać liczbę sprzedawców potrzebnych do osiągnięcia 2000 sprzedaży. Więc tutaj zmienną zależną jest liczba sprzedawców, a zmienną niezależną sprzedawane napoje bezalkoholowe.
Zmienna niezależna jest najczęściej oznaczana jako x i zmienna zależna jako tak.
W naszym przypadku sprzedawane są napoje bezalkoholowe x a liczba sprzedawców wynosi tak.
Jeśli chcemy wiedzieć, ile napojów bezalkoholowych zostanie sprzedanych, jeśli się umówimy 200 sprzedawców, wtedy scenariusz będzie odwrotnie.
Iść dalej.
„Prosta” matematyka równania regresji liniowej:
Cóż, to nie jest proste. Ale Excel ułatwił to.
Musimy przewidzieć wymaganą liczbę sprzedawców we wszystkich 11 przypadkach, aby uzyskać 12 najbliższą prognozę.
Powiedzmy:
Sprzedany napój bezalkoholowy jest x
Numer sprzedawców jest tak
Przewidywany tak (liczba sprzedawców) zwana także równanie regresji, byłoby
| x*nachylenie+przecięcie (zrelaksuj się, mam to pokryte) |
Teraz musisz się zastanawiać, gdzie stat czy zdobędziesz nachylenie i przechwycisz. Nie martw się, Excel ma dla nich funkcje. Nie musisz uczyć się, jak znaleźć skarpę i ręcznie ją przechwycić.
Jeśli chcesz, przygotuję na to osobny tutorial. Daj mi znać w sekcji komentarzy. Oto kilka ważnych narzędzi do analizy danych.
Przejdźmy teraz do naszych obliczeń:
Krok 1: Przygotuj ten mały stolik
Krok 2: Znajdź nachylenie linii regresji
Funkcja Excel dla stoków to
| = NACHYLENIE(znane_y;znane_x) |
Twoje znane_y są w zasięgu B2:B12 a znane_x są w zakresie C2:C12
W komórce B16, napisz wzór poniżej
| =NACHYLENIE(B2:B12; C2:C12) |
(Uwaga: Nachylenie jest również nazywane współczynnikiem x w równaniu regresji)
Dostaniesz 0.058409. Zaokrąglij do 2 cyfr dziesiętnych, a otrzymasz 0.06.
Krok 3: Znajdź punkt przecięcia linii regresji
Funkcja Excela dla przechwycenia to
| =PRZECIĘCIE(znane_y; znane_x) |
Wiemy, co nasze znane x i y
W komórce B17, zapisz tę formułę
| =PRZECIĘCIE(B2:B12, C2:C12) |
Otrzymasz wartość -1,1118969. Zaokrąglij do 2 cyfr dziesiętnych. Dostaniesz -1.11.
Nasze równanie regresji liniowej to = x*0,06 + (-1,11). Teraz możemy łatwo przewidzieć możliwe y w zależności od celu x.
Krok 4: W D2 napisz poniższy wzór
| =C2*$16$+$17$(Równanie regresji) |
Otrzymasz wartość 13.55.
Wybierz D2 do D13 i naciśnij CTRL+D wypełnić formułę w zakresie D2:D13
W komórce D13 masz wymaganą liczbę sprzedawców.
| Stąd, aby osiągnąć cel 2000 Sprzedaż napojów bezalkoholowych, potrzebujesz szacunkowej liczby 115,71 sprzedawców lub powiedzmy 116, ponieważ krojenie ludzi na kawałki jest nielegalne. |
Teraz, korzystając z tego, możesz łatwo przeprowadzić analizę warunkową w programie Excel. Po prostu zmień liczbę sprzedaży, a zobaczysz, jak wielu sprzedawców będzie potrzebnych, aby osiągnąć ten cel sprzedaży.
Pobaw się, aby dowiedzieć się:
Ile siły roboczej potrzebujesz, aby zwiększyć sprzedaż?
Ile wzrośnie sprzedaż, jeśli zwiększysz swoich sprzedawców?
Spraw, aby Twoja ocena była bardziej wiarygodna:
Teraz wiesz, że potrzebujesz 116 sprzedawców, aby zrealizować 2000 sprzedaży.
W analityce nic się nie mówi i nie wierzy. Musisz podać procent wiarygodności swojego oszacowania. To tak, jakby dać świadectwo twojego równania.
Wzór na współczynnik korelacji:
Następną rzeczą, którą zostaniesz zapytany, będzie to, jak bardzo te dwie zmienne są powiązane. W kategoriach statycznych musisz podać współczynnik korelacji.
Funkcja Excela do korelacji to
| =WSPÓŁPRACA(tablica1; tablica2) |
W twoim przypadku znane_x i Know_y to niezależnie tablica1 i tablica2.
W B18 wpisz ten wzór
| =WSPÓŁPRACA((B2:B12, C2:C12) |
Będziesz miał 0.919090. Sformatuj komórkę B2 na wartość procentową. Teraz mam 92% korelacji.
Teraz, co to? 92% znaczy. To znaczy, tam 92% szans na wzrost sprzedaży, jeśli zwiększysz liczbę sprzedawców i 92% sprzedaży spadnie, jeśli zmniejszysz liczbę sprzedawców. Nazywa się to Dodatni współczynnik korelacji.
Giermek R (R^2) :
Wartość R Squire mówi ci, o jaki procent twoje równanie regresji nie jest przypadkiem. Na ile jest to zgodne z dostarczonymi danymi.
Funkcja Excela dla giermków R to RSQ.
| RSQ(znane_y, znane_x) |
W naszym przypadku otrzymamy wartość R Squire w komórce B19.
W B19 wpisz ten wzór
| =RSQ(B2:B12, C2:C12) |
Mamy więc 84% wartości r Square. Co jest bardzo dobrym wyjaśnieniem naszej regresji. Mówi, że 84% naszych danych to nie przypadek. Y (liczba sprzedawców) jest bardzo zależna od X (sprzedaż napojów bezalkoholowych).
Istnieje wiele innych testów, które możemy wykonać na tych danych, aby zapewnić naszą regresję. Ale ręcznie będzie to skomplikowana i długa procedura. Dlatego excel dostarcza Analysis Toolpak. Za pomocą tego narzędzia możemy przeprowadzić analizę regresji w kilka sekund.
REGRESJA W PROGRAMIE EXCEL Z WYKORZYSTANIEM DODATKU EXCEL ANALYSIS TOOLPAK
Jeśli już wiesz, czym są równania regresji i chcesz po prostu szybko uzyskać wyniki, to ta część jest dla Ciebie. Ale jeśli chcesz łatwo zrozumieć równania regresji, przewiń w górę do ANALIZA REGRESJI Z WYKORZYSTANIEM FUNKCJI EXCEL (RĘCZNE WYSZUKIWANIE REGRESJI).
Program Excel udostępnia całą gamę narzędzi do analizy w swoim pakiecie Analysis Toolpak. Domyślnie nie jest dostępny w zakładce Dane. Musisz to dodać. Więc dodajmy to najpierw.
Dodawanie pakietu narzędzi analitycznych do programu Excel 2016
Jeśli nie wiesz, gdzie jest analiza danych w programie Excel, wykonaj następujące kroki
Krok 1: Przejdź do opcji programu Excel: Plik? Opcje? Dodatki
Krok 2: Kliknij Dodatki. Zobaczysz listę dostępnych dodatków.
Wybierz Analysis ToolPak i na dole okna znajdź zarządzaj. W zarządzaniu wybierz Dodatki programu Excel i kliknij GO.
Otworzy się okno dodatków. Tutaj wybierz Analysis ToolPak. Następnie kliknij przycisk OK.
Teraz masz dostęp do wszystkich funkcji ToolPak do analizy danych z zakładki Data.
Korzystanie z narzędzia Analysis ToolPak do regresji
Krok 1: Przejdź do zakładki Dane, Zlokalizuj analizę danych. Następnie kliknij na niego.
Pojawi się okno dialogowe.
Krok 2: Znajdź „Regresja” na liście narzędzi analitycznych i naciśnij przycisk OK.
Regresja pojawi się okno wprowadzania. Zobaczysz kilka dostępnych opcji wprowadzania. Ale na razie skoncentrujemy się tylko na Zakresie Y i Zakresie X, pozostawiając wszystko inne domyślnie.
Krok 4: Podaj dane wejściowe:
Liczba sprzedawców to Tak
Sprzedaż napojów bezalkoholowych jest x
Stąd
- Zakres Y= B2:B11
I
- Zakres X = C2:C11
Dla zakresu wyjściowego wybrałem E4 na tym samym arkuszu. Możesz wybrać nowy arkusz, aby uzyskać wyniki w nowym arkuszu w tym samym skoroszycie lub całkowicie nowym skoroszycie. Po zakończeniu wprowadzania danych naciśnij przycisk OK.
Wyniki:
Otrzymasz różne informacje z Twoich danych. Nie daj się przytłoczyć. Nie musisz spożywać wszystkich potraw.
Zajmiemy się tylko tymi wynikami, które pomogą nam oszacować wymaganą liczbę sprzedawców
Krok 5: Znamy równanie regresji do estymacji tak, to jest
x*nachylenie+przecięcie
Musimy tylko zlokalizować Nachylenie oraz Przechwycić w wynikach.
I oto one.
Wyraźnie wspomniano o współczynniku przecięcia.
Nachylenie jest napisane jako „X Zmienna 1’, czasami wymieniany również jako współczynnik X. Zaokrąglij je, a otrzymamy -1.11 jako Przechwytywanie oraz 0,06 jako nachylenie.
Krok 6: Na podstawie wyników możemy kierować równaniem regresji. I to by było
=x*(0,06) + (-1,11)
Przygotuj tę tabelę w Excelu.
Na razie, x to 2000, który znajduje się w komórce E2.
W komórce F2 wprowadź tę formułę
=E2*F21+F20
Otrzymasz wynik 115.7052757.
Zaokrąglenie to da nam 116 Wymaganych Sprzedawców.
Tak więc nauczyliśmy się tworzyć równanie regresji ręcznie i przy użyciu Analysis ToolPak. Jak możesz użyć tego równania do oszacowania przyszłych statystyk?
Teraz przyjrzyjmy się wynikom regresji podanym przez Analysis Toolpak.
Zrozumienie wyniku regresji:
Nie ma korzyści, jeśli przeprowadzasz analizę regresji za pomocą pakietu narzędzi analitycznych w programie Excel i nie możesz zinterpretować jej znaczenia.
Sekcja podsumowująca:
Jak sama nazwa wskazuje, jest to podsumowanie danych.
-
- Wielokrotne R: Informuje, w jakim stopniu równanie regresji jest dopasowane do danych. Nazywany jest również współczynnikiem korelacji.
W naszym przypadku jest to 0.919090619 lub 0.92 (podsumowanie). Oznacza to, że jeśli zwiększymy liczbę naszych sprzedawców, istnieje 92% szans na wzrost sprzedaży.
-
- R Square: Mówi o wiarygodności znalezionej regresji. Mówi nam, ile obserwacji jest częścią naszej linii regresji. W naszym przypadku jest to 0,844727566 lub 0,85. Oznacza to, że nasza regresja jest dopasowana w 85%.
- Regulowany kwadrat R: Dopasowany kwadrat jest po prostu bardziej potwierdzoną wersją kwadratu R. Głównie przydatne w analizie regresji wielokrotnej.
- Standardowy błąd: Podczas gdy R. Squire informuje, ile punktów danych znajduje się w pobliżu linii regresji, błąd standardowy informuje o tym, jak daleko punkt danych może oddalić się od linii regresji.
W naszym przypadku jest to 6.74.
- Obserwacja: Jest to po prostu liczba obserwacji, która w naszym przykładzie wynosi 11.
Sekcja Anova:
Ta sekcja jest rzadko używana w regresji liniowej.
- df. Jest to stopień swobody. Jest używany podczas ręcznego obliczania regresji.
- SS. Suma kwadratów. To tylko suma kwadratów wariancji. Służy do znajdowania wartości R Squire.
- SM. Oznacza to kwadrat wartości.
- I 5. F i istotność F. Jeśli istotność F (wartość p nachylenia) jest mniejsza niż test F, możesz odrzucić hipotezę zerową i udowodnić swoją hipotezę. Prostym językiem można wywnioskować, że zmiana x ma pewien wpływ na y.
W naszym przypadku F wynosi 48,96264, a Istotność F wynosi 0,000063. Oznacza to, że nasza regresja pasuje do danych.
Sekcja regresji:
W tej sekcji mamy dwie najważniejsze wartości naszego równania regresji.
- Przecięcie: Mamy tutaj punkt przecięcia, który mówi, gdzie przecina się z osią osi Y. Jest to ważna część równania regresji. W naszym przypadku jest to -1,11.
- X zmienna 1 (Nachylenie). Nazywany również współczynnikiem x. Określa tangens linii regresji.
WYKRES REGRESJI W EXCELU
W programie Excel łatwo jest wykreślić wykres regresji. Wystarczy wykonać następujące kroki. Aby dodać wykres regresji w programie Excel 2016, 2013 i 2010, wykonaj następujące proste czynności.
Krok 1. Miej swoje znane x w pierwszej kolumnie i wiedz, że y jest w drugiej.
W naszym przypadku wiemy, że znane_x to sprzedane napoje bezalkoholowe. A znane_y to Sprzedawcy.
Krok 2. Wybierz znany zakres x i y.
Krok 3: Przejdź do zakładki Wstaw i kliknij wykres punktowy.
Otrzymasz wykres, który wygląda tak.
Krok 4. Dodaj linię trendu: Przejdź do układu i znajdź opcję linii trendu w sekcji analizy.
W opcji Linia trendu kliknij Liniowa linia trendu.
Twój wykres będzie wyglądał tak.
To jest twój wykres regresji.
Teraz, jeśli dodasz dane poniżej i rozszerzysz wybrane dane. Zobaczysz zmianę na swoim wykresie.
W naszym przykładzie dodaliśmy 2000 do sprzedanego napoju bezalkoholowego, a pole Sprzedawcy pozostawiliśmy puste. A kiedy rozszerzymy zakres wykresu, to właśnie będziemy mieli.
Da to wymaganą liczbę sprzedawców do wykonania 2000 sprzedaży napojów bezalkoholowych w formie graficznej. Co jest na wykresie nieco poniżej 120. A z naszego równania regresji wiemy, że jest to 116.
W tym artykule starałem się omówić wszystko w ramach analizy regresji programu Excel. Wyjaśniłem regresję w Excelu 2016. Regresja w Excelu 2010 i Excelu 2013 jest taka sama jak w Excelu 2016.
W przypadku dalszych zapytań na ten temat skorzystaj z sekcji komentarzy. Zadaj pytanie, wyraź opinię lub po prostu wspomnij o moich błędach gramatycznych. Wszystko jest mile widziane. Po prostu nie wahaj się skorzystać z sekcji komentarzy.
Jak obliczyć funkcję TRYBU w programie Excel?
Jak obliczyć funkcję średnią w programie Excel?
Jak utworzyć wykres odchylenia standardowego
Statystyki opisowe w Microsoft Excel 2016
Jak korzystać z funkcji ROZKŁAD.NORMALNY programu Excel
Jak korzystać z wykresu i analizy Pareto
Popularne artykuły:
50 skrótów Excela, aby zwiększyć produktywność
Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel
Jak korzystać z funkcji LICZ.JEŻELI w programie Excel 2016
Jak korzystać z funkcji SUMA.JEŻELI w programie Excel