Regresja liniowa to jedno z najczęściej wykorzystywanych narzędzi analitycznych w pracy z danymi, a Excel oferuje do niej znakomitą funkcję – REGLINP. W odróżnieniu od prostego dodania linii trendu na wykresie, funkcja ta dostarcza pełen zestaw statystyk diagnostycznych, dzięki którym można ocenić jakość dopasowania modelu, istotność współczynników oraz wykorzystać wyniki do prognozowania. W tym artykule znajduje się kompletne omówienie składni, praktyczne przykłady krok po kroku oraz interpretacja wyników.

Czym jest funkcja REGLINP w Excelu?

REGLINP to polski odpowiednik angielskiej funkcji LINEST. Służy do wyznaczania parametrów prostej regresji liniowej metodą najmniejszych kwadratów według równania y = mx + b, gdzie:

• m – współczynnik nachylenia prostej (slope),
• b – wyraz wolny, czyli punkt przecięcia z osią Y (intercept).

Funkcja działa zarówno dla regresji prostej (jedna zmienna niezależna), jak i regresji wielorakiej (wiele zmiennych niezależnych). Zwraca wyniki w formie tablicy, dlatego wymaga zaznaczenia odpowiedniego zakresu komórek przed wpisaniem formuły.

Składnia funkcji REGLINP

Pełna składnia wygląda następująco:

=REGLINP(znane_y; [znane_x]; [stała]; [statystyka])

Opis argumentów

• znane_y – zakres wartości zmiennej zależnej (wynikowej),
• znane_x – zakres wartości zmiennej niezależnej (objaśniającej); jeśli pominięty, Excel przyjmuje sekwencję {1, 2, 3…},
• stała – wartość logiczna: PRAWDA (lub pominięta) oznacza standardowe obliczenie wyrazu wolnego b; FAŁSZ wymusza b = 0,
• statystyka – PRAWDA zwraca pełen zestaw statystyk diagnostycznych; FAŁSZ (domyślnie) zwraca tylko współczynniki m i b.

Przygotowanie danych – krok 1

Załóżmy, że analizowana jest sprzedaż w sklepie internetowym w zależności od numeru miesiąca. Dane warto uporządkować w czytelnej tabeli:

Wartości X znajdują się w zakresie A2:A8, wartości Y w B2:B8. Dane powinny mieć identyczne wymiary – to warunek konieczny prawidłowego działania funkcji.

Podstawowe zastosowanie REGLINP – krok 2

Aby uzyskać dwa współczynniki (nachylenie i wyraz wolny), należy:

1. Zaznaczyć dwie sąsiadujące komórki w jednym wierszu, np. D2:E2.
2. Wpisać formułę: =REGLINP(B2:B8; A2:A8)
3. W Excel 365 lub 2021 wystarczy zwykły Enter (dzięki tablicom dynamicznym). W starszych wersjach należy zatwierdzić kombinacją Ctrl + Shift + Enter.

Wynik: w komórce D2 pojawi się współczynnik nachylenia m, a w E2 wyraz wolny b. Dla powyższych danych otrzymamy m ≈ 980 oraz b ≈ 2 150, co przekłada się na równanie:

Sprzedaż = 980 × miesiąc + 2 150

Pełne statystyki diagnostyczne – krok 3

Aby uzyskać kompletny zestaw informacji o jakości modelu, należy ustawić ostatni argument na PRAWDA i zaznaczyć większy zakres – 5 wierszy na 2 kolumny (przy jednej zmiennej X):

1. Zaznaczyć zakres 5×2 komórek, np. D2:E6.
2. Wpisać: =REGLINP(B2:B8; A2:A8; PRAWDA; PRAWDA)
3. Zatwierdzić Ctrl + Shift + Enter (w starszych wersjach).

Układ zwracanych wartości

Interpretacja wyników

Współczynnik R²

Wartość R² mieści się w przedziale od 0 do 1 i informuje, jaki procent zmienności Y jest wyjaśniany przez model. R² na poziomie 0,99675 oznacza znakomite dopasowanie – aż 99,68% zmienności sprzedaży jest tłumaczone przez upływ czasu.

Statystyka F i istotność modelu

Wysoka wartość F (np. 459,75 przy df = 6) wskazuje, że model jako całość jest statystycznie istotny. Im wyższe F i większa liczba stopni swobody, tym wyższa pewność, że zależność nie jest dziełem przypadku – w tym przypadku p-value spada poniżej 0,0000001.

Błędy standardowe i test istotności

Dzieląc współczynnik przez jego błąd standardowy, otrzymujemy statystykę t: t = m / se(m). Jeśli wartość bezwzględna t przekracza około 2 (dla typowych poziomów istotności), współczynnik można uznać za statystycznie istotny.

Wyodrębnianie pojedynczych parametrów funkcją INDEKS

Jeśli potrzebny jest tylko jeden wynik, warto połączyć REGLINP z funkcją INDEKS:

• Nachylenie: =INDEKS(REGLINP(B2:B8; A2:A8); 1; 1)
• Wyraz wolny: =INDEKS(REGLINP(B2:B8; A2:A8); 1; 2)
• R² (z pełnymi statystykami): =INDEKS(REGLINP(B2:B8; A2:A8; PRAWDA; PRAWDA); 3; 1)

Prognozowanie z użyciem REGLINP

Mając obliczone parametry, łatwo przewidzieć wartość dla nowego punktu. Dla miesiąca 9 prognoza wynosi:

=SUMA(REGLINP(B2:B8; A2:A8) * {9;1})

Formuła mnoży nachylenie przez 9 oraz wyraz wolny przez 1, a następnie sumuje wynik. Dla analizowanych danych otrzymujemy prognozę około 11 000 zł.

Alternatywnie można skorzystać z dedykowanej funkcji =REGLINW lub =PROGNOZA.LINIOWA, jednak REGLINP daje większą kontrolę nad procesem.

REGLINP a regresja wieloraka

Funkcja świetnie radzi sobie z modelami wielu zmiennych. Wystarczy w argumencie znane_x podać zakres obejmujący kilka kolumn, np. =REGLINP(C2:C20; A2:B20; PRAWDA; PRAWDA). Wyniki zwracane są w odwrotnej kolejności – współczynnik dla ostatniej zmiennej X pojawia się jako pierwszy z lewej strony.

Co istotne, REGLINP automatycznie wykrywa zmienne współliniowe i ustawia ich współczynniki na 0, co odróżnia ją od prostszych funkcji takich jak NACHYLENIE czy ODCIĘTA, które w takiej sytuacji zwracają błąd #DZIEL/0!.

REGLINP a linia trendu na wykresie

Dodanie linii trendu w wykresie punktowym to szybki sposób na wizualizację zależności i odczytanie równania y = mx + b. Jednak ta metoda ma ograniczenia:

• nie pokazuje błędów standardowych poszczególnych współczynników,
• nie podaje statystyki F ani stopni swobody,
• utrudnia automatyczne wykorzystanie parametrów w dalszych obliczeniach.

REGLINP eliminuje te ograniczenia, dostarczając pełen pakiet danych diagnostycznych gotowych do dalszej analizy.

Najczęstsze pułapki i dobre praktyki

• Niezgodne wymiary zakresów – znane_y i znane_x muszą mieć identyczną liczbę punktów, inaczej funkcja zwróci błąd #ARG!.
• Brak zaznaczenia odpowiedniego zakresu – w starszych wersjach Excela bez Ctrl + Shift + Enter pojawi się tylko pierwszy wynik (nachylenie).
• Ryzyko overfittingu – dodawanie zbyt wielu zmiennych w regresji wielorakiej może sztucznie zawyżać R², jednocześnie pogarszając jakość prognoz poza zakresem danych.
• Ekstrapolacja – prognozowanie daleko poza obserwowany zakres X bywa zawodne; warto wspierać się dodatkowymi metodami walidacji.
• Sprawdzenie założeń – regresja liniowa zakłada liniowość zależności i normalność reszt; przy nieliniowych danych lepiej rozważyć transformację zmiennych.

Wnioski

REGLINP to potężne narzędzie analityczne, które wykracza daleko poza możliwości prostej linii trendu na wykresie. Pozwala nie tylko wyznaczyć równanie prostej regresji, ale przede wszystkim ocenić jego jakość, istotność statystyczną oraz wykorzystać do prognozowania – zarówno w przypadku regresji prostej, jak i wielorakiej. Kluczem do efektywnej pracy z funkcją jest poprawne przygotowanie danych, zaznaczenie właściwego zakresu komórek przed wprowadzeniem formuły oraz świadoma interpretacja wyników, zwłaszcza R², statystyki F i błędów standardowych. Opanowanie REGLINP otwiera drogę do profesjonalnej analizy zależności między zmiennymi bezpośrednio w arkuszu Excela, bez konieczności sięgania po wyspecjalizowane oprogramowanie statystyczne.

Paweł Klimala

Jestem doświadczonym redaktorem specjalizującym się w tematach związanych z nowinkami technologicznymi. Moja pasja do pisania artykułów o innowacjach w technologii przekłada się na bogate doświadczenie w kreowaniu treści zrozumiałych i przystępnych dla czytelników. Posiadam szeroką wiedzę na temat najnowszych trendów w branży IT , które angażują i edukują naszą społeczność.