Konditerem
Kategória: Excel emelt szint
Feladat leírása
Az „Iron Pump Gym” rendszergazdája vagy. A teremben egy automata beléptetőrendszer működik, ami minden kártyalehúzást rögzít. A tulajdonos elégedetlen, mert a régi szoftver nem mutatja ki pontosan, kik járnak le járt bérlettel, és melyik napszakban van a legnagyobb tömeg.
A feladatod, hogy a nyers belépési naplóból (kapu_log.txt) és a tagok adatbázisából (tagok_db.csv) egy összefüggő nyilvántartást készíts. A naplófájlban a dátumok angol formátumban vannak, a kapuk kódjai pedig tartalmazzák a zóna nevét is, amit szét kell válogatnod. Ki kell számolnod a bérletek lejáratát, és statisztikát kell készítened a bevételekről.
Tudnivalók a megoldáshoz:
-
A feladatok megoldásához használd a táblázatkezelő beépített függvényeit!
-
Ahol a feladat nem kér mást, a formázást a minta vagy az értelemszerű olvashatóság szerint végezd el.
-
Segédszámításokat a Napló munkalapon a Q oszloptól jobbra végezhetsz.
Részfeladatok
1. Töltsd be a 𝚔𝚊𝚙𝚞_𝚕𝚘𝚐.𝚝𝚡𝚝 tabulátorokkal tagolt szövegfájlt egy új munkafüzetbe! A munkalap neve legyen 𝗡𝗮𝗽𝗹ó, az adatok az A1-es cellától kezdődjenek.
2. Importáld a 𝚝𝚊𝚐𝚘𝚔_𝚍𝚋.𝚌𝚜𝚟 pontosvesszővel tagolt fájlt egy új, 𝗧𝗮𝗴𝗼𝗸 nevű munkalapra! Ügyelj az UTF-8 kódolásra, hogy a nevek (pl. Árvai) helyesen jelenjenek meg.
3. A 𝗡𝗮𝗽𝗹ó munkalapon a B oszlopban a dátumok „YYYY-MMM-DD HH:MM” szöveges formátumban vannak (pl. „2024-FEB-12 06:30”). A G oszlopban 𝗜𝗱ő𝗽𝗼𝗻𝘁 néven alakítsd át ezeket valódi Excel dátum-idő értékké! Használd a bal/közép/jobb szövegfüggvényeket és a dátumfüggvényeket, vagy a szövegdarabolást.
4. A C oszlopban a 𝙺𝚊𝚙𝚞_𝙺𝚘𝚍 tartalmazza a kapu számát és a zónát (pl. „G01-CARDIO” vagy „G04-SAUNA”). A H oszlopban 𝗭ó𝗻𝗮 néven jelenítsd meg csak a zóna nevét (a kötőjel utáni részt).
5. Az I oszlopban 𝗧𝗮𝗴_𝗡𝗲𝘃𝗲 néven keresd ki a D oszlopban található 𝚃𝚊𝚐_𝙸𝙳 alapján a tag teljes nevét a 𝗧𝗮𝗴𝗼𝗸 munkalapról.
6. A J oszlopban 𝗕é𝗿𝗹𝗲𝘁_𝗧í𝗽𝘂𝘀 néven szintén a 𝚃𝚊𝚐_𝙸𝙳 alapján keresd ki, milyen bérlete van az illetőnek (pl. „Full Pass”, „Délelőtti”).
7. A 𝗧𝗮𝗴𝗼𝗸 munkalapon számoljuk ki a lejáratot! Az F oszlopban 𝗟𝗲𝗷á𝗿𝗮𝘁 néven add hozzá a 𝚅á𝚜á𝚛𝚕á𝚜_𝙳á𝚝𝚞𝚖𝚊 (D oszlop) értékéhez a bérlet érvényességi idejét napokban (E oszlop).
8. Térjünk vissza a 𝗡𝗮𝗽𝗹ó munkalapra! A K oszlopban É𝗿𝘃é𝗻𝘆𝗲𝘀_𝗘 néven vizsgáljuk meg a belépést. A belépés akkor érvényes („IGAZ”), ha a belépés időpontja (G oszlop) kisebb vagy egyenlő, mint a tag bérletének lejárati dátuma (amit a Tagok lapról kell kikeresned vagy segédoszloppal áthoznod).
9. Bonyolítsuk a feltételt! Az L oszlopban 𝗝𝗼𝗴𝗼𝘀𝘂𝗹𝘁𝘀á𝗴 néven vizsgáld meg: Ha a Zóna „SAUNA”, de a tag bérlete csak „Basic” típusú, akkor írd ki: „Megtagadva”. Minden más esetben írd ki: „Engedélyezve”.
10. Feltételes formázással emeld ki a 𝗡𝗮𝗽𝗹ó munkalapon piros háttérrel és félkövér betűvel azokat a sorokat (A:L), ahol az É𝚛𝚟é𝚗𝚢𝚎𝚜_𝙴 oszlop értéke HAMIS (tehát lejárt bérlettel próbálkoztak).
11. Az M oszlopban 𝗜𝗱ő𝘀á𝘃 néven kategorizáld a belépéseket! Ha a belépés órája 6 és 10 közé esik: „Reggel”. Ha 16 és 20 közé: „Csúcsidő”. Egyébként: „Normál”.
12. A 𝗧𝗮𝗴𝗼𝗸 munkalapon a G oszlopban 𝗦𝘁á𝘁𝘂𝘀𝘇 néven jelezd a bérlet állapotát egy mai dátumhoz képest (tételezzük fel, hogy ma 2024. február 20. van, vagy használd a 𝙼𝙰() függvényt). Ha a lejárat dátuma régebbi, mint a mai nap, írd ki: „LEJÁRT”, egyébként „AKTÍV”.
13. Számítsd ki a 𝗧𝗮𝗴𝗼𝗸 munkalap H1-es cellájába, hogy mennyi a várható összbevétel a „Full Pass” típusú bérletekből. Használd a 𝚂𝚉𝚄𝙼𝙷𝙰 függvényt a C (Bérlet_Ár) és B (Bérlet_Típus) oszlopok alapján.
14. Hozz létre egy új munkalapot 𝗦𝘁𝗮𝘁𝗶𝘀𝘇𝘁𝗶𝗸𝗮 néven! Készíts ide egy kimutatást (Pivot táblát) a 𝗡𝗮𝗽𝗹ó munkalap adataiból.
15. A kimutatás sorai az 𝙸𝚍ő𝚜á𝚟 legyenek, az oszlopai a 𝚉ó𝚗𝚊. Az értékek mezőben a belépések száma szerepeljen.
16. A 𝗦𝘁𝗮𝘁𝗶𝘀𝘇𝘁𝗶𝗸𝗮 munkalapon a kimutatás mellé készíts egy csoportosított oszlopdiagramot, amely vizualizálja, melyik idősávban melyik zóna mennyire terhelt.
17. A diagram címe legyen „Zónaterhelés Idősávonként”, a jelmagyarázat kerüljön alulra, és válassz egy narancssárga árnyalatú stílust.
18. Térj vissza a 𝗡𝗮𝗽𝗹ó munkalapra! Rendezd az adatokat a 𝙳á𝚝𝚞𝚖 szerint csökkenő sorrendbe (a legfrissebb belépés legyen legfelül).
19. Szűrd a táblázatot úgy, hogy csak a „Megtagadva” jogosultságú sorok látsszanak! (Ha nincs ilyen, hozz létre egy tesztadatot, hogy a szűrés működjön).
20. Nyomtatási beállítások: A 𝗧𝗮𝗴𝗼𝗸 munkalapot állítsd be úgy, hogy nyomtatáskor csak az A:F oszlopok és az 1:50 sorok kerüljenek a papírra, és az oldal tájolása fekvő legyen.
