Excel munkafüzetek #1 (adatkezelés) (#23)

Frissítve: 2020 május 14.

Miről szól ez a cikk?
Az ebben a cikkben található munkafüzeteket az Excel adatkezelő szolgáltatásainak gyakorlásához készítettük. Elsősorban a sorba rendezést, az automatikus és irányított szűrést gyakorolhatjuk velük.
A cikkben részletesen bemutatjuk a munkafüzetek tartalmát és felépítését, valamint megtaláljuk a munkafüzetek letöltését biztosító linkeket is.
A munkafüzetek szabadon felhasználhatók, illetve változtatás nélkül terjeszthetők.

1. Autóker lista

A munkafüzet neve és letöltése: 160-K-Excel-Autóker mintapélda.xlsx

Az Autóker munkafüzet egy képzeletbeli autókereskedés bizományba átvett, használt, eladásra váró, illetve eladott autóinak adatait tartalmazza. Az autótulajdonosok, vevők és autók összefüggő adatai kitalált, ugyanakkor létező részadatok (mint például címadatok, család- és keresztnevek, autójellemzők stb.) alapján készültek. Az adatok forrása az internet.

A lista jellemzői a következők:

• A lista egyszerű adatbázisnak tekinthető.
• A listát táblázattá alakítottuk (Beszúrás/Táblázat).
• A lista első sora tartalmazza az oszlopazonosítókat, vagyis a mezőneveket.
• A lista első sora rögzített, tehát a tartalom függőleges görgetésekor mindig a képernyőn marad. Ez azzal is jár, hogy a Ctrl+Home billentyűparancs nem az A1, hanem az A2 cellára helyezi az aktív cellát.
• A lista tartománycíme A1:BE5001, tartományneve Autóker.
• Az oszlopok, vagyis mezők száma 57.
• 5000 db rekordot tartalmaz.
• A rekordok konstans, nem változó értékeket tartalmaznak, képlet tehát nem található bennük.
• A gyakorláshoz célszerű másolatot készíteni a munkafüzetről és/vagy a munkalapról.

Az egyes oszlopok jelentése, szerepe a következő:

• ID = A oszlop, szám adattípus. A rekordokat sorszámmal azonosító mező. Ezen oszlop emelkedő sorba rendezésével visszaállítható a rekordok eredeti sorrendje.
• Eladó_* = B-H oszlopok, szöveg adattípus. Az autótulajdonos vezeték- és keresztneve, lakcímadatai és neme található ezekben az oszlopokban. Az adatokat véletlenszerűen állítottuk össze az interneten található vezetéknév, keresztnév és postai cím adatbázisok felhasználásával.
• Eladó_Életkor = I oszlop, szám adattípus. Az Eladó_Életkor oszlop értékei 18 és 80 közé esnek. Feltételes formázással az átlag alatti életkorokat kék színnel, az átlag felettieket piros színnel emeltük ki.
• Tulajdonosszám = J oszlop, szám adattípus. A jelenlegi tulajdonos hányadik tulajdonosa az autónak. Az oszlop lehetséges értékei 1, 2, 3, 4, és 5.
• Beadás_dátuma = K oszlop, dátum adattípus. A gépjármű autókereskedésbe történő beadásának dátuma 2000 január 1-től 2014 december 31-ig.
• Beadás_éve, Beadás_hónapja, Beadás_napja = L, M és O oszlopok, szám adattípus. A beadás dátumának éve (2000-2014), hónapja (1-12) és napja (1-31) számként rögzítve.
• Beadás_hónapneve, Beadás_napneve = N és P oszlopok, szöveg adattípus. A beadás hónapjának (január-december) és napjának (hétfő-vasárnap) neve.
• Rendszám = Q oszlop, szöveg adattípus. Az autó forgalmi rendszáma ­– mind a betű-, mind a számhármasok véletlenszerűen generált értékek.
• Tehermentes = R oszlop, logikai adattípus. Ha az autón nincs semmilyen közteher-, hitel- stb. tartozás, akkor a mező értéke IGAZ, egyébként HAMIS.
• Kategória = S oszlop, szöveg adattípus. Mivel a lista csak személyautókat tartalmaz, így a forgalmiban szereplő kategóriák közül csak az M1, az M1G, M2 és M3 szerepel.
• Márka_név = T oszlop, szöveg adattípus. Az oszlopban a munkafüzet készítésének időpontjában működő 103 autómárka egyikét találjuk. Az adatok forrása a Wikipedia.
• Márka_ország = U oszlop, szöveg adattípus. A Márka_név oszlopban megjelenített márka országa.
• Motortípus = V oszlop, szöveg adattípus. A motor típusa, a felhasznált üzemanyag, vagy hajtási elv megnevezése (Benzin, Elektromos, Gáz/biodízel, Gáz/CNG, Gáz/LPG, Gázolaj/dízel, Hibrid, Üzemanyagcella).
• Évjárat = W oszlop, szám adattípus. Az autó gyártási éve sorszámként rögzítve. Az Évjárat értéke a Beadás_éve mezőben található értéknél legfeljebb 20 évvel kisebb.
• Kivitel = X oszlop, szöveg adattípus. Az autó kialakításának megnevezése (Crossover, Csapott hátú, Egyterű/MPV, Kabrió, Kisbusz, Kombi, Kupé, Liftback, Limuzin, Pickup, Roadster, Sedan/lépcsős hátú, Sport, Terepjáró, Városi terepjáró/SUV, Zárt).
• Hengerűrtartalom = Y oszlop, szám adattípus. Az autó lökettérfogata, véletlenszerűen generált szám 500 és 3500 között köbcentiméterben.
• Szín = Z oszlop, szöveg adattípus. Az autó színe. Az 526 színnév forrása az internet.
• ABS = AA oszlop, logikai adattípus. Ha az autó rendelkezik ABS (Anti-lock Braking System) automata blokkolásgátló fékrendszerrel, akkor a mező értéke IGAZ, egyébként HAMIS. Az ABS egy olyan aktív biztonsági eszköz, amely erőteljes (padló)fékezés esetén megakadályozza a kerekek beragadását a fékezés másodperc töredékéig tartó kiiktatásával. Így az autó irányíthatatlan megcsúszását megakadályozva biztosítja a fékezendő autó kormányozhatóságát.
• ASR = AB oszlop, logikai adattípus. Ha az autó rendelkezik ASR (Anti-Slip Regulation) kipörgésgátlóval, akkor a mező értéke IGAZ, egyébként HAMIS. Az ASR a kerekek gyors fordulatszám növekedését, kipörgését gátolja, például az autó gyors indulásakor, vagy megcsúszásakor.
• Légzsák = AC oszlop, logikai adattípus. Ha az autó rendelkezik légzsákkal, akkor a mező értéke IGAZ, egyébként HAMIS.
• Szervo = AD oszlop, logikai adattípus. Ha az autó szervorásegítéses kormánnyal rendelkezik, akkor a mező értéke IGAZ, egyébként HAMIS.
• GPS = AE oszlop, logikai adattípus. Ha az autó rendelkezik GPS készülékkel, akkor a mező értéke IGAZ, egyébként HAMIS.
• Computer = AF oszlop, logikai adattípus. Ha az autó rendelkezik fedélzeti számítógéppel (autós fejegységgel), akkor a mező értéke IGAZ, egyébként HAMIS.
• Media = AG oszlop, logikai adattípus. Ha az autó rendelkezik medialejátszó berendezéssel (pl. autórádió, CD lejátszó, MP3 lejátszó stb.), akkor a mező értéke IGAZ, egyébként HAMIS.
• Klíma = AH oszlop, logikai adattípus. Ha az autó rendelkezik belső klímával, akkor a mező értéke IGAZ, egyébként HAMIS.
• Fedett_garázs = AI oszlop, logikai adattípus. Ha az autót többnyire fedett garázsban tárolták, akkor a mező értéke IGAZ, egyébként HAMIS.
• Fotók = AJ oszlop, logikai adattípus. Ha az autóról készültek fényképek, akkor a mező értéke IGAZ, egyébként HAMIS.
• Eladási_ár = AK oszlop, pénznem adattípus, tizedeshelyek száma 0. Az autó eladási ára forintban 1 milliótól 11 millió forintig.
• Eladási_arány = AL oszlop, százalék adattípus, tizedeshelyek száma 1. Az Eladási_ár oszlop legmagasabb árához viszonyítva az aktuális sorban szereplő ár annak hány százaléka. Feltételes formázással a felső 10%-ba tartozó értékeket piros, az alsó 10%-ba tartozó értékeket kék betűszínnel emeltük ki.
• Eladási_aránysáv = AM oszlop, feltételes formázással készült adatsáv egy színnel. Az Eladási_arány oszlop értékei adatsávval megjelenítve.
• Érdeklődőszám = AN oszlop, szám adattípus. Hányan érdeklődtek már konkrét vásárlási szándékkal. A lehetséges értékek 1-től 10-ig.
• Jutalékszám = AO oszlop, százalék adattípus. Az eladási ár hány (1, 2, 3, 4, 5) százalékát kapjuk jutalékként az autó eladását követően.
• Jutalékösszeg = AP oszlop, pénznem adattípus, tizedeshelyek száma 0. Az Eladási_ár értékének Jutalékszám százaléka forintban.
• Eladva = AQ oszlop, logikai adattípus. A mező értéke IGAZ, ha az autót sikerült eladnunk, egyébként HAMIS.

