Tydzień wielkiej wyprzedażyClaude Skills — 20% RABATU
Tips

Jak wykonać regresję w Excelu: Pełny przewodnik

Powerdrill Bloom·
Jak wykonać regresję w Excelu: Pełny przewodnik

Aby wykonać regresję w Excel, włącz Analysis ToolPak, a następnie przejdź do Dane > Analiza danych i wybierz Regresja. Zaznacz zakres Y oraz zakres X, wybierz miejsce docelowe i kliknij OK. Excel zwróci współczynniki, R-kwadrat oraz inne statystyki w nowej tabeli.

Ten przewodnik opisuje trzy sposoby przeprowadzania regresji w Excel: Analysis ToolPak, funkcję REGLINP oraz linię trendu na wykresie. Zawiera również pełny przykład, wyjaśnia, jak interpretować wyniki, i pokazuje szybszy sposób na testowanie zależności w dużych plikach. Szczegóły pochodzą ze stron pomocy technicznej Microsoft, stan na 12 listopada 2026 r.

Co mówi regresja

Regresja mierzy, jak jedna wartość zmienia się pod wpływem zmiany innej wartości. Wartość, którą chcesz wyjaśnić, to zmienna zależna, czyli Y. Wartości, które Twoim zdaniem na nią wpływają, to zmienne niezależne, czyli X.

Microsoft opisuje narzędzie Regresja w następujący sposób. „Wykonuje ono analizę regresji liniowej przy użyciu metody „najmniejszych kwadratów” w celu dopasowania linii do zestawu obserwacji”.

Ta linia ma proste równanie. Microsoft zapisuje je jako y = mx + b, gdzie m to nachylenie, a b to punkt przecięcia. Przy więcej niż jednym X równanie przyjmuje postać y = m1x1 + m2x2 + ... + b.

W kategoriach biznesowych regresja odpowiada na pytania takie jak:

  • O ile wzrasta sprzedaż z każdym dodatkowym miesiącem wzrostu?
  • W jakim stopniu wydatki na reklamę wiążą się z liczbą rejestracji?
  • Jaka część zmiany kosztów jest wyjaśniona przez wolumen zamówień?

Przykład własny Microsoft dotyczy sportowców. Mówi on, że można analizować, „jak na wyniki sportowca wpływają takie czynniki jak wiek, wzrost i waga”.

Regresja pokazuje powiązanie, a nie dowód przyczyny. Silna zależność liniowa między dwiema wartościami może nadal wynikać z trzeciego czynnika, którego nie zmierzono.

Przygotowanie danych w Excel

Regresja wymaga czystych, dopasowanych par liczb. Każdy wiersz powinien zawierać jedną obserwację, z Y w jednej kolumnie i każdym X w osobnej kolumnie.

Oto dane z przykładu prostej regresji liniowej Microsoft. Przedstawiają one sześć miesięcy sprzedaży w dolarach.

Miesiąc Sprzedaż
1 3,100
2 4,500
3 4,400
4 5,400
5 7,500
6 8,100

Umieść Miesiąc w kolumnie A i Sprzedaż w kolumnie B, z nagłówkami w wierszu 1. Poniższe przykłady korzystają z tego układu. Dane znajdują się w wierszach od 2 do 7, dlatego poniższe formuły używają tych zakresów.

Kilka zasad pozwala zachować wiarygodność wyników:

  • Brak pustych komórek. Każdy wiersz musi mieć zarówno wartość Y, jak i X.
  • Tylko liczby. Wartości zapisane jako tekst spowodują błędy.
  • Wystarczająca liczba wierszy. Sześć wierszy wystarczy do demonstracji, ale prawdziwa analiza wymaga znacznie więcej.
  • Jeden wiersz na obserwację. Nie mieszaj wierszy miesięcznych i tygodniowych w jednym zakresie.

Włączanie Analysis ToolPak

Narzędzie Regresja znajduje się w Analysis ToolPak, dodatku dostarczanym wraz z Excel. Może być konieczne jego wcześniejsze włączenie.

Kroki Microsoft dla Excel w systemie Windows to:

  1. Wybierz Plik, następnie Opcje, a potem Dodatki.
  2. W polu Zarządzaj wybierz Dodatki programu Excel i kliknij Przejdź.
  3. W oknie Dodatki zaznacz pole wyboru Analysis ToolPak i kliknij OK.

Na komputerach Mac Microsoft zaleca przejście do Narzędzia > Dodatki programu Excel z menu pliku. Jeśli ToolPak nie ma na liście, wybierz Przeglądaj, aby go znaleźć.

Po włączeniu zobaczysz opcję Analiza danych na karcie Dane. Microsoft zauważa, że jeśli polecenie jest brakujące, „należy załadować i aktywować program dodatku Analysis ToolPak”.

