Számok, számjegyek, szummák - avagy hogy tűnjünk el táblázatkezelőnkben elmélkedni

Bevezetés a tömbképletek világába - II. rész

2015. szeptember 03. - Office Guru

Érkezett az elmúlt napokban néhány érdekes probléma a bloghoz kreált e-mail fiókomba és adós vagyok az első feladvány megfejtésével és magyarázatával is, de a mai posztban inkább továbbmennék a tömbképletekkel, hiszen most még friss az élmény az első, bevezető poszt után. Miután az alapokkal már tisztában vagyunk és egy relatíve összetettebb tömbképlettel zártuk az előző posztot, most egy olyan problémával és megoldásával fogunk foglalkozni, amelyet jópár évvel ezelőtt Exceles munkahelyi felvételi tesztekben és szintmérőkben is láthattunk elég sokat és azt hiszem, tökéletes lesz több, igen hasznos függvény megismerésére és a tömbképletek igazi lehetőségeinek feltárására.

Szóval a kérdés roppant egyszerű: van egy számunk egy cellában, mondjuk meg róla, mennyi számjegyeinek összege - lásd a példán.

negyvenhet.jpgAhogy a legtöbb ilyen problémánál, a kulcs ismét a logika megtalálása, hiszen utána azt képletekbe varázsolni már nem túlságosan nagy kunszt, jelen esetben pedig ez a logika igazából egy nagyon fontos Excel-függvényen alapul, ami nem más, mint az OFFSET. Ez a formula egy, az általunk paraméterként megadott magasságú, szélességű hivatkozást fog visszaadni nekünk egy cellától vagy cellatartománytól meghatározott sornyi és oszlopnyi távolságra. Ez most talán töményre sikeredett, de lássuk inkább egy példán:

=OFFSET(A1,2,1)

negyvenhat.jpgEz az egyszerű kis OFFSET az A1-es cellától fog majd két sort lefelé haladni, majd utána jobbra egy oszlopot (használhatnánk negatív számokat is persze a másik irányhoz) és kiírja az ott lévő értéket. Ez hogy fog jól jönni majd a megoldásunkhoz? Nos, a célunk az, hogy szerezzünk egy olyan tartományra való hivatkozást, amely a célszámunk számjegyei számának megfelelő függőleges tartományra mutat, tehát ha a példánkban lévő öt számjegyből álló számot vizsgáljuk, akkor egy öt soros tartományt szeretnénk összehozni - igen, mert függőlegesen már pár lépés után szépen össze fogjuk majd tudni adni az értékeket.

Hogy érjük ezt el? Első lépésként szükségünk lesz egy ilyen képletre:

=OFFSET(A1,LEN(D5)-1,0)

Az előzőek alapján elég egyértelmű, hogy ez az A1 cellától annyit fog majd lefelé haladni, amennyi a célszámunk karaktereinek száma (a mínusz egyre azért van szükség, hogy pontosan ennyit haladjunk, hiszen maga az A1 cella is egy sornak számít), tehát jelen esetben 4-et. És most jön az egész képlet kulcsmomentuma, ami a ROW függvény képében jelenik majd meg megoldásunkban, ez a roppant egyszerű és roppant ritkán használt kis formula simán megadja egy meghivatkozott cella sorszámát - tartomány esetén pedig tömböt ad vissza, azaz megadja az adott tartományban lévő cellák sorainak sorszámát.

Ergó az =ROW(X1:X5) egy öt értékből álló tömböt ad vissza egytől ötig, hiszen tartományunk celláinak ezek a sorszámai. De ez hol jön most a képbe? Ha fogjuk az előző OFFSET függvényünket és beágyazzuk a ROW függvénybe a következő módon:

=ROW(A1:OFFSET(A1,LEN(D5)-1,0))

Akkor egy tömbben meg fogjuk kapni, hogy pontosan hány számjegyből áll célszámunk, hiszen az OFFSET-tel lehaladtunk annyi sort, ahány számjegyünk van, a ROW függvénnyel pedig ezt vissza is kértük mint hivatkozást egy tömbre. Jelen esetben tehát kiírva ugyan nem, de valójában a példánkban ott tartunk, hogy függvényünk tudja: 1,2,3,4,5 - azaz öt számjegyből áll számunk.

Ezzel már a nehezén túl is vagyunk, hiszen innentől kezdve már alapfüggvényeket kell csak használnunk, elsőként a MID formula egyik kevéssé ismert lehetőségét, azaz ha tömböt adunk meg egyik paramétereként, akkor tömbben fogjuk visszakapni az értékeket - így lehet egyetlen lépésben feldarabolni egy szöveget három részre például, amit aztán egy tömbben tudunk eltárolni (fontos, hogy számok feldarabolása esetén is szövegformátumban jön vissza az eredmény!).

Azaz fogjuk az előző függvényrészünket és egy MID-be ágyazzuk:

=MID(D5,ROW(A1:OFFSET(A1,LEN(D5)-1,0)),1)

Ezzel pedig azt érjük el, hogy a D5 cellában szereplő értéket (itt van célszámunk), annyi részre fogunk egyesével feldarabolni, ahány sorból a ROW-OFFSET kombinációval előállított tömbünk áll A1 cellától kezdődően, majd az eredményt szintén tömbben fogjuk eltárolni. Miután ez igazából egyáltalán nem látható eredmény még mindig, elég komoly koncentrációt igényel, hogy egy ilyen megoldást megszüljünk (persze erre legyinthetnek sokan, de egy alap Excel-felhasználó számára ezt a szintet a legnehezebb megugrani), hiszen itt még fejben kell azt is tartani, hogy MID formulánk ezeket a számjegyeket szövegként adta vissza a tömbben, tehát még számmá kell őket alakítanunk.

Erre ott a zseniális VALUE függvény, amely egy szövegformátumból számformátumot varázsol nekünk és milyen fantasztikus, tartományokra/tömbökre is működik, azaz

=VALUE(MID(D5,ROW(A1:OFFSET(A1,LEN(D5)-1,0)),1))

az előbb létrejött tömbünk értékeit fogja számmá konvertálni, amit aztán szimplán már csak szummázni kell egy SUM függvénnyel és that's all:

=SUM(VALUE(MID(D5,ROW(A1:OFFSET(A1,LEN(D5)-1,0)),1)))

Persze ne felejtsük, hogy végig tömbképletről beszélünk, tehát CTRL+SHIFT+ENTER kell a használatához!

negyennyolc.jpgUgye, hogy végigvezetve nem is annyira földtől elrugaszkodott ez a feladat sem? Persze feltételezve, hogy többen már ismerték ezt a feladványt vagy nem okozott nekik gondot a megoldás, álljon itt egy kis kiegészítő kérdés: mit kellene még beiktatnunk, hogy számjegyeink szummájának számjegyeit is összegezzük és bármilyen hosszú szám esetén egyetlen számjegyet kapjunk a végén?

A bejegyzés trackback címe:

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

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.

Nincsenek hozzászólások.