Amire ma már van függvény - NAPOK

Az Excel igen sok verziója van használatban jelenleg is. A régebbi verziókban kevesebb függvény volt, ezért egy új verzió birtokba vételekor érdemes megnézni, milyen függvények vannak.
Természetesen az ember nem néz végig több száz függvényt, de a gyakran használtakat és azok kategóriáit érdemes böngészni.

Kedvenc szórakozásom, hogy a legnagyobb nyári forróságban tájékoztatom ismerőseimet arról, hány nap van karácsonyig. Már évek óta jelzem a a 150 napot. Idén már hamarabb szóltam, mindenkinek legyen ideje felkészülni. :-)

Azt, hogy két dátum között hány nap van, egyszerű módszerrel ki lehet számolni.
Egy cellába beírjuk az egyik dátumot, egy másikba a másikat. Egy harmadik cellába kerül a képlet.
Egyenlőségjel után hivatkozunk a későbbi dátumra, és kivonjuk belőle a korábbi dátumot.

Két nap között levő napok számát számoljuk

A képlet

 Ekkor dátum formátumban kapjuk meg az eredményt.
Ezen cellaformázással tudunk javítani. Tehát formátum/cellák/szám.

dátum átalakítása számmá

A formázás után ezt látjuk:

Cellaformázás után a dátumból szám lett

Az Excel 2011-től kezdve azonban más módszer is van.
A NAPOK függvény. Lényegében ugyanazt csinálja, mint amit fentebb bemutattam, csak nem kell a dátum formátumot átalakítani, mert számként adja meg az eredmény.

Használata két módon lehetséges:

1. Beírjuk a dátumokat a képletbe pl így: =NAPOK("16/12/24";"16/02/04")
   Figyeljünk arra, hogy idézőjelek között legyen a dátum, az elválasztó pedig pontosvessző
2. Cellahivatkozásokkal =NAPOK(A2;A1)Először a végdátum cellája, utána a kezdődátum cellája következik.

A NAPOK függvény használatakor az eredményt számként kapjuk meg, itt a 324-et.

Függvényvarázsló vagy beírás

Az Excel millió függvényét a varázsló segítségével könnyedén (?) be tudjuk állítani.

Az egyszerűbb függvényeket megtanulva, gyorsabban tudunk dolgozni.

Például, a SZUM függvény használható úgy is, hogy

1. az ikonok közül kiválasztjuk a jelét, és egérrel megmutatjuk, miket is kell összeadnia.
2. a függvények közül választjuk ki a nevét, de ezt így minek. :-)
3. a szerkesztőlécre begépeljük a =SZUM -ot, majd zárójelben megadjuk a cellahivatkozásokat. Pl. A1:A34. A tartományt kettősponttal választja el az Excel.

A begépelős megoldás ismerete akkor is hasznos, ha az egerünk nem túl jól, vagy  a laptopon a tapipad használata nehézkes.

HOSSZ - karakterszámláló

Szükségünk lehet arra az információra, hogy egy-egy cellában milyen hosszú szöveget tárolunk.
Például, szeretnénk egy új adatbázis készíteni, de nem tudjuk, milyen hosszú szövegnek kell elférnie egy mezőben.
Ehhez kaphatunk segítséget a HOSSZ függvény használatával.

A HOSSZ függvény a szöveg karakterekben mért hosszát adja eredményül. Ne feledjük, a szóközök is karakternek számítanak!

A függvény használata:

HOSSZ függvény használata

Azonos-e két szöveg?

Lélekölő munka, ha két oszlopban végig kell vizsgálnunk, hogy minden nevet, elnevezést, vagy más szöveget jól írtunk-e.
Lehet, nem is a jóságot nézzük, hanem azt, hogy a két oszlopban levő szövegek betűről betűre megegyeznek-e.
Ezek nem csak értelmes szavak lehetnek, hanem karaktersorozatok, kódok, képletek, bármi.

Ezt a feladatot is el tudja végezni az Excel.

