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

  • a szöveg-, idő, és dátumkezelő függvényeket a célnak megfelelően használni,
  • típuskeveredési hibákat felismerni,
  • a különböző típuskonverziókat alkalmazni,
  • az adott feladathoz szükséges függvényeket megkeresni és alkalmazni (a tananyagban nem említetteket is).

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ényeket
  • típuskonverzió
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:

  • a szöveg valamely részét kivágó függvények (Bal, Jobb, Közép),
  • konverziós műveleteket végrehajtó függvények (szöveg és más típusú adatok között),
  • szövegdarabok helyettesítését, cseréjét vagy összeillesztését elvégző függvények.

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!

Nyers táblázat
1. ábra

Í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
=G7-G8 képlet segítségével!

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.

Vállalati nyilvántartás
2. ábra

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évből kimásoljuk a kezdőbetűt.
  • Megkeressük a névben a(z első) szóközt, és a következő részből is leválasztjuk a kezdőbetűt.
  • Összefűzzük a kezdőbetűket, a pontokat és a szóközt.
Név karaktereinek megkeresése
3. ábra
Monogramok meghatározása
4. ábra

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:

  • A vezetéknévből leválasztjuk a kezdőbetűt.
  • Megkeressük a névben az első szóközt.
  • Előállítjuk a név maradékát (ez már csak a keresztnév).
  • Az első lépéssel azonos módon a név maradékából leválasztjuk a kezdőbetűt.
  • Megnézzük, hogy a név maradékában van-e még szóköz.
  • Ha igen, akkor leválasztjuk a név utolsó részét, és meghatározzuk a kezdőbetűt.
  • Összefűzzük a kezdőbetűket.

Az útmutató alapján készült megoldást az ábra mutatja.

Monogramok előállítása 2
5. ábra

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 mai dátum év értékéből kivonjuk a születési dátumból kapott évtized és év értéket úgy, hogy ez utóbbiakat hozzáfűzzük a "19" szövegkonstanshoz. Ezzel megkapjuk a páciens korát úgy, hogy még nem vizsgáltuk azt a lehetőséget, hogy az idén volt-e születésnapja (esetleg: nem töltötte be még az idén az adott évet).
  • A mostani dátum hónap és nap részét ("hhnn" egyéni formázással) szöveggé alakítjuk, és összehasonlítjuk a születési dátum hónap és nap részével. Ha az illetőnek az idén még nem volt születésnapja, akkor levonunk a korból még egyet.

A megoldáshoz az Év, Ma, Érték, Közép, Ha és Szöveg függvényeket használtuk fel.

Életkor meghatározása születési dátum alapján
6. ábra

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).

A Hét.napja függvény alkalmazása feladatmegoldáshoz
7. ábra
A Hét.napja függvény súgója
8. ábra
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).

A Hahiba függvény
9. ábra

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.

Decimális-bináris átalakítás képletekkel
10. ábra
Decimális-bináris átalakítás függvénnyel
11. ábra

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.

Önellenőrző kérdések
1. Hova igazítja a táblázatkezelő alapértelmezés szerint az alábbi függvény eredményét? (b = balra, j = jobbra, k = középre, n = a megadottak alapján nem lehet eldönteni)

=Ha(Percek(Most())>-1;7;A2)
=Ha(A2>Most();1;"2")

2. Adja meg azt a legegyszerűbb képletet, amely a B2-es cellába írt személyi számból (szöveges adat) lecsípi a legelső karaktert! A képlet a cellára vegyes hivatkozással hivatkozzon, oszloprögzítéssel.

Képlet:

3. Melyek igazak a következő állítások közül az =Most()-Ma() képletre?
Értéke mindig egész.
Értékét a Kerek.le függvény biztosan nem változtatja meg.
Értéke nagyobb a pi számnál.
Értéke 0 és 1 közé esik (0-nál nagyobb egyenlő, 1-nél kisebb).
Értéke dátumformában nem jeleníthető meg.
Értékét célszerű időformátumban megjeleníteni.
4. Csoportosítsa a következő függvényeket! Írja a mezőkbe a megfelelő sorszámot! Ha egy függvény több csoportba is tartozik, vesszővel elválasztva, növekvő sorrendben adja meg a számokat, ha egyikbe sem, írjon a mezőbe x-et!

Kategóriák:

1 Második paramétere elhagyható
2 Értéke mindig egész szám
3 Első paramétere logikai típusú

Párose
Szum
Abs
Int
Ha
Vagy
Hahiba
Percek
Hét.napja

5. Csoportosítsa a következő függvényeket! Írja a mezőkbe a megfelelő sorszámot! Ha egy függvény egyik csoportba sem tartozik, írjon a mezőbe x-et!

Kategóriák:

1 Matematikai és trigonometriai
2 Szöveg
3 Idő- és dátumkezelő
4 Logikai

Szum
Abs
Pi
Int
Ha
Vagy
Szöveg.talál
Most
Percek
Hét.napja

6. Nyissa meg az Úszás.xlsx munkafüzetet, majd oldja meg a következő feladatokat az Úszók munkalapon! A nyilvántartás egy 2014 május 27-ei úszóversenyen készült.

Töltse ki a Táv oszlopot! A 20 év alatti versenyzőknek 1000 métert, a többieknek pedig 1300 métert kell úszniuk. A versenyzők korát a személyi szám segítségével lehet meghatározni.

A személyi szám felépítése
Mennyi a Kódok munkalapon megjelenő sárga ellenőrző kód értéke?

Sárga kód:

Számítsa ki a tényleges idő alapján a versenyidőt! A női versenyzők kedvezményt kapnak a versenyen, az ő tényleges idejüket meg kell szorozni 0,9-del. A férfiak verseny ideje megegyezik a tényleges idővel. A versenyzők nemét a személyi szám segítségével tudja meghatározni!

Mennyi a Kódok munkalapon megjelenő piros ellenőrző kód értéke?

Piros kód:

Nézze meg a RANG.EGY függvény súgóját, majd töltse ki a Helyezés oszlopot!

Mennyi a Kódok munkalapon megjelenő kék ellenőrző kód értéke?

Kék kód: