Mintafeladat

Függvény összefoglaló

Függvények

10 függvény

SZUM függvény Ezzel a függvénnyel cellaértékeket adhat össze.

HA függvény

Ezzel a függvénnyel aszerint adhat vissza egy értéket, hogy egy feltétel teljesül-e. Íme egy videó a HA függvény használatáról.

KERES függvény

Ezt a függvényt akkor használhatja, ha egyetlen sorban vagy oszlopban szeretne keresni, és egy második sor vagy oszlop azonos pozíciójában lévő értéket szeretné megtalálni.

FKERES függvény

Ezt a függvényt akkor használhatja, ha egy táblázatban vagy tartományban sor alapján szeretne megkeresni értékeket. Például megkeresheti egy alkalmazott vezetéknevét az alkalmazotti azonosítója alapján, vagy telefonszámát a vezetékneve alapján (mint egy telefonkönyvben). Nézze meg ezt a videót az FKERES függvényről.

HOL.VAN függvény

Ez a függvény egy elemet keres egy cellatartományban, és az elem tartománybeli relatív pozícióját adja eredményül. Ha például az A1:A3 tartomány az 5, 7 és 38 értékeket tartalmazza, akkor a =HOL.VAN(7;A1:A3;0) képlet eredménye 2 lesz, mivel a 7-es érték a tartomány második eleme.

VÁLASZT függvény

Ezzel a függvénnyel az indexszáma alapján egy értéket választhat legfeljebb 254 értékből. Ha például az érték1; ... ; érték7 argumentum a hét napjainak neve, akkor a VÁLASZT függvény a napok egyikét adja eredményül, ha az index értékének egy 1 és 7 közötti számot ad meg.

DÁTUM függvény

Ez a függvény a megadott dátumnak megfelelő sorszámot adja eredményül. Akkor nagyon hasznos, ha az évet, a hónapot és a napot képletek vagy cellahivatkozások adják meg. Előfordulhat például, hogy egy munkalapon olyan formátumban vannak megadva a dátumok (pl. ÉÉÉÉHHNN formátumban), amelyet az Excel nem ismeri fel.

Két dátum közé eső napok, hónapok vagy évek számának kiszámításához használja a DÁTUMTÓLIG függvényt.

NAPOK függvény Ez a függvény a napok számát adja vissza két dátum között.

SZÖVEG.TALÁL és SZÖVEG.TALÁL2 függvény

A SZÖVEG.TALÁL és a SZÖVEG.TALÁL2 egy szöveges karakterláncot keres egy másikban. Visszaküldik az első karakterlánc első karakterének helyét a második karakterlánc elejétől számítva.

INDEX függvény Ez a függvény egy értéket vagy egy értékre mutató hivatkozást ad vissza egy táblázatból vagy egy tartományból.

A 30 legfontosabb Excel függvény

Alap függvények: SZUM (SUM), ÁTLAG (AVERAGE), MIN (MIN) MAX (MAX), DARAB (COUNT), DARAB2 (COUNTA), DARABÜRES (COUNTBLANK)

Logikai és kereső Excel függvények: HA (IF), ÉS (AND), VAGY (OR), FKERES (VLOOKUP)

Összesítő és kerekítő Excel függvények: SZUMHA (SUMIF), SZUMHATÖBB (SUMIFS), DARABTELI (COUNTIF), DARABHATÖBB (COUNTIFS), KEREKÍTÉS (ROUND), KEREK.FEL (ROUNDUP), KEREK.LE (ROUNDDOWN), PADLÓ (FLOOR), PLAFON (CEILING)

Szöveges és dátum Excel függvények: ÖSSZEFŰZ (CONCATENATE), BAL (LEFT), KÖZÉP (MID), JOBB (RIGHT), AZONOS (EXACT), DÁTUM (DATE), ÉV (YEAR), HÓNAP (MONTH), NAP (DAY), MA (TODAY)

Alap Excel függvények

Az alap összesítések, statisztikák. Tegyük fel, hogy vállalkozó vagy, és vannak ügyfeleid, mi érdekel: hány ügyfél van, mennyi bevételt hoztak, átlagosan egy ügyfél mennyit költött (kosárérték), és milyen értékek között mozognak a vásárlások.

De ugyanezek a statisztikák érdekesek egy osztály esetén is: hány tanuló van, mi az átlagos tanulmányi eredmény, például a vizsgán elért pontszámok (az összeg itt kevésbé érdekes), és milyen értékek között mozognak a pontok.

1. SZUM (SUM)

A Szum (a menün AutoSzum szerepel) függvény összesíti a kijelölt tartományon belüli értékeket - jellemzően sorokat vagy oszlopokat. Akár egymástól távoli cellák is kijelölhetőek a Ctrl segítségével (a képletben pontosvessző jelöli). Példák:

=SZUM(A2:A25)
=SZUM(A5:D80)
=SZUM(A:A)
=SZUM(A2:A25;E2:E25)

2. ÁTLAG (AVERAGE)

Az ÁTLAG függvény nagyon hasonló SZUM függvényhez, viszont a végösszeg helyett az egyes elemek átlagát számolja ki. Az üres cellákat és szövegeket figyelmen kívül hagyja. Ha átlagot számolsz, gondold át, hogy nullát írsz a cellába, vagy üresen hagyod.

Példák:

=ÁTLAG(A2:A25)
=ÁTLAG(A5:D80)
=ÁTLAG(A:A)
=ÁTLAG(A2:A25;E2:E25)

Az ÁTLAG függvény használatáról nézd meg ezt a videómat.

3. MIN (MIN), 4. MAX (MAX)

A Minumum, Maximum függvény is nagyon hasonló a SZUM, ÁTLAG függvényekhez. Megmondja, hogy a bemeneti értékek közül melyik a legalacsonyabb, illetve a legmagasabb szám. Itt is megadhatsz cellákat, oszlopot, akár többet is és egymástól távolabb lévőt is. Pl:

=MIN(A2:D15)
=MAX(A2:A25;E2:E25)

5. DARAB (COUNT), 6. DARAB2 (COUNTA), 7. DARABÜRES (COUNTBLANK)

A három DARAB függvény hasonlóan működik: a függvény beírása után jelöld ki a megszámolni kívánt cellákat.

• A DARAB függvény csak a számokat számolja meg.
• Ha a szövegeket is (azaz a nem üres cellákat) szeretnéd megszámolni, használd a DARAB2 függvényt.
• Ha csak az üres cellákat szeretnéd megszámolni, akkor használd a DARABÜRES függvényt.

Például megtudhatod, hány tranzakció / ügyfél / jelentkező stb. van a listádban, vagy hányan válaszoltak / nem válaszoltak egy adott kérdésre.
=DARAB(A:A)
=DARAB2(A2:A25;E2:E25)
=DARABÜRES(A2:A25)

A 14-15-ös pontban 2 további, feltételes darab függvényt is megismerhetsz. A DARAB függvényekről még itt írtam.

Logikai és kereső Excel függvények

8. HA (IF)

Megvizsgál egy összehasonlítást, és ettől függően írja ki az eredményt. Például jelzi, ha nagyobb terület szükséges, mint amennyi megvan. Vagy a laptop vásárlóknak jár 20% kedvezmény

= HA (A2 > B2; "További terület";"Terület rendben")
= HA (D2="laptop"; E2*0,8;E2)

A HA függvény használatáról ide a linkre kattintva találsz egy részletes összefoglalót.

9. ÉS (AND), 10. VAGY (OR)

Gyakran halmozni kell a feltételeket, ilyenkor meg kell mondani, hogy mi a közöttük lévő kapcsolat.

Ha például a B oszlopban szerepel az életkor, és a D oszlopban a férfi/nő adat, akkor az 50 év feletti nők:

=ÉS(B2>50;D2="nő")

Ha azokat az ügyfeleket keresed, akik legalább 5x vásároltak (C oszlop), vagy minimum 100.000 Ft értékben (E oszlop), akkor:

=VAGY(C2>=5;E2>=100000)

Önmagában IGAZ/HAMIS eredményt ad ki. (A logikai műveletekről bővebben itt olvashatsz.) Ha ezt szeretnéd másra "lecserélni", akkor építs köré egy HA függvényt, pl:

=HA(VAGY(C2>=5;E2>=100000);"VIP";"átlag")

11. FKERES (VLOOKUP)

Az FKERES már egy bonyolultabb függvény, érdemes alaposabban megismerni. A lényege, hogy egy listából kikeres egy konkrét adatot, például adott készülékhez kikeresi a fogyasztását, vagy egy ajtó típushoz a pontos méretet.

= FKERES (E2; A:B; 2; 0)

További segédanyagok az FKERES függvényhez:

• Magyarázat és egy FKERES mintapélda
• Fkeres alapok: mire és hogyan használd az FKERES függvényt?
• A leggyakoribb FKERES hibák

Összesítő és kerekítő Excel függvények

12. SZUMHA (SUMIF), 13. SZUMHATÖBB (SUMIFS)

Ugyanúgy összesít, mint a SZUM függvény, de csak akkor, ha az értékek megfelelnek bizonyos feltételeknek - például a 0-nál nagyobb, vagy a 100 és 200 közé eső számokat összesíti.

Több feltétel esetén a SZUMHATÖBB függvényt használd.

= SZUMHA (A1: A7; "> 0")
= SZUMHATÖBB (A1: A7; A1: A7; "> 100"; A1: A7; "<200")

Ugyanígy meg tudod mondani, hogy mennyit költöttek nálad a női vagy a férfi vásárlóid - feltételezve, hogy olyan oszlopod, amiben szerepel a férfi/nő adat. Vagy kiszámíthatod, hogy mennyit költöttek a női vásárlóid egy-egy konkrét termékre.

14. DARABTELI (COUNTIF), 15. DARABHATÖBB (COUNTIFS)

Azokat a tételeket számolja meg, amelyek megfelelnek a kritériumnak. (Több feltétel esetén a DARABHATÖBB függvényt válaszd.)

Például hány 20 évnél fiatalabb ügyfeled van, és hány 20-30 év közötti, ha az A oszlopban szerepel az életkor?

= DARABTELI (A2: A80; "< 20")
= DARABHATÖBB (A2: A80; ">= 20"; A2: A80; "<= 30")

Hány nő van az ügyfeleid között? Ha például a D oszlopban szerepel a férfi/nő adat, akkor:

=DARABTELI(D:D;"nő")

Ha csak a 20 év alatti nőket keresed?

