Rachunek regresja kwadratowa i sześcienna w Excelu

Question

Rachunek regresja kwadratowa i sześcienna w Excelu

Mam następujące informacje:

  Height    Weight

    170     65
    167     55
    189     85
    175     70
    166     55
    174     55
    169     69
    170     58
    184     84
    161     56
    170     75
    182     68
    167     51
    187     85
    178     62
    173     60
    172     68
    178     55
    175     65
    176     70

Chcę skonstruować analizę regresji kwadratowej i sześciennej w Excelu. Wiem, jak to zrobić poprzez regresję liniową w Excelu, ale co z kwadratowym i sześciennym? Przeszukałem wiele zasobów, ale nie mogłem znaleźć niczego pomocnego.

54

excel regression

Author: Bart, 2012-06-01

Source

3 answers

Wiem, że to pytanie jest trochę stare, ale pomyślałem, że podam alternatywę, która moim zdaniem może być nieco łatwiejsza. Jeśli chcesz dodać "tymczasowe" kolumny do zestawu danych, możesz użyć narzędzia do analizy Excela→Analiza danych→regresja. Sekretem wykonywania analizy regresji kwadratowej lub sześciennej jest zdefiniowanie zakresu wejściowego x: .

Jeśli robisz prostą regresję liniową, wszystko czego potrzebujesz to 2 kolumny, X i Y. Jeśli robisz kwadratowy, będziesz potrzebował X_1, X_2, & Y gdzie x_1 jest zmienną x i x_2 jest x^2; podobnie, jeśli robisz sześcienny, będziesz potrzebował X_1, X_2, X_3, & Y gdzie X_1 jest zmienną x, x_2 jest x^2 i x_3 jest x^3. Zauważ, że wejście X Range jest od A1 do B22, obejmujące 2 kolumny.

Analiza regresji kwadratowej w Excelu

Poniższy obrazek stanowi wynik analizy regresji. Podkreśliłem wspólne wyniki, w tym wartości R-kwadrat i wszystkie współczynniki.

Analiza regresji kwadratowej w Excelu

13

Author: Michael,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-07-15 17:52:40

Najbardziej liniowa funkcja opisana w poprzedniej odpowiedzi jest droga do zrobienia, ale łatwiejszym sposobem pokazania 3 współczynników wyjściowych jest dodatkowo użycie funkcji INDEX. W jednej komórce, type: =INDEX(LINEST(B2:B21,A2:A21^{1,2},TRUE,FALSE),1) (nawiasem mówiąc, B2:B21 i A2:A21, których użyłem, są tymi samymi wartościami, których użył pierwszy plakat, który odpowiedział na to... oczywiście zmieniłbyś te zakresy odpowiednio do swoich danych). Daje to współczynnik X^2. W sąsiedniej komórce wpisz ten sam formuła ponownie, ale zmień ostatnie 1 na 2... daje to współczynnik X^1. Na koniec, w następnej komórce ponownie wpisz tę samą formułę, ale zmień ostatnią liczbę na 3... to daje stałą. Zauważyłem, że trzy współczynniki są bardzo bliskie, ale nie do końca identyczne z tymi uzyskanymi za pomocą graficznej funkcji linii trendu w zakładce wykresy. Odkryłem również, że LINEST działa tylko wtedy, gdy dane X i Y są w kolumnach (nie wierszach), Bez pustych komórek w zakresie, pamiętaj o tym, Jeśli pojawi się błąd #VALUE.

5

Author: Spanky,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-05-26 00:50:27

score 191 · Accepted Answer

Musisz użyć nieudokumentowanej sztuczki z funkcją Excela LINEST:

=LINEST(known_y's, [known_x's], [const], [stats])

Tło

Regresja regularna liniowa jest obliczana (z Twoimi danymi) jako:

=LINEST(B2:B21,A2:A21)

Który zwraca pojedynczą wartość, nachylenie liniowe (m) według wzoru:

Tutaj wpisz opis obrazka

Które dla Twoich danych:

Tutaj wpisz opis obrazka

Jest:

Tutaj wpisz opis obrazka

Nieudokumentowana sztuczka numer 1

Możesz również użyć programu Excel do obliczenia regresja ze wzorem wykorzystującym wykładnik dla x różny od 1, np. x^1.2:

Tutaj wpisz opis obrazka

Używając wzoru:

=LINEST(B2:B21, A2:A21^1.2)

Które dla Ciebie dane:

Jest:

Nie jesteś ograniczony do jednego wykładnika

Funkcja Excela LINEST może również obliczać wielokrotne regresje, z różnymi wykładnikami na x w tym samym czasie, np.:

=LINEST(B2:B21,A2:A21^{1,2})

Notatka: jeśli locale jest ustawione na Europejski( symbol dziesiętny","), to przecinek należy zastąpić średnikiem i ukośnikiem, tzn. =LINEST(B2:B21;A2:A21^{1\2})

Teraz Excel obliczy regresje za pomocą obu x¹ i x² jednocześnie:

Tutaj wpisz opis obrazka

Jak to zrobić

Niemożliwie trudna część nie ma oczywistego sposobu na zobacz inne wartości regresji. Aby to zrobić, musisz:

Wybierz komórka, która zawiera wzór:
Rozszerz zaznaczenie o lewe 2 spacje (select musi mieć szerokość co najmniej 3 komórek):
Naciśnij F2
Naciśnij Ctrl+Shift+Enter

Zobaczysz teraz swoje 3 stałe regresji:

  y = -0.01777539x^2 + 6.864151123x + -591.3531443

Bonus Chatter

I had a function that I chciał wykonać regresję używając pewnego wykładnika:

Y = m×x ^k + b

Ale nie znałem wykładnika. Dlatego zmieniłem funkcję LINEST, aby zamiast niej używać referencji komórki:

=LINEST(B2:B21,A2:A21^F3, true, true)

Z Excelem, a następnie wyprowadzenie pełnych statystyk (czwarty parametr do LINEST):

Tutaj wpisz opis obrazka

Mówię Solver aby zmaksymalizować R²:

Tutaj wpisz opis obrazka

I może znaleźć najlepszy wykładnik. Które dla Ciebie dane:

Jest: