TFeri.hu

Táblázatkezelés alapjai 3. rész PDF Nyomtatás E-mail
  

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

A cikksorozat korábbi részei:
1. rész - alapok
2. rész - hivatkozások és függvények

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.)

Excel 061

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:

Excel adatok

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.

Excel szövegvarázsló

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!

Excel adatok beolvasása

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!)

Excel mentés

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:

Excel 066

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.

Excel 067

 

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.

Excel kerekítés

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:

Excel függvény beszúrása

Itt a legelső találati eredményt fogjuk használni! Lásd a képen: +KEREKÍTÉS(+C36/D36;0).

Excel 070

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.

Excel 071

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:

Excel Darabteli 072

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.

Excel darabteli 073

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ő:

Excel egyedi formázás

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.

Excel index és hol.van

 

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:

Excel Ha és darabteli

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:

Excel cellaformázás

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!

Excel díszítések

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!

Excel sormagasság

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:

Excel sormagasság beállítása után

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:

Excel diagram 081

Ez persze a B13:C32 tartomány. Most már a következő képet kapjuk:

Excel diagram tartomány beállítása

Ez már-már megfelelő. Elsőnek a diagram címét javítsuk ki egy ottani kettős kattintással:

Excel diagram címe

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)

Excel diagramterület formázása

Így már készen is vagyunk a kívánalmaknak megfelelő grafikonnal:

Excel diagram cége

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

 

© TFeri.hu, 2015.

 
Ulti Clocks content

Hirdetés