= DARABHATÖBB (A2: A80; "< 20″; D2:D80;"nő")

Ugyanígy megszámolhatod a negatív számokat (például banki tranzakciókat).

16. KEREKÍTÉS (ROUND), 17. KEREK.FEL (ROUNDUP), 18. KEREK.LE (ROUNDDOWN)

Megadott számú számjegyre kerekít egy számot a matematika szabályai alapján. Létezik csak felfelé és csak lefelé kerekítő változata is. (Emlékszel ugye, azokra a matekpéldákra, mikor az volt a kérdés, hogy hány X literes hordóban fér el az Y liter bor. Olyankor például felfelé kell kerekíteni az Y/X litert, különben nem lesz helye a "maradéknak".)

= KEREKÍTÉS (7,86; 1) eredménye 7,9
= KEREK.FEL (7,23; 0) eredménye 8
= KEREK.LE (8,87; 1) eredménye 8,8

Míg a számformátum csak virtuálisan kerekít, addig a függvényekkel ténylegesen levágod a "felesleges" számjegyeket.

19. PADLÓ (FLOOR) és 20. PLAFON (CEILING)

A megadott számot egy másik szám többszörösére kerekíti, a választott függvény szerint le vagy fel.

= PADLÓ (87; 10) eredménye 80
= PLAFON (107,6; 0,25) eredménye 107,75

Szöveges és dátum Excel függvények

A szöveges és dátum függvények az Excel legegyszerűbb és legkönnyebben megtalálható, megtanulható függvényei, mivel gyakran egy-egy az egyben a "probléma" nevét viselik.

A szöveges függvények "hozzáállása", hogy a beírt tartalomról karakterekben "gondolkodik":

21. ÖSSZEFŰZ (CONCATENATE)

Használd a ÖSSZEFŰZ függvényt, hogy két cellában lévő szöveget egymás mellé írj. Akár saját szövegeket is beilleszthetsz az elemek közé.

= ÖSSZEFŰZ (B1; "-"; A1)

A szövegösszefűzésről korábban már írtam egy külön cikket.

22. BAL (LEFT), 23. KÖZÉP (MID), 24. JOBB (RIGHT)

A cellában szereplő szövegeket karakterekre bonthatod, és tetszés szerint vehetsz ki az elejéről, közepéről és a végéről karaktereket. Pl. ha adott A2 cellában XTNK6632-120 cikkszám (lásd fenti kép):

Az első 3 karakter, XTN: =BAL(A2;3)
4-6. karakter, K66: =KÖZÉP(A2;4;3)
Utolsó 6 karakter, 32-120: =JOBB(A2;6)

Az utolsó számjegy mindhárom esetben azt adja meg, hogy hány karaktert eredményez a függvény.

25. AZONOS (EXACT)

Az Excel alapvetően nem tesz különbséget a kis- és nagybetű között. Pl. a szűrésnél, keresésnél, és az összehasonlításnál sem (pl. =A2=B2 vagy =D2="Pécs") A keresésnél kicsit eldugva van lehetőség beállítani, hogy tegyen különbséget.

Az AZONOS függvény azonban figyelembe veszi a kis- nagybetű különbséget, és csak akkor lesz IGAZ az eredménye, ha pontosan megegyezik minden karakter: =AZONOS(B2;C2)

26. DÁTUM (DATE), 27. ÉV (YEAR), 28. HÓNAP (MONTH), 29. NAP (DAY)

A dátumok sok problémát okoznak, mivel igaziból számok, és például 2019.05.15-e volt 43.600! "Normális" (matematikai) módon nem tudod átváltani egyiket a másikra. Viszont az Excel tudja, és a DÁTUM függvény össze tud tenni 3 számból - külön adod meg az évet, hónapot, napot - egy évszámot:

=DÁTUM(2019;5;15)

Vagy fordítva, egy dátumot (43600) szét tud bontani év-hónap-nap elemekre:

=ÉV(B2) vagy =HÓNAP(B2) vagy =NAP(B2)

Sok ügyviteli rendszer szöveges formátumba exportálja a dátumokat. Azt a Villámkitöltés vagy - mivel karakterek - a szöveges függvények (BAL, KÖZÉP) segítségével lehet feldarabolni, és utána a DÁTUM függvénnyel visszaalakítani dátum függvénnyé. Például ha az M oszlopban szerepel a dátum:

=DÁTUM(BAL(M2;4);KÖZÉP(M2;6;2);KÖZÉP(M2;9;2))

30. MA (TODAY)

Mindig a mai nap értékét írja ki (a rendszeridő alapján), azaz minden nap változik az értéke. Segítségével számolhatod, hogy egy bizonyos naptól - pl. születésnap, fizetési határidő - hány nap telt el, vagy hány nap múlva esedékes. Így naponta frissül az érték. Pl:

=MA()-B2

Mennyit ér, ha ismered az Excelt?

7 hasznos Excel trükk, amivel időt nyerhetsz

Diagram fajták az Excelben

Excel függvények, amelyeket muszáj ismerned

Ha azt mondom, Excel függvények, rengeteg függvény jut eszedbe valószínűleg neked is. Bizonyára tudod, hogy több száz Excel függvény áll rendelkezésedre, de vajon melyeket érdemes ismerned, mik azok, amiket nem hagyhatsz ki?

Excel függvények - kategóriák

Logikai függvények

• Szövegfüggvények
• Dátumfüggvények és időfüggvények
• Keresési- és hivatkozási függvények
• Matematikai és trigonometriai függvények
• Statisztikai függvények
• Információs függvények

Excel logikai függvények

HA függvény

Az első logikai függvény a HA függvény (angolul IF függvény), amely megvizsgál egy logikai feltételt, amely, ha IGAZ, akkor az ehhez tartozó értéket adja vissza, ha HAMIS, akkor a hamisnál megadott értéket adja vissza.

Például azt vizsgálom, hogy az eladott db nagyobb-e mint 20, ha igen, akkor írja ki, hogy "OK", ha nem teljesül, akkor írja ki, hogy "Nem Ok". Tegyük fel, hogy a vizsgálandó érték a B2-es cellában van.

=HA(B2>20;"OK";"Nem Ok")

Konkrét példa HA függvényre: HA függvény: 

ÉS függvény, VAGY függvény

Az ÉS függvény (angolul AND függvény) és a VAGY függvény (angolul OR függvény) hasonlóképpen működik. Megvizsgálnak egy vagy több logikai feltételt, és ennek alapján adják vissza az IGAZ vagy HAMIS értékeket. Az ÉS függvény akkor lesz IGAZ, ha minden feltétel IGAZ. Ha csak egy is nem teljesül, akkor már HAMIS lesz a végeredménye. A VAGY függvényben viszont vagy kapcsolat van a feltételek, között, tehát, ha egy feltétel is igaz, akkor az eredmény az lesz, hogy IGAZ.

A példában azt vizsgáljuk, hogy budapesti ÉS 20 évesnél idősebb, majd a VAGY függvényben azt vizsgáljuk, hogy budapesti VAGY 20 évesnél idősebb.

HAHIBA függvény

A HAHIBA függvény (angolul IFERROR függvény) lehetővé teszi, hogy amennyiben hibát ad eredményül a függvény vagy képlet, helyette egy másik szöveget láss. Ez persze nem arra való, hogy a hibásan megírt képleteket eltüntessük, hanem arra az esetre, ha valóban nem található eredmény.

Az alábbi példában százalékos változást vizsgálok a bevételekre, az Üzem2-nél viszont nincs előző évi adat, itt azt fogja kiírni, hogy #ZÉRÓOSZTÓ!. Ehelyett viszont megadjuk a függvényben, hogy azt írja ki: nincs adat.

Excel szövegfüggvények

BAL és JOBB függvény

A BAL függvény (angolul LEFT függvény) egy cellából balról ad vissza megadott számú karaktert. Például, ha az első négy karakterre van szükséged a B2-es cellából:

=BAL(B2;4)

A JOBB függvény (angolul RIGHT függvény) egy cellából jobbról ad vissza megadott számú karaktert. Például, ha az utolsó három karakterre van szükséged a B2-es cellából:

=JOBB(B2;3)

Konkrét példa BAL és JOBB függvényre: Mire jó a BAL és JOBB függvény?

KÖZÉP függvény

A KÖZÉP függvény (angolul MID függvény) a cellában balról jobbra halad, hasonló, mint a BAL függvény, de a BAL függvény csak az első karaktertől tud indulni, a KÖZÉP függvényben megadhatod, hogy hanyadik karaktertől induljon.

Például, ha a B2-es cellában ez szerepel: "HUN-1171 lakossági", és ebből neked az irányítószám szükséges, akkor a függvény:

=KÖZÉP(B2;5;4), ami azt jelenti, hogy az ötödik karaktertől adjon vissza négy karaktert.

Példa KÖZÉP függvényre: Hogyan szedd ki az irányítószámokat a címekből?

KIMETSZ függvény

A KIMETSZ függvény (angolul TRIM függvény) nagyon egyszerű, de annál hasznosabb függvény. Eltávolítja a felesleges szóközöket egy cellából, vagyis a cella elejéről, végéről és a szövegek között, ha egynél több szerepel egymás mellett, azokból egyet hagy meg. Egyetlen argumentuma a cella, amiből szeretnéd a szóközöket kiszedni.

=KIMETSZ(B2)

KIMETSZ függvény feladat: Felesleges szóközök eltávolítsa KIMETSZ függvénnyel

TISZTÍT függvény

A TISZTÍT függvény (angolul CLEAN függvény) a nem nyomtatható karaktereket távolítja el a cellákból. Ezek akkor fordulhatnak elő, ha weboldalról, külső programból másolsz adatokat Excelbe, és benne maradhatnak fura karakterek.

HELYETTE függvény

A HELYETTE függvény (angolul SUBSTITUTE függvény) egy szövegen belül kicserél egy karaktert egy másik karakterre. Meg kell adnod, hogy melyik cellában végzed a helyettesítést, melyik az a karakter, amit helyettesítesz, és mi az az új karakter, amivel helyettesíted.

CSERE függvény

A CSERE függvény (angolul REPLACE függvény) nem megadott karaktereket cserél, hanem a megadott pozícióban lévő karaktereket cseréli. Tehát nem kell megadni konkrét karaktert, mint a HELYETTE függvényben, hanem azt, hogy hanyadik karaktertől, hány karaktert szeretnél cserélni, és mire. A példában a 20at cserélem 70re a cella elején.

KISBETŰ függvény

A KISBETŰ függvény (angolul LOWER függvény) a cellában lévő szöveget végig kisbetűsre alakítja

NAGYBETŰS függvény

A NAGYBETŰS függvény (angolul UPPER függvény) a cellában lévő szöveget végig nagybetűssé alakítja.

TNÉV függvény

A TNÉV függvény (angolul PROPER függvény) a cellában lévő szöveget tulajdonnévvé alakítja, minden szókezdő betű nagy lesz.

SZÖVEG függvény

A SZÖVEG függvény (angolul TEXT függvény) a szöveget megadott formátumú szöveggé alakítja át.

Argumentumaiban meg kell adnod, hogy melyik cellát alakítod át (vagy melyik szöveget), majd a második argumentumban megadod, hogy milyen formátumú legyen a végeredmény.

=SZÖVEG(A2;"000000") - 6 karakter hosszúságú lesz a kód

=SZÖVEG(A2;"# ##0,00 Ft") - ezres csoportosítással, két tizedesjeggyel és Ft formátummal lesz látható a szöveg.

Konkrét példák: SZÖVEG függvény, ahogy a leggyakrabban használjuk

SZÖVEG.KERES függvény és SZÖVEG. TALÁL függvény

SZÖVEG.KERES függvény (angolul SEARCH függvény) és SZÖVEG.TALÁL függvény (angolul FIND függvény) hasonlóképpen működik, egy szövegben megkeres egy szövegdarabot, karaktert. Ha megtalálható benne, akkor egy számot fog adni, ami a szövegben elfoglalt helyét mutatja (pl.: 3, azt jelenti, hogy a harmadik karaktertől indul az adott szövegdarab, itt találta meg).

Két apró különbség van a két függvény között:

1. A SZÖVEG.KERES függvény nem érzékeny a kis-és nagybetűkre, a SZÖVEG.TALÁL igen

2. A SZÖVEG.KERES függvényben lehet használni karakterhelyettesítőket (* ?), a SZÖVEG.TALÁL függvényben nem.

Ezeket a függvényeket ritkán használjuk önmagukban, legtöbbször más függvényekben segítenek (BAL, JOBB, KÖZÉP, HELYETTE, CSERE)

Excel dátum- és időfüggvények

ÉV, HÓNAP és NAP függvény

Az ÉV (YEAR), HÓNAP (MONTH) és NAP (DAY) függvények egy dátumból adják vissza az elnevezésüknek megfelelő részét a dátumnak.

DÁTUM függvény

A DÁTUM (DATE) függvény a különálló év, hónap, nap értékeket alakítja át dátummá. Egymás után szükséges megadni az év, hónap és nap értékeket

ÓRA, PERCEK, MPERC függvény

Hasonlóak az előző három függvényhez, ezek az időt bontják egységeire. ÓRA (HOUR), PERCEK (MIN), MPERC (SECOND) függvények az időből adják vissza a nevüknek megfelelő részeket.

HÉT.NAPJA függvény

HÉT.NAPJA függvény (WEEKDAY) egy dátumból adja vissza számként a hét napját. Az első argumentum maga a dátum, a második argumentumban pedig azt kell megadni, hogy mi legyen a számok kiírásának alapja. Ha azt szeretnénk, hogy hétfő =1, vasárnap = 7 legyen, ahhoz a 2-es számot kell beírni.

MA és MOST függvények

A MA (TODAY) és MOST (NOW) függvényeknek nincs argumentuma. Használd így: =MA(), =MOST(). A MA függvény megadja az aktuális napi dátumot, a MOST függvény pedig az aktuális napot és időpontot.

Ha olvasnál még a témában akkor olvasd el az Excel dátumfüggvények megoldással bejegyzésünket az exceltanfolyam.info oldalunkon.

Excel keresési- és hivatkozási függvények

FKERES függvény

Az FKERES függvény (angolul VLOOKUP függvény) az egyik leggyakrabban használt függvény. Egy adott tartomány első oszlopában megkeres egy értéket, majd ennek sorában lévő adatot ad vissza a kijelölt tartomány megadott oszlopából. Kétféle verziója van, kereshetsz pontos egyezéssel (kódok, nevek esetében), vagy közelítőleges egyezéssel, ha intervallumokban szeretnél keresni. Ezt használhatod az egybeágyazott HA függvények helyett.

Bővebb leírások:

FKERES függvény pontos egyezés esetén
FKERES függvény közelítőleges egyezés esetén

INDEX és HOL.VAN függvények

Az INDEX függvény (angolul INDEX függvény) egy adott tartomány megadott sorának és oszlopának metszéspontjában lévő értéket adja vissza.

A HOL.VAN (MATCH) függvénnyel együtt használjuk a leggyakrabban. Sok esetben hasznosabb, mint az FKERES függvény, több problémára megoldást jelent.

INDEX és HOL.VAN függvények FKERES helyett

Használhatod úgy is, ha nem pontos egyezést keresel: Mérettáblázat - INDEX és HOL.VAN függvények

INDIREKT függvény

Az INDIREKT függvény (INDIRECT) szöveg által meghatározott hivatkozást ad eredményül.

Excel INDIREKT függvény

XKERES függvény

Egyik legújabb függvény az XKERES függvény (XLOOKUP), amely összesíti az FKERES és az INDEX(HOL.VAN) függvények előnyeit. 2019-től elérhető.

Itt olvashatsz róla bővebben: XKERES függvény (XLOOKUP)

Excel matematikai és trigonometriai függvények

KEREKÍTÉS, KEREK.FEL, KEREK.LE függvények

A kerekítés függvények a számadataidat kerekítik, a KEREKÍTÉS függvény mindig a matematikai szabályok alapján vagy felfelé, vagy lefelé kerekít. A KEREK.FEL mindig felfelé, a KEREK.LE pedig mindig lefelé kerekít. Az első argumentum mindegyikben maga a számérték, a második argumentum pedig az, hogy hány tizedesre szeretnéd kerekíteni.

Ha a második argumentumot negatív számként adod meg, akkor a tizedesjegy bal oldalán kerekít, például a -2 esetén százasokra, -3 esetén ezresekre stb.

RÉSZÖSSZEG függvény

A RÉSZÖSSZEG függvény (SUBTOTAL) akkor hasznos számodra, ha vannak elrejtett, szűrt soraid a táblázataidban.

Számolj részösszeget a RÉSZÖSSZEG függvénnyel

SZORZATÖSSZEG függvény

A SZORZATÖSSZEG függvény (SUMPRODUCT) soronként szorozza össze az értékeket, majd összeadja az eredményeket.

SZORZATÖSSZEG függvény használata

SZUM, SZUMHA, SZUMHATÖBB függvények

A SZUM függvény (SUM) szintén gyakori függvény, egy alapfüggvény. A megadott tartományban lévő számokat fogja összeadni számodra.

Excel SZUM függvény leírással példákkal excellence.hu oldalunkon.

A SZUMHA függvény (SUMIF) pedig azokat az értékeket adja össze, amelyek megfelelnek egy adott feltételnek. Pl: csak a budapesti bevételeket

A SZUMHATÖBB függvény (SUMIFS) hasonló, azokat az értékeket adja össze, amelyek megfelelnek egy vagy több feltételnek. Ebben tehát egynél több feltételt is meg tudunk adni. Pl.: budapesti és áprilisi bevételek.

SZUMHA és SZUMHATÖBB függvények

Excel Statisztikai függvények

ÁTLAG, ÁTLAGHA, ÁTLAGHATÖBB függvények

Az ÁTLAG függvény (AVERAGE) szintén alapfüggvény. Argumentumában meg kell adni a tartományt (tartományokat), melyeket átlagolni szeretnél.

Az ÁTLAGHA függvény (AVERAGEIF) átlagolja azokat az értékeket, melyek eleget tesznek egy feltételnek, például a budapesti számokat.

Az ÁTLAGHATÖBB függvény (AVERAGEIFS) szintén átlagolja a számokat, amelyek eleget tesznek egy vagy több feltételnek, itt tehát több feltételt is megadhatsz.

DARAB, DARAB2, DARABÜRES függvények

DARAB függvény (COUNT) megszámolja azokat a cellákat, amelyek számokat tartalmaznak.

A DARAB2 függvény (COUNTA) megszámolja azokat a cellákat, amelyek nem üresek.

DARABÜRES függvény (COUNTBLANK) megszámolja azokat a cellákat, amelyek üresek.

DARABTELI és DARABHATÖBB függvények

DARABTELI függvény (COUNTIF) összeszámolja azokat a cellákat, amelyek eleget tesznek egy feltételnek.

A DARABHATÖBB függvény (COUNTIFS) összeszámolja azokat a cellákat, amelyek eleget tesznek egy vagy több feltételnek.

Excel DARABTELI függvény példákkal

DARABTELI, DARABHATÖBB függvények

KICSI, NAGY függvények

A KICSI függvény (SMALL) egy tartomány k-adik legkisebb elemét adja eredményül.

A NAGY függvény (LARGE) egy tartomány k-adik legnagyobb elemét adja eredményül.

MIN, MAX függvények

Alapfüggvények a MIN (MIN) és MAX (MAX) függvények. Egy tartomány minimális és maximális értékét adják vissza.

Excel információs függvények

SZÁM és SZÖVEG.E függvények

A SZÁM függvény (ISNUMBER) és a SZÖVEG.E függvény (ISTEXT) megvizsgálják, hogy az adott cella tartalma szám típusú-e, vagy szöveg típusú-e.

És függvény

Képlet

Leírás

=ÉS(A2>1;A2<100)

Eredménye akkor IGAZ, ha az A2 cella értéke egynél nagyobb ÉS száznál kisebb. Minden más esetben a HAMIS értéket adja eredményül.

=HA(ÉS(A2<A3;A2<100);A2;"Az érték a tartományon kívül esik")

Az A2 cellában lévő értéket jeleníti meg, ha az kisebb az A3 cella értékénél ÉS száznál. Ellenkező esetben "Az érték a tartományon kívül esik" üzenetet jeleníti meg.

=HA(ÉS(A3>1;A3<100);A3;"Az érték a tartományon kívül esik")

Az A3 cellában lévő értéket jeleníti meg, ha az nagyobb 1-től ÉS kisebb 100-ig, ellenkező esetben üzenetet jelenít meg. Bármilyen üzenetet helyettesíthet.

Bónusz kiszámítása

A következő példa azt szemlélteti, hogy miként számítható ki az értékesítők bónusza a HA és az ÉS függvénnyel.

=$B$7;C14>=$B$5);B14*$B$8;0)"> =$B$7;C14>=$B$5);B14*$B$8;0)">

=HA(ÉS(B14>=$B$7;C14>=$B$5);B14*$B$8;0) - HA az összforgalom nagyobb vagy egyenlő (>=), mint az értékesítési célérték ÉS az ügyfélszerzések száma nagyobb vagy egyenlő (>=), mint az ügyfélszerzési célérték, akkor az összforgalom szorzása a bónusz százalékos értékével, minden más esetben a 0 érték visszaadása.

Szintaxis

Függvény

megszámlálás tárgya

DARAB

számot tartalmazó cellák

DARAB2

tartalommal rendelkező cellák

(nem üres cellák)

DARABÜRES

üres cellák

DARABTELI

feltételnek megfelelő cellák

DARABHATÖBB

több feltétel együttes teljesülésének megfelelő cellák

DARABHATÖBB függvény

DARABHATÖBB(kritériumtartomány1; kritérium1; [kritériumtartomány2; kritérium2]; ...)

A DARABHATÖBB függvény szintaxisa az alábbi argumentumokat foglalja magában:

• kritériumtartomány1: Megadása kötelező. A kapcsolódó kritériumok kiértékelésére használt első tartomány.
• kritérium1 Kötelező megadni. A megszámoljuk a cellákat meghatározó, számként, kifejezésként, cellahivatkozásként vagy szövegként megadott feltételeket. Például a következő formákban adható meg feltétel: 32, "32", ">32", "alma" vagy B4.
• kritériumtartomány2, kritérium2, ...: Megadása nem kötelező. A további tartományok és az azokhoz társított kritérium. Legfeljebb 127 tartomány-kritérium pár engedélyezett.

Fontos: Minden további tartománynak ugyanolyan számú sort és oszlopot kell tartalmazni, mint a criteria_range1 argumentumnak. A tartományoknak nem kell egymás mellett lennie.

Az első sorok vagy oszlopok rögzítése a panelek rögzítésével az Excelben

A felső sor rögzítése: Kattintson a Nézet lap -> Panel rögzítése gombjára.

Legördülő lista létrehozása

https://excelneked.hu/2016/07/11/konnyitsd-meg-az-adatbevitel-legordulo-listaval/

Első lépésként jelöld ki azt a területet, ahol a szabályt szeretnéd érvényesíteni.

Majd az Adatok menü Érvényesítés lehetőségénél kattints az Érvényesítés gombra, vagy a mellette lévő nyílra (vagy magára az Érvényesítés gombra) és válaszd az Érvényesítést.

A felugró ablakban a Megengedve résznél keresd ki a Listát!

A Forrásnál kattints a mezőbe, majd jelöld ki azokat a cellákat, amelyek a listában lesznek.

(Tipp a munkádhoz: A listát áltában nem ilyen látható helyen helyezik el, ezt a példa kedvéért raktam ilyen közel. Javaslom, hogy a forráscellákat helyezd el egy másik munkalapon, vagy a táblázattól egy távolabbi oszlopban és akár el is rejtheted oszlop elrejtésével, vagy cellaformázással)

