KURZUS: Bevezetés a számítógépek használatába

MODUL: Táblázatkezelés

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
1. ábra

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
2. ábra

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
3. ábra
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
4. ábra

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
5. ábra
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.