Repülőtér
Kategória: Excel emelt szint
Feladat leírása
A „SkyHigh Airlines” junior elemzője vagy. A cég járatai egész Európában és a tengerentúlon közlekednek, de a vezetőség aggódik a növekvő késések és az üzemanyagköltségek miatt. A rendelkezésedre álló jaratok_raw.txt fájl a diszpécserközpont nyers exportja, amelyben a járatszámok és útvonalak egybe vannak fűzve, az időpontok pedig szöveges formátumúak.
A repterek_db.csv tartalmazza a célállomások adatait (zóna, reptéri illeték, távolság Budapesttől). A feladatod, hogy egy dinamikus elemzést készíts, amely kiszámolja a várható bevételeket, kategorizálja a járatokat távolság szerint, és vizualizálja a késéseket.
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 Menetrend 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 ékezetes karakterekre (UTF-8).
3. A 𝗠𝗲𝗻𝗲𝘁𝗿𝗲𝗻𝗱 munkalapon a B oszlopban (𝙺𝚘𝚍_𝚂𝚝𝚛𝚒𝚗𝚐) az adatok „JÁRAT-HONNAN-HOVA” formátumban vannak (pl. „SK123-BUD-LHR”). A G oszlopban 𝗝á𝗿𝗮𝘁𝘀𝘇á𝗺 néven válaszd le az első kötőjel előtti részt (pl. „SK123”).
4. A H oszlopban 𝗜𝗻𝗱𝘂𝗹á𝘀 néven válaszd le a két kötőjel közötti kódot (pl. „BUD”). Használj szövegfüggvényeket!
5. Az I oszlopban Érkezés néven válaszd le az utolsó kötőjel utáni kódot (pl. „LHR”).
6. A 𝗠𝗲𝗻𝗲𝘁𝗿𝗲𝗻𝗱 munkalapon a C oszlopban az időpontok szövegként szerepelnek (pl. „2024.06.15 14:30”). A J oszlopban 𝗗á𝘁𝘂𝗺_𝗜𝗱ő néven alakítsd ezeket valódi dátum-idő értékké! Ha az Excel nem ismeri fel automatikusan, használd a szövegdarabolást vagy a 𝙳𝙰𝚃𝙴𝚅𝙰𝙻𝚄𝙴 / 𝚃𝙸𝙼𝙴𝚅𝙰𝙻𝚄𝙴 (DÁTUMÉRTÉK/IDŐÉRTÉK) függvényeket.
7. A K oszlopban Célállomás néven keresd ki a 𝗥𝗲𝗽𝘁𝗲𝗿𝗲𝗸 munkalapról az I oszlopban meghatározott érkezési kód (É𝚛𝚔𝚎𝚣é𝚜) alapján a város nevét.
8. Az L oszlopban Távolság_km néven szintén az érkezési kód alapján keresd ki a Budapesttől való távolságot a 𝗥𝗲𝗽𝘁𝗲𝗿𝗲𝗸 lapról.
9. Számoljunk jegyárat! Az M oszlopban 𝗝𝗲𝗴𝘆á𝗿 néven számítsd ki a jegy árát az alábbi logika szerint: Alapdíj (50 EUR) + (Távolság_km * 0.15 EUR). Az eredményt kerekítsd egész számra!
10. Az N oszlopban 𝗕𝗲𝘃é𝘁𝗲𝗹 néven számítsd ki az adott járat teljes bevételét: Jegyár * Utasok száma (D oszlop). Formázd a cellákat „EUR” pénznemre, tizedesjegyek nélkül.
11. Kategorizáljuk a járatokat! Az O oszlopban 𝗧í𝗽𝘂𝘀 néven hozz létre egy logikai vizsgálatot: Ha a távolság kisebb, mint 1500 km, írd ki: „Rövidtáv”. Ha 1500 és 4000 km közötti: „Középtáv”. 4000 km felett: „Hosszútáv”.
12. A P oszlopban 𝗞é𝘀é𝘀_𝗦𝘁𝗮𝘁𝘂𝘀𝘇 néven vizsgáld meg a késést! Az E oszlop (𝚃é𝚗𝚢𝚕𝚎𝚐𝚎𝚜_𝙿𝚎𝚛𝚌) mutatja a repülési időt. A 𝗥𝗲𝗽𝘁𝗲𝗿𝗲𝗸 munkalapon megadott 𝚃𝚎𝚛𝚟𝚎𝚣𝚎𝚝𝚝_𝙸𝚍𝚘 alapján számold ki a különbséget. Ha a tényleges idő több mint 15 perccel meghaladja a tervezettet, írd ki: „KÉSETT”, egyébként „PONTOS”. (Vigyázat: a Repterek lapon percek vannak megadva, keresd ki azt is!).
13. Feltételes formázással állítsd be a 𝗠𝗲𝗻𝗲𝘁𝗿𝗲𝗻𝗱 munkalapon, hogy a „KÉSETT” státuszú sorokban a 𝙹á𝚛𝚊𝚝𝚜𝚣á𝚖 (G oszlop) cellája piros hátterű és fehér betűszínű legyen.
14. A 𝗠𝗲𝗻𝗲𝘁𝗿𝗲𝗻𝗱 munkalap fejlécét (első sor) formázd sötétkék háttérrel, fehér, félkövér betűkkel, és rögzítsd az ablakot úgy, hogy görgetéskor látható maradjon.
15. Készíts egy új munkalapot 𝗘𝗹𝗲𝗺𝘇é𝘀 néven! Az A1 cellába írd be a 𝙼𝙰() függvényt, hogy mindig az aktuális dátumot mutassa.
16. Az 𝗘𝗹𝗲𝗺𝘇é𝘀 munkalapon készíts egy kimutatást (Pivot táblát) a 𝗠𝗲𝗻𝗲𝘁𝗿𝗲𝗻𝗱 adataiból. A sorok a 𝙲é𝚕á𝚕𝚕𝚘𝚖á𝚜 legyenek, az oszlopok a 𝗧í𝗽𝘂𝘀 (Rövid/Közép/Hosszú).
17. A Pivot tábla értékmezőjébe az 𝚄𝚝𝚊𝚜𝚘𝚔 száma kerüljön. Állítsd be, hogy ne összeget, hanem átlagot számoljon a rendszer, tizedesjegyek nélkül!
18. Szűrd a kimutatást úgy, hogy csak a „PONTOS” státuszú járatok adatait mutassa! (Használhatsz szeletelőt vagy a kimutatás szűrőmezőjét).
19. Készíts egy sávdiagramot az 𝗘𝗹𝗲𝗺𝘇é𝘀 munkalapra, amely a „Hosszútáv” kategóriájú járatok utaslétszámát mutatja célállomásonként (a Pivot adatai alapján).
20. A diagram címe legyen „Hosszútávú járatok telítettsége”. A tengelyfeliratok legyenek olvashatóak, az oszlopok színét állítsd a cég arculatához illő kékre.
21. Nyomtatási beállítások: A 𝗠𝗲𝗻𝗲𝘁𝗿𝗲𝗻𝗱 munkalapon állíts be nyomtatási területet úgy, hogy csak a G:P oszlopok (a feldolgozott adatok) kerüljenek nyomtatásra, fekvő tájolással.