A Hibajelzés fülön megadhatod azt is, hogy hibát jelezzen és ne engedjen tovább menni, ha eltérő értéket próbál beírni a felhasználó. Itt begépeltem a hibajelzés címét és a hibaüzenetet. Természetesen itt bármilyen üzenetet megadhatsz, ami eligazítja a felhasználót, ha hibás értéket vitt be.

Egy Enter leütése után már láhatod is az eredményt a kijelölt cellákban. A cella jobb oldalán megjelenik egy nyíl, ami jelzi, hogy lista szerepel a cellában, és rákattintva látod is, hogy miből választhatsz.

Legnépszerűbb diagram fajták

1. Oszlopdiagram

Hatásosan szemléltetheted az egyes elemek összehasonlítását, úgy hogy az értékeket oszlopokkal ábrázolod.

Mikor használj oszlopdiagramot?

A legelterjedtebb és legszélesebb körben használható típus, gyakorlatilag bármikor használhatod.

• Termékek, évek összehasonlításánál
• Sorrendbe nem állított neveknél (például földrajzi nevek vagy személynevek)
• Különböző skálák eredményének ábrázolásánál

Több alaptípusból választhatsz forma és elhelyezés szerint is:

• Csoportosított elrendezésnél egymás mellé kerülnek az oszlopok. Ilyenkor az egyes tételeket tudod összehasonlítani. Bármelyik oszlopot tudod viszonyítani a többihez.
• Halmozott oszlopdiagramnál egymás tetejére kerülnek a téglalapok. Ebben az esetben a részösszegeket fogod tudni összehasonlítani, de azért nagyjából le tudod olvasni az egyes elemek nagyságát is.
• 100%-ig halmozott diagram a megoszlás (arányok) változását szemlélteti, azaz a konkrét mennyiséget nem látod. Több kördiagramot nehéz összehasonlítani, ilyenkor hasznos ez a típus.

