Jak przeprowadzić regresję logistyczną w programie excel

Regresja logistyczna to metoda, której używamy do dopasowania modelu regresji, gdy zmienna odpowiedzi jest binarna.

W tym samouczku wyjaśniono, jak przeprowadzić regresję logistyczną w programie Excel.

Przykład: regresja logistyczna w Excelu

Wykonaj poniższe kroki, aby przeprowadzić w programie Excel regresję logistyczną dla zbioru danych wskazujących, czy koszykarze z college’u zostali powołani do NBA (wersja robocza: 0 = nie, 1 = tak) na podstawie ich GPA. punktów, zbiórek i asyst w przeszłości. pora roku.

Krok 1: Wprowadź dane.

Najpierw wprowadź następujące dane:

Krok 2: Wprowadź komórki współczynników regresji.

Ponieważ w modelu mamy trzy zmienne objaśniające (pts, rebs, ast), utworzymy komórki dla trzech współczynników regresji plus jeden dla początku modelu. Dla każdego z nich ustawimy wartości na 0,001, ale zoptymalizujemy je później.

Następnie będziemy musieli utworzyć kilka nowych kolumn, których użyjemy do optymalizacji współczynników regresji, w tym logit, e ^logit , prawdopodobieństwo i log wiarygodności.

Krok 3: Utwórz wartości dla logitu.

Następnie utworzymy kolumnę logit, korzystając z następującej formuły:

Krok 4: Utwórz wartości dla e ^logit .

Następnie utworzymy wartości dla e ^logit korzystając z poniższego wzoru:

Krok 5: Utwórz wartości prawdopodobieństwa.

Następnie utworzymy wartości prawdopodobieństwa, korzystając z następującego wzoru:

Krok 6: Utwórz wartości prawdopodobieństwa dziennika.

Następnie utworzymy wartości prawdopodobieństwa logu, korzystając z następującego wzoru:

Prawdopodobieństwo logu = LN (Prawdopodobieństwo)

Krok 7: Znajdź sumę prawdopodobieństw logarytmicznych.

Na koniec znajdziemy sumę logarytmów wiarygodności, czyli liczbę, którą będziemy próbować maksymalizować, aby rozwiązać współczynniki regresji.

Krok 8: Użyj solwera do rozwiązania współczynników regresji.

Jeśli nie zainstalowałeś jeszcze solwera w programie Excel, wykonaj w tym celu następujące kroki:

• Kliknij opcję Plik .
• Kliknij Opcje .
• Kliknij Dodatki .
• Kliknij opcję Dodatek Solver , a następnie kliknij przycisk Przejdź .
• W nowym oknie, które się pojawi, zaznacz pole obok dodatku Solver , a następnie kliknij przycisk Przejdź .

Po zainstalowaniu solwera przejdź do grupy Analiza na zakładce Dane i kliknij Solver . Wprowadź następujące informacje:

• Wyznacz cel: Wybierz komórkę H14, która zawiera sumę logarytmów prawdopodobieństw.
• Modyfikując komórki zmiennych: wybierz zakres komórek B15:B18, który zawiera współczynniki regresji.
• Ustaw zmienne nieograniczone jako nieujemne: usuń zaznaczenie tego pola.
• Wybierz metodę rozwiązania: wybierz nieliniowy GRG.

Następnie kliknij Rozwiąż .

Solver automatycznie oblicza szacunki współczynników regresji:

Domyślnie do znalezienia prawdopodobieństwa, że projekt = 0, można zastosować współczynniki regresji.

Jednak ogólnie w regresji logistycznej interesuje nas prawdopodobieństwo, że zmienna odpowiedzi = 1.

W ten sposób możemy po prostu odwrócić znaki na każdym ze współczynników regresji:

Te współczynniki regresji można teraz wykorzystać do znalezienia prawdopodobieństwa, że projekt = 1.

Załóżmy na przykład, że zawodnik zdobywa średnio 14 punktów na mecz, 4 zbiórki na mecz i 5 asyst na mecz. Prawdopodobieństwo powołania tego zawodnika do NBA można obliczyć w następujący sposób:

P(projekt = 1) = e ^{3,681193 + 0,112827*(14) -0,39568*(4) – 0,67954*(5)} / (1+e ^{3,681193 + 0,112827*(14 ) -0,39568*(4) – 0,67954*(5 )} ) = 0,57 .

Ponieważ prawdopodobieństwo to jest większe niż 0,5, przewidujemy, że ten gracz   zostaje powołany do NBA.

Powiązane: Jak utworzyć krzywą ROC w programie Excel (krok po kroku)