omawiałem już regresję liniową na tym blogu, ale dość często linia prosta nie jest najlepszym sposobem na reprezentowanie danych. W tych konkretnych sytuacjach możemy skorzystać z niektórych dostępnych narzędzi do wykonywania regresji nieliniowej lub dopasowywania krzywych w programie Excel.
pamiętasz naszego starego przyjaciela LINESTA? Chociaż LINEST jest skrótem od „liniowego oszacowania”, możemy go również użyć do nieliniowych danych z kilkoma prostymi poprawkami.
powiedzmy, że mamy pewne dane dotyczące spadku ciśnienia vs. natężenie przepływu przez zawór wodny, a po wykreśleniu danych na wykresie widzimy, że dane są kwadratowe.
mimo, że dane te są nieliniowe, funkcja LINEST może być również użyta tutaj, aby znaleźć najlepiej dopasowaną krzywą dla tych danych. Dla równania wielomianowego, robimy to używając stałych tablicowych.
zaletą korzystania z LINEST do uzyskania współczynników definiujących równanie wielomianowe jest to, że możemy zwrócić współczynniki bezpośrednio do komórek. W ten sposób nie musimy ręcznie przenosić ich z wykresu.
ponieważ równanie jest kwadratowe lub wielomianem drugiego rzędu, istnieją trzy współczynniki, jeden dla X do kwadratu, jeden dla x i stała. Musimy więc zacząć od stworzenia przestrzeni do przechowywania trzech współczynników dla równania.
używanie LINEST do regresji nieliniowej w programie Excel
funkcja LINEST zwraca tablicę współczynników i opcjonalne statystyki regresji. Musimy więc wprowadzić go jako formułę tablicy, wybierając wszystkie trzy komórki współczynników przed wprowadzeniem formuły.
Jeśli komórki zawierające dane dotyczące przepływu i ciśnienia są nazwane „przepływ” i „ciśnienie”, wzór wygląda następująco:
=LINEST(ciśnienie, przepływ^{1,2},TRUE, FALSE)
znane y w tym przypadku są pomiarami ciśnienia, a znane x są przepływem pomiary podniesione do potęgi pierwszej i drugiej. Nawiasy klamrowe, ” {„i”}”, wskazują stałą tablicy w programie Excel. Zasadniczo mówimy Excelowi, aby stworzył dwie tablice: jedną z flow i drugą z Flow-squared, i aby dopasować ciśnienie do obu tych tablic razem.
wreszcie, prawdziwe i fałszywe argumenty mówią funkcji liniowej do obliczenia przecięcia osi y normalnie (zamiast zmuszać go do zera) i nie zwracać dodatkowych statystyk regresji, odpowiednio.
ponieważ jest to formuła tablicy, musimy ją wprowadzić wpisując Ctrl+Shift+Enter.
funkcja następnie zwraca współczynniki x2 i x oraz stałą (ponieważ zdecydowaliśmy się pozwolić LINIŚCIE obliczyć punkt przecięcia osi y).
współczynniki są identyczne z tymi generowanymi przez narzędzie wykres trendu, ale są teraz w komórkach, co znacznie ułatwia ich wykorzystanie w kolejnych obliczeniach.
dla dowolnego równania wielomianowego, LINEST Zwraca współczynnik dla najwyższego rzędu zmiennej niezależnej po lewej stronie, następnie następny najwyższy i tak dalej, a na końcu stałą.
podobną technikę można zastosować również do dopasowania krzywej funkcji wykładniczej, logarytmicznej i mocy w programie Excel.
dopasowanie krzywej logarytmicznej do danych
funkcja logarytmiczna ma postać:
nadal możemy użyć linest, aby znaleźć współczynnik, M i stałą, B dla tego równania, wstawiając LN(X) jako argument dla znanych_x:
=LINEST(y_values,LN(x_values),TRUE,FALSE)
oczywiście, ta metoda ma zastosowanie do dowolnego równania logarytmicznego, niezależnie od liczby bazowej. Tak więc można to zastosować do równania zawierającego log10 lub log2 równie łatwo.
znajdowanie współczynników najlepiej dopasowanej krzywej wykładniczej
funkcja wykładnicza ma postać:
trochę trudniej jest uzyskać współczynniki, a i b, dla tego równania, ponieważ najpierw musimy zrobić trochę algebry, aby równanie przybrało formę „liniową”. Najpierw weź logarytm naturalny z obu stron równania, aby uzyskać następujące dane:
teraz możemy użyć LINEST, aby uzyskać ln (a) i b, wprowadzając LN (y) jako argument dla wartości y_values:
=LINEST(LN(y_values),X_VALUES,TRUE,FALSE)
drugą wartością zwracaną przez tę formułę tablicy jest ln(a), więc aby uzyskać tylko „a”, po prostu użyjemy funkcji wykładniczej:
, która, w Excelu, tłumaczy się na:
=EXP(liczba)
dopasowanie funkcji mocy do danych
krzywa funkcji mocy może być dopasowana do danych za pomocą linest w taki sam sposób, jak robimy to dla funkcji wykładniczej. Funkcja potęgowa ma postać:
ponownie możemy „linearyzować” go, przyjmując logarytm o podstawie 10 z obu stron równania, aby uzyskać:
z równaniem w tej postaci,funkcja liniowa zwracająca B i log10(a) może być ustawiona w następujący sposób:
=linest(log10(Yvalues), log10(xvvalues), true, false)
ponieważ funkcja liniowa zwraca B i log10(a), będziemy musieli znaleźć a za pomocą następującego wzoru:
w Excelu formuła ta jest:
=10^(liczba)
to na razie tyle. Jak widać, istnieje wiele sposobów korzystania z funkcji liniowej do nieliniowego dopasowania krzywej w programie Excel.