1.1.1
Dátum és idő függvények
Azt már tudjuk, hogy két dátum különbsége az eltelt napok számát adja meg, köszönhetően a dátum tárolási módjának az Excel-ben. Azt is tudjuk a korábbiakból, hogy a MA() függvény a mai (rendszer) dátumot adja vissza, a MOST() a pontos időt is. Ismerkedjünk meg további hasznos függvényekkel! Függvény
Funkció
ÉV(dátumérték)
Visszaadja a dátumértékből az évet.
HÓNAP(dátumérték)
A dátumhoz tartozó hónap számát adja eredményül.
IDŐ(óra;perc;másodperc)
Az argumentumban lévő részekből időt állít elő és ezt adja eredményül.
DÁTUM(év;hónap;nap)
Az argumentumban megadott részeket dátumként adja vissza.
HÉT.NAPJA(dátumérték;eredmény_típus)
Megadja, hogy a dátum a hét melyik napjára esett. A visszaadott értéke 1-7 közé eső szám, ha 1 akkor vasárnap, a szombat értéke a 7.
MA()
A mai dátumot adja vissza (a rendszerdátumot).
MOST()
A mai dátumot és pontos időt adja vissza a beépített óra alapján.
MPERC(időérték)
Az időből a másodpercet adja eredményül.
NAP(dátumérték)
A dátumban szereplő napot adja vissza.
ÓRA(időérték)
Az időből eredményül.
az
PERC(időérték)
Az időből eredményül.
a
órát
adja
percet
adja
A képen látható táblázatban a MA() és a MOST() függvények felhasználásával állítottunk elő egy dátumot (D1) és az időt (D2). A B1 cellába egy tetszőleges dátum került. A B3-ban az =ÉV(B1) függvény került, ami 2006-ot adott vissza. A B4-ben az =HÓNAP(B1), a B5-ben az =NAP(B1) került. A B6 tartalma: DÁTUM(B3;B4;B5). Hasonlóan lettek előállítva a D oszlopbeli értékek az ÓRA(), PERC(), MPERC(), IDŐ() függvényekkel. A D7-ben azért van 5, mert a D1-ben lévő dátum csütörtöki, aminek a sorszáma 5 (=HÉT.NAPJA(D1)).
Logikai függvények Az Excelben a logikai műveleteket függvényekkel valósították meg. ÉS(logikai_kifejezés1;logikai_kifejezés2;…) Igazat ad vissza, ha minden benne szereplő logikai kifejezés értéke igaz. VAGY(logikai_kifejezés1;logikai_kifejezés2;…)
Igazat ad vissza, ha a paraméterként megadott bármelyik logikai kifejezés igaz. NEM(logikai_kifejezés) A logikai kifejezés ellentettjét adja vissza. HA(logikai kifejezés;érték_ha_igaz;érték_ha_hamis) Ha a logikai kifejezés igaz, akkor az első értéket (a második paramétert), egyébként a második értéket adja vissza. Az első három függvény egyszerű példában kerül bemutatásra. Az A2:B3 tartomány értékeit hasonlítjuk össze. A C2 tartalma: =ÉS(A2
B2) függvény van. Mivel az első logikai kifejezés értéke igaz, a másodiké hamis, így a VAGY függvény igazat adott vissza. Az E2 cellába írt =NEM(A2
=HA(C2<6;”bagoly”;HA(C2<8;”álmos”;”álomszuszék”)). A legegyszerűbb talán az, ha begépeljük a képletet, nem pedig az, ha a függvényvarázsló ablakának Érték_ha_hamis szövegdobozába gépeljük a második HA-tól kezdve a képletet. A függvény a következőt csinálja: megvizsgálja, hogy a C2 kisebb-e, mint 6. Ha igen, a cellába a bagoly szó kerül. Ha nem kisebb, akkor azt vizsgálja meg, hogy 8-tól kisebb-e. (Ez pont azt jelenti, hogy hat vagy hét. ☺). Ha igen, a cellába az álmos szó kerül. Ha nem kisebb, mint 8, akkor a cellába az álomszuszék szó kerül. Éppen ezt szerettük volna.
Feladatok Készítsük el az alábbi táblázatot! Ha a csiga haladási sebessége 15 m/óra alatti, akkor lassú csiga, ha 15 és 25 m/óra közé esik, akkor gyors, egyébként villám a csiga minősítése. Adjuk meg a gyors csigák számát is! (☺)
1.2 Feltételes formázás Gyakran előfordul, hogy a táblázatainkban lévő bizonyos adatokra fel szeretnénk hívni a figyelmet. Ezt megtehetjük egyszerű formázással, de mi a teendő, ha előre nem tudjuk, hogy melyik cellába mi kerül, illetve utólag változhatnak az adatok. A megoldást a feltételes formázás jelenti. Használata során megvizsgáltatjuk egy cella tartalmát, és a formátumot a vizsgálat eredményétől tesszük függővé. Használhatjuk ezt a funkciót arra is, hogy a hibás adatokra felhívjuk a figyelmet. Például egy cellában nem szerepelhet 1-nél kisebb szám. Ha mégis az kerül oda, akkor piros színnel hívjuk fel rá a figyelmet! Az ábrán lévő táblázat B2 cellájában most 13 van, és fekete színnel látszik. Azt szeretnék elérni, hogy ha ebbe a cellába 1-nél kisebb szám kerül, akkor az pirossal látszódjon. Ehhez először kattintsunk a B2 cellára. Feltételekhez kötött formátumok kialakításához a Formátum menü Feltételes formázás… menüpontját kell kiválasztani. Az első legördülő listából A cella értéke lehetőséget választjuk (ez az alapértelmezés). A példánkban a második legördülő listából a kisebb, mint részt választjuk, majd a harmadik helyre az 1-et írunk. A Formátum… gombra kattintva a színnél beállítjuk a pirosat. Ezzel készen is vagyunk. Ha most a B2-be például nullát írunk, az pirossal jelenik meg. A Formátum… gombra kattintás után a megjelenő Cellák ablakban a Mintázat fülre kattintva a cella hátterét is meg beállíthatjuk A következő példában kicsit nehezebb feltételt fogalmazunk meg. Azt szeretnénk, ha a B oszlopban lévő számok háttere kék lenne, ha értékük 18 és 65 közé, (ez a két érték került az E1 és F1 cellákba.) sárga, ha 1 és 17 közé esik (E2, F2 cellák). Egynél kisebb szám esetében a háttér piros legyen. Először beírjuk az E1 és az F1 cellákba a 18-at, illetve 65-öt, az E2-be 1, az F2-be 17 kerül. A megoldás során először kattintsunk az első cellára, amit formázni szeretnénk, jelen esetben a B2-re. Válasszuk ki a Formátum menü Feltételes formázás pontját, majd állítsuk be a feltételeket az ábrán látható módon. Új feltétel megadásához kattintsunk a Hozzáadás>> gombra. Láthatjuk, hogy az értékeket nem csak begépelhetjük, hanem cellahivatkozásként is megadhatjuk. A cellahivatkozást megadhatjuk úgy is, hogy begépeljük például az =E$2 hivatkozást, de úgy is, hogy a szövegdobozra történő kattintás után rákattintunk a megfelelő cellára, például az E1-re. Ha ezt tesszük, a cellahivatkozás abszolút lesz, ezt mutatják a dollárjelek. Természetesen ezt utólag megváltoztathatjuk. Ha elkészültünk a beállításokkal, nyomjuk meg az OK gombot. Ezzel egy cella formázását elvégeztük, de a többit már nem így szeretnénk. Több megoldás is lehetséges. Az egyik az, hogy a már megformázott B2 cellát aktívvá tesszük (rákattintunk), majd a Szokásos eszköztár Formátum másolása (az ecset ☺) ikonjára kattintunk. Ezután a B3 cellára kattintunk, de az egér bal gombját továbbra is lenyomva tartva az egeret a B5 cella fölé visszük. Azaz lemásoljuk a B2 cella formátumát.
Tegyük fel, hogy úgy szeretnénk a korábbi feltételeket módosítani, hogy ne csak akkor legyen a cellák háttere piros, ha egynél kisebb számot tartalmaznak, hanem akkor is, ha nem egész számot tárolnak. Ahogyan az előző ábrán is látható, a Hozzáadás>> gomb nem aktív. Ezt számunkra azt jelenti, hogy maximum három feltételt lehet megadni, nekünk pedig négyet kellene megadni. Ráadásul a feltételeket sorrendben értékeli ki. Ha a cellába például 12,3 volna írva, akkor az kék színű lenne, a következő feltételek már nem befolyásolnák a cella színét. Tehát hiába adnánk meg a negyediket… A helyes megoldáshoz észre kell vennie, hogy a feltétel megadása során A cella értéke helyett a Képlet értéke kifejezést is ki lehet választani (az első legördülő listában). A példa megoldásában előre visszük az utoljára igényelt feltételt és összevonjuk az 1-nél kisebb számokra, valamint a törtszámokra vonatkozó feltételt. Így csak három feltételt adunk meg a formázás során. Az ábrán azt láthatjuk, hogy akkor lesz piros a cella háttere, a cellában lévő érték felfelé kerekítve (lefelé is lehetett volna ☺) nem egyezik meg a cellában lévő számmal (tehát nem lehet egész szám) vagy ha a cellában 1-nél kisebb szám van. Az ábra mutatja a megoldást.
Feladatok 1) Készítsd el az alábbi táblázatot! Ha a csiga haladási sebessége 15 m/óra alatti, akkor lassú csiga, ha 15 és 25 m/óra közé esik, akkor gyors, egyébként villám a csiga minősítése. Adjuk meg a gyors csigák számát is! (☺) Az E oszlopban tehát a HA függvényt használd! (15 pont)
2) Formázd meg a táblázatot úgy, hogy ga a csiga lassú, akkor a teljes sor sárga legyen (a cellák háttere), ha gyors, akkor világoskék, ha villám, akkor piros! (15 pont) Mentsd ki a táblázatot csigab.xls néven, és küldd el nekem csatolva! 3) Valamint a netre felrakott xls állományban lévő feladatok.
