Telefonszámunk: 30-905-2076

Függvények

Adott érték n-dik előfordulásának keresése egy oszlopban

On 2013-04-17   /     /   1 Comment

Találkoztam néhányszor azzal az igénnyel, hogy egy tartományban egy adott értéknek ne csak az első, hanem a második, harmadik, n-dik előfordulást is meg lehessen keresni.

Az Excelnek erre nincs külön fügvénye, és a VLOOKUP (FKERES), illetve a MATCH (HOL.VAN) függvények csak az első előfordulást tudják megkeresni.

Vannak az Interneten segédoszlopot használó, megkerülő megoldások, de egyszer csak ráakadtam egy régebbi bejegyzésre, aminek a tanulmányozása után arra jutottam, hogy ez lesz a helyes megoldás.

Az eredeti cikk egy ősrégi Microsoft tudásbázis bejegyzés, Excel 4.0 és Excel 97 verziókra vonatkoztatva.

Nézzük a megoldást

Az alábbi ábrán látjuk a mintaként használt táblázatot. Tehát a feladat az lenne, hogy az „alma” szó második vagy harmadik előfordulását is meg tudjuk találni, és esetleg kivenni mellőle a hozzá tartozó értéket ugyanabból a sorból.

n-dik-elofordulas-alaptabla

Az ötlet azon alapszik, hogy ha tudnánk képezni egy listát az „alma” szót tartalmazó sorokról (1;4;6), akkor ezek közül a SMALL (KICSI) függvénnyel kiválaszthatnánk az n-dik legkisebbet, ami megadná az n-dik előfordulás munkalap-sorszámát. Jelen példában, ha az {1;4;6} tömb 2-dik legkisebb elemét keresem, akkor egy cellába beírhatom a következő képletet:

=SMALL({1;4;6};2)

Eredményként a 4-et fogom kapni.

Hogy lehet az „almás” sorok tömbjét előállítani?

Természetesen tömbképlettel. Végignézzük az „A1:A6” tartományt, és ha bármelyik eleme egyenlő az  „alma” szóval, akkor kivesszük az aktuális sor számát, különben egy üres sztringet.

Jelöljünk ki 6 egymás alatti cellát (mert 6 elemből áll a példa táblázat), írjuk be a következő képletet, és a végén nyomjuk meg a Ctrl+Shift+ENTER kombinációt!

=IF((A1:A6)<>"alma";"";ROW(A1:A6))

A cellákban a képlet kapcsos zárójelek közé került: {=IF((A1:A6)<>”alma”;””;ROW(A1:A6))} , és a kijelölt cellákban ez lett az eredmény:

sorok-tombje

Tehát megkaptuk a 1;4;6 listát, igaz, hogy közben vannak üres cellák is, de az nem baj. Már csak az van hátra, hogy ebből a listából kiválasszuk a SMALL (KICSI) függvénnyel mondjuk a második legkisebbet, azaz az „alma” szó második előfordulásának  munkalap-sorszámát (4)

=SMALL(IF((A1:A6)<>"alma";"";ROW(A1:A6));2)

A végén látható, hogy a második legkisebbet keressük. Ne felejtsük el ezt is a Ctrl+Shift+Enter-el lezárni.

Amennyiben a táblázat a munkalap első sorában kezdődött, akkor a megtalált munkalap sorszám egyenlő lesz az adott tartományban elfoglalt sorszámmal.

És a mellette levő érték?

Ha valójában az n-dik előfordulás melletti értéket keressük, akkor a sorszám ismeretében az INDEX függvénnyel célt érünk, azaz az egészet beágyazhatjuk egy INDEX függvénybe, ami a „B” oszlopból kiveszi a megkapott sorszámú elemet. Ezt a képletet is természetesen a tömbképleteket megillető Ctrl+Shift+Enter-el kell lezárni.

=INDEX(B1:B6;SMALL(IF((A1:A7)<>"alma";"";ROW(A1:A7));2))

n-dik-elofordulas-eredmeny

És mi van akkor, ha a táblázat nem az első sorban kezdődik?

Akkor egy kicsit matekozni kell a Sorokkal, de azért megoldható. Ha kíváncsi vagy rá, nézd meg a Videó anyagot is!

Ertek-n-dik-elofordulasa

  • Érték n-dik előfordulása

Tovább... →
Back to Top

Intervallum tömbök

On 2013-04-13   /     /   Leave a comment

Ebben az anyagban leírtak megértéséhez hasznos lehet előbb ezt megnézni:

excel-tombok-tombkonstansok

Hogyan válaszolnánk meg Excel segítségével a következő kérdéseket:

  • Két egész szám között hány 3-al osztható szám van?
  • Két dátum között hány hétfő van?
  • Két dátum között hány dátum esik hétvégére

Megoldások

Első

Nézzük az első feladatot. Hagyományos módon ezt úgy csinálnánk, hogy képezzük egy oszlopba a növekvő értékeket, és mellette egy segédoszlopban logikai értékként megadnánk az oszthatóságot, majd feltételes összegzéssel megkapjuk az eredményt. Az ábrán látható, hogy a „B” oszlopban a hárommal való oszthatóságot vizsgáljuk.

oszthatosag-logikai-vizsgalatat

Ezután nincs több dolgunk, mint egy eredménycellába megszámolni a „B” oszlopban lévő TRUE értékek számát.

Mondjuk a „D2”-be írjuk be a képletet:

=COUNTIF(B2:B11;TRUE)

Második

A megoldást egyetlen képlettel is megadhatjuk, ami az egész tartományban vizsgálja az oszthatóságot, a visszakapott TRUE/FALSE értékeket 1-re és 0-ra alakítja, és összeadja az eredményt. Értelemszerűen csak ott lesz 1-es, ahol az érték osztható, és az 1-esek összegzése megadja, hogy hány szám van a tartományban, ami osztható 3-al.

=SUMPRODUCT(--(MOD(A2:A11;3)=0))

Tól-Ig tömbök képzése

Próbáljuk ki a következőket:

Egy cellába írjuk be a következő képletet, de mielőtt lezárnánk, nyomjuk meg az F9 billentyűt :

=SOR(INDIREKT("34:55"))

angol verzióban

=ROW(INDIRECT("34:55"))

A tartalom átváltozik erre

={34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55}

Tehát ez az összefüggés létrehozza a memóriában a kezdő és végérték közötti számok növekvő halmazát. (Ctrl+Z-vel vagy ESC-el lehet visszatérni.)

Ha a kezdő és a végérték nem konstans, akkor felvehetjük őket egy-egy cellába, és a képlet így alakul

=SOR(INDIREKT(A1 &":" & A2))

Látható, hogy az Indirekt függvénynek egy szövegre (stringre) van szüksége, ezt összefűzéssel állítjuk elő, belecsempészve a kettőspontot, mint a kezdő és a végérték elválasztójelét.

Ugyanez dátumokkal

A dátumokat ugyebár az Excel sorszámként kezeli. Egy egy szomszédos nap között pont egy egész különbség van. Két dátum közötti tömböt az előzőekhez hasonlóan képezhetjük, azaz a kezdő és végdátumot beírjuk két cellába, és az előző képletet beírva, majd F9-et nyomva ezt láthatjuk:

datumszamok-tombje

Tehát a 2013 január 1 és 5 közötti dátumszámok növekvő értékeit.

Mire jó ez az egész?

Ha visszatérünk a fenti feladatokhoz, akkor abban az esetben, ha nem akarjuk vagy nem lehetséges képezni a tömb elemeit külön a munkalapon, akkor ezt a megoldást alkalmazhatjuk a képletekben.

Hány hárommal osztható szám van két szám között (A1 és A2):

=SUMPRODUCT(--(MOD(ROW(INDIRECT(A1&":"&A2));3)=0))

Két dátum között hány hétfő van?

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($A$1 & ":" & $A$2));2)=1))

A WEEKDAY függvény magyar verzióban a HÉT.NAPJA. Azt mondja meg, hogy egy dátum a hét hányadik napjára esik. A képlet végén 1-el hasonlítjuk össze, mert a Hétfő a hét első napja.

Két dátum között hány dátum esik hétvégére

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT($A$18&":"&$A$2));2)=6)+(WEEKDAY(ROW(INDIRECT($A$1&":"&$A$2));2)=7))

A hatodik nap a szombat, a hetedik nap pedig a vasárnap. A kettő közötti plusz jel a logikai VAGY műveletet jelenti.

Letölthető munkafüzet, Video

Ertekek-kozotti-szamolas-row-indirect

  • Növekvő számhalmazok

Tovább... →
Back to Top

Cellához képesti reláció a feltételes összesítésekben

On 2013-02-08   /    

Az Excel 2003-ig még csak a SUMIF/COUNTIF (SZUMHA/DARABTELI) függvények léteztek a feltétel szerinti összesítésre. Az első feltétel szerinti összegzést, a második feltétel szerinti megszámlálást végez.

Aztán az Excel 2007-ben bevezettek jó néhány új függvényt, így a hasonló funkciójú függvények sora így néz ki:

Funkció leírása Angol név Magyar név
Egy feltétel szerinti összegzés SUMIF SZUMHA
Több feltétel szerinti összegzés SUMIFS SZUMHATÖBB
Egy feltétel szerinti megszámlálás COUNTIF DARABTELI
Több feltétel szerinti megszámlálás COUNTIFS DARABHATÖBB
Egy feltétel szerinti átlagolás AVERAGEIF ÁTLAGHA
Több feltétel szerinti átlagolás AVERAGEIFS ÁTLAGHATÖBB

Ennek a bejegyzésnek nem az  acélja, hogy ismertesse ezeket a függvényeket, hiszen meglehetősen egyszerűek, az Excel súgója is remek leírást és példákat ad.

De van egy olyan eset a feltétel megfogalmazásánál, ami nem teljesen egyértelmű.

Feladat

Számoljuk meg az összes olyan cellát az A1:A100 tartományban, ahol a cellák értéke kisebb, mint a  D1-ben lévő érték.

Ezt elsőre így próbálnánk megoldani:

=COUNTIF(A1:A100;”<D1″) – Az eredmény valószínűleg nulla lesz, akkor is, ha vannak a D1-nél kisebbek a tartományban

Másodikra megpróbálnánk így:

=COUNTIF(A1:A100;<D1)- Erre pedig kapunk egy hibaüzenetet

Helyes megoldás

Az összes fenti függvény esetén, ha a feltételben használnánk egy cella tartalmát, és ehhez relációval akarunk viszonyítani (kisebb, kisebb vagy egyenlő, stb…), akkor a relációs jelet össze kell fűznünk a cella címével.

=COUNTIF(A1:A100;”<„&D1)

Tehát a kívánt relációs műveleti jelet idézőjelbe tesszük, majd összefűzzük a kívánt cella címével.

Tovább... →
Back to Top

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