KURZUS: Mérnöki számítási módszerek

MODUL: Táblázatkezelés

5. lecke: A táblázat mint adatbázis

Cél: A lecke a táblázatkezelők által nyújtott adatbázis szintű szolgáltatásokat tárgyalja. Az anyag elsajátítása - a korábbiakhoz hasonlóan, az intenzív gyakorlási igény miatt - csak akkor tekinthető sikeresnek, ha a jegyzetben bemutatott és az órákon szerepelt gyakorlati feladatokat is bizonyos jártassággal meg tudja oldani a hallgató.

Többször hangsúlyoztuk a tananyagban, hogy nem szabad összekeverni a táblázatkezelőt az adatbázis-kezelővel, ennek ellenére mégis előfordulhatnak olyan problémák, amit tipikusan táblázatkezelővel oldunk meg, majd a végén a keletkezett táblázatból készítenénk egy lekérdezést, jelentést. Ilyenkor általában nem kell a táblázatot áttölteni egy adatbázisba, hanem az Excel eszközeit kell alkalmazni. Ilyen eszköz lehet a szűrés, kimutatás, adatbázis függvények, amikkel a lecke foglalkozik.

Követelmények: Ön akkor sajátította el megfelelően a tananyagot, ha képes az Excel segítségével

  • rendezni egy táblázat sorait és oszlopait növekvő és csökkenő sorrendbe,
  • speciális szűrőfeltételeket készíteni,
  • az AutoSzűrőt a célnak megfelelően használni,
  • kritériumtáblát létrehozni és értelmezni,
  • irányított szűrőt használni,
  • adatbázis-kezelő függvényt alkalmazni,
  • kimutatást és kimutatás diagramot készíteni,
  • a cella adatainak érvényességét ellenőrizni.

Időszükséglet: A tananyag elsajátításához (a feladatok megoldásával együtt) hozzávetőlegesen 3,5 órára lesz szüksége.

Kulcsfogalmak

  • rendezés
  • kritériumtábla
  • AutoSzűrő
  • irányított szűrő
  • kimutatás
  • adatérvényesség
Bevezetés

A táblázatkezelőkben 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.

Az adatbázisblokkra szigorúbb szabályok vonatkoznak, mint egy általános táblázatra. Egy általános Excel táblázatban lehet két teljesen azonos sor vagy oszlop, egy adatbázisblokkban nem. Az a szigorúbb megkötés is érvényes, hogy adatbázis blokkban nem szerepelhetnek egyforma azonosítójú oszlopok. Nem lehet benne teljesen üres sor vagy oszlop sem, ugyanakkor az adatbázisblokkban is lehet egy vagy több üres cella.

Az adatbázisblokknak célszerű nevet adni.

Adatbázis szinten logikailag a következő műveletek hajthatók végre:

  • Ú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.

Új adatok beszúrása illetve rekordok, vagy mezők törlése a blokkműveleteknél korábban ismertetett módon lehetséges, rekordok vagy mezők módosítását pedig egyszerűen átírással végezhetjük el. Mező vagy rekord beszúrása természetesen új oszlop vagy sor beszúrásával indítható. Az új oszlop és sor megfelelően kitöltendő!

A rendezés és a szűrés lehetőségei csoportosítva az Adatok menüszalagon találhatók meg.

Rendezés

A rendezés előtt célszerű kijelölni az egész táblázatot. Ha a rendezendő táblázat minden cellája ki van töltve, akkor elegendő a táblázat belsejébe állni, így a program felismeri azt. A rendezést az Adatok/Rendezés és szűrés/Rendezés menüponttal indíthatjuk el.

A továbblépés előtt ellenőrizzük, hogy a táblázatkezelő helyesen ismerte-e fel a mezőazonosítók sorát vagy oszlopát. (Általában sorok szerint rendezünk, de előfordulhat az is, hogy a rendezést oszlopok szerint kell elvégezni, mivel az adatbázisunk transzponálva tartalmazza az adatokat. A mezőnevek egyik esetben se kerüljenek bele a rendezendő adatok közé.) Amennyiben szükséges, "Az adatok fejlécet tartalmaznak" jelölőnégyzettel módosíthatjuk az automatikus felismerés eredményét.

Ezután be kell állítani, hogy melyik adatsor vagy oszlop szerint történjék a rendezés. Ehhez a Rendezés részablak bal oldali legördülő listájából ki kell választani a megfelelő mezőnevet. A rendezés jellege emelkedő vagy csökkenő lehet.

Érdekesség, hogy a 2010-es Excelben már nemcsak a cella értéke, hanem a cellaszín, betűszín és cellaikon szerint is lehet rendezni.

Az így beállított rendezés a Szokásos menüszalagról is végrehajtható. A táblázat kijelölése után az A Z, Z A gombokra kattintva emelkedő illetve csökkenő rendezést kapunk. Itt figyelnünk kell arra, hogy a mezőazonosítók sora/oszlopa ne kerüljön bele a kijelölésbe, mert ekkor a mezőnevek az adatok közé keveredhetnek, ami súlyos hiba. A rendezés azon adatsor/oszlop szerint történik, amelyikben a kurzor a kijelölés végén állt.

Előfordulhat az is, hogy nemcsak egy rendezési szempont szerint kell rendezni, hanem további szempontokat is figyelembe kell venni (például a dolgozókat rendeztük név szerint, de több azonos nevű dolgozót találtunk). Ekkor a Rendezés ablakrész után az Azután ablakrész beállításával egy második rendezési szabályt is megadhatunk, sőt az (újabb) Azután részablakban továbbiakat is (az Azután mező az Újabb szint gombra kattintva aktiválódik). A második rendezési szabály akkor dönt, ha az első rendezés szempontjából azonos adatokat talált.

Rendezési beállítások
1. ábra

Rendezze a DVD kölcsönző (előző lecke végi feladat ) adatait a kivétel szerint növekvően és a vissza hozatal szerint csökkenően!

Szűrés

Az adatbázisban a rekordok megjelenése alkalmas feltételek szerint beállítható, illetve a rekordok ezen szempontok szerint kilistázhatók. Ezt a műveletet kiválogatásnak vagy szűrésnek nevezzük.

A táblázatkezelők erre általában két különböző lehetőséget kínálnak, az AutoSzűrő helyben, csak az adatbázis és a szűrő menüjének a felhasználásával dolgozik, míg az irányított szűrő egy kimeneti blokkba vagy az eredeti táblázat helyére készíti el a végeredményt.

AutoSzűrő

Az aktiváláshoz először ki kell jelölni az adatbázist (vagy legalább kattintsunk bele, hogy a program felismerje), majd az Adatok/Rendezés és szűrés/Szűrő paranccsal be kell kapcsolni az AutoSzűrőt. A bekapcsolt állapotot a menüben egy kis pipa jelzi. A bekapcsolás után a mezőnevek sorában a gép legördíthető listákat jelenít meg. A listákat egyenként lenyitva megadhatjuk az egyes mezőnevekre vonatkozó szűrési feltételeket.

A lehetőségek a következők:

  • Az összes kijelölése: az összes rekord megjelenik;
  • Üres: azok a rekordok jelennek meg, amelyeknél ez a mező üres;
  • érték: azok a rekordok jelennek meg, amelyeknél a mező értéke megegyezik a kiválasztott értékkel.
  • speciális szűrők: Az AutoSzűrő felismeri a szűrni kívánt adatok - aktuális oszlop - típusát (szám, szöveg, dátum), és azokon belül speciálisabb lehetőségeket kínál a megfelelő szám-, szöveg- és dátumszűrő segítségével. (Részletek a következő ábrákon.)
  • Egyéni szűrő...: saját szűrőfeltételt állíthatunk be. Ehhez relációk és - szöveges adatok esetén - az operációs rendszerek használatánál már ismert dzsóker jelek (*, ?) használhatók.
    Példák: >1500 (számtípusú cellákra), ?A* (szöveges típusú cellákra, irányított szűrésnél ehelyett ?A is használható). Összetett feltételeket az És illetve a Vagy kapcsológombokkal állíthatunk be (az ablak két feltétel megadására ad lehetőséget).

Több különböző mezőre együtt beállított feltételek között logikai és kapcsolat van.

Szám- és szövegszűrő
2. ábra
Dátumszűrő
3. ábra

Szűrje ki a DVD kölcsönző (előző lecke végi feladat) adataiból az 1. negyedév kölcsönzéseit!

Szűrje ki a DVD kölcsönző (előző lecke végi feladat) adataiból a 2012. 04. 06 és 2012. 09. 13 közötti kölcsönzéseket!

Szűrje ki a DVD kölcsönző (előző lecke végi feladat) adataiból az Anna nevű kölcsönzőket!

Figyeljen arra, hogy az Egyéni szűrő... használatakor "* Anna" kifejezést kell használni. Ha az Anna elé nem írnánk oda a szóközt, akkor a * miatt a Zsuzsanna, Marianna nevű kölcsönzőket is kiszűrné.

Szűrje ki a DVD kölcsönző (előző lecke végi feladat) adataiból azokat a kölcsönzőket, akiknek a vezetéknevük és keresztnevük második karaktere "a" betű.

Segítség: "?a* ?a*"

Irányított szűrő

Az irányított szűrő használatához a táblázatkezelőkben általában három blokkra van szükségünk.

Az adatbázis mellett szükséges egy szűrőtartomány, amelyet a szűrés előtt nekünk kell létrehoznunk. Ennek felső sora azokat a mezőneveket tartalmazza, amely mezők tartalmára feltételt szeretnénk szabni. Ezek alatt következnek a feltételek. Megadásuk lényegében ugyanolyan szintaktikával történik, mint az AutoSzűrő esetén. Ha összetett logikai feltételre van szükségünk, akkor az és kapcsolatot egymás mellé írással, a vagy kapcsolatot egymás alá írással jelezzük. Ha az És kapcsolat ugyanarra a mezőre megadott több feltételre vonatkozik, akkor az adott mezőnév többször fog szerepelni a szűrőtartományban. A szűrőtartomány tartalmazhat felesleges mezőneveket is (amelyekre nem adunk meg szűrési feltételt), de ilyeneket nem célszerű felvenni, törekedjünk a minimális szűrőtartomány használatára. A szűrőtartomány üres cellája azt jelenti, hogy arra a mezőre, amiben ez a cella van, nem adtunk meg feltételt. Ez a vagy kapcsolatra hibás eredményt adhat!

Tevékenyég: Gondolja át a Boole-algebra műveleteit felidézve, hogy ez miért igaz!

Megjegyezzük, hogy a szűrőtartomány megtervezése és korrekt felépítése általában a szűrés legnehezebb része. Komolyabb gyakorlati problémák esetén ez a fázis viszonylag hosszú gondolkodást, időráfordítást is igényelhet, és sok hibát ejthetünk, ha a feladatot nem gondoljuk át kellőképpen! (Lásd még: feladatok lent.)

Ha nem helyben szűrünk, akkor Excelben létre kell hozni egy kimeneti blokkot is, ahol az eredmények megjelennek. Ez a blokk a megjeleníteni kívánt mezők neveit tartalmazza, alatta megfelelő üres területtel, ahová a gép a listát elhelyezi.

Fontos, hogy a mezőnevek mindhárom használt blokkban pontosan ugyanazok legyenek - eltérő szóköz vagy ékezet sem lehet, ezért az a legjobb, ha a blokkok létrehozásakor a mezőneveket másolással készítjük el az új blokkokban.

Új szűrésnél mindig új kimeneti blokkot kell készítenünk, ha a régit használjuk, akkor a régebben szűrt adatok elvesznek.

A szűrés technikai végrehajtása úgy történik, hogy a blokkok gondos elkészítése után az Adatok/Rendezés és szűrés/Speciális pontot választjuk; megadjuk vagy kijelöljük a listatartományt - azaz az adatbázist -, a szűrőtartományt, majd ha nem helyben szűrünk, akkor a Más helyre másolja gomb bekapcsolása után megadhatjuk a kimeneti blokk címét, vagy a számára biztosított területet fejléccel együtt. Az OK gombra kattintva a táblázatkezelő elkészíti a listát.

