MS Excel – charakterystyki jednowymiarowej zmiennej losowej

(1)

MS Excel – charakterystyki

jednowymiarowej zmiennej losowej

A. Miary położenia i pewne inne przydatne funkcje arkusza ... 2

A1. ŚREDNIA [w próbie/z próby] ... 2

A2. Inne średnie ... 2

A3. MEDIANA [w próbie]... 3

A4. KWARTYL [w próbie]... 4

A5. PERCENTYL [w próbie]... 5

A6. POZYCJA [ranga (elementu próby)] ... 6

A7. PROCENT.POZYCJA [prawdopodobieństwo nieprzekroczenia: mniej więcej to samo, co prawdopodobieństwo empiryczne z rys. 1] ... 6

A8. RANGA I PERCENTYL, narzędzie analizy ... 7

A9. WYST.NAJCZĘŚCIEJ [moda, dominanta (w próbie)] ... 8

B. Miary zmienności... 9

B1. ODCH.STANDARDOWE ... 9

B2. ODCH.STANDARD.POPUL ... 9

B3. ODCH.ŚREDNIE... 10

B4. WARIANCJA ... 10

B5. WARIANCJA.POPUL ... 10

B6. Inne miary... 11

C. Miary asymetrii (skośności) ... 12

C1. SKOŚNOŚĆ ... 12

D. Miary spłaszczenia... 13

D1. KURTOZA... 13

Uwaga: tekst czarny oznacza oryginalny tekst z tzw. helpów Excela (czasami dla zwrócenia uwagi zaznaczany na czerwono; np. kiedy dane sformułowanie jest dziwne, niejasne itp.), natomiast tekst dopisany przez mnie oznaczony jest kolorem niebieskim.

(2)

A. Miary położenia i pewne inne przydatne funkcje arkusza

A1. ŚREDNIA [w próbie/z próby]

Podaje wartość średnią (arytmetyczną) argumentów:

1

1 ⁿ

i i

x x

n ₌

=

∑

⁽¹⁾

Składnia

ŚREDNIA(liczba1;liczba2;...)

Liczba1; liczba2;... to od 1 do 30 argumentów liczbowych, dla których należy wyznaczyć średnią.

Uwagi

• Argumenty powinny stanowić liczby, nazwy, tablice lub adresy komórek zawierających liczby.

• Jeśli argument w postaci tablicy lub adresu zawiera tekst, wartości logiczne lub puste komórki, wartości te są zignorowane, jednakże komórki z wartością zerową są uwzględniane.

Przykłady

Jeśli zakres A1:A5 nazwano Wyniki i zawiera liczby 10, 7, 9, 27 i 2, to:

ŚREDNIA(A1:A5) jest równe 11 ŚREDNIA(Wyniki) jest równe 11 ŚREDNIA(A1:A5;5) jest równe 10

ŚREDNIA(A1:A5) jest równe SUMA(A1:A5)/ILE.LICZB(A1:A5) jest równe 11 Jeśli zakres C1:C3 nazwano InneWyniki i zawiera liczby 4, 18 i 7, to:

ŚREDNIA(Wyniki;InneWyniki) jest równe 10,5

A2. Inne średnie

ŚREDNIA.A nieprzydatna [dla nas]

ŚREDNIA.GEOMETRYCZNA rzadkie zastosowania ŚREDNIA.HARMONICZNA rzadkie zastosowania ŚREDNIA.WEWN rzadkie zastosowania

(3)

A3. MEDIANA [w próbie]

Podaje wartość mediany w próbie. Mediana w próbie jest liczbą w środku uporządkowanego (rosnąco lub mająco) zbioru liczb tzn., że połowa liczb ma wartości większe niż mediana i połowa ma wartości mniejsze.

Próba:

i xi

1 1 2 1.5 3 3 4 4 5 11.5 6 12

0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 1

0 1 2 3 4 5 6 7 8 9 10 11 12 13

x

prawd. empiryczne nieprzewyższenia

średnia = 5.5 mediana = 3.5

prawd. emp. nieprzewyższenia wg Excela

Rys. 1. Jak Excel liczy kwantyle

Składnia

MEDIANA(liczba1;liczba2;...)

