[Excel] aproksymacja danych funkcją

[coldguy]

Cześć. Muszę nauczyć się rozwiązywania tego typu zadań. Nie chcę studiować książek na temat aproksymacji (cokolwiek to jest), bardziej zależy mi na poznaniu kroków, jakie należy wykonać, żebym potrafił zrobić to i podobne zadania. Może ktoś mądry mi wytłumaczy

Mamy podane pomiary x i y (

Kod: Zaznacz cały

http://wklej.org/id/866039/

). Aproksymować te dane funkcją
a) \(\displaystyle{ y=ax+b}\)
b) \(\displaystyle{ y=ax^{b}}\)
c) \(\displaystyle{ y=ae ^{bx}}\)
Wyznaczyć współczynnik korelacji liniowej r oraz współczynniki a i b podanych funkcji.
Wskazać f najlepiej aproksymującą.

[pawellogrd]

W tej chwili nie bardzo jestem w stanie Ci napisać coś dokładniej z braku czasu, ale odnośnie a) sprawdź funkcję REGLINP - to jest regresja liniowa. Jako argument podajesz argumenty x i wartości y, a funkcja ta zwraca Ci współczynniki \(\displaystyle{ a}\) oraz \(\displaystyle{ b}\) funkcji \(\displaystyle{ y=ax+b}\) aproksymującej dane.

[mmoonniiaa]

b) i c) należy najpierw zlinearyzować obustronnie logarytmując, a następnie także wykorzystać regresję liniową w Excelu.

[coldguy]

Dzięki za odpowiedź, już coś jaśniej.
Wyszło \(\displaystyle{ y=ax+b \Rightarrow y=5,46x-3,76}\)

Mam nadzieję, że będziesz miał jeszcze czas w tym tygodniu

@monia,
Da się jakoś zlinearyzować w Excelu?

[mmoonniiaa]

a) powinno Ci wyjść: \(\displaystyle{ y=\red 5,42 \black x-3,76}\)
b) i c) linearyzujemy (czyli sprowadzamy do postaci liniowej) najpierw na literkach poprzez obustronne logarytmowanie.
b) \(\displaystyle{ \ln y=\ln \left( ax^{b}\right) \Leftrightarrow \ln y = \ln a + \ln x^b \Leftrightarrow \blue \ln y = \ln a + b \ln x}\)
Teraz Excel przyda się do obliczenia kolumny: \(\displaystyle{ \ln y}\) oraz \(\displaystyle{ \ln x}\), a następnie oszacuj regresję liniową korzystając z funkcji REGLINP, gdzie zmienną zależną jest \(\displaystyle{ \ln y}\), a niezależną \(\displaystyle{ \ln x}\). Na koniec będzie trzeba jeszcze wrócić do postaci potęgowej.
c) Spróbuj zrobić sam, jest to model wykładniczy.

Jeśli powyższa metoda Ci nie odpowiada, dla wszystkich trzech przykładów możesz otrzymać identyczne wyniki wykorzystując Excela w ten sposób: Zaznacz dane -> wstaw wykres punktowy -> kliknij prawym na jeden z punktów i wybierz dodaj linię trendu -> wybierz liniowy, potęgowy i wykładniczy i zaznacz opcję: wyświetl równanie na wykresie.

[coldguy]

A tak, 5,42, nie wiem, czemu napisałem 6, późno było xD

Wyszło mi
b) \(\displaystyle{ y=2,64x^{1,31}}\)
c) \(\displaystyle{ y=5,04e^{0,24x}}\)
Tylko trzeba uważać, co jest współczynnikiem przy x, a co wyrazem wolnym, ale jak mogę sprawdzić wykresem, to nie powinno być problemu.

Funkcja najlepiej aproksymująca to, domyślam się, \(\displaystyle{ y=ax+b}\), tylko jak to uzasadnić tak ładnie na papierze?
Współczynnik korelacji liniowej?

[mmoonniiaa]

Przyda się współczynnik determinacji \(\displaystyle{ R^2}\), który jest oparty na wartościach teoretycznych, które sobie możesz wyliczyć. Albo wykorzystasz gotowe \(\displaystyle{ R^2}\) z Excela.

[coldguy]

