REGLINP

Ten artykuł zawiera opis składni formuły i zastosowania funkcji REGLINP w programie Microsoft Excel.

Opis

Funkcja REGLINP oblicza statystykę dla linii, korzystając z metody najmniejszych kwadratów, aby obliczyć linię prostą, która najlepiej pasuje do danych, a następnie zwraca tablicę opisującą tę linię. Funkcję REGLINP można również połączyć z innymi funkcjami, aby obliczyć statystykę dla innych typów modeli, które są liniowe w nieznanych parametrach, w tym serii wielomianowych, logarytmicznych, wykładniczych i potęgowych. Funkcja zwraca tablicę wartości, musi więc być wprowadzana w postaci formuły tablicowej. Instrukcje są zgodne z przykładami przedstawionymi w tym artykule.

Równanie dla linii jest następujące:

y = mx + b

— lub —

y = m1x1 + m2x2 + ... + b

jeśli istnieje wiele zakresów wartości x, gdzie zależne wartości y są funkcją niezależnych wartości x. Wartości m to współczynniki odpowiadające każdej wartości zmiennej x, a wartość b jest wartością stałą. Należy zauważyć, że wartości y, x i m mogą być wektorami. Funkcja REGLINP zwraca tablicę {mn;mn-1;...;m1;b}. Funkcja REGLINP może również zwracać dodatkową statystykę regresji.

Składnia

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

W składni funkcji REGLINP występują następujące argumenty:

• known_y Argument wymagany. Jest to zestaw znanych wartości y spełniających zależność y = mx + b.

  • Jeśli zakres known_y znajduje się w jednej kolumnie, to każda z known_x jest interpretowana jako oddzielna zmienna.
  • Jeśli zakres known_y znajduje się w jednym wierszu, to każdy wiersz known_x jest interpretowany jako oddzielna zmienna.

• known_x Opcjonalnie. Jest to zestaw znanych wartości x spełniających zależność y = mx + b.

  • Zakres known_x może zawierać jeden lub więcej zestawów zmiennych. Jeśli użyto tylko jednej zmiennej, known_y i known_x mogą być zakresami o dowolnym kształcie, o ile mają jednakowe wymiary. Jeśli użyto więcej niż jednej zmiennej, known_y musi być wektorem (czyli zakresem o wysokości jednego wiersza lub szerokości jednej kolumny).
  • Jeśli argument known_x zostanie pominięty, przyjmuje się, że jest on tablicą {1;2;3,...} o takim samym rozmiarze jak known_y.

• const Opcjonalnie. Wartość logiczna określająca, czy stała b ma mieć narzuconą wartość 0.

  • Jeżeli stała ma wartość PRAWDA lub jest pominięta, to stała b jest obliczana normalnie.
  • Jeśli stała ma wartość FAŁSZ, to stała b jest ustawiana jako równa 0, a wartości m są dostosowywane tak, aby wypełnić równanie y = mx.

• statystyki Opcjonalnie. Wartość logiczna określająca, czy mają być zwracane dodatkowe statystyki regresji.

  • Jeśli argument statystyka ma wartość PRAWDA, funkcja REGLINP zwraca dodatkowe statystyki regresji; W rezultacie zwrócona tablica to {mn;mn-1,...,m1;b; sen,sen-1,...,se1,seb; r^2,sey; F,df; ssreg,ssresid}.
  • Jeśli argument statystyka ma wartość FAŁSZ lub jest pominięty, funkcja REGLINP zwraca tylko współczynniki m i stałą b.
    Poniżej przedstawiono dodatkowe statystyki regresji:

