Funkcja REGLINP Excel - statystyka regresji liniowej

Pobierz przykładowy skoroszyt

Pobierz przykładowy skoroszyt

Ten samouczek pokazuje, jak używać Funkcja Excel REGLINP w programie Excel, aby obliczyć statystyki dotyczące linii trendu.

Omówienie funkcji REGLINP

Funkcja LINEST Oblicza statystyki dotyczące linii trendu dopasowanej do znanych punktów danych przy użyciu metody najmniejszych kwadratów.

Aby użyć funkcji arkusza programu Excel REGLINP, wybierz komórkę i wpisz:

(Zwróć uwagę, jak pojawiają się dane wejściowe formuły)

Funkcja LINEST Składnia i wejścia

1	= REGLINP(znane_ys;znane_xs;stała;statystyki)

znane_y - Tablica znanych wartości Y.

znane_x - Tablica znanych wartości X.

stały - OPCJONALNY. Wartość logiczna wskazująca, czy obliczyć B (punkt przecięcia w y = mx + b) metodą najmniejszych kwadratów (PRAWDA lub pominięta), czy ręcznie ustawić B = 0 (FAŁSZ).

statystyki - OPCJONALNY. Zwróć dodatkowe statystyki (PRAWDA) lub zwróć tylko m (nachylenie) i b (przecięcie) (FAŁSZ lub pominięte)

Co to jest REGLINP?

Funkcja REGLINP w programie Excel to funkcja służąca do generowania statystyk regresji dla modelu regresji liniowej. Funkcja REGLINP to formuła tablicowa, której można używać samodzielnie lub z innymi funkcjami do obliczania określonych statystyk dotyczących modelu.

Regresja liniowa to metoda statystyczna służąca do przewidywania danych po linii prostej przy użyciu znanych danych. Regresja służy do przewidywania wartości, takich jak wzrost sprzedaży, zapotrzebowanie na zapasy lub proste trendy rynkowe.

Funkcja REGLINP jest podobna do PROGNOZOWANIA, ponieważ osiąga podobny wynik, ale zawiera znacznie więcej informacji o modelu regresji, a także opcję dopasowania więcej niż jednej zmiennej niezależnej.

Załóżmy, że mam tabelę danych z x oraz tak wartości gdzie x jest zmienną niezależną i tak jest zmienną zależną:

Chcę wiedzieć, jakie jest równanie regresji powyższych danych. Używając funkcji REGLINP:

1	=REGLINP(B3:B7;C3:C7;PRAWDA;FAŁSZ)

Wartość punktu przecięcia y jest tutaj równoważna 0 w notacji naukowej.

Równanie prostej to y= 2x + 0 . Zwróć uwagę, że zwraca REGLINP Zarówno nachylenie i przecięcie linii. Aby zwrócić obie wartości, formułę należy wprowadzić jako formułę tablicową. Więcej o formułach tablicowych później.

Jak korzystać z funkcji LINEST

Funkcja REGLINP przyjmuje cztery argumenty:

1	=REGLINP(znane_y;znane_x;stała;statystyki)

Gdzie,

Argument	Opis
znane_y oraz znane_x	Jest x oraz tak dane w Twojej tabeli danych
stały	PRAWDA/FAŁSZ opcja określająca, czy punkt przecięcia z osią y powinien być wymuszony na 0, czy obliczany normalnie
statystyki	TRUE/FALSE opcja, czy należy zwrócić dodatkowe statystyki regresji

Korzystając z naszego pierwszego przykładu, funkcja jest napisana jako:

1	=REGLINP(B3:B7;C3:C7;PRAWDA;FAŁSZ)

Kiedy statystyki opcja jest ustawiona na TRUE, organizacja statystyk regresji jest następująca:

Być może zastanawiasz się, co oznacza każda zmienna.

Statystyczny	Opis
mn	Współczynniki nachylenia dla x zmienne
b	przechwycenie y
sen	Błąd standardowy dla każdego współczynnika nachylenia
seb	Błąd standardowy punktu przecięcia osi Y
r2	Współczynnik determinacji
setak	Błąd standardowy dla tak oszacować
F	Statystyka F (w celu określenia, czy związek zmiennych występuje przypadkowo)
DF	Stopnie swobody
SSreg	Suma kwadratów regresji
SSmieszkać	Pozostała suma kwadratów

Główne statystyki do zrozumienia to współczynniki nachylenia, punkt przecięcia z osią y i współczynnik determinacji lub r² wartość modelu.

Korzystając z powyższego przykładu i wybierając TRUE dla statystyki parametr:

Podświetlone komórki pokazują nachylenie = 2, przecięcie = 0 i r² = 1.

R² wartość jest wskaźnikiem siły korelacji modelu. Można to traktować jako wskaźnik dopasowania. Niski r² wartość oznaczałaby słabą korelację między zmienną zależną i niezależną, a odwrotna sytuacja ma miejsce w przypadku wysokiego r² wartości, z r² = 1 oznacza idealne dopasowanie.

W wydaniach następujących po styczniu 2022 r. programu Excel na platformie Microsoft 365 (wcześniej Office 365) tablice dynamiczne zmieniły sposób oceny formuł tablicowych. Nie jest już wymagane używanie klawiszy CTRL + SHIFT + ENTER ani zaznaczanie obszaru komórek, które zajmie tablica. Po prostu wprowadź formułę i kliknij enter, a wynikowe komórki „wypłyną” do tablicy.

W pozostałej części tego artykułu będziemy odwoływać się do używania funkcji REGLINP w odniesieniu do tablic dynamicznych w programie Microsoft 365 Excel.

Prognozowanie za pomocą funkcji REGLINP (prosta regresja)

Połączenie funkcji REGLINP i SUMA można wykorzystać do przewidywania wartości zmiennej zależnej tak, biorąc pod uwagę znane x oraz tak dane. Poniżej znajduje się przykład pokazujący, co tak wartość będzie, gdy x=14.

1	=SUMA(REGLINP(C3:C7;B3:B7)*{14,1})

Model ma postać y =mx + b . To to samo co y = a+ bx, tylko inny sposób przedstawienia równania. Wskazówka, o której należy pamiętać w przypadku równań liniowych, to zmienna obok x jest zawsze nachyleniem, a zmienna występująca po znaku plus lub minus jest zawsze punktem przecięcia, niezależnie od liter użytych w równaniu.

Użycie formuły: =SUM(LINEST(C3:C7,B3:B7)*{14,1})zwraca wynik 28. Ponieważ jest to pojedynczy wynik, nie trzeba wprowadzać go jako tablicy.

Koniec powyższego wzoru *{14,1} określa zmienną niezależną, która ma być użyta do przewidywania zmiennej zależnej, w tym przypadku 14.

Możemy to sprawdzić, wpisując x= 14 do równania prostej, y =2x + 0.

Prognozowanie za pomocą funkcji REGLINP (wielokrotna regresja liniowa)

Poniższa tabela danych pochodzi ze strony REGLINP w witrynie pomocy technicznej firmy Microsoft.

W niektórych przypadkach istnieje więcej niż jedna zmienna niezależna, którą należy wziąć pod uwagę przy tworzeniu modelu regresji liniowej. Nazywa się to wielokrotną regresją liniową (tj. wiele zmiennych niezależnych). Jeśli chcę oszacować koszt budynku biurowego, elementy takie jak powierzchnia, liczba wejść do budynku, wiek budynku i liczba biur będą częścią równania. Zobaczmy przykład.

Wpisując formułę REGLINP do komórki G29 i wykonując ją, otrzymujemy:

1	=REGLINP(E3:E13;A3:D13;PRAWDA;PRAWDA)

Model występuje w postaci:

Pamiętaj, że tablica wyników REGLINP jest w odwrotnej kolejności niż w równaniu. W powyższym przykładzie 52 317,8 to nasz punkt przecięcia, b, a 27,6 to nasze m₁ lub wartość nachylenia zmiennej Przestrzeń podłogi, x₁.

Używając funkcji REGLINP z dostarczonymi danymi, nasz model regresji to:

Z r² wartość 0,997, co wskazuje na model silny lub silnie skorelowany. Korzystając z modelu, można teraz przewidzieć, jaka będzie szacowana wartość budynku biurowego na podstawie dowolnej kombinacji powyższych zmiennych niezależnych.

Wskazówki dotyczące funkcji LINEST

1. Upewnij się, że masz najnowszą wersję platformy Microsoft 365, aby korzystać z funkcji REGLINP z tablicami dynamicznymi. Może być konieczne włączenie bieżącego kanału niejawnego programu testów pakietu Office (wersja zapoznawcza) w celu korzystania z funkcji tablic dynamicznych. Na stronie konta:
   
2. Jeśli korzystasz z wersji innej niż Microsoft 365, będziesz musiał użyć starszej metody CTRL + SHIFT + ENTER (CSE) do oceny formuł tablicowych.
3. W przypadku korzystania ze starszej metody liczba kolumn do podświetlenia podczas wprowadzania funkcji tablicowej REGLINP jest zawsze liczbą x zmienne w danych plus 1. Liczba wierszy do wybrania dla tablicy to 5.
4. Jeśli będziesz udostępniać wersję programu Excel obsługującą tablicę dynamiczną osobie, która korzysta z wersji innej niż Microsoft 365, użyj starszej metody CSE, aby uniknąć problemów ze zgodnością.

Chcesz więcej prognozować?

Zobacz nasze inne artykuły na temat prognozowania z funkcjami wygładzania wykładniczego, TRENDU, WZROSTU i LOGESTU.

LINII funkcja w Arkuszach Google

Funkcja REGLINP działa dokładnie tak samo w Arkuszach Google, jak w programie Excel.

LINEST Przykłady w VBA

Możesz także użyć funkcji REGLINP w VBA. Rodzaj:
application.worksheetfunction.linest(znane_ys,znane_xs,const,statystyki)

Wykonanie następującej instrukcji VBA

1	Range("D2") = Application.WorksheetFunction.LinEst(Range("A2:A8"), Range("B2:B8"))

przyniesie następujące wyniki

W przypadku argumentów funkcji (znane_y itp.) można wprowadzić je bezpośrednio do funkcji lub zdefiniować zmienne, które mają być używane w zamian.

Wróć do listy wszystkich funkcji w Excelu