Ha a kimeneti blokk megadásánál nemcsak a fejlécet, hanem a teljes output területet kijelöljük, akkor a táblázatkezelő csak akkora részt használ a lista elkészítésére, amennyi a kijelölésben rendelkezésre áll. Ha erre a területre nem fér rá minden rekord, amely a feltételnek eleget tesz, akkor a többlet elveszik, nem jelenik meg a kigyűjtésben.

Egyszerű irányított szűrés
4. ábra

Ha a Helyben szűrést választottuk, az adatbázisunk soraiból csak a szűrőfeltételeknek megfelelő sorok maradnak láthatóak a szűrés befejezése után. Az Adatok/Rendezés és szűrés/Szűrők törlése ponttal lehet újra látni a teljes adatbázist.

A 4. ábrán bemutatott egyszerű példával ellentétben, valódi gyakorlati problémáknál az irányított szűrést célszerű úgy végrehajtani, hogy a használt blokkokat különböző munkalapokon legyenek Nem elegáns pl. a szűrési feltételeket az adatbázisblokk mellé felépíteni. Ilyenkor a szűrés technikailag bonyolultabbá válik. Ekkor mindig arról a munkalapról kell indítani, ahol a kimeneti blokkot várjuk. Ha nem így teszünk, akkor a szűrés végén hibaüzenetet kapunk, ugyanis a szűrt adatok csak az aktív munkalapra kerülhetnek. De a munkalapon belül sem mindegy, hogy hol állunk. Ha a szűrést a kimeneti fejlécéből indítjuk (Excel), akkor a gép azt akarja adatbázisnak tekinteni, és így szintén hibaüzenetet kapunk. Legjobb, ha egy vagy két üres sort kihagyunk a fejléc alatt, és onnan kezdjük a szűrést.

Irányított szűrő használata több munkalap esetén
5. ábra

Ha az eredeti táblázat adatai a szűrés után megváltoznak, akkor az Excel a már kiszűrt listát nem változtatja meg. A frissítés itt nem is indokolt, hiszen a szűrés eredménye mindig egy kigyűjtés, a szűrés időpontjának megfelelő állapot szerint.

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áztassa 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. Készítsen szűrőtartományt a feladat megoldására, majd hajtsa is végre a szűrést!

Rövid magyarázattal bemutatjuk a feladatok megoldásához használható szűrőtartományokat.

Vagy - mivel a * karakter a maszk végéről irányított szűrésnél elhagyható:

Felhasználjuk, hogy az Excelben a * a karaktersorozat bármelyik részének a helyettesítésére is használható:

Járjon utána, mi a különbség az Excel és az operációs rendszerek * használata között!

Az évjárat megadásánál az adat számtípusa miatt nem használhatjuk a * karaktert:

Értelmezze az alábbi szűrőtartományokat! Mi lesz a szűrés eredménye?

A győri lakosok kiszűrésére fent bemutatott szűrőtartomány felvet egy érdekes problémát. Mit tudunk tenni akkor, ha a lakcím mezőben csak a település neve szerepel, és nincs utána a "segítő" vessző jel, amivel könnyen le tudtuk választani a számunkra most nem szükséges győrújbaráti, győrszemerei stb. lakosokat? Ilyen esetekben a szűrőtartomány megfelelő cellájában az =Győr karakterláncnak kell szerepelnie, ekkor kapjuk meg a megoldást. A két lehetséges jó beírást a következő ábra mutatja be.

Győri lakosok szűrése - bonyolultabb eset
6. ábra

Gondolja át, hogy milyen szűrőfeltétel segítségével lehet kiszűrni egy keresztneveket tartalmazó oszlopból a dupla keresztneveket!

A keresztneveket szóköz választja el egymástól, ezért a dupla keresztneveket a "* *" maszkkal lehet helyettesíteni.

A következő ábrán egy tárgy zárthelyi eredményeit rögzítő táblázatból szeretnénk kiszűrni azokat a dupla keresztnevű hallgatókat, akik legalább 6 pontra írták meg valamelyik dolgozatukat.

Értelmezze a lenti képen látható feladatot, majd módosítsa úgy a kritériumtáblát, hogy a pótlásokat is vegye bele! Azaz szűrje ki azokat a két keresztnevű hallgatókat, akik legalább 6 pontot értek el valamelyik normál vagy pót zárthelyin!

Az irányított szűrő használata - olyan dupla keresztnevű hallgatók kiválogatása, akik valamelyik zh-t legalább 6 pontra megírták
7. ábra

Miért lehet >5 feltételt írni a >=6 helyett az előző feladatban?

Adatbázis-kezelő függvények

Az adatbázisblokk bizonyos feltételeknek eleget tevő rekordjait nemcsak listáztatni lehet, hanem a mezőik adataival műveleteket is végezhetünk. Erre az adatbázis-kezelő függvények szolgálnak.

A függvények általános alakja a következő:

AB.függvénynév(adatbázisblokk; mező; feltételtábla).

Az adatbázisblokk és a feltételtábla ugyanúgy adható meg, illetve építhető fel, mint az irányított szűrésnél. A mezőparamétert a mező nevével (idézőjelek közé téve, például következő ábrán: "Fizetés") vagy a mezőnévre való hivatkozással vagy oszlopsorszámmal megadhatjuk meg. Más mezőmegadás hibás (pl. B2; az "eredmény": #ÉRTÉK!).

A szűrési feltételnek eleget tevő rekordok mezőin illetve mezőinek adatain a leggyakrabban a következő műveleteket végezzük el: számlálás, összegzés, maximum- illetve minimumkeresés, átlagszámítás. A megfelelő függvények: AB.Darab, AB.Darab2, AB.Szum, AB.Max, AB.Min, AB.Átlag. A Darab és a Darab2 függvények közötti különbség az, hogy az egyik a függvény második paraméterével megadott oszlop számértékű, a másik pedig a nem üres mezőit számolja össze.

Adatbázis-kezelő függvény használata
8. ábra

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 - amennyiben az automatikus számolási opció nincs kikapcsolva -, automatikusan frissítik az értéküket.

Az AB.Szum és az AB.Darab függvényekhez hasonlóan működnek, és hasonló, de egyszerűbb feladatok megoldására használhatók a Szumha és a Darabteli függvények. Ezeket a függvényeket nem számítjuk az adatbázis-kezelő függvények közé.

Az adatbázis-kezelő függvények használata a tárgyalt táblázatkezelőkben megegyezik.

A DVD kölcsönzős adatbázist használva függvény segítségével válaszoljon az alábbi kérdésekre:

  • Hányszor kölcsönözték ki "A sas" című filmet?
A sas című film kölcsönzése
9. ábra
  • Hány napra vették ki átlagosan a "Wall-E" című filmeket?
A Wall-e című film kölcsönzése
10. ábra
  • Mennyibe a legolcsóbb Fodor Erzsébet által kikölcsönzött film napi díja?
Fodor Erzsébet kölcsönzésének adatai
11. ábra
  • Hányszor vettek ki "A" vagy "B" kategóriás filmet májusban?
A vagy B kategóriás filmek kölcsönzése
12. ábra

Az adatbázis-kezelő függvényeket ügyes, fejlett - csoportos - módon tudjuk alkalmazni olyan esetekben, amikor a feladatban egymás után több kérdést kell megválaszolni ezekkel az eszközökkel (pl. mennyi a januári, februári, márciusi stb. filmkölcsönzések összes bevétele). Ekkor elkészítjük a megfelelő szűrőtartományokat egymás mellett, és alatta egy másolható (!) képlettel előállítjuk a megoldást.

Adatbázis-kezelő függvény másolható képlettel
13. ábra
Kimutatások

Gyakori feladat, hogy ismétlődő adatelemeket tartalmazó listákból olyan táblázatot készítsünk, amely bizonyos szempontok szerint csoportosítja és összegzi az adatokat. Ezek a táblázatok a kimutatástáblák. Elkészítésükhöz a kimutatás-varázslót használhatjuk. A varázsló a következő módokon indítható: Beszúrás/Táblázatok/Kimutatás/Kimutatás, illetve Kimutatásdiagram.

Kimutatást általában egy listából vagy adatbázisból, illetve több különálló tartományból készítünk. A varázsló indítása után először meg kell adnunk a kimutatás forrását. Ez egy táblázatblokk, nem feltétlenül egyedi adatokat tartalmaz, tehát nem mindig adatbázis. Ezután definiálnunk kell, hogy a kigyűjtés hova kerüljön. Ha nem új munkalapra dolgozunk, akkor itt elegendő egyetlen cellacímet megadni, de ügyelni kell arra, hogy a kigyűjtést tartalmazó táblázat elférjen a megadott cellától lefelé és jobbra.

Vigyázat, a kigyűjtés táblázata esetenként jóval nagyobb területet foglalhat el, mint a forrásadatokat tartalmazó blokk!

Ezt követően meg kell határozni a kigyűjtés szempontjait, meg kell mondani, hogy az egyes szempontok alapján kigyűjtött adatok sor vagy oszlop szerint kerüljenek-e a táblázatba, és meg kell adni azt is, hogy a kigyűjtés melyik adatok közül történjen. A kész kimutatás a varázsló vagy a tündér segítsége nélkül is átrendezhető, átalakítható.

A kimutatástáblázat a forrásadatok változtatásával nem frissül automatikusan, és az átrendezés vagy átalakítás is a régi adatok alapján történik. A frissítést manuálisan kell elindítani (menüből, eszköztárról vagy gyorsbillentyű segítségével), a lehetőségek a következők:

  • Elemzés/Adatok/Frissítés
  • Alt+F5

Egy nagykereskedő a megrendeléseiről a következő adatokat tarja 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ő.

Nyissa meg a Boltok.xlsx táblázatot! Tanulmányozza az adatokat:

EladóMegrendelőTermékRendelésNegyedév
DorkaSarki ABCHús5621. n.
DorkaKisboltTengeri hal3422. n.
FüttyösKisboltHús1201. n.
DorkaSarki ABCTengeri hal5431. n.
SuyamaSarki ABCTengeri hal2321. n.
FüttyösKisboltHús1211. n.
SuyamaKisboltHús2372. n.
DorkaKisboltHús8652. n.
FüttyösKisboltTengeri hal3452. n.

Második lépésben a kimutatás varázslót használva elkészítjük a kigyűjtést. Ehhez kijelöljük az előbb elkészített táblázatot, és a menüből elindítjuk a varázslót.

Kimutatásvarázsló
14. ábra

A megfelelő adatok megadása után az OK gombra kattintva máris eljutunk a kialakításhoz. A jobb oldali ablakrészben a sor- és oszlopcímkéknél ill. a Σ értékeknél lehet megadni, hogy melyik mezőnév szerepeljen az elrendezés az adott helyén. A felhasználni kívánt mezők a kimutatásablak jobb felső mezőlistájában kijelölendők. 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.

Összesítések kigyűjtéssel
15. ábra

A Σ értékekhez felvett mezőkön a kimutatás alapértelmezés szerint szöveges adatok esetén a kitöltött mezők darabszámát szám típusú adatok esetén a számok összegét számolja. A számolás módját lehet változtatni, így átlagot, minimumot, maximumot... is számoltathatunk.

A 2010-es Excelben nemcsak a táblázatban lévő mezőkkel lehet kalkulálni a kimutatás során, hanem új úgynevezett számított mezőt is be lehet szúrni. Ennek a funkciónak a használata 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. Számított mező készítésénél függvényeket is lehet használni, de nem megengedettek a változó eredményt adó típusok, mint pl. a véletlenszám-generátorok, a MA() vagy a MOST() függvény.

Értékmező-beállítások és számított mező beszúrása
16. ábra

Szükség esetén módosíthatjuk a már elkészített kimutatás-struktúrát a mezőnevek átmozgatásával. A kis lefelé mutató nyilat tartalmazó gombokkal az adott címkéhez tartozó adatok megjelenítésére adhatunk meg feltételt: rendezhetünk, ill. AutoSzűrő-szerű módon szűrhetünk (feliratra és értékre egyaránt).

Megváltoztathatjuk még az egész kimutatás formai megjelenését (ha az alapértelmezett elrendezéssel elégedetlenek vagyunk), a jobb oldali ablakrész felső sarkában található kis lenyíló lista segítségével (Mezők szakasz/területek szakasz megjelenítése).

A kimutatásdiagram ugyanúgy készíthető, mint a kimutatás, csak más beállításokkal kell dolgozni a varázslóval. Ekkor - a megadás befejeztével - az elemzés grafikus formában is megjelenik. A szemléltetéshez az alapértelmezett diagramtípus az oszlopdiagram, ezen belül sokféle altípus közül választhatunk.

Az összesítéssel kigyűjtött adatok egyes speciális esetekben felhasználhatók statisztikai elemzésekre is. (Erre egyébként a táblázatkezelők általában nyújtanak fejlett céleszközt - ezt a második félévben tanuljuk külön is.) Zh eredményeket tartalmazó számítógépes adatbázis használatával kimutatással kereshetjük a választ pl. olyan típusú kérdésekre, hogy:

  • Van-e korreláció a két félévközi zh eredménye között? (Remélhetőleg igen, azaz aki jobb eredményt ért el az első zh-n, vélhetőleg a másodikon is sikeresebb.)
  • Van-e korreláció az elektronikus teszt kitöltésére ráfordított idő és az eredmény között?
  • Kerülik-e a hallgatók kimutathatóan valamelyik témakör (feladatcsoport) megtanulását?
  • Van-e olyan feladatcsoport, amely "nem mér" (100%-os teljesítés), illetve olyan, ami túl nehéznek bizonyult (egy hallgató sem senki sem tudta hibátlanul megcsinálni)?
Korrelációelemzés kimutatással
17. ábra

A kimutatás képlettel kombinálva gyakran erősebb eszköz a feladatok megoldására. A következő ábrán egy ilyen esetet mutatunk be. (A videókölcsönzős példában meg kellett határozni, hogy melyik volt az a dátum, amikor a legtöbb filmet hozták vissza).

Kimutatás és képlet
18. ábra

Készítsen kimutatást a DVD kölcsönzős nyilvántartás alapján, hogy hány napra vették ki átlagosan az egyes filmeket. Próbálja ki az oszlopos és a soros elrendezést is

Érvényességellenőrzés

Az adatbázis-kezelők klasszikus szolgáltatása, hogy a felhasználó az adatbázis egy-egy mezőjébe csak olyan adatokat vihet be, amelyek megfelelnek azoknak a korlátoknak, amiket az adott mező adataihoz az adatbázis létrehozásakor rendeltek. Ennek az a következménye, hogy a bevitt adat formailag hibátlan lesz. Táblázatkezelőkben adatok ellenőrzése utólag is és bevitel közben is elvégezhető. Erre szolgál az érvényességellenőrzés és az érvényesítés.

A táblázatban lévő adatok ellenőrzését a követendő lépésekkel végezhetjük el:

  • Elkészítjük azt a forrástartományt, amely a megengedett adatokat tartalmazza. Törekedjünk arra, hogy ebben ne legyen hiba!
  • Kijelöljük azt a tartományt, ahova beírták az érvényesítendő adatokat. Ezek között lehetnek hibásak is.
  • Végrehajtjuk az érvényességellenőrzést. (Az ellenőrzött celláknál valamilyen módon jelölve lesz a hiba.).
  • Kijavítjuk a hibás adatokat. (Ha a javítás is hibás lenne, nem engedi az átírást a program.)

Az is előfordulhat, hogy a feltételek olyan jellegűek, hogy a megengedhető értékek listában nem sorolhatóak fel, illetve ez túl nehézkes vagy hosszadalmas lenne. Ilyenkor egyéni feltételt kell megfogalmaznunk, és ez alapján végezhető el az érvényesítés. Az egyéni feltételek megadásában minden olyan eszköz felhasználható, amivel feltételt lehet megfogalmazni.

Az érvényesítés funkció az Adatok menüszalag Adateszközök csoportjában érhető el.

Adatérvényesítés-példa
19. ábra

A DVD kölcsönzős munkafüzet Árak munkalapjának B oszlopában írjon át néhány kategóriát Z kategóriára! Végezze el az adatérvényesítést az előző ábra alapján (ahol kategóriák névvel a H2:M2 blokkot láttuk el)!

Karikáztassa be az Excellel az érvénytelen adatokat!

A következő ábra B oszlopában egy autókereskedésnél használt járműkódokat láthatunk. A kódok 5 karakteresek, amiből az utolsó kettő egy 0 és 48 közé eső sorszám.

Értelmezze, és próbálja ki a következő ábrán látható egyéni képlettel készült adatérvényesítést!

Ha egyéni érvényesítést készítünk, akkor a képletet a kijelölt blokk aktív cellájához viszonyítva kell beírni.

Adatérvényesítés-példa - egyéni feltétellel
20. ábra

Állítsa be egy cellára, hogy csak -5-nél nagyobb egész számokat lehessen beleírni!

Önellenőrző kérdések
1. Egy táblázat termékek adatait tartalmazza. Készítsen szűrőfeltételt a terméknév oszlophoz, aminek a segítségével azokat a termékeket lehet megjeleníteni, ahol a terméknév utolsó előtti betűje "b"!

Szűrőfeltétel:

2. Az előző feladathoz készítsen újabb szűrőfeltételt (terméknév oszlop), aminek a segítségével azokat a termékeket lehet megjeleníteni, ahol a terméknév utolsó betűje "b"!

Szűrőfeltétel:

3. Egy táblázat személyek adatait tartalmazza. Készítsen szűrőfeltételt a név oszlophoz, aminek a segítségével azokat a személyneveket lehet megjeleníteni, ahol a vezetéknév hossza legalább három karakter, és a keresztnév A-val kezdődik! (Feltehetjük, hogy nincsenek dupla vezetéknevek.)

Szűrőfeltétel:

4. Válassza ki a következő lehetőségek közül azt/azokat, amely(ek) megfelel(nek) egy videotéka kölcsönzési táblázatában az alábbi kritériumnak!
A több mint kétnapos tatabányai lakcímű kölcsönzések, valamint a szombathelyi lakcímű kölcsönzések.
Az összes szombathelyi, soproni vagy tatabányai kölcsönzés.
A minimum kétnapos tatabányai lakcímű kölcsönzések, valamint a szombathelyi vagy soproni lakcímű kölcsönzések.
A kevesebb mint kétnapos tatabányai lakcímű kölcsönzések, valamint a szombathelyi vagy soproni lakcímű kölcsönzések.
A több mint kétnapos tatabányai lakcímű kölcsönzések, valamint a szombathelyi vagy soproni lakcímű kölcsönzések.

5. Kimutatást szeretnénk készíteni az alábbi táblázat alapján. Arra vagyunk kíváncsiak, hogy egy-egy osztályzat hányszor fordul elő közgazdaságtanból. (A kimutatást az alapbeállítások szerint végezzük el, az értékmező összegzésének alapját nem állítjuk át.)

Adja meg, hogy hova húzzuk az egyes mezőket, ha az osztályzatelemeket egymás mellett szeretnénk látni?

Lehetőségek: 1 jelentésszűrő, 2 oszlopcímkék, 3 sorcímkék, 4 értékek (Excel 2010 alapon).
Ha a mezőt nem kell sehova húzni, írjon kötőjelet.

Neptun:
Matematika:
Informatika:
Közgazdaságtan:

6. Kimutatást szeretnénk készíteni az alábbi táblázat alapján. Arra vagyunk kíváncsiak, hogy hány olyan tanuló van, aki matematikából 4 és informatikából 3, illetve matematikából 5 és informatikából 3 szorgalmi feladatot készített.

Hova húzzuk az egyes mezőket, ha a matematika elemeit egymás alatt szeretnénk látni?

Lehetőségek: 1 jelentésszűrő, 2 oszlopcímkék, 3 sorcímkék, 4 értékek (Excel 2010 alapon).
Ha a mezőt nem kell sehova húzni, írjon kötőjelet.

Neptun:
Matematika:
Informatika:
Közgazdaságtan: