Telefonszámunk: 1-472-0679

Függvények

FKERES (VLOOKUP) helyett van jobb!?

On 2013-02-03   /     /   2 hozzászólás

Két táblázat összekapcsolását tapasztalatom szerint a legtöbb esetben a VLOOKUP (FKERES) függvénnyel oldják meg. A tipikus helyzet az, hogy egy cikk, termék, számla, személy, stb… adatait szeretnénk kikeresni egy törzs táblázatból.

Egy másik táblázatban rendelkezésre áll a keresendő valami azonosító adata, ezt keressük a törzs táblázatban, és ha megtaláljuk benne, akkor ugyanabból a sorból egy másik tulajdonságát (nevét, egységárát, stb..) kérjük vissza.

Az FKERES (VLOOKUP) működése pontos egyezőségi keresés esetén

Az FKERES működése

Az FKERES (VLOOKUP) problémái

  • Csak a keresési tábla első oszlopában tud keresni, és csak tőle jobbra lévő oszlopból tudunk kérni információt
  • Érzékeny a keresési táblázat szerkezetének (oszlopsorrendjének) változására
  • Ha több oszlopból is kérünk vissza adatot, akkor mindannyiszor kerestetni kell a kulcsot
  • Előbbi miatt nagy táblázatoknál lassulást eredményezhet

MATCH+INDEX (HOL.VAN+INDEX) a király!

Az előző megoldás helyett sokkal rugalmasabbnak tűnik egy másik megoldás, ami ugyanarra a feladatra hesználható, és a fenti hiányosságokra megoldást nyújt. Konyhanyelven  a következőről van szó:

  1. Először megkeresem az azonosítót a keresési táblázat vonatkozó oszlopában, és megtudom, hogy hányadik helyen van benne. Erre való a MATCH (HOL.VAN) függvény.
  2. Majd ez alapján a szükséges oszlopból kiveszem az annyiadik elemet, amit az előbb megkaptam. Ezt pedig az INDEX függvény teszi meg.

Tehát két függvényhívás. Lehetnek két külön oszlopban, de össze is lehet őket fűzni egy képletbe.

Letölthető példa munkafüzet

További érthetetlen ábrák és magyarázkodás helyett nézzük akció közben!

  • Fkeres helyett Hol.van+Index

Tovább... →
Back to Top

Remek függvény a hatékony hibakezeléshez – IFERROR -HAHIBA

On 2013-01-23   /     /   Leave a comment

Előfordulhat, hogy a képletek által visszaadott hibajelzéseket érzékelni szeretnénk, és a hibát eredményező képlet cellájába nem a hibakódot akarjuk látni, hanem valami általunk definiált számot vagy szöveget.

Leggyakrabban a Vlookup (Fkeres) vagy a Match (Hol.van) függvényeknél találkozunk azzal, hogy ha nem találja a keresendő adatot pontos egyezőségre, akkor a #N/A (#NINCS) hibakódot adja vissza. Ezen kívül lehetnek még egyéb hibakódok nullával történő osztásnál, hibás névhasználatnál, stb…

Az Excel 2007 előtti időkben ennek a kezelésére egy feltételvizsgálatot kellett csinálni, amit kétféleképpen oldhattunk meg.

ISERROR (HIBÁS) függvény használata első módszer

A vizsgálatot egy külön segédoszlopban tettünk meg, és utána a következő oszlopban azt vizsgáltuk, hogy hibakódot adott-e.

ISERROR (HIBÁS) függvény használata második módszer

Segédoszlop nélkül, magában a képletben először ellenőriztük, hogy hibát kapunk-e, és ha nem, akkor lefuttattuk a függvényt, különben beírtuk a saját üzenetet. Ez utóbbi megoldás nagy hátránya, hogy ha nincs hiba, akkor a függvényt kétszer hajtatjuk végre az Excel-lel.

IFERROR (HAHIBA) függvény

Az Excel 2007-ben bevezettek egy új függvényt, ami az előző két módszert összevonja, és kiküszöböli a hatékonysági problémát. A függvény első argumentuma a kifejezés, amit vizsgálunk, és ha nem okoz hibát, akkor a kifejezés által visszaadott eredményt írja a cellába, ha viszont hibát adna, akkor a második argumentumban beállított egyedi értéket, a mi lehet szám, vagy szöveg.

Figyelem! Ha valaki a függvény beszúrás varázslóval szeretné használni, akkor érdemes tudnia, hogy a függvény a logikai függvénykategóriába lett besorolva!

Tovább... →
Back to Top

Számított feltétel szerinti megszámlálás egy tartományban

On 2013-01-04   /     /   Leave a comment

A feltétel szerinti megszámlálásra az Excel kínál két függvényt:

  • Darabteli (Countif)
  • Darabhatöbb (Countifs)

Ezekkel a függvényekkel az a probléma, hogy a megszámlálás feltételeként nem lehet képletet alkalmazni. De mit tegyünk, ha például meg kellene számolnunk  a páros számokat egy tartományban? A páros értékek megszámolásához képlet kell, amivel el tudjuk dönteni egy értékről, hogy páros-e. Erre több megoldás is van, de a legjobb a MARADÉK (MOD) függvény.

De hogy a feltételhez használt képletet hogyan építhetjük bele a megszámlálásba, az már nem olyan egyszerű. A példa alapján egyéb, hasonló probléma is megoldható, pl. az, hogy hány olyan sor van, ahol az egyik oszlopban kisebb vagy nagyobb érték van, mint egy másikban.

Számított feltétel szerinti megszámlálás – VIDEO

http://youtu.be/jZYb3KKV0Zc

Tovább... →
Back to Top

Logikai értékek változtatása 1-re vagy 0-ra

On 2013-01-04   /     /   Leave a comment

Az Excelben a logikai kifejezések és függvények eredménye alapértelmezésként „IGAZ” vagy „HAMIS” („TRUE”/”FALSE”).

Előfordulhat, hogy ezeket a megfelelő számra, azaz 1-re vagy 0-ra kell konvertálni, például azért, hogy megszámolhassuk őket. Az átalakítással sima összeadássá egyszerűsíthetjük a dolgot.

Az átalakításra leggyakrabban a dupla mínuszjelet használják, de tulajdonképpen bármilyen matematikai művelet megteszi, ami az eredeti értéket nem változtatja meg.

Legegyszerűbben úgy próbálhatjuk ki, hogy egy cellába beírjuk  a következőt:

Magyar: =- -IGAZ

Angol: =- -TRUE

Önmagában ennek persze semmi értelme, de képletekben alkalmazva már jól használható.

Néhány lehetőség az átalakításra (A1-ben van a logikai eredmény)

=–(A1)
=(A1)*1
=(A1)/1
=(A1)+0
=(A1)^1
=ABS(A1)

Például szeretnénk megtudni, hogy egy oszlopban (A1:A10) hány páros szám van.

  1. Egy segédoszlopban (B oszlop) vizsgáljuk  a párosságot
    =MARADÉK(A1;2)=0 (IGAZ/HAMIS eredményeket ad)
  2. Módosítjuk a dupla mínuszjellel: =–(MARADÉK(A1;2)=0) -> (0/1 eredményeket ad, a zárójel fontos!)
  3. Összeadjuk a segédoszlopot

Az egészet egy db speciális képlettel is meg lehet csinálni, de azt csak a VIDEO-ban lehet megnézni.

  • Páros számok megszámlálása egy tartományban

Tovább... →
Back to Top

Pontos sortörés a cellában – akár képlettel is

On 2012-12-02   /     /   3 hozzászólás

Az Excel mindegyik verziójában bekapcsolhatjuk a szöveg tördelését a cellaformázás párbeszéd ablakban, vagy az újabb verziókban a Kezdőlap szalagon a megfelelő nyomógombbal:

„Sortöréssel több sorba” vagy angolul „Wrap Text”

Ezzel a módszerrel az a probléma, hogy a sortöréseket automatikusan helyezi el, és mi nem tudjuk szabályozni. Pl.: Ha növeljük az oszlop szélességet, akkor a töréspontok elmozdulhatnak.

Barkácsmódszerrel úgy lehet a dolgot szabályozni, hogy a kívánt töréspontnál szóközöket teszünk a cellába, hogy ezzel kényszerítsük ki a sortörést.

Az igazi megoldás

A töréspont helyén a bal oldali ALT+ENTER kombinációt alkalmazzuk. Az így elhelyezett sortörés az oszlop szélességétől függetlenül mindig jó lesz.

Sortörés összefűzött szövegnél

Abban az esetben, ha a cella tartalmát több szöveg összefűzésével kapjuk, akkor is szabályozhatjuk a sortörés pozícióját, a következő módszerrel:

  1. A sortörés helyére befűzzük a KARAKTER(10) függvényt. Angol verzióban CHAR(10).
  2. Beállítjuk a „Sortörésseltöbb sorba” tulajdonságot a cellán.

Példa

Tovább... →
Back to Top

Szóközök vagy más karakterek megszámolása egy cellában

On 2012-12-02   /     /   Leave a comment

Ha meg szeretnénk számolni egy adott karakter előfordulásainak számát egy cellában, akkor a következő képletet használjuk.

A példa a szóközöket számolja, feltételezve, hogy az ‘A1’ cellában lévő szövegben számolunk.

Angol

=LEN(A1)-LEN(SUBSTITUTE(A1;” „;””))

Magyar

=HOSSZ(A1)-HOSSZ(HELYETTE(A1;” „;””))

Úgy működik, hogy az eredeti szöveg hosszából kivonja a szóközöktől mentesített szöveg hosszát. A szóköz mentesítés úgy történik, hogy lecseréli az összes szóközt az üres karakterre.

Ha előfordulhat az, hogy az eredeti szöveg elején vagy végén plusz szóközök vannak, és nem szeretnénk, hogy ez elrontsa az eredményt, akkor az eredeti cellát egy TRIM függvénybe érdemes ágyazni.

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1;” „;””))

Ha nem szóközöket számolunk, akkor csak annyi a dolgunk, hogy a helyettesítő függvény második argumentumában kicseréljük a karaktert arra, amit számolni szeretnénk.

Amennyiben a keresett karakter egyszer sincs a cellában, a képlet nulla értéket ad.

Tovább... →
Back to Top
« Előző oldal