Molnár Mátyás
Excel 2010 függvények Csak a lényeg – érthetően!
Tartalomjegyzék FÜGGVÉNYHASZNÁLAT ALAPJAI FÜGGVÉNYEK BEVITELE HIBAÉRTÉKEK KEZELÉSE A VARÁZSLATOS AUTOSZUM GOMB SZÁMÍTÁSOK A REJTETT CELLÁKBAN LÉVŐ ADATOKKAL FELTÉTELEKNEK MEGFELELŐ SZÁMOK ÖSSZEADÁSA
1 1 4 6 8 11
MATEMATIKAI ÉS TRIGONOMETRIKUS FÜGGVÉNYEK13 GYÖKVONÁS ÉS HATVÁNYOZÁS A ELŐÁLLÍTÁSA ABSZOLÚT ÉRTÉK, EGÉSZRÉSZ ÉS CSONKOLÁS KEREKÍTŐ FÜGGVÉNYEK VÉLETLEN SZÁMOK ELŐÁLLÍTÁSA SZORZAT ÖSSZEGEK KISZÁMÍTÁSA TRIGONOMETRIKUS FÜGGVÉNYEK
13 13 13 14 15 15 16
STATISZTIKAI FÜGGVÉNYEK
19
ÁTLAGSZÁMÍTÁS LEGNAGYOBB, LEGKISEBB ÉRTÉKET KERESŐ FÜGGVÉNYEK SZÁMLÁLÓ FÜGGVÉNYEK FÜGGVÉNYEK TÖKÉLETESÍTÉSE
19 20 21 22
LOGIKAI FÜGGVÉNYEK
27
HA FÜGGVÉNY ÉS FÜGGVÉNY VAGY FÜGGVÉNY NEM FÜGGVÉNY HAHIBA FÜGGVÉNY
27 29 30 31 31
KERESŐ ÉS HIVATKOZÁS FÜGGVÉNYEK
33
FKERES (VKERES) FÜGGVÉNYEK KERES FÜGGVÉNY HOL.VAN FÜGGVÉNY INDEX FÜGGVÉNY
33 35 36 37
SZÖVEGKEZELŐ FÜGGVÉNYEK
39
ÖSSZEFŰZ FÜGGVÉNY BAL, JOBB, KÖZÉP FÜGGVÉNYEK ÉRTÉK FÜGGVÉNY SZÖVEG.KERES ÉS SZÖVEG.TALÁL FÜGGVÉNYEK HOSSZ FÜGGVÉNY CSERE ÉS A HELYETTE FÜGGVÉNYEK
39 39 40 40 40 42
DÁTUM- ÉS IDŐKEZELŐ FÜGGVÉNYEK
43
ÉV, HÓNAP, NAP FÜGGVÉNYEK MA FÜGGVÉNY HÉT.NAPJA FÜGGVÉNY ÓRA, PERC FÜGGVÉNYEK MOST FÜGGVÉNY
44 44 44 45 45
Logikai függvények A logikai függvényekkel ellenőrizhetjük, hogy egy, vagy több feltétel teljesül-e. Az Excel dönteni tud, és a döntés eredményétől függően más-más számítást végrehajtani. Egy egyszerű példa: vizsgáljuk meg, hogy a kolléga bruttó bére kisebb, mint a minimálbér. Ha igen, akkor emeljük a bérét minimálbér+1000 forintra, ha nem, akkor marad, amennyi volt.
HA FÜGGVÉNY A HA függvény kétirányú elágazást hoz létre. Ha teljesül a megadott feltétel, akkor az igaz ágon megadott számítást végzi el, ha nem, akkor a hamis ágon megadottat. HA (logikai_vizsgálat;érték_ha_igaz;érték_ha_hamis) Logikai_vizsgálat – logikai kifejezés, amely kiértékeléskor IGAZ, vagy HAMIS eredményt ad. A logikai algebrában (Boole-algebra) csak két érték van: az IGAZ és a HAMIS értékek.
Például az A5=20 logikai kifejezés (reláció) kiértékeléskor IGAZ értéket ad, ha az A5 cella tartalma 20, minden más esetben az eredmény HAMIS. Fogalmazhatunk úgy, hogy ez egy állítás: azt állítjuk, hogy az A5 cella tartalma egyenlő 20-al. A függvény megvizsgálja, hogy ez az állítás igaz, avagy hamis. Az argumentumban tetszőleges összehasonlító operátor használható. Összehasonlító operátor
Jelentése
Példa
= (egyenlőségjel)
Egyenlő
A1=B1
> (nagyobb, mint jel)
Nagyobb, mint
A1>B1
< (kisebb, mint jel)
Kisebb, mint
A1
>= (nagyobb, mint vagy egyenlő jel)
Nagyobb, mint vagy egyenlő
A1>=B1
<= (kisebb, mint vagy egyenlő jel)
Kisebb, mint vagy egyenlő
A1<=B1
<> (nem egyenlő jel)
Nem egyenlő
A1<>B1
Érték_ha_igaz – ezt az értéket adja a függvény eredményül, ha a logikai_vizsgálat eredménye IGAZ.
Ez a paraméter lehet konstans, képlet, függvény, vagy ilyen értékeket tartalmazó cellahivatkozás. Érték_ha_hamis – ezt az értéket adja a függvény eredményül, ha a logikai_vizsgálat eredménye HAMIS. Ez a paraméter lehet konstans, képlet, függvény, vagy ilyen értékeket tartalmazó cellahivatkozás.
100.000-nél kisebb forgalmi értékre nem jár árengedmény (0%), 100.000, vagy annál nagyobb forgalmi értékre viszont 5% árengedményt adunk. Döntse el a program, hogy az A2 cellában lévő értékre mennyi árengedmény jár. A megoldás:
A döntéshozó függvényeknél nagyon fontos a határértékek ellenőrzése – csak akkor mondhatjuk, hogy kész a függvény, ha a határértékeket ellenőriztük! A példánkban az alsó határ 99.999 – erre még nem jár árengedmény, de a 100.000-től már igen.
2
Microsoft Excel 2010 – Függvényhasználat alapjai
Csak a lényeg – érthetően!
A példánknál maradva „bonyolítsuk” egy kicsit a helyzetet. 100.000 alatt továbbra sem adunk árengedményt, viszont 100.000 és 199.999 közötti értékekre adjunk 3%, 200.000-től pedig 6% árengedményt. „Huston, van egy kis gondunk!” A HA függvény csak kétirányú elágazásra képes, nekünk pedig három sávunk van, azaz három irányba kellene elágazni. A megoldás: ágyazzunk be a külső HA függvény belsejébe egy újabb HA függvényt. Összetett vizsgálatok esetén legfeljebb 64 (hatvannégy) HA függvényt ágyazhatunk egymásba érték_ha_igaz és érték_ha_hamis argumentum formájában. A 2003-as és korábbi verziók csak 7 függvény egymásba ágyazására voltak képesek. A megoldás vázlata (ez szintaktikailag még nem helyes, nem is ez a cél): HA(forgalom<100.000;0%;HA(forgalom <200.000;3%;6%)) Hogyan „olvassuk” ezt a megoldást? Az első HA függvény megvizsgálja, hogy a forgalmi érték a legalsó sávba tartozik-e. Ha igen, akkor 0% az árengedmény. Ha nem, akkor viszont további vizsgálatra van szükség – itt jön a második beágyazott HA függvény, ami megvizsgálja, hogy a forgalmi érték az kisebb-e, mint 200.000. Ha igen, akkor a vizsgált érték a 100.000 és 199.999-es sávban van, tehát 3% jár. Ha nem, akkor az érték csak 200.000, vagy annál nagyobb lehet, tehát 6% engedmény dukál.
Fontos, hogy a vizsgálat a sávok szerint növekvő, vagy csökkenő sorrendben történjen. És ne feledkezzünk meg a határértékek ellenőrzéséről! Az ellenőrzéshez nagyszerű eszköz a képletkiértékelő. Lépésről lépésre láthatjuk a műveletek sorrendjét és az eredményét. Az ábrán csak a legfontosabb lépéseket ábrázoltuk!
Jól láthatók a relációk kiértékelésének az ereményei, a logikai IGAZ, illetve HAMIS logikai értékek.
Csak a lényeg – érthetően!
Microsoft Excel 2010 – Függvényhasználat alapjai
3
ÉS FÜGGVÉNY Az ÉS függvény több feltételt (legfeljebb 255) tud megvizsgálni. IGAZ értéket ad eredményül, ha az összes argumentuma IGAZ, és HAMIS értéket, ha egy vagy több argumentuma HAMIS. A HA függvény döntéshozó lehetőségeit bővíthetjük a segítségével.
Az ÉS függvény igazságtáblája A argumentum
B argumentum
Eredmény
HAMIS
HAMIS
HAMIS
HAMIS
IGAZ
HAMIS
IGAZ
HAMIS
HAMIS
IGAZ
IGAZ
IGAZ
Az ÉS kapcsolat soros áramköri kapcsolással „modellezhető”. Csak akkor fog világítani a lámpa, ha minden kapcsolót bekapcsoltunk – azaz ha minden feltétel teljesült.
ÉS(logikai1;logikai2;...logikai255)
Az argumentumok logikai műveletek (pl. reláció), vagy az IGAZ, illetve HAMIS logikai értékek lehetnek, illetve olyan hivatkozások, amelyek logikai értéket tartalmaznak. Az argumentumokban az Excel a szöveget, és az üres cellákat figyelmen kívül hagyja. A példában azok kapnak 10.000 forint jutalmat, akik 2009-nél régebbtől képviselők ÉS 120.000-nél nagyobb forgalmat produkáltak. A feltétel „szigorú”, hiszen mindkét feltételnek teljesülni kell a jutalom eléréséhez. Akik nem kapnak jutalmat, azoknak a Jutalom mezőjében jelenjen meg a „nincs jutalom” szöveg. A feladatot HA függvénnyel oldjuk meg, a döntést egy beágyazott ÉS függvény segíti.
Emlékeztetőül: a szövegállandókat a függvényekben (képletekben) idézőjelek (”) között kell megadni.