A használt függvény az AZONOS függvény.

Ha két karaktersorozatot összehasonlítunk, akkor nem számot kapunk eredményül, hanem azt, hogy azonos-e a két szöveg vagy sem. Ezt viszont nem így jelzi nekünk az Excel, hanem logikai értékkel. Vagyis, ha a két összehasonlított karaktersorozat mindenben megegyezik, akkor az érték, amit kapunk az IGAZ, ha pedig bármilyen eltérés van a két cella tartalma között a HAMIS értéket írja ki.

A függvény használata:

AZONOS függvény használata

A képen is látható, hogy a kis és nagybetűk eltérésére is érzékeny, és természetesen a szóközök különbözősége is hamis eredményt fog adni.
Nem értelmet néz, hanem azt, hogy betűről betűre megegyezik-e a két karaktersorozat.

Szöveg összefűzése

Előfordul, hogy különböző cellákban levő szöveget kell egyesítenünk, akár úgy is, hogy még plusz szöveget is írunk mellé.

Erre a feladatra az ÖSSZEFŰZ nevű függvényt használhatjuk.

Hogyan működik?

ÖSSZEFŰZ (szöveg1;szöveg2;...) ahol a szöveg1, stb helyére az összefűzni kívánt szöveget tartalmazó cella hivatkozása kerül. Szöveget is írhatunk közé, számokat is, sőt a szóköz is karakter, tehát azt is.

A függvény használata:

Összefűz függvény működése

A mintán látszik, hogy az argumentumokat pontosvesszővel kell elválasztani egymástól, és ahol az üres karaktert látjuk, az a szóköz, ami idézőjelek közé kerül.

TNÉV függvény

A TNÉV függvény segítségével a szavak első betűjét nagybetűssé, többi betűjét pedig kisbetűssé alakíthatjuk.

Mikor van erre szükség?
Például a vÁLTÓ eSET-ben, vagyis, amikor véletlenül lenyomva maradt a caps lock, és úgy írtuk be az adatokat.

Az is lehet, valamilyen alkalmazásból hoztunk át adatokat, ahol csak kis vagy nagybetűt használtak.

A függvény használata:

TNÉV függvény használata

A függvény a karaktersorozat első betűjét, valamint a nem betű után álló betűket nagybetűsre változtatja. Az összes többi betűt kisbetűssé alakítja.

ABS - a szám nagysága

A számok lehetnek negatívak és pozitívak.
Ha a szám nagysága érdekel minket, akkor a szám abszolút értékét kell csak néznünk, vagyis a számot előjel nélkül.

A függvény használata:

ABS függvény használata

ABS(szám)

Az ABS függvény egy szám abszolút értékét adja eredményül, ami maga a szám, előjel nélkül.
Szám:   az a valós szám, amelynek az abszolút értékét keressük.
Fontos: csak számnak van abszolút értéke!

MÓDUSZ - a leggyakoribb

A MÓDUSZ függvény a kijelölt tartomány leggyakrabban előforduló értékét keresi meg.
Az érték csak szám lehet!

A MÓDUSZ függvény az üres cellákat, logikai értékeket, szöveget és hibaüzeneteket figyelmen kívül hagyja, de a nullát tartalmazó cellákat számításba veszi.
Ha az adathalmazban nincs két egyforma adatpont, a MÓDUSZ függvény a #HIÁNYZIK hibaértéket adja eredményül.

Módusz függvény használata

Statisztikai számításokban gyakran az alábbi három függvényt együtt használják.

Melyik mit tud?

• A módusz a leggyakrabban előforduló érték,
• a medián a középső érték,
• az átlag pedig a középérték.

Miért használnak több módszert a számításokhoz? Azért, mert az adathalmazról egy mérőszám nem ad teljes információt.

MEDIÁN - középső érték

A MEDIÁN függvény adott számhalmaz középső értékét adja meg.

A medián a számhalmaz középső értéke, a számok fele ennél kisebb, másik fele pedig nagyobb.
Tehát nem átlagol, hanem a kijelölt területen megkeresi azt a számot, aminél a számok fele kisebb, a számok másik fele pedig nagyobb.

