WSTĘP DO ĆWICZENIA Regresja liniowa

Instrukcja ćwiczenia

Regresja liniowa dla danych X Y

1. W Arkuszu1 programu Excel wpisać dane liczbowe do pierwszej tabeli takie, jakie są
pokazane na stronie 2. Sformatować odpowiednio nagłówki i liczby. Zrobić obramowanie.
Czcionką jest domyślna czcionka programu Excel - Calibri 11 pkt.

2. Na danych z kolumn x

i y

wykonać wykres XY (Punktowy tylko ze znacznikami - bez

żadnych  dodatkowych  linii).  Opisy  do  wykresu  zrobić  takie,  jakie  są  na  stronie  2.
Zoptymalizować wykres, tzn.  dobrać  jego rozmiar, rozmiar obszaru kreślenia,  wyświetlanie
obydwu siatek X i Y,  ilość wyświetlanych cyfr, opis osi X ustawić na samym dole wykresu.
Wielkość czcionki wszystkich opisów powinna być zawarta między 8 a 12 pkt.

Wykonanie wykresu i jego formatowanie:
zaznaczyć dane (liczby w kolumnach x

)



karta Wstawianie



Wykresy



Punktowy



wybrać wykres zbudowany tylko z punktów



pojawi się wykres i karta

kontekstowa Narzędzia wykresów zawierająca karty Projektowanie, Układ i
Formatowanie

Karta Formatowanie



ustawić rozmiar wykresu 8 cm x 14 cm

Karta Układ



Etykiety



Tytuł wykresu



wpisać tytuł jak w przykładzie



czcionka

10-12 pkt.

Karta Układ



Etykiety



Tytuły osi



wpisać tekst w postaci xi i yi



indeksy zrobić

po wpisaniu tytułów



zaznaczyć literę i prawy klawisz myszy



Czcionka



Indeks

dolny

Karta Układ



Etykiety



Legenda



wybrać Brak

Karta Układ



Osie



Więcej opcji głównej osi...



otwiera się okno Formatowanie

osi
Okno Formatowanie osi (najważniejsze polecenia):
Opcje osi



wykonać czynności związane z Minimum i Maksimum (dobieranie skali

wykresu), Jednostka główna i Jednostka pomocnicza



Typ pomocniczego

znacznika domyślnie jest Brak - ew. zmienić



Etykiety osi ustawić tak jak na

przykładowym wykresie (dla osi poziomej Nisko)

Liczby



wybrać Kategoria



Liczbowe i ilość cyfr po kropce

Karta Układ



Linie siatki



Podstawowe pionowe linie siatki



Główne linie siatki

Jeżeli na wykresie nie są wyświetlane poziome linie siatki wykonać ostatnią czynność dla

tych linii.

3. Dodać na wykresie linię trendu (linię regresji liniowej) z wyświetlonym równaniem prostej
i wartością kwadratu współczynnika korelacji r

(R-kwadrat). Wybrać kolor linii, ilość cyfr w

równaniu i tło (pole z równaniem nazywa się etykietą linii trendu).

Dodawanie linii trendu i jej formatowanie:
karta kontekstowa Narzędzia wykresów



karta Układ



Analiza



Linia trendu



wybrać Więcej opcji linii trendu



otwiera się okno Formatowanie linii trendu

Okno Formatowanie linii trendu (najważniejsze polecenia):

Opcje linii trendu



Typ trendu/regresji wybrać Liniowy



zaznaczyć Wyświetl

równanie na wykresie



zaznaczyć Wyświetl wartości R-kwadrat na wykresie

Kolor linii



wybrać Linia ciągła i Kolor

Formatowanie etykiety linii trendu



prawy klawisz myszy na etykiecie



Formatuj

etykietę linii trendu



otwiera się okno Formatowanie etykiety linii trendu



wybrać

polecenia Liczby, Wypełnienie i Kolor krawędzi

4. Wykonać pełną statystykę regresji z wykorzystaniem funkcji REGLINP dla prostej o
równaniu y=a