Sposób 1: Uruchomienie regresji za pomocą Analysis ToolPak

Jest to najbardziej kompletna metoda. Daje pełną tabelę podsumowującą w jednym kroku.

  1. Przejdź do karty Dane i wybierz Analiza danych.
  2. Wybierz Regresja z listy i kliknij OK.
  3. W polu Zakres wejściowy Y zaznacz kolumnę Sprzedaż wraz z nagłówkiem.
  4. W polu Zakres wejściowy X zaznacz kolumnę Miesiąc wraz z nagłówkiem.
  5. Zaznacz Tytuły, ponieważ oba zakresy zawierają nagłówki.
  6. Wybierz Zakres wyjściowy lub Nowy arkusz.
  7. Kliknij OK.

Excel tworzy podsumowanie z trzema blokami: statystyki regresji, tabela ANOVA oraz tabela współczynników. Następna sekcja wyjaśnia, jak je czytać.

W przypadku regresji wielokrotnej w kroku 4 wybierz kilka sąsiadujących kolumn X. Microsoft zauważa, że narzędzie może analizować, „jak na pojedynczą zmienną zależną wpływają wartości jednej lub większej liczby zmiennych niezależnych”.

Wewnątrz narzędzie to korzysta z funkcji arkusza. Microsoft podaje: „Narzędzie Regresja używa funkcji arkusza REGLINP”.

Sposób 2: Użycie funkcji REGLINP

REGLINP dostarcza te same liczby w formie formuły. Jest odpowiednia dla osób, które chcą, aby wyniki aktualizowały się wraz ze zmianą danych.

Składnia Microsoft to REGLINP(znane_y; [znane_x]; [stała]; [statystyka]). Znane_y to zakres Sprzedaż, a znane_x to zakres Miesiąc.

Dla danych sprzedaży wpisz to w pustej komórce:

=REGLINP(B2:B7; A2:A7)

Funkcja zwraca dwie wartości: nachylenie, a następnie punkt przecięcia. Microsoft podaje, że tablica zwracana przez REGLINP to „{mn;mn-1;...;m1;b}”, więc punkt przecięcia zawsze pojawia się na końcu.

Aby uzyskać więcej statystyk, ustaw ostatni argument na PRAWDA:

=REGLINP(B2:B7; A2:A7; PRAWDA; PRAWDA)

Gdy statystyka jest ustawiona na PRAWDA, REGLINP zwraca błędy standardowe, R-kwadrat, statystykę F, stopnie swobody oraz sumy kwadratów. Microsoft zauważa, że funkcja „musi zostać wprowadzona jako formuła tablicowa”, ponieważ zwraca kilka wartości naraz.

Strona Microsoft pokazuje również prognozę w jednej komórce. Poniższa formuła szacuje sprzedaż w miesiącu 9 na podstawie miesięcy od 1 do 6:

=SUMA(REGLINP(B2:B7; A2:A7)*{9;1})

Wynik Microsoft dla tego przykładu to 11,000.

Sposób 3: Dodanie linii trendu do wykresu punktowego

Linia trendu pokazuje linię regresji na wykresie. To najszybszy sposób, aby sprawdzić, czy linia prosta pasuje do danych.

  1. Zaznacz obie kolumny danych.
  2. Wstaw wykres punktowy.
  3. Wybierz wykres, a następnie dodaj Linię trendu z menu elementów wykresu.
  4. Otwórz okno Formatowanie linii trendu i pozostaw opcję Liniowa.
  5. Zaznacz pola, aby wyświetlić równanie i wartość R-kwadrat na wykresie.

Wykres wyświetli wtedy równanie, np. y = 1000x + 2000, obok linii. Odpowiada to wynikowi REGLINP dla danych sprzedaży.

Linie trendu mogą również prognozować przyszłość. Microsoft podaje, że można „ustawić wartość w polach W przód i W tył, aby prognozować dane w przyszłość”.

Linia trendu jest dobra do szybkiego podglądu lub na slajd. W przypadku błędów standardowych, wartości p lub kilku zmiennych X, użyj Sposobu 1 lub Sposobu 2.

Której metody użyć

Każda metoda nadaje się do innego zadania. Wybierz na podstawie tego, co musisz pokazać i kto będzie to czytał.

Metoda Najlepsza dla Podaje wartości p Aktualizuje się z danymi
Analysis ToolPak Pełny raport o jednym modelu Tak Nie, uruchom narzędzie ponownie
REGLINP Modele wewnątrz działającego arkusza Nie, ale daje statystyki do ich obliczenia Tak
Linia trendu na wykresie Szybka kontrola wizualna lub slajd Nie Tak

