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.