\(\displaystyle{ \sqrt{R^{2}}=1}\)
Trochę nieudolna ta funkcja, skoro \(\displaystyle{ R}\) przyjmuje wartości \(\displaystyle{ [-1; 1]}\), a ja mam \(\displaystyle{ |R|}\), ale mam nadzieję, że o to mu chodziło.

W uzasadnieniu wystarczy, jak napiszę, że prosta \(\displaystyle{ y=5,42x-3,76}\) w największym stopniu pokrywa się z krzywą łączącą punkty?

Jeśli tak, to chyba tyle na temat funkcji.
Mam jeszcze takie pytanie: pierwiastek równania zawsze się znajdzie poleceniem "szukaj wyniku", czy są jakieś szczególne przypadki?

[mmoonniiaa]

Nie bardzo rozumiem, czemu wyciągasz pierwiastek z \(\displaystyle{ R^2}\)?
Ten współczynnik przyjmuje wartości: \(\displaystyle{ R^2 \in \left[ 0;1\right]}\).

Dla modelu liniowego: \(\displaystyle{ R^2=1}\), co oznacza, że \(\displaystyle{ 100 \%}\) zmienności \(\displaystyle{ y}\) zostało wyjaśnione przez ten model.
Dla modelu potęgowego: \(\displaystyle{ R^2=0,9891}\), co oznacza, że \(\displaystyle{ 98,91 \%}\) zmienności \(\displaystyle{ y}\) zostało wyjaśnione przez ten model.
Dla modelu wykładniczego: \(\displaystyle{ R^2=0,8225}\), co oznacza, że \(\displaystyle{ 82,25 \%}\) zmienności \(\displaystyle{ y}\) zostało wyjaśnione przez ten model.
Czyli najlepszą funkcją aproksymującą jest funkcja liniowa.

Co masz na myśli, mówiąc o poleceniu "szukaj wyniku"? Chodzi o Solvera czy Analizę symulacji?

[coldguy]

A, bo ja się posiłkowałem tym ... i_Pearsona
Wyciągam pierwiastek, bo on napisał w zadaniu

Wyznaczyć współczynnik korelacji liniowej r

więc myślałem, że chodzi o \(\displaystyle{ R}\), a nie \(\displaystyle{ R^{2}}\)

Takie zadanie (nie chciało mi się zakładać wątku na jedno pytanie)
Znaleźć pierwiastek równania:
\(\displaystyle{ e^{x}sin(2+x)= \sqrt{x^{2}+2}}\)

AU

3a.png (3.96 KiB) Przejrzano 2414 razy

Dane -> analiza warunkowa -> Szukaj wyniku...

AU

3b.png (22.67 KiB) Przejrzano 2414 razy

AU

3c.png (23.56 KiB) Przejrzano 2414 razy

Wolfram pokazuje, że funkcja ma 3 pierwiastki, ale w poleceniu jest liczba pojedyncza, więc wystarczy 1.
Tylko ja się tego tak szybko uczyłem i nie wiem, czy zawsze mogę sobie na takie coś pozwolić. Albo co by było, jakby mi kazał znaleźć wszystkie?

[mmoonniiaa]

Excel znajduje tylko jedno rozwiązanie, to które spośród wszystkich rozwiązań równania znajduje się najbliżej wartości początkowej. Jeśli wartość początkową wpiszesz 7, to zauważ, że dostaniesz inne rozwiązanie. Ale jakbyś miał znaleźć wszystkie rozwiązania, to nie wiem, co robić mając do dyspozycji tylko Excela.

[coldguy]

Tutaj coś pisze, jak to zrobić Solverem. Ale i tak trzeba mieć wykres funkcji, żeby dodać ograniczenia.
Niby mamy jeszcze w syllabusie Matlab, ale tu się chyba wszystko robi w Excelu.

No to jak Ci tak dobrze idzie, to może sprawdzisz mi jeszcze jedno, bo mam wątpliwości:

Podać wartość elementu \(\displaystyle{ a^{-1}_{2,3}}\) macierzy odwrotnej do macierzy współczynników.

AU

2a.png (57.33 KiB) Przejrzano 2414 razy

[mmoonniiaa]

Dobrze.

[coldguy]

Dzięki