Joker Excel megoldás, amelynek logikája sok hasonló problémára ráhúzható

Az ilyen, egyszerűen kilogikázott eredmények segíthetik gondolkodásunkat a megfelelő irányba állítani a napi munkában

2015. augusztus 25. - Office Guru

Egy nap szünet volt ugyan most a posztolásban, de ez az egy nap sem múlt el Office nélkül, legalábbis számomra, ráadásul a kedves Olvasók egy-két izgalmasabb feladvánnyal is megörvendeztettek levélben (amiért nagy köszönet azoknak, akik klaviatúrát ragadtak egy üzenet erejéig - csak így tovább), úgyhogy most következzen egy érdekesebb Excel-történet megint, egy egyszerűbb és egy kicsit továbbgondolt verzióban.

Nincs nagyon mit kerülgetni a forró kását, a kérdés ugyanis egyértelmű és a megoldás is egyszerűnek nevezhető, így azoknak is kedvezhetek ezzel, akik még csak most barátkoznak a beágyazásokkal és függvények egymáshoz kapcsolásával, ráadásul ez egy logikailag igen jól lekövethető megoldás (józan paraszti ésszel is kitalálható), amelyek amúgy is nagyon közel állnak a szívemhez.

Szóval ha egy cellában szereplő szavak számát szeretnénk megadni, hogy tudjuk ezt megtenni (lásd a példán)?

tizenhatodik.jpgA megoldáshoz nincs szükség ismeretlen függvények megtanulására, tömbökre, egyszerűen csak a már itt is publikált néhány függvény felhasználásával össze tudjuk hozni az eredményt.

Célszerű ilyen jellegű feladatoknál a TRIM függvénnyel letisztítani szövegünket a felesleges szóközöktől, amelyek később problémát okozhatnak, jelen esetben mindig TRIM(D4)-ként hivatkozzunk majd cellánkra.

Elsőként tehát számoljuk meg, hogy felesleges szóközök nélkül hány karakterből áll szövegünk: LEN(TRIM(D4))

Az így kapott számból azt kellene majd kivonnunk, hogy a már nem felesleges szóközök nélkül hány karakterből áll szövegünk, ezt a karakterszámot pedig az igencsak hasznos és ajánlott SUBSTITUTE függvénnyel tudjuk meghatározni, méghozzá úgy, mint ahogy az egyik korábbi posztban felhasználtuk, azaz lecseréljük a szóközöket semmire, majd az így kapott szöveg hosszát határozzuk meg: LEN(SUBSTITUE(TRIM(D4)," ",""))

Ha ezt a két részletet egy kivonásjellel összefűzzük (hiszen kivonjuk egyik számot a másikból), akkor kapunk is egy szép számot, de csodálkozhatunk, hiszen eggyel kevesebb, mint szavaink száma, viszont ez logikus is, mindig is eggyel kevesebb szóközünk van, mint ahány szavunk bármilyen szöveg esetén - ergó ha hozzáadunk egyet az összefűzött képletünkhöz, akkor már teljes is az eredmény:

LEN(TRIM(D4))-LEN(SUBSTITUTE(TRIM(D4)," ",""))+1

tizenotodik.jpgTudom, hogy alapvetően most sokan rálegyintenek erre a képletre, mert hát egyszerű történetről van szó, de én azért szeretnék azokhoz is szólni kicsit, akik szeretnének elmélyülni az ilyen jellegű megoldásokban, de jelenleg még csak barátkoznak a hasonló problémák megfejtésével.

Viszont bonyolítsuk kicsit a történetet tovább azzal, hogy mi a helyzet akkor, ha mondjuk nem szóköz választja el szavainkat, hanem pontosvessző és szóköz, lásd a példán:

tizennyolcadik.jpgEkkor már nincs túl sok dolgunk ezzel, egyszerűen csak be kell ágyaznunk az alap SUBSTITUTE függvényünkbe egy másik SUBSTITUTE függvényt, azaz ebben az esetben a SUBSTITUTE első paramétereként megadandó TEXT nem a TRIM(D4) lesz egyszerűen, hanem inkább a SUBSTITUTE(TRIM(D4),";"," "), hiszen ezzel a pontosvesszőnket is szóközre cseréljük, amit aztán a külső SUBSTITUTE függvénnyel a fentebb már leírt módon eliminálni fogunk. Így ez a megoldásunk, ahogy a képen is látszik:

tizenhetedik.jpgExcelben jártasak persze könnyen találhatnak más megoldást is erre, ha valaki szeretné megosztani a sajátját, szívesen látom kommentben.

A bejegyzés trackback címe:

https://officeguru.blog.hu/api/trackback/id/tr197734464

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.

A kulcslyuk szelleme 2015.08.26. 10:14:45

Köszi az újabb posztot.:-)
Megjegyzés1: Magyar office-ban LEN helyett HOSSZ, SUBSTITUTE helyett HELYETTE használható.)
Megjegyzés2: Üres cellára fals eredményt ad, célszerű egy ellenőrzést is beiktatni a képletbe.

A kulcslyuk szelleme 2015.08.26. 10:40:52

Megjegyzés3: arra is figyelni kell, ha sortörés van a vizsgált cellában.

Atzs · https://adatrendezo.hu 2015.08.26. 23:52:39

A TRIM pedig magyarul KIMETSZ. Eltávolítja a szöveg elejéről és végéről a szóközöket, valamint a szöveg belsejében a kétszeres, háromszoros... szóközöket egyetlen szóközre cseréli.

A kulcslyuk szelleme 2015.08.28. 10:04:24

@Atzs: nekem magyar Office2007 van, abban csak TRIM van, és nincs KIMETSZ. Érdekes...

Atzs · https://adatrendezo.hu 2015.08.28. 10:34:13

@A kulcslyuk szelleme: Én is TRIM-re emlékeztem. Ezek szerint az O2013-ban magyarítottak függvényeket. Eddig csak a RANDBETWEEN - VÉLETLEN.KÖZÖTT tűnt fel.

Önmagában az jó, hogy magyar felhasználók magyar függvényneveket kapnak, csak a magyar fordítás minőségéről és következetességéről ne ejtsünk szót, lást COUNTIF, SUMIF - DARABTELI, SZUMHA páros. Vagy a KITEVŐ függvény.

Tanulság: nem elég arra figyelni, hogy régebbi verziókban a függvények egy része még nem létezett, azt is nézni kell, hogy melyik verzióban hogy hívták őket.