Statystyka	Opis
se1;se2;...;sen	Standardowe wartości błędu dla współczynników m1;m2;...;mn.
seb	Standardowe wartości błędu dla stałej b (seb = #N/D!, gdy stała ma wartość FAŁSZ).
R2	Współczynnik wyznaczania. Porównuje szacunkowe i rzeczywiste wartości y, a jego wartość jest w zakresie od 0 do 1. Jeśli współczynnik jest równy 1, istnieje doskonała korelacja w próbce — nie ma różnicy między szacowaną wartością y a rzeczywistą wartością y. Przy drugiej wartości skrajnej, jeśli współczynnik wyznaczania jest równy 0, równanie regresji nie jest pomocne w obliczaniu prognozowanej wartości y. Aby uzyskać informacje o sposobie obliczania liczby2 , zobacz sekcję "Spostrzeżenia" w dalszej części tego tematu.
sey	Standardowy błąd oceny y.
F	Statystyka F lub wartość obserwowana F. Statystykę F należy stosować do określenia, czy obserwowana zależność między zmienną zależną a zmienną niezależną występuje przypadkowo.
df	Stopnie swobody. Można użyć stopni swobody, aby łatwiej znaleźć wartości krytyczne F w tabeli statystycznej. Należy porównać wartości znalezione w tabeli ze statystyką F zwróconą przez funkcję REGLINP w celu określenia poziomu ufności modelu. Aby uzyskać informacje o sposobie obliczania wartości df, zobacz sekcję „Spostrzeżenia" w dalszej części tego tematu. W przykładzie 4 opisano sposób korzystania ze statystyki F i wartości df.
ssreg	Regresyjna suma kwadratów.
ssresid	Resztkowa suma kwadratów. Aby uzyskać informacje o sposobie obliczania wartości ssreg i ssresid, zobacz sekcję „Spostrzeżenia” w dalszej części tego tematu.

Na poniższej ilustracji pokazano kolejność zwracania dodatkowych statystyk regresji.

Spostrzeżenia

• Można opisać dowolną nachyloną linię prostą przecinającą oś y:
  Nachylenie (m):
  Aby znaleźć nachylenie linii, często zapisywane jako m, weź dwa punkty na prostej (x1,y1) i (x2,y2); Nachylenie jest równe (y2 - y1)/(x2 - x1).
  Punkt przecięcia z osią y (b):
  Punkt przecięcia linii z osią y, często zapisywany jako b, jest wartością y w punkcie, w którym prosta przecina oś y.
  Równanie linii prostej to y = mx + b. Jeśli znane są wartości m i b, można obliczyć każdy punkt na linii, wstawiając wartość x lub y do tego równania. Można również użyć funkcji REGLINW.

• Jeżeli istnieje tylko jedna zmienna niezależna x, to można otrzymać nachylenie i punkt przecięcia z osią y bezpośrednio, stosując następujące formuły:
  Nachylenie:
  =INDEKS(REGLINP(known_y's;known_x's);1)
  Punkt przecięcia z osią y:
  =INDEKS(REGLINP(known_y's;known_x's);2)

• Dokładność linii obliczonej za pomocą funkcji REGLINP zależy od stopnia rozproszenia danych. Im bardziej liniowe są dane, tym dokładniejszy jest model tworzony przez funkcję REGLINP. Funkcja REGLINP korzysta z metody najmniejszych kwadratów, aby określić optymalne dopasowanie danych. Jeśli istnieje tylko jedna zmienna niezależna x, to obliczenia m i b są oparte na następujących formułach:
  
  
  gdzie x i y są średnimi z próby; to znaczy, że x = ŚREDNIA(znane, x) i y = ŚREDNIA(known_y).

• Funkcje dopasowania linii i krzywej REGLINP i REGEXPP umożliwiają obliczanie najlepszej linii prostej lub krzywej wykładniczej najlepiej pasującej do danych. Należy jednak zdecydować, który z wyników najlepiej pasuje do danych. Można obliczyć funkcję REGLINW(known_y, known_x) dla linii prostej lub REGEXPW(known_y, known_x) dla krzywej wykładniczej. Te funkcje, bez argumentu new_x , zwracają tablicę wartości y przewidywanych wzdłuż tej linii lub krzywej w rzeczywistych punktach danych. Następnie można porównać przewidywane wartości z wartościami rzeczywistymi. W celu wizualnego porównania możesz utworzyć oba wykresy.

• W przypadku analizy metodą regresji program Excel oblicza dla każdego punktu kwadrat różnicy pomiędzy wartością y szacowaną dla tego punktu a jego rzeczywistą wartością y. Suma kwadratów różnic nazywana jest resztkową sumą kwadratów (ssresid). Następnie program Excel oblicza całkowitą sumę kwadratów (sstotal). Gdy stała = PRAWDA lub jest pominięta, to całkowita suma kwadratów jest sumą kwadratów różnic między rzeczywistymi wartościami y i średnią tych wartości. Gdy stała = FAŁSZ, to całkowita suma kwadratów rzeczywistych wartości y jest sumą kwadratów rzeczywistych wartości y (bez odejmowania średniej wartości y od poszczególnych wartości y). Regresyjna suma kwadratów (ssreg) jest obliczana jako różnica łącznej sumy kwadratów i resztkowej sumy kwadratów. Im mniejsza jest resztkowa suma kwadratów w porównaniu z całkowitą sumą kwadratów, tym większa jest wartość współczynnika^r2, który jest wskaźnikiem tego, w jakim stopniu równanie wynikające z analizy metodą regresji wyjaśnia zależność między zmiennymi. Wartość r² jest równa wartości ssreg/sstotal.

• W niektórych przypadkach jedna lub więcej kolumn X (załóżmy, że Y i X znajdują się w kolumnach) może nie mieć dodatkowej wartości predykcyjnej w obecności pozostałych kolumn X. Innymi słowy, wyeliminowanie jednej lub więcej kolumn X może spowodować, że prognozowane wartości Y będą równie dokładne. W takim przypadku te nadmiarowe kolumny X powinny zostać pominięte w modelu regresji. To zjawisko nazywa się "współliniowością", ponieważ każdą nadmiarową kolumnę X można wyrazić jako sumę wielokrotności nienadmiarowych kolumn X. Funkcja REGLINP sprawdza współliniowość i usuwa nadmiarowe kolumny X z modelu regresji po ich zidentyfikowaniu. Usunięte kolumny X można rozpoznać w wynikach funkcji REGLINP jako zawierające współczynniki 0 oprócz wartości 0 se. Jeśli jedna lub więcej kolumn zostanie usuniętych jako nadmiarowe, wpływa to na df, ponieważ zależy to od liczby X kolumn faktycznie użytych do celów predykcyjnych. Aby uzyskać szczegółowe informacje na temat obliczania wartości df, zobacz przykład 4. Jeśli df zostanie zmienione z powodu usunięcia nadmiarowych kolumn X, wpłynie to również na wartości sey i F. Współliniowość powinna być w praktyce stosunkowo rzadka. Jednak jednym z przypadków, w którym jest to bardziej prawdopodobne, jest sytuacja, gdy niektóre kolumny X zawierają tylko wartości 0 i 1 jako wskaźniki tego, czy osoba badana w eksperymencie jest, czy nie jest członkiem określonej grupy. Jeśli stała = PRAWDA lub zostanie pominięta, funkcja REGLINP wstawi dodatkową kolumnę X zawierającą wszystkie wartości 1 w celu modelowania przecięcia. Jeśli masz kolumnę z wartością 1 dla każdego tematu, jeśli jesteś mężczyzną, lub 0, jeśli nie, a także kolumną z wartością 1 dla każdego tematu, jeśli jesteś kobietą, lub 0, jeśli nie, ta ostatnia kolumna jest zbędna, ponieważ wpisy w niej można uzyskać, odejmując wpis w kolumnie "wskaźnik męski" od wpisu w dodatkowej kolumnie wszystkich wartości 1 dodanych przez funkcję REGLINP .

• Wartość df jest obliczana w następujący sposób, gdy żadne kolumny X nie są usuwane z modelu ze względu na współliniowość: jeśli istnieje k kolumn known_x i stała = PRAWDA lub jest pominięta, df = n – k – 1. Jeśli stała = FAŁSZ, df = n - k. W obydwu wypadkach każde usunięcie liniowo zależnej kolumny X powoduje zwiększenie wartości df o 1.

• Gdy jako argument jest wprowadzana stała tablicowa (na przykład known_x's), wartości w tym samym wierszu należy rozdzielać średnikami, a wartości w tej samej kolumnie ukośnikami odwrotnymi (\). Znaki separatorów mogą być inne, w zależności od ustawień regionalnych.

• Należy zauważyć, że wartości y przewidziane przez równanie regresyjne mogą być nieprawidłowe, jeśli znajdują się poza zakresem wartości y użytym dla określenia równania.

• Algorytm używany w funkcji REGLINP jest inny niż algorytm używany w funkcjach NACHYLENIE i ODCIĘTA. Różnica między tymi algorytmami może prowadzić do innych wyników, gdy dane są nieokreślone i współliniowe. Jeśli na przykład punkty danych argumentu known_y mają wartość 0, a punkty danych argumentu known_x mają wartość 1:

  • Funkcja REGLINP zwraca wartość 0. Algorytm funkcji REGLINP ma zwracać rozsądne wyniki dla danych współliniowych, a w tym przypadku można znaleźć co najmniej jedną odpowiedź.
  • Funkcje NACHYLENIE i ODCIĘTA zwracają wartość #DIV/0! #POŁĄCZ!. Algorytm funkcji NACHYLENIE i ODCIĘTA umożliwia wyszukiwanie tylko jednej odpowiedzi, a w tym przypadku może być więcej niż jedna odpowiedź.

• Oprócz obliczania statystki dla innych typów regresji za pomocą funkcji REGEXPP, przy użyciu funkcji REGLINP można obliczać zakres innych typów regresji, wprowadzając funkcje zmiennych x i y jako serie x i y dla funkcji REGLINP. Na przykład następująca formuła:
  =REGLINP(wartości_y; wartości_x^NR.KOLUMNY($A:$C))
  działa, gdy pojedyncza kolumna wartości y i pojedyncza kolumna wartości x zostaną użyte do obliczenia sześciennego przybliżenia formuły (wielomianu rzędu 3):
  y = m1*x + m2*x^2 + m3*x^3 + b
  Można dostosować tę formułę do obliczania innych typów regresji, ale w niektórych przypadkach wymaga to dopasowania wartości wyjściowych i innych statystyk.

• Wartość testu F zwracana przez funkcję REGLINP różni się od wartości testu F zwracanej przez funkcję TEST.F. Funkcja REGLINP zwraca statystykę F, natomiast funkcja TEST.F zwraca prawdopodobieństwo.

Przykłady

Przykład 1. Nachylenie i punkt przecięcia osi y

Skopiuj przykładowe dane z poniższej tabeli i wklej je w komórce A1 nowego arkusza programu Excel. Aby formuły wyświetlały wyniki, zaznacz je, naciśnij klawisz F2, a następnie naciśnij klawisz Enter. Jeśli to konieczne, możesz dostosować szerokości kolumn, aby wyświetlić pełne dane.

Znane y	Znane x
1	0
9	4
5	2
7	3
Wynik (nachylenie)	Wynik (przecięcie z osią y)
2	1
Formuła (formuła tablicowa w komórkach A7:B7)
=REGLINP(A2:A5;B2:B5;;FAŁSZ)

Przykład 2. Prosta regresja liniowa

Skopiuj przykładowe dane z poniższej tabeli i wklej je w komórce A1 nowego arkusza programu Excel. Aby formuły wyświetlały wyniki, zaznacz je, naciśnij klawisz F2, a następnie naciśnij klawisz Enter. Jeśli to konieczne, możesz dostosować szerokości kolumn, aby wyświetlić pełne dane.

Miesiąc	Sprzedaż
1	3100 zł
2	4500 zł
3	4400 zł
4	5400 zł
5	7500 zł
6	8100 zł
Formuła	Wynik
=SUMA(REGLINP(B1:B6;A1:A6)*{9,1})	11 000 zł
	Oblicza szacowaną sprzedaż w dziewiątym miesiącu na podstawie sprzedaży od pierwszego do szóstego miesiąca.

Przykład 3. Wielokrotna regresja liniowa

Skopiuj przykładowe dane z poniższej tabeli i wklej je w komórce A1 nowego arkusza programu Excel. Aby formuły wyświetlały wyniki, zaznacz je, naciśnij klawisz F2, a następnie naciśnij klawisz Enter. Jeśli to konieczne, możesz dostosować szerokości kolumn, aby wyświetlić pełne dane.

Powierzchnia budynku (x1)	Liczba lokali biurowych (x2)	Liczba wejść (x3)	Wiek budynku (x4)	Szacowana wartość budynku (y)
2310	2	2	20	142 000 zł
2333	2	2	12	144 000 zł
2356	3	1,5	33	151 000 zł
2379	3	2	43	150 000 zł
2402	2	3	53	139 000 zł
2425	4	2	23	169 000 zł
2448	2	1,5	99	126 000 zł
2471	2	2	34	142 900 zł
2494	3	3	23	163 000 zł
2517	4	4	55	169 000 zł
2540	2	3	22	149 000 zł
-234,2371645
13,26801148
0,996747993
459,7536742
1732393319
Formuła (dynamiczna formuła tablicowa wprowadzona w komórce A19)
=REGLINP(E2:E12;A2:D12;PRAWDA;PRAWDA)

Przykład 4. Użycie statystyk F i r²

W poprzednim przykładzie użyto współczynnika wyznaczania r², który był równy 0,99675 (zobacz komórkę A17 w wyniku funkcji REGLINP), co oznaczałoby ścisłą relację między zmiennymi niezależnymi a ceną sprzedaży. Można zastosować statystykę F do określenia, czy wyniki te, przy tak wysokiej wartości r2, wystąpiły przypadkowo.

Przyjmijmy na chwilę, że w rzeczywistości nie ma żadnej relacji pomiędzy zmiennymi, ale została przyjęta rzadka próbka 11 biurowców, która powoduje, że analiza statystyczna wykazuje ścisłą relację. Dla prawdopodobieństwa błędnego wnioskowania o istnieniu zależności stosowane jest określenie „Alfa”.

Wartości F i df w danych wyjściowych funkcji REGLINP umożliwiają ocenę prawdopodobieństwa przypadkowego wystąpienia wyższej wartości F. Wartość F można porównać z wartościami krytycznymi w opublikowanych tabelach rozkładu F, a funkcję ROZKŁAD.F w programie Excel można zastosować do obliczenia prawdopodobieństwa przypadkowego wystąpienia większej wartości F. Odpowiedni rozkład F-Snestora ma stopnie swobody v1 i v2. Jeśli n jest liczbą punktów danych, a stała = PRAWDA lub jest pominięta, to v1 = n – df – 1 i v2 = df. (Jeśli stała = FAŁSZ, to v1 = n – df i v2 = df). Funkcja ROZKŁAD.F — ze składnią ROZKŁAD.F(F;v1;v2) — zwraca prawdopodobieństwo przypadkowego wystąpienia wyższej wartości F. W tym przykładzie df = 6 (komórka B18) i F = 459,753674 (komórka A18).

Zakładając, że Alfa = 0,05, v1 = 11 – 6 – 1 = 4 i v2 = 6, wartość krytyczna F wynosi 4,53. Ponieważ F = 459,753674 jest dużo większe od 4,53, prawdopodobieństwo przyjęcia przez F tak dużej wartości jest bardzo małe. (Przy alfa = 0,05 hipoteza, że nie ma związku między known_y a known_x, należy odrzucić, gdy F przekracza poziom krytyczny 4,53). Za pomocą funkcji ROZKŁAD.F w programie Excel można obliczyć prawdopodobieństwo, że wartość F przyjmie większą wartość. Na przykład ROZKŁAD.F(459,753674; 4; 6) = 1,37E-7, czyli jest to prawdopodobieństwo bardzo małe. Odszukując krytyczny poziom wartości F w tablicach lub używając funkcji ROZKŁAD.F można wysnuć wniosek, że równanie regresji jest przydatne do oszacowania przewidywanej wartości badanych biurowców w tym obszarze. Należy jednak pamiętać o przyjęciu prawidłowych wartości v1 i v2 (obliczanych metodą podaną w poprzednim akapicie).

Przykład 5. Obliczanie statystyki t

Inny hipotetyczny test będzie określać, czy można korzystać z każdego współczynnika nachylenia podczas oceny szacunkowej wartości biurowca w przykładzie 3. Na przykład, aby sprawdzić znaczenie statystyczne współczynnika wieku budynku, należy podzielić -234,24 (współczynnik nachylenia odpowiadający wiekowi) przez 13,268 (szacunkowy standardowy błąd współczynników wieku w komórce A15). Obserwowaną wartość t podano poniżej:

t = m4 ÷ se4 = -234,24 ÷ 13,268 = -17,7

Jeśli wartość bezwzględna jest wystarczająco duża, można przyjąć, że obliczony współczynnik nachylenia jest użyteczny w oszacowaniu przewidywanej wartości biurowca omawianego w przykładzie 3. W poniższej tabeli pokazano wartości bezwzględne dla 4 obserwacji.

Przeglądając tabelę w podręczniku statystyki, przekonasz się, że dwustronny test krytyczny t z 6 stopniami swobody i alfa = 0,05 wynosi 2,447. Tę wartość krytyczną można również obliczyć za pomocą funkcji ROZKŁAD.T.ODW w programie Excel. ROZKŁAD.T.ODW(0,05;6) = 2,447. Ponieważ wartość bezwzględna t (17,7) jest większa niż 2,447, wiek budynku jest ważną zmienną przy szacowaniu szacunkowej wartości biurowca. W podobny sposób można sprawdzić każdą z pozostałych zmiennych niezależnych pod względem istotności statystycznej. Poniżej przedstawiono wartości obserwowane t dla każdej zmiennej niezależnej.

Zmienna	Wartość obserwowana t
Powierzchnia budynku	5,1
Liczba lokali biurowych	31,3
Liczba wejść	4,8
Wiek budynku	17,7

Wartość bezwzględna wszystkich wartości jest większa niż 2,447, a zatem wszystkie zmienne użyte w równaniu regresji są użyteczne w prognozowaniu szacunkowej wartości biurowców na tym terenie.