Tworzenie wykresu regresji liniowej

Wstęp

Excel jest bardzo potężnym narzędziem, dzięki któremu można wykonać proste obliczenia permutacji zbioru, czy narysować słupki błędów na wykresie przedstawiającym wartość oporu obliczoną według prawa Ohma. Arkusz kalkulacyjny wchodzący w skład pakietu MS Office ma olbrzymie możliwości, które wykorzystują specjaliści jak i uczniowie do obliczenia różnych danych. W niniejszym artykule zaprezentowana została metoda jak krok po kroku narysować wykres rezystancji wraz z słupkami błędów.

Regresja liniowa na przykładzie prawa Ohma cz. I

Na początku musimy przygotować tabelkę z danymi. Załóżmy, że wykonaliśmy pomiary napięcia i natężenia prądu, a wyniki zapisaliśmy w tabelce:

1. Następnym krokiem będzie obliczenie rezystancji ze wzoru: Ustawiamy się więc w komórce C2 wpisujemy „Rezystancja” i przechodzimy do komórki C3,
2. W komórce C3 wpisujemy formułę (według wzoru na rezystancję) dzięki której obliczmy rezystancje dla pierwszej, w tabeli wartości napięcia i natężenia:
3. Następnie przeciągamy formułę do końca tabelki – tak aby Excel obliczył wartość rezystancji dla wszystkich danych,
4. Na koniec obliczymy jeszcze średnią oczytanych wyników. Ustawiamy się w komórce C11 i wpisujemy odpowiednią formułę:
5. Otrzymane wyniki zaokrąglimy jeszcze do dwóch miejsc po przecinku, aby lepiej się pracowało:
6. Jak widać wyniki w kolumnie „Rezystancja” znacznie się różnią jeden wynosi 9,72 a drugi 12,50 Om-ów. Gdzie zatem jest błąd?

Rodzaje błędów i typ wykresu

Jak każdy z pewnością wie, żaden pomiar nie jest dokładny. Wszystkie dane jakie mamy umieszczone w tabelce posiadają pewien błąd. Podczas rysowania wykresu regresji liniowej należy zwrócić uwagę na trzy rodzaje błędów, jakie mogą pojawić się podczas pomiarów:

1. Błąd wynikający z klasy zastosowanych przedmiotów,
2. Błąd wynikający z zastosowanej metody pomiaru (błąd metody),
3. Błąd gruby (pomyłka).

Jeżeli prowadzimy serię pomiarową to wielkości powinniśmy wyznaczać metodą regresji (aproksymacji), metodą najmniejszych kwadratów.

Zanim zabierzemy się do rysowania wykresu należy zastanowić się jak ma wyglądać wzór funkcji której chcemy narysować wykres. W poprzednim ćwiczeniu przygotowaliśmy tabelkę z takimi danymi jak napięcie (U) oraz natężenie (I). Wykresem funkcji będzie zatem stosunek napięcia (U) do natężenia (I):

y = f(x)

gdzie:

y = U

x = I

czyli:

U = f(I)

Jak z pewnością każdy się domyślił wykresem będzie prosta przechodząca przez punk 0,0. Należy zwrócić jeszcze uwagę, że na wykresie regresji nie da się połączyć punktów linią. Wobec tego prosta którą chcemy narysować będzie przechodzić jak najbliżej wszystkich zaznaczonych pomiarów (kropek) na wykresie (stworzymy tzw. linię trendu).

Regresja liniowa na przykładzie prawa Ohma cz. II

Rozpoczynamy rysowanie wykresu:

1. Wybieramy w zakładce „Wstaw” opcję „Punktowy” z działu „Wykresy” i klikamy w typ „Punktowy tylko ze znacznikami”:
2. Następnie klikamy na wykresie prawą myszą i z menu kontekstowego wybieramy opcję „Przenieś wykres” zaznaczamy „Arkusz 2” i klikamy „Ok”. Wykres został przeniesiony do nowego arkusza.
3. W kolejnym kroku, klikamy prawym klawiszem myszy na wykresie i zaznaczamy opcję „zaznacz dane”. Pojawi się okienko „Wybieranie źródła danych”:
4. Klikamy w przycisk „Dodaj” aby dodać nową serię danych.
5. Zaznaczamy teraz wartości X czyli według podanego wcześniej wzoru U = f(I), natężenie:
6. Następnie podajemy wartości Y czyli napięcie
7. Zatwierdzamy wybór i w tym momencie powinien pojawić się wykres rezystancji:

Dodajemy linię trendu

1. Zaznaczamy serię danych klikając na dowolny punkt.
2. Kolejno klikamy na wykresie prawym klawiszem myszki i z menu kontekstowego wybieramy opcję „dodaj linię trendu”:
3. Na samy dole zaznaczamy opcję „Ustaw przecięcie” i „Wyświetl równanie na wykresie”, klikamy przycisk „Zamknij”.
4. Gotowe, linia trendu została dodana do wykresu:

Słupki błędów

Teraz wystarczy dodać tylko słupki błędów do wykresu:

1. Klikamy na wykres, następnie wybieramy z menu głównego zakładkę „Układ” klikamy na „Słupki błędów” i wybieramy „Słupki błędów z błędem standardowym”:
2. Następnie musimy odpowiednio sformatować wstawione przez Excel słupki. Zaznaczamy słupki błędów klikając na jeden z nich (zaczniemy od pionowych czyli klikamy na pionową kreskę przy słupku błędu) następnie klikamy prawym klawiszem myszy i z menu kontekstowego wybieramy opcję „Formatuj słupki błędów”. Pojawi nam się okno „Formatowanie słupków błędów”:
3. Zaznaczamy pole stała wartość i wpisujemy „0,01”:
4. Teraz zaznaczamy poziome słupki błędów (jeżeli elementy na wykresie są zbyt małe to należy rozciągnąć pole wykresu na cały ekran) i analogicznie do poprzedniego punktu klikamy prawym przyciskiem myszy (w poziome linie na „słupku błędu”), wybieramy opcję „Formatuj słupki błędów” zaznaczamy opcję „Stała wartość” i wpisujemy „0,2”.
5. W ostatnim kroku musimy skasować błędny wynik. W naszym przypadku jest to drugi od dołu pomiar w tabeli, przechodzimy więc do tabelki z danymi i usuwamy wynik. Efekt końcowy prezentuje się tak:Y = 10,521x

Regresja bez wykresu, czyli funkcja REGLINP

Linia trendu na wykresie jest wygodna wizualnie, ale czasem potrzebujemy samych liczb, czyli współczynnika kierunkowego i wyrazu wolnego prostej y = a·x + b, na przykład po to, by użyć ich w dalszych obliczeniach. Excel liczy je bez rysowania wykresu, funkcją REGLINP (w wersji angielskiej LINEST):

Funkcja zwraca dwie wartości naraz: współczynnik kierunkowy a oraz wyraz wolny b. Ponieważ jest to tzw. formuła tablicowa, w starszych wersjach Excela zatwierdzamy ją kombinacją Ctrl + Shift + Enter (w najnowszych wystarczy zwykły Enter). Dla naszego przykładu z prawem Ohma współczynnik a odpowiada wprost oporowi R.

Jak dobrze prosta pasuje do danych? Współczynnik R²

Sama prosta to nie wszystko. Dobrze wiedzieć, jak dobrze opisuje ona nasze pomiary, a służy do tego współczynnik determinacji R², który przyjmuje wartości od 0 do 1. Im bliżej jedynki, tym lepiej prosta pasuje do punktów. Wartość 1 oznacza idealne dopasowanie, a wartości bliskie 0, że zależność liniowa praktycznie nie występuje. W Excelu policzymy go funkcją:

R² można też wyświetlić bezpośrednio na wykresie. W opcjach linii trendu wystarczy zaznaczyć „Wyświetl wartość R-kwadrat na wykresie”. W przypadku prawa Ohma, gdzie zależność napięcia od natężenia jest niemal idealnie liniowa, wartość R² powinna być bardzo bliska jedności, co potwierdza, że nasze pomiary są spójne, a dobrany model liniowy właściwy.