Medián függvény használata

A függvény csak a számokat használja, az üres cellákat, logikai értékeket, szöveget és hibaüzeneteket figyelmen kívül hagyja, de a nullát tartalmazó cellákat számításba veszi.

Ha a számhalmazban a számértékek párosan fordulnak elő, akkor a MEDIÁN a középső két szám átlagát fogja eredményül adni.

Nézzük példán:

Páros elemszám esetén: 1 2 5 4 5 3 4 2 1 5 3 4
A rendezett sokaság:  1 1 2 2 3 3 4 4 4 5 5 5
A medián a középső elemek számtani közepe:  3,5
Páratlan elemszám esetén: 1 2 5 4 5 3 4 2 1
A rendezett sokaság: 1 1 2 2 3 4 4 5 5
A medián a középső elem: 1 1 2 2 3 4 4 5 5

MÉRTANI.KÖZÉP - számoljunk mértani közepet

A MÉRTANI.KÖZÉP függvény pozitív számokból álló tömb vagy tartomány mértani középértékét adja meg.

A függvény a kijelölt területen szereplő értékek közül csak a számokat használja, az üres cellákat, logikai értékeket, szöveget és hibaüzeneteket figyelmen kívül hagyja, de a nullát tartalmazó cellákat számításba veszi.

Fontos!
Ha bármelyik argumentum ≤ 0, akkor a MÉRTANI.KÖZÉP a #SZÁM! hibaértéket adja eredményül.

Mértani közép számítása

A mértani közép kiszámítása

Az nem negatív számok G mértani közepe:

A mértani közép számításának képlete

A mértani középről bővebben olvashatunk a Wikipédián is, és a tankönyvekben.

MIN - legkisebb szám

A MIN függvény, nagyon egyszerű dolgot tud, a kijelölt cellákban levő számok közül kiválasztja a legkisebbet.

Nem kell lebecsülni mégsem, mert ha nem 3-4 szám közül kell kiválasztani a legkisebbet, hanem mondjuk 19-ből, és ezek a számok nem egy jegyűek, hanem mondjuk 4-5 jegyűek, akkor ezt ránézésre nem fogjuk megmondani.

Minimum függvény használata

Ez a függvény a számok között keresi a legkisebbet, üres cella, logikai érték, szöveg figyelmen kívül lesz hagyva.

MAX - legnagyobb szám

A MAX függvény nagyon egyszerű dolgot tud, a kijelölt cellákban levő számok közül kiválasztja a legnagyobbat.

Nem kell lebecsülni mégsem, mert ha nem 5-6 szám közül kell kiválasztani a legnagyobbat, hanem mondjuk 27-ből, és ezek a számok nem egy jegyűek, hanem mondjuk 4-5 jegyűek, akkor ezt ránézésre nem fogjuk megmondani.

Maximum függvény használatban

Ez a függvény a számok között keresi a legnagyobbat, üres cella, logikai érték, szöveg figyelmen kívül lesz hagyva.

DARABÜRES - Az üresek fontosak

A DARABÜRES függvény az üres cellákat számolja össze a tartományban. A nulla is érték!

Darabüres függvény használata

Azokat a cellákat is figyelembe veszi, melyek képletet tartalmaznak, és az eredmény üres cellát eredményez.
Például, a cella azt tartalmazza, írjon ki egy számot, ha teljesül egy feltétel. Amennyiben ez a feltétel nem teljesül, akkor nem ír ki semmit, vagyis a DARABÜRES függvény szempontjából ezt a cellát is bele kell számítani az üres cellák közé.

DARABTELI - feltételekkel

A DARABTELI függvény is számlál. A kijelölt cellákban megszámlálja, hány olyan cella van, ami megfelel a megadott feltételnek.

Darabteli függvény

Az A1:A5 tartományban vizsgáljuk meg, hány olyan cella van, ami 5-nél nagyobb értéket tartalmaz.

