Microsoft Excel 2000

- 200 -

Wykorzystanie funkcji statystycznych

Excel

posiada

bogaty

zestaw

wbudowanych

funkcji

statystycznych. Ponadto, je

żeli został zainstalowany dodatek Analysis

ToolPak, to poprzez menu Narz

ędzia | Analiza danych uzyskamy

dost

ęp do zestawu narzędzi do analizy statystycznej:

Prze

ćwiczenie możliwości Excela w zakresie statystyki znacznie

przekracza zakres tej ksi

ążki. Poniżej zostanie pokazane tylko kilka

przykładów zastosowania wybranych funkcji.

Zadanie 52.

Sporz

ądzić wykres funkcji gęstości rozkładu normalnego o

parametrach:

średnia = 0, odch. standardowe = 1, dla argumentów z

przedziału [-5, 5] z krokiem 0,1. Na wykresie umie

ścić dwa elementy

steruj

ące (pokrętła) do zmiany wartości średniej i odchylenia

standardowego. Powinna by

ć możliwa zmiana średniej w zakresie od -

5 do 5 co 0,1, za

ś odchylenia w zakresie od 0,1 do 3 co 0,1.

Microsoft Excel 2000

- 201 -

Rozwi

ązanie.

Przygotowujemy arkusz jak na rysunku poni

żej

W komórkach A2:A102 umieszczamy dowolnym znanym sposobem
seri

ę danych od -5 do 5 z krokiem 0,1. W komórce B2 wpisujemy

formuł

ę:

=ROZKŁAD.NORMALNY(A2;$D$1;$D$2;FAŁSZ).

Funkcja ROZKŁAD.NORMALNY odwołuje si

ę do komórek D1 i

D2, w których umieszczone s

ą odpowiednio średnia i odchylenie

standardowe. Ostatni parametr FAŁSZ. decyduje o tym,

że wykresem

b

ędzie funkcja gęstości. (jeśli parametr ten jest równy PRAWDA to

wykresem jest dystrybuanta). Zawarto

ść komórki D1 to formuła

=F1/10+-5, a komórki D2 formuła =F2/10. Komórki F1 i F2
zawieraj

ą wartości generowane przez pokrętła, które umieszczamy w

arkuszu pobieraj

ąc je z paska narzędziowego Formularze lub

Przybornik Formantów . Nie mo

żemy bezpośrednio pobierać

warto

ści ustawionej przez pokrętło jako parametru rozkładu, ponieważ

pokr

ętło działa w zakresie liczb naturalnych, a w zadaniu potrzebne są

warto

ści ujemne i ułamkowe.

Microsoft Excel 2000

- 202 -

Zmieniaj

ąc wartości średniej i odchylenia standardowego w zakresie

dopuszczalnych warto

ści przez wykorzystywanie pokręteł możemy na

bie

żąco śledzić kształt krzywej gęstości rozkładu normalnego.

Uwaga!

Warto sformatowa

ć oś Y wykresu tak, aby skala na niej była stała, a

nie zmienna w zale

żności od aktualnych wartości odchylenia

standardowego.

Zadanie 53.

Sporz

ądzić wykres funkcji gęstości rozkładu Gamma o parametrach

alfa = 1, beta = 1 dla argumentów z przedziału [0, 10] z krokiem 0,1.
Na wykresie umie

ścić dwa elementy sterujące (pokrętła) do zmiany

warto

ści parametrów. Powinna być możliwa zmiana parametru alfa i

beta w zakresie od -0,1 do 5 co 0,1. (Wykorzysta

ć funkcję

ROZKŁAD.GAMMA)

Jako rozwi

ązanie powinno się uzyskać arkusz o wyglądzie podobnym

do poni

ższego:

Microsoft Excel 2000

- 203 -

Zadanie 54.

Zakładaj

ąc, że średnia wzrostu jest równa 176 cm zaś odchylenie

standardowe 4 cm wygenerowa

ć wzrost 100 ludzi (liczby całkowite),

przy zało

żeniu że rozkład jest Normalny. Znaleźć ilości osób, których

wzrost wpada w nast

ępujące przedziały:

mniej ni

ż 164 cm, (164, 168], (168, 172], (172, 176], (176, 180], (180,

184], (184, 188], wi

ęcej niż 188 cm.

Sporz

ądzić wykres słupkowy obrazujący częstość w poszczególnych

przedziałach

(Wykorzysta

ć funkcję arkusza ROZKŁAD.NORMALNY.ODW i

funkcj

ę CZĘSTOŚĆ). Wykorzystując klawisz F9 wymuszający

przeliczanie arkusza obserwowa

ć jak zmienia się liczba osób, których

wzrost wpada w poszczególne przedziały.

Rozwi

ązanie

Na rysunku poni

żej widać arkusz zawierający rozwiązanie zadania.

Microsoft Excel 2000

- 204 -

Zwró

ćmy uwagę na formułę widoczną na pasku formuły (złożenie

trzech funkcji arkusza. LOS() - bezargumentowej funkcji zwracaj

ącej

liczb

ę losową z rozkładu równomiernego z odcinka (0, 1],

ROZKŁAD.NORMALNY.ODW,

b

ędącej realizacją funkcji

odwrotnej do dystrybuanty rozkładu normalnego, oraz funkcji
ZAOKR pozwalaj

ącej zaokrąglić jej pierwszy argument do ilości

miejsc dziesi

ętnych określonych przez drugi argument. Formuła ta jest

skopiowana do zakresu A1:J10.

Druga formuła widoczna jako komentarz do komórki B12 jest formuł

ą

tablicow

ą wpisaną do całego zakresu B12:B19 (zaznaczamy obszar

B12:B19, wpisujemy formuł

ę, naciskamy Ctrl + Shift + ENTER).

Pierwszy argument funkcji oznacza zakres badanych liczb, a drugi
zakres, do którego zostały wpisane górne granice rozpatrywanych
przedziałów.

Zadanie 55.

Poziom zatrudnienia w 41 badanych zakładach przedstawia si

ę

nast

ępująco:

Zbudowa

ć szereg rozdzielczy obrazujący liczbę zakładów z

poziomem zatrudnienia w nast

ępujących przedziałach: 1-9, 10-19, 20-

39, 40-99, 100-199, 200-399, 400-599. Sporz

ądzić wykres złożony

kolumnowo-liniowy przedstawiaj

ący częstość w postaci kolumn, a

ł

ączny udział procentowy kolejnych przedziałów w całkowitej liczbie

zakładów w postaci linii. Na osi X powinny by

ć pokazane granice

przedziałów.

Microsoft Excel 2000

- 205 -

Uwaga!

1. Wpisz do arkusza w jednej kolumnie dane o poziomie

zatrudnienia, a w dowolnej innej górne granice wskazanych
przedziałów

2. W kolumnie s

ąsiedniej do granic wpisz formułę tablicową

zawieraj

ąca funkcję CZĘSTOŚĆ.

Ostatecznie arkusz i wykres powinny by

ć podobne do poniższego.

Uwaga!

Powy

ższe rozwiązanie można również uzyskać wybierając

Histogram z listy narz

ędzi Analysis ToolPak.

Zadanie 56.

Dla danych z Zadania 36 obliczy

ć:

a) Współczynniki a i b definiuj

ące linię trendu

b) Warto

ść przewidywanych kosztów dla produkcji w wysokości

200 szt.

Microsoft Excel 2000

- 206 -

Rozwi

ązanie.

Skorzystamy z funkcji statystycznych REGLINP oraz REGLINX.
Pierwsza z wymienionych funkcji w najprostszym przypadku (gdy
pominiemy dwa ostatnie jej argumenty) zwraca współczynniki a i b.
Nale

ży ją wpisać jako formułę tablicową. Dobierając odpowiednio

parametry funkcji mo

żna uzyskać również statystyki regresji (zob.

opis funkcji w Pomocy). Druga funkcja wylicza warto

ść leżącą na

linii trendu bezpo

średnio z danych początkowych.