Bár a beépített sablonok között több látványos diagram típust is találsz, érdemes az egyszerűbb, kétdimenziós oszlopdiagramokat választani. Ábrázolásban is igaz, hogy a kevesebb több.

Tipp: Ha két adatsort hasonlítasz össze (pl. terv-tény), mindig a csoportosított (egymás mellé helyezett) típusokból válassz!

Itt bővebben is írtam arról, hogy mi a különbség a csoportosított és halmozott oszlopdiagram között.

2. Vonaldiagram

Az adatokat pontokkal jelöli és egy vonallal köti össze. Leginkább az adatok időbeli változásának és a trendek ábrázolására alkalmas.

Mikor használj vonaldiagramot?

• Időbeliség, trendek szemléltetésénél, például napok, hónapok, negyedévek vagy pénzügyi évek összehasonlításánál, terv-tény kontrollnál, részvényárfolyamok alakulásánál.
• Amikor 2 nagyon eltérő adatot szemléltetsz (akár különböző skálázással), akkor eltérő diagram fajták segítségével is meg szokták különböztetni az adatokat, jellemzően oszlop- és vonaldiagrammal.

Az időegységek a vízszintes, az értékek pedig a függőleges tengely mentén jelennek meg.

Vonaldiagramnál is választhatod a halmozott és 100%-ig halmozott elrendezést.

Tipp: Ha nem tetszenek a szögletes vonalak, görbítsd meg!

Ha menet közben meggondoltad magad, utólag is tudsz módosítani a diagram típusán. Kattints a linkre, ha kíváncsi vagy a módjára.

3. Tortadiagram vagy kördiagram

Az adatok százalékos megoszlását jelenítheted meg. A diagram a tételek arányát a teljes összeghez viszonyítva kiszámolja, és ennek megfelelően ábrázolja.

Mikor használj tortadiagramot?

• Ha csak egyetlen adatsort szeretnél ábrázolni
• Fontos a tételek egymáshoz viszonyított aránya, az egyes kategóriák a teljes torta részeit jelképezik
• Az értékek között nincs negatív szám és nulla, és csak 5-6 adatod van.

Tipp: Írd ki a pontos %-ot az ábrára!

Speciális diagramok

4. Sávdiagram

A sávdiagram tulajdonképpen az oszlopdiagram függőleges változata, ugyanúgy tételek összehasonlítására szolgál.

A kategóriák függőlegesen, az értékek pedig vízszintesen jelennek meg.

Mikor használd?

• Ha a tengelyek felirata hosszú
• Például országok összehasonlításánál, korfánál, projekt-menedzsmentben az ütemezés szemléltetésére

5. Területdiagramok

A területdiagram ugyanaz, mint a vonal, csak a vonal alatti teljes terület ki van töltve.

Mikor használd?

• Amikor a vonaldiagramot is használhatnád
• De csak akkor, amikor a vonalak nem keresztezik (nagyon) egymást

A vonalhoz hasonlóan itt is létezik halmozott és 100%-os változat.

6. Pontdiagram (XY)

A pontdiagram több adatsor számértékei közötti viszonyt ábrázol, úgy, hogy az egyik számsort a vízszintes tengelyen (x) és a másik számsort a függőleges tengelyen (y) ábrázolja.

A diagramon ennek a 2 értéknek a metszete fogja meghatározni a pontok helyét, amelyek szabály-talan közönként, illetve csoportokban jelennek meg.

A tendenciát, összefüggést a pontok sűrűsödése fogja jelezni.

Mikor használd?

• tudományos, statisztikai vagy műszaki adatok, mérések ábrázolására és összehasonlítására. Például van-e összefüggés a csapadékmennyiség és a szennyezettség között, az árak és az eladott darabszámok között.
• sok adatpontot szeretnél összehasonlítani az időtől függetlenül: nem a mérés időpontja számít, hanem maga az eredmény
• a vízszintes tengely értékei nem egyenletesen helyezkednek el

7. Buborékdiagram

A pontdiagram 3 dimenziós változata, ahol a pont vagy buborék méretével egy harmadik tulajdonságát is szemléltetheted.

A buborék méretének a piaci részesedést, potenciált vagy egyéb hatékonysági mutatót válassz.

Így könnyen eldöntheted, hogy mely szegmenssel érdemes a jövőben foglalkoznod.

8. Sugárdiagram

Sugárdiagrammal több adatsor értékeit hasonlítod össze, jellemzően olyan szempontok szerint, amelyeket nehéz egymáshoz viszonyítani.

Jellemzően nem számokat, hanem különböző tulajdonságokat hasonlítasz össze.

Végül a vonalak által behatárolt terület segít a döntésben.

Mikor használd?

• Összehasonlításoknál, pl. hogyan változott fogyasztók preferenciája 5 különböző szempont szerint (sugarak) egyik évről a másikra (színek)
• Döntési helyzetben, ha több szempont szerint értékeled az egyes opciókat, például projektek vagy személyek között kell döntened

Sortörés beszúrása

A cella bármely pontján új sort kezdhet a szövegben. Ehhez a következőket kell tennie:

Kattintson duplán arra a cellára, amelybe sortörést szeretne beszúrni.

Tipp: A cellát ki is jelölheti, majd lenyomhatja az F2 billentyűt.

Kattintson a cellában arra a helyre, ahol meg szeretné szakítani a vonalat, és nyomja le az Alt+ Enter billentyűkombinációt.