107,23 102,05 95,47
87,98 107,77 95,73 102,54 89,07 109,17 84,26 117,30 97,00 123,96 114,06 98,39 100,62 97,54
108,15 107,62 111,14 120,81 95,11 106,76 91,63 108,03 108,14 113,42 91,35 100,27 102,89 91,55
83,73
93,70 119,10 93,60 106,41 108,06 102,76 89,28 103,38 99,44 108,57 92,73 91,62
PRO ZPRACOVÁNÍ A ANALÝZU DAT 88,52 107,81 74,08 85,47 92,51 94,23 87,37 109,44 97,52
107,51 110,78 97,77 103,43 113,07 118,80 114,90 110,41 96,95 84,25 106,11 102,52 98,26 100,58 95,26
91,35 104,01 110,17 102,64 104,50 100,91 80,39
110,96 95,30 104,17 112,68 91,53 115,67 106,98 107,29 94,16 120,47 98,31
96,02
92,14 106,97 92,42
Příručka
97,56
105,00 88,25
96,79
94,57
85,42 103,73 115,97 101,40 76,87 112,80 106,06 116,87 91,70
97,26 125,45 100,05 103,93 108,02 99,07
90,24
92,80
80,25
87,48
97,20
99,01 115,19 106,70 87,77
98,40
79,25 102,33 104,37 103,42 119,40 107,07 97,05
98,04 101,78 94,14 117,49 91,19
97,62
98,12
89,55
86,91 126,11 109,27 105,35 108,31 97,76
92,96
86,35
105,08 110,71 120,48 87,88 106,44 112,62 91,77
92,11
80,85 115,28 96,67
99,81 111,03 104,99 94,79 103,90 89,90 113,11 106,16 105,17 97,37 95,26 122,73 80,81
96,65 108,10 93,15
87,96
F-test 86,09 102,91 99,84 114,80 123,42 86,43
99,29
83,30 104,65 118,27 87,00
102,88 80,39
90,32 110,67 104,16 78,91 107,79 90,45
86,21
Soubor91,14 1 Soubor 2 84,67 113,28 96,22 109,11 127,48 97,57 118,79 90,98 102,83 96,46 106,26 87,21 100,42 95,07 100,03 100,87 117,38 107,65 106,25
117,06 113,40 97,16 107,91 104,91 101,02 109,86 103,64 108,06 104,62 118,79 90,90
85,76 119,19 90,38
90,07 108,81
hodnota 81,94 99,4911173 92,27stř. 116,61 87,5697,6875634 98,73 93,54 102,67 103,60 116,91 99,78
rozptyl 122,617713 98,31 124,96 102,23 101,21 92,47119,575281 98,06 109,11 101,36 103,18 96,98
96,70
104,81 92,34
pozorování 39 40 91,65 112,62 120,26 98,00 99,02 110,80 82,49 108,95 89,81 105,28 114,04 99,47 104,45 105,55 107,27 100,66 88,63 102,48 101,75
85,48
109,08 98,21
93,00
89,45
81,31 102,48 92,94
93,74
96,49
97,89
97,13
99,94 100,28 99,12
98,25
96,14
95,89
96,39 118,03 97,87 115,63 108,40 91,90
105,61 82,82
(1) 98,60 103,15 105,01 106,08 97,2510103,32 92,76 112,34 86,44 101,57 98,68P(F<=f) 83,12
97,27
99,81
98,12
94,71 103,64 94,45
107,51 faktor 96,57
92,29
98,73
98,34 109,64 113,09 108,11 110,55 91,35
5
92,22 87,99 součet 94,15 107,39 95,07 109,46 112,00S596,45 výběr 109,38 počet průměr 89,53 rozptyl 94,46 S3 91,76 85,12 sloupec 100,151 103,38 106,20 89,20 115,28 88,36 103,53 103,35 39 3880, 154 115,29 99,49112 96,77 122,6177 sloupec 3
108,02 80,23
95,36
93,77 115,89 98,24
F krit (1)
94,93
39 3795, 477 97,31993 117,1737 088,88 91,20 109,71 95,93 90,89
1,70873449
97,38 104,92 200 91,27 104,05 92,73 14128,95
160
96,67
91,67
150 99,24 87,94 103,13
86,80 101,23 91,95
90,15
88,47 102,39 103,92 106,21 114,05 112,09
100
100 90,82 103,35 110,18 108,73 107,89 87,60 119,86 98,40 101,89 99,01 80
93,08 100,34 91,29
109,67 122,12 1113,16 86,88 40 98,23
116
99,44
96,23
99,72 106,55 101,07 103,82 103,75 98,94
100
40
95,88 102,16 78,68 20
84,08 105,21 111,78
92,62 102,55 102,22 106,76 110,48 50
40
89,64 110,10 102,86 105,34 87,17
98,48
95,31
95,06 115,29 104,51 109,21 98,48 0
79,42 104,46 99,73 100,51 96,13
60 111,40 99,23 109,62 81,50 113,96 88,94 113,73 115,63 106,03 104,93 114,65 97,94
98,71
98,59 102,79 84,05
60 83,38 116,15 111,58 102,26 110,77 114,89 105,64 106,72 105,20 89,93 102,11 100,29 103,47 106,34
20 93,84 1101,85 111,34 88,87 115,52 90,34 115,63 110,88 96,66
112,07 93,08 102,59 96,35 80
99,28
Četnost
240
114 111,46 122,8364 99,81 108,95 všechny 112,43výběry 101,1214003,35 100,28 107,64 220
77,34
99,08 101,67
85,47 113,80 91,68 107,22 88,86 105,43 90,10
89,01
86,35 105,75 98,60 118,33
104,65 82,52 103,22 111,89 115,61 90,50 110,25 95,83
82,17
93,28
114,14 86,29
90,36
95,20 104,16 107,50
92,28
90,40
98,47 112,37 102,21 109,59 103,55 98,22
93,95
Popisná statistika 102,36 110,26 98,9687 86,56 81,10 1,8055 110,51 100,07
99,6059 97,74 116,10 S1 98,27 92,38 99,44 100,71 1 2 #N/A 79,41 94,89 102,23 115,04 102,02 93,01 109,04 109,61 103,42 104,58 119,19 89,01 103,08 101,83 106,27 3 4 114,38 250 93,45 101,45 98,49 114,22 98,26 98,64 63,69 102,44 391,06 98,71 89,55 modus 5 97,12 118,10 83,57 108,55 112,71 103,05 97,15 108,23 95,06 101,56 102,90 107,48 98,87 109,68 94,75 89,64 103,88 111,66 91,47 290,62 104,97 96,19 102,81 109,16 96,24 111,36 sm. odchylka 11,4188 119,10 39 3882, 206 99,54374 128,7177
200 92,25 116,26 100,14 101,37 107,99 108,67 92,47 93,16 102,85 108,91
85,46
88,52
1 112,65 103,29 103,40 111,61 83,66 87,70 88,50 stř. hodnota 91,16 96,73 86,88 98,73 111,65 83,34 106,01 99,60 112,88 99,50 117,43 90,77 101,92 121,00 103,43 100,82 hodnoty 96,92 Plot 89,29 107,45 101,18 107,96 102,05 105,98 102,67 98,99 99,06 92,40 89,71 Norm 104,97al Probability 92,07 chyba 89,29 stř. 109,32 medián 92,62 106,27 97,58 102,39Histogram 102,57 97,51 102,61 92,02 83,54 105,26 4104,04 95,00 105,21 102,35 107,12 107,48
88,89 106,95 111,54 96,04 118,23 121,42 119,02 101,86 97,47
88,43
79,26
107,29 83,17 118,59 110,00 120,34 97,45
0, 3401632 92,84 102,95 95,15 118,17 sloupec 89,15 2 78,99 89,09
112,28 Zdroj 98,40variace 99,73 SS 91,84 rozdíl 93,91 101,66 93,25 MS 128,34 F 103,95 hodnota95,73 P F krit 92,01 mezi 108,20 117,12 96,962Pareto 108,65 106,78 89,74 97,63 výběry87,30125,6075 62,80374 92,82 0,51128 0,601096 3,075854
180
93,36
1 96,91 117,99 sloupec 104,611 126,26 110,17 113,25 91,57 105,19 101,20 103,33 106,22 90,05
108,92 ANOV 108,24 77,29 108,00 98,45 106,59 107,81 80,26 113,65 116,96 89,87 A
celkem
91,95
Box Whisker Plot 89,13 118,67 100,94 99,78and 106,37 90,02
0,46857906 sloupec85,89 1 sloupec 2 97,22 89,07 100,93 102,94 117,28 109,20 96,04 105,99 112,00 83,86
99,88 102,61 96,32
91,92 100,98 91,54 106,31 103,30 94,22 104,82 98,72 114,85 96,56
97,90
107,04 sloupec 90,012 99,80
99,30
38 88,45 Funkce 99,54 dvou 99,92 prom 87,92ěnných 87,41 113,50 99,06rozdíl 111,07 91,55 103,50 91,3939 106,91 111,21 98,06 100,20 117,89 113,69 87,15 116,58 102,72 D281,13 100,20 D1 D3 86,27 95,11 1,02544365 Korelace 79,07 99,70F 105,86 91,25 110,27 91,35 116,56 91,55 112,68 107,08 111,50 97,46 107,74 101,49 86,03 X 99,19 102,18 112,02 102,78
92,69
83,41 107,89 98,27
Anova: jeden faktor
99,05 116,64 130 101,99 96,19 120 95,79 98,48 110 108,00 107,33 100 97,37 100,69 90 91,89 92,55 80 109,40 102,62 70
20
91,18
96,87 115,68 105,21 89,47 109,32 102,34 83,88 111,68 86,21
108,94 111,22 105,03 97,55 110,03 111,12 95,86 101,37 92,71 114,74 76,65 0 0 99,02
94,40
94,27 103,14 102,54 103,85 89,13
87,48
98,78 108,70 100,10 100,02 85,77
96,98
111,48 108,64 106,80 101,09 112,62 102,75 95,43
94,74
74,89 196,04 91,16 91,70 rozptyl 91,09 výběru 87,50 85,21 115,52 091,37 106,38 97,14 101,69 špičatost 90,27 89,10 110,29 96,93 113,49-1104,98 100,20 92,53 112,64 90,60 šikmost 103,97 108,49 92,86 115,11 105,23 113,48 103,53 102,58 100,81 -2 rozdíl max-min 98,42 65,74 92,42 102,01 96,94 92,64 95,42 85,25 101,81 -3 minimum 98,02 87,25 122,75 108,49 82,66 94,05 98,49 107,36 85,24 -4 maximum 97,11 98,30 87,80 84,08 103,23 87,14 89,05 94,03 110,86 70 90 110 93,72 117,01 93,98 106,94 87,24 93,70 106,49 součet 71,01 102,30
Value
80,17 120,72 91,94 107,52 102,69 111,52 91,67 102,91 92,05
90,95 102,90 90,14 119,70 88,40 Hodnota
91,83 114,29 90,27
98,77 117,49 96,55 102,25 90,53 109,16 98,35
90,92 116,85 87,53 106,20 94,99
95,26
97,05 105,33 87,78
98,22 108,47 100,49 107,82 103,25 99,24 126,97 108,86 70,87 108,52 85,58
98,22 102,01 82,72 101,30 100,73 103,65 104,71 93,80 84,13
96,27 114,72
82,16 102,52 76,90 107,13 99,52
Multiple of
84,60
92,25
94,51 102,58 117,19 99,78
85,28 106,34 113,68 122,55 103,54 98,04 111,12 104,69 113,47 114,04
Y
89,87
97,55
93,16
97,63 114,27 100,72 104,89 97,05
96,10 106,81 94,22
94,29 109,97 113,29 115,35 112,27 106,07 88,71 100,03 108,49 99,11 89,93 103,23 87,62
81,27
89,82 106,19 106,31 95,09 106,52 115,32 111,57 117,19 104,80 99,60
112,88 107,53 112,30 98,73 92,51
99,61
98,19 107,35 120,75 107,83 104,10 99,75 102,16 108,18 110,18 105,15 102,99 90,19 107,46 132,63 108,58 102,56 110,75 125,28 109,88 124,87 114,39
88,80 108,89 100,42 109,28 101,65 92,35
100,92 110,27 87,83 102,16 114,95 100,10 98,13
80,40
96,05 115,86 102,08 98,25 101,97 109,35 90,47 112,61 111,37
90,06 103,72 103,67 96,94 107,78 117,66 117,82 97,63POUŽITÍ 92,71 95,33 105,63 PROGRAMU 103,63 97,63 89,05 112,04 102,85 89,20 101,27 110,27 107,35 87,27 123,87 107,78 88,19 104,92 KURZ PRAKTICKÉHO Microsoft EXCEL 87,43 103,72 99,67 98,04 77,89 112,71 98,60 97,92 106,56 100,98 88,75 108,64 107,78 94,27 93,12 100,18 111,19 85,48 97,81 106,23 96,86 83,82 101,65 105,79
104,74 84,73 100,68 109,03 93,54 94,64
90,43
90,95 103,67 103,29 97,74 110,06 95,00 101,02 104,44 93,68
86,43
94,26
85,85 111,54 102,50 89,46
98,70
98,41
95,52
89,10
počet 40,0000 110,74 96,69 119,09 93,15 100,21 128,52
98,16
80,68
3,6519 94,91 97,86
94,97 106,60 94,23 115,20 102,98 96,61 104,60 85,95 104,59 109,97
2.2 114,98 94,09 81,68 103,06 110,55 103,02 94,99
věrohodnost (95,0%) 97,55 107,28 99,53
99,60 104,33 96,87 101,82 101,44 98,20 104,35 108,81 102,41
95,85 114,74 109,26 101,69 93,01 102,65 101,70 99,07 89,29
97,96 112,44 130,3900115,95 106,95 97,56 -0,5087 96,83 90,67 106,97 83,82 -0,0137 101,49 111,37 107,30 46,1525 98,76 103,47 102,30 78,5949 110,82 117,91 104,14 124,7474 104,94 91,01 92,82 130 86,38 3958,7484 124,12 95,66
99,12 117,23 83,62
82,36 103,53 87,79
91,55
99,91
98,54 107,74 102,83 105,63 114,90
76,19 111,89 93,73
95,52
88,48
96,88
88,62
Úvod Microsoft, Visual Basic a Windows jsou registrované ochranné známky společnosti Microsoft Corporation. Tato příručka, a soubory na CD "Štatistické kurzy" jsou určeny pouze pro účastníky kurzu. Jiné použití je možné po dohodě s firmou VPS s.r.o. .
V úvodě každého listu je navozen problém, který se týká vzorových dat a pak nástin řešení. Obvykle v levé části listu je popsán postup použití příslušného nástroje a v pravé části další možnosti použití, anebo interpretace výsledků (z prostorových důvodů může být toto rozdělení na některých listech jiné).
Použití symbolů:
1 Příručka byla připravena pro potřeby kurzu praktického použití programu Microsoft® EXCEL pro zpracování a analýzu dat. Příručka není učebnicí ani uživatelským manuálem programu Microsoft ® EXCEL. Kurz je koncipován pro EXCEL 2002. Většina zpracovaných témat je platná také pro nižší verze EXCELu. V příručce jsou stručně a většinou názorným způsobem popsaná jednotlivá témata probírána v kurzu, má tedy sloužit účastníkům kurzu pro rychlé připomenutí postupů použití některých nástrojů EXCELu, nebo interpretace jejich výsledků. Každé téma je zpracováno na jednom listu. V patě listu jsou informační údaje. Je zde odkaz na vzorová data a také na soubor uživatelských funkcí, pokud si jej popisované téma vyžaduje. Pata listu Úvod slouží pouze k vysvětlení významu informací.
2 !
pořadová čísla kroků postupu důležitá upozornění
Použití písma: Arial
Text, vzorce
Arial, Bold
Názvy nástrojů, nabídek, dialog. oken, funkcí a pod.
Courier New
Hodnoty v textu
Probírána témata jsou rozdělena do tří typů:
" informace, vysvětlení, teorie, nastavení # standardní nástroje EXCELu ! nástroje vyžadující instalaci souboru *.XLA nebo speciální přípravu
Tato příručka byla připravena pomocí programu Microsof t® EXCEL 2002 pod operačním systémem WINDOWS 98.
!
Značka pro typ listu / tématu Zde je uveden název souboru, pokud popsaný nástroj vyžaduje jeho instalaci. Zde je uvedeno číslo listu, kde je návod jako popsaný nástroj připravit (pokud není standardním nástrojem). Název listu / tématu
!
UFU.XLA (16)
Krabicový diagram
Pata tohoto listu slouží jako příklad
! Čísla listů se souvisejícími tématy
V případě, že je pro nástroj / téma běžně používaný anglický název, je uveden zde.
(Box and Whisker Plot)
Číslo listu / tématu
Název souboru se vzorovými daty, v závorkách je název příslušného listu.
KURZ1.XLS (Krab. dia.)
(7, 9)
1
Přepočet automaticky/ručně
Užitečná nastavení Problém:
Řešení:
Zejména na pomalejších počítačích mohou některé akce EXCELu probíhat neúnosně dlouho.
Rychlost startu EXCELu se dá výrazně ovlivnit počtem otevřených doplňků. Při použití velkého množství funkcí nebo rozsáhlých vstupních dat je vhodné nastavit manuální ovládání výpočtů.
Přes nabídku Tools - Options otevřete dialogové okno Options - Calculation a označte Calculation - Manual. Přepočet listu proběhne až po stisknutí klávesy F9. Nastavení se ukládá se sešitem. Po startu nového sešitu EXCELu je vždy nastaveno Calculation - Automatic.
1
Otevření (zavření) doplnků (Add-Ins) Přes nabídku Tools - Add-Ins otevřete dialogové okno Add-Ins. V okénkách zaškrtněte jen ty doplňky, které opravdu potřebujete k práci. Zbytečně otevřené doplňky zpomalují start EXCELu a také zabírají operační paměť počítače. Toto nastavení můžete kdykoli změnit.
1
2
!
Užitečná nastavení
(Add-Ins, Calculation)
2
2
Automatické řady a seznamy - přetažením v listu 1 2 3 4 5 6 7 8 9 10 11 12 13
A
B
C
120 130 140 150 160 170 180 190 200
pondělí úterý středa čtvrtek pátek sobota neděle
D
Problém:
Další možnosti
Časté vytváření různých seznamů bývá zdlouhavé.
EXCEL se při těchto operacích chová inteligentně. Z hodnoty teplota1 dostanete řadu teplota1, teplota2, ... . Příklady jsou na obrázcích.
Řešení: EXCEL umí automaticky vytvářet seznamy podle prvního členu, nebo aritmetickou řadu podle prvních dvou členů.
Pokud nechcete vytvářet automatický seznam, ale pouze kopírovat, podržte při tažení myší stisknuté tlačítko Ctrl (objeví se malé znaménko +).
1
2
Vytvoření automatického seznamu v listu 1
Podobně můžete vytvářet řady a seznamy také v řádcích
2
3
Napište první dva členy aritmetické řady a označte je. Pak umístěte kurzor myši na značku v pravém dolním rohu označené plochy, až se objeví křížek. Stiskněte levé tlačítko myši a táhněte směrem dolů. Po uvolnění tlačítka myši bude označena oblast vyplněna členy aritmetické řady.
Ikona v dolní části řady nabízí menu s dalšími možnostmi pro vytvořenou řadu.
3
Podobně vytvoříte i jiné řady například názvy dnů týdne, nebo názvy měsíců. V těchto případech stačí napsat první člen řady. Pokud se seznam nevytvoří, doplňte jej do rejstříku seznamů (list 4).
!
Automatické řady a seznamy - přetažením v listu
(4)
3
2
Přidání seznamu do rejstříku seznamů A 1 2 3 4 5 6 7 8 9 10 11 12 13
B helium neon argon krypton xenon radon
C
Problém: Časté vytváření různých seznamů které nejsou v EXCELu standardní.
Řešení: Přidejte potřebné seznamy do rejstříku seznamů.
Postup Napište seznam na libovolné místo v listu a označte jej. Pak přes nabídku Tools - Options otevřete dialogové okno Options. V něm zvolte záložku Custom Lists. Kliknutím na tlačítko Import se seznam z listu přenese do rejstříku seznamů.
3 1
Nový seznam nyní můžete používat standardním způsobem.
!
Přidání seznamu do rejstříku seznamů (Custom List)
4
Zvolte Type - Growth a zaškrtněte Trend (v tom případě parametr Step value nemá vliv).
Automatické řady a seznamy - nástrojem Series A
B
1 1,2 2 3 1,8 4 2,7 5 4,05 6 6,075 7 9,1125 8 13,6688 9 20,5031 10 30,7547 11 46,132 12 69,198 13
C 12.9.02 13.9.02 14.9.02 15.9.02 16.9.02 17.9.02 18.9.02 19.9.02 20.9.02 21.9.02 22.9.02
3
D
Problém: Vytvořit geometrickou řadu s prvními dvěma členy 1,2 a 1,8 . Vytvořit seznam pracovních dnů od 12.9.02
Řešení: Pomocí nástroje Series můžete vytvářet aritmetické, geometrické a kalendářní řady.
Vytvoření kalendářní řady Vytvoření geometrické řady
Napište první datum řady, pak označte oblast, která má být vyplněná, včetně prvního data. Přes nabídku Edit - Fill - Series otevřete dialogové okno Series. Zvolte Type - Date, Date unit Weekday, Step value 1.
Napište první dva členy řady, pak označte oblast, která má být vyplněná, včetně prvních členů. Přes nabídku Edit - Fill - Series otevřete dialogové okno Series.
1
2
2
1
Pozor, Date unit Weekday v tomto případě znamená vyplnění oblasti pouze pracovními dny, ne krok jeden týden. Ostatní jednotky znamenají velikost kroku. ! Pozor, při tomto postupu vytváření řad se zkopírují také formáty prvních buněk.
!
Automatické řady a seznamy - nástrojem Řady (Series)
!
5
Pravidla
Databáze a tabulky A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
B
C
Reaktor A DATUM TYP 12.8.02 MC78L 13.8.02 MC78L 13.8.02 MA78 15.8.02 MA78 16.8.02 MA78 16.8.02 TL431 18.8.02 TL431 19.8.02 MA78 19.8.02 MA78
R 3,40 3,35 3,25 3,00 3,15 3,30 3,30 2,80 3,40
Reaktor B DATUM TYP 17.8.02 TL431 18.8.02 TL431 18.8.02 TL431 18.8.02 TL431 18.8.02 MC78M 18.8.02 MC78M 18.8.02 MC78M 18.8.02 MC78M
R 2,80 2,90 3,20 3,55 3,20 3,35 3,55 3,50
D
E
F
G
H
Databáze DATUM 12.8.02 13.8.02 13.8.02 15.8.02 16.8.02 16.8.02 17.8.02 18.8.02 18.8.02 18.8.02 18.8.02 18.8.02 18.8.02 18.8.02 18.8.02 19.8.02 19.8.02
TYP MC78L MC78L MA78 MA78 MA78 TL431 TL431 TL431 TL431 TL431 TL431 MC78M MC78M MC78M MC78M MA78 MA78
R 3,40 3,35 3,25 3,00 3,15 3,30 2,80 3,30 2,90 3,20 3,55 3,20 3,35 3,55 3,50 2,80 3,40
REAKT A A A A A A B A B B B B B B B A A
Problém:
I
Uspořádat data co nejuniverzálnějším způsobem.
Řešení:
Existují jisté nesrovnanosti v pojmech TABULKA (TABLE), SEZNAM (LIST) a DATABÁZE (DATABASE). V nápovědě EXCELu nejsou někdy rozlišeny. V této příručce budeme používat pojmy TABULKA a DATABÁZE v tom smyslu, jako byly uvedeny zde, SEZNAM pak bude mít význam podle listů Automatické řady a seznamy - ... . !
1 2 3 4 5 6 7 8 9 10 11 12 13
A
B
C
D
DATUM 12.8.02 13.8.02 13.8.02 15.8.02 16.8.02 16.8.02 17.8.02 18.8.02 18.8.02 18.8.02 18.8.02
TYP MC78L MC78L MA78 MA78 MA78 TL431 TL431 TL431 TL431 TL431 TL431
R 3,40 3,35 3,25 3,00 3,15 3,30 2,80 3,30 2,90 3,20 3,55
REAKT A A A A A A B A B B B
Záhlaví (header) databáze Záznamy (records) databáze Databázi vytvářejte vždy ve svislém směru. Tedy nahoře záhlaví, pod ním záznamy. Pokud máte na jednom listu více databází, umístěte je pod sebe. Dbejte na to, aby databáze a tabulky byly vždy ohraničeny prázdnými buňkami. Používejte záhlaví.
sloupec - Pole (field) databáze
Při dodržení těchto pravidel vám budou dobře sloužit nástroje EXCELu určené na zpracování databází, jako jsou Sort, Filter, Form, Subtotal, Pivot Table atd. . Pro práci s databázemi a tabulkami je užitečné přidat do panelu nástrojů ikonu nástroje Výběr oblasti. Pomoci této ikony vyberete rychle celou oblast tabulky nebo databáze.
Vytvořit databázi.
Na obrázku, ve sloupcích A, B, C, jsou dvě tabulky. Jedna pro reaktor A druhá pro reaktor B. Hodnoty z tabulek můžete použít pro vyhodnocení reaktoru A nebo B.
Přes nabídku View - Toolbars z nabítky vyberte Customize. Získáte dialogové okno Customize. Označte záložku Commands. Zvolte Edit a pak z plochy Commands přesuňte ikonu Select Current Region na panel nástrojů.
Pro komplexní analýzu je výhodnější uspořádání dat tak, jako ve sloupcích E, F, G, H. Zde jsou stejná data, ale je přidaný další sloupec REAKT, ve kterém je uvedeno zda data pocházejí z reaktoru A anebo B. Z takto uspořádaných dat je možné získat ty samé výsledky, jako z předchozích tabulek, navíc, je možné získat další informace. Ve sloupcích E, F, G, H je vytvořena DATABÁZE (DATBASE). Vytváření databází je výhodné, protože z databáze lehko vytvoříte libovolné tabulky (naopak je to obtížné). Z databází je možné získat i další informace, které v separovaných tabulkách zanikají. Pokud tedy chcete řešit specifický problém a data jsou získána pouze pro tento účel, vytvářejte specifické tabulky. Ale pokud chcete data analyzovat, vytvořte databázi.
!
Databáze a tabulky
KURZ1.XLS (Dbaze)
6
Další možnosti
Automatický filtr (AutoFilter) A 1 DATUM 2 2.9.02 3 2.9.02 4 3.9.02 5 3.9.02 6 3.9.02 7 4.9.02 8 4.9.02 9 4.9.02 10 5.9.02 11 5.9.02 12 5.9.02 13 5.9.02 14 6.9.02 15 6.9.02 16 7.9.02 17 7.9.02 18 8.9.02 19 8.9.02 20 8.9.02 21 8.9.02 22 9.9.02 23 9.9.02 24 9.9.02
TYP MA78 MA78 MA78 MA78 TL431 TL431 MA78 MA78 MC79 MC79 MC79 MC79 MA78 MA78 TL431 MC79 MC79 MA78 MA78 MA78 MA78 MA78 MA78
C
D
T 15,00 15,33 14,60 14,88 14,98 14,90 15,08 14,90 14,80 14,83 14,98 14,88 15,10 15,03 14,65 15,55 15,43 15,18 15,13 15,48 15,30 15,25 15,18
R 3,60 3,25 3,30 3,45 3,30 3,30 3,25 3,35 3,15 3,30 3,35 3,35 3,55 3,35 2,90 3,35 3,25 3,35 3,35 3,30 3,50 3,55 3,35
1 Problém:
Automatický filtr můžete zrušit opět přes nabídku Data Filter - Autofilter.
Z databáze vybrat pouze řádky s typem MC79. Volba položky Show All zruší všechny podmínky filtrace, ale nezruší filtr.
Řešení: Použití automatického filtru umožňuje rychle skrýt řádky nevyhovující zadané podmínce. Zruší všechny podmínky v daném sloupci
Spuštění automatického filtru Označte libovolnou buňku uvnitř databáze. Pak přes nabídku Data - Filter - AutoFilter zapněte režim filtrování. V nejvyšším řádku databáze, nebo v záhlaví se objeví tlačítka. Klikněte na tlačítko ve sloupci Typ. Otevře se nabídka pro podmínky filtrace. Zvolte hodnotu MC79. Filtr skryje všechny řádky, ve kterých hodnoty buňek nevyhovují podmínce. Podobně můžete přidávat podmínky do dalších sloupců.
Pokud chcete zobrazit data v určitém intervalu, nastavte podmínky filtrace v dialogovém okně Custom....
2
Otevře dialogové okno vlastních podmínek
Volba operátora
Volba hodnoty
3
1
Podmínka pro dolní hranici intervalu
2
Podmínka pro horní hranici intervalu
3 4
Pokud před spuštěním filtru označíte nějakou oblast databáze (více jako jednu buňku), bude filtr působit pouze na řádky označené oblasti a níže. ! Automatický filtr skryje celé řádky, tedy i buňky mimo oblast databáze.
"
Automatický filtr
(AutoFilter)
KURZ1.XLS (Aut. filt.)
(6, 9)
!
7
2
Rozšířený filtr (Advanced Filter) A B C 1 TYP Odpor 2 MC79 =(C5+D5)/2>3,3 3 4 DATUM TYP R1 5 2.9.02 MA78 3,40 6 2.9.02 MA78 3,30 7 3.9.02 MA78 3,40 8 3.9.02 MA78 3,50 9 3.9.02 TL431 3,30 10 4.9.02 TL431 3,40 11 4.9.02 MA78 3,20 12 4.9.02 MA78 3,40 13 5.9.02 MC79 3,30 14 5.9.02 MC79 3,40 15 5.9.02 MC79 3,50 16 5.9.02 MC79 3,50 17 6.9.02 MA78 3,60 18 6.9.02 MA78 3,40 19 7.9.02 TL431 3,00 MC79 3,60 20 7.9.02 21 8.9.02 MC79 3,10 22 8.9.02 MA78 3,50 MA78 3,70 23 9.9.02 24 9.9.02 3,40
D
R2 3,80 3,20 3,20 3,40 3,30 3,20 3,30 3,30 3,00 3,20 3,20 3,20 3,50 3,30 2,80 3,10 3,40 3,20 3,30 3,30
Filtrace zkopírováním hodnot - extrakce
Filtrace skrytím řádků Oblast databáze včetně záhlaví databáze
E Při použití rozšířeného filtru je vhodné ponechat nad databází několik volných řádků (3 a více) pro umístění podmínek filtrace.
Oblast podmínek včetně záhlaví podmínek Oblast do které bude umístěn výsledek (pouze v případě extrakce)
!
Při použití rozšířeného filtru musí mít databáze i oblast podmínek záhlaví
Výsledek v oblasti F5:I8
3
!
Problém: Z databáze vybrat pouze řádky s typem MC79 a průměrem R1 a R2 větším 3,3.
Řešení: Rozšířený filtr EXCELu umožňuje použití složitých podmínek obsahujících matematické funkce. Umožňuje také provést filtraci skrytím řádek, jako automatický filtr, nebo překopírování dat vyhovujících podmínce na jiné místo listu.
Další možnosti Porovnávací podmínka Záhlaví stejné jako záhlaví příslušného sloupce databáze
1 2 3
A
B
TYP MC79
Odpor
C
=(C5+D5)/2>3,3
Mezi hodnotami v jednom řádku platí operátor AND
Spuštění rozšířeného filtru Napište podmínky filtrace (v příkladu buňky A1:B2). Označte libovolnou buňku uvnitř databáze, pak přes nabídku Data - Filter - Advanced Filter otevřete dialogové okno.
1
K
Vzorec musí obsahovat aspoň jeden odkaz na buňku databáze (C5, D5). Hodnota, nebo hodnota s logickým operátorem. Podmínka <>MC79 by zobrazila typy jiné jako MC79.
Mezi hodnotami v různých řádcích platí operátor OR
D E F 1 DATUM TYP 2 4.9.02 MC79 3 Typ MC79 dne 4.9.02 J 1 DATUM 2 <4.9.02 3 >7.9.02
D
Odkazovací podmínka Záhlaví musí být odlišné od záhlaví databáze.
L
G DATUM 7.9.02
H
TYP Zobrazí všechny typy dne 4.9.02, v ostatních dnech pouze typ MC79. MC79
M
DATUM DATUM Zobrazí dny 4.9.02 až 7.9.02 >=4.9.02 <=7.9.02 Vyloučí dny 4.9.02 až 7.9.02
Filtr v módu filtrace skrytím řádků skryje celé řádky, tedy i buňky mimo oblast databáze.
"
Rozšířený filtr
(Advanced Filter)
KURZ1.XLS (R. filt.)
(6,7, 9)
!
8
Graf s filtrovanými daty
Práce s filtrovanými daty A B C 1 DATUM TYP R 2 4.9.02 TL431 3,20 3 4.9.02 MA78 3,15 4 4.9.02 MA78 3,35 5 5.9.02 MC79 3,15 6 5.9.02 MC79 3,30 7 5.9.02 MC79 3,35 8 5.9.02 MC79 3,35 9 3,26 10 =AVERAGE(C2:C8) 11 12 13 14 15
D
Problém:
1
Chcete zobrazit pouze den 4.9.02 a vypočítat průměr ze sloupce R také pouze pro tento den. Při aplikaci automatického filtru na data bude skryta i buňka C9 se vzorcem, protože ji filtr považuje za data nevyhovující podmínce. V případě, že oddělíte buňku se vzorcem od dat prázdným řádkem, buňka se vzorcem skryta nebude, ale protože funkce AVERAGE počítá i se skrytými buňkami, výsledek nebude závislý na podmínce filtru.
Pokud chcete, aby graf zobrazoval pouze hodnoty, které jsou výsledkem filtrace, skontrolujte označení políčka Plot visible cells only v záložce Chart. Označte graf a přes nabídku Tools - Options otevřete dialogové okno Options. Objeví se záložka Chart. Políčko Plot visible cells only musí být označené.
Automatický filtr
2
Řešení:
A 12 DATUM 13 4.9.02 14 4.9.02 15 4.9.02 16 5.9.02 17 5.9.02 18 5.9.02 19 5.9.02 20 21 22 23 24 25 26 27
!
B
C
TYP TL431 MA78 MA78 MC79 MC79 MC79 MC79
R 3,20 3,15 3,35 3,15 3,30 3,35 3,35 3,26 3,00 5,00 8,00 4,00 7,00 19,00
=SUBTOTAL(1;C13:C19)
SUBTOTAL(funkce, odkaz) je funkce umožňující provádět výpočty s filtrovanými daty. Ignoruje všechny skryté buňky a buňky s funkcí SUBTOTAL.
Chybí průměr
SUBTOTAL(funkce, odkaz): Funkce je číslo od 1 do 11, které určuje typ funkce použité pro výpočet. Například 1 znamená průměr, 9 součet (nápověda funkce).
3
Funkce SUBTOTAL v buňce C20 bude počítat pouze s hodnotami zobrazenými filtrem. Funkce SUBTOTAL v buňce C27 ignoruje hodnotu 8 v buňce C24, protože je výsledkem jiné funkce SUBTOTAL.
=SUBTOTAL(9;C22:C23) =SUBTOTAL(9;C22:C26) Nástrojem Kopírovat - Vložit přenesete pouze filtrem zobrazená data!
Práce s filtrovanými daty
!
Toto nastavení platí pro každý graf individuálně!
KURZ1.XLS (Filt. data)
! (7, 8)
9
Souhrn (Subtotal)
Formuláře a souhrny (Form, Subtotal) A 1 DATUM 2 4.9.02 3 4.9.02 4 4.9.02 5 6.9.02 6 6.9.02 7 7.9.02 8 7.9.02 9 8.9.02 10 8.9.02 11 8.9.02 12 8.9.02 13
B
C
D
TYP TL431 MA78 MA78 MA78 MA78 TL431 MC79 MC79 MA78 MA78 MA78
T 14,90 15,08 14,90 15,10 15,03 14,65 15,55 15,43 15,18 15,13 15,48
R 3,30 3,25 3,35 3,55 3,35 2,90 3,35 3,25 3,35 3,35 3,30
Označte některou buňku databáze. Přes nabídku Data - Subtotals otevřete okno Subtotal.
Problém: Zjednodušit zadávání nebo úpravy dat. Rychle vypočítat průměry v jednotlivých dnech.
2
1
Vyberte sloupec, podle kterého se má řídit vytváření souhrnů.
Řešení: Nástroj Form slouží ke zjednodušení úprav databází. Nástroj Subtotals vytvoří potřebné souhrnné výpočty.
Vyberte funkci pro souhrn.
Označte sloupce, do kterých mají být souhrny přidané.
Označte některou buňku databáze. Přes nabídku Data - Form vytvoříte formulář pro editaci databáze. Pomocí formuláře můžete měnit údaje v jednotlivých záznamech, přidávat záznamy, nebo je odstraňovat.
Odstraní všechny souhrny.
2 1 3 Ovládací lišta přehledů Pomocí ovládacích prvků přehledů můžete skrývat nebo zobrazovat části tabulky. Pokud chcete vyhledat konkrétní záznam, klikněte na tlačítko Criteria, zapište hodnotu kterou chcete vyhledat (nebo také porovnávací operátor >3,3 a pod.) a klikněte na tlačítko Find Prev anebo Find Next.
!
Formuláře a souhrny
(Form, Subtotal)
KURZ1.XLS (Form)
10
4
Kontingenční tabulka (Pivot Table) A 1 DATUM 2 6.9.02 3 6.9.02 4 6.9.02 5 6.9.02 6 6.9.02 7 7.9.02 8 7.9.02 9 8.9.02 10 8.9.02 11 8.9.02 12 9.9.02 13 9.9.02 14 9.9.02 15 9.9.02 16 9.9.02 17 9.9.02 18 6.9.02 19 6.9.02 20 6.9.02 21 7.9.02 22 7.9.02 23 7.9.02 24 7.9.02 25 9.9.02 26 9.9.02 27
B
C
D
TYP MA78 MA78 MA78 MA78 MA78 MC79 MC79 MC79 MA78 MA78 MA78 MA78 MA78 MA78 MA78 MA78 MA78 MA78 MA78 MC79 MC79 MC79 MC79 MA78 MA78
REAKT A A A A A A A A A A A A A A B B B B B B B B B B B
R 3,55 3,45 3,35 3,25 3,35 3,20 3,35 3,25 3,35 3,30 3,50 3,30 3,55 3,35 3,35 3,20 3,30 3,30 3,45 3,30 3,25 3,15 3,20 3,15 3,50
Kontingenční tabulka může být vytvořena na novém listu.
Problém: Sestrojit tabulku přehledu zpracování typů v jednotlivých dnech.
Řešení: Nástroj Pivot Table umožňuje vytvářet z databází křížové tabulky. Označte některou buňku databáze. Přes nabídku Data - Pivot Table otevřete okno PivotTable and PivotChart Wizard.
Kontingenční tabulka
5
1 Pro každý typ bude vytvořen sloupec. Pro každé datum bude vytvořen řádek.
2 Ikonu přeneste pomocí myši
6
3
!
Klikněte na tlačítko Layout a vyberte oblast, ve které se má označený výběr zobrazit nebo na tlačítko Finish a pak vyberte oblasti zobrazení výběrů v otevřeném okně Pivot Table Field List.
(Pivot Table)
7
Zde můžete vybrat reaktor, kterého data mají být zobrazena.
KURZ1.XLS (Kont. tab.)
11
Použití ovládacích prvků A 1 DATUM 2 2.9.02 3 2.9.02 4 3.9.02 5 3.9.02 6 3.9.02 7 4.9.02 8 4.9.02 9 4.9.02 10 5.9.02 11 5.9.02 12 5.9.02 13 5.9.02 14 6.9.02 15 6.9.02 16 7.9.02 17 7.9.02 18 8.9.02 19 8.9.02 20 8.9.02 21 8.9.02 22 9.9.02 23 9.9.02 24 9.9.02
B
C
D
TYP MA78 MA78 MA78 MA78 TL431 TL431 MA78 MA78 MC79 MC79 MC79 MC79 MA78 MA78 TL431 MC79 MC79 MA78 MA78 MA78 MA78 MA78 MA78
T 15,00 15,33 14,60 14,88 14,98 14,90 15,08 14,90 14,80 14,83 14,98 14,88 15,10 15,03 14,65 15,55 15,43 15,18 15,13 15,48 15,30 15,25 15,18
R 3,60 3,25 3,30 3,45 3,30 3,30 3,25 3,35 3,15 3,30 3,35 3,35 3,55 3,35 2,90 3,35 3,25 3,35 3,35 3,30 3,50 3,55 3,35
Problém:
Označte buňku F2. Tažením za značku v pravém dolním rohu směrem vpravo zkopírujte vloženou funkci i do buněk G2 - I2. Upravte formát buňky F2 na "Date". Změnou hodnoty v buňce I4 se v poli G2 - I2 zobrazí odpovídající záznam.
3
1 Rychle zobrazit záznam databáze podle aktuální požadavky.
4
5 6
Řešení: Pomocí funkce OFFSET vytvořte odkaz na databázi, který umožní vyhledat a v definované oblasti zobrazit určený počet řádků a sloupců z této databáze.
=OFFSET(A1;$I$4;0;1;1)
Postup Do buňky I4 napište číslo udávající počet řádku, o kolik se posune začátek zobrazení hledaného záznamu od začátku databáze. Označte a zkopírujte hlavičku databáze a vložte ji do buňky F1. Do buňky F2 vložte funkci OFFSET(v kategorii Lookup & Reference) a zadejte vstupní hodnoty podle obrázku.
Pro snadnější ovládání této funkce vložte ovládací prvek Scroll Bar z panelu nástrojů Forms do listu. Přes pravou myš umístěnou na tomto prvku vyberte z nabídky Format Control a v dialogovém okně zadejte hodnoty podle obrázku.
7
1
8 2 3
!
Použití ovládacích prvků
(Controls)
KURZ1.XLS (Ovl. pr.)
12
Databázové funkce EXCELu A 1 DATUM 2 2.9.02 3 2.9.02 4 3.9.02 5 3.9.02 6 3.9.02 7 4.9.02 8 4.9.02 9 4.9.02 10 5.9.02 11 5.9.02 12 5.9.02 13 5.9.02 14 6.9.02 15 6.9.02 16 7.9.02 17 7.9.02 18 8.9.02 19 8.9.02 20 8.9.02 21 8.9.02 22 9.9.02 23 9.9.02 24 9.9.02
B
C
D
TYP MA78 MA78 MA78 MA78 TL431 TL431 MA78 MA78 MC79 MC79 MC79 MC79 MA78 MA78 TL431 MC79 MC79 MA78 MA78 MA78 MA78 MA78 MA78
T 15,00 15,33 14,60 14,88 14,98 14,90 15,08 14,90 14,80 14,83 14,98 14,88 15,10 15,03 14,65 15,55 15,43 15,18 15,13 15,48 15,30 15,25 15,18
R 3,60 3,25 3,30 3,45 3,30 3,30 3,25 3,35 3,15 3,30 3,35 3,35 3,55 3,35 2,90 3,35 3,25 3,35 3,35 3,30 3,50 3,55 3,35
3
Problém: Získat průměr hodnot T pro typ TL431 z celé databáze.
Řešení: Funkce DAVERAGE umožňuje výpočet průměru pouze hodnot vybraných z databáze podle kritérii specifikovaných v podmínce výběru.
Postup Do buněk F1 a F2 vložte podmínku výběru z databáze. Do buňky H4 vložte funkci DAVERAGE (v kategorii Database) a zadejte vstupní hodnoty podle obrázku.
1
Podmínka výběru z databáze
4
2
=DAVERAGE(A1:D24;"T";F1:F2)
Podmínky výběru pro databázové funkce se tvorí stejným způsobem jako pro rozšířený filtr (Advanced Filter) (list 8).
Stejný postup lze použít pro všechny funkce z kategorie Database.
!
Databázové funkce EXCELu
(Database Functions)
KURZ1.XLS (Dbaze fun.)
(8)
!
13
Hledání závislostí (Auditing)
Výpočty a závislosti (Calculations, Auditing)
V některých případech jsou výpočty v listu tak složité, že ztrácíte přehled. Jindy se může vyskytnout chyba. Přehled v závislostech mezi buňkami získáte pomocí nástroje Formula Auditing. Označte buňku o níž chcete zjistit, kde všude její hodnota vstupuje. Pak přes nabídku Tools -Formula Auditing - Trace Dependents získáte znázornění závislostí přímo v listu.
Přesnost výpočtů 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
A
B
C
T1 944,6 946,5 890,7 894,6 880,7 880,6 945,5 940,4 950,4 946,7 934,2 931,4 890,8 894,3 881,0
T2 944,5 946,8 890,7 894,6 880,8 880,8 945,4 940,5 950,3 946,6 933,7 930,6 890,7 894,8 881,0
Rozp. 0,30 0,00 0,00 0,10 0,20 0,10 0,10 0,10 0,10 0,50 0,80 0,10 0,50 0,00
D 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
E
F
Hodn. 0,1
Počet 0
=SUM(D2:D16) =IF(C2=$E$2;1;0) =ABS(A2-B2)
Problém: Sčítat, kolik rozpětí 0,1 se nachází ve sloupci Rozp. Standardní postup vede k výsledku, že hledaná rozpětí neexistují. Výsledek v buňce F2 je 0, i když ve sloupci Rozp. je sedm rozpětí 0,1.
Příčina: Roztáhněte sloupec C tak, aby do něj vešlo alespoň 15 desetinných míst, pak nastavte počet zobrazených desetinných míst na 15. Je vidět, že vypočtená rozpětí nejsou přesné hodnoty, ale jsou zatížená malou chybou, která vzniká při výpočtu v pohyblivé řádové čárce, nicméně při porovnávání s přesnou hodnotou je zjištěna nerovnost hodnot.
Řešení: Jednou z možností je vhodné zaokrouhlení výpočtů pomocí funkce ROUND(číslo; číslice). Další možností je nastavení Precision as displayed.
V oblasti C2:C16 nastavte číselný formát 0,00. Přes nabídku Tools - Options otevřete dialogové okno Options - Calculation.
1
2
3
Nastavení Precision as displayed platí pro celý sešit a ukládá se s ním. Nastavení je aktuální pouze pro buňky s číselným formátem, který má pevný počet desetinných míst. !
!
Výpočty a závislosti
2
1
(Calculations, Auditing)
Když chcete získat představu o tom, které hodnoty vstupují do výpočtu, označte buňku se vzorcem a zvolte Trace Precedents.
Pokud v nekteré buňce vznikne výpočtová chyba, označte ji a zvolte Trace Error. Získáte znázornění postupu výpočtu a šíření chyby.
!
Všechny šipky zrušíte volbou Remove All Arrows.
KURZ1.XLS (Vypocet)
(2)
14
Funkce a pole 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
A 81,45 114,27 96,33 90,29 107,69 94,62 100,42 90,4 101,17 B A 104,76 89,17 87,89
B 95,54 101,64 88,62 88,13 108,48 86,28 90,35 108,88 86,32 114 94,49 87,43 89,3 105,87
C 114,2 110,07 113,62 106,84
79,18 124,11 86,85 127,87 94,16 99,38 113,75 98,59
D
E 84 88 92 96 100 104 108 112 116
2
Problém: Připravit data pro sestrojení histogramu. Hranice tříd jsou v sloupci E.
3 Řešení:
4
Umístit kurzor do vstupního řádku
Funkce FREQUENCY provede rozdělení dat do zadaných tříd. Její výstupem je pole.
Funkce FREQUENCY vrátí pole v rozsahu jeden sloupec a počtem rádků rovným počtu hranic tříd + 1. V první třídě jsou spočteny všechny hodnoty od - nekonečna po první hranici. V poslední třídě pak hodnoty od poslední hranice po nekonečno. Výstupem funkce FREQUENCY - v tomto příkladu - bude tedy vertikální 10 prvkové pole.
Postup
Označit výstupní oblast Podobně se vkládají i další funkce jejichž výstupem jsou pole.
Označte buňku ve které má být první četnost. Vložte funkci FREQUENCY (v kategorii Statistical). Zadejte vstupní hodnoty podle obrázku. Oblast vstupních hodnot
V buňce G2 je nyní vložena funkce. Označte oblast výstupního pole G2:G11, pak stiskněte F2 (nebo umístěte kurzor do vstupního řádku) a pak kombinaci kláves Ctrl - Shift - Enter. Označená oblast pak bude vyplněna výstupními hodnotami funkce.
Oblast hranic tříd
1
Pole jsou také vstupem funkce FREQUENCY. Ve vstupních polích dat jsou ignorovány nečíselné hodnoty. Během označování výstupního pole sledujte okénko v levém horním rohu sešitu, kde je zobrazen rozměr označené oblasti. Nemusíte pak počítat buňky.
Rozměr označené oblasti. R znamená řádky (Rows) C znamená sloupce (Columns)
!
Funkce a pole
KURZ1.XLS (F. pole)
15
2
Datová tabulka (Table) A
B
P= FH =
105 270
h=
16,78
C
D
3
Problém:
kW l/min
Pro tloušťku vrstvy byla experimentem zjištěna rovnice h=14,38+0,064*P-0,016*FH , kde h je tloušťka v µ, P je výkon v kW a FH je průtok vodíku v l/min. Je třeba sestavit tabulku tloušťky, pro výkon P v rozsahu 105 až 115 kW a průtok vodíku FH od 260 do 380 l/min.
µ
=14,38+0,064*B1-0,016*B2
Řešení: Sestrojte datovou tabulku se dvěma vstupy.
Sestrojení datové tabulky Podle obrázku sestrojte záhlaví tabulky a levý sloupec. Vzorec musí být umístěn v levém horním rohu tabulky a nesmí být navázán na žádnou buňku budoucí tabulky. Pak označte oblast B5:I16 a přes nabídku Data - Table otevřete dialogové okno Table. Odkaz pro Row input cell bude B2 a pro Column input cell bude B1.
"
B
P= FH =
105 270
h=
16,78 105 106 107 108 109 110 111 112 113 114 115
C
D
kW l/min
260
E
F
G
H
I 1
=14,38+0,064*B1-0,016*B2 vzorec v levém horním rohu tabulky Průtok H2 [l/min] 300 320 340
280
360
5 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
Datová tabulka
B
P= FH =
105 270
h=
16,78 105 106 107 108 109 110 111 112 113 114 115
C
D
E
F
G
H
I
360 15,34 15,40 15,47 15,53 15,60 15,66 15,72 15,79 15,85 15,92 15,98
380 15,02 15,08 15,15 15,21 15,28 15,34 15,40 15,47 15,53 15,60 15,66
kW l/min
260 16,94 17,00 17,07 17,13 17,20 17,26 17,32 17,39 17,45 17,52 17,58
280 16,62 16,68 16,75 16,81 16,88 16,94 17,00 17,07 17,13 17,20 17,26
Průtok H2 [l/min] 300 320 340 16,30 15,98 15,66 16,36 16,04 15,72 16,43 16,11 15,79 16,49 16,17 15,85 16,56 16,24 15,92 16,62 16,30 15,98 16,68 16,36 16,04 16,75 16,43 16,11 16,81 16,49 16,17 16,88 16,56 16,24 16,94 16,62 16,30
Další možnosti 5 6 7 8 9 10 11 12 13 14 15
(Table)
A
380
Hodnoty pro průtok vodíku Hodnoty pro výkon
A
Výkon [kW]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
4
Výkon [kW]
1 2 3 4 5 6 7 8 9
L
M
N
Úhel 0 10 20 30 40 50 60 70 80 90
Sin 0
cos 1
O =COS(RADIANS(L6))
=SIN(RADIANS(L6))
Můžete vytvořit také datovou tabulku s jedním vstupem, ale pro více vzorců. Označte oblast L6:N15, přes nabídku Data - Table otevřte okno Table a zadejte L6 jako Column input cell.
KURZ1.XLS (Dat. tab.)
16
Spuštění makra
Makra 1 2 3 4 5 6 7 8 9 10 11 12
A A 0,44584 C 0,97729 E
A B C D E
B 0,34214 B 0,49217 D 0,41337
C
0,55531 0,18882 0,75652 0,9613 0,37165
D A B C D E
E
F 0,66274 Problém: 0,59844 Ve všech oblastech uspořádat 0,71783 čísla a písmena tak, jako 0,68654 v oblasti A1:B5. 0,97461
A B C D E
0,50395 Nástroj Makro slouží 0,34608 k automatizování opakované 0,50295 činnosti. 0,46086 0,12815
Nyní, když máte připravený nástroj, můžete jej aplikovat na každou oblast. Označte buňku v levém horním rohu oblasti (A8). Přes nabídku Tools - Macro otevřete dialogové okno Macro. Zvolte makro Uspořádat a klikněte na tlačítko Run.
1
Řešení:
2
Záznam makra Označte buňku E1. Přes nabídku Tools - Macro - Record New Macro... otevřete dialogové okno Record Macro. Napište Macro name: - Uspořádat a klikněte na OK. Objeví se tlačítka pro ukončení makra a pro použití Relative Reference. Klikněte na tlačítko Relative Reference a upravte buňky v oblasti E1:F5 podle vzoru v A1:B5. Záznam makra ukončete kliknutím na tlačítko Stop Recording.
2
3
!
Makra
4
Při použití relativních odkazů se činnost makra provádí vždy vzhledem k označené buňce před spuštěním makra. Pokud chcete aby činnost makra byla nezávislá, nesmí být zapnuta možnost Relative Reference před spuštěním záznamu.
KURZ1.XLS (Makro1, Makro2)
17
Vložení spojnice trendu
Graf A
B
C
D
Problém:
1 Hodnoty pro zobrazení přímky y=x 2 X Y 3 1 1 4 2 2 5 3 3 6 5 5 7 8 8 8 12 12 9 15 15 10
Správně zobrazit přímku a rovnici spojnice trendu. Zobrazení uvedených dat grafem Line nevede k správnému výsledku.
Klikněte na některý bod zobrazené řady údajú a pak pravým tlačítkem myši vyvolejte místní nabídku. Zvolte Add Trendline, vyberte záložku Type a označte Linear, a vyberte záložku Options a označte položku Display equation on chart.
4 1
Řešení: Pro správné zobrazení funkce použijte graf XY (Scatter).
Line Type Chart
20 15
2
y = 2,3929x - 3
Y
10 5 Nesprávné použití grafu typu 0 "Line" pro zobrazení funkce. -5 1
2
3
4
5
6
7
X
XY (Scatter)
3
Označte oblast A3:B9 a pomocí Chart Wizard sestrojte XY (Scatter) graf.
1
3
Kopírování grafů Při kopírování grafu bude jeho kopie napojena na původní data a jakákoli změna v datech se projeví jak v originálu, tak v kopii grafu. Když chcete uchovat kopie nezměněné, stiskněte Shift, označte graf a Copy. Pak vložte zkopírovaný graf přes nabítku Edit nástrojem Paste Special a zvolte Picture.
2
2
!
Graf
(Chart)
KURZ2.XLS (Graf)
18
A
B
C
D
DATUM 10.8.02 11.8.02 12.8.02 13.8.02 15.8.02 16.8.02 18.8.02 19.8.02 20.8.02 21.8.02 22.8.02 23.8.02 24.8.02 25.8.02 26.8.02 27.8.02 28.8.02 29.8.02 31.8.02
R 3,63 3,28 3,4 3,3 3,18 3,23 3,27 3,31 3,33 3,31 3,36 3,41 3,48 3,33 3,38 3,34 3,33 3,31 3,31
Dolní 2,6
Horní 4
Problém:
2
Každý měsíc zobrazit pohyb hodnoty R v mezích Dolní a Horní. Je jednoduché vytvořit potřebný graf. Soustavné nastavování formátu grafu je však zdlouhavé. Označte graf, pak pravým tlačítkem myši vyvolejte místní nabídku. Zvolte Chart Type, čímž otevřete okno Chart Type. Zde vyberte Custom Types, pak User-defined a Add.... Napište název nového vlastního typu grafu a zvolte OK.
Řešení: Vytvořte potřebný formát a pak jej uložte jako typ grafu definovaný uživatelem. Ten pak můžete kdykoli vyvolat.
Postup
3 4
Označte oblast A1:D20, pak pomocí Chart Wizard sestrojte Line chart, sub-type 4.
2,6
4
Nastavte graf tak, aby se meze zobrazovaly jako vodorovné čáry. Upravte formát, zrušte legendu, přizpůsobte velikost písma.
2
1
4,5 4 3,5 3 2,5 2 1,5 1 0,5 0
Použití vlastního typu grafu
Rad1 Rad2 Rad3
10 .8 .0 12 2 .8 .0 15 2 .8 .0 18 2 .8 .0 20 2 .8 .0 22 2 .8 .0 24 2 .8 .0 26 2 .8 .0 28 2 .8 .0 31 2 .8 .0 2
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
Nyní, když máte vytvořený formát jaký potřebujete, uložte jej jako User-defined Type.
1
Uživatelem definovaný typ grafu
Označte sestrojený graf. Přes nabídku Tools Options otevřete okno Options a záložku Chart. Označte volbu Plot empty cells - Interpolated. Toto nastavení umožňuje zadávat pouze krajní body úseček (v tomto případě mezí v buňkách C2, D2, C20, D20).
Označte oblast vstupních dat. Pomocí ikony Chart Wizard otevřete okno Chart Wizard. Zde vyberte Custom Tytes, pak User-defined a vyberte vhodný typ grafu.
1 2
3
"
Uživatelem definovaný typ grafu
(User-Defined Chart Type)
KURZ2.XLS (Aut. form.)
19
2.2
UFU - užitečné funkce
Po instalaci a aktivování doplňku UFU máte k dispozici 22 nových funkcí. 4 z nich, BOXPLOT, HISTOGRAMF, NORMPROBPLOT a PARETO, je určených k vytvoření polí dat potřebných k sestrojení odpovídajících grafů. Tyto funkce jsou popsané v listech, které se zabývají grafy. Instalační soubory, návod na instalaci a podrobný manuál k funkcím UFU jsou na přiloženém CD.
!
UFU - užitečné funkce
(21, 22, 23, 24)
20
Sestrojení histogramu
Histogram 15,4 15,5 15,4 15,1 15,4 15,3 15,4 15,5 14,7 15,5 15,4 14,6 14,8 14,6 14,9 15,3 14,8 15 14,8
Problém: Znázornit rozložení hodnot tloušťky.
Řešení: Rozložení hodnot lze velmi dobře znázornit histogramem.
2
Histogram Levý okraj
Pravý okraj
3 4
Hodnoty Třídní znak - střed třídy Šířka třídy
Hranice tříd
Histogram znázorňuje rozložení hodnot prostřednictvím četností jejich výskytu v jistém počtu stejně širokých intervalů - tříd. Počet tříd je zaokrouhlenou odmocninou z počtu hodnot, nejméně však 5 a nejvíce 20. Do každé vnitřní třídy padnou hodnoty > dolní hranice a <= horní hranice třídy. Do levé krajní třídy padnou všechny hodnoty <= její horní hranice, do pravé krajní pak hodnoty > její dolní hranice. Histogram může být sestrojen jako kumulativní, kdy do každé třídy padnou hodnoty <= její horní hranice. Pravá krajní třída pak obsahuje všechny hodnoty. Zobrazení četností může být relativní, kdy se zobrazuje poměr počtu hodnot v každé třídě k celkovému počtu hodnot. Levý okraj a Pravý okraj nemají matematický smysl. Existují pouze pro jednodušší zadávání do funkce Histogram.
14; 3; HGR
Avg 15,0776 Std 0,23071 Count 76 Value Abs. freq. Bord. 14,6 4 14,6563 14,7125 2 14,7688 14,825 7 14,8813 14,9375 10 14,9938 15,05 23 15,1063 15,1625 13 15,2188 15,275 7 15,3313 15,3875 6 15,4438 15,5 4
Průměr ze vstupních hodnot
1 Tloušťka, náh. výběr z 12.02 2 15,1 15 15,2 3 14,9 15,1 15,2 4 15 15 15,1 5 14,7 14,9 14,9 6 15 15,1 15,2 7 14,9 15 15,2 8 14,9 14,9 15,1 9 15 15,1 15,2 10 15,3 15,1 14,8 11 15,1 15,2 15,3 12 15 15,2 15,2 13 15,2 14,6 15,1 14 15,1 14,6 15,2 15 15,1 14,9 15,2 16 15,2 14,9 15,2 17 15,1 14,8 14,8 18 15,1 14,8 15,3 19 15,4 15 15,5 20 15,3 14,9 15,3 21
1
D
Směrodatná odchylka
C
Počet vstupních hodnot
B
Četnosti
A
V této buňce bude v případě kumulativního histogramu označení Cumul. V záhlaví sloupce četností bude v případě relativních četností označení Rel. freq., anebo Dens. v případě hustoty pravděpodobnosti
5
Histogram můžete sestrojit pomocí nástroje EXCELu Analýza dat - Histogram. Dále můžete použít funkce FREQUENCY, popsané na listu 15. V obou případech musíte předem připravit hranice tříd. Pokud máte nainstalovaný doplněk UFU, můžete použít jeho funkci HISTOGRAMF. Výstupem funkce HISTOGRAMF je pole.
!
Doplněk UFU
(20)
Histogram
KURZ2.XLS (Hist.)
(15)
21
Sestrojení grafu
Grafický test normality A
B
C
1 Tloušťka, náh. výběr z 12.02 2 15,10 15 15,20 3 14,90 15,1 15,20 4 15,00 15 15,10 5 14,70 14,9 14,90 6 15,00 15,1 15,20 7 14,90 15 15,20 8 14,90 14,9 15,10 9 15,00 15,1 15,20 10 15,30 15,10 14,80 11 15,10 15,20 15,30 12 15,00 15,20 15,20 13 15,20 14,60 15,10 22 23 Tloušťka, náh. výběr z 1.03 24 14,80 14,80 15,00 25 14,90 14,90 15,10 26 14,80 15,10 15,10 27 15,00 14,90 15,10 28 14,90 14,90 15,10 29 14,70 14,90 14,90 30 14,80 14,80 15,00 31 14,80 14,80 15,00 32 14,80 14,90 15,00 33 15,00 14,90 15,1
!
Doplněk UFU
(20)
D 15,40 15,50 15,40 15,10 15,40 15,30 15,40 15,50 14,70 15,50 15,40 14,60
1 Problém: Je předpoklad, že data z 12.02 a z 1.03 pocházejí z normálního rozdělení. Je možné tento předpoklad zamítnout, nebo ne?
Řešení:
2
Použijte funkci UFU NormProbPlot.
3 4
15,20 15,20 15,40 15,30 15,20 15,20 15,20 15,10 15,20 15,20
Grafický test normality
5
(Normal Probability Plot)
KURZ2.XLS (Norm.)
(28)
22
Sestrojení grafu
Krabicový diagram (Box and Whisker Plot) B
C
T1 15,0 15,3 15,1 14,6 15,2 14,6 15,0 15,1 15,4 15,2 15,1 14,4 15,2 14,9 15,4 15,3 15,3 15,9 15,0 15,3
T2 14,4 14,8 14,8 15,0 15,4 14,8 14,9 14,9 15,3 15,0 14,6 15,3 14,9 15,2 14,5 15,1 14,9 15,3 14,8 15,6
T3 15,4 14,8 14,7 14,8 15,0 15,1 14,9 14,7 15,1 14,8 15,2 15,2 15,3 14,3 14,7 14,6 15,7 14,7 15,3 15,0
1
D T4 15,1 Problém: 15,0 Čtyři skupiny dat z tabulky je třeba zobrazit 14,8 takovým způsobem, aby byl z grafu patrný jednak 15,2 vzájemný vztah skupin, jednak rozložení hodnot v 15,3 každé skupině. 14,8 14,9 15,9 Řešení: 15,2 Každou skupinu dat zobrazit krabicovým 14,7 diagramem. Ten je obrazem rozdělení dat ve 15,0 skupině, vzájemná poloha diagramů pak vyjadřuje 15,1 vztah mezi skupinami. Pro sestrojení grafu 15,2 použijte funkci UFU BoxPlot. 15,0 15,0 14,8 14,8 Krabicový diagram 15,4 (Box and Whisker Plot) 15,3 Extrémní hodnota (outlier) 14,7 Maximum
Minimum
50%
Dolní kvartil (DQ)
25
Medián
75% hodnot
Horní kvartil (HQ)
100% hodnot
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
A
2
4
5; 13
T1 T2 T3 T4
Doplněk UFU
(20)
Krabicový diagram
25%Min. Max. 50% OutMinOrOutMaxO 75% AVG 15 Počet 14,6řádků 15,4 14,4 15,9 15,3 15,1111 a sloupců15,15 výstupní oblasti 15 14,4 15,6 14,9 14,9 14,9 15,225 14,975 15 14,3 15,7 14,95 14,95 14,95 15,2 14,965 15 14,7 15,4 15 15 15,9 15,2 15,0158
Názvy skupin
Krabicový diagram zobrazuje rozložení hodnot ve čtyřech částech po 25% hodnot. Hranice těchto oblastí jsou kvartily. Krabice (box) znamená dvě střední části a krabici přesahující úsečky (whiskers - vousy) zobrazují dvě krajní 25% části. Nesymetrické uspořádání hranic částí - kvartilů ukazuje na nesymetrické rozdělení. Extrémní hodnoty (outliers) jsou hodnoty větší jako HQ + 1,5 x (HQ - DQ), anebo menší jako DQ - 1,5 x (HQ - DQ). Hodnoty pro sestrojení grafu jsou pak přepočítány bez těchto extrémních hodnot. Funkce BoxPlot nevyhodnocuje extrémní hodnoty.
!
3
5
(Box and Whisker Plot)
Hodnoty kvartilů
Oblast hodnot pro graf
STD Count Outli 0,23487 18 -13 19 Četnosti skupin 0,30758 20 0,32811 20 0,21412 19 9
Průměry skupin Extrémní hodnoty (outliers), pokud se vyskytují, jinak medián
Směrodatné odchylky skupin
Pořadová čísla extrémních hodnot (outliers) ve skupinách. Záporná čísla znamenají extrémní hodnoty na straně minima. Z extrémních hodnot (otliers) jsou zobrazeny grafem pouze největší anebo nejmenší.
KURZ2.XLS (Krab. dia.)
!
23
Sestrojení grafu
Paretův diagram (Pareto Chart) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
A
B
TYP MA78 MA7809 MA7812 MA78L MC78 MC7805 MC782 MC78L MC78M MC79 MC7912 MC79L TL431
POČET 500 2 3 1 63 3 2 97 68 112 7 2 190
1
C
Problém: Znázornit přehled typů podle jejich jejich podílu na vytěžení výrobní linky.
Řešení: Na zobrazení je vhodné použít Paretův diagram. Pro přípravu dat použijte funkci UFU Pareto.
2
3 4 15; 3; PR SumOfSig 1050 Group Value Cumul. MA78 500 47,619 TL431 190 65,7143 MC79 112 76,381 MC78L 97 85,619 MC78M 68 92,0952 MC78 63 98,0952 MC7912 7 98,7619 MA7812 3 99,0476 MC7805 3 99,3333 MA7809 2 99,5238 MC782 2 99,7143 MC79L 2 99,9048 MA78L 1 100
Paretův diagram 60 50 40 30 20
100 80 Kumulativní křivka
10 0
60 40 20 0
Osa kumulativní křivky [%]
Stupnice významnosti
(Pareto)
Kategorie (příčiny, jevy ...) uspořádané podle jejich významnosti
5
Názvy kategorií Významnosti Kumulativní hodnoty
!
Doplněk UFU
(20)
Paretův diagram
(Pareto Chart)
KURZ2.XLS (Pareto)
24
Zobrazení funkce dvou proměnných Řešení:
Experimentem byla pro tloušťku vrstvy zjištěna rovnice h=14,38+0,064*P-0,016*FH , kde h je tloušťka v µ, P je výkon v kW a FH je průtok vodíku v l/min. Je třeba sestrojit graf závislosti tloušťky, pro P v rozsahu 105 až 115 kW a průtok vodíku od 260 do 380 l/min.
Připravte datovou tabulku hodnot. Pak hodnoty zobrazte 3D grafem.
Výkon [kW]
Problém:
16,78 105 106 107 108 109 110 111 112 113 114 115
Sestrojení datové tabulky B
P= FH =
Výkon [kW]
h=
105 270
16,78 105 106 107 108 109 110 111 112 113 114 115
C
D
kW l/min
260
E
F
G
H
I
280
Průtok H2 [l/min] 300 320 340
280 16,62 16,68 16,75 16,81 16,88 16,94 17,00 17,07 17,13 17,20 17,26
Průtok H2 [l/min] 300 320 340 16,30 15,98 15,66 16,36 16,04 15,72 16,43 16,11 15,79 16,49 16,17 15,85 16,56 16,24 15,92 16,62 16,30 15,98 16,68 16,36 16,04 16,75 16,43 16,11 16,81 16,49 16,17 16,88 16,56 16,24 16,94 16,62 16,30
360 15,34 15,40 15,47 15,53 15,60 15,66 15,72 15,79 15,85 15,92 15,98
380 15,02 15,08 15,15 15,21 15,28 15,34 15,40 15,47 15,53 15,60 15,66
Sestrojení grafu
=14,38+0,064*B1-0,016*B2 vzorec v levém horním rohu tabulky
Obsah buňky B5 přesuňte mimo tabulky, označte oblast B5:I16 a pomocí Chart Wizard sestrojte graf typu Surface. Po dokončení grafu přesuňte vzorec na původní místo. 360
380
1
Hodnoty pro průtok vodíku Hodnoty pro výkon
A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
260 16,94 17,00 17,07 17,13 17,20 17,26 17,32 17,39 17,45 17,52 17,58
2
3
Vzorec přesunout mimo tabulky Další postup sestrojení datové tabulky je popsaný na listu 16. Vzorec vrátit na původní místo
Pro účel vytvoření 3D grafu funkce dvou proměnných je důležité, aby řady hodnot pro obě proměnné byly tvořeny aritmetickými řadami. 3D graf v EXCELu umí na osách x, y zobrazit pouze pořadí hodnot a ne jejich polohu. Hodnoty v záhlaví tabulek budou zobrazeny pouze jako popisky os.
4 5
Pro jednoduché sestrojení grafu je důležité, aby v levém horním rohu tabulky vstupních dat byla během sestrojování grafu prázná buňka.
!
"
Zobrazení funkce dvou proměnných
KURZ2.XLS (F. 2 prem.)
(16)
25
Generátor pseudonáhodných čísel (Random ...)
3
Řešení:
Problém: Pro ověření funkce statistických nástrojů vytvořit množinu 100 čísel, která bude reprezentovat výběr z normálního rozdělení se střední hodnotou 100 a směrodatnou odchylkou 10.
Na vytvoření množiny náhodných čísel s definovaným rozdělením použijte nástroj Random Number Generation.
Počet sloupců Počet řádků
Použití generátoru Přes nabídku Tools - Data Analysis otevřete dialogové okno Data Analysis. Pak vyberte Random Number Generation.
Volba typu rozdělení
1 2
Buňka levého horního rohu výstupní oblasti
Po klepnutí na OK bude oblast A1:A100 vyplněna pseudonáhodnými čísly, která reprezentují náhodný výběr z normálního rozdělení se střední hodnotou 100 a směrodatnou odchylkou 10. Random Seed (základ generátoru) může nabývat hodnot 1 - 32767 a ovlivňuje start generátoru. Při stejném základu generátoru dostanete stejná pseudonáhodná čísla.
V dialogovém okně Random Number Genaration zadejte vstupní hodnoty podle obrázku: Number of Variables - počet sloupců výstupu Number of Random Numbers - počet řádků výstupu Mean - střední hodnota Standard Deviation - směrodatná odchylka V rámečku Output Options zvolte Output Range a zadejte buňku levého horního rohu výstupní oblasti.
!
Doplněk Analytické nástroje
(2)
Generátor pseudonáhodných čísel (Random Number Generation)
KURZ3.XLS (Nahod. cisla)
26
Vzorkování (Sampling) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 993 994 995 996 997 998 999 1000 1001
A T 15,5 15,6 16 15,5 15,7 15,5 15,5 15,5 15,5 15,4 15,4 15,4 14,9 15,2
B
C
D
3
Problém: Z 1000 hodnot získaných z technologického deníku náhodně vybrat 100 hodnot pro test normality
Řešení: Nástroj Sampling umožňuje provést náhodný výběr.
Použití nástroje Vzorkování Přes nabídku Tools - Data Analysis otevřete dialogové okno Data Analysis. Pak vyberte Sampling.
1
15,1 15,1 15,1 15,3 15 15 15,1 15 15,5
2 Nástroj Sampling provede vzorkování s opakováním.
!
Jiná možnost
V dialogovém okně Sampling zadejte vstupní hodnoty podle obrázku:
A
B
1 2 3
T 15,5 15,6
0,70002 0,87532
###
15 15,5
0,70997 0,36081
###
Input Labels (označte, pokud jste zadali vstupní oblast včetně záhlaví) Sampling Method (zvolte Random)
C =RAND()
Do sloupce B vedle čísel vložte funkci RAND(). Pak označte oblast A1:B1001 a pomocí nástroje Seřadit uspořádejte hodnoty podle sloupce B. Nyní vyberte ze sloupce A prvních 100 hodnot.
=RAND()
Tímto způsobem provedete náhodný výběr bez opakování.
V rámečku Output Options zvolte Output Range a zadejte buňku levého horního rohu výstupní oblasti.
!
Doplněk Analytické nástroje
(2)
Vzorkování
(Sampling)
KURZ3.XLS (Vzorka)
27
Pořadová statistika a percentily (Rank ...) A 1 2 3 4 5 6 7 8 9 10 11
T 15,1 14,9 15 14,7 15 14,9 14,9 15 15,3 15,1
75 76 77 78
14,8 15 14,8
B
C
D
Hodnota
Pořadí podle velikosti
Původní pořadí
Hodnota percentilu
Problém: Graficky znázornit distribuční funkci hodnot získaných náhodným výběrem.
4
Řešení: Pomocí nástroje Rank and Percentile můžete vypočítat hodnoty výběrové distribuční funkce a pak ji graficky znázornit.
Použití nástroje Pořadová statistika a percentily Přes nabídku Tools - Data Analysis otevřete dialogové okno Data Analysis. Pak vyberte Rank and Percentile a zadejte hodnoty podle obrázku.
1
2
Označte oblasti G2:G77 a I2:I77. Pomocí Chart Wizard sestrojte XY (Scatter).
5
3
!
Doplněk Analytické nástroje
(2)
Pořadová statistika a percentily
(Rank and Percentile)
KURZ3.XLS (Percentil)
28
Popisná statistika (Descriptive Statistics) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
A
B
T1 15,1 15,3 15,6 15 15,1 15,2 15,1 15,1 15,1 15,1 14,9 14,9 14,4 14,8
T2 15,1 15,3 15,5 15 15,2 15,1 15,1 15,2 15,10 15,00 14,80 14,70 14,50 14,90
C
D
sm. odchylka Výsledky pro T1
počet
Problém: Při výpočtech statistických charakteristik může být vkládání funkcí, označování oblastí atd. zdlouhavé.
Výsledky pro T2
4
Řešení: Nástrojem Descriptive Statistics můžete najednou získat základní statistické charakteristiky jedné, nebo více skupin dat.
Použití nástroje Popisná statistika Přes nabídku Tools - Data Analysis otevřete dialogové okno Data Analysis. Pak vyberte Descriptive Statistics.
1
2
3 Skupiny dat jsou ve sloupcích
Kurtosis (Špičatost) Vyjadřuje relativní špičatost rozdělení, vhledem k normálnímu rozdělení. Kladná Špičatost znamená, že rozdělení je strmější, jako normální (hodnoty jsou více soustředěny kolem střední hodnoty). Naopak, záporná Špičatost známená příliš ploché rozdělení. Stejnou hodnotu vrátí funkce KURT.
Pokud není zaškrtnuto, bude použita hodnota 95%, jinak podle zadání Stupeň spolehlivosti pro určení intervalu spolehlivosti střední hodnoty. Stupeň spolehlivosti = 100(1 - α)%, kde α je hladina významnosti Pokud je zaškrtnuto, bude ve výstupní tabulce také k-tá největší, nebo nejmenší hodnota
!
Doplněk Analytické nástroje
(2)
Popisná statistika
(Descriptive Statistics)
Skewness (Šikmost) Vyjadřuje asymetrii rozdělení. Kladná Šikmost znamená posun k větším hodnotám, záporná posun k menším hodnotám. Stejnou hodnotu vrátí funkce SKEW.
V jiných programech (nebo literatuře) mohou být výsledky pro špičatost a šikmost poněkud odlišné. Většinou se liší pouze o konstantu.
KURZ3.XLS (Popis)
!
29
Korelace (Correlation) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
A
B
C
t1 97,5 93,6 93,1 93,2 95,7 97,7 92,1 93,7 97,1 94,9 92,8 96,1 93,1 94,7 92,5
t2 1000,4 997,3 1004,0 997,8 1000,7 997,6 999,1 997,8 1001,2 998,9 1001,6 999,0 999,9 994,5 997,6
T 1054,1 1000,0 1001,3 1011,7 1027,9 1037,2 995,4 1021,0 1029,3 1003,0 993,6 1013,0 1014,3 1022,4 993,5
Použití nástroje Korelace Přes nabídku Tools - Data Analysis otevřete dialogové okno Data Analysis. Pak vyberte Correlation.
D
Na obrázku vidět, že pouze hodnota 0,8455 může znamenat významnou závislost. R2=0,7148, proveďte tedy test hypotézy H0, že T a t1 jsou nezávislé.
4
Problém: Zjistit, zda je velká variabilita tloušťky T způsobená změnou teploty t1 nebo t2 anebo ani jednou z nich.
z´=FISHER(0,8455)=1,24 SQRT(15-3)*z´=4,3 4,3 > 1,96 proto se H0 zamítá. Výsledek potvrdil, že variabilitu tloušťky výrazně ovlivňuje právě změna teploty t1.
Řešení: Použít nástroje Correlation k vytvoření korelační matice. Podle velikostí korelačních koeficientů posoudit závislosti. Korelační koeficient dvou skupin dat vypočítá také funkce CORREL.
Další možnosti I
Korelační koeficient R vyjadřuje vzájemnou závislost dvou skupin dat. R=0 skupiny jsou nezávislé R=1 nebo R=-1 skupiny jsou lineárně závislé Test hypotézy H0, že dvě skupiny dat jsou vzájemně nezávislé (pro rozsah výběru n > 10, α=0,05).
T 13,69 14,02 14,49
100
13,93 14,56
101
1. Vypočítat R a pak hodnotu z´=FISHER(R) 2. Hypotézu H0 zamítáme pokud SQRT(n-3)*z´ > 1,96
2
3. Hypotézu H0 obvykle zamítáme když R2>0,8
1 2 3
1 2 3 4
1 2 3 4 5 6 7 8 9 10 11 12
A
13,69 14,02
Problém:
J1
Zjistit, zda v časové řadě není závislost mezi předcházejícími a následujícími hodnotami - autokorelace.
Řešení: Zkopírujte hodnoty z oblasti I2:I101 postupně do H3:H102, G4:G103, ... A10:A109. Potom pomocí nástroje Correlation vytvořte korelační matici pro vstupní oblast A10:I101 do výstupní oblasti začínající v buňce K1. (tentokrát nezaškrtávejte políčko Záhlaví v prvním řádku).
B
13,69 14,02 14,49
C
13,69 14,02 14,49 14,77
D
13,69 14,02 14,49 14,77 15,08
E
13,69 14,02 14,49 14,77 15,08 16,14
F
313,69 14,02 14,49 14,77 15,08 16,14 15,18
G
13,69 14,02 14,49 14,77 15,08 16,14 15,18 13,87
H
I
13,69 14,02 14,49 14,77 15,08 16,14 15,18 13,87 13,94
T 13,69 14,02 14,49 14,77 15,08 16,14 15,18 13,87 13,94 15,25
Ve výslední matici je vidět že je výrazná korelace mezi sloupci 1 a 8, 2 a 9 atd.
!
Doplněk Analytické nástroje
(2)
Korelace
(Correlation)
KURZ3.XLS (Korel, Akorel)
30
Regrese (Regression) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
A
B
t1 97,5 93,6 93,1 93,2 95,7 97,7 92,1 93,7 97,1 94,9 92,8 96,1 93,1 94,7 92,5
T 1054,1 1000,0 1001,3 1011,7 1027,9 1037,2 995,4 1021,0 1029,3 1003,0 993,6 1013,0 1014,3 1022,4 993,5
C
D
4
SUMMARY OUTPUT
Korelační koeficient R R2
Regression Statistics Multiple R 0,84546 R Square 0,7148 Adjusted 0,69286 Standard 9,77599 Observati 15
Problém: Vytvořit lineární model závislosti T na t1 a zjistit jeho statistickou významnost.
Řešení:
Upravené R2 (Adj R ) 2
Reziduální odchylka Počet stupňů volnosti (dF) p - hodnota Fisherova rozdělení pro F
Použijte statistický nástroj Regrese.
ANOVA
Použití nástroje Regrese
df Regressio Residual Total
Přes nabídku Tools - Data Analysis otevřete dialogové okno Data Analysis. Pak vyberte Regression.
SS MS F Significance F 1 3113,79 3113,79 32,5813 7,2E-05 13 1242,41 95,5699 14 4356,2
Coefficients Standard Err
1
t Stat
P-value
MSRegrese MSRe zidua
Lower 95% Upper 95% Lower 95,0%Upper 95,0%
Intercept 267,892 130,827 2,04768 0,06135 -14,743 550,526 -14,743 550,526 t1 7,89909 1,38386 5,708 7,2E-05 4,90943 10,8887 4,90943 10,8887 a
2
regresní přímka: y = α + βx odhad α = a odhad β = b
b
3 t1 Line Fit Plot
Hodnoty pro zadanou úroveň věrohodnosti, pokud bylo zaškrtnuto políčko Úroveň věrohodnosti.
1060,0
T
1040,0
Pokud je zaškrtnuto, bude regresní přímka procházet počátkem
Hranice intervalů spolehlivosti pro odhad parametrů regresní přímky
1020,0 1000,0 980,0 90,0
T Predicted T
92,0
94,0 t1
96,0
98,0
Statistická významnost modelu Hypotéza H0, že model je statisticky nevýznamný, se zamítá jestliže vypočtená p - hodnota (Significance F) je menší, jako předem zvolená hladina významnosti (0,05). Vypočtený model platí pro průměry, přičemž konstanty a, b jsou odhadem parametrů regresní přímky (α, β). Hranice intervalů spolehlivosti pro tyto hodnoty jsou ve sloupcích Lower 95% a Upper 95% .
!
Doplněk Analytické nástroje
(2)
Regrese
(Regression)
KURZ3.XLS (Regres)
(32, 34, 35)
31
F-Test Two-Sample for Variances
F - Test T 14,5 14,5 14,9 14,7 14,8 15,2 13,5 14,3 13,9 15,4 13,3 15,1 16,2 15,6 14,6 16,6 14,3 15,9
T1 14,8 14,8 14,4 15,9 15,5 15,1 13,6 14,9 14,3
T2 14,7 14,4 14,4 15,8 15,4 14,2 15,0 15,5 14,9
0,878
0,675
Problém: Ve sloupci A jsou hodnoty tloušťky a v buňce A:21 je směrodatná odchylka. Protože pro výrobní účely je příliš velká, byly provedeny různé úpravy zařízení a pak technologické zkoušky. Výsledky jsou ve sloupcích B a C. Směrodatné odchylky v řádku 21 naznačují, že by mohlo jít o zlepšení. Nyní je nutno ověřit, jestli změny směrodatné odchylky jsou výsledkem technologických úprav nebo jsou pouze náhodné.
Řešení: Statistický nástroj F - Test umožňuje provést test hypotézy, že dva výběry pocházejí z rozdělení se stejným rozptylem. Postavte hypotézu H0: výběry T a T1 pocházejí z rozdělení se stejným rozptylem a hypotézu H1: výběr T1 pochází z rozdělení s menším rozptylem, jako výběr T. Podobně pro T2. Když pomocí F - testu zamítnete hypotézu H0, můžete považovat zlepšení směrodatné odchylky za výsledek technologických úprav.
Použití nástroje F - Test Přes nabídku Tools - Data Analysis otevřete dialogové okno Data 0,554 Analysis. Pak vyberte F-Test Two -Sample for Variances.
1
Výběr s větším rozptylem Výběr s menším rozptylem Zvolená hladina významnosti
Do vstupního pole 1. soubor zadávejte vždy výběr s větším rozptylem. Výběry musejí pocházet z normálního rozdělení!
!
Doplněk Analytické nástroje
(2)
F - Test
!
Počty stupňů volnosti
D
Počty hodnot
C
Odhady rozptylů
B
Odhady středních hodnot
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
A
2 T T1 Mean 14,85 14,8111 Variance 0,77088 0,45611 Observati 18 9 df 17 8 F 1,69012 P(F<=f) o 0,22828 F Critical 3,1867
Hypotéza H0 se zamítá, když F > F krit (1), anebo P(F<=f) (1) je menší jako zvolená hladina významnosti. Dvoustranní p-hodnotu vrátí také funkce FTEST, anebo UFU funkce XFtest. P(F<=f)(1) = P(F<=f)(2) / 2 Hodnota testového kritéria Jednostranní p-hodnota Jednostranní kritická hodnota F-rozdělení
1,6901 < 3,1867, tedy hypotézu, že výběry T a T1 pocházejí z rozdělení se stejným rozptylem nemůžete zamítnout. Podobný výsledek je také pro T a T2 (2,5115 < 3,1867). Zdá se tedy, že snížení směrodatných odchylek pro T1 a T2 je pouze náhodné. Na následujícím obrázku byly malé výběry T1 a T2 nahrazeny doplněnými výběry T1d a T2d. Nyní s těmito výběry opět proveďte F - test.
24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44
A
B
C
T 14,5 14,5 14,9 14,7 14,8 15,2 13,5 14,3 13,9 15,4 13,3 15,1 16,2 15,6 14,6 16,6 14,3 15,9
T1d 14,8 14,8 14,4 15,9 15,5 15,1 13,6 14,9 14,3 17,0 13,5 15,7 14,9 15,5 14,9 14,5 16,1 15,5
T2d 14,7 14,4 14,4 15,8 15,4 14,2 15,0 15,5 14,9 14,5 14,9 14,7 14,7 15,4 15,3 14,4 14,7 15,3
0,878
0,862
0,458
V případě T1d se hypotéza H0 opět nezamítá. Také směrodatná odchylka odhadnuta z výběru T1d (0,862) je blízká původní hodnotě 0,878. Je patrné, že rozdílná hodnota odhadnutá z výběru T1 (0,675) byla pouze náhodní (protože výběr T1 je malý).
V případě T2d se hypotéza H0 zamítá (3,6812>2,2719). V tomto případě můžete pokládat zmenšení směrodatné odchylky za výsledek úpravy zařízení.
KURZ4.XLS (F - Test)
(36)
32
t-Test: Two-Sample Assuming Equal Variances
t - test
14,993
15,507
Problém: Po poruše zařízení vzniklo podezření, že byla zvětšená střední hodnota. Stav před poruchou je reprezentovaný výběrem T, stav po poruše pak výběrem T1. V řádku 18 jsou odhady středních hodnot. Je třeba ověřit, zda skutečně došlo ke zvýšení střední hodnoty, anebo je výsledek pouze náhodný.
Řešení: Pro testování hypotézy o rovnosti středních hodnot, nebo jejich definovaném rozdílu slouží statistický nástroj t - test. Postavte hypotézu H0: výběry T a T1 pocházejí z rozdělení se stejnými středními hodnotami a hypotézu H1: výběr T1 pochází z rozdělení s větší střední hodnotou jako výběr T. Když pomocí t - testu zamítnete hypotézu H0, můžete prohlásit, že vlivem poruchy byla zvětšena střední hodnota.
Počet stupňů volnosti
T1 15,6 15,2 16,0 15,7 15,0 15,5 15,2 16,2 15,6 15,4 16,1 15,1 15,5 15,4 15,1
Počty hodnot
T 15,7 14,9 14,5 14,5 14,8 14,9 15,1 14,7 15,0 15,0 14,6 15,3 15,8 14,6 15,5
C
Odhady rozptylů
B
Odhady středních hodnot
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
A
2 T T1 Mean 14,9933 15,5067 Variance 0,17495 0,13781 Observati 15 15 Pooled Va 0,15638 Hypothes 0 df 28 t Stat -3,555 P(T<=t) o 0,00068 t Critical o 1,70113 P(T<=t) tw 0,00137 t Critical t 2,04841
Sdružený rozptyl (pooled variance) Definovaný rozdíl mezi středními hodnotami Hodnota testového kritéria Jednostranní p-hodnota p-hodnotu vrátí také funkce TTEST. Jednostranní kritická hodnota T-rozdělení Dvoustranné hodnoty
Hypotéza H0 se zamítá, když t stat > t krit (t stat < -t krit), anebo P(T<=t) je menší jako zvolená hladina významnosti. Když je H1 definována tak, že jeden výběr pochází z rozdělení se střední hodnotou pouze větší (menší) jako střední hodnota druhého rozdělení pak se použijí jednostranné hodnoty. V případě definice H1, že výběry pocházejí z rozdělení s různými středními hodnotami, se použijí dvoustranné hodnoty. p-hodnoty vrátí také funkce TTEST, anebo UFU funkce XTtest.
Použití nástroje T - Test
V tomto případě použijte jednostranné hodnoty. -3,555 < -1,7011, tedy hypotézu, že výběry T a T1 pocházejí z rozdělení se stejnými středními hodnotami můžete zamítnout.
Přes nabídku Tools - Data Analysis otevřete dialogové okno Data Analysis. Pak vyberte Two -Sample Assuming Equal Variances.
Byla provedena oprava zařízení. Po opravě by měla být střední hodnota snížena přibližně o 0,5. Stav po opravě je reprezentován výběrem T2.
1
Postavte hypotézu H0: výběry T1 a T2 pocházejí z rozdělení se stejnými středními hodnotami a hypotézu H1: výběry T1 a T2 pocházejí z rozdělení s různými středními hodnotami. Nula pro hypotézu H0, že výběry pocházejí z rozdělení se stejnou střední hodnotou.
Zvolená hladina významnosti
Definovaný rozdíl středních hodnot pro hypotézu H0, že výběry pocházejí z rozdělení s definovaným rozdílem středních hodnot.
Pokud bude F-testem zamítnuta hypotéza o rovnosti rozptylů, použijte nástroje Two - Sample Assuming Unequal Variances (to však vede ke snížení počtu stupňů volnosti). Výběry musejí pocházet z normálního rozdělení!
!
Doplněk Analytické nástroje
(2)
t - test
Když pomocí t - testu zamítnete hypotézu H0, můžete prohlásit, že opravou byl dosažen posun hodnoty.
21 22 23
A
B
T1 15,6 15,2
T2 14,7 15,7
34 15,5 35 15,4 36 15,1 37 38 15,507
Položku Hypotesized Mean Difference zadejte 0.
C
Je vidět, že 3,2227 > 1,7011 ale také 3,2227 > 2.0484. Hypotézu, že rozdíl mezi středními hodnotami je 0 můžete zamítnout, tedy oprava byla účinná. Sestrojením konfidenčních intervalů (funkce UFU Estimation) pro průměry ve výběrech T1 a T2 se přesvědčte, jakého posunu střední hodnoty bylo dosaženo.
15,5 14,5 14,7 15,027
! KURZ4.XLS (t - test)
(36)
33
Anova 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
A
B
C
D
T1 14,5 16,8 16,8 16,0 14,8 14,8 14,7 15,4 16,7 14,0 13,7 15,7 15,3 14,6 15,0
T2 15,1 14,6 14,8 15,4 16,2 15,6 14,4 14,8 13,6 13,7 15,5 16,6 16,8 13,5 15,1
T3 15,7 14,7 16,7 14,4 14,4 15,4 14,5 14,6 13,8 14,2 15,3 14,8 14,5 14,5 15,5
T4 16,5 15,3 14,7 13,5 13,8 13,6 15,2 14,4 15,5 13,9 14,3 14,9 14,0 14,2 13,4
15,253
15,047
14,867
14,480
2
Problém: Výběry T1, T2, T3, T4 pocházejí ze čtyř různých zařízení. Otázka zní, zda jsou stejné střední hodnoty produkce z těchto čtyř strojů.
Řešení: Problém by bylo možno rozřešit testováním všech dvojic pomocí t-testu. To by ale bylo značně nepohodlné a také nespolehlivé. EXCEL poskytuje vhodnější nástroj - ANOVA (Analysis of Variance). Postavte hypotézu H0: výběry pocházejí z rozdělení se stejnými středními hodnotami a hypotézu H1: výběry pocházejí z rozdělení s různými středními hodnotami.
Použití nástroje Anova Přes nabídku Tools - Data Analysis otevřete dialogové okno Data Analysis. Pak vyberte Anova: Single Factor.
1
Hypotéza H0 se zamítá, když F > F krit, anebo hodnota P je menší jako zvolená hladina významnosti.
Anova: Single Factor SUMMARY Groups Count T1 15 T2 15 T3 15 T4 15
Sum 228,8 225,7 223 217,2
Počty stupňů volnosti (dF) ANOVA Source of Varia SS Between 4,84983 Within Gr 45,952 Total
50,8018
Součty čtverců
df
Average 15,2533 15,0467 14,8667 14,48
Variance 0,95838 1,03552 0,53238 0,756
Testové kritérium F - testu =
MS 3 1,61661 56 0,82057
MSMezi výběry MS Vse š chny výběry
F P-value F crit 1,9701 0,12892 2,76943 Jednostranní kritická hodnota F - rozdělení
59 Střední kvadratické odchylky
Jednostranní p - hodnota testového kritéria
1,9701 < 2,7694, tedy hypotézu, že výběry T1 až T4 pocházejí z rozdělení se stejnými středními hodnotami nemůžete zamítnout. ANOVA zkoumá, jestli je možné rozlišit rozdíl mezi odhady středních hodnot jednotlivých skupin, vzhledem k rozptylům hodnot uvnitř skupin. Výsledek testu s výběry T1 až T4 tedy říká, že mezi nimi není možné zjistit rozdíl, protože je velký rozptyl uvnitř výběrů - každé zařízení pracuje s velkým rozptylem. Jiná situace by nastala, kdyby se snížily rozptyly (seřízením). Pak by se mohly projevit rozdíly.
Zvolená hladina významnosti
!
Doplněk Analytické nástroje
(2)
Anova
(ANOVA)
KURZ4.XLS (Anova)
(36)
34
určit pravděpodobnost s jakou se vyskytne právě tato hodnota.
Vysvětlení k testům hypotéz Proveďme myšlenkový pokus. Máme několik pytlů, v nichž jsou černé a bílé kuličky. Pytle jsou označeny podle procenta černých kuliček. Někdo přinese 100 kuliček a řekne že je vybral z pytle 40%. Jeho hypotéza tedy je: Přinesené kuličky pocházejí z pytle 40%. Označme ji H0. Pak někdo jiný tvrdí, že kuličky byly vybrány z jiného pytle. Máme další hypotézu: Kuličky pocházejí z jiného pytle jako 40%. Tuto hypotézu označme H1.
0,6
80% 100%
50% 30%
70%80%
60% Obr. 1
Nyní máme dvě hypotézy postavené proti sobě. Dokážeme nějakým způsobem získat důkazy o tom, že první tvrzení je velice málo pravděpodobné a naopak, že pravdivost druhého tvrzení je vysoce pravděpodobná ? Dokážeme tedy zamítnout hypotézu H0 ve prospěch hypotézy H1? Taková rozhodnutí jsou možná, musíme ale mít k dispozice vhodné nástroje. Připravme si je. 0,3
40%, rozsah 5
Zkusme zjistit, zda skupina náhodně vybraných kuliček z jednoho pytle nese informaci o svém původu.
0,2 0,1 0
0,3
40%, rozsah 10
0,2 0,1 0
0,4
40%, rozsah 20
0,3 0,2 0,1 0
0,6
40%, rozsah 100
0,4
Z pytle 40% vybereme náhodně pět kuliček a zjistíme procento černých. Toto provedeme 100 krát. Ze zjištěných hodnot sestrojíme histogram relativních četností. Je na Obr.2 označený 40%, rozsah 5. Je vidět, že nejčastěji se vyskytovaly výběry, které obsahovaly něco mezi 30% a 40% černých. Hodně bylo také 5 procentních výběrů ale i 70 procentních. Nyní zvětšíme rozsah výběru na 10, pak na 20 a 100. Příslušné histogramy jsou na Obr.1. Zdá se že se zvyšováním rozsahu výběru, se informace zlepšuje. Je to tím, že ve větším výběru, pokud je náhodný, je větší počet prvků, které mohou mít různé hodnoty. Každý má volnost nabýt libovolnou hodnotu, ale má hodnotu charakterizující právě místo jeho původu. Při větším rozsahu výběru je více prvků majících volnost nabývat různých hodnot máme větší počet stupňů volnosti. Histogramy na Obr. 2 reprezentují rozdělení pravděpodobnosti výskytu hodnot procenta černých kuliček ve výběru pro 40% pytel. Procento černých kuliček ve výběru je tedy parametr obsahující informaci o tom, z kterého pytle pochází a může sloužit jako testové kritérium (též testová statistika) pro odhad místa původu výběru.
0,2 0
"
Obr. 2
Máme testové kritérium a poznáme pro něj rozdělení pravděpodobnosti s různým počtem stupňů volnosti. Jinými slovy, když poznáme velikost výběru (počet stupňů volnosti) a procento černých ve výběru, můžeme
Vysvětlení k testům hypotéz
0,4
Máme vyhráno. Provedeme výběr z pytle, zjistíme procento černých a z rozdělení pravděpodobnosti pro pytel 40% zjistíme zda hodnota testového kritéria není pro pytel 40% málo pravděpodobná. Když víme, že pro některý jiný pytel je tato hodnota vysoce pravděpodobná, můžeme zamítnout předpoklad, že výběr pochází z pytle 40%. Zůstává určit co je málo pravděpodobné, tedy určit takovou hodnotu pravděpodobnosti, že všechny menší už jsou to "málo". Této hodnotě říkáme hladina významnosti (označovaná α).
0,4
90%
40%
10%
Nyní zkusme jiné pytle. Na Obr. 3 jsou histogramy pro pytle 20% a 70% porovnány s histogramen pytle 40%, vše pro rozsah výběru 100. Je patrné, že histogramy neboli rozdělení se liší. Jinak, hodnoty procenta černých (testového kritéria), které jsou málo pravděpodobné pro výběry z pytle 40%, mohou být naopak vysoce pravděpodobné pro některé jiné pytle.
20%, rozsah 100
0,2 0
0,6
40%, rozsah 100
0,2 0
0,6
70%, rozsah 100
0,4 0,2 0
Na Obr.1 jsou znázorněny pytle s kuličkami, jsou tam 10%, 30% tedy menší jako 40% a také větší. Pokud bude hodnota testového kritéria Obr. 3 pro nějaký výběr například 2 procenta, je malá pravděpodobnost, že pochází z pytle 40%, ale velká, že pochází třeba z pytle 5%. Podobně pro hodnotu testového kritéria 85. Na obou stranách rozdělení pro 40% pytel tedy nacházíme rozdělení pro jiné pytle které nám dávají vysokou pravděpodobnost, že výběr pochází z nich, i když je pravděpodobnost pro 40% pytel malá. Na každé straně rozdělení pro 40% pytel můžeme zamítnout hypotézu H0, že výběr pochází ze 40% pytle ve prospěch hypotézy H1, že výběr pochází z jiného pytle. Protože to jde na obou stranách, hovoříme o
dvoustranném testu. Na Obr. 4 je jiná situace. Zde chybějí pytle 10%, 20%, 30%, jsou jen 40% a větší. Kdyby byla hodnota testového kritéria jakkoli nízká a pravděpodobnost pro 40% pytel jakkoli malá, nenajdeme pytel, který by poskytoval větší pravděpodobnost. Na levé straně rozdělení pro 40% pytel tedy není možné hypotézu H0 zamítnout. Hypotéza H1 může mít v tomto případě tvar: Výběr pochází z pytle, kde je podíl černých kuliček větší jako 40%. Zde se jedná o jednostranný test. Všimněte si formulace hypotéz H1 pro dvoustranný a jednostranný test. V prvním případě se vyskytuje slovo "jiný" ve druhém "větší". Při takovémto statistickém přístupu k testování se můžeme dopustit dvou chyb. Chyba prvního druhu vznikne když zamítneme H0 a ona je pravdivá. Chyby druhého druhu se dopustíme, když H0 pravdivá není a my ji nezamítneme. Možnost vzniku chyby prvního druhu omezíme snížením hladiny významnosti. Možnost vzniku chyby druhého druhu omezíme zvyšováním počtu stupňů volnosti.
50%
80% 100%
70% 90%
40%
60% Obr. 4
35