A függvény használatakor két dolgot kell megadnunk.
Az első az a tartomány, ahol meg kell számlálni a cellákat, melyek megfelelnek a második részben megadott feltételnek (kritériumnak), jelen esetben annak, hogy >5.

A függvény általánosan így írható le: =DARABTELI(tartomány;kritérium)

Darabteli, szöveges feltétellel

Nem csak számokat lehet megadni feltételként, hanem szöveget is.

DARAB2 - Ez is számlál

A DARAB2 függvény nagyban hasonlít a DARAB függvényre, de itt a kijelölt területen a nem üres cellákat és értékeket számlálja össze.

DARAB2 függvény

A függvény minden adattípust értéknek tekint, még az üres szöveget ("") is. Az üres cellákat viszont  nem veszi értéknek.
Ha nem kell logikai értékeket, szöveget vagy hibaértéket számlálnunk, használjuk a DARAB függvényt!

DARAB - számokat tartalmazó cella

A DARAB függvény arra való, hogy megszámolja, a kijelölt területen hány olyan cella van, ami számot tartalmaz.
Csak a számokat veszi figyelembe, dátumot sem!

Darab függvény

A képen a szerkesztőlécen látható a függvény, az A6 cella a függvény eredményét mutatja.

ÁTLAG - Átlagolás táblázatkezelőben

Az átlag mindenki számára ismerős, magyarázni nem kell sokat.

Kíváncsiak lehetünk arra, hogy naponta átlag hány forintot költünk, hogy havonta átlag hány köbméter gáz fogy el, stb.

A lényeg, az összegyűjtött adatok alapján számoljuk ki az átlagos költést, fogyasztást, stb.

Emlékszünk, az általános iskolában is tanultuk a módszert: összeadjuk az átlagolandó számokat, majd elosztjuk annyival, ahány számot átlagolunk. Ez a számtani átlag.

Az Excelben egy kicsit másként kell megadni az argumentumokat, de ez azt jelenti, tudnunk sem kell, hogyan kell átlagot számolni. Azt a függvény tudja, egy feladatunk van, kijelölni azokat a cellákat, amiket az átlagszámításnál figyelembe kell venni.

Átlagfüggvény

Az átlag kiszámítva

      

A bal oldali képen az átlagfüggvényt látjuk. A függvény neve után zárójelben az argumentumok, vagyis az a tartomány, melyben az átlagolandó számok vannak.

A jobb oldalon azt látjuk, amikor már kiszámolta az Excel az előbbi képletet.

SZUMHA - összeadás másképpen

Gyakran előfordul, hogy nem minden számot szeretnék összeadni egy oszlopban, csak azokat, amelyek megfelelnek valamilyen feltételnek.

Például szorgalmasan írjuk a kiadásainkat, és tudni szeretnénk, mennyi pénzt szórunk el butaságokra, amik nem kerülnek 100 Ft-ba sem.

Meg tudjuk mondani az Excelnek, hogy a számok közül csak azokat adja össze, melyek kisebbek, mint 100.

Nem kell a listát rendezgetni, és egyéb trükköket alkalmazni, mert erre találták ki a SZUMHA függvényt.

A fenti példa megoldásakor így néz ki a SZUMHA függvény:

=SZUMHA(A1:A39;<100)

Tehát az A oszlop első 39 cellájában megkeresi azokat a számokat, amik kisebbek, mint 100, éa össze is adja azokat.

A zárójelben a tartomány megadása után egy pontosvessző van, ez az Excelben a listaelválasztó. Utána relációs jelekkel megadjuk a feltételt.

A függvényeket varázsló segítségével is használhatjuk, de nem árt, ha értelmezni tudjuk, mi szerepel a képletben.
A képleteket a szerkesztőlécen tudjuk szerkeszteni, átírni. Ha tudjuk, mit és hogyan, milyen sorrendben kell beírni a képletbe, akkor azt nyugodtan megtehetjük a szerkesztőlécen is. Eredménye ugyanaz lesz, mintha a varázslót használtuk volna.