x+b (UWAGA: w programie Excel równanie prostej ma postać y=m



x+b).

Funkcja REGLINP wykorzystuje formułę tablicową.

Obliczenie pełnej statystyki regresji z użyciem funkcji REGLINP:
- w Arkuszu1 poniżej wykresu zaznaczyć blok komórek o wymiarach 2



- karta Formuły



Biblioteka funkcji



Więcej funkcji



Statystyczne i REGLINP.

Otwiera się okno Argumenty funkcji. Okno to wypełnić następująco:

Znane_y: przeciągnąć myszką przez wszystkie wartości y

;

Znane_x: przeciągnąć myszką przez wszystkie wartości x

;

Stała: ponieważ wykres jest typu y=a



x+b wpisać wartość logiczną PRAWDA lub 1;

Statystyka: ponieważ ma być zrobiona pełna statystyka wpisać PRAWDA lub 1.

Następnie  wcisnąć  OK.  W  pasku  formuły  wyświetlona  zostanie  formuła  w  postaci:
=REGLINP(C4:C13;B4:B13;1;1).  Ustawić kursor  myszy  w pasku  formuły (w dowolnym
miejscu formuły) i nacisnąć klawisze: Ctrl+Shift+Enter. Efektem tego będzie pojawienie
się  liczb  w  zaznaczonym  bloku  komórek  tabeli  -  są  one  wartościami  statystyki  metody
najmniejszych kwadratów.

Umieścić opis poszczególnych komórek tabeli tak jak pokazane jest to na stronie 2:
a - współczynnik kierunkowy prostej, s

- niepewność współczynnika a, r

- kwadrat

współczynnika korelacji r, b - wyraz wolny, s

- niepewność wyrazu wolnego b, s

- średnie

odchylenie współrzędnych y

od wartości teoretycznych y

, k - ilość stopni swobody rozkładu

Studenta. Pozostałe komórki związane są z rozkładem statystycznym F - nie są w naszych
opracowaniach wykorzystywane.

5. Obliczyć współczynnik korelacji r poprzez wpisanie odpowiedniej formuły.

6. Wyznaczyć wartość Rozkładu t-Studenta - funkcja ROZKŁ.T.ODWR.DS.

- wpisać tekst „Stopnie swobody k”, a następnie formułę kopiującą k z tabeli REGLINP
- wpisać tekst Współczynnik istotności



, a następnie liczbę 0.05

- wpisać tekst Wartość rozkładu t



, a następnie karta Formuły



Więcej funkcji



Statystyczne i ROZKŁ.T.ODWR.DS.
Obliczenie funkcji ROZKŁ.T.ODWR.DS:
okno Argumenty funkcji wypełnić następująco: Prawdopodobieństwo - adres
komórki z liczbą 0.05, Stopnie_swobody - adres komórki z liczbą Stopni swobody k.
Wcisnąć OK.

7. Obliczyć Przedziały ufności



a i



b dla współczynników prostej:

- wpisać tekst



a=s



=, a następnie formułę do obliczenia



- wpisać tekst



b= s



=, a następnie formułę do obliczenia



8. Podać końcowe parametry Równania prostej y= a



x+b. Wpisać do odpowiednich komórek

teksty  a=  i  b=,  a  za  nimi  formuły  kopiujące  zawartości  odpowiednich  komórek.  Ilość  cyfr
podać zgodnie z zasadami obowiązującymi przy formatowaniu wynik ± niepewność.

UWAGA:  Uzupełnić  opracowanie  o  kolory  wyróżnienia  tekstu,  sprawdzić  indeksy,
pogrubienia,  itp.  Całe  opracowanie  Arkusz1  -  wszystkie  opisy,  tabele  i  wykres  -  powinny
zmieścić się na jednej stronie formatu A4.

9. Wstawianie na wykresie różnego typu słupków błędów



x i



