REGLINP, funkcja

Opis

Funkcja REGLINP oblicza statystyki dla linii przy użyciu metody "najmniejszych kwadratów", aby obliczyć linię prostą najlepiej pasjącą do danych, a następnie zwraca tablicę opisującą linię. Funkcję REGLINP można również połączyć z innymi funkcjami, aby obliczyć statystyki dla innych typów modeli liniowych w nieznanych parametrach, takich jak wielomianowe, logarytmiczne, wykładnicze i szeregi potęgowe. Funkcja zwraca tablicę wartości, musi więc być wprowadzana w postaci formuły tablicowej. Instrukcje są zgodne z przykładami 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:

Składnia

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

  • Jeśli zakres znane_y znajduje się w jednej kolumnie, to każda kolumna znane_x jest interpretowana jako oddzielna zmienna.

  • Jeśli zakres znane_y znajduje się w jednym wierszu, to każdy wiersz znane_x jest interpretowany jako oddzielna zmienna.

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

  • Zakres known_x może zawierać co najmniej jeden zestaw zmiennych. Jeśli jest używana tylko jedna zmienna, known_y i known_x mogą być zakresami dowolnego kształtu, o ile mają jednakowe wymiary. Jeśli jest używana więcej niż jedna zmienna, known_y musi być wektorem (czyli zakresem o wysokości jednego wiersza lub szerokości jednej kolumny).

  • Jeśli argument znane_x jest pominięty, przyjmuje się, że jest on tablicą {1;2;3;...} o takim samym rozmiarze co znane_y.

• stała    Argument opcjonalny. Wartość logiczna określająca, czy stała b ma być wymuszana jako równa 0.

  • Jeśli 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.

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

  • Jeśli statystyka ma wartość PRAWDA, funkcja REGLINP zwraca dodatkowe statystyki regresji. w wyniku tego zwrócona tablica to {mn;mn-1,...,m1;b; sen, sen-1,...,se1, seb; r², sey; F,df; ssreg, ssresid}.

  • Jeśli argument statystyka ma wartość FAŁSZ lub jest pominięty, to funkcja REGLINP zwraca tylko współczynniki m i stałą b.

    Poniżej przedstawiono dodatkowe statystyki regresji:

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 pisane jako m, weź dwa punkty na linii (x1,y1) i (x2,y2); nachylenie jest równe (y2 - y1)/(x2 - x1).

  Przecięcie Y (b):
  Przecięcie y linii, często pisane jako b, jest wartością y w punkcie, w którym linia 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;known_x);1)

  Przecięcie Y:
  =INDEKS(REGLINP(known_y;known_x);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 arytmetycznymi, tj. x = ŚREDNIA(znane_x) i y = ŚREDNIA(znane_y).

• Funkcje reglinii i krzywej REGLINP i REGLINP mogą obliczyć najlepszą linię prostą lub krzywą wykładniczą pasującą do danych. Musisz jednak zdecydować, który z tych dwóch wyników najlepiej pasuje do Twoich danych. Można obliczyć wartość REGLINW(known_y;known_x) dla linii prostej lub funkcji 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. Warto je utworzyć na obu wykresach w celu porównania wizualnego.

• 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). Jeśli argument stała ma wartość PRAWDA lub jest pominięty, łączna suma kwadratów jest sumą kwadratów różnic pomiędzy rzeczywistymi wartościami y a średnią z wartości y. Jeśli argument stała ma wartość FAŁSZ, łączna suma kwadratów jest sumą kwadratów rzeczywistych wartości y (od wartości tych nie jest odejmowana średnia z wartości y). Regresyjna suma kwadratów (ssreg) jest obliczana jako różnica łącznej sumy kwadratów i resztkowej sumy kwadratów. Mniejsza jest resztkowa suma kwadratów w porównaniu z całkowitą sumą kwadratów, tym większa jest wartość współczynnika oznaczania r², co jest wskaźnikiem tego, jak dobrze równanie wynikające z analizy regresji wyjaśnia relację między zmiennymi. Wartość r² jest równa wartości ssreg/sstotal.

• W niektórych przypadkach co najmniej jedna kolumna X (załóżmy, że kolumny Y i X znajdują się w kolumnach) może nie mieć żadnej dodatkowej wartości predykcyjnej w obecności pozostałych kolumn X. Innymi słowy, wyeliminowanie jednej lub większej liczby kolumn X może prowadzić do równie dokładnych przewidywanych wartości Y. W takim przypadku nadmiarowe kolumny X powinny zostać pominięte w modelu regresji. Zjawisko to nazywa się "współliniowością", ponieważ każdą nadmiarową kolumnę X można wyrazić jako sumę wielokrotności nie nadmiarowych kolumn X. Funkcja REGLINP sprawdza współliniowość i usuwa zbędne kolumny X z modelu regresji, gdy je identyfikuje. Usunięte kolumny X można rozpoznać w wynikach funkcji REGLINP jako mające 0 współczynników oprócz wartości 0 se. Jeśli co najmniej jedna kolumna zostanie usunięta jako nadmiarowa, problem dotyczy df, ponieważ wartość df zależy od liczby kolumn X faktycznie używanych do przewidywania. Aby uzyskać szczegółowe informacje na temat obliczania wartości df, zobacz Przykład 4. Jeśli wartość df zostanie zmieniona, ponieważ usuwane są nadmiarowe kolumny X, mają one również wpływ na wartości sey i F. Współliniowość powinna być stosunkowo rzadka w praktyce. Jednak bardziej prawdopodobne jest, że niektóre kolumny X zawierają tylko wartości 0 i 1 jako wskaźniki tego, czy temat eksperymentu jest członkiem określonej grupy, czy nie. Jeśli argument stała = PRAWDA lub zostanie pominięty, funkcja REGLINP skutecznie wstawia dodatkową kolumnę X zawierającą wszystkie 1 wartości w celu modelowania przecięcia. Jeśli masz kolumnę z 1 dla każdego tematu, jeśli mężczyzna, lub 0, jeśli nie, i masz również kolumnę z 1 dla każdego tematu, jeśli kobieta, lub 0, jeśli nie, ta ostatnia kolumna jest zbędna, ponieważ wpisy w niej można uzyskać odejmowania wpisu w kolumnie "wskaźnik męski" od wpisu w dodatkowej kolumnie wszystkich 1 wartości dodanych przez funkcję REGLINP .

• Gdy z modelu nie są usuwane żadne kolumny X z powodu zależności liniowej, wartość df jest obliczana w następujący sposób: jeśli istnieje k kolumn znane_x i argument stała ma wartość PRAWDA lub jest pominięty, to df = n – k – 1. Jeśli argument stała ma wartość FAŁSZ, to 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, taka jak znane_x, 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 znane_y mają wartość 0, a punkty danych argumentu znane_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! . Algorytm funkcji NACHYLENIE i ODCIĘTA jest przeznaczony do wyszukiwania 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