KURZUS: Bevezetés a számítógépek használatába
MODUL: Táblázatkezelés
3. lecke: Függvények folytatása
Cél: A lecke a függvények használatának második, nagyobb részét tekinti át. Hangsúlyosan felhívjuk hallgatóink figyelmét arra, hogy ez a rész erősen gyakorlatias! Az anyag elsajátítása itt - a téma rendkívül szerteágazó volta miatt - csak akkor tekinthető sikeresnek, ha a tananyagban szerepelő gyakorlati feladatokat is bizonyos jártassággal meg tudja oldani a tanuló. | |||
Az első fejezetben a szöveg-, idő- és dátumkezelő függvények használatát sajátíthatja el, majd olyan további függvények használatát mutatjuk be, amelyek elősegítik a különböző adatfeldolgozást. | |||
Követelmények: Ön akkor sajátította el megfelelően a tananyagot, ha képes az Excel segítségével | |||
| |||
Időszükséglet: A tananyag elsajátításához (a feladatok megoldásával együtt) hozzávetőlegesen 4 órára lesz szüksége. | |||
Kulcsfogalmak | |||
| |||
Szöveg-, idő- és dátumkezelő függvények | |||
A szövegkezelő függvények a táblázatkezelőkben a következő részcsoportokba oszthatók: | |||
| |||
Az idő- és dátumkezelő függvények ezen adatok részeinek kivágását és konverzióját hajtják végre (pl. Percek, Hét.napja), illetve egy-egy speciális függvény a mai dátum és a mostani idő előállítására alkalmas (a Ma és a Most). Ezeknél függvényeknél figyelni kell arra, hogy a táblázatkezelők a dátumot és az időt számként tárolják. Itt csak a dátumkezelőkkel foglakozunk részletesebben, de ezzel lényegében az időkezelők használatát is megismerjük. | |||
Egy vállalat nyilvántartást készít dolgozóiról a személyi számuk első hét jegye alapján (lásd következő ábra). Készítse el ön is látható nyers táblázatot! Majd írja be a G7-es cellába a Ma függvényt! | |||
| |||
Írja be a G8-as cellába a születési dátumát! Számolja ki a G9-es cellába, hogy hány napos az | |||
A táblázat adatainak felhasználásával válaszoljuk meg, hogy a dolgozó férfi vagy nő, és ebben a hónapban van-e a születésnapja (ezt mindig a mai dátumra vonatkoztatjuk). | |||
Azokat a számokat, amelyekkel nem számolunk szövegként célszerű tárolni (pl. bankszámlaszám). Ennek több praktikus oka is van, így tudjuk a kezdő nullákat is tárolni, illetve nagy szám esetében se tudományos alakban fog megjelenni. Tehát a személyi szám első hét jegyét szöveges adatként tároljuk (a szám elé írt aposztróf segítségével). | |||
A dolgozó nemét a személyi szám első jegyének vizsgálatával állapíthatjuk meg. Ehhez a Bal függvényt használjuk. Ennek a függvénynek az értéke az első paraméterben megadott sztring első karakterétől számított, a második paraméterben megadott hosszúságú része lesz. (a Jobb függvény egy karakterlánc végéből ugyanígy állítja elő a függvényértéket). A válasz megfogalmazásához a Ha függvényt használjuk. A C2 cellára a megoldás tehát (egy leegyszerűsített helyzetet feltételezve): | |||
=Ha(Bal(B2)="1";"férfi";"nő"). | |||
A Bal függvény eredménye szöveg, ezért az 1-est is szövegként adtuk meg a feltételben. | |||
| |||
A második kérdéshez a születési hónapokra van szükség. A személyi számból a negyedik, ötödik jel jelenti ezt, amit összehasonlítunk a mai dátumból kapott hónappal. A különböző típusú adatok kezelése miatt konverzióra is szükség lesz. | |||
A mai dátumot a Ma függvény állítja elő. Értékét a számítógép belső órája határozza meg. Ebből a Hónap függvénnyel kapjuk meg az aktuális hónapot (a hasonló Év és Nap függvény értelemszerűen egy dátum év és nap részének a meghatározására szolgál). A Ma függvényt beleírhatjuk direkt módon a képletbe, vagy egy tetszőleges cellába írhatjuk a táblázaton kívül, és erre ezután hivatkozhatunk. | |||
A személyi szám hónap részét a Közép függvénnyel vágjuk ki, amelynek három paramétere a következő: az adott szöveg, hányadik jeltől kezdődjön a kimásolandó rész és ez hány jelből álljon. A kivágott hónaprészt az Érték függvénnyel számmá alakítjuk, és ezt hasonlítjuk a mai dátumból már ismert módon meghatározott hónappal. | |||
Végül egy Ha függvénybe beépítjük a megfelelő függvényeket, és a képletet lefelé másolhatóvá tesszük: | |||
=Ha(Érték(Közép(B2;4;2))=Hónap(C$10);"igen";"nem"). | |||
A 2000 után született fiatalok személyi száma már 3-mal, illetve 4-gyel kezdődik. Ha képletünket fel akarjuk készíteni arra, hogy pár év múlva ilyen fiatal dolgozókat is alkalmaz majd a cég, akkor a Maradék függvényt is be kell építeni a kifejezésbe (a kettővel való osztási maradékot határozzuk meg). Noha a Bal függvény karakteres visszaadott értéke miatt elméletileg konverzióra is szükség lenne, a gyakorlatban ez elhagyható, a táblázatkezelő ezt az átalakítást automatikusan elvégzi. Így a módosított megoldás a C2 cellára a következő: | |||
=Ha(Maradék(Bal(B2);2)=1;"férfi";"nő"). | |||
A 2010-es Excelben a Párose információs függvény felhasználásával egy másik megoldás is léhetséges (=Ha(Párose(Bal(B2));"nő";"férfi")). | |||
A feladatban a személyi szám jegyeit szöveges adatként tároltuk. Mivel szám típusú adatok szövegként való tárolása egyes esetekben logikai hiba (most természetesen nem), ezért a táblázatkezelő - beállítástól függően - figyelmeztet bennünket (jelzés: kis zöld háromszögek a cellák bal felső sarkában). Ez a jelzés kikapcsolható (Képletek menüszalag, Képletvizsgálat csoport, Hibaellenőrzés). | |||
Határozzuk meg a hallgatók neve alapján a monogramjukat. Az egyszerűség kedvéért feltesszük, hogy a vezetéknév és a keresztnév nem kezdődhet dupla betűvel, és nincs kettős keresztnév sem. | |||
A feladatot több lépésben oldjuk meg: | |||
| |||
| |||
| |||
A vezetéknév kezdőbetűjének leválasztására most is a Bal függvényt használjuk. A szóközt a vezeték- és a keresztnév között a Szöveg.talál függvénnyel keressük meg, amelynek két kötelező paramétere a következő: a keresett szövegrész, és az a szöveg, ahol keresünk. A megtalált szóköz utáni karaktert a Közép függvénnyel másoljuk ki, végül a kezdőbetűket, a pontokat és a szóközt az Összefűz függvénnyel állítjuk össze egy karaktersorozattá. | |||
=Összefűz(Bal(B2);".";Közép(B2;Szöveg.talál();" ";B2)+1;1);"."). | |||
Bővítse az előző megoldást oly módon, hogy a vezetéknévben és a keresztnévben megengedett a dupla kezdőbetű, és előfordulhat kettős keresztnév is! | |||
Segítségként megadjuk a megoldás javasolt lépéseit: | |||
| |||
Az útmutató alapján készült megoldást az ábra mutatja. | |||
| |||
A kezdőbetűk vizsgálatánál az algoritmus néha tévedhet, ha a kettősnek tűnő betű valójában mégsem kettős (pl. Lyka László). Három tagnál hosszabb nevekre az algoritmus már nem használható jól. | |||
Páciensek születési adatai (személyi számmal adottak) és a mai dátum felhasználásával állapítsa meg a betegek korát. A vizsgálatnál figyeljen arra, hogy a páciensnek volt-e már az adott évben születésnapja! | |||
Segítségként megadjuk a javasolt felépítés egyes lépéseit: | |||
| |||
A megoldáshoz az Év, Ma, Érték, Közép, Ha és Szöveg függvényeket használtuk fel. | |||
| |||
Oldja meg a feladatot más módon is (lásd: ábra)! | |||
A következő példában egy speciális dátumkezelő függvény érdekes alkalmazását mutatjuk be. A feladat: számlakészítés egy család éttermi fogyasztásáról. Ennek része, hogy a számla összegét módosítani kell aszerint, hogy hétvégi napra esett-e az éttermi látogatás (hétvégi kedvezmény figyelembe vétele). A Hét.napja függvény alkalmas ilyen típusú vizsgálatra; a kötelező argumentum egy dátum, a visszaadott érték egy sorszám, 1-től 7-ig. Az alapértelmezés az, hogy a függvény a hét napjait vasárnaptól sorszámozza, lehetőség van azonban arra, hogy ezt módosítsuk (amennyiben a második, opcionális paraméter értéke 2, akkor a sorszámozás hétfőtől indul). | |||
| |||
| |||
Egyéb fontos függvények | |||
Egyes speciális esetekben különleges adatkezelésre van szükség. | |||
Előfordulhat például, hogy valamely cella tartalma üres, és külön vizsgálat nélkül ez hibához vezet. Ilyen esetekben használjuk az Üres függvényt. Ez megvizsgálja, hogy a paraméteréül megadott cella tartalmaz-e adatot, és ettől függően IGAZ vagy HAMIS logikai értéket ad vissza. | |||
A Hiba.e vagy Hibás függvény megvizsgálja, hogy a hivatkozott kifejezés/paraméterül megadott érték hibaüzenetet állít-e elő. A válasz logikai IGAZ vagy HAMIS. | |||
Szám, Szöveg.e, Logikai: a függvények megvizsgálják, hogy a hivatkozott kifejezés/paraméterül megadott érték a megfelelő típusba tartozik-e. | |||
A fenti függvények mind az információs függvénykategóriába tartoznak. | |||
Mint ismert, a műveletek elvégzése csak azokkal a típusokkal történhet, amelyekre az adott műveletek értelmezve vannak (például összeszorozni csak számokat lehet). Ezért típus-átalakításra, konverzióra van szükség akkor, ha az adat formája alapján egy művelet elvégezhető lenne, de a típus ezt a műveletelvégzést nem teszi lehetővé. A konverziók egy része automatikus vagy figyelmeztető jelzéssel "javasolt" (például, ha egy számformájú adatot szöveg típusúként adunk meg, akkor az Excel figyelmeztet, és átalakítást javasol), más része függvénnyel, függvényekkel végezhető el. Ilyen konverziós függvények például a Szöveg, Érték, Római, Dátumérték, stb. Ezeket a függvényeket több különböző kategóriába sorolják a táblázatkezelők. | |||
Itt is külön kitérünk arra, hogy a 2010-es Excel több új függvényt is tartalmaz a 2003-as változathoz képest. Érdekes példa a Hahiba függvény, amely lehetőséget biztosít alternatív válasz megadására abban az esetben, ha a beírt képlet hibás (a függvény a logikai kategóriájában szerepel). | |||
| |||
Említést érdemel még a 2010-es Excel új Tervezés kategóriája. Itt sok egyéb mellett konverziós függvényeket találhatunk, amelyek segítségével a tízes, kettes, nyolcas és tizenhatos számrendszer között végezhetünk átalakításokat. A Dec.bin függvény például tízes-kettes átalakítást biztosít, hátránya azonban, hogy csak a [-512, 511] intervallumba eső számokat tudja átalakítani. Párja a Bin.dec függvény, hasonlóan működik: a negatív számok az [1000000000, 1111111111], a nemnegatívak a [0, 0111111111] intervallum számaiból képződnek. Ez az átalakítás a komplemens kód szabályai szerint történik. | |||
| |||
| |||
A problémára természetesen adható általánosabb megoldás is a táblázatkezelőkben (az ábra bal oldalán). | |||
A megvalósításhoz beírjuk az átváltani kívánt számot egy adott cellába (az ábra szerint: S5). Mellette megvizsgáljuk, hogy a szám negatív-e, ez utóbbi esetben 65536-ot hozzáadunk (nemnegatív esetben a túlcsordulás miatt a hozzáadás felesleges, de nem okozna hibát). Egy segédsorban felvesszük a bitsorszámokat (4. sor). | |||
A szám átváltását maradékos osztással végezzük. Kiszámítjuk a hányadost és a maradékot (5. és 6. sor). Végeredményben a maradékok sorozata a szám fixpontos alakját adja. A legmagasabb helyi értékű bit az előjelet mutatja. | |||
Más fixpontos számábrázolási módszerek is ismeretesek, és ilyen tárolási forma nem csak egész számokra lehetséges. |