A következő mezőkben csak akkor van adat, ha az Eladva mező értéke IGAZ!

• Eladás_dátuma = AR oszlop, dátum adattípus. Az autó eladásának dátuma 2000 január 1-től 2020 december 31-ig.
• Eladás_éve, Eladás_hónapja, Eladás_napja = AS, AT és AV oszlopok, szám adattípus. Az autó eladásának éve (2000-2014), hónapja (1-12) és napja (1-31) számként rögzítve.
• Eladás_hónapneve, Eladás_napneve = AU és AW oszlopok, szöveg adattípus. Az eladás hónapjának (január-december) és napjának (hétfő-vasárnap) neve.
• Vevő_* = AX-BD oszlopok, szöveg adattípus. Az autót megvásárló vezeték- és keresztneve, lakcímadatai és neme található ezekben az oszlopokban. Az adatokat véletlenszerűen állítottuk össze az interneten található vezetéknév, keresztnév és postai cím adatbázisok felhasználásával.
• Vevő_Életkor = BE oszlop, szám adattípus. A Vevő_Életkor oszlop értékei 18 és 80 közé esnek. Feltételes formázással a 18-30 év közé eső értékeket piros színnel, a 31-65 év közötti értékeket zöld színnel és a 66-80 év közötti értékeket kék színnel emeltük ki.

2. Fakertész lista

A munkafüzet neve és letöltése: 0-K-Excel-Fakertész mintapélda.xlsx

A Fakertész mintapélda egy képzeletbeli csemetefa kereskedés, kertészet által eladott facsemeték adatait tartalmazza. A facsemeték adatai valósak, míg a vásárlók összefüggő adatai kitalált, ugyanakkor létező részadatok (mint például a címadatok, család- és keresztnevek stb.) alapján készültek. Az adatok forrása az internet.

A lista jellemzői a következők:

• A lista egyszerű adatbázisnak tekinthető.
• A listát táblázattá alakítottuk (Beszúrás/Táblázat).
• A lista első sora tartalmazza az oszlopazonosítókat, vagyis a mezőneveket.
• A lista első sora rögzített, tehát a tartalom függőleges görgetésekor mindig a képernyőn marad. Ez azzal is jár, hogy a Ctrl+Home billentyűparancs nem az A1, hanem az A2 cellára helyezi az aktív cellát.
• A lista tartománycíme A1:AD5001, tartományneve Fakertész.
• Az oszlopok, vagyis mezők száma 30.
• 5000 db rekordot tartalmaz.
• A rekordok konstans, nem változó értékeket tartalmaznak, képlet tehát nem található bennük.
• A gyakorláshoz célszerű másolatot készíteni a munkafüzetről és/vagy a munkalapról.

Az egyes oszlopok jelentése, szerepe a következő:

