Excel függvények

Az Excelt sokrétűen használhatjuk a mindennapi munkánk során. Ez a program alkalmas különböző számítások elvégzésére, adatok szűrésére, rendezésre, vagy az adatok összefüggéseinek grafikus megjelenítésére diagramok formájában.

Az Excelt alkalmazhatjuk csupán adatok rögzítésére és kinyomtatására, de akkor nem léptük túl azokat a lehetőségeket, amelyet egy egyszerű „kockás füzet” adhat számunkra. Az Excelben végezhetünk számításokat és létrehozhatunk logikai összefüggéseket is.  Az egyes celláknak adhatunk értékeket, de a cellában található értékeket hivatkozások segítségével függővé tehetjük más cellák értékeitől. Ha például az B1 cellába beírjuk hogy: =123, ezzel értéket adtunk a cellának, ha viszont azt írjuk be hogy: =A1, akkor hivatkozást hoztunk létre. A B1 cella értéke ekkor az A1 cella értékével lesz egyenlő.

A hivatkozásokat másolhatjuk is, ekkor viszont a másolás következtében meg fog változni a hivatkozó cella! Az ilyen típusú hivatkozást relatív hivatkozásnak nevezzük, mivel másoláskor mind az oszlop (A), mind a sor (1) azonosítója megváltozhat. Fogalmazhatnánk úgy is, hogy ez a fajta kapcsolat a hivatkozott cella és a hivatkozó cella relatív elhelyezkedésére vonatkozik. A fenti esetben ez azt jelenti, hogy a cella értéke legyen egyenlő a balra mellette található cella értékével. Ha ezt a hivatkozást ebben a formájában másik cellába másoljuk, akkor ez a szabály a másolással tovább öröklődik, és a másik cella értéke is a balra mellette lévőével lesz azonos. Ha a másolás során olyan pozícióba kerül a hivatkozás, amely túlmutat a munkalap határain, akkor: #HIV! hibajelzést fogunk kapni.

Van azonban lehetőség olyan hivatkozás létrehozására is, amely másolás során megőrzi az eredetileg hivatkozott cella oszlop- és sorazonosítóját. Az ilyen típusú hivatkozást abszolút hivatkozásnak nevezzük. Ilyenkor a hivatkozásban az oszlop- és sorazonosítók előtt egy dollárjelet ($ – string) láthatunk. Az ilyen hivatkozás a másolás során megőrzi az eredetileg hivatkozott cella azonosítóit, tehát mindig ugyanoda fog mutatni. Abszolút hivatkozás például: =$A$1, ekkor az adott cella értéke – bárhová is másoljuk – mindig az A1 cella értékével lesz azonos.

Létrehozhatunk vegyes hivatkozásokat is, amikor egy cellának vagy csak az oszlop-, vagy csak a sorazonosítóját rögzítjük. Ilyen esetben a cella másik azonosítója másolás során a relatív hivatkozásban látottaknak megfelelően módosul. Vegyes hivatkozás például: =E43/I$42. Ilyen típusú hivatkozást olyan esetekben használhatunk, ha például egy táblázat számos elemét el kell osztanunk a táblázat egy adott cellájában található értékkel. Fenti példánkban másoláskor a sorazonosító rögzített marad.

A $ jelet beírhatjuk kézzel is, de praktikusabb, ha a hivatkozás létrehozása után a szerkesztőlécen a hivatkozásba kattintva az F4 funkcióbillentyű többszöri megnyomásával hozzuk létre a hivatkozás végleges alakját. Az alaphivatkozás: =E43/I42. Az egérkurzorral az I42-be kattintunk, majd az F4 billentyű többszöri megnyomásával megjelenő lehetőségek közül (rendre: $I$42, I$42, $I42, I42,) kiválasztjuk a kellő variációt. Ha az F4 funkcióbillentyű nem változtatja a hivatkozást, akkor az fn (funkció) gombot is nyomva kell tartani közben. Látható, hogy a negyedik gombnyomásra kimerítettük a lehetőségeket, és visszajutottunk az eredeti relatív hivatkozáshoz.

Definíció: A képletek (képlet: Egy adott cellában lévő értékek, cellahivatkozások, nevek, függvények vagy operátorok (műveleti jelek) sorozata, amelyek együttesen új értéket hoznak létre. A képletek mindig egyenlőségjellel, vagy + jellel kezdődnek.) olyan egyenletek, amelyekkel az Excel a munkalap adatain számításokat hajt végre.�

Definíció:  Az f: X >>> Y vagy röviden az f függvény olyan hozzárendelés, mely az X halmaz minden eleméhez hozzárendel egy Y-beli elemet. Az X az f függvény értelmezési tartománya, az Y azon elemeit pedig, amelyek a hozzárendelés során szóba jönnek, az f függvény értékkészletének nevezzük.

A cellákba nem csak hivatkozásokat írhatunk, hanem képleteket és függvényeket is, amelyben a hivatkozások felhasználhatók. A következőkben a függvény működését a matematikában megszokottnál némileg lazábban magyarázzuk el, kérem, hogy a matematikusok ugorják át a következő részt.

Egy függvénynek van egy vagy több kiindulási értéke (argumentum), van végeredménye, a kettő között pedig egy szabályrendszer, ami meghatározza, hogy a kiindulási értékből hogyan kapjuk meg a végeredményt. Egy egyszerű példa: GYÖK(225) = 15. Itt a 225 a kiindulási érték, a végeredmény a 15, a szabály pedig a gyökvonás.

Ha a szabály és a kiindulási adat valamilyen módon nem illik össze, nem kapunk eredményt. Lássunk erre is egy példát! Legyen a feladat a következő: „Kérem, adja össze a következő három számot: 4 és 7.” De hát hol a harmadik szám? Így nem lehet kiszámolni az eredményt! Bizony nem lehet. Az Excel is hibát fog jelezni, nem is hagyja beírni a függvényt, ha hiányzik valamely kiindulási érték.

Egyes függvényeknek nincsenek argumentumaik, látszólag nem kell hozzájuk semmilyen egyéb adat. Ilyen például a PI értéke, amelyet az Excelben egy függvény formájához hasonló módon kaphatunk meg: =PI(). Ennek eredménye rögzített: 3,1415927. Ilyen továbbá a mai dátum függvénye is: =MA(), amely a rendszerben tárolt aktuális dátumértéket adja vissza eredményként.

A függvényeket és a képleteket többféle módon is beírhatjuk a cellába:

a) Beírjuk kézzel, hogy: =C3+12

b) Vagy begépeljük, hogy: =HA(D4>$H$7;5;HA(D4>$H$6;4;HA(D4>$H$5;3;HA(D4>$H$4;2;1))))

Valljuk be, ez utóbbi azért ritkán szokott elsőre sikerülni.

Ha bonyolultabb, több kiindulási adattal rendelkező vagy több egymásba ágyazott függvényt tartalmazó képletet szeretnénk rögzíteni, akkor praktikus, ha az f_x (függvény beszúrása) gombot használjuk a szerkesztőléc elején. Ezzel megkapjuk a függvények listáját, amelyből a számunkra megfelelő függvény kiválasztása után a hozzá tartozó argumentumok kitöltésében és azok jelentéstartalmának értelmezéséhez is kapunk segítséget. Érdemes tehát a párbeszédpanelen megjelenő szöveget figyelmesen elolvasni Ha mindez mégsem elegendő, a párbeszédpanel bal alsó sarkában lévő súgóra kattintva további útmutatáshoz és példákhoz juthatunk. Ha jól adjuk meg az adatokat, akkor a párbeszédablakban már a függvény szerkesztése közben láthatjuk az előzetes eredményt, illetve ha ezt nem látjuk, akkor sejthetjük, hogy hiba van valahol.

A többszörösen egymásba ágyazott függvények esetében azonban ez nem segít. Ha ugyanazt a függvényt kell többször egymásba ágyazni, akkor az alapfüggvényt elkészítve, azt a szerkesztőlécről kimásolva önmagába többször beilleszthetjük. Utólag persze kisebb módosításokat kell még elvégezni, de a nehezén ezzel már túl vagyunk.

Nézzünk egy példát! A diákok dolgozatot írtak. Feladat olyan képlet létrehozása, amely egy segédtáblázat alapján az elért pontszámuknak megfelelő érdemjegyet írja a cellába. 

A példában szereplő logikai HA függvény felépítése: =HA(logikai feltétel;igaz ág;hamis ág). Ennek alapján legyen az eredeti (kiindulási) függvényünk: =HA(D4>$H$7;5;4)

A kiindulási függvényt a hamis ágba a 4-es szám helyére többször bemásolva egymásba ágyazzuk: =HA(D4>$H$7;5;HA(D4>$H$7;5;HA(D4>$H$7;5;HA(D4>$H$7;5;4))))

Végül, a ponthatárok hivatkozásait és az érdemjegyeket kiigazítva megkapjuk a kész képletet: =HA(D4>$H$7;5;HA(D4>$H$6;4;HA(D4>$H$5;3;HA(D4>$H$4;2;1))))

A függvény argumentumait úgy is módosíthatjuk, hogy kijelöljük a függvényt tartalmazó cellát, majd megnyomjuk az F2 funkcióbillentyűt. Ennek hatására azok a cellák, amelyekre a függvényben hivatkoztunk, színes keretet kapnak; a függvényt magát tartalmazó cellában pedig szerkesztési módba lépünk.

A színes keret a cella áthelyezéshez hasonlóan mozgatható, így a függvényargumentum hivatkozása grafikusan is módosítható. Ahogy a keretet mozgatjuk, úgy változik a hivatkozás a cellában. Ha ennél még szemléletesebbé szeretnénk tenni a függvény felépítését és kiindulási adatait, akkor használhatjuk a Nyilak megjelenítése funkciót, amelyet a Képletek ► Képletvizsgálat ► Elődök mutatása és Utódok mutatása menüpontokban érhetünk el.  E funkciók bekapcsolásával jól látható nyilakkal kerülnek jelölésre azok a cellák, amelyekhez az adott képlet hivatkozásokkal kapcsolódik. Az Elődök mutatása azokra az adatokra mutat, amelyek a képlet kiszámításához szükségesek, míg az Utódok mutatása azt mutatja meg, hogy egy adat vagy eredmény hol került további felhasználásra. Egy cella tartalmának törlése előtt érdemes megnézni, hogy felhasználásra került-e valahol az adott cellában található adat, mert ha igen, és mégis töröljük, akkor a hiánya biztosan hibát fog okozni.

Hivatkozások létrehozásakor tipikus hibalehetőség a körkörös hivatkozás, amikor egy képlet�közvetlenül vagy közvetve a saját cellájára hivatkozik. Ha például a C2 cellába beírjuk, hogy: =A2+C2, akkor ennek értéke kiszámíthatatlan lesz. Szerencsére az Excel már a cellába való beíráskor hibajelzést ad, ha körkörös hivatkozást készülünk létrehozni. Ha ennek ellenére erőltetjük a dolgot, és figyelmen kívül hagyjuk a hibajelzést, akkor rögzíthetjük ugyan ezt az állapotot, de az elődök és utódok mutatásához hasonló figyelmeztető nyíl azért látható marad a munkalapon.

Hivatkozásokat nem csak egy munkalapon belül hozhatunk létre, hanem van mód további oldalakra való hivatkozásra is. Ha egy hivatkozás az aktuális munkalapon túl mutat, akkor egy további adat, a hivatkozott munkalap nevének magadása is szükséges az alábbi formában: =Munka2!A1. Az egyenlőségjel után ilyenkor a munkalap neve és egy felkiáltójel látható, majd ez után következik az oszlop- és sorazonosító.

Amennyiben egy másik munkafüzetből szeretnénk adatokat átvenni, akkor a hivatkozás további kiterjesztésére van szükség: ='[Tudásszint bemutató.xls]Alapadatok’!$C$7. Itt szögletes zárójelben megjelenik annak a munkafüzetnek neve is, ahol a hivatkozott cella található. Másik munkafüzetre mutató hivatkozást is létrehozhatunk egyszerűen, néhány kattintással. Nyissuk meg mindkét munkafüzetet, jelöljük ki azt a cellát, ahová a hivatkozást szeretnénk beilleszteni, írjunk be egy egyenlőségjelet, majd váltsunk át a másik munkafüzetre, itt kattintsunk bele a hivatkozni szándékozott cellába, és üssük le az Enter billentyűt. Másik munkafüzetre történő hivatkozásnál azonban ügyelni kell arra, hogy a hivatkozott munkafüzet nehogy törlésre vagy átnevezésre kerüljön!

A függvényeket a megfelelő cél érdekében kombinálhatjuk is, ennek az eredménye az alábbihoz hasonló többszörösen összetett függvény lehet: =ÖSSZEFŰZ(BAL(D5;2);$E$11;KÖZÉP(D5;3;$E$13);$E$12;KÖZÉP(D5;$E$13+3;$E$14))

Ez a függvény a folyamatos számsorként megjelenő telefonszámokat (pl.: 201234567) egy jobban olvasható formára (pl.: 20/123-4567) alakítja. Ehhez az ÖSSZEFŰZ, a BAL, és a KÖZÉP nevű szövegfüggvényeket használtuk fel. A függvények argumentumainak megadásánál éltünk azzal a lehetőséggel, hogy nem feltétlenül muszáj konkrét értéket megadnunk, hanem lehetőség van hivatkozás megadására is, sőt, a fenti függvénybe még egy kis számítás is ($E$13+3) belekerült.

A végezetül néhány gyakran szükséges függvény:

Dátumfüggvények
=MOST()	Az aktuális dátumot és időt jeleníti meg, a táblázat újraszámolásakor frissül.
=MA()	Az aktuális dátumot jeleníti meg, a táblázat újraszámolásakor frissül.
=ÉV(C5)	Egy dátumértékből megjeleníti az évet. 2011.02.16 → 2011
=HÓNAP(C5)	Egy dátumértékből megjeleníti a hónapot. 2011.02.16 → 02
=NAP(C5)	Egy dátumértékből megjeleníti a napot. 2011.02.16 → 16
=HÉT.NAPJA(C4)	Egy dátumértékből megjeleníti a nap sorszámát. 2011.03.12 → 7
Matematikai függvények
=KEREKÍTÉS(C11;1)	Egy számot adott számú számjegyre kerekít.
=KEREKÍTÉS(C11;3)	Egy számot adott számú számjegyre kerekít.
=KEREK.FEL(C11;2)	Egy számot felfelé, a nullától távolabbra kerekít.
=KEREK.LE(C11;2)	Egy számot lefelé, a nulla felé kerekít.
=SZORZATÖSSZEG(E18:E20;F18:F20)	Megadott tömbök megfelelő elemeit szorozza össze, majd kiszámolja a szorzatok összegét.
=SZUMHA(E23:F29;”Budapest”;F23:F29)	Egy tartomány azon értékeit adja össze, amelyek megfelelnek a megadott feltételnek.
Szövegfüggvények
=ÖSSZEFŰZ(E33;F33)	Két vagy több szövegrészt egyetlen szöveggé egyesít.
=BAL(E33;2)	Egy szöveg bal oldali karaktereiből jelenít meg adott számút.
=JOBB(E34;2)	Egy szöveg jobb oldali karaktereiből jelenít meg adott számút.
=CSERE(E35;3;2;”XX”)	Egy szöveg adott részét másik karaktersorozatra cseréli.
=HELYETTE(E36;”-„;”_”)	Egy szövegben adott karakterek előfordulásait cseréli ki más karakterekre.
=SZÖVEG.KERES(„-„;E37)	Egy szövegben egy adott karaktersorozatot keres. Eredményül az első karakterlánc (-) első karakterének helyét adja meg a második (az E37 cellában lévő) karakterlánc elejétől számítva.
Logikai függvények
=HA(E40=F40;”Kell az ernyő”;”Nem kell ernyő”)	Egy logikai vizsgálat után annak IGAZ vagy HAMIS voltának megfelelő eredményt ad vissza.
=ÉS(E40=”Esik”;E43=”Hideg”)	Értéke IGAZ, ha mind a két logikai vizsgálat eredménye IGAZ.
=VAGY(E40=”Esik”;E43=”Hideg”)	Értéke IGAZ, ha legalább az egyik logikai vizsgálat eredménye IGAZ.

Excel Expert Team, Tatai István (In: PC World Magazin, 2011)