Informatika I.
Széchenyi István Egyetem
12. Előadás Táblázatkezelés – A táblázat mint adatbázis
Dr. Kallós Gábor
2015–2016 11
Informatika I.
Széchenyi István Egyetem
Tartalom Meghatározások Műveletek Rendezés Szűrés AutoSzűrő Irányított szűrő Speciális használati esetek
Adatbázis-kezelő függvények Kimutatások
2
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis Meghatározások Az Excelben egy adatoszlopokat tartalmazó, fejléccel rendelkező táblázat sok esetben úgy is tekinthető, mint egy adatbázis Ilyenkor a táblázat sorait (ahol logikailag összetartozó, különböző típusú adatok találhatók) rekordoknak, egyes celláit pedig egy-egy rekord mezőinek nevezzük A mezőneveket az első sor tartalmazza
Adatbázis blokk: szigorúbb szabályok, mint egy általános Excel táblázatnál Utóbbiban lehet két teljesen azonos sor vagy oszlop, egy adatbázis blokkban nem Az adatbázis blokkban nem szerepelhetnek megegyező azonosítójú oszlopok (hasonlóan: teljesen üres sor vagy oszlop sem) Az adatbázis blokkban is lehet egy vagy több üres cella
Az adatbázis blokkot célszerű elnevezni
3
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – műveletek Adatbázis szinten végrehajtható műveletek: Új adat (rekord vagy mező) beszúrása Rekord(ok) törlése Rekord illetve mező módosítása Rekordok sorba rendezése Bizonyos tulajdonságú rekordok leválogatása, szűrése Speciális műveletek a szűrt rekordokkal Kimutatások készítése (Kapcsolat „igazi” adatbázis-kezelőkkel, pl. export/import – nem tanuljuk most) Az első három műveletet már ismerjük, ezeket most külön nem tárgyaljuk
4
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – rendezés A rendezés menete Kijelölés – teljes táblázat, vagy álljunk a belsejébe (hogy az Excel felismerje azt) Indítás: Adatok/Rendezés és szűrés/Rendezés Fejlécsor (mezőazonosítók sora) ellenőrzése – ez ne kerüljön bele a rendezendő adatok közé Módosítás: Az adatok fejlécet tartalmaznak
Rendezési szempont megadása – melyik oszlop szerint történjen a rendezés (Rendezés ablak; legördülő listából mezőnév) Általában értékek szerint rendezünk A rendezés jellege emelkedő vagy csökkenő lehet
5
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – rendezés A rendezés menete (folyt.): Előfordulhat, hogy az első rendezés után még más szempontok szerinti további rendezésre is szükség van Újabb szint gomb
A második rendezési szabály akkor dönt, ha az első szabály holtversenyt eredményezett Újdonság (E 10): színek szerint is rendezhetünk
A rendezés (egy szempontsor – oszlop szerint) az A→ →Z, Z→ →A gombokkal is végrehajtható A fejléc itt se kerüljön bele a rendezendő adatok közé A rendezés az aktuális cella oszlopa szerint történik
6
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – szűrés Az adatbázis egyes rekordjainak kiválogatása, szűrése AutoSzűrő Helyben, csak az adatbázis és a szűrő menüjének a felhasználásával dolgozik
Irányított szűrő Egy kimeneti blokkba vagy az eredeti táblázat helyére készíti el a végeredményt A szűrési feltételek külön blokkban adandók meg
AutoSzűrő (a használat lépései) Adatbázis kijelölése/aktiválása (teljes táblázat vagy csak egy cella) Adatok/Rendezés és szűrés/Szűrő bekapcsolása Ismételt aktiválás kikapcsolja az AutoSzűrőt
A szűrési szempontok beállítása Legördülő listák az egyes mezőnevekhez, a megfelelő szűrési feltételek kiválaszthatók ill. megadhatók Több különböző mezőre együtt beállított feltételek között logikai „és” kapcsolat van (Ugyanitt rendezés is kérhető az adott mező oszlopa szerint)
7
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – szűrés AutoSzűrő (folyt.)
Az egyes mezőnevekhez megadható szűrési lehetőségek a következők (E 03): mind: az összes rekord megjelenik Üres: azok a rekordok jelennek meg, amelyeknél ez a mező üres NemÜres: azok a rekordok jelennek meg, amelyeknél ez a mező nem üres egy konkrét érték: azok a rekordok jelennek meg, amelyeknél a mező értéke megegyezik a kiválasztott elemmel Egyéni…: saját szűrőfeltételt állíthatunk be (relációkkal, ill. szöveges adatok esetén a dzsóker jelek (*, ?) használhatók). Összetett feltételeket az És, ill. a Vagy kapcsológombokkal állíthatunk be. Pl. ?A* (szöveges típusú cellákra)
8
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – szűrés AutoSzűrő (folyt.) E 10: az AutoSzűrő felismeri a szűrni kívánt adatok – aktuális oszlop – típusát (szám, szöveg, dátum), ezért speciálisabb (és részben bővebb) lehetőségeket kínál a megfelelő szám-, szöveg- és dátumszűrő segítségével
9
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – szűrés AutoSzűrő (folyt.) Dátumszűrő példa: januári kölcsönzések
10
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – szűrés Irányított szűrő Adatbázis (listatartomány) Szűrőtartomány (kritérium tábla) A szűrés előtt nekünk kell létrehoznunk Első sora azokat a mezőneveket tartalmazza, amelyekre feltételt szeretnénk szabni. Ez alatt következnek az egyes mezőnevekre vonatkozó feltételek. Megadásukhoz dzsóker jeleket és relációkat is használhatunk, mint az AutoSzűrő esetén Üres sor, mint „feltétel”: biztosan hibás, mert minden rekord kielégíti!
Megj.: egy adott mezőnév többször is szerepelhet a szűrőtartományban („és” kapcsolat)
Kimeneti blokk (hova másolja) Ha nem helyben szűrünk, akkor szükséges megadni Ez a blokk a kilistáztatni/megjeleníteni kívánt mezők neveit tartalmazza, alatta megfelelő üres területtel, ahová a gép az eredménylistát elhelyezi Fejlécét a szűrés előtt nekünk kell létrehoznunk (ha az nem a teljes adatbázis fejléce – ekkor elég egy üres cellát megadni a szűrésnél) 11
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – szűrés Irányított szűrő (folyt.) A szűrőtartomány feltételei Relációs jelek (számtípusú cellákra) <, >, <=, >=, <> (az = jel képleteknél kell) A reláció szerepel a megadás elején! Pl. >=1500, 300
Dzsóker karakterek (szöveges típusú cellákra) ?, * (tudjuk) Pl. ?A* vagy egyszerűen ?A, ??? (legalább 3 karakteres szöveg), <>* A kis és nagybetűk között itt nem tesz különbséget az Excel
Egyenlőségjel Szövegként: pontos egyezésre keres; pl. '=Győr, '=?? (pontosan két karakter) Képletként: számított érték; pl. = (üres cellák), =C2>Átlag(C$2:C$7) (fejléc: nem létező oszlopnév!!!)
Összetett logikai feltétel esetén az „és” kapcsolatot egymás mellé írással, a „vagy” kapcsolatot egymás alá írással jelezzük Bonyolult szűrési feltétel megadása időigényes lehet (!) 12
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – szűrés Irányított szűrő (folyt.) A használat lépései: Adatok/Rendezés és szűrés/Speciális (E 10) Helyben szűrje ill. Más helyre másolja gomb bekapcsolása A blokkok megadása/kijelölése A blokkokat fejléccel együtt kell megadni A mezőnevek mindhárom használt blokkban pontosan ugyanazok legyenek (eltérő szóköz vagy ékezet sem lehet; létrehozás célszerűen: másolás, hivatkozás) A szűrőtartomány tartalmazhat felesleges mezőneveket is (amelyekre nem adunk meg szűrési feltételt), de törekedjünk a minimális szűrőtartomány használatára
Ha szükséges: csak egyedi rekordok kiszűrése 13
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – szűrés Irányított szűrő (folyt.) Helyben szűrés után az Adatok/Rendezés és szűrés/Szűrők törlése (E 10) alponttal lehet visszaállítani a teljes adatbázis láthatóságát Ha a kimeneti blokknál nemcsak fejlécet, hanem nagyobb területet is kijelölünk (output), akkor az Excel csak a megadott részt használja a lista elkészítésére Ha erre a területre nem fér rá minden – a feltételnek eleget tevő – rekord, akkor a továbbiak elveszhetnek ebben a kigyűjtésben – erről üzenetet kap(hat)unk
Új szűrésnél célszerű új kimeneti blokkot készítenünk, ha a régit használjuk, akkor a régebben szűrt adatok elvesznek (!) (Persze csak: ha szükség van a korábbi adatokra)
Az adatbázis változása esetén az Excel nem frissíti automatikusan a már korábban kiszűrt adatokat. Ha a változást követni kell, akkor újra kell szűrni. A kiszűrt adatok mindig a készítéskori állapotot őrzik meg
Az irányított szűrésnél használt blokkokat sokszor célszerű különböző munkalapokon tárolni (Nem elegáns pl. a szűrési feltételeket az adatbázis blokk mellett megadni) 14
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – szűrés Irányított szűrő (folyt.) Több munkalapon elhelyezkedő blokkok esetén a szűrést mindig arról a munkalapról kell indítani, ahol a kimeneti blokk elhelyezkedik. Ellenkező esetben a szűrés végén hibaüzenetet kapunk (a szűrt adatok csak az aktív munkalapra kerülhetnek). A munkalapon belül sem mindegy, hogy hol állunk (a kimeneti blokk belsejében: szintén hibaüzenet). Legjobb, ha egy vagy két üres sort kihagyunk a kimeneti blokk alatt, és onnan kezdjük a szűrést. 15
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – szűrés Irányított szűrő (folyt.) Feladat: Kettős keresztnevű hallgatók kilistázása, akik valamelyik zh-t 5 pontnál többre írták meg
16
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – szűrés Irányított szűrő (folyt.) Feladat: Melyik osztálynak mikor van hétfőn matek vagy csütörtökön földrajzórája?
17
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – szűrés Irányított szűrő (folyt.) Feladat: Melyik osztálynak mikor van hétfőn matek vagy csütörtökön földrajzórája? (folyt.)
18
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – szűrés Irányított szűrő/A használat speciális esetei Feladat: Mely termekben tartják az egyes tárgyakat? Csak az egyedi rekordokra van szükségünk (tantárgy és terem)
19
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – szűrés Irányított szűrő/A használat speciális esetei Feladat: Mely termekben tartják az egyes tárgyakat? (folyt.) Rendezés
20
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – szűrés Irányított szűrő/A használat speciális esetei Győri lakosok
21
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – szűrés Irányított szűrő/A használat speciális esetei (folyt.) Győri lakosok/2.
22
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – szűrés Feladatok irányított szűrővel Egy dvd kölcsönző a kölcsönzésekről a következő adatokat tárolja: kölcsönzött film, ügyfél neve, ügyfél lakcíme (város, utca, házszám), kölcsönzési idő, kölcsönzési díj. Listáztassuk ki a győri lakosok által kikölcsönzött filmek közül azokat, amelyek 3 napnál tovább vannak kint; azon Eszter vagy Erika keresztnevű ügyfelek által kikölcsönzött filmeket, akik a Liszt Ferenc utcában laknak.
Egy autókereskedés az autókról a következő adatokat tárolja: név (azonosító), szín, évjárat és ár (millió Ft-ban). Ki kell listáztatnunk a zöld vagy piros színű, 1990-es években gyártott, 1 és 2 millió Ft közötti áru autókat. Értelmezzük a következő szűrőtartományt: ügyfél neve
ügyfél lakcíme
Tamás
* Liszt Ferenc utca * Bartók Béla út 23
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – szűrés
24
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – adatbázis-kezelő függvények Az adatbázis blokk bizonyos feltételeknek eleget tevő rekordjait nemcsak listáztatni lehet, hanem további műveleteket is végezhetünk velük. Erre szolgálnak az adatbáziskezelő függvények. A függvények általános alakja: AB.függvénynév(adatbázis; mező; feltétel tábla) Az adatbázis blokk és a feltétel tábla ugyanolyan, mint az irányított szűrésnél. A mező paramétert betűvel vagy sorszámmal is megadhatjuk. A táblázatkezelők itt gyakran elfogadják a fejléc-mezőnevet is, idézőjelek között (pl. "Összes nettó jövedelem") Hibához vezet ugyanakkor, ha a betűvel történő azonosításkor 1-től különböző sorszámot adunk meg (pl. Adatok!I2; az „eredmény”: #ÉRTÉK!)
A szűrési feltételnek eleget tevő rekordokon a leggyakrabban a következő műveleteket végezzük el: Számlálás Összegzés ill. átlagszámítás Maximum- illetve minimumkeresés A megfelelő függvények: AB.Darab, AB.Darab2 (hasonló: Darabteli) az egyik a számértékű, a másik pedig a nem üres cellákat számolja össze a megadott oszlopban
AB.Szum (hasonló: Szumha) ill. AB.Átlag AB.Max, AB.Min
25
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – adatbázis-kezelő függvények Az adatbázis-kezelő függvények fontos tulajdonsága, hogy – hasonlóan más függvényekhez – az adatbázis változása esetén automatikusan frissítik értéküket (V.ö.: irányított szűrés: Nem fixen a „készítéskori” állapotot mutatják, hanem mindig az aktuálisat!)
(Az adatbázis-kezelő függvények sok esetben kiválthatók autoszűréssel és a megfelelő egyszerű statisztikai függvénnyel – lásd ábra is) 26
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – adatbázis-kezelő függvények Feladatok adatbázis-kezelő függvényekkel Az autókereskedéses adatbázist használva függvény segítségével válaszoljunk az alábbi kérdésekre: Hány darab zöld autónk van? Mennyi a piros autók átlagos ára? Mennyibe kerül a legdrágább piros autó? Hány darab olyan zöld autónk van, amit az 1990-es években gyártottak? Hány darab olyan piros vagy zöld autónk van, amit az 1990-es években gyártottak?
A dvd kölcsönzős adatbázist használva függvény segítségével válaszoljunk az alábbi kérdésekre: Mennyi volt az egyes kategóriás filmek kölcsönzése utáni összes bevétel? Mennyi volt az egyes évekbeli kölcsönzések utáni összes bevétel? Mennyi volt az egyes évekbeli kölcsönzések utáni átlagos bevétel? (Rakjuk az egyes éveket összbevételük szerint növekvő sorrendbe!) 27
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – adatbázis-kezelő függvények
28
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – kimutatások Gyakran kell ismétlődő adatelemeket tartalmazó listákból olyan táblázatot készíteni, amely bizonyos szempontok szerint csoportosítja és összegzi az adatokat (kimutatás) Eszköz az Excelben: kimutatás Indítás Beszúrás/Kimutatás ill. Kimutatásdiagram (E 10)
Feladat: Egy nagykereskedő a megrendeléseiről a következő adatokat tartja nyilván: a megrendelő neve, a megrendelés tárgya, mennyisége, ideje (hónap), valamint a megrendelés teljesítését végző alkalmazott neve. Készítsünk kimutatást és összesítést a megrendelésekről a következő szempontok szerint. Legyen leolvasható a kimutatásról, hogy ki volt a megrendelést elintéző munkatárs, milyen terméket és mikorra rendelt meg a megrendelő. 29
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – kimutatások
30
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – kimutatások A kimutatások készítésének általános lépései Elkészítjük az alaptáblázatot (megfelelő fejléccel) Kijelöljük a táblázatot vagy beleállunk, és a menüszalagról elindítjuk a varázslót (Kimutatás létrehozása ablak): megadandó a kimutatás helye, és megadható még az alaptáblázattól eltérő külső adatforrás Az alapértelmezett hely egy új munkalap
Struktúra kialakítása: a mezők behúzással sor- és oszlopcímkékbe, ill. az értékekhez kerülhetnek (jobb oldal, fent és lent) Sor- és oszlop: megfelelő tervezéssel döntünk (a feladat, ill. a célok alapján) Egy címkéhez akár több mező is kerülhet A Σ értékek alapján hajtódik végre a számolás Ha a kialakításnál hibázunk (rosszul döntünk), a korrekció egyszerű
A kimutatástáblázat adatainak megjelenítése szűrhető AutoSzűrő funkció és jelentésszűrő (lásd köv. slide)
A kimutatás általában automatikusan frissül, de manuálisan is frissíthető 31
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – kimutatások További jellegzetességek, specialitások A jelentésszűrőbe felvett mezőnevek segítségével AutoSzűrő funkciót tudunk hozzárakni a jelentéshez, így az egyébként már kész kimutatás adatainak megjelenését korlátozhatjuk bizonyos szempontok alapján Néhány más funkció, pl. rendezés is lehetséges
A Σ értékekhez felvett mezőkön (értékmező-beállítások) a kimutatás alapértelmezés szerint darabszámot (szöveges adatokra) vagy összeget (szám típusú adatokra) számol. A számolás alapját lehet módosítani, és szükség esetén átlagot, minimumot, maximumot… is számoltathatunk ily módon. Nemcsak a táblázatban lévő mezőkkel lehet kalkulálni a kimutatás során, hanem új számított mezőt is be lehet szúrni. Ez azért kényelmes, mert így nem kell a táblázatunkba új segédoszlopot felvenni a különböző számításokhoz. A számított mező készítésénél függvényeket is lehet használni (kivétel: változó eredményt adó típusok, pl. Vél()) 32
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – kimutatások Kimutatás készítése (E 10) – létrehozás
33
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – kimutatások Kimutatás készítése (E 10) – kezdőállapot
34
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – kimutatások Kimutatás készítése (E 10) – összeállítás
35
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – kimutatások Kimutatás készítése (E 10) – kimutatásdiagram
36
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – kimutatások Újdonságok/változások a 2010-es Excelben (folyt.) Beállítások: a kész kimutatás még finomítható
37
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – kimutatások Osztálylétszám megállapítása
38
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – kimutatások Ki mennyi órát tart az egyes termekben?
39
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – kimutatások Hányszor kölcsönözték ki az egyes filmeket?
40
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – kimutatások Mekkora bevételt hoztak az egyes filmek?
41
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – kimutatások Feladatelemzés: hogyan sikerült válaszolni az egyes kérdésekre?
42
Informatika I.
Széchenyi István Egyetem
A táblázat mint adatbázis – kimutatások Kimutatás felhasználása korreláció megállapításához (felh. idő és pontszám)
Külön köszönet: Pusztai Pál, Corona és Boros N. kollégáimnak
43
