1

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

i y

i

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

i

y

i

)



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

2

(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

2

UWAGA: większość czynności związanych z formatowaniem elementów wykresu można
wykonać po kliknięciu na wykresie



wyświetli się karta kontekstowa Narzędzia wykresów



karta Formatowanie



grupa Bieżące zaznaczenie



rozwinąć menu Elementy wykresu

wyświetlane na górze



wybrać obiekt do formatowania



następnie polecenie Formatuj

zaznaczenie i zrobić formatowanie.

3

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



5

- 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

i

;

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

i

;

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

a

- niepewność współczynnika a, r

2

- kwadrat

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

b

- niepewność wyrazu wolnego b, s

y

- średnie

odchylenie współrzędnych y

i

od wartości teoretycznych y

it

, 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

k,



, 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

*t

k,



=, a następnie formułę do obliczenia



a

- wpisać tekst



b= s

b

*t

k,



=, a następnie formułę do obliczenia



b

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.

4

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



x i



y

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



x



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



y



Ujemna wartość błędu



zaznaczyć ten sam zakres

komórek



y. Kliknąć Zamknij.

5

UWAGA: Po wstawieniu błędów zmienić odpowiednio skale X i Y wykresów tak, żeby
wszystkie elementy wykresów były widoczne. Dopasować rozmiary wykresów tak, żeby
Arkusz2 mieścił się na stronie formatu A4. Wszystkie wykresy na tym arkuszu powinny mieć
takie same rozmiary.

6

Powiększenia trudno czytelnych fragmentów instrukcji:

7