Dla  wstawienia  słupków  błędów  należy  skopiować  wykres  na  Arkusz2.  W  tym  celu  na
Arkuszu1  zaznaczyć  wykres  i  wybrać  polecenie  Kopiuj.  Przełączyć  się  na  Arkusz2.
Trzykrotnie  wykonać  polecenie  Wklej,  ustawiając  kopie  wykresu  podobnie  jak  to  jest  na
stronie 5.Wpisać w Arkuszu2 do komórek w kolumnach A i B odpowiednie teksty i liczby -
tak jak to przedstawione jest na stronie 5.

Dla wszystkich typów błędów wskazane jest w pierwszym kroku wykonać następujące
czynności:
Zaznaczyć wykres. Na karcie kontekstowej Narzędzia wykresów wybrać Układ



Analiza



Słupki błędów



Słupki błędów z wartością procentową. Efektem będzie dodanie

słupków z domyślną wartością 5%.
Wstawianie słupków błędów procentowych o innych wartościach
Aby zmienić wartości błędów należy wykonać: karta kontekstowa Narzędzia wykresów



karta Układ



grupa Bieżące zaznaczenie



rozwinąć menu Elementy wykresu i wybrać

Serie1 Słupki błędów X



polecenie Formatuj zaznaczenie.

Otworzy się okno Formatowanie słupków błędów - Poziome słupki błędów. W polu
Wielkość błędu



Wartość procentowa wpisać liczbę 3. Ustawienia Wyświetl zostawić

domyślne: Kierunek Oba, Styl końca Zakończenie. Kliknąć Zamknij.
Aby zmienić wartości słupków błędów Y powrócić do grupy Bieżące zaznaczenie



rozwinąć menu Elementy wykresu i wybrać Serie1 Słupki błędów Y



polecenie

Formatuj zaznaczenie. W oknie Formatowanie słupków błędów - Pionowe słupki błędów
w polu Wielkość błędu



Wartość procentowa wpisać liczbę 10. Kliknąć Zamknij.

Wstawianie słupków błędów o stałej wartości
Dla drugiego wykresu należy wykonać: karta kontekstowa Narzędzia wykresów



karta

Układ



grupa Bieżące zaznaczenie



rozwinąć menu Elementy wykresu i wybrać Serie1

Słupki błędów X



polecenie Formatuj zaznaczenie.

Otworzy się okno Formatowanie słupków błędów - Poziome słupki błędów. W polu
Wielkość błędu



Stała wartość wpisać liczbę 0.2. Kliknąć Zamknij.

Aby zmienić wartości słupków błędów Y rozwinąć menu Elementy wykresu i wybrać Serie1
Słupki błędów Y



polecenie Formatuj zaznaczenie. W oknie Formatowanie słupków

błędów - Pionowe słupki błędów w polu Wielkość błędu



Stała wartość wpisać liczbę

10. Kliknąć Zamknij.
Wstawianie słupków błędów niestandardowych
Dla trzeciego wykresu należy wykonać: karta kontekstowa Narzędzia wykresów



karta

Układ



grupa Bieżące zaznaczenie



rozwinąć menu Elementy wykresu i wybrać Serie1

Słupki błędów X



polecenie Formatuj zaznaczenie.

Otworzy się okno Formatowanie słupków błędów - Poziome słupki błędów. W polu
Wielkość błędu



Niestandardowa



Określ wartość. Otworzy się okno Niestandardowe

słupki błędów



Dodatnia wartość błędu zaznaczyć zakres komórek





Ujemna

wartość błędu



zaznaczyć ten sam zakres komórek



x. Kliknąć Zamknij.

Aby zmienić wartości słupków błędów Y rozwinąć menu Elementy wykresu i wybrać Serie1
Słupki błędów Y



polecenie Formatuj zaznaczenie. W oknie Formatowanie słupków

błędów - Pionowe słupki błędów w polu Wielkość błędu



Niestandardowa



Określ

wartość. Otworzy się okno Niestandardowe słupki błędów



Dodatnia wartość błędu

zaznaczyć zakres komórek





Ujemna wartość błędu



zaznaczyć ten sam zakres

komórek



y. Kliknąć Zamknij.