REGLINP, funkcja

Uwaga: Staramy się udostępniać najnowszą zawartość Pomocy w Twoim języku tak szybko, jak to możliwe. Ta strona została przetłumaczona automatycznie i może zawierać błędy gramatyczne lub nieścisłości. Chcemy, aby ta zawartość była dla Ciebie przydatna. Prosimy o powiadomienie nas, czy te informacje były pomocne, u dołu tej strony. Oto angielskojęzyczny artykuł do wglądu.

Ten artykuł zawiera opis składni formuły i zastosowania funkcji REGLINP w programie Microsoft Excel. Aby uzyskać więcej informacji na temat wykresów i wykonywania analizy regresji, skorzystaj z linków dostępnych w sekcji Zobacz też.

Opis

Funkcja REGLINP oblicza statystykę dla wiersza przy użyciu metody "najmniejszych kwadratów" w celu obliczenia linii prostej, która najlepiej pasuje do danych, a następnie zwraca tablicę opisującą linię. Funkcję REGLINP można też łączyć z innymi funkcjami w celu obliczenia statystyk dla innych typów modeli, które są liniowe w nieznanych parametrach, w tym wielomianu, logarytmicznej, wykładniczej i szeregowej potęgi. Funkcja zwraca tablicę wartości, musi więc być wprowadzana w postaci formuły tablicowej. Instrukcje są zgodne z przykładami zawartymi 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 znane_x może zawierać jeden lub kilka zestawów zmiennych. Jeśli użyto tylko jednej zmiennej, argumenty znane_y i znane_x mogą być zakresami dowolnego kształtu, o ile mają jednakowe wymiary. Jeśli użyto więcej niż jednej zmiennej, argument znane_y musi być wektorem (czyli zakresem z 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 argument statystyka ma wartość PRAWDA, to funkcja REGLINP zwraca dodatkowe statystyki regresji, więc zwrócona tablica wygląda następująco: {mn;mn-1;...;m1;b\sen;sen-1;...;se1;seb\r2;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:

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!, kiedy stała ma wartość FAŁSZ).

r2

Współczynnik wyznaczania. Porównuje szacunkowe i rzeczywiste wartości y oraz wartości z zakresu od 0 do 1. Jeśli jest ona równa 1, w próbce jest doskonale poprzednia, a wartością y nie jest różnica. W drugiej skrajnie, jeśli współczynnik określania jest równy 0, równanie regresji nie jest pomocne przy prognozowaniu wartości y. Aby uzyskać informacje na temat obliczania liczby R2, zobacz "uwagi" w dalszej części tego tematu.

sey

Standardowy błąd oceny y.

F

Statystyka F lub wartość obserwowana F. Statystyka F umożliwia określenie, czy obserwowana zależność między zmiennymi zależnymi a niezależnymi jest spowodowana szansą.

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.

Arkusz

Spostrzeżenia

  • Można opisać dowolną nachyloną linię prostą przecinającą oś y:

    Nachylenie (m):
    Aby znaleźć nachylenie linii, często zapisywane jako m, należy wziąć 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(znane_y;znane_x);1)

    Punkt przecięcia z osią y:
    =INDEKS(REGLINP(znane_y;znane_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:

    Równanie

    Równanie

    gdzie x i y są średnimi arytmetycznymi, tj. x = ŚREDNIA(znane_x) i y = ŚREDNIA(znane_y).

  • Funkcje REGLINP i REGEXPP , które dopasowują optymalną linię prostą lub krzywą wykładniczą, które pasują do danych. Należy jednak zdecydować, które z dwóch wyników najlepiej pasuje do danych. Możesz obliczyć Trend (znane_y; znane_x) dla linii prostej lub przyrostu (znane_y; znane_x) dla krzywej wykładniczej. Te funkcje bez argumentu nowe_x zwracają tablicę wartości y przewidywanych wzdłuż tego wiersza lub krzywej na rzeczywistych punktach danych. Następnie można porównać wartości przewidywane z wartościami rzeczywistymi. W celu wizualnego porównania warto utworzyć wykres.

  • 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. Im mniejsza jest resztkowa suma kwadratów w porównaniu z łączną 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ść r2 jest równa wartości ssreg/sstotal.

  • W niektórych przypadkach w jednej lub większej liczbie kolumn X (Załóżmy, że kolumny Y i X znajdują się w kolumnach) mogą nie mieć dodatkowej wartości predykcyjnej w obecności innych kolumn X. Innymi słowy, usunięcie jednej lub wielu kolumn X może prowadzić do przewidzenia wartości Y, które są jednakowo dokładne. W takim przypadku nadmiarowe kolumny X należy pominąć w modelu regresji. Zjawisko to jest nazywane "kolinearnością", ponieważ każda nadmiarowa kolumna X może być wyrażona sumą wielokrotności nienadmiar kolumn X. Funkcja REGLINP sprawdza kolinearność i usuwa wszystkie nadmiarowe kolumny X z modelu regresji, gdy identyfikują je. Usunięte kolumny X można rozpoznać w danych wyjściowych funkcji REGLINP , oprócz wartości 0 SE. Jeśli co najmniej jedna kolumna zostanie usunięta jako nadmiarowa, wpływa to na wartość DF, ponieważ jest ona zależna od liczby kolumn X używanych w celu przewidywania. Aby uzyskać szczegółowe informacje na temat obliczania DF, zobacz przykład 4. Jeśli wartość DF zostanie zmieniona, ponieważ nadmiarowe kolumny X zostaną usunięte, dotyczy to również wartości Sey oraz F. Kolinearność powinna być stosunkowo rzadkim rozwiązaniem. Jednak w przypadku, gdy niektóre kolumny X zawierają tylko wartości 0 i 1, jako wskaźniki określające, czy dany element eksperymentu jest lub nie jest członkiem określonej grupy. Jeśli argument stała ma wartość PRAWDA lub jest pominięty, funkcja REGLINP skutecznie wstawia dodatkową kolumnę X zawierającą wszystkie wartości w celu zamodelowania przecięcia. Jeśli masz kolumnę z wartością 1 dla każdego podmiotu, jeśli jest to argument męski, lub 0, jeśli nie, a także kolumna z wartością 1 dla każdego tematu, jeśli jest to wartość żeńskia, lub 0, jeśli nie, ta Ostatnia kolumna jest zbędna, ponieważ wpisy w nim można uzyskać od odejmowania wpisu w "wskaźniku męskim". kolumna z wpisu w dodatkowej kolumnie wszystkich 1 wartości dodawanych 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.

  • Formuły zwracające tablice muszą być wprowadzone jako formuły tablicowe.

    Uwaga:  W aplikacji Excel Online nie można tworzyć formuł tablicowych.

  • 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ź.

    • Nachylenie i przecięcie zwracają #DIV/0! . Algorytm funkcji nachylenie i ODCIĘTA służy 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

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

Będąc

169 000 zł

2448

2

1,5

99

126 000 zł

2471

2

2

34

142 900 zł

2494

3

3

Będąc

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 (formuła tablicowa wprowadzona w komórkach A14:A18)

=REGLINP(E2:E12;A2:D12;PRAWDA;PRAWDA)

Przykład 4. Użycie statystyk F i r2

W poprzednim przykładzie użyto współczynnika wyznaczania r2, 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 z funkcji REGLINP mogą być używane do oceny prawdopodobieństwa zwiększenia wartości F w wyniku. Funkcja F może być porównywana z wartościami krytycznymi w opublikowanych tabelach rozkładu F lub w programie Excel za pomocą funkcji rozkład . f. tekst można użyć do obliczenia prawdopodobieństwa zwiększenia wartości F występującej przez szansę. Odpowiedni rozkład F ma liczbę stopni 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 argument const = FAŁSZ, to V1 = n – DF i v2 = DF). Funkcja rozkład . F — za pomocą składni funkcji rozkład. f (F; v1; v2) — zwraca prawdopodobieństwo zwiększenia wartości F w wyniku. 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. (Na poziomie Alfa = 0,05 hipoteza o braku zależności między wartościami w tablicy Znane_y i tablicy Znane_x powinna zostać odrzucona, gdy F przekracza wartość krytyczną 4,53). Z pomocą funkcji ROZKŁAD.F programu 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.

W przypadku zapoznania się z tabelą w instrukcji statystycznej można stwierdzić, że krytyczne, dwie strony z 6 stopniami swobody i alfa = 0,05 są 2,447. Tę wartość krytyczną można również znaleźć przy użyciu funkcji rozkład . CHI. 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 jest ważną zmienną podczas szacowania szacowanej wartości biurowca. Każda z pozostałych zmiennych niezależnych może być testowana w podobny sposób. Poniżej przedstawiono wartości obserwowane t dla każdej z zmiennych niezależnych.

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.

Rozwijaj umiejętności związane z pakietem Office
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

Dziękujemy za opinię!

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×