Tehát a SZUMHA függvény valamilyen feltételnek megfelelő számokat ad össze a kijelölt cellákra vonatkozóan.

SZUM függvény - összeadás

Ez a legegyszerűbb függvény. Ha a középiskolai tanulmányainkra visszagondolunk, akkor csodálkozhatunk is, ez miért függvény, hiszen ez egy egyszerű összeadás.

Az Excel mindent, amikor számítást végez, és ahhoz valamilyen képletet használ fel, függvénynek nevez.

A SZUM függvény számokat fog összeadni.
Számokat, ezt azért érdemes kiemelni, mert nem minden szám, ami annak látszik.

A számok beírásáról részletesen itt lehet olvasni:

SZUM függvény

Mit látunk a képen?
Három számot írtam a táblázatba, ezek a 3, 4, 6.
Az A4 cellában szeretném látni a 3 szám összegét.

A SZUM függvény azért is különleges, mert még ikonja is van, a szumma jel. Ʃ
Az eszköztáron is megtaláljuk.
Tehát a Ʃ jelet kell megnyomnunk, és ekkor látjuk a kijelölést, amit a képen a szaggatott vonal jelez.
Azt akarja összeadni az Excel, amit kijelölt. Ha ez nekünk megfelel, akkor megnyomjuk az Entert, és készen is vagyunk.

Lehet, hogy nem jó számokat jelölt ki, akkor nekünk kell a kijelölést elvégezni.
Vagy tartományokat jelölünk ki, ahogy itt olvasható, vagy egyesével kijelöljük az összeadandó számokat. Közben a Ctrl billentyűt is nyomni kell, mert akkor tudunk egyszerre több dolgot is kijelölni.

Leírva bonyolultabb a használata, mint a gyakorlatban.
A képen látható maga a képlet is.
=SZUM(A1:A3)
Jelentése:
minden képlet, függvény egyenlőség jellel kezdődik, tehát ez is.
SZUM a függvény neve
zárójelben az Excel által argumentumoknak nevezett dolgok vannak. Magyarul, azoknak a celláknak az elnevezése, amelyekben azok a számok vannak, amiket össze akarunk adni.
A1:A3 egy tartományt jelez, bal felső sarok az első rész, itt A1,  a jobb alsó sarok pedig az A3, a : után látható.

Ha a beírt számokat megváltoztatjuk, az Excel átszámolja a függvényt, és azonnal az aktuális összeget fogjuk látni.

Az Excel függvényeiről

Az Excelben a függvények több kategóriába vannak csoportosítva.
Minden kategória meghatározott függvényeket tartalmaz. Olyan függvény nincs, ami egynél több kategóriába van besorolva.

Tekintsük át ezeket a kategóriákat:

• Adatbázisfüggvények
• Dátum és idő
• Külső függvények
• Mérnöki függvények
• Pénzügyi függvények
• Információs függvények
• Logikai függvények
• Kereső és hivatkozási függvények
• Matematikai és trigonometriai függvények
• Statisztikai függvények
• Szöveg- és adatfüggvények

Blogunkban a kategóriákból csak néhányat érintünk.

A hétköznapi feladatok során ennyi fajta függvényre nincs szükségünk.
Azt hiszem, bizton állíthatom, hogy nincs olyan ember, aki valamennyi, az Excelben szereplő függvényt tud használni, érti, mire való, mikor és miért használhatjuk.

Egy átlagos irodai munka során a függvénykategóriák felével ha találkozunk, és a kategóriákban szereplő sok-sok függvény közül csak néhánnyal kell megismerkedni.

Azt is tudjuk, egy feladatot sokszor többféleképpen is meg lehet oldani, lehet, hogy két ember ugyanannak a feladatnak a megoldásához más függvényeket használ fel.

Blogunkban azokat a függvényeket mutatjuk be, melyeket gyakran használunk az irodai munkában, melyeket "illik ismerni".