Univerzita Pardubice Fakulta chemicko-technologická Katedra analytické chemie
Licenční studium Statistické zpracování dat
Semestrální práce Systém jakosti Tabulkový procesor - Excel
2005
Jindřich Freisleben
2
OBSAH OBSAH ...............................................................................................................................................................3 1. SYSTÉM JAKOSTI A POČÍTAČOVÁ KONTROLA JAKOSTI ...............................................................................4 1.1 Otázky ing. Kupky ......................................................................................................................................4 Proč se jako model ztrátové funkce používá parabola? ...............................................................................4 Jakou směrodatnou odchylku bude mít veličina c = 10a + 2b? ...................................................................4 Co je autokorelace? Jaký je rozdíl mezi pojmy autokorelace a závislost? ..................................................4 Které jsou fáze konstrukce Shewhartova regulačního diagramu x-průměr? ...............................................4 Co je ARL? ..................................................................................................................................................5 Jaká je výhoda a nevýhoda použití racionálních podskupin pro regulační diagramy? ................................6 Uveďte hodnoty těchto kvantilů normálního normovaného rozdělení: 2,5%, 50%, 97,5%. .......................6 Uvažujte, že 25 dat pocházejících z normálního rozdělení má průměr 165,42 a rozptyl 12,5; odhadněte interval spolehlivosti uvedeného průměru. ..................................................................................................6 Co lze říci o výrobci, který uvádí jako svůj index způsobilosti hodnotu 1,155? .........................................6 Existuje omezení pro vícerozměrná data, pro něž nelze použít Hotellingův regulační diagram? ...............7 2. TABULKOVÝ PROCESOR – MICROSOFT EXCEL ............................................................................................7 2.1 Příklad 1: Analýza malých výběrů.............................................................................................................7 2.2 Příklad 2: Docházka ..................................................................................................................................8 2.3 Příklad 3: Příjezdy.....................................................................................................................................9 2.4 Příklad 4: Najdi čísla v označeném poli buněk (makro) ...........................................................................9 LITERATURA .....................................................................................................................................................13
3
1. Systém jakosti a počítačová kontrola jakosti 1.1 Otázky ing. Kupky Proč se jako model ztrátové funkce používá parabola?
kvadratický průběh ztrátové fce
Kvadratická funkce je jednoduchá, ale při tom postačující pro aproximaci průběhu ztrátové funkce L (Loss Function) v blízkém okolí ideálního bodu T (Target).
ztráta
ztráta X-T T (Target - cílová hodnota)
X
Jakou směrodatnou odchylku bude mít veličina c = 10a + 2b? zadáno: µ(a) = 0,4 ; s(a) = 0,06 µ(b) = 4 ; s(b) = 0,2 platí:
µ(x + y) = µ(x) + µ(y) s2(x + y) = s2(x) + s2(y)
µ(c) = 10µ(a) + 2µ(b) = 4 + 8 = 12 s2(c) = 100s2(a) + 4s2(b) = 0,36 + 0,16 = 0,52 s(c) =
0,52 = 0,72
Co je autokorelace? Jaký je rozdíl mezi pojmy autokorelace a závislost? Autokorelace: 1) korelace (vzájemný vztah, závislost) mezi členy téže řady pozorování 2) naměřená hodnota je závislá na hodnotě předcházejícího a následujícího měření Které jsou fáze konstrukce Shewhartova regulačního diagramu x-průměr? a) Příprava dat sledovaného procesu.
X1 X11
X2 X21
X1m X2m
Xn Xn1 Xnm
průměr X1
rozptyl 2 1
S
2 m
Xm
S
X
S
2
rozsah 1 až n je velikost logických podskupin rozsah 1 až m je počet logických podskupin
S
S
2
Výpočtem přes rozptyly získáváme nevychýlený odhad směrodatné odchylky a tudíž nemusíme při výpočtu 4
používat koeficient C4, který v sobě obsahuje funkci směrodatné odchylky.
(gama) jako korekci na zjednodušení výpočtu
b) Ověření platnosti statistických předpokladů – – – –
normalita konstantnost střední hodnoty a rozptylu nezávislost bez vybočujících měření
c) Výpočet základní linie a regulačních mezí CL (Central Line) = X
LCL (Lower Control Level) = X 3
S n
UCL (Upper Control Level) = X 3
S n
d) Používání regulačního diagramu Regulační diagram se pak používá pro ověřování, zda měřené hodnoty splňují stanovená pravidla z nichž základní je překročení regulačních mezí.
diagram x-průměr - Sheet1
x-průměr 1.0 0.8 0.6
V případě výskytu zvláštností v datech se hledá „přiřaditelná“ příčina.
0.4 0.2 0.0 -0.2 -0.4 -0.6 -0.8 -1.0
Č. vzorku
-1.2 0
20
40
60
80
100
120
140
160
180
200
Co je ARL? a) ARL je definována jako střední hodnota počtu bodů v regulačním diagramu, který je třeba k tomu, aby bylo indikováno překročení regulačních mezí. ARL
1 p
p … pravděpodobnost, že jeden z výběrových průměrů překročí regulační meze
b) Z hlediska způsobilosti výrobního procesu je ARL (Average Run Length) „střední doba“ mezi výskytem nevyhovujících výrobků, tedy kolik výrobků připadá na jeden zmetek. ARL
1 PZMET
PZMET … podíl zmetků a pravděpodobnost výskytu nevyhovujícího výrobku (jeho znak jakosti leží mimo specifikační meze) 5
Jaká je výhoda a nevýhoda použití racionálních podskupin pro regulační diagramy? Výhoda: může vyřešit problém s nekonstantní střední hodnotou u individuálních dat Nevýhoda: potřeba většího množství dat Uveďte hodnoty těchto kvantilů normálního normovaného rozdělení: 2,5%, 50%, 97,5%. Pravděpodobnost [%] 2,5 50 97,5
Kvantil pro N(0,1) –1,96 0 1,96
Uvažujte, že 25 dat pocházejících z normálního rozdělení má průměr 165,42 a rozptyl 12,5; odhadněte interval spolehlivosti uvedeného průměru.
x 165,42 ; n 25 ;
2 12,5
12,5
pro interval spolehlivosti pro N(0,1) platí: u 1
2 n
volíme p 95%
u 1
2 n
0,05
12,5 x x u 12,5 0,975 25 25 165,42 1,96 1 165,42 165,42 1,96 1 2 2 x u
0,975
164,03 165,42 166,81 Co lze říci o výrobci, který uvádí jako svůj index způsobilosti hodnotu 1,155? *
index způsobilosti je podíl očekávané a skutečně dosažené směrodatné odchylky P LSL (Lower Specification limit) - spodní specifikační mez USL (Upper Specification limit) - horní specifikační mez. c
Pokud hodnota měřeného znaku x výrobku nepadne mezi tyto meze je výrobek považován za nevyhovující (NC). Protože pro rozmezí vyhovující jakosti výrobku se obvykle uplatňuje pravidlo 3 je pak: USL LSL USL LSL 6 * c P 6 LSL x USL Pokud znak x má normální rozdělení N(d,
m (USL LSL) 2 a F N P 2F ( 3c ) zmet N P
) platí: d (LSL USL) 2 a podíl zmetků P zmet je distribuční funkce normálního normovaného rozdělení 2
6
2F ( m N
)
pro c
P
1,155 : P zmet
ARL
2F ( 3, 465) 0,000530 530ppm (530 zmetků na 1000 000 výrobků) N 1 1 1887 (střední doba výskytu nevyhovujícího výrobku) P 0,000530 zmet
Existuje omezení pro vícerozměrná data, pro něž nelze použít Hotellingův regulační diagram? Platí obdobné statistické předpoklady jako pro ostatní regulační diagramy: Normalita Konstantnost µ a 2 Nezávislost dat Bez vybočujících hodnot
2. Tabulkový procesor – Microsoft Excel 2.1 Příklad 1: Analýza malých výběrů V doprovodném souboru s názvem Excel.xls je tento příklad vypracován v listu Analýza - malé výběry. Jedná se o jednoduchý nástroj pro výpočet střední hodnoty a intervalu spolehlivosti pro malé výběry (počet hodnot n=2 až 20) dle literatury [1]. Data pro výpočet se zadávají do barevně odlišených buněk v prvním sloupci, data nemusí být seřazeny vzestupně dle hodnot ani není nutné, aby byla data zadána do bezprostředně po sobě jdoucích buněk. Některé buňky mění formátování (zejména barvu pozadí a písma) podle určitých podmínek, toto je řešeno pomocí Podmíněného formátování (Conditional Formatting) v nabídce Formát. Pokud oblast pro zadání dat bude obsahovat méně než 2 číselné hodnoty, budou její buňky vyznačeny šedou barvou a v orámované oblasti v řádce 25 se nezobrazí žádný výsledek. Pro n = 2 a 3 se v prvním řádku objeví nápis :„Analýza malých výběrů - alternativní postup pro n = 2 a 3“ a oblast dat a některá další pole jsou zvýrazněna modře. Ve třetím sloupci se zobrazí pořadová statistika a ve druhém pak příslušná čísla indexů i a vyplněny budou také další buňky: počet hodnot n, pravděpodobnost [%] (ovšem s pevně zadanou hodnotou 95), hladina významnosti , střední hodnota µ, v buňce F18 bude aktivní volba N nebo R (normální či rovnoměrné rozdělení) a na ní navazuje zobrazená hodnota pro kvantil T a samozřejmě výsledek v řádce 25 v podobě střední hodnoty s dolní a horní mezí intervalu spolehlivosti. Pro n = 4 a více (maximálně 20 hodnot) se v první řádce zobrazí nápis: „Hornův postup pro malé výběry n = 4 až 20“ a barevně označená pole se zvýrazní zeleně. Kromě pořádkové statistiky a počtu hodnot n se zobrazí všechny charakteristické hodnoty pro Hornův postup. Jsou to hloubka pivotu H, dolní pivot x D, horní pivot xH, pivotová polosuma PL, pivotové rozpětí RL a samozřejmě nyní již je možno volit hodnotu pravděpodobnosti v [%], na níž a na počtu dat n závisí hodnota kvantilu Hornova rozdělení a pochopitelně i výsledek. Rovněž se v tabulce Hornových kvantilů zvýrazní vybraná hodnota. Kvůli jednoduššímu psaní vzorců jsou oblast dat a buňky obsahující hodnoty využívané pro výpočty pojmenovány. Ve vzorcích je často užíván argument v podobě “”,což znamená prázdnou hodnotu, která je vložena do dané buňky, pokud výsledek výpočtu v buňce není smysluplný. Tím se vyhneme tomu, že by se za určitých podmínek v některých buňkách objevovalo nějaké označení chyby.
7
Použité funkce: KDYŽ (IF) .............................. pro určení, co se má stát, když je či není zadaná podmínka splněna ŘÁDEK (ROW) ..................... vrací číslo řádky, ve které je daná buňka SMALL .................................. vrací k-tou nejmenší hodnotu v poli buněk; této funkce je užito pro sloupec pořadové statistiky CELÁ.ČÁST (INT) ................ zaokrouhlí na nejbližší nižší celé číslo; důležitá funkce pro výpočet hloubky pivotu v Hornově postupu USEKNOUT (TRUNC) ......... uříznout část čísla; využito pro rozhodování zda výraz INT((n+1)/2)/2 vrací celočíselnou hodnotu, opět důležité pro Hornův postup VVYHLEDAT (HLOOKUP) . nalezne hodnotu buňky v řádku; využito např. pro vyhledání odpovídajícího kvantilu v tabulce pro Hornovo rozdělení PRAVDA (TRUE).................. logická hodnota proměnné PRŮMĚR (AVERAGE) ......... vypočítá aritmetický průměr NEBO (OR) ............................ výsledek logického operátoru nebo, pro kombinaci více podmínek CONCATENATE................... slučuje textové hodnoty; pro vyjádření výsledku ABS ........................................ absolutní hodnota čísla SMODCH.VÝBĚR (STDEV) odhad směrodatné odchylky základního souboru určeného z náhodného výběru; objevuje se ve vzorci pro výpočet intervalu spolehlivosti u výběrů pro n= 3. ODMOCNINA (SQRT) ......... druhá odmocnina čísla
2.2 Příklad 2: Docházka V doprovodném souboru s názvem Excel.xls je tento příklad vypracován v listu Docházka. Poměrně detailně řešené zaznamenávání docházky s využitím Microsoft Excelu. První dva sloupce „datum“ a „den“ jsou vytvořeny rozkopírováním buněk. Uživatel vyplňuje pouze sloupce s nadpisem „příchod“, „odchod“ popř. „poznámka“. Pro lepší orientaci je aktuální den ve sloupci „datum“ zvýrazněn tučným podtrženým písmem se žlutým pozadím v buňce, zde je opět využito Podmíněné formátování (Conditional Formatting) v nabídce Formát. Buňky ve sloupci „počet hodin“ obsahují velmi rozsáhlý vzorec s vnořenými podmínkami. Zde je uveden počet odpracovaných hodin konkrétního dne, jednoduchý rozdíl času odchodu a příchodu je rozšířen o další podmínky potřebné pro správný výpočet na základě zavedených pravidel docházky jako jsou: 1. po 4,5 hodinách se odečte 0,5 hodiny na povinnou přestávku 2. při služebním příchodu (označí se ve sloupci „poznámka“ zkratkou „SP“) se jako čas příchodu počítá 7:30 3. při služebním odchodu (označí se ve sloupci „poznámka“ zkratkou „SO“) se jako čas odchodu počítá 15:30 4. při služebním příchodu i odchodu (označí se ve sloupci „poznámka“ zkratkou „SPO“) se užívá časů jako v bodě 2. a 3. 5. při služební cestě (označí se ve sloupci „poznámka“ zkratkou „SC“) se objeví jako počet odpracovaných hodin 8:00 6. při dovolené (označí se ve sloupci „poznámka“ zkratkou „D“) se odečte v buňce M2 počet takto označených dní od celkového počtu dnů na dovolenou (je nastaveno 25 dní) Ve sloupci doporučený odchod je uveden čas, který odpovídá 9 hodinám 25 minutám od příchodu do zaměstnání, tedy 5 minut před odečtením druhé povinné půlhodinové přestávky. Řádky s posledním dnem měsíce jsou ukončeny důležitou informací o plnění či neplnění fondu pracovní doby (tedy průměru 8 hodin na den). Tatáž informace pro aktuální měsíc je uvedena i v prvním řádku. Pro zaznamenání podrobnějších informací ve sloupci „poznámka“ je využito komentáře v buňkách, který je vkládán přes nabídku vložit/komentář a zobrazuje se ukázáním kurzorem myši na buňku. S pomocí nabídky Okno (Window) / Ukotvit příčky (Freeze Panes) je docíleno toho, že se rolují pouze řádky pod záhlavím. Použité funkce: KDYŽ (IF) .............................. pro určení, co se má stát, když je či není zadaná podmínka splněna MĚSÍC (MONTH).................. vrací pořadové číslo měsíce v roce 8
DNES (TODAY) .................... vrací systémové datum NEBO (OR) ............................ výsledek logického operátoru NEBO, pro kombinaci více podmínek A (AND) ................................. výsledek logického operátoru A, pro kombinaci více podmínek ČAS (TIME) ........................... velmi důležitá funkce pro zadávání časových údajů ve vzorcích v přehledném formátu (hodina;minuta;sekunda) SUMIF .................................... součet buněk v dané oblasti odpovídajících určité podmínce COUNTIF ............................... počet buněk v dané oblasti odpovídajících určité podmínce ABS ........................................ absolutní hodnota čísla ZAOKROUHLIT (ROUND) .. zaokrouhlit číslo na zadanou přesnost
2.3 Příklad 3: Příjezdy V doprovodném souboru s názvem Excel.xls je tento příklad vypracován v listech Příjezdy – kolo a Příjezdy kolo (graf). Jde o jednoduchý tabulkový a grafický záznam příjezdů do zaměstnání na kole. Uživatel vyplňuje tři zeleně zvýrazněné sloupce „Datum“, „Čas“ a „Průměrná rychlost“ z údajů na cyklocomputeru. Souhrnné údaje za jednotlivé roky se zobrazují v jednoduché kontingenční tabulce, která je vytvořena přes nabídku Data / Kontingenční tabulka a graf (PivotTable and PivotChart Report). Jsou v ní použity základní sumarizační funkce jako je součet, počet buněk, aritmetický průměr, maximální a minimální hodnota. Zároveň je u kontingenční tabulky nastaveno její aktualizování při otevírání excelovského souboru. Data jsou propojena s grafem na samostatném listu, který je vytvořen přes nabídku Vložit (Insert) / Graf (Chart). Formát grafu (popis a rozsah os, nastavení hlavních a vedlejších jednotek, nastavení mřížky, barva pozadí, velikost, tvar a barva bodů atd.) je jednoduše nastaven přes formáty jednotlivých součástí grafu, jejichž nabídka se zobrazí kliknutím levého tlačítka myši, je-li příslušná část grafu aktivní. Použité funkce: KDYŽ (IF) .............................. pro určení, co se má stát, když je či není zadaná podmínka splněna
2.4 Příklad 4: Najdi čísla v označeném poli buněk (makro) V doprovodném souboru s názvem Excel.xls Tento příklad je „makro“, což je vlastně procedura vytvořená v programovacím jazyce Visual Basic, který je součástí Microsoft Excelu. Programový kód je možno zadávat buď ručně v editoru VBA (zobrazí se ALT + F11) nebo prostřednictví nahrávání, kdy uživatel provádí různé operace a kód je souběžně automaticky zaznamenáván (nabídka Nástroje (Tools) / Makro (Macro) / Záznam nového makra (Record New Macro)). Jednoduchá tvorba maker nahráváním má však určitá omezení. Nelze jím docílit tvorby podmínek a cyklů, které jsou důležitou součástí většiny programů. Naopak zadávat kód ručně znamená znát přesnou syntaxi programovacího jazyka, proto je účelné při tvorbě makra užívat obou nástrojů. Toto makro provádí zdánlivě velmi jednoduchou věc: nalezne v označené oblasti buňky s číselnými hodnotami, které uživatel zadá prostřednictví jednoduchého okna. Nicméně jak bude vidět dále, může být programový kód poměrně složitý, pokud má řešit detailně různé situace, které mohou nastat. Toto makro je zároveň pěknou ukázkou zadávání podmínek a použití různých typů cyklů, stejně tak jako práce z různými typy proměnných. V tomto souboru je nastaveno spouštění makra klávesovou zkratkou CTRL + SHIFT + N, všechna makra v souboru se dají spouštět také přes nabídku Nástroje (Tools) / Makro (Macro) /Makra (Macros) / výběr makra (zde Hledani_cisel) / Spustit (Run). Pokud používáme nějaké makro velmi často je rozhodně vhodnější ho uložit jako doplněk Microsoft Excelu a vytvořit si pro něj vhodné tlačítko v panelu nástrojů. Takové makro je pak přístupné při otevření jakéhokoli excelovského souboru.
9
Příklad: 1. Označení oblasti buněk
2. Spuštění makra (CTRL+SHIFT+N) a zadání hledaných čísel
4. Oblast buněk s vyznačenými nalezenými čísly
3. Zpráva o počtu nalezených čísel
Kód makra (Lze ho zobrazit klávesovou zkratkou ALT + F11. Je dodržena obvyklá syntaxe včetně odsazování textu a barevného rozlišení. Modře jsou klíčová slova, zeleně a uvozené apostrofem jsou poznámky a černě je zbytek kódu): Sub Hledani_cisel() 'Najdi čísla v označeném poli buněk. 'Klávesová zkratka, kterou lze spustit proceduru: Ctrl+Shift+N 'Created 17.6.2005 by JF (http://mujweb.cz/www/freisleben)
Dim VybranaOblast As Range, p_znaku As Long, k As Long, i As Long, j As Long, c As Long Dim cislo() As Variant, nalezene_cislo() As Variant, kolikrat() As Variant, retezec As Variant Dim vysledek_hledani As Variant, vyber As Variant, r As Variant, v As Variant 'Klíčové slovo Dim uvozuje deklaraci proměnných s určením jejich formátového typu.
retezec = InputBox("Zadejte hledaná čísla oddělená nečíselnými znaky!" & _ vbCrLf & "Čárka (popř. tečka) je oddělovač desetinných míst!") 'Objeví se okno se žádostí o vložení hledaných čísel a vše se vloží do proměnné retezec.
If retezec = "" Then Exit Sub 'Pokud retezec neobsahuje žádný znak (je prázdný tedy nebylo nic zadáno), procedura se ukončí.
i = Selection.Count 'Do proměnné i se vloží počet buněk v označené oblasti.
Set VybranaOblast = Selection 'Proměnné VybranaOblast je přiřazena označená oblast buněk.
p_znaku = Len(retezec)
10
'Do proměnné p_znaku se vloží počet všech znaků v proměnné retezec.
ReDim cislo(1 To p_znaku) ReDim nalezene_cislo(1 To p_znaku) ReDim kolikrat(1 To p_znaku) 'Proměnným typu pole je nutné určit maximální rozsah.
k = 0: j = 1: cislo(1) = "" 'Zadání vstupních hodnot do proměnných.
Do 'Počátek vnějšího cyklu typu Do...Loop ("dělej něco zatímco platí podmínka za klíčovým slovem While nebo 'dokud neplatí podmínka za klíčovým slovem Until").
k=k+1 'k je argumentem pole proměnných cislo(k).
Do Until (IsNumeric(Mid(retezec, j, 1)) Or j >= p_znaku) 'První vnitřní cyklus Do...Loop pro nalezení pozice číselné hodnoty v proměnné retezec. 'Funkce IsNumeric je pro vyhodnocení zda argumentem je číslo. 'Funkce Mid(retezec, j, 1) vrací jeden znak, který se v proměnné retezec nachází na j-té pozici.
j=j+1 Loop If IsNumeric(Mid(retezec, j, 1)) Then 'Tři podmínky typu If...Then...End If pro 1) vložení nalezené číselné hodnoty do proměnné cislo(k)
cislo(k) = Mid(retezec, j, 1) If j > 1 Then '2) zjištění zda nalezené číselné hodnotě předchází znak
If Mid(retezec, j - 1, 1) = "-" Then '3)určení zda předcházející znak není mínus (identifikace záporného čísla) a 'jeho předřazení před číselnou hodnotu do proměnné cislo(k).
cislo(k) = Mid(retezec, j - 1, 1) & cislo(k) End If End If End If vyber = cislo(k) 'Zadání vstupní hodnoty do proměnné vyber.
Do While (IsNumeric(vyber) And j < p_znaku) 'Druhý vnitřní cyklus Do...Loop - řetězec znaků v proměnné vyber se bude rozšiřovat o následující znaky.
j=j+1 vyber = vyber & Mid(retezec, j, 1) If IsNumeric(vyber) Then 'Jestliže řetězec znaků v proměnné vyber bude číslo, vloží se jeho hodnota do proměnné cislo(k).
If Mid(retezec, j, 1) = " " Then 'Podmínky týkající se znaků: mezera, čárka a tečka jsou nutné, protože funkce IsNumeric vyhodnocuje 'jako jedno číslo i sekvenci znaků (např. 8 59 nebo 75. nebo 3, apod.)
Exit Do 'Znak mezery je chápán jako jiné znaky (např: středník, písmena atd.), která nejsou součástí čísla a 'proto prostřednictvím Exit Do vyskakujeme z probíhajícího druhého vnitřní cyklu Do...Loop.
ElseIf Mid(retezec, j, 1) = "." Or Mid(retezec, j, 1) = "," Then If j < p_znaku And IsNumeric(Mid(retezec, j + 1, 1)) Then 'Znak čárky nebo tečky je do řetězce čísla zahrnut jen v případě, že za ním následuje číselný znak.
cislo(k) = vyber Else Exit Do End If Else 'Else značí jiné případy než výše uvedené. Zde může nastat jen situace, 'že hodnocený znak je 0 nebo 1 nebo...9, proto je zahrnut do řetězce čísla.
cislo(k) = vyber End If End If Loop 'Loop vrací průběh na začátek cyklu, kterým je Do While (IsNumeric(vyber) And j < p_znaku). 'Při splnění podmínky za klíčovým slovem Do 'nebo použitím příkazu Exit Do uvnitř cyklu se ukončí běh procedury v tomto cyklu.
Loop While j < p_znaku 'U vnějšího cyklu Do...Loop je podmínka na konci, to znamená, že minimálně jednou musí cyklus proběhnout. 'Podmínka j < p_znaku nám určí moment, 'kdy už byly hodnoceny všechny znaky zadané na počátku do proměnné retezec.
If cislo(1) <> "" Then
11
'Tato podmínka řeší problém toho, pokud nebyl žádný znak v proměnné retezec vyhodnocen jako číselný.
c=0 'Zadání vstupní hodnoty. Proměnná c odpovídá počtu zadaných čísel, 'ke kterým bylo nalezeno číslo v označeném poli buněk.
For b = 1 To k 'Jiným typem cyklu s určeným počtem opakování je cyklus For...Next, 'za klíčovým slovem For je počáteční hodnota zvolené proměnné, 'kterou pak obvykle využíváme v argumentu jiných proměnných uvnitř cyklu a 'za klíčovým slovem To je počet opakování cyklu. 'Vnější cyklus probíhá přes vytvořené pole proměnných cislo(1), cislo(2) atd.
e=0 'Zadání vstupní hodnoty. Proměnná e odpovídá počtu kolikrát bylo konkrétní zadané číslo (nalezene_cislo(c)) 'nalezeno v označeném poli buněk.
For a = 1 To i 'Vnitřní cyklus - počet opakování je roven počtu buněk vybrané oblasti. 'Dochází postupně k výběru jedné buňky za druhou z označené oblasti buněk.
r = "A" & cislo(b): v = "A" & VybranaOblast(a) 'Před vlastním porovnáváním je dobré zavést pomocné proměnné (zde r a v), 'kde předřazením totožného nečíselného znaku (zde A) 'jsou hodnoty proměnných jednoznačně převedeny na formát textového řetězce,čímž se vyhneme problému, 'že při některých operací s proměnnými bohužel dochází i ke změně formátu z číselného na textový a 'porovnávané řetězce jsou pak vyhodnoceny jako různé. 'Proměnná VybranaOblast(1) odkazuje na první buňku označené oblasti, VybranaOblast(2) na druhou atd.
If r = v And v <> "A" Then 'Vlastní porovnávání zadaných čísel a čísel v buňkách vybrané oblasti. 'Výrazem v <> "A" vyloučíme z porovnávání prázdné buňky.
shoda = cislo(b) If c > 0 Then If shoda <> nalezene_cislo(c) Then c=c+1 nalezene_cislo(c) = cislo(b) End If Else c=c+1 nalezene_cislo(c) = cislo(b) End If e=e+1 'Při nalezené shodě se zadané číslo stává nalezeným, 'což je zajištěno uložením čísla do proměnné nalezene_cislo(c).
kolikrat(c) = e 'Při nalezení další buňky, ve které je právě testované zadané číslo, se hodnota proměnné e zvýší o 1 a 'zároveň se uloží do proměnné kolikrat(c).
With VybranaOblast(a) 'V buňce s shodným číslem dojde ke změně formátu.
.Interior.ColorIndex = 6 'Pozadí bude žluté.
.Font.ColorIndex = 3 'Písmo bude červené.
.Font.Bold = True 'Písmo bude tučné.
.Font.Underline = xlUnderlineStyleSingleAccounting 'Písmo bude podtrženo jednou čarou.
End With End If Next a 'Návrat na počátek vnitřího cyklu For...Next, pokud nebylo dosaženo zadaného počtu opakování.
Next b 'Návrat na počátek vnějšího cyklu For...Next, pokud nebylo dosaženo zadaného počtu opakování.
pocet_cisel = 0 'Vstupní hodnota pro proměnnou určující počet nalezených shodných čísel v poli buněk je logicky nula.
For d = 1 To c 'Poslední cyklus typu For...Next uloží do proměnné výsledek hledání informací o nalezených číslech.
vysledek_hledani = vysledek_hledani & vbCrLf & kolikrat(d) & " krát číslo " & nalezene_cislo(d) pocet_cisel = pocet_cisel + kolikrat(d) Next d End If
12
msgbox "počet všech nalezených čísel je: " & pocet_cisel & vbCrLf & vysledek_hledani 'Objeví se zpráva o nalezených číslech.
Selection.Columns.AutoFit 'Šířka sloupců označené oblasti se přizpůsobí minimální potřebné šířce buněk po změně jejich formátu.
End Sub
Literatura [1] Meloun M., Militký J.: Statistická analýza experimentálních dat. Academia, Praha 2004. [2] Walkenbach J.: Microsoft Excel vzorce a funkce. Mobil Media, a.s., 2001 [3] Walkenbach J.: Microsoft Excel 2000 Programování ve VBA. Computer Press, 1999 použitý software: QCExpert 2.5 (http://www.trilobyte.cz) Microsoft Excel 2000
13
