7. lecke: Mintafeladatok
| Cél: A lecke célja, hogy néhány mintafeladaton keresztül rendszerezze az eddig összegyűjtött tudást. |
Követelmények: Ön akkor sajátította el megfelelően a tananyagot, ha képes az Excel segítségével megoldani a leckében található mintafeladatokat. |
Időszükséglet: A tananyag elsajátításához (a feladatok megoldásával együtt) hozzávetőlegesen 2 órára lesz szüksége. |
Kisbolygók |
Nyissa meg a Kisbolygók.xls munkafüzetet, és oldja meg a feladatot! |
A megoldás során a Naprendszer első 100 kisbolygójának néhány adatával dolgozunk, amit az "ASTEROID" munkalapon találunk. Ezek alapján válaszolunk a feltett kérdésekre. |
Fontos, hogy ne mechanikusan másoljuk, gépeljük be a megoldást az útmutatóból, hanem törekedjünk arra, hogy megértsük a képletek logikáját. Javasolt, hogy később egyedül is próbáljuk előállítani a helyes képletet, és csak akkor nézzük meg a megadott megoldást, ha úgy gondoljuk, hogy a sajátunk kész! |
Kövessük a táblázatkezelési feladatok általános megoldásával kapcsolatban tanult lépéseket, és töltsük fel a táblázat hiányzó oszlopait! |
Kisbolygós feladat - nyers táblázat | |
Kiindulásként hozzunk létre egy "Feltétel" nevű munkalapot, és egy másikat a saját monogramunkra átnevezve. |
A térfogat meghatározása |
A kisbolygók térfogatát az átmérőjük alapján határozzuk meg. A megoldás során az E oszlop megfelelő celláit töltjük fel adatokkal. Ha az átmérő nem ismert, akkor ? kerüljön a térfogat cellájába. |
Útmutató: A gömb térfogatának képlete: V = 4/3*sugár^3*Pi. Most feltehetjük, hogy a kisbolygók gömb alakúak (bár ez általában nem így van). A nem ismert átmérőjű kisbolygók kezelésére használjuk a megfelelő információs függvényt (Üres). |
Megoldás (E2 cella, másolható lefelé az E3:E101 tartományban): |
=HA(ÜRES(C2);"?";4/3*(C2/2)^3*PI()) |
Vagy: |
=HA(C2;4/3*(C2/2)^3*PI();"?") |
Megjegyzés: Ennél a feladatnál az Üres függvény alkalmazása helyett a C2=0 logikai képlettel is jó megoldást kapunk, bár ez a megoldás nem használható olyan általánosan, mint amit az Üres függvénnyel kapunk. |
Típusjellemzők |
A "Típusok" munkalap segítségével töltsük fel az "ASTEROID" munkalap "Típusjellemzők" oszlopát. Amennyiben egy adott kisbolygónak nincs típusa megadva, a "nincs adat" szöveget jelenítsük meg a cellatartományban, az előző lépéshez hasonló módszerrel. A feltételesen osztályozott (kérdőjeles pl.: S?) típusú kisbolygókat tekintsük biztosan az adott csoportba tartozónak! |
Megoldás (F2 cella, másolható lefelé az F3:F101 tartományban): |
=HA(ÜRES(D2);"nincs adat";FKERES(BAL(D2;1);Típusok!A$2:B$6;2;0)) |
Vagy: |
=HA(NEM(ÜRES(D2));FKERES(BAL(D2;1);Típusok!A$2:B$6;2;0);"nincs adat") |
Kisbolygós feladat - megoldás közben | |
Készítsünk irányított szűrővel egy listát azokról a kisbolygókról, amelyek biztosan vagy valószínűleg C típusúak (ez utóbbiak típusa C?). A listában csak az égitestek neve és típusa jelenjen meg. Az eredmény a monogramos lapra, a szükséges szűrőfeltétel pedig a "Feltétel" munkalapra kerüljön. |
A megfelelő kritériumtábla: |

|
Típusstatisztika 1. |
Függvénnyel számoltassuk meg, hogy típusonként hány darab kisbolygót találunk a rendelkezésre álló adatok szerint a listában! A kérdőjeles típusú kisbolygókat itt is tekintsük az adott csoportba tartozónak! A szükséges feltételt a "Feltétel" munkalapon adjuk meg, az eredmény tetszőleges munkalapra kerülhet. |
Útmutató: Célszerű olyan szűrőtartományokat létrehoznunk, amelyek egymás mellett helyezkednek el, így tudjuk ugyanis majd megfelelő másolással elkészíteni a végleges megoldást. |
Másolással létrehozzuk a következő szűrőtartományt pl. a H10:L11 blokkban: |

|
Megoldás a H12 cellára: |
=AB.DARAB2($A1:$D101;4;H10:H11) |
A képlet jobbra másolható. Ezt a megoldási ötletet alaposan gondoljuk át! |
Az adatbázis megadásánál elég lenne csak a D oszlop is. |
Gondolkodjon el azon, hogy az AB.DARAB függvénnyel is elő tudnánk-e állítani a megoldást? Ha igen, akkor hogyan? |
Típusstatisztika 2. |
Készítsünk kimutatást, amely megadja, hogy típusonként hány darab kisbolygót találunk a rendelkezésre álló adatok szerint a listában! A kérdőjeles típusú kisbolygókat itt tekintsük külön kategóriába tartozónak! A nem ismert típusú kisbolygókat vegyük le a listáról! |
Kisbolygós feladat - kimutatás | |
Diagramkészítés |
Ábrázoljuk az előző feladatban kapott eredményeket tortadiagramon, külön munkalapként beszúrva. A grafikon neve legyen: "Kisbolygó statisztika", a jelmagyarázatban adjuk meg a típusokat. |
Formázások |
Formázzuk meg a megfelelő oszlopok adatait oly módon, hogy az átmérő km-ben, a térfogat pedig "km3"-ben jelenjen meg. |
Az alkalmazandó formátumkód: 0" km" (az első beállításhoz). |
Végezetül mentsük el a munkafüzetet a táblázatkezelő saját formátumában Kisbolygók néven. |
Gratulálunk! Ezzel elérkeztünk a példa végéhez. |
Utazási díjak |
Nyissa meg a Busz.xlsx munkafüzetet, és oldja meg az útmutató szerint a feladatot! |
Bevezetés |
A feladatunk, hogy az Adatok és a Díjak munkalap segítségével számoljuk ki, hogy az egyes emberek mennyit fizetnek az utazásért. |
A távolság meghatározása |
Töltsük fel a távolság oszlopot 1 és 350 közötti egész számokkal. |
Megoldás (Adatok!C2 cella, másolható lefelé a C3:C101 tartományban): |
=VÉLETLEN.KÖZÖTT(1;350) |
Az utazás díja |
Határozzuk meg a Teljes ár oszlopba az egyes utazásokhoz tartozó teljes árú menetjegy díját a Díjak munkalap segítségével. |
A megoldáshoz a függőleges keresést érdemes használni. A Díjak munkalap A oszlopának a celláiban két adat található a minimum és a maximum kilométer. Az FKERES függvény működéséhez át kell alakítani a táblázatot, hogy egy cellában csak egy adat legyen. |
Megoldás: |
- Oldjuk fel a Díjak munkalap védelmét (jelszó: busz).
- Szúrjunk be egy új oszlopot a Díjak!A és a Díjak!B oszlop közé.
- Jelöljük ki az A oszlopban lévő adatokat, majd készítsünk belőle két oszlopot az Adatok/Adateszközök/Szövegből oszlopok paranccsal. (Tagolt, kötőjel az elválasztó.)
|
Szövegből oszlopok | |
Töltsük ki a Teljes ár oszlopot. |
Megoldás: |
- Nevezzük el a Név mező segítségével a Díjak!A3:D27 tartományt áraknak.
- Adatok!D2 cella tartalma (másolható lefelé a D3:D101 tartományban): =FKERES(C2;árak;3;IGAZ)
|
A Típus oszlopban azt látjuk, hogy az egyes személyek milyen kedvezményre (50%-os, 90%-os jegy) jogosultak, illetve, hogy kértek-e kiegészítő jegyet. A kiegészítő jegyet a plusz jel jelzi a kedvezmény után. |
Jelenítsük meg a Kedvezmény oszlopba az egyes utazásokhoz tartozó kedvezményt százalékosan. |
Megoldás (Adatok!E2 cella, másolható lefelé a E3:E101 tartományban): |
=ÉRTÉK(BAL(B2;SZÖVEG.KERES("%";B2)+1)) |
Határozzuk meg a Kiegészítő oszlopba az egyes utazásokhoz tartozó kiegészítő díjakat. |
Megoldás (Adatok!F2 cella, másolható lefelé a F3:F101 tartományban): |
=HA(JOBB(B2)="+";FKERES(C2;árak;4;IGAZ);0) |
Határozzuk meg az előző számolt adatok alapján a végleges árat. Az összeg meghatározásánál vegyük figyelembe, hogy a kedvezményt csak a menetjegy árából vehetik igénybe a kiegészítő jegyből nem. |
Megoldás (Adatok!G2 cella, másolható lefelé a G3:G101 tartományban): |
=D2*E2+F2 |
Formázások |
Formázzuk meg a kész táblázatot úgy, hogy a kedvezmény %-ban, a távolság km-ben, a pénz adatok pedig Ft-ban jelenjenek meg. |
Rejtsük el a Teljes ár, Kedvezmény, Kiegészítő című oszlopokat. |
Kimutatás |
Készítsünk oszlopdiagramot arról, hogy átlagosan mennyit utaznak az egyes kategóriákhoz tartozó személyek. |
Megoldás: |
Hozzunk létre új kimutatásdiagramot (Beszúrás/Kimutatás/Kimutatásdiagram) a következő ábra szerint. |
Utazási díjak feladat - kimutatás | |
Adatbázis-függvények |
Számítsuk ki a 100 km és 300 km közötti 90%-os (+-os is) utazások végleges árának az összegét. |
Megoldás: |
Kritériumtábla: |

|
Vagy |

|
=AB.SZUM(Adatok!A1:G101;"Végleges ár";Kritérium) |
Végezetül mentsük el a munkafüzetet a táblázatkezelő saját. |
Gratulálunk! Ezzel elérkeztünk a példa végéhez. |