• ID = A oszlop, szám adattípus. A rekordokat sorszámmal azonosító mező. Ezen oszlop emelkedő sorba rendezésével visszaállítható a rekordok eredeti sorrendje.
• Név = B oszlop, szöveg adattípus. A facsemete megnevezése.
• Természetvédelmi_státusz = C oszlop, szöveg adattípus. A facsemete élvez-e valamiféle védettséget, például felvették-e a veszélyeztetett növényeket felsoroló Vörös listára, és ha igen, akkor milyen mértékű a veszélyeztetettsége.
• Ország, Törzs, Osztály, Rend, Család = D-H oszlopok, szöveg adattípus. A facsemete rendszertani besorolásából ezt az öt jellemzőt választottuk ki és rögzítettük a listában.
• Örökzöld = I oszlop, logikai adattípus. Ha a facsemete örökzöld, akkor a mező értéke IGAZ, ha nem, akkor HAMIS.
• Lombhullató = J oszlop, logikai adattípus. Ha a facsemete lombhullató, akkor a mezőértéke IGAZ, ha nem, akkor HAMIS.
• Ár = K oszlop, pénznem adattípus, tizedeshelyek száma 0. A facsemeték egységára forintban, értéke 1000 és 5000 közötti véletlen egész szám lehet.
• Dátum = L oszlop, dátum adattípus. A facsemete eladásának dátuma 2000 január 1-től 2019 december 31-ig.
• Év, Hónap, Nap = M, N és P oszlopok, szám adattípus. A Dátum éve (2000-2014), hónapja (1-12) és napja (1-31) számként.
• Hónapnév, Napnév = O és Q oszlopok, szöveg adattípus. A Dátum hónapjának (január-december) és napjának (hétfő-vasárnap) neve.
• Szlaszám = R oszlop, szöveg adattípus. Az eladást igazoló számla sorszáma.
• Db = S oszlop, szám adattípus. Az eladott facsemeték darabszáma.
• Bruttó = T oszlop, pénznem adattípus, tizedeshelyek száma 0. Számított mező (Ár*Db*1,27), amely a bruttó árat tartalmazza konstansként.
• Arány = U oszlop, százalék adattípus, tizedeshelyek száma 1. A Bruttó oszlop legmagasabb árához viszonyítva az aktuális sorban szereplő ár annak hány százaléka. Feltételes formázással a felső 10%-ba tartozó értékeket piros, az alsó 10%-ba tartozó értékeket kék betűszínnel emeltük ki.
• Aránysáv = V oszlop, feltételes formázással készült adatsáv egy színnel. Az Arány oszlop értékei adatsávval megjelenítve.
• Vezetéknév, Keresztnév, Megye, IRSZ, Település, Közterület, Nem = W-AC oszlopok, szöveg adattípus. A vevő vezeték- és keresztneve, lakcímadatai és neve található ezekben az oszlopokban.
• Életkor = AD oszlop, szám adattípus. Az Életkor oszlop értékei 18 és 80 közé esnek. Feltételes formázással a 18-30 év közé eső értékeket piros színnel, a 31-65 év közötti értékeket zöld színnel és a 66-80 év közötti értékeket kék színnel emeltük ki.

3. Házbiznisz lista

A munkafüzet neve és letöltése: 0-K-Excel-Házbiznisz mintapélda.xlsx

A Házbiznisz mintapélda egy képzeletbeli ingatlankereskedés eladásra váró, illetve eladott ingatlanjainak adatait tartalmazza. Az ingatlantulajdonosok, és az ingatlanok adatai kitalált, ugyanakkor létező részadatok (mint például címadatok, család- és keresztnevek, ingatlanjellemzők stb.) alapján készültek. Az adatok forrása az internet.

A lista jellemzői a következők:

• A lista egyszerű adatbázisnak tekinthető.
• A listát táblázattá alakítottuk (Beszúrás/Táblázat).
• A lista első sora tartalmazza az oszlopazonosítókat, vagyis a mezőneveket.
• A lista első sora rögzített, tehát a tartalom függőleges görgetésekor mindig a képernyőn marad. Ez azzal is jár, hogy a Ctrl+Home billentyűparancs nem az A1, hanem az A2 cellára helyezi az aktív cellát.
• A lista tartománycíme A1:BD5001, tartományneve Házbiznisz.
• Az oszlopok, vagyis mezők száma 56.
• 5000 db rekordot tartalmaz.
• A rekordok konstans, nem változó értékeket tartalmaznak, képlet tehát nem található bennük.
• A gyakorláshoz célszerű másolatot készíteni a munkafüzetről és/vagy a munkalapról.

Az egyes oszlopok jelentése, szerepe a következő:

• ID = A oszlop, szám adattípus. A rekordokat sorszámmal azonosító mező. Ezen oszlop emelkedő sorba rendezésével visszaállítható a rekordok eredeti sorrendje.
• Eladó_* = B-H oszlopok, szöveg adattípus. Az ingatlantulajdonos vezeték- és keresztneve, lakcímadatai és neme található ezekben az oszlopokban. Az adatokat véletlenszerűen állítottuk össze az interneten található vezetéknév, keresztnév és postai cím adatbázisok felhasználásával.
• Eladó_Életkor = I oszlop, szám adattípus. Az Eladó_Életkor oszlop értékei 18 és 80 közé esnek. Feltételes formázással az átlag alatti életkorokat kék színnel, az átlag felettieket piros színnel emeltük ki.
• Tulajdonosszám = J oszlop, szám adattípus. A jelenlegi tulajdonos hányadik tulajdonosa az ingatlannak. Az oszlop lehetséges értékei 1, 2, 3, 4, és 5.
• Meghirdetés_dátuma = K oszlop, dátum adattípus. Az ingatlan eladásra történő meghirdetésének dátuma 2000 január 1-től 2014 december 31-ig.
• Meghirdetés_éve, Meghirdetés_hónapja, Meghirdetés_napja = L, M és O oszlopok, szám adattípus. A meghirdetés dátumának éve (2000-2014), hónapja (1-12) és napja (1-31) számként rögzítve.
• Meghirdetés_hónapneve, Meghirdetés_napneve = N és P oszlopok, szöveg adattípus. A meghirdetés hónapjának (január-december) és napjának (hétfő-vasárnap) neve.
• Tehermentes = Q oszlop, logikai adattípus. Ha az ingatlanon nincs semmilyen közteher-, hitel- stb. tartozás, akkor a mező értéke IGAZ, egyébként HAMIS.
• Alapterület = R oszlop, szám adattípus. Az ingatlan lakható részének alapterülete 50-től 100 négyzetméterig.
• Telekméret = S oszlop, szám adattípus. Az ingatlanhoz tartozó telek mérete 51-től 600 négyzetméterig.
• Szintszám = T oszlop, szám adattípus. Az ingatlan lakószintjeinek száma, lehetséges értékek 1, 2, 3 szint.
• Szobaszám = U oszlop, szám adattípus. Az ingatlan lakószobáinak száma 1-től 15-ig.
• Altípus = V oszlop, szöveg adattípus. Az ingatlan jellege (Családi ház, Házrész, Hétvégi ház, Ikerház, Kastély, Kúria, Sorház, Tanya, Üdülő).
• Állapot = W oszlop, szöveg adattípus. Az ingatlan építészeti állapota (Beköltözhető, Építés alatt, Felújítandó, Felújított, Jó állapotú, Közepes állapotú, Romos, Új építésű, Újszerű).
• Komfort = X oszlop, szöveg adattípus. Az ingatlan komfortfokozata (Duplakomfort, Félkomfort, Komfort, Komfort nélküli, Luxus, Összkomfort).
• Tanúsítvány = Y oszlop, szöveg adattípus. Az ingatlan energetikai besorolása A+-tól I-ig.
• Fűtés = Z oszlop, szöveg adattípus. A fűtés típusának megnevezése (Cserépkályha, Elektromos, Gáz (cirkó), Gáz (héra), Gáz (konvektor), Gázkazán, Geotermikus, Hőcserés, Kazán + napkollektor, Nincs, Nullenergiás, Távfűtés egyedi méréssel, Vegyestüzelésű kazán).
• Kilátás = AA oszlop, szöveg adattípus. A fő lakóhelyiségek ablakaiból látható látvány típusa (Kertre néző, Panorámás, Udvari, Utcai).
• Tetőtér = AB oszlop, szöveg adattípus. Az ingatlan rendelkezik-e tetőtérrel, és ha igen, akkor annak milyen az állaga (Beépített, Beépíthető, Nincs).
• Pince = AC oszlop, logikai adattípus. Ha az ingatlan rendelkezik pincével, akkor a mező értéke IGAZ, egyébként HAMIS.
• FürdőWC = AD oszlop, logikai adattípus. Ha az ingatlan rendelkezik fürdőszobával és WC-vel, akkor a mező értéke IGAZ, egyébként HAMIS.
• Akadálymentes = AE oszlop, logikai adattípus. Ha az ingatlan rendelkezik bármilyen akadálymentesítő megoldással (közlekedő rámpák, széles ajtók, alacsony asztalok, mozgássérült mosdó stb.), akkor a mező értéke IGAZ, egyébként HAMIS.
• Bútorzat = AF oszlop, logikai adattípus. Ha az ingatlan lakó- és kiszolgálóhelyiségei bebútorozottak, akkor a mező értéke IGAZ, egyébként HAMIS.
• Légkondi = AG oszlop, logikai adattípus. Ha az ingatlan rendelkezik légkondicionáló berendezéssel, akkor a mező értéke IGAZ, egyébként HAMIS.
• Fedett_garázs = AH oszlop, logikai adattípus. Ha az ingatlan rendelkezik fedettgarázzsal, akkor a mező értéke IGAZ, egyébként HAMIS.
• Fotók = AI oszlop, logikai adattípus. Ha az ingatlanról készültek fényképek, akkor a mező értéke IGAZ, egyébként HAMIS.
• Eladási_ár = AJ oszlop, pénznem adattípus, tizedeshelyek száma 0. Az ingatlan eladási ára forintban 20 milliótól 70 millió forintig.
• Eladási_arány = AK oszlop, százalék adattípus, tizedeshelyek száma 1. Az Eladási_ár oszlop legmagasabb árához viszonyítva az aktuális sorban szereplő ár annak hány százaléka. Feltételes formázással a felső 10%-ba tartozó értékeket piros, az alsó 10%-ba tartozó értékeket kék betűszínnel emeltük ki.
• Eladási_aránysáv = AL oszlop, feltételes formázással készült adatsáv egy színnel. Az Eladási_arány oszlop értékei adatsávval megjelenítve.
• Érdeklődőszám = AM oszlop, szám adattípus. Hányszor tekintették már meg az ingatlant konkrét vásárlási szándékkal. A lehetséges értékek 1-től 10-ig.
• Jutalékszám = AN oszlop, százalék adattípus. Az eladási ár hány (1, 2, 3, 4, 5) százalékát kapjuk jutalékként az ingatlan eladását követően.
• Jutalékösszeg = AO oszlop, pénznem adattípus, tizedeshelyek száma 0. Az Eladási_ár értékének Jutalékszám százaléka forintban.
• Eladva = AP oszlop, logikai adattípus. A mező értéke IGAZ, ha az ingatlant sikerült eladnunk, egyébként HAMIS.

A következő mezőkben csak akkor van adat, ha az Eladva mező értéke IGAZ!

• Eladás _dátuma = AQ oszlop, dátum adattípus. Az ingatlan eladásának dátuma 2000 január 1-től 2020 december 31-ig.
• Eladás_éve, Eladás_hónapja, Eladás_napja = AR, AS és AU oszlopok, szám adattípus. Az ingatlan eladásának éve (2000-2014), hónapja (1-12) és napja (1-31) számként rögzítve.
• Eladás_hónapneve, Eladás_napneve = AT és AV oszlopok, szöveg adattípus. Az eladás hónapjának (január-december) és napjának (hétfő-vasárnap) neve.
• Vevő_* = AW-BC oszlopok, szöveg adattípus. Az ingatlant megvásárló vezeték- és keresztneve, lakcímadatai és neme található ezekben az oszlopokban. Az adatokat véletlenszerűen állítottuk össze az interneten található vezetéknév, keresztnév és postai cím adatbázisok felhasználásával.
• Vevő_Életkor = BD oszlop, szám adattípus. A Vevő_Életkor oszlop értékei 18 és 80 közé esnek. Feltételes formázással a 18-30 év közé eső értékeket piros színnel, a 31-65 év közötti értékeket zöld színnel és a 66-80 év közötti értékeket kék színnel emeltük ki.

VÉGE.

Infopanel
Készült: 2019 szeptember 15.
Operációs rendszer: Windows 10 (19H2, 1909b18363)
Irodai programcsomag: Microsoft Office Professional Plus 2016, Havi csatorna
Szint: kezdő, ECDL: M04/S6
Kategória: Táblázatkezelés → Példatár → Munkafüzetek (adatkezelés)