Gymnázium a Střední odborná škola, Rokycany, Mládežníků 1115 Číslo projektu: Číslo šablony: Název materiálu: Ročník: Identifikace materiálu: Jméno autora: Předmět: Tématický celek: Anotace: Datum:
CZ.1.07/1.5.00/34.0410 24 Databázové funkce 2. ročník WOH_52_24_D-funkce Ing. Bc. Martina Wohlrathová Informatika Seznamy Učební text s popisem použití databázových funkcí, ukázkami tabulek kritérií a s příklady na procvičení 21.3.2014
Databázové funkce Výhodou databázových funkcí je snadné použití u tisíce záznamů. Jejich názvy začínají písmenkem D, např DMAX, DMIN atd. Všechny databázové funkce vyžadují zadat tři parametry: 1) Databáze – odkaz na zkoumaný seznam, vyplatí se tento seznam předem pojmenovat, potom stačí vyplnit jeho název 2) Pole – název sloupce, ve kterém se funkce spočítá. Lze kliknout v záhlaví tabulky na název sloupce nebo napsat pořadové číslo sloupce zleva 3) Kritéria – odkaz na oblast buněk, obsahující kritéria Do výpočtu databázové funkce jsou zahrnuty buňky ve sloupci Pole, které splňují podmínky uvedené v tabulce Kritéria (přičemž dané pole je součástí tabulky zadané v parametru Databáze)
Jak vytvořit tabulku kritérií? Tabulku kritérií umístíme pod tabulku v případě, že nebudeme přidávat další záznamy. V prvním řádku zadáme názvy polí (záhlaví sloupců), ve kterých stanovujeme podmínky. Doporučuje se tyto názvy z tabulky kopírovat. V dalších řádcích zapisujeme pro jednotlivá pole podmínky a dodržujeme při tom tato pravidla: s podmínky, které mají být splněny zároveň (spojeny pomocí AND), zapisujeme na stejný řádek tabulky kritérií s podmínky spojené pomocí OR (platí jedna nebo druhá) uvádíme do různých řádků s nevynecháváme prázdné řádky, neboť mají také význam podmínky (podmínky, kterou splňují všechny záznamy) Zápis podmínek:
jako u filtrů – např.
můžeme použít zástupné znaky * a ?
>10000 větší než <>100 nerovno <=18 menší nebo rovno 20000 je rovno 20000 střední je rovno střední p* vše začínající na p 5? např. všechna čísla od 50 do 59 = H10>PRŮMĚR($H$10:$H$47) podmínka např. pro všechny, kteří mají plat větší než průměrný
můžeme použít výpočet (vzorec) pozn. Název pole Plat je v buňce H9. Vzorec je Excelem kopírován při prozkoumávání, proto odkaz ve výpočtu průměru je absolutní. Název sloupce tabulky kriterií s takovou podmínkou (vzorcem) se nesmí shodovat s žádným názvem pole databáze, může se však použít prázdná buňka. Ve vzorci použijeme relativní adresu první buňky pole (H10), na které se odkazujeme, ostatní odkazy musí být absolutní. Podíváme se na použití těchto databázových funkcí: DMAX, DMIN, DSUMA, DPOČET, DPRŮMĚR, DZÍSKAT
Více na listu Ukázky. pozn. Údaje v tabulce sloužící k ukázce a procvičení jsou smyšlené, výsledky zjištěné funkcemi nemají žádnou vypovídací hodnotu!
pořadí 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
příjmení Horáček Mladý Korn Jará Novotná Sabová Rolník Daněk Štrunc Šebková Šebová Kolomazník Novotný Moudrá Klapka Klapková Řezníček Fialová Trhlíková Boudová Skopec Šťastný Švec Havlíček Karpíšek Svobodová Veselý Šedivý Bílá Červený Novák Svobodová Vintíř Plachý Franěk Svatuška Slaninka Masný Svojtka Maňas Pilous Hrobařík Kovář Nekovář Hrbatý Sivá Mladý Stará Ledajaksová Pilný
jméno David Josef Petr Radka Ilona Markéta Martin Jiří Josef Jana Bohumila Dan Petr Jiřina Josef Stanislava David Hana Zlata Helena Petr Petr Josef Karel Martin Dana Jiří Karel Jana Martin Daniel Iveta Josef Jiří Jaroslav Marek Josef Martin Albert Petr Luboš Martin Jiří Petr Josef Anna Jiří Iris Dagmar Josef
datum narození 11. září 1972 3. srpen 1978 6. červenec 1967 23. květen 1956 27. únor 1979 19. březen 1971 24. červen 1965 18. květen 1982 31. leden 1979 14. červenec 1969 7. duben 1972 15. listopad 1978 13. únor 1985 25. květen 1976 21. červen 1968 4. říjen 1967 29. červen 1988 27. prosinec 1986 16. červen 1986 12. prosinec 1967 11. září 1976 17. srpen 1972 30. květen 1983 8. červen 1969 23. duben 1985 11. březen 1983 13. listopad 1980 15. prosinec 1979 20. říjen 1965 8. srpen 1967 15. leden 1979 22. únor 1978 14. září 1965 7. září 1976 5. srpen 1980 30. leden 1981 16. duben 1974 27. říjen 1982 5. prosinec 1985 6. červen 1984 6. červenec 1979 3. červenec 1989 4. květen 1976 27. duben 1973 14. srpen 1979 3. květen 1969 14. březen 1990 11. prosinec 1983 6. listopad 1977 25. únor 1980
město Brno Plzeň Plzeň Praha Plzeň Brno Praha Rokycany Praha Plzeň Rokycany Brno Praha Brno Rokycany Plzeň Brno Ostrava Praha Rokycany Plzeň Ostrava Rokycany Ostrava Brno Ostrava Praha Ostrava Rokycany Plzeň Ostrava Brno Praha Teplice Ostrava Plzeň Brno Brno Rokycany Teplice Praha Ostrava Teplice Praha Plzeň Rokycany Ostrava Plzeň Rokycany Plzeň
pohlaví m m m ž ž ž m m m ž ž m m ž m ž m ž ž ž m m m m m ž m m ž m m ž m m m m m m m m m m m m m ž m ž ž m
počet dětí 1 2 1 0 0 3 3 1 1 1 2 2 1 5 4 2 2 1 0 0 0 1 0 1 0 2 2 1 0 0 1 1 1 2 2 1 2 3 3 0 0 2 2 1 1 2 2 3 3 1
zaměstnání účetní obchodník asistent učitel prodavačka asistent řidič obchodník asistent účetní prodavačka ředitel návrhář prodavačka obchodník účetní asistent asistent účetní ředitel řidič návrhář asistent učitel obchodník účetní návrhář pokrývač účetní ředitel obchodník učitel ředitel učitel účetní řidič pokrývač asistent pokrývač učitel obchodník účetní ředitel asistent učitel ředitel řidič účetní prodavačka ředitel
mzda 21500 34000 35000 22000 25000 36000 38000 29000 48000 21000 14000 89000 67000 21000 43000 23000 45000 34000 19500 35000 16500 56000 29500 25000 32000 30000 49000 18000 28000 46000 29000 24000 185000 29000 25000 21000 29000 34000 21500 27000 27000 23000 67000 41000 26000 54000 15000 23500 18000 79000
věk 41 35 46 57 35 43 48 32 35 44 42 35 29 37 45 46 26 27 28 46 37 41 31 44 29 31 33 34 48 46 35 36 48 37 33 33 40 31 28 30 34 25 38 41 34 44 24 30 36 34
výška 187 190 195 163 167 170 192 198 195 175 172 187 198 178 195 175 186 168 161 178 180 197 187 189 186 174 194 180 175 176 179 168 180 198 186 179 180 189 175 176 179 180 182 187 179 178 197 169 163 186
váha 98 87 89 65 54 64 87 97 105 62 59 78 96 71 84 69 81 75 59 62 79 96 89 108 96 67 89 84 65 83 90 64 85 87 91 90 109 78 87 80 97 92 75 99 86 73 89 59 56 79
vzdělání středoškolské vysokoškolské středoškolské vysokoškolské vysokoškolské středoškolské vysokoškolské středoškolské vysokoškolské středoškolské středoškolské vysokoškolské vysokoškolské základní středoškolské středoškolské středoškolské vysokoškolské středoškolské vysokoškolské základní vysokoškolské středoškolské vysokoškolské středoškolské středoškolské středoškolské základní středoškolské vysokoškolské středoškolské vysokoškolské vysokoškolské vysokoškolské středoškolské středoškolské vysokoškolské středoškolské základní vysokoškolské vysokoškolské středoškolské středoškolské vysokoškolské vysokoškolské vysokoškolské základní středoškolské středoškolské vysokoškolské
Ukázka tabulek kritérií Funkce se spočítá ze všech záznamů, u kterých je v poli jméno údaj Josef, jinými slovy pro všechny Pepíky.
jméno Josef
... lidé z Teplic se základním nebo město Teplice Teplice
vzdělání základní středoškolské
věk >30
...starší než 30 let
město Praha Brno
počet dětí 0
Kdyby zde nebylo zapsáno Teplice (např. prázdná buňka), šlo by o středoškolsky
...všichni s bydlištěm v Praze nebo v Brně
příjmení
...s příjmením od M
M datum narození <1.1.1970
...narozeni před rokem 1970
... všichni s počtem dětí 0.
zaměstnání ředitel asistent asistent ředitel
... všichni ředitelé nebo asistenti, kteří mají jedno nebo dvě děti. (Čteme podmínky po řádcích: ředitelé s jedním dítětem, nebo asistenti se dvěma, nebo asistenti s jedním, nebo ... atd.
počet dětí 1 2 1 2
pořadí ...pro všechny záznamy. Lze použít libovolné pole (název sloupce)
pohlaví m m
DMAX
14. březen 1990
DMIN
14000
DSUMA
12
DPRŮMĚR
84,5
DPOČET2 DPOČET
DZÍSKAT
5 11
Luboš
mzda >50000
výška >190
Datum narození nejmladší osoby (největší datum) Nejnižší mzda osob starších 30 let Počet dětí dohromady všech Josefů Průměrná hmotnost lidí z Brna a Prahy Počet lidí, jejichž příjmení začíná na M (excel sám doplní za M hvězdičku) Počet bezdětných. (Tato funkce zjišťuje počet buněk ve sloupci s čísly splňující podmínku, na rozdíl od funkce DPOČET2) Křestní jméno Pilouse (funkce oznamuje chybu, pokud se s kritérii neshoduje žádný záznam, nebo se shoduje víc než jeden záznam.
příjmení Pilous
... všichni muži s platem vyšším než 50 000 Kč nebo s výškou těla větší než 190 cm.
pořadí 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
příjmení jméno Horáček David Mladý Josef Korn Petr Jará Radka Novotná Ilona Sabová Markéta Rolník Martin Daněk Jiří Štrunc Josef Šebková Jana Šebová Bohumila KolomazníkDan Novotný Petr Moudrá Jiřina Klapka Josef Klapková Stanislava Řezníček David Fialová Hana Trhlíková Zlata Boudová Helena Skopec Petr Šťastný Petr Švec Josef Havlíček Karel Karpíšek Martin Svobodová Dana Veselý Jiří Šedivý Karel Bílá Jana Červený Martin Novák Daniel Svobodová Iveta Vintíř Josef
datum narození 11. září 1972 3. srpen 1978 6. červenec 1967 23. květen 1956 27. únor 1979 19. březen 1971 24. červen 1965 18. květen 1982 31. leden 1979 14. červenec 1969 7. duben 1972 15. listopad 1978 13. únor 1985 25. květen 1976 21. červen 1968 4. říjen 1967 29. červen 1988 27. prosinec 1986 16. červen 1986 12. prosinec 1967 11. září 1976 17. srpen 1972 30. květen 1983 8. červen 1969 23. duben 1985 11. březen 1983 13. listopad 1980 15. prosinec 1979 20. říjen 1965 8. srpen 1967 15. leden 1979 22. únor 1978 14. září 1965
město Brno Plzeň Plzeň Praha Plzeň Brno Praha Rokycany Praha Plzeň Rokycany Brno Praha Brno Rokycany Plzeň Brno Ostrava Praha Rokycany Plzeň Ostrava Rokycany Ostrava Brno Ostrava Praha Ostrava Rokycany Plzeň Ostrava Brno Praha
pohlaví m m m ž ž ž m m m ž ž m m ž m ž m ž ž ž m m m m m ž m m ž m m ž m
počet dětí 1 2 1 0 0 3 3 1 1 1 2 2 1 5 4 2 2 1 0 0 0 1 0 1 0 2 2 1 0 0 1 1 1
zaměstnání účetní obchodník asistent učitel prodavačka asistent řidič obchodník asistent účetní prodavačka ředitel návrhář prodavačka obchodník účetní asistent asistent účetní ředitel řidič návrhář asistent učitel obchodník účetní návrhář pokrývač účetní ředitel obchodník učitel ředitel
mzda 21500 34000 35000 22000 25000 36000 38000 29000 48000 21000 14000 89000 67000 21000 43000 23000 45000 34000 19500 35000 16500 56000 29500 25000 32000 30000 49000 18000 28000 46000 29000 24000 185000
věk 41 35 46 57 35 43 48 32 35 44 42 35 29 37 45 46 26 27 28 46 37 41 31 44 29 31 33 34 48 46 35 36 48
výška 187 190 195 163 167 170 192 198 195 175 172 187 198 178 195 175 186 168 161 178 180 197 187 189 186 174 194 180 175 176 179 168 180
váha 98 87 89 65 54 64 87 97 105 62 59 78 96 71 84 69 81 75 59 62 79 96 89 108 96 67 89 84 65 83 90 64 85
vzdělání středoškolské vysokoškolské středoškolské vysokoškolské vysokoškolské středoškolské vysokoškolské středoškolské vysokoškolské středoškolské středoškolské vysokoškolské vysokoškolské základní středoškolské středoškolské středoškolské vysokoškolské středoškolské vysokoškolské základní vysokoškolské středoškolské vysokoškolské středoškolské středoškolské středoškolské základní středoškolské vysokoškolské středoškolské vysokoškolské vysokoškolské
34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
Plachý Jiří Franěk Jaroslav Svatuška Marek Slaninka Josef Masný Martin Svojtka Albert Maňas Petr Pilous Luboš Hrobařík Martin Kovář Jiří Nekovář Petr Hrbatý Josef Sivá Anna Mladý Jiří Stará Iris LedajaksováDagmar Pilný Josef
7. září 1976 5. srpen 1980 30. leden 1981 16. duben 1974 27. říjen 1982 5. prosinec 1985 6. červen 1984 6. červenec 1979 3. červenec 1989 4. květen 1976 27. duben 1973 14. srpen 1979 3. květen 1969 14. březen 1990 11. prosinec 1983 6. listopad 1977 25. únor 1980
Teplice Ostrava Plzeň Brno Brno Rokycany Teplice Praha Ostrava Teplice Praha Plzeň Rokycany Ostrava Plzeň Rokycany Plzeň
Použitím databázových funkcí zjistěte: Průměrný plat vysokoškolsky vzdělaných mužů Nejvyšší mzda Datum narození osoby s nejvyšší mzdou Počet učitelů z Prahy Výška nejmenší osoby Hmotnost všech účetních dohromady :) Průměrný věk žen s jedním nebo žádným dítětem Jaké má pořadové číslo Jiří Mladý Kolik osob má hmotnost menší než průměrnou Kolik lidí z Plzně je starších než průměrný věk v Praze
m m m m m m m m m m m m ž m ž ž m
2 2 1 2 3 3 0 0 2 2 1 1 2 2 3 3 1
výsledky 52875 185000 14.9.1965 1 161 662 40 47 23 4
učitel účetní řidič pokrývač asistent pokrývač učitel obchodník účetní ředitel asistent učitel ředitel řidič účetní prodavačka ředitel
29000 25000 21000 29000 34000 21500 27000 27000 23000 67000 41000 26000 54000 15000 23500 18000 79000
37 33 33 40 31 28 30 34 25 38 41 34 44 24 30 36 34
198 186 179 180 189 175 176 179 180 182 187 179 178 197 169 163 186
87 91 90 109 78 87 80 97 92 75 99 86 73 89 59 56 79
vysokoškolské středoškolské středoškolské vysokoškolské středoškolské základní vysokoškolské vysokoškolské středoškolské středoškolské vysokoškolské vysokoškolské vysokoškolské základní středoškolské středoškolské vysokoškolské