Liczba1; liczba2;... to od 1 do 30 liczb [niekoniecznie], dla których należy wyznaczyć medianę.

Uwagi

Jeśli liczba danych liczbowych w zbiorze jest parzysta, to MEDIANA oblicza średnią [arytmetyczną] dwóch liczb środkowych.

Przykłady

MEDIANA(1;2;3;4;5) = 3

MEDIANA(1;2;3;4;5;6) = 3,5 (średnia [arytmetyczna] z 3 i 4)

(4)

A4. KWARTYL [w próbie]

Daje w wyniku kwartyl w próbie.

Próba:

i x_i

1 1

2 1.5

3 3

4 4

5 11.5 6 12

Kwartyle:

0 1

1 1.875 2 3.500 3 9.625

4 12 0

0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 1

0 1 2 3 4 5 6 7 8 9 10 11 12 13

x

kwartyl 1

kwartyl 3

Rys. 2. Kwartyle Składnia

KWARTYL(tablica;kwartyl)

tablica jest to tablica zawierająca badaną próbę losową.

kwartyl to rząd kwartyla, zob. tabela niżej.

Jeżeli kwartyl równa się KWARTYL daje w wyniku

0 wartość minimalną

1 pierwszy kwartyl (25. percentyl)

2 wartość mediany (50. percentyl)

3 trzeci kwartyl ( 75. percentyl)

4 wartość maksymalną

Uwagi

• Jeśli argument tablica jest pusty lub zawiera więcej niż 8191 punktów danych, funkcja KWARTYL daje w wyniku wartość błędu #LICZBA!.

• Jeśli argument kwartyl nie jest liczbą całkowitą, zostaje sprowadzony do najbliższej mniejszej liczby całkowitej.

• Jeśli argument kwartyl < 0 lub jeśli kwartyl > 4, funkcja KWARTYL daje w wyniku wartość błędu #LICZBA!.

(5)

• Funkcje MIN, MEDIANA i MAX dają w wyniku tę samą liczbę co funkcja KWARTYL, jeśli argument kwartyl jest równy odpowiednio 0, 2 i 4.

Przykład

KWARTYL({1;2;4;7;8;9;10;12};1) = 3,5

A5. PERCENTYL [w próbie]

Daje w wyniku (100*k)-ty percentyl (percentyl rzędu k) w próbie.

Próba:

i xi

1 1

2 1.5

3 3

4 4

5 11.5

6 12

Percentyle:

0 1.000 0.1 1.250 0.2 1.500 0.3 2.250 0.4 3.000 0.5 3.500 0.6 4.000 0.7 7.750 0.8 11.500 0.9 11.750 1.0 12

0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 1

0 1 2 3 4 5 6 7 8 9 10 11 12 13

x

30. percentyl (percentyl 30%)

70. percentyl (percentyl 70%)

Rys. 3. Percentyle 0, 10%, 20%, ..., 90%, 100%.

Składnia

PERCENTYL(tablica;k)

tablica

jest to tablica zawierająca próbę losową.

k

jest to rząd percentyla (prawdopodobieństwo nieprzekroczenia):

liczba od 0 do 1 włącznie.

Uwagi

• Jeżeli

tablica

jest pusta lub zawiera więcej niż 8191 punktów

danych, funkcja

PERCENTYL

daje w wyniku wartość błędu #LICZBA!.

• Jeżeli

k

jest argumentem nieliczbowym, funkcja

PERCENTYL

^{daje w} wyniku wartość błędu #ARG!.

• Jeżeli

k

jest < 0 lub jeśli

k

> 1, funkcja

PERCENTYL

daje w wyniku wartość błędu #LICZBA!.

• Jeżeli

k

nie jest wielokrotnością 1/(n - 1), funkcja

PERCENTYL

^stosuje interpolację w celu określenia wartości k-tego percentylu.

Przykład

PERCENTYL({1;2;3;4};0,3) = 1,9

(6)

A6. POZYCJA [ranga (elementu próby)]

Daje w wyniku pozycję [rangę] pewnej liczby na uporządkowanej rosnąco lub malejąco liście liczb.

Składnia

POZYCJA(liczba;lista;lp)

liczba jest to liczba, której pozycję [rangę] chcemy znaleźć.

lista jest to tablica listy liczb lub adres do niej. Nieliczbowe wartości argumentu lista będą ignorowane.

lp określa sposób uporządkowania listy: jeśli lp = 0 lub jest pominięte, to POZYCJA jest obliczana na podstawie

uporządkowania malejącego, jeśli lp ma dowolną wartość

niezerową POZYCJA jest obliczana na podstawie uporządkowania rosnącego.

Uwagi

Funkcja POZYCJA nadaje podwójnym liczbom taką samą pozycję. Obecność podwójnych liczb ma jednak wpływ na pozycje następnych liczb.

Przykładowo, na liście liczb całkowitych, jeżeli liczba 10 pojawia się

dwukrotnie i ma pozycję 5, wówczas liczba 11 będzie miała pozycję 7 (żadna liczba nie będzie miała pozycji 6).

Przykłady

Jeżeli A1:A5 zawiera odpowiednio liczby 7; 3,5; 3,5; 1 i 2, wówczas:

POZYCJA(A2;A1:A5;1) = 3 POZYCJA(A1;A1:A5;1) = 5

A7. PROCENT.POZYCJA [prawdopodobieństwo nieprzekroczenia: mniej więcej to samo, co prawdopodobieństwo empiryczne z rys. 1]

Daje w wyniku prawdopodobieństwo nieprzekroczenia zadanej wartości.

Składnia

PROCENT.POZYCJA(tablica;x;istotność)

tablica jest to tablica lub zakres danych z wartościami liczbowymi, który określa względną pozycję.

x jest to wartość, dla której chcemy znać pozycję.

istotność jest to opcjonalna wartość, która określa liczbę cyfr znaczących dla obliczanej wartości procentowej. Jeżeli argument ten jest pominięty, funkcja PROCENT.POZYCJA stosuje trzy cyfry (0,xxx%).

Uwagi

• Jeżeli tablica jest argumentem pustym, funkcja PROCENT.POZYCJA daje w wyniku wartość błędu #LICZBA!.

• Jeżeli istotność < 1, funkcja PROCENT.POZYCJA daje w wyniku wartość błędu #LICZBA!.

(7)

• Jeżeli x nie pasuje do jednej z wartości w argumencie tablica, funkcja PROCENT.POZYCJA realizuje interpolację, aby dać w wyniku właściwą pozycję procentową.

Przykład

PROCENT.POZYCJA({1;2;3;4;5;6;7;8;9;10};4) = 0,333

A8. RANGA I PERCENTYL, narzędzie analizy

Narzędzie analizy Ranga i percentyl tworzy tabelę jak poniżej tabela prawa (tabela lewa zawiera próbę losową).

i xi Punkt Kolumna1 Ranga Percentyl

1 1 6 12 1 100.0%

2 1.5 5 11.5 2 80.0%

3 3.5 3 3.5 3 40.0%

4 3.5 4 3.5 3 40.0%

5 11.5 2 1.5 5 20.0%

6 12 1 1 6 .0%

Zakres wejściowy

Podaj adres zakresu danych arkusza, który chcesz przeanalizować.

Grupowanie wg

Wskaż, czy dane w zakresie wejściowym są zorganizowane w wiersze, czy w kolumny, klikając odpowiednio przycisk Wierszy albo Kolumn.

Tytuły w pierwszym wierszu/Tytuły w pierwszej kolumnie

Nie wymaga objaśnień.

Zakres wyjściowy

(8)

Podaj adres lewej górnej komórki tabeli wyników. Tabela wyników zostanie utworzona dla każdego zbioru danych z zakresu wejściowego. Każda tabela wyników zawiera cztery kolumny: numer punktu, wartość punktu, rangę punktu i rangę procentową punktu. Punkty w tabeli uporządkowane są rosnąco.

A9. WYST.NAJCZĘŚCIEJ [moda, dominanta (w próbie)]

Podaje wartość najczęściej występującą w zakresie lub tablicy danych.

Podobnie jak MEDIANA, funkcja WYST.NAJCZĘŚCIEJ jest miarą położenia Składnia

WYST.NAJCZĘŚCIEJ(liczba1;liczba2;...)

Liczba1; liczba2;... to od 1 do 30 argumentów, dla których należy wyznaczyć wartość modalną. Zamiast listy argumentów rozdzielonych przecinkami można wykorzystać także pojedynczą tablicę lub jej adres.

Uwagi

• Argumentami powinny być liczby lub nazwy, tablice lub adresy zawierające liczby.

• Jeśli argument w postaci tablicy lub adresu zawiera tekst, wartości logiczne lub puste komórki, wartości te zostaną pominięte, jednakże komórki zawierające wartości zerowe zostaną wzięte pod uwagę.

• Jeśli zbiór danych nie zawiera danych powtarzających się, funkcja WYST.NAJCZĘŚCIEJ podaje wartość błędu #N/D!.

Wartość modalna to wartość najczęściej powtarzająca się, mediana jest wartością środkową, a średnia oznacza wartość uśrednioną. Żadna z miar centralnych nie przedstawia kompletnego obrazu danych.

Przykład

WYST.NAJCZĘŚCIEJ({5,6; 4; 4; 3; 2; 4}) = 4

(9)

B. Miary zmienności

B1. ODCH.STANDARDOWE

Oblicza odchylenie standardowe w próbie, sn-1, wg wzoru:

2 1

1

1 (

1

n n

i

s x

− n

=

= −

∑

ⁱ⁻^x⁾ ⁽²⁾

Składnia

ODCH.STANDARDOWE(liczba1;liczba2;...)

Liczba1;liczba2; ... to od 1 do 30 wartości odpowiadających próbce populacji. Można stosować pojedynczą tablicę lub adres tablicy zamiast argumentów rozdzielonych średnikami.

Przykład

Załóżmy, że mamy 10 narzędzi wykonanych na tej samej maszynie w jednym cyklu produkcji, wziętych jako przypadkowa próbka. Dla narzędzi tych

zmierzono wytrzymałość na pękanie. Wartości próbki (1345; 1301; 1368;

1322; 1310; 1370; 1318; 1350; 1303; 1299) są zapisane odpowiednio w komórkach A2:E3. Funkcja ODCH.STANDARDOWE ocenia standardowe odchylenie wytrzymałości wszystkich tych narzędzi na pękanie.

ODCH.STANDARDOWE(A2:E3) = 27,46

B2. ODCH.STANDARD.POPUL

Funkcja ta oblicza odchylenie standardowe, sn, za pomocą wzoru:

2 1

1 ⁿ ( )

n i

i

s x

n ₌

=

∑

−^x ⁽³⁾

Składnia

ODCH.STANDARD.POPUL(liczba1;liczba2;...)

Liczba1;liczba2;... to od 1 do 30 wartości odpowiadających populacji.

Można stosować pojedynczą tablicę lub adres tablicy zamiast argumentów rozdzielonych średnikami.

Przykład

Stosując te same dane z przykładu ODCH.STANDARDOWE i zakładając, że w cyklu produkcyjnym wytwarza się tylko 10 narzędzi, funkcja

ODCH.STANDARD.POPUL mierzy standardowe odchylenie wytrzymałości na pękanie dla wszystkich narzędzi.

ODCH.STANDARD.POPUL(A2:E3) = 26,05

(10)

B3. ODCH.ŚREDNIE

ODCH.ŚREDNIE jest zdefiniowane następująco:

1

1 ⁿ |

śr i

i

s x

n ₌ x|

=

∑

− ⁽⁴⁾

Składnia

ODCH.ŚREDNIE(liczba1;liczba2;...)

Liczba1; liczba2;... są argumentami, dla których należy wyznaczyć średnią odchyleń bezwzględnych. Zamiast argumentów rozdzielanych średnikami, możliwe jest użycie pojedynczej tablicy bądź adresu tablicy.

Przykład

ODCH.ŚREDNIE(4;5;6;7;5;4;3) = 1,020408

B4. WARIANCJA

WARIANCJA, s_n²₋₁, jest zdefiniowana wzorem:

2 1

1

1 (

1

n

n i

i

s x

− n

=

= −

∑

⁻^x⁾² ⁽⁵⁾

Składnia

WARIANCJA(liczba1;liczba2;...)

Liczba1;liczba2;... to od 1 do 30 argumentów liczbowych odpowiadających próbce z populacji.

Przykład

Przyjmijmy, że 10 narzędzi wykonanych na tej samej maszynie w jednym cyklu produkcji zostało zebranych jako przypadkowa próbka, na której przeprowadzono pomiar wytrzymałości na złamanie. Wartości tej próbki (1345; 1301; 1368; 1322; 1310; 1370; 1318; 1350; 1303; 1299) są zapisane odpowiednio w A2:E3. Funkcja WARIANCJA ocenia wariancję wytrzymałości tych narzędzi na złamanie.

WARIANCJA(A2:E3) = 754,3

B5. WARIANCJA.POPUL

WARIANCJA.POPUL, , jest zdefiniowana wzorem: s_n²

2

1

1 ⁿ (

n i

i

s x

n ₌

=

∑

−^x⁾² ⁽⁶⁾

Składnia

WARIANCJA.POPUL(liczba1;liczba2;...)

Liczba1;liczba2;... to od 1 do 30 wartości odpowiadających populacji.

(11)

B6. Inne miary

ODCH.STANDARD.POPUL.A nieprzydatne [dla nas]

ODCH.STANDARDOWE.A nieprzydatne [dla nas]

ODCH.KWADRATOWE może być przydatne, nie jest to zresztą żadne odchylenie, tylko SUMA KWADRATÓW ODCHYLEŃ

WARIANCJA.POPUL.A nieprzydatna [dla nas]

WARIANCJA.A nieprzydatna [dla nas]

(12)

C. Miary asymetrii (skośności) C1. SKOŚNOŚĆ

Skośność charakteryzuje stopień asymetrii rozkładu względem jego średniej.

Skośność dodatnia oznacza rozkład z asymetrycznym ogonem rozciągającym się w kierunku do wartości dodatnich. Skośność ujemna oznacza rozkład z asymetrycznym ogonem rozciągającym się w kierunku wartości ujemnych.

SKOŚNOŚĆ, , dokładniej: współczynnik skośności lub współczynnik asymetrii, jest zdefiniowana wzorem:

ˆS

C

3 1

( )

ˆ ( 1)( 2)

n i i S

n

n x x

C n n s

=

−

= − −

∑

(7)

Współczynnik skośności w próbie, , jest estymatorem (tzn. oceną) współczynnika skośności, C

ˆS

C

S, zmiennej losowej X:

3 3/ 2

E( E ) ˆ

(var )

S

X X

C X

−

CS

= ≈ (8)

Składnia

SKOŚNOŚĆ(liczba1;liczba2;...)

Liczba1, liczba2;... są to argumenty od 1 do 30, dla których chcemy obliczyć skośność. Można również zastosować pojedynczą tablicę lub adres do tablicy zamiast argumentów rozdzielonych przecinkami.

Przykład

SKOŚNOŚĆ(3;4;5;2;3;4;5;6;4;7) = 0,359543

(13)

D. Miary spłaszczenia D1. KURTOZA

Daje w wyniku kurtozę zbioru danych. Kurtoza charakteryzuje względną szczytowość lub płaskość rozkładu w porównaniu z rozkładem normalnym.

Dodatnia kurtoza oznacza rozkład o stosunkowo dużej szczytowości. Ujemna kurtoza oznacza rozkład stosunkowo płaski.

KURTOZA, , dokładniej: współczynnik spłaszczenia w próbie lub współczynnik ekscesu w próbie, jest zdefiniowana skomplikowanym wzorem:

ˆe

C

4

2 1

4 1

( )

( 1) 3( 1)

ˆ ( 1)( 2)( 3) ( 2)( 3)

n i e i

n

x x

n n n

C n n n s n n

=

−

+ − −

= − − − − −

∑

− (9)

Współczynnik spłaszczenia w próbie, , jest estymatorem (tzn. oceną) współczynnika spłaszczenia, C

ˆe

C

e, zmiennej losowej X:

4 2

E( E ) 3 ˆ (var )

e

X X

C X

−

Ce

= − ≈ (10)

Składnia

KURTOZA(liczba1;liczba2;...)

Liczba1; liczba2;... są to argumenty od 1 do 30, dla których chcemy obliczyć kurtozę. Można również zastosować pojedynczą tablicę lub adres tablicy zamiast argumentów przedzielonych przecinkami.

Przykład

KURTOZA(3;4;5;2;3;4;5;6;4;7) = -0,1518