Tamás Ferenc: A táblázatkezelés alapjai – 3. rész



A cikksorozat ezen részében egy komplett középszintű táblázatkezelési feladatot fogunk lépésről lépésre megoldani. A kiszemelt a 2014. őszi vizsgaidőszak táblázatkezelési feladata, „Magyarország városai” címmel. Pontos URL:
http://dload.oktatas.educatio.hu/erettsegi/feladatok_2014osz_kozep/k_inf_14okt_fl.pdf 
Nézzük a feladatot lépésről lépésre! A feladat bevezetőjét el kell olvasni!

„Magyarországon 2013. július 15-én 346 város volt. Feladata a városok adatainak elemzése, illetve egy adott nevű város adatainak kikeresése lesz. (A rendelkezésére álló közigazgatási adatok a 2013. július 15-i, a területi és népességi adatok pedig a 2012. január 1-jei állapotnak felelnek meg.)
A megoldás során vegye figyelembe a következőket!
A megoldás során a H oszloptól jobbra végezhet segédszámításokat.
Amennyiben lehetséges, a megoldás során képletet, függvényt, hivatkozást használjon, hogy az alapadatok módosítása esetén is a kívánt eredményeket kapja.
Ha egy részfeladatban fel akarja használni egy korábbi részfeladat eredményét, de azt nem sikerült teljesen megoldania, használja a megoldását úgy, ahogy van, vagy írja be a valószínűnek tartott adatokat! Így ugyanis pontokat kaphat erre a részfeladatra is.”

Indítsuk el a táblázatkezelőt! (Jelen cikkben az Excel 2013-as verzióját használom.)
 
Itt még lényeges teendőnk nincs, utána jön csak a tényleges feladat:

„1. Helyezze el a varosadat.txt tabulátorokkal tagolt, UTF-8 kódolású fájl adatait a táblázatkezelő program munkalapján úgy, hogy az első érték az A1-es cellába kerüljön! Mentse a táblázatot varosok néven a táblázatkezelő program alapértelmezett formátumában! Az adatok helyes beolvasása esetén a városok adatai a 35. sortól lefelé jelennek meg. A táblázat tartalmazza a város nevét, a megye nevét, amelyhez az adott város tartozik, a város népességét (fő), területét (km2-ben kifejezve), az irányítószámát, illetve a várossá nyilvánításának évét.”


Ezt az adatok főmenüből kell kezdeni:
 
A szövegbeolvasó varázsló itt 3 lépésből áll. A kezdet kezdetén keressük meg a beolvasandó fájlt. (Ez a fenti képen látható varosadat.txt néven.) A nagyobb szövegfájlokat általában tagolják. Az első lépésben ezt jelöljük meg! Itt még figyelni kell a kódolásra, ami most UTF-8-as.
 
A 2.-3. lépés értelemszerű, viszont van még egy „meglepetés”, a 4., nem számozott lépés. Itt meg kell határozni, hogy hová is szeretnénk beilleszteni a szöveget. Szerencsére itt is elég elfogadni az alapértelmezést!
 
Persze még menteni kell „varosok” néven az alapértelmezett formátumba, ami most XLSX. (A képen ez Excel munkafüzet néven szerepel!)
 
Folytassuk a feladatot!

„2. A „Statisztikai adatok:” rész C8-as cellája hazánk népességszámát tartalmazza a 2012. január 1-jei állapot szerint. Határozza meg képlet segítségével a C9-es, illetve a C10-es cellában a városlakók számát, illetve Magyarország teljes népességéhez viszonyított arányát ezen a napon! Az arány tizedesjegyek nélkül, százalék formátumban jelenjen meg!”

Ez egy egyszerű összeadás lesz. A C9-es cellában állva kezdjük begépelni a „+SZUM(„ képletet, majd a nyilak segítségével menjünk le a C36-ig. Itt nyomjuk meg a CTRL+SHIFT+lefelé nyíl kombinációt, amelynek hatására az alkalmazás kijelöli a C36:C381 területet. Most már mehet az ENTER! Így a kész képlet a következő lesz:
 
A feladat még tartalmaz egy százalékszámítást is. Ha simán bevisszük a +C9/C8 képletet, akkor a 0,7084294 számot kapjuk, ami még nem megfelelő. A cellán állva a jobb egérgomb segítségével bele kell lépni a cellaformázásba, majd százalék formátumot 0 tizedesjeggyel.
 

Folytatás a következő:

„3. Képlet segítségével határozza meg az egyes városok népsűrűségét fő/km2-ben kifejezve a városok adatait tartalmazó rész Népsűrűség oszlopában! Az eredményt függvény segítségével minden esetben egész számra kerekítse!”

Maga a népsűrűség nem egy komoly feladat, hiszen csak a népességet kell elosztani a területtel, de a kerekítés függvénnyel már gondolkoztató lehet. Ha nem jut kapásból eszünkbe a függvény, akkor érdemes a függvény beszúrása ablakba beírni a kerekítés szót.
 
Ez túl sok eredményt adott, mivel valamennyi függvény között keresgéltünk, helyette a függvény kategóriáját szűkítsük le a javasolt kifejezésre, így már korrekt találati mennyiséget kapunk:
 
Itt a legelső találati eredményt fogjuk használni! Lásd a képen: +KEREKÍTÉS(+C36/D36;0).
 
Ezt a kész függvényt már csupán másolni kell valamelyik eljárással saját maga alá a G37:G381 területre. Most jöhet a következő feladat!

„4. Másolható képlet segítségével határozza meg a C13:C32 tartomány celláiban a városok számát megyénként!”

Itt a dolog kicsit bonyolultnak tűnhet, de nem az, ha megszokjuk az Excel fondorlatait. A megoldásban a darabteli függvényt fogjuk használni, amely leírása szerint egy tartományban összeszámolja azon nem üres cellákat, melyek eleget tesznek a megadott feltételnek.
Tapasztalatom szerint célszerű a legelső függvényt simán összeállítani. A tartomány legyen a megyék neve, tehát B36:B381. Így a függvény első verziója a következő: +DARABTELI(B36:B381;B13). Ez korrektül mutatni fogja a Bács-Kiskun megyében lévő városok számát (22), de sajnos másoláskor a tartomány már nem lesz megfelelő, így azt át kell állítani relatívról abszolút hivatkozásra. Tehát a végső képlet a következő lesz:
 
Ezt persze még másolni kell a C13:C32 tartományban, de a kiigazított formulával immár a helyes eredményt kapjuk. Jöhet a következő feladat!

„5. Az F7-es cellában egy évszámot talál. Írassa ki egész mondatban az F8-as cellába, hogy hány település lett város a megadott évben! Például ha az F7-es cella értéke 2004, akkor az F8-as cellában a következő szövegnek kell megjelennie: „Ebben az évben 18 település lett város.”. Az évszám módosítása esetén a szövegben lévő szám automatikusan frissüljön!”

Itt megint két lépésben érdemes megoldani a feladatot. Az első lépésben használjuk a megszokott darabteli függvény ezúttal az évszámok oszlopon.
 
Mivel nem simán egy számot kell kiíratni a cellába, hanem speciális szöveget, ezért használjuk a cellaformázást, azon belül az egyéni formátumot! Némi kísérletezéssel rá lehet jönni, hogy az idézőjelek közötti szöveget a program simán kiírja, viszont a kiszámolt értéket a Normál szót meghagyva írja ki. Tehát a végső formátum a következő:
 
Most jöhet a következő feladat!

„6. Az A4-es cellában egy város nevét találja. Képlet segítségével jelenítse meg a B4:F4 tartomány celláiban az adott város megfelelő adatait a munkalap alsó részén lévő táblázatból! Ügyeljen arra, hogy a város nevének módosítása esetén a B4:F4 tartomány értékei automatikusan frissüljenek!”

Az eddigiektől eltérően ez most kicsit összetettebb. Tehát a 4-es sorba ki kell íratni Székesfehérvár adatait. Erre a legkézenfekvőbb megoldás lenne a B4-es cellába beírni, hogy +B314, majd ezt másolni a C4:F4 tartományba. Ez tényleg kiírná Székesfehérvár adatait, ám egy másik város választásánál már nem mutatná a helyes értékeket, így bonyolultabb megoldást kell keresni.
Válasszuk az INDEX függvényt, ami értéket vagy hivatkozást ad vissza egy adott tartomány bizonyos sorának és oszlopának metszéspontjában lévő cellából. A tömb egyértelműen az A36:G381 lesz, míg a sorszám a 314-es, tehát Székesfehérvár sorának száma, valamint az oszlopszám a 2-es legyen, mivel ez mutatja meg a megyét (Fejér). Sajnos erre a Pest választ kapjuk, ami hamis. Ezért meg kell keresni azt a sort, ahol a Székesfehérvár városnév van. Erre a HOL.VAN függvény a legalkalmasabb. Javaslatom a következő: az A5-ös (jelenleg üres) cellában illesszük be a kereső függvényt. Tehtá az A5-ös tartalma legyen a következő: =HOL.VAN(A4;A36:A381).
Így már látható, hogy az INDEX függvényben nem a 314-es értéket kellett volna írni, hanem a 279-est. Most már az érték helyett a megfelelő függvényt írjuk a 2. argumentum helyére. Így a végső formula a következő: =INDEX($A$36:$G$381;HOL.VAN($A$4;$A$36:$A$381);2). Ezt már értelemszerűen másolhatjuk a C4:F4 területre, bár a képlet utolsó számát, az oszlopszámot javítani kell.

 
Most már jöhet a következő feladat!

„7. Ha olyan név kerül az A4-es cellába, amely nem szerepel a Név alatti oszlopban, akkor az A5-ös cellában jelenjen meg dőlt, piros betűkkel, hogy „Nincs ilyen nevű magyarországi város.”! Egyébként a cella maradjon üresen!”

Mivel az előző feladatban használtuk az A5-öt cellát, de csak kiegészítő számításra, így azt simán felülírhatjuk! Most meg kell nézni, hogy a városok között van-e az A4-essel megegyező nevű. Ez a megszokott darabteli függvénnyel tegyük meg. Próbaképpen a következőt írjuk be az A5-ös cellába:
=+DARABTELI(A36:A381;A4). Ekkor 1-est kapunk, ha van ilyen város és 0-t, ha nincsen. Erre már rá lehet építeni egy feltételes kiírást, tehát egy +HA függvényt:=+HA(A5=1;”Van”;”Nincs”).
Most persze a két függvényt össze fogjuk kombinálni a következőféleképpen:
 
Ez még csak simán a szöveget írja ki, de nem formázza a kívánalmaknak megfelelően. Ezt már a cellaformázással kell megoldanunk:
 

A következő feladat a díszítő elemek használatáról szól:

„8. A 3. sorban, továbbá a B7, az E7 és a B12-es cellákban lévő címek dőlt betűstílussal, a B4:F4 tartomány celláiban és az F8-as cellában képlettel megjelenítendő értékek félkövér betűstílussal jelenjenek meg! A táblázat címét az első sorban félkövér, 16 pontos betűkkel alakítsa ki a minta szerint!”

Ezt is a jól ismert cellaformázással oldjuk meg (szegélyek, dőlt, vastagított, középre igazított, betűméret, stb.)  . Tapasztalatom szerint itt 1-2 díszítő elemek sokan kihagynak, amit sajnos a javító kénytelen pontlevonással büntetni, ezért mindent lépésenként oldjunk meg és ne felejtsük el ellenőrizni a megoldást!
 

A következő feladat megint egyszerű:

„9. Állítsa be az oszlopok szélességét úgy, hogy minden cella tartalma olvasható legyen, továbbá zárja középre az A3:F4 cellák tartalmát!”

Itt csupán ki kell jelölni az oszlopokat és megfelelő szélességűre igazítani azokat! Ezt legegyszerűbben a már megszokott dupla kattintással tehetjük meg!

Hasonló a 10. feladat is:

„10. Rejtsen el valamennyi adatot tartalmazó sort a 35. sortól kezdve lefelé!”

Itt ki kell jelölni a 35.-381. sort, majd a sormagasságot kell 0-ra beállítani! Megjegyzés: ha tévedésből a sorokat kitöröljük, akkor az eddig számított eredményeket is lenullázzuk!
 
Ha most megnézzük a táblázat bal oldalát, akkor a következőt látjuk a sorok számánál:
 
De ez az elrejtett sorok miatt így helyes! Most jöhet a következő formázási feladat:

„11. A mintának megfelelően egyesítse a megfelelő cellákat, továbbá állítsa be a megfelelő cellák szegélyét és hátterét!”

Ezt szintén a megszokott cellaformázással kell megoldani. Azon belül a Kitöltés fülön belül érdemes próbálkozni! Most jöhet az utolsó feladat: a grafikon!

„12. Készítsen oszlopdiagramot, amely tartalmazza a városok számát megyénként! A diagram címe „A városok száma megyénként” legyen, jelmagyarázatot viszont ne tartalmazzon! A diagram hátterébe a hungary.jpg kép kerüljön a mintának megfelelően! Minden megye neve olvasható legyen! „

A diagram beszúrásának legfontosabb eleme az adatok kijelölése:
 
Ez persze a B13:C32 tartomány. Most már a következő képet kapjuk:
 
Ez már-már megfelelő. Elsőnek a diagram címét javítsuk ki egy ottani kettős kattintással:
 
Most már jöhet a háttérbe való kép beszúrása ( kép jobb oldalán lévő eszköztár segítségével)
 
Így már készen is vagyunk a kívánalmaknak megfelelő grafikonnal:
 

A feladat ezennel véget ért, ahogy a cikksorozat is. További jó táblázatkezelést kívánok mindenkinek!

© TFeri.hu, 2015.