Nem tudom már pontosan mikor, de valószínűleg 1-2 éve volt hasonló feladvány egy Exceles bajnokságon, amellyel ismét csak azt kívánom bemutatni, hogy a megszokott és ismert függvények kreatív felhasználásával egészen hihetetlen kérdésekre lehet egészen hihetetlen megoldásokat adni - azt a bizonyos feladatot emlékeim szerint akkor az összes résztvevő 10 percnél kevesebb idő alatt abszolválta, de ahogy általában lenni szokott, itt már nem a függvények összeállítása, egyszerűen a logika megtalálása a leghosszabb idő.

Adott a lenti példa és hozzá kapcsolódóan a feladat: adjuk meg B5:B7 cellákba azt, hogy az azonos sorokban szereplő dátumintervallumok és a B3:B4 cellákban megadott két dátum intervalluma között pontosan hány nap az átfedés!

Például az elsőnél mondjuk meg, hogy január 15. és május 30.-a közötti intervallumból hány nap esik január elseje és április elseje közé. Finom kis fejtörő, főleg ha tudjuk, hogy alig néhány percünk van rá - de ha megvan a logika, akkor villámgyorsan összedobhatjuk az eredményt. És ahogy Excelben általában mindig, most sem egyetlen megoldás létezik, a kérdésre a választ ugyanis kis eltökéltséggel még egy IF-fel is ki tudjuk csikarni a programból, de most inkább nyúljunk két jóval egyszerűbb, ámde egy ilyen megoldásnál fel sem merülő formulához, a MAX-hoz és a MIN-hez.

A MAX nevéből adódóan megadja, hogy egy adott tömbben melyik a legnagyobb érték, a MIN pedig értelemszerűen a legkisebbet fogja visszaadni, ami nekünk itt azért fog jól jönni, mert meg fogjuk tudni, hogy a két összehasonlítandó intervallum kezdő illetve záró időpontjai közül melyik a nagyobb és a kisebb, azaz végül lesz majd két dátumunk, ahonnan már könnyen eljuthatunk a megoldáshoz.

Elsőként tehát nézzük meg, hogy első összehasonlításunknál melyik záródátum a kisebb a két pár közül, azaz:

=MIN(B$4,$F5)

Innen pedig már tudni fogjuk, hogy április elseje a kisebbik záródátum. Ezután ki kell derítenünk, hogy melyik a nagyobb nyitódátum, ehhez jön majd segítségként a MAX, azaz:

=MAX(B$3,$E5)

Innen pedig tudni fogjuk, hogy a nagyobb kezdődátum a január 15.-e, azaz ha ezt a két függvényt összedolgozzuk, akkor meg is van a közös intervallum, azaz a megoldás is. Na igen ám, de hogyan tegyük ezt?

Ha szimplán ezt véssük be a cellánkba:

=MIN(B$4,$F5)-MAX(B$3,$E5)

akkor kapunk már egy számot, de ez még nem helyes, hiszen nem lesz benne maga a kiinduló- illetve záródátum, ezért egyet mindenképpen hozzá kell majd adnunk.

=MIN(B$4,$F5)-MAX(B$3,$E5)+1

És ezzel kész is vagyunk, ott van az elvárt eredményünk. De módosítsunk egy kicsit a feladványon és tegyük fel, hogy olyan intervallumunk van az E5:F5-ben, amelynek nincs átfedése a kérdéses intervallumunkkal, lásd a példán:

Ekkor az eredmény negatív szám lesz, ami nem értelmezhető, hiszen ilyenkor azt szeretnénk látni, hogy 0, azaz nincs átfedés - ismét egy feladat, amelyet másképp is meg tudnánk oldani, de térjünk csak vissza a MAX függvényre megint! A MAX ugyebár számokat, egy tömb értékeit hasonlítja össze és adja vissza nekünk a legnagyobbat, ergó ha a fenti eredményünket beépítjük egy MAX függvénybe, mint paraméter, majd felkínáljuk neki másik paraméterként a 0-t, akkor értelemszerűen a nulla nagyobb szám, mint a negatív szám, azaz mindig 0-t kapunk, ha nincs átfedés:

=MAX(0,MIN(B$4,$F5)-MAX(B$3,$E5)+1)

Sőt, ha a 0-t be sem írjuk, az Excel automatikusan nullához fogja hasonlítani a különbségünket, azaz ez lehet a végső megoldás:

=MAX(,MIN(B$4,$F5)-MAX(B$3,$E5)+1)

De ez tipikusan az a probléma, amire szerintem bőven több, mint egy megoldás létezhet, azaz bátran írjatok, ha van egy jóval egyszerűbb, vagányabb ötletetek!

Az alig ismert vagy éppen titkos Excel-funkciókról szóló posztban nem igazán akartam szóba hozni, nem is azért, mert annyira elterjedt lenne a használata, hanem inkább azért, mert úgy gondoltam, megérdemel egy külön posztot a hihetetlen lehetőségeket rejtő SUMPRODUCT függvény, amelynek bemutatása után ki is térek arra, hogy pontosan mi mindenre lehet használni.

Ha bemegyünk az Excelünkbe és egyszerűen meghívjuk a függvényt, akkor már a leírásból szépen ki tudjuk találni, hogy mire való: két vagy több tömb elemeit szorozza majd adja össze, azaz ahogy a lenti példán is látható, a két tömb egymással egy sorban lévő elemeit összeszorozza majd az eredményeket szummázza. Eddig tehát ezzel nem is lenne gond, itt jön inkább a kérdés, hogy erre mi szükség van, hiszen ezt a feladatot ez a formula nélkül is simán meg lehet oldani.

Mi van akkor, ha kicsit komplikálom az előbbi példát, és az első oszlopban most már nem számok, hanem mondjuk megyék fognak szerepelni (mondjuk ahol egy cég boltjai működnek) és azt szeretném megtudni, hogy mondjuk Csongrád megyében pontosan az adott bolt mekkora forgalmat generált egy adott periódus alatt. Még jómagam is egyértelműen az egyszerűbbnek tűnő, jobban ismert SUMIF megoldás mellett tenném le a voksot, de ne adjuk fel, tartsunk még ki a SUMPRODUCT mellett, hogy meglássuk valódi értékét!

Szóval ezt a feladványt SUMPRODUCT segítségével elvileg a következőképpen oldhatjuk meg - gondolnánk elsőre:

=SUMPRODUCT((A2:A11="Csongrád"),B2:B11)

Azaz fogjuk az első oszlopunkat és mint tömb meghivatkozzuk, majd az egyenlővé tétellel arra utasítjuk a függvényt, hogy a Csongrád értéket tartalmazó cellákat keresse meg, majd az ezekhez tartozó értékeket a B2:B11 tömbünkból összesítse.

De hát az eredmény nulla lett! Miközben Csongrádnál 24 a szumma összeg, mégis miért nem jó a függvényem? Itt kell megtanulnunk valami olyat, amit máshol is könnyen hasznosíthatunk, azaz a kötőjel funkcióját az Excelben.

A kötőjel (illetve a dupla kötőjel) Boolean (IGAZ,HAMIS - TRUE, FALSE) értékeket alakít egyesekké és nullákká, sőt, mivel a program a kötőjelet "tagadásként" is értelmezi, még az előjelet is megfordítja, ergó egy kötőjellel egy TRUE értékből -1 lenne (a nulla nem változik), azaz még egy kötőjelre van szükségünk, hogy megkapjuk a vágyott egyesünket. Azaz erre van szükségünk:

=SUMPRODUCT(--(A2:A11="Csongrád"),B2:B11)

Hiszen első körben azért nem működött jól a formula, mert ugyan megtalálta a Csongrád értékeket, de ott a cella TRUE értéket vett fel, azzal pedig hiába szorozgatta a nyolcasainkat - a két kötőjellel viszont meg is van a kívánt eredményünk.

A SUMPRODUCT ereje szerintem abban rejlik, hogy kvázi iszonyú sok tömböt képes kezelni egyszerre (gyengesége meg abban, hogy ezen tömböknek azonos méretűnek kell lenniük), amit kapásból látni is fogunk, ha kibővítjük fenti példánkat egy újabb oszloppal (de ez lehetne akár 10 újabb oszlop is) és arra leszünk kíváncsiak, hogy Zala megye keleti részén mennyi volt az eladások értéke.

Innen pedig már roppant egyszerű a sztori, hiszen A2:A11="Zala" valamint B2:B11="Kelet" és a C2:C11 tömböket kell összeolvasztanunk egy SUMPRODUCT-ba, értelemszerűen dupla kötőjel használatával:

=SUMPRODUCT(--(A2:A11="Zala"),--(B2:B11="Kelet"),C2:C11)

És done! Mintha egy negyven oszlopból álló listában filterezgetnénk oszloponként, hogy megkapjuk a vágyott szummát, csak ezt most belevághatjuk egyetlen függvénybe - nem mondom, hogy gyorsabb, de hogy szép megoldás, az biztos. Barátkozzunk egy kicsit most ezzel a függvénnyel, ugyanis még vissza fogok térni rá egy emeltszintű "SUMPRODUCT kiképzés" alkalmával.

Mai posztom témáján nem kellett sokat gondolkodnom, egyik kedves Olvasóm küldött egy alapvetően rém egyszerűnek tűnő és reális kérdést, amelyről aztán úgy gondoltam, közzé is teszem megoldással együtt, hiszen elég sanszos, hogy másokban is felmerül majd egyszer ez a kérdés.

A modul a WORD, a probléma pedig a következő: sorszámozott dokumentumunkba hogyan lehet beszúrni egy számozatlan oldalt, anélkül, hogy a sorozat megtörne? Tehát 1,2,3,számozatlan oldal,4,5 és így tovább.

Nekem még pontosan ilyen gondom még nem volt, de küzdöttem már egy hasonlóval, akkor az volt a célom, hogy egy sorszám maradjon ki az oldalszámok sorozatából, azaz 1,2,3 után 5,6,7 és így tovább folytatódjon a sorozat. Azt sem volt túl könnyű megszülni, de a mostani megoldáshoz az adta az ihletet. Azt a kérdést a következőképpen oldhatjuk meg:

Ráállunk annak az oldalnak az elejére, amelyről ki akarjuk dobni a számot, majd a Page Layout ribbonfül Page Setup szekciójában a Break funkcióra kattintunk és beszúrunk egy Next Page breaket, ami konkrétan egy megszakítást jelent két oldalunk között.

Ezután duplán kattintunk annak az oldalnak a Footerén, amelyből ki akarjuk szedni a számunkat, majd az itt megjelenő Same as Previous opcióra kattintunk, utána pedig Ribbonunk Design fülén a Navigation szekcióban kikapcsoljuk a Link to Previous szabályt, majd szabadon törölhetjük sorszámunkat a sorozatból az adott oldalon.

Ezzel pedig azt hiszem, tökéletesen látszik is, hogy hol rejtőzik a megoldás kedves Olvasóm számára: két Next Page break használatában.

1. Kattintsunk annak az oldalnak a tetejére, amelyik mostantól majd az új második oldalunk lesz (tehát amelyik előtt lesz majd egy számozatlan oldal az első után), majd a Page Layout ribbonfül Page Setup szekciójában a Break funkcióra kattintunk és beszúrunk egy Next Page breaket.

2. Duplán kattintunk a még mindig hármasnak számító lapunk tetején, az itt megjelenő Same as Previous opcióra kattintunk, majd leballagunk szépen az oldal aljára és a hármas számnál Ribbonunk Design fülén a Navigation szekcióban kikapcsoljuk a Link to Previous szabályt.

3. Kijelöljük hármas számunkat, majd Ribbonunk Design fülén a Header & Footer szekcióban a Page Number menü alatt Format Page Numbers menüben a Page Numberinget beállítjuk úgy, hogy ezen az oldalon kettőtől induljon a számozás. Ezzel konkrétan "elszakítjuk" egymástól első két lapunkat és a harmadikat, azaz mostantól ez lesz a sorszámunk: 1,2,2,3,4,5 stb.

4. Innen pedig már nagyjából ugyanaz lesz a tennivaló, amit már fentebb részleteztem tapasztalataim kapcsán, azaz most annak az oldalnak az elejére kattintunk, amelyik a számozatlan lapunk lesz, majd a Page Layout ribbonfül Page Setup szekciójában a Break funkcióra kattintunk és beszúrunk egy Next Page breaket.

5. Ezután duplán kattintunk annak az oldalnak a Footerén, amelyből ki akarjuk szedni a számunkat, majd az itt megjelenő Same as Previous opcióra kattintunk, utána pedig Ribbonunk Design fülén a Navigation szekcióban kikapcsoljuk a Link to Previous szabályt, majd szabadon törölhetjük sorszámunkat a sorozatból az adott oldalon.

És lám, kész is vagyunk, azaz ahogy a megoldás-képemen látszik, egy oldal kimaradt az oldalszámokból, de mégis szépen összeállt a sorozatunk.

Köszönöm a kérdést, élmény volt gondolkodni rajta egy kicsit!

Általánosságban elég nagy bizonyossággal kijelenthető, hogy a legjáratosabb Excel-felhasználók sem használják a programban rendelkezésre álló függvények jelentős részét (egy felmérés szerint az átlagfelhasználó az Excel 5%-át ismeri és használja), sőt, én rendszeresen találkozom azzal az amúgy igencsak becsülendő jelenséggel, amikor valaki az általa ismert formulák segítségével old meg egy olyan kérdést, amelyre egyébként már létezik egy kifejlesztett egyedi függvény is. A következőkben öt olyan függvényt mutatnék be, amelyek nem túlságosan ismertek, de szerintem roppant hasznosak lehetnek és sok területen szükség is lehet rájuk, hiszen beágyazásoktól, segédcelláktól kímélhetnek meg bennünket.

Az első bemutatásra kerülő függvény már csak a körülötte kialakult rejtélyesség miatt is a DATEDIF nevű formula, amely a Ribbon Formulas füle alatt található "Insert Function" listájában nincs is benne és egyedül talán valamelyik régebbi Excel verzióban volt dokumentált nyoma bárhol is a programban (ennek okáról tudok is egy jó pletykát, amely szerint egy felfedezett hiba kijavítása helyett vették ki a listából, azaz egy januári és egy márciusi dátum között nem egészen jó számot hoz a függvény). Ettől függetlenül ha cellába visszük fel, akkor működik és nagyon is jól működik, hiszen megadja nekünk két dátumot között a különbséget abban az értékben, amire szükségünk van - azaz az első paraméterként bedobjuk az első dátumot, második paraméterként a másodikat, majd harmadik paraméterként megadjuk, hogy napban, hónapban, évben vagy ugyanezekben úgy, mintha ugyanarról az évről beszélnénk (ezek a paraméterek az "m","y","d","ym","yd","md"), tehát például a

=DATEDIF(F6,F7,"m") az F6 és az F7 cellákban szereplő dátumok közötti különbséget adja meg hónapban.

Sok-sok kalkulációtól meg tudjuk magunkat kímélni, ha felírjuk elménk füzetének egyik lapjára a CONVERT formula nevét, ugyanis ennek segítségével 49 különböző mértékegység között tudunk átváltásokat végezni, persze csak egy bizonyos típuson belül (tehát kilométert ne akarjunk literre váltani). Be nem fogom most másolni ide az összes lehetőséget, aki szeretne elmélyülni a dologban, az hívja segítségül a Helpet a teljes listához.

Mindenesetre példánkban az =CONVERT(E3,"kg","g") értelemszerűen ki fogja írni, hogy az E3 cellában tárolt 10 kilogramm pontosan hány grammnak felel meg.

Általában mit teszünk, ha azt szeretnénk megtudni, hogy egy adott Excel-fájlunkat éppen hova mentettünk vagy honnan nyitottunk meg? Vagy megpróbáljuk a folderek között levadászni vagy hozzáadjuk a Document Location parancsot a Quick Access Toolbarunkhoz - vagy használjuk az INFO függvényt! Ez ugyanis információkat szolgáltathat operációs rendszerünkről, Excelünk verziószámáról, kalkulációs metódusáról, nyitott munkalapjaink számáról és hát adott fájlunk könyvtárának elérési útjáról is.

Azaz az =INFO("directory") szépen meg fogja adni, honnan nyitottuk meg aktuális fájlunkat.

Majd későbbi példákban hozni fogok olyan kérdéseket, ahol rendkívül kapóra jön majd nekünk a DELTA formula, a formula amely alapvetően nem csinál semmi mást, mint összehasonlít két számot és kiírja, hogy egyenlőek-e vagy sem.

Tehát =DELTA(A1,A2) összehasonlítja az A1 cellában lévő számot A2-vel, majd ha ezek megegyeznek, 1-et ad vissza, eltérés esetén 0-t. Gondolom most néhány emberben felötlött a gondolat, hogy vajon mégis hol a fenébe lehetne ezt jól hasznosítani - jómagam két célra használtam már a múltban több ízben, az egyik esetben akkor, amikor nem számformátumban tárolt értékeket akartam Number formátummal bíró számokkal összehasonlítani, a másik esetben pedig akkor, amikor egy hatalmas listában a megegyező értékeket szerettem volna összeszámolni beágyazott függvénnyel segédcellák nélkül.

Könnyedén megkerülhető, ám szerintem elég szép megoldás az ERROR.TYPE függvény alkalmazása, amely konkrétan egy cellában lévő ERROR értékekből (pl. #NULL! vagy #VALUE!) számokat ad nekünk vissza, tehát mondjuk #NULL! esetén az ERROR.TYPE egy egyest fog nekünk kiírni. Ez akkor jöhet igazán jól, ha hibaüzenetek esetén inkább szöveget szeretnénk megjeleníteni, nem a megszokott és idegesítő #N/A-kat. Lásd példa.

A következő, egy valós probléma kapcsán felmerült Excelt-kérdést igyekeztem igencsak általános formába önteni az egyszerűbb érthetőség kedvéért, de így is igencsak megdolgoztatja az ember agytekervényeit, hiszen bármennyire is egyszerűnek tűnik a kérdés, a válasz meglelése már okozhat egy kis fejtörést (bár ez a feladat talán Excel-guruknak nem okoz akkora kihívást, viszont mindenki másnak igencsak jó tanulási lehetőség). Szóval a példánk két táblázatát kellene összekapcsolni, méghozzá úgy, hogy a B ("Megoldás") oszlopba kerüljön be az A oszlopban lévő mondatokban szereplő színek alapján a színekhez tartozó szám az E oszlopból.

Már a példa felvázolása is utal arra, hogy itt valamiféle VLOOKUP-jellegű megoldást kell keresni, és ebben nincs is nagy tévedés, hiszen a megoldást a (nem is annyira) nagyszerű LOOKUP rejti magában, amelynek használata nem meglepő módon eléggé visszaszorult, hiszen a VLOOKUP jelentősen egyszerűbb és könnyebb használatot tesz lehetővé.

A LOOKUP ugyanis az első paramétereként megadott értéket fogja keresni egy, a második paramétereként megadott tartományban, majd találat esetén a harmadik paramétereként tartományból adja ki az előbbi tartománnyal egy sorban/oszlopban szereplő értéket.

Azaz a =LOOKUP(5,A2:A5,D2:D5) megnézi, hogy A2:A5 tartományban hol van az ötös érték, majd az ezzel egy sorban lévő értéket dobja nekünk vissza D2:D5 tartományból.

Viszont itt meg kell állnunk egy kicsit, ugyanis a LOOKUP-nak van egy olyan tulajdonsága, amelyet én kifejezetten nem kedvelek és emiatt nem is annyira szeretem használni ezt a formulát, ez pedig az, hogy emelkedő sorrendben kell lenniük a második paraméterként megadott tartományban az értékeknek, hogy helyesen működjön, ugyanis a függvény logikája alapján feltételezi az emelkedő sorrendet.

Lásd a lenti példát, függvényünk az elvárt 1 helyett 3-at ad vissza, de ha jól megnézzük, pont azért, mert nem növekvő a sorrendünk. Ha emelkedőbe vágjuk a színek sorozatát, akkor máris azt kapjuk, amit elvárunk. Ugye milyen vicces?

Na de ugorjunk vissza a kiinduló problémához. A LOOKUP formulával tehát nagyjából megbarátkoztunk, de lehet, hogy sokaknak még nem áll össze, hogy ez pontosan hogy is fog segíteni nekünk, ami érthető is, ugyanis még van egy másik függvény is, amit majd a LOOKUP-ba kell ágyaznunk, ez pedig igencsak szorgos és hasznos kis barátunk, a SEARCH. Szerintem nem nagyon van Excelt rendszeresen használó ember, aki ezt ne ismerné, hiszen kis formulánk az első paramétereként megadott szót/szövegrészt fog egy, a második paramétereként megadott szövegben keresni és visszaadja, hogy hányadik karakternél kezdődik keresett szövegünk.

Például a =SEARCH("Autó",D2) az Autó szót fogja megkeresni a D2 cellában és megadja, hogy az adott szövegben hányadik karakternél kezdődik a szó.

Ezzel pedig már össze is állt a kiinduló kérdésünk megoldásához vezető út, hiszen elsőként meg kell keresnünk hányadik karakternél indulnak az aktuális színek az A oszlopban szereplő szövegekben, majd ezt egy LOOKUP-ba kell ágyaznunk. Igenám, de mit keressünk és hol? Mert az egyértelmű, hogy a LOOKUP harmadik paramétere az E2:E6 tartomány lesz, hiszen itt csücsülnek eredményeink. Hogy hol keresünk, az is nagyjából összeállhat, hiszen itt a SEARCH által létrehozott tartomány lesz a kulcs, azaz a színeket megkeressük a szövegrészekben/mondatokban, majd az így kapott karakterszámokból összeálló tartomány lesz második paraméterünk a LOOKUP-ban. Így már az is adja magát, hogy a keresett értékünk valami jó nagy szám legyen, azaz lefedje az összes lehetséges karakterszámot - ergó, mivel egy cellába emlékeim szerint 32767 karakter fér, adjuk meg ezt a számot (de jelen esetben a leghosszabb mondatunk hossza is megteheti).

Így a megoldásunk:

=LOOKUP(32767,SEARCH(D$2:D$6,A2),E$2:E$6)

Az oldaldobozban is látható néven is elérhetőségen már Facebookon is várom a kérdéseket, ötleteket, javaslatokat, illetve mostantól már ott is elérhető minden írásom és elmélkedésem. Várlak benneteket!

A következő posztban továbbra is maradunk az Excelnél (előbb-utóbb teszek újabb kitekintést más modulokba is megint) és egy kedves Olvasó által beküldött kérdést járok majd körül (utólagos engedelmével), ami egy igencsak érdekes problémát vet fel egy sokak által napi szinten is rengetegszer használt funkció, az Autofill kapcsán.

Szóval a felvetés a következő képen látható példához (a példa a saját értékeimen és kísérletemen alapul, de ez ráhúzható minden hasonló témára) kapcsolódik, azaz ha kijelölve a három cellánkat lehúzzuk, hogy az Autofill tegye a dolgát, miért 2.33333 a következő érték?

Hogy ne csak egyetlen problémán lássuk ezt a történetet, íme egy másik hasonló kérdés, azaz ha a következő képen látható sorozatot eggyel lejjebb húzzuk, miért 47 lesz a következő érték?

Hivatalosan dokumentált magyarázatról nem tudok, de az én elméletem szerint a magyarázat a lineáris regresszióban és a trendvonalban keresendő, azaz ha felrajzoljuk értékeinket egy koordinátarendszerben és megpróbáljuk összehozni az úgynevezett "best-fit" lineáris trendvonalat, akkor az Excel számítása szerint a koordinátarendszerben a trendnek megfelelő következő érték az Autofill által generált szám lenne.

Elsőre elolvasva ez lehet, hogy kicsit értelmetlennek tűnik (gondolom egy matematikatanár csak nevetne a leírásomon, de én normál szavakkal próbálom leírni az elméletem, hogy bárki megérthesse), úgyhogy következzen itt két diagram és egy trendvonal.

Az első diagramunk a rajta lévő trendvonallal tökéletesen mutatja a lineáris trendjét számaink alakulásának - ha ezt a trendvonalat továbbhúznánk, a következő elem a 47 lenne, ahogy ez a második diagramon tökéletesen látszik is. És ez az elmélet működik az 1,1,2-es sorozatunkra is, sőt igazából bármely olyan nem lineáris sorozatra, amelynél Autofillt szeretnénk használni.

Persze ez csak inkább elméleti kihívás volt, hiszen ha konkrétan tudjuk, hogy mit szeretnénk látni következő értékként az Autofill által felkínált verzió helyett, az nem lehet túlságosan nagy feladat, hiszen mondjuk a CTRL billentyű használatával az Autofillünket másolásra kényszeríthetjük, de mondjuk a következő példa esetén is könnyen elérhetjük az eredményünket függvények segítségével.

Adott a következő sorozat, ahogy a képen is látható: 1,1,1,2,2,2 - hogy érjük el, hogy így folytatódjon: 3,3,3,4,4,4?

Több megoldás is létezik, de én elsőre valószínűleg egy IF - COUNTIF kombinációt használnék. A COUNTIF-ről tudjuk, hogy egy adott tartományon belül számolja meg a második paramétereként megadott kritériumnak megfelelő értékeket, azaz példánk esetén a

=COUNTIF(A1:F1,F1)

azt fogja megnézni, hogy az A1:F1 tartományban hányszor fordul elő az F1 érték.

Innen már látszik, hol jön a képbe az IF függvény, hiszen csak annyit kell megnéznünk, hogy az F1 (vagy bármely cella) előfordulása egyenlő hárommal vagy sem, hiszen innen tudja majd a függvényünk, hogy lépjen egyet feljebb a számok sorozatában.

Azaz a következő függvénykombináció adja meg a megoldást:

=IF(COUNTIF(A1:F1,F1)=3,F1+1,F1)

Tehát az előbb már megismert COUNTIF-fel megszámoljuk F1 előfordulását, majd megnézzük, hogy ez egyenlő-e hárommal vagy sem. Amennyiben egyenlő hárommal, akkor a következő cellába az F1 értékét eggyel megnövelve írja, ha nem egyenlő hárommal, akkor még F1 értékét írja ki. Hát, én az ilyenek miatt imádom az Excelt (is).

É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.

Ahogy 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)

Ez 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!

Ugye, 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?

Miután az elmúlt posztokban kicsit megmozgathattuk agytekervényeinket Excelben (valahogy úgy alakult, hogy ennyi idő után is még mindig csak három Office-alkalmazásról volt szó, de ne aggódjunk, sorra kerül a többi is, sőt még Sharepoint is lesz, ahogy idő és energia engedi), térjünk vissza jó kis szövegszerkesztő barátunkhoz, a WORD-höz és néhány újabb, igencsak kellemes kis funkció bemutatásával erősítsük tovább azt a hitet magunkban, hogy ebben a programban is jóval több van, mint amit első látásra feltételeznek róla sokan.

Tudjuk-e például, hogy ha valakinek csak mondjuk formázást, hasábokat, fontokat szeretnénk megmutatni egy dokumentum elkészítése előtt, akkor nem kell hosszas időráfordítással szövegeket másolgatnunk vagy magunknak pötyögni egy kis asdfjklét, hanem simán használhatjuk a WORD beépített lorem ipsum generátorát (vagy a random szöveggenerátorát)? Azok kedvéért, akik nem tudják, hogy mi ez, a lorem ipsum egy afféle töltelékszöveg, amellyel főként kiadók, grafikusok operálnak és amely egy Cicero mű szavainak összekevert, értelmetlen verziója.

Lépjünk be egy üres dokumentumba, gépeljük be a következőt:

=LOREM(1,1)

Majd nyomjuk egy ENTER-t és már előttünk is van egy teljesen értelmezhetetlen latin mondat. A LOREM funkció paraméterek nélkül is használható, de ha paraméterekkel használjuk, akkor az első szám a bekezdéseket, a második pedig a bekezdésekben szereplő mondatok számát jelenti, ergó

=LOREM(100,100)

egy száz bekezdéses, 10 ezer mondatot tartalmazó dokumentumot hoz nekünk létre pillanatok alatt.

Ugyanezt tudják egyébként a =RAND(1,1) és a =RAND.OLD(1,1) funkciók is, annyi különbséggel, hogy mindkettőnél pár értelmes angol mondat ismétlődik, illetve utóbbi WORD 2003 vagy régebbi verziókkal is kompatibilis és használható. Hozzám valahogy a lorem ipsum áll közelebb, de az biztos, hogy a szövegszerkesztőt sokat használók számára mindegyik megoldás megjegyzendő.

Sok esetben, főként hosszú szövegek esetén lehet hasznos a Spike, amelynek segítségével szövegrészeket tudunk a Quick Partsba menteni (megkövetek mindenkit, de hosszú évek angol Office-használata nem múlik el nyomtalanul), hogy aztán később egyben beillesszük őket valahová. A következő példán be is mutatom, hogy tudjuk ezt megtenni.

Szóval van egy szövegünk, amelyből egy-két szövegrészt szeretnénk kiszedni (mondjuk egy leíráshoz ollózgatunk innen-onnan), akkor jelöljük ki az első fontos szövegrészünket, majd nyomjunk CTRL+F3-at. Ez kivágja a kijelölt szövegrészt, úgyhogy mehetünk is tovább, CTRL+F3-al szépen gyűjtögessük össze a nekünk fontos tartalmakat. Aztán ha megvagyunk, CTRL+SHIFT+F3 lenyomásával bárhova be is illeszthetjük az egészet egyben. Persze beillesztés előtt meg is nézhetjük az összemásolgatott szövegünket, ha a Ribbonunk Insert füle alatt található Quick Parts menüre kattintunk (TEXT szekció) és ott a Building Blocks Organizerbe lépünk. Itt Spike név alatt meg is találjuk, amit összemásolgattunk.

Ezzel a funkcióval sokan azért nem is akarnak barátkozni, mert alapesetben csak kivág és beilleszt, tehát pont a másolást nem tudja teljesíteni, amire igazán szükségünk lenne - nos, ez nem így van, egyszerűen a CTRL+F3 kombináció után CTRL+Z-vel vissza kell vonnunk az előző műveletet. Így a kivágott szövegünk visszakerül, ám a Spike blokkunkból már nem törlődik, szóval elérhető a másolás lehetősége is.

Egy időben igen nagy segítség volt számomra, az a lehetőség, amely ha csak minimális mértékben is, de számolni segített WORD-ben: szóközökkel elválasztott számokat szövegszerkesztőnk össze tud adni, de szorozni, osztani és kivonni is képes, ráadásul nem kell a számoknak egymás mellett állni, simán egy szövegben is lehetnek, a szóköz az egyetlen feltétel. Annyit kell csak hozzá tennünk, hogy a Quick Access Toolbarban hozzáadjuk a Calculate parancsot a gyorsan elérhető eszközeink listájához, majd amikor számításra van szükségünk, egyszerűen csak kijelöljük a számolandó értékeket és Calculate.

Nem egy Excel, de arra a célra tökéletes, amire WORD használata közben szükségünk lehet.

Tömbképletek, tömbfüggvények. Emlékeimből rémlik, hogy milyen hosszan szokták ezeket taglalni a szakirodalomban, oktatásokon, de én most nem tervezem, hogy bármiféle leírást próbáljak utánozni vagy bemásolni, hiszen valószínűleg ezeket a segédanyagokat bárki bármikor meg tudja találni magának, úgyhogy csak néhány szóban bemutatnám őket, hiszen ezeknek az ismerete létfontosságú ahhoz, hogy azt az apró lépcsőfokot meg tudjuk ugorni és már haladó Excel-felhasználóknak hívhassuk magunkat.

A következő példán tökéletesen látni fogjuk, hogy miről is van szó alapesetben, hiszen ha a lenti táblában a költség értékét szeretnénk meghatározni (hangsúlyozom, ez a tömbképletek egyik legegyszerűbb felhasználási módja), akkor szinte mindenki valószínűleg csak összeszorozza G és H oszlopokat, majd lehúzza az eredményt és kész is.

De mi lenne, ha tömbképlettel próbálkoznánk, hiszen ezek a képletek egyszerre több kalkulációt is el tudnak végezni és megspórolnának nekünk egy kis időt? Jelöljük ki a Költség oszlopot, I3-tól I7-ig, majd a Képlet (Formula) mezőbe írjuk be a következőt:

=G3:G7*H3*H7

Ezután nyomjuk le minden tömbképletek legfontosabb billentyűkombinációját, a CTRL+SHIFT+ENTER-t, amely után az Excel kapcsos zárójelek közé teszi képletünket ({ } - ezzel jelezve, hogy tömbképletről van szó) és kitölti az I oszlopot az eredményeinkkel. Ugye, hogy milyen egyszerű és hatékony?

Anno a Microsoft úgy reklámozta ezeket a képleteket, hogy tömbképletekkel kalkulációs fájlunk mérete kisebb lesz és biztonságosabb is lesz a használata, hiszen nem lehet csak úgy egyszerűen megváltoztatni egy elemét a tömbképletünknek. Én azonban azt mondanám, hogy a legfontosabb érv mellettük az, hogy vannak olyan problémák, amelyeket "normál" módszerekkel, függvényekkel már nem lehet megoldani.

És ez főként azért van így, mert bár tömbképleteknek hívják őket, egyetlen egy cellában is használhatóak egyetlen eredmény elérésére, ahogy a következő példában látszani is fog.

Ha ki szeretnénk számolni az egy dolgozóra jutó költség összegét, valószínűleg ismét csak képletezgetnénk egy egyenlőségjel után, ami persze érthető is, hiszen egy ilyen egyszerű probléma esetén ki a fene szórakozik tömbképletekkel, viszont a logikát megérteni jó lesz, hiszen ha egy tetszőleges cellába az alábbi képletet írjuk be és nyomunk utána CTRL+SHIFT+ENTER-t, nézzük csak mit kapunk:

=(SUM(G3:G7*H3:H7))/K3

Először tehát létrehozza a két tömbünk szorzatát, az eredményeket összegzi, majd elosztja K3 cella tartalmával. Fantasztikus és még csak az utunk legelején járunk, hiszen a tömbképletek legnagyobb hasznát ott vesszük, ahol már más megoldás nem nagyon segíthet.

Például adjuk össze a példában látható számokat a SUM függvény felhasználásával.

Ugye, hogy nem is olyan egyszerű? Az a fránya #N/A eléggé megakasztja a SUM működését, amit viszont a következő tömbképlettel könnyedén ki tudunk játszani:

=SUM(IF(ISERROR(N3:N9),"",N3:N9))

Ezt ugyanis egyetlen cellába beírva (CTRL+SHIFT+ENTER-t sose felejtsük el) kapásból megkaphatjuk az #N/A (vagy egyéb ERROR-értéket tartalmazó) érték nélküli szummánkat, hiszen ahogy látható, a fenti formula az N3:N9 tömb minden ERROR-eredményét lecseréli "semmire", majd ezeket a számokat össze is adja.

De hogy még ezt is megspékeljük egy kis elmélkednivalóval, legyen az a feladat, hogy még mindig a fenti táblánál maradva, egyetlen szót írassunk ki eredménycellánkba, mégpedig azt, hogy szummánk eredménye páros vagy páratlan szám-e. Ehhez a MOD függvényre lesz majd szükségünk, amely roppant egyszerű célt szolgál, a paraméterként beadott osztandó és osztó alapján kiírja nekünk az osztás műveletének maradékát - ergó ha ez a maradék nulla, páros számról beszélünk, ha nem, páratlanról. És ezt kellene még majd beágyaznunk a fenti tömbképletbe, hogy a végeredményünk tényleg csak egy szó legyen.

Kezdjük az elejéről, azaz mondjuk meg egy számról, hogy az páros vagy páratlan-e:

=IF(MOD(A1,2)=0,"Páros","Páratlan")

Mit is csinál ez? Ha az A1 cellában lévő számot elosztjuk kettővel és nulla lesz a maradék, akkor az páros, ellenkező esetben páratlan. Akkor most ezt hozzuk össze a korábbi tömbképlettel valahogy így:

=IF(MOD(SUM(IF(ISERROR(N3:N9),"",N3:N9)),2)=0,"Páros","Páratlan")

Ha jól megnézzük, mit is csináltunk itt, látható, hogy egyszerűen csak az első képletünkben található A1 cellát helyettesítettük a teljes korábbi szummás tömbképletünkkel és így értük el az eredményünket:

Ezzel zárom is a tömbképletek világába bevezető első posztomat, a következő részben már komplexebb tömbképletekkel fogunk megismerkedni és kihívást jelentő feladatokon mutatom meg, hogy mi mindenre képesek lehetünk, ha jól megtanuljuk ezen képletek használatát.