Użyj ToolPak, gdy musisz obronić wynik na spotkaniu. Jego tabela podsumowująca zawiera każdą liczbę, o którą może zapytać recenzent, w tym wartości p dla każdego współczynnika.

Użyj REGLINP, gdy model zasila inne formuły, takie jak arkusz prognoz. Ponieważ jest to formuła, nachylenie i punkt przecięcia zmieniają się natychmiast po dodaniu nowych wierszy.

Użyj linii trendu, gdy odbiorcy muszą zobaczyć wzorzec. Wykres z linią i jej równaniem jest łatwiejszy do odczytania niż tabela. Wielu analityków używa wszystkich trzech: linii trendu do eksploracji, REGLINP do budowy i ToolPak do raportowania.

Przykład regresji

Oto co zwracają trzy metody dla sześciu miesięcy sprzedaży.

Statystyka Wartość Co to oznacza
Nachylenie (m) 1,000 Sprzedaż rośnie o około 1,000 miesięcznie
Punkt przecięcia (b) 2,000 Wartość linii w miesiącu 0
R-kwadrat 0.93 Około 93% zmienności sprzedaży podąża za trendem miesięcznym
Szacunek dla miesiąca 9 11,000 1,000 × 9 + 2,000

Nachylenie można sprawdzić ręcznie. Średni miesiąc to 3.5, a średnia wartość sprzedaży to 5,500. Nachylenie to suma iloczynów odchyleń podzielona przez sumę kwadratów odchyleń miesiąca. Daje to 17,500 podzielone przez 17.5, co wynosi 1,000.

Następnie wyliczany jest punkt przecięcia. Jest to średnia wartość sprzedaży minus nachylenie pomnożone przez średni miesiąc: 5,500 minus 3,500, co daje 2,000.

R-kwadrat mówi, jak dobrze linia pasuje do danych. Microsoft opisuje go jako wartość, która „mieści się w zakresie od 0 do 1”. Przy 1 linia pasuje do każdego punktu. Przy 0 Microsoft twierdzi, że „równanie regresji nie jest pomocne w przewidywaniu wartości y”.

Jak czytać wyniki regresji

Wyniki z Analysis ToolPak mogą wyglądać na skomplikowane. Większość decyzji opiera się na kilku liczbach.

  • Wielokrotne R. Korelacja między wartościami rzeczywistymi a przewidywanymi. Wartość bliższa 1 oznacza lepsze dopasowanie.
  • R-kwadrat. Udział zmienności Y wyjaśniony przez X. Tutaj wynosi około 0.93.
  • Skorygowane R-kwadrat. R-kwadrat skorygowane o liczbę zmiennych X. Używaj go przy porównywaniu modeli z różną liczbą danych wejściowych.
  • Błąd standardowy. Typowa odległość między rzeczywistymi punktami a linią.
  • Współczynniki. Punkt przecięcia i nachylenie dla każdego X. One budują Twoje równanie.
  • Wartość p. Szansa na zaobserwowanie tak dużego współczynnika, jeśli rzeczywisty efekt wynosiłby zero. Wielu analityków uznaje wartości poniżej 0.05 za istotne.
  • F i Istotność F. Test całego modelu. Mała wartość Istotność F sugeruje, że model wyjaśnia więcej niż przypadek.

Microsoft dodaje uwagę na temat dokładności. Mówi: „Im bardziej liniowe są dane, tym dokładniejszy jest model REGLINP”.

Czytaj współczynniki w prostych jednostkach. Nachylenie 1,000 oznacza, że każdy dodatkowy miesiąc dodaje średnio około 1,000 do sprzedaży w tym okresie.

Wykonaj regresję w Excel z AI

Gdy masz wiele kolumn i nie masz pewności, które z nich mają znaczenie, ręczne testowanie każdej pary zajmuje czas. Narzędzie AI może sprawdzić zależności w całym zestawie danych i je wyjaśnić.

Oto metoda w trzech krokach, przy użyciu Powerdrill Bloom.

Krok 1: Prześlij swój plik Excel

Prześlij skoroszyt z kolumną Y i potencjalnymi kolumnami X. Strona Excel Calculations prosi o najpierw przesłanie skoroszytu. Dodaje: „Upewnij się, że dane są poprawnie sformatowane, aby umożliwić sprawne obliczenia”.

Zachowaj jeden wiersz na obserwację i wyraźne nagłówki kolumn. AI używa nagłówków, aby zrozumieć, co oznacza każda kolumna.

Przesyłanie pliku Excel w celu uruchomienia regresji w Powerdrill Bloom

Krok 2: Poproś o analizę w języku naturalnym

Opisz, co chcesz przetestować. Na przykład: „Uruchom regresję liniową sprzedaży względem miesiąca i wydatków na reklamę i wyjaśnij, co ma większe znaczenie”.

Ta sama strona mówi, że możesz „wprowadzić żądanie obliczenia w formie tekstu”. Zadawaj pytania uzupełniające, na przykład czy zależność nadal się utrzymuje po usunięciu jednego nietypowego miesiąca.

Krok 3: Sprawdź wyniki i przedstaw je na wykresie

Sprawdź nachylenie względem REGLINP we własnym skoroszycie. Następnie przedstaw zależność wizualnie. Scatter Graph Creator został stworzony, aby „wizualizować zależności danych za pomocą interaktywnych i konfigurowalnych wykresów punktowych”.

W przypadku prognoz wykraczających poza linię prostą, narzędzie AI Forecasting buduje wykresy prognoz z przedziałami ufności. Aby dowiedzieć się więcej o samej metodzie, zobacz nasz artykuł o analizie regresji.

Sprawdzanie wyników regresji i tworzenie wykresu zależności

Typowe błędy, których należy unikać

Oto błędy, które pojawiają się najczęściej, gdy ludzie przeprowadzają regresję w Excel.

  • Pomylenie X i Y. Y to wartość, którą chcesz wyjaśnić. Ich zamiana zmienia nachylenie.
  • Zapomnienie o polu Tytuły. Jeśli Twoje zakresy zawierają nagłówki, zaznacz Tytuły, inaczej Excel odczyta nagłówek jako dane.
  • Uruchamianie regresji w Excel w wersji internetowej. Microsoft podaje, że „nie można tam utworzyć analizy regresji”, ponieważ narzędzie Regresja nie jest dostępne. Użyj aplikacji komputerowej.
  • Poleganie wyłącznie na R-kwadrat. Wysokie R-kwadrat nie dowodzi przyczyny i może ukrywać zakrzywiony wzorzec. Zawsze patrz na wykres punktowy.
  • Używanie zbyt małej liczby wierszy. Sześć punktów może przypadkowo utworzyć idealną linię. Więcej danych daje bardziej wiarygodny wynik.
  • Ekstrapolacja zbyt daleko w przyszłość. Linia dopasowana do miesięcy od 1 do 6 może nie sprawdzić się w miesiącu 24.
  • Ignorowanie wartości odstających. Jeden nietypowy miesiąc może przeciągnąć całą linię. Sprawdź wykres pod kątem punktów oddalonych od reszty.
  • Dodawanie wielu powiązanych kolumn X. Dane wejściowe, które poruszają się razem, sprawiają, że współczynniki są niestabilne. Microsoft zauważa, że REGLINP „sprawdza współliniowość” i usuwa nadmiarowe kolumny X.

Gdy będziesz gotowy do przetestowania zależności w całym zestawie danych, wypróbuj Powerdrill Bloom z miesięcznymi danymi z zeszłego roku.

Często zadawane pytania

Jak wykonać prostą regresję liniową w Excel?

Włącz Analysis ToolPak, przejdź do Dane > Analiza danych i wybierz Regresja. Zaznacz zakresy Y i X, zaznacz Tytuły, jeśli uwzględniłeś nagłówki, i kliknij OK. Możesz również użyć =REGLINP(zakres Y; zakres X).

Czy mogę wykonać regresję w Excel w wersji internetowej?

Nie w pełni. Microsoft podaje, że w Excel w wersji internetowej można przeglądać wyniki regresji, ale nie można tam utworzyć analizy regresji. Otwórz plik w aplikacji komputerowej, aby ją uruchomić.

Co oznacza R-kwadrat w regresji Excel?

R-kwadrat pokazuje, jaką część zmienności Y wyjaśnia linia. Mieści się w zakresie od 0 do 1. Wartość 0.93 oznacza, że około 93% zmienności podąża za dopasowaną linią.

Jak wykonać regresję wielokrotną w Excel?

Umieść każdą zmienną X w osobnej, sąsiadującej kolumnie. W narzędziu Regresja wybierz wszystkie kolumny X jako Zakres wejściowy X. REGLINP również akceptuje kilka kolumn X.

Jaka jest różnica między REGLINP a narzędziem Regresja?

Dają one te same współczynniki. Microsoft podaje, że narzędzie Regresja używa REGLINP. Narzędzie tworzy pełną tabelę podsumowującą, podczas gdy REGLINP zwraca liczby jako formułę, która aktualizuje się wraz z danymi.

Źródła: Microsoft Support, Use the Analysis ToolPak to perform complex data analysis · Microsoft Support, LINEST function · Microsoft Support, Perform a regression analysis · Microsoft Support, Add a trend or moving average line to a chart.