Dinamikus tömbök - az Excelt teljesen új alapokra helyező funkcionalitás már a spájzban van!

2018. november 24. - Office Guru

Az Excel függvényeinek magas szintű ismerete és biztos használata alapvető a függvények másik dimenziójának, a tömbfüggvényeknek a használatához - tudjuk, ezek a CTRL+SHIFT+ENTER segítségével előhívható és tömbök kezelésére szolgáló függvények. Ennek tükrében és főleg használatuk nehézkessége miatt nem igazán voltak elterjedtek, de ahogy az elmúlt hetek híreiből kiderült, nem is fognak nagyobb szintű ismertségre szert tenni a jövőben sem, ugyanis már itt vannak az előszobában az Excel beépített, dinamikus tömbfüggvényei. 2018. szeptemberében ugyanis a Microsoft bemutatott hét új függvényt, amelyek dinamikus tömbök kezelésére fognak szolgálni és ezzel feleslegessé teszik a régi CTRL+SHIFT+ENTER-féle tömbfüggvények használatát.

Jelenleg azok számára elérhető ez a hét funkció, akik feliratkoztak az Office 365 Insiders Programjára és bár hivatalos dátumot még nem tett közzé a cég, a közeljövőben minden felhasználó megismerkedhet ezekkel az új lehetőségeket jelentő függvényekkel és a velük együtt érkező új, ún. spill range lehetőségeivel. Hogy miről is van szó, azt a következő példán keresztül mutatom be. Adott a következő tábla, amely néhány várost tartalmaz, de több előfordulással:

pelda1.PNGAlapesetben, ha azt szerettük volna megmondani, hogy melyek az egyedi értékek, akkor mindenféle függvényes machinációkat alkalmazhattunk vagy akár egy tömbfüggvényt, ahogy én fogom mindjárt tenni a jó öreg INDEX-MATCH baráti páros segítségével.

Azt már ugye betéve tudjuk, hogy az INDEX formulának a segítségével egy tábla megadott sorában, oszlopában vagy sor és oszlop kereszteződésében található értéket kaphatjuk vissza, a MATCH pedig az első paramétereként megadott érték helyzetét fogja a második paramétereként megadott tartományból megadni nekünk (a harmadik paraméter a pontos egyezés, kisebb-nagyobb feltétel megadására ad lehetőséget). Így a következő formula tömbfüggvényként használva segíteni fog nekünk az egyedi értékek meghatározásában:

INDEX($D$21:$D$50,MATCH(0,COUNTIF($F$20:F20,$D$21:$D$50),0))

Az INDEX segítségével megadjuk, hogy a D21:D50-es tartományban keressük az értéket és hogy hányadik sorban van ez az érték (ugye ez az INDEX második, sorra vonatkozó paramétere), azt a MATCH adja meg. De a MATCH sem olyan simán fogja ezt visszaadni, ugyanis a használt tömbhöz beágyazunk egy COUNTIF-et, ami a céltáblánkat fogja vizsgálni soronként és azt nézi meg, hogy beírtuk-e már a céltáblánkba az adott várost, mert ha be, akkor ugyebár a MATCH nem nullát fog találni (hiszen 0-t keresünk) és megy a következő sorra a függvény. Ha viszont nem találja az adott várost, akkor beírja a következő sorba, majd megy tovább a kiinduló tartományunkon és vizsgálja a következő értéket.

Ezt így magyarázatként leírni elég körülményes volt, bár remélhetőleg maga a függvény annyira nem komplikált - a lényeg tehát, hogy erre nyomtunk egy CTRL+SHIFT+ENTER-t és automatikus kitöltés után már látjuk is az eredményünk:

pelda3.PNGNa viszont, amint elérhető lesz mindenki számára a dinamikus tömbök funkcionalitása, akkor az egész egyetlen könnyű lépésből végrehajtható lesz. Ugyanis szimplán csak beírjuk a funkció nevét (UNIQUE ebben az esetben), majd az eredményeket azonnal látni is fogjuk - tehát azonnal ki is tölti a céltáblát az összes egyedi értékkel.

 

pelda4.PNG

pelda5.PNGÉs ez a funkció még paraméterezhető is, tehát megadhatjuk azt is, hogy csak azokat az értékeket akarjuk visszakapni, amelyek pontosan egyszer fordulnak elő, de akár több oszlopból álló tartományok vizsgálatára is lesz lehetőség. És ez még csak egy, az ígért hét új funkció közül, úgyhogy érdemes a "spill range" fogalmával is megbarátkoznunk gyorsan. A Spill Range az a tartomány, ahova vissza fogja adni a dinamikus tömbfüggvény az eredményt, tehát nincs már több CTRL+SHIFT+ENTER és automatikus kitöltés.

Ha a Spill Rangeben vannak nem üres cellák is, akkor hibaüzenetet kapunk és a funkció lehetőséget ad azon cellák elmozgatására, majd automatikusan folytatja/megcsinálja a kitöltést.

És akkor ismerkedhetünk további új lehetőségekkel is, mint például a spill hivatkozással, amely mondjuk így nézne ki a példánk esetében:

A2#

Ha ezt adjuk meg egy függvényben, akkor az egész spill tartományra fogunk hivatkozni, amelynek első cellája az A2, tehát simán összedolgozhatunk több dinamikus tömbfüggvényt is.

És hogy jelenleg melyik 7 új funkciót tesztelhetik a szerencsések és melyekre várhat az egész Excel-társadalom?

UNIQUE - erről volt szó fentebb
FILTER - a megadott paramétereink mentén szűrhetünk le egy adatbázisban és automatikusan visszakapjuk a szűrés eredményét
RANDARRAY - kitölt nekünk egy tartományt véletlenszerűen generált számokkal (tehát nincs már RANDBETWEEN és automatikus kitöltéssel küszködés)
SINGLE - visszaad egy értéket egy cella sorának/oszlopának kereszteződéséből
SORT - sortolni tudunk egy megadott tartományt
SORTBY - sortolni tudunk egy megadott tartományt egy másik tartomány értékei alapján
SEQUENCE - egymást szekvenciálisan követő számokból tudunk tartományt létrehozni

És ami a legjobb, hogy ezek ugyan új funkcióként jönnek, de a funkcionalitás kiterjesztett a régi funkciókra is, tehát a spill range innentől kezdve napi szinten életünk része lesz. Őszintén mondom, alig várom!

Egy elrejtett "jokerfunkció" a Paste Special kazamaták mélyén

A mai írás még mindig az elmúlt, posztmentes időszak tapasztalataiból, kérdéseiből merít és egy alapvetően már többször átrágott kérdést, a Conditional Formattingot fogunk érinteni. Nem is konkrétan a CF használatát, hanem inkább a másolási lehetőségeket fogjuk kicsit megnézni, hiszen vannak (és szerintem lesznek is) kérdéses pontok.

Adott a következő tábla és Conditional Formatting:

capture1_1.PNGAhogy látható, azt szeretném elérni, hogy minden olyan cellám háttere sárga legyen, ahol az érték harmincnál kevesebb. Vegyük észre, hogy ki van jelölve a tartományom B3-tól B14-ig, tehát csak erre fog vonatkozni a formázás.

capture2_1.PNGEz eddig szépen is működik és az esetek jelentős részében ennyi elég is nekünk - de mi van, ha esetleg ezt a feltételes formázást másolni szeretnénk? Rakjunk egy másik táblát a megformázott mellé:

capture3_1.PNGJelöljük ki az eredeti táblát, majd nyomjunk egy CTRL+C-t és tegyük a vágólapra. Szimpla CTRL+V beillesztés helyett azonban nyomjunk jobb gombot az egerünkön, majd válasszuk a Paste Special menüt és utána még egyszer a Paste Special almenüt. Ezt fogjuk látni:

capture4_1.PNGVálasszuk ki ezen a képernyőn a Paste Formats rádiógombot és nyomjunk okét:

capture5_1.PNGAhogy látható, felülírtuk a második tábla formázását és átültettük az eredeti, első tábla formátumát - ezzel együtt pedig a Conditional Formattingot is. Viszont mi történik, ha a második tábla értékeit akarjuk beilleszteni az eredeti oszlopba, de úgy, hogy a Conditional Formatting ne vesszen el?

Elsőként jelöljük ki a második táblát, majd nyomjunk egy CTRL+C-t és tegyük a vágólapra. Szimpla CTRL+V beillesztés helyett azonban megint nyomjunk jobb gombot az egerünkön, majd válasszuk a Paste Special majd még egyszer a Paste Special menüt:

capture6_1.PNGÉs itt válasszuk az All merging conditional formats opciót a beillesztés előtt. Ez nem tesz mást, mint fogja a forráscellák összes formátumát és beilleszti az új helyre, de úgy, hogy az ott már meglévő Conditional Formattingot megtartja. Így miután okét nyomtunk, ezt fogjuk látni:

capture7_2.PNGÉs itt látható is, hogy miért említettem még a poszt elején, hogy figyeljünk arra, a Conditional Formatting milyen tartományra kerül beállításra, ugyanis ahogy látható, az eredeti tartomány méretéig szépen működik a CF, de az utolsó hármas azért nem lett sárga hátterű, mert már kívül van az eredeti CF tartományán.

Na de most ugorjunk csak vissza egy lépést! Tehát az első táblán van már egy feltételes formázás, bármilyen másolgatás előtt a másodikra is állítsunk be valamit:

capture8_2.PNGAhogy látható, a 100 és 600 közötti számok hátterét szeretném kékre állítani:

capture9_1.PNGHa ezután úgy szeretném a két tartományomat összemásolni, hogy mindkét CF megmaradjon, akkor szintén kijelölöm a másolandó tartományt, majd beillesztés előtt a Paste Special almenübe lépve ismét az All merging conditional formats opciót választom és úgy hajtom végre a műveletet:

capture10.PNGEzzel pedig elértük, amit akartunk, de mivel az eredeti tartomány méretére volt a CF is méretezve, így a második táblában is csak ezen a tartományméreten lesz beállítva a két CF, méghozzá úgy, hogy a másolt tartomány feltétele lesz az elsőszámú feltétel:

capture11.PNGAz All merging conditional formats ismerete igencsak fontos ilyen jellegű másolásnál, ugyanis ha csak Insert Copied Cellst vagy sima Pastet használunk, akkor eléggé viccessé válhat a Conditional Formatting működése (egyes cellákon jól fog működni, másokon nem, új sorok beszúrása pedig szintén meg tudja dönteni a funkciót). Szóval jótanácsként tároljuk el elménkben ezt az opciót, minden Conditional Formatting másolásnál jusson eszünkbe!

Függvényből futtatható függvény

Nem valószínűsítem, hogy ez sokaknál előfordult már egyáltalán kérdésként és ha valakinél elő is jött, valószínűleg ott is egy nem igazán átlagos feladat részeként - a cél az lenne, hogy egy formulát tartalmazó cellából ki kell olvasnunk a formulát, majd még le is kellene futtatnunk egy másik cellában.

capture1_3.JPGA feladat annyi lenne, hogy egy másik cellába olvastassuk ki ezt a függvényt, majd futtassuk is le, tehát egy másik cellába is kapjuk meg a szummánk eredményét. Az Excel ehhez már kínál nekünk beépített funkciót is, ez a FORMULATEXT nevű függvény, amely szépen vissza is adja szövegként a függvényünket:

capture2_3.JPG

capture3_3.JPGViszont a futtatáshoz szükségünk van egy aprócska kódra VBA-ban, úgyhogy gyorsan ugorjunk is be ALT+F11-gyel a VBA-editorba. A következő funkciót fogjuk definiálni egy modulban:


Function Futtat (Content as String)

    Futtat = Evaluate (Content)

End Fuction

A funkció a cella szöveges tartalmát felveszi paraméterként, majd az evaluate funkcióval végrehajtuk a megadott formulát és visszaadjuk az eredményt.

capture4_3.JPGInnentől kezdve ezzel a custom funkcióval már végre tudjuk hajtani a FORMULATEXT segítségével kiszedett, szövegformátumú függvényt. 

A másik megoldáshoz nem kell VBA, bár az egy kicsit macerásabb. Első lépésként lépjünk be a Formulas ribbonfül Defined Names szekciójában lévő Name Managerbe és hozzunk létre egy új nevesített tartományt:

capture5_3.JPG
Ez legyen mondjuk a "Futtat" névre hallgató tartomány, amely az EVALUATE funkció segítségével lefuttatja a sheetünk megadott cellájában lévő, szövegformátumú függvényt.

capture6_3.JPGFontos, hogy az EVALUATE kizárólag a Name managerben használható, ha nem hoztuk létre a custom funkciót, akkor nem tudjuk cellából közvetlenül meghívni. Szóval ha megvan a definált tartomány, akkor már csak beírjuk egy cellába és kész is vagyunk:

capture7_2.JPG

Három egyszerű, ám hasznos trükk a mindennapokra

Jó hosszú idő eltelt már azóta, hogy legutoljára friss anyagot tettem fel a blogra, de sajnos időnként előfordul(hat) mindenki életében, hogy nem igazán annyi idő jut a hobbikra, mint korábban - ez történt az én esetemben is, de most ismét megpróbálok felzárkózni és segíteni. Elnézést azoktól, akik közben levelet írtak, választ reméltek és nem kaptak, most azonban ismét nekifutok és előbb-utóbb feldolgozom a leveleket is.

Így tehát a következő időszak posztjaiban az elmúlt hónapok mindennapjai során felmerült problémák, kérdések megoldásaiból szemezgetek, kezdve a mai posztban néhány egyszerű trükkel, amelyek triviális mivoltuk ellenére igen jó szolgálatot tettek nekem.

Kezdjük az elsővel, amelynek kiindulópontja a következő képen látható helyzet:

cap1.JPGLe merném fogadni, hogy az Excellel dolgozók 99%-a rendszeresen találkozik azzal a hibával, amelyben Text formátumú cellákba kerülnek számok és emiatt például az erre a cellára hivatkozó képleteink nem igazán működnek megfelelően. Ha csak néhány esetről van szó, akkor simán átállítani a formátumot vagy a kis pöcök segítségével számmá konvertálni az értékeket megoldás lehet - viszont ez sok tízezer sornál hosszú-hosszú perceket jelent.

Ennek elkerülése érdekében tanuljuk meg egy életre a következő lehetőséget. Jelöljük ki azt az oszlopot, amelyben a hibás értékek szerepelnek, majd a Data ribbonfül alól válasszuk a Text to Columns funkciót:

cap2a.JPGAz első felugró ablakban Delimited opciót válasszuk, a másodikban a delimiterek elől vegyük ki a pipákat, tehát semmilyen elválasztót ne adjunk meg, a harmadik képernyőn pedig válasszunk General opciót az adatformátumra:

cap3.JPGHa a Finishre kattintunk, már látjuk is az eredményt:

cap4.JPGA következő apró tanács a Pivot-táblákra vonatkozik (amelyek egyébként képesek néha az őrületbe kergetni a felhasználót, annyi korlátozás van bennük), ugyanis könnyen bele lehet futni abba a problémába, hogy egyszerűen nem lehet szűrőt bekapcsolni a táblára (vagy akár sortolni sem lehet), ahogy ez a következő képen látható is:

cap5.JPGKicsit elrejtve, de azért jól látható, hogy a Filter funkció szürke, tehát nem használható, hiába akarnánk bekapcsolni a Pivot-táblán állva. A megoldás a billentyűkombináció használata, azaz ALT+A+T lenyomása után már el is érjük a kívánt hatást:

cap6.JPG

cap7.JPGA poszt végére pedig (afféle harmadik tanácsként) jöjjön egy fontos emlékeztető a Conditional Formattingról, méghozzá feltételes formázásunk kiterjesztésére vonatkozóan. Adott a következő kis tábla:

cap8.JPGHárom különböző variációban kellene színeznünk a táblában, elsőként azokat a sorokat kellene sárgára színeznünk, amelyekben a C oszlopban szereplő érték IGEN, másodikként a B oszlop értékeit kell a C oszlop IGEN-jei esetén sárgára színezni, végül pedig a teljes sort ki kell színeznünk, ahol a C oszlop IGEN-t tartalmaz.

Az biztos, hogy a Home ribbonfül Styles szekciójában található Conditional Formatting ikonra fogunk kattintani, ahonnan formulával fogjuk meghatározni, mely értékeket akarunk formázni az első, a második és a harmadik esetben.

cap9.JPGHa csak a C oszlop IGEN értékeit akarjuk színezni:

cap10.JPGVegyük észre, hogy ez a megoldás nem működik jól egy olyan táblában, ahol más oszlop is tartalmazhat IGEN értékeket, azokban az esetekben az Applies To mezőben módosítsuk, pontosan mely oszlopokat akarjuk figyelni.

Ha a B oszlop értékeit akarjuk sárgára színezni ott, ahol a C oszlop értéke IGEN:

cap11.JPGÉrtelemszerűen itt is módosítsuk az Applies To mezőt, ha más oszlop is tartalmazhat IGEN értékeket.

És végül ha a teljes sort színezni akarjuk IGEN érték esetén:

cap12.JPG

Egyébként a tapasztalataim szerint a Formula és az Applies To mezők megfelelő használatával bármilyen kombinációban tudunk vizsgálatot végrehajtani és azok alapján formázni, de nyugodtan osszátok meg tapasztalataitokat Ti is kommentben. 

A Calculation Options megtévesztő csapdái

Az Excel felhasználókat egyik legjobban megviccelő funkciójának címéért valószínűleg esélyesként indulna a Calculation mode, amit két módon tudunk buherálni a programban - egyrészt a Formulas ribbonfül alatt lévő Calculation szekció Calculation Options menüjében:

 calculation1.JPGMásrészt a File menüben megnyitható Excel Options Formulas almenüjében:

calculation2_1.JPGAzt valószínűleg a felhasználók 90%-a pontosan tudja is, hogy ez mire való, azaz ha Automatic calculationt állítunk be, akkor értelemszerűen az Excel újrakalkulál mindent az adott munkafüzetben, amikor egy érték változik vagy valamilyen meghatározott esemény történik (sorok beszúrása például), míg a Manual calculation csak akkor számít újra mindent, ha erre a felhasználó utasítást ad (mondjuk a Calculate sheet funkcióval).

Ennek megfelelően alapesetben nem is szokta senki használni a Manual calculation beállítást, csak ha óriási adathalmazról vagy sok tucat komplex függvényről beszélünk, mert olyan esetekben a manuális kalkuláció rendkívüli módon fel tudja gyorsítani a munkát.

Viszont ez a funkció óriási csapdát is rejt magában, kezdve azzal az alapvetéssel, hogy ez programszintű beállítás, azaz ha egy nagyobb munkafüzetünkben beállítjuk a manuális kalkulációt, akkor az minden más nyitott munkafüzetünkben érvényes lesz - ezt pedig jobb nem elfelejteni.

Még ennél jobban is megviccelhetjük magunkat akkor, ha le is mentjük Manual calculation beállítással az adott munkafüzetünket, mert innentől kezdve bármilyen Excel-fájlt ha megnyitunk, az már Manual beállítással fog megnyílni - erre szintén oda kell figyelni, főleg azért, mert nem biztos, hogy csak saját fájlokkal dolgozunk. Tehát ha a kolléga/vevő stb. küld nekünk egy Excel-fájlt, amiben Manual calculation a beállítás, akkor azzal szépen tönkreteheti egyik-másik munkánkat, ha nem figyelünk.

És akkor még nem is ejtettünk szót a VBA-ról, amelyben az Application Calculation tulajdonságának állítgatásával érhetük el a célunkat, úgymint például

Application.Calculation = xlAutomatic

vagy

Application.Calculation = xlManual

calculation3_1.JPGElég gyakran előfordul kódolás közben, hogy a sebesség érdekében a szubrutin kezdetén kikapcsolásra kerül az automatikus kalkuláció és csak a végén kerül ismét bekapcsolásra - ez pedig azzal a kockázattal jár, hogy ha mondjuk leállítjuk valahol a kódunkat, akkor a beállítás úgy marad, ahogy éppen a kód szerint be volt állítva.

Konkrét megoldás ezen fenti csapdák elkerülésére az ellenőrzésen kívül nagyon nincs, minden munkafüzetünkbe belehegeszteni a Calculation tulajdonságának beállítását elég macerás lehet, ahogy az is, hogy minden gépindításnál automatikus elindul egy Excel-munkafüzetünk, amelyben a tulajdonság is beállításra kerül, hogy utána minden megnyitott munkafüzetünk már automatikusan kalkuláljon.

A legviccesebb azonban a történetben az, hogy egy kvázi bug még ennél is jobban megnehezíti a dolgunkat. Alapesetben ugyebár ha a Formulas ribbonfül alatt vagy az Excel Options alatt (lásd fentebb) vizsgáljuk meg a kalkulációs beállítást, akkor mindkét helyen ugyanazt találjuk. De jelöljünk csak ki több sheetet egyszerre:

calculation4.JPGEzután ha megnézzük a Formulas ribbonfül alatt, akkor a kalkulációs mód még mindig Automatic:

calculation1_1.JPGDe ha a File - Excel Options alatt megnézzük, akkor már Manualon áll a rádiógomb. Persze megnyugodhatunk, alapvetően ekkor is Automatic calculation beállításon vagyunk, de eléggé megtévesztő, hogy a File Options alatt mást mutat, mint a Formulasnál.

calculation5_1.JPG

Automatikus jelzőnyilacska diagramunkra

A mai rövidebb posztban egy olyan technikát fogunk megnézni, amelynek segítségével többféle charttípusra tudunk mutató nyilakat vagy akár mosolygós arcocskákat is rávarázsolni, de úgy, hogy azok kalkulációnknak megfelelően mozognak is. Elképzelhető, hogy ebből a mondatból még nem vált egyértelművé, hogy mit is fogunk csinálni, úgyhogy vágjunk is bele a megvalósításba, aztán mindenki eldönti néhány perc múlva, hogy hasznos dologról szól-e a poszt vagy sem. Adott a következő adattábla:

elso.PNGAz egyik sorban több paraguayi régiót láthatunk felsorolva az adott régió lakosságszámával egyetemben, legalul pedig a chilei V. régió lélekszámát láthatjuk - a célunk pedig az lesz, hogy akár egy bar charton, akár egy column diagramon egy nyíllal mutassuk meg, hogy az összes paraguayi régiót figyelembevéve hol helyezkedik el a chilei V. régió, már ami a lakosok számát illeti.

Ennek érdekében tehát beszúrtunk még egy sort a kis adattáblánkba, amelyek kalkulált értékeket tartalmaznak. A nyíl kezdő azt jelzi, hogy hol lesz a nyílunk bal oldali széle, a záró azt jelzi, hogy lesz a jobb oldali széle és látható is, hogy ha elosztjuk a záróértéket kettővel, az lesz a nyíl közepe, az pedig pontosan az V. régió lélekszáma.

Ezután az Insert ribbonfül Charts szekciójának Column/Bar diagramos ikonja segítségével szúrjunk be egy bar chartot a paraguayi régiós lakosságszám és a nyíl kezdő/záró értékek sorainak kijelölése után:

masodik.PNGAhogy látható, az első sorban különféle színekkel ábrázolva ott vannak a paraguayi régiók, a második sor pedig a nyílunk kezdő értékét és záró értékét mutatja:

masodikesfel.PNGTehát ha beszúrtuk, a fenti kép szerinti eredménnyel, akkor még mindig az Insert ribbonfül alatt maradva, az Illustrations szekció Shapes menüjéből válasszunk egy nekünk szükséges ikont (én nyilat választok), majd szúrjuk be:

harmadik.PNGEzután a nyílon állva formázzuk meg, ahogy szeretnénk, majd nyomjunk egy CTRL+C-t - ezután pedig jelöljük ki a chartunk első sorában a kisebbik részt (tehát a nyíl kezdő és záró értéke közötti kis kockát és nyomjunk egy CTRL+V-t. Ezt fogjuk kapni:

negyedik.PNGÉs ezzel valószínűleg már mindenki látja, merre tartunk a történetben, hiszen a következő lépésként a második sorunk első szekcióját fogjuk megformázni (ez a nyíl előtti rész). Jelöljük ki, majd a Format ribbonfül Shape Styles szekciójából a Fill és Outline funkciók segítségével tüntessük el a kitöltést és a határokat is:

otodik.JPGMég egy lépésünk van hátra, kattintsunk valamelyik Series értékre (ezek a színes kis kockáink) jobb egérgombbal, majd a jobboldalt megjelenő Format Data Series menüben a Series Overlap értéket állítsuk 100%-ra, a Gap Width értéket pedig 0%-ra (tehát ne legyen a két barunk között semmiféle távolság):

hatodik.PNGEzzel pedig ezt kaptuk:

hetedik.PNGInnentől kezdve pedig már nincs túl sok feladatunk, szimplán úgy dolgozzuk ki a kalkulációkat a nyíl kezdő és záró értékére, hogy azok folyamatosan frissüljenek az adatainkból és így a nyíl is úgy fog mozogni, ahogy szeretnénk és ahogy szükséges.

Mintha autót vezetnénk: igenis számít a POWER

Folytatva tegnap elkezdett kalandozásunkat a PowerQuery világába, ma ismét egy olyan lehetőséget fogok megmutatni, ami egyáltalán nem bonyolult, megvalósítható Excel funkciók segítségével is, de a PowerQuerys megoldásnál nem nagyon látok egyszerűbbet egy óriási adathalmaz esetén. Adott a következő (leegyszerűsített) kis tábla:

y01.PNGAhogy látható, üres sorok és értékek összevisszaságából áll, a célunk pedig az értékek kinyerése lenne egyetlen oszlopba, figyelembe véve azt is, hogy egy sorban esetleg több érték is szerepelhet. Első lépésként a táblán állva lépjünk a Data ribbonfülre, majd a Get & Transform Data szekcióból válasszuk a From Table/Range opciót:

y02.PNG

Szépen be is töltődött minden a Query Editorba:

y03.PNGFogjuk és jelöljük ki az összes oszlopot, de legalábbis azokat mindenképpen, amelyeket az egyesítésben szeretnénk figyelembevenni (itt is simán működik a Shift vagy Ctrl segítségével történő kijelölés), majd a Transform ribbonfül alatt keressük meg a Text Column szekciót, azon belül is a Merge Columns funkciót:

y04.PNGA felugró ablakban kiválaszthatjuk az új oszlop nevét, majd azt is, hogy az egy sorban szereplő több érték esetén milyen elválasztót szeretnénk használni:

y05.PNGÉn most vesszővel fogom elválasztani az értékeket és az oszlop neve maradt a defaultként felkínált Merged. Ahogy látható, az OK gomb lenyomása után el is készült az új oszlopunk, ami vesszővel elválasztva tartalmazza a többi oszlop értékét. Kattintsunk erre az oszlopra jobb egérgombbal, majd a felugró menüből válasszuk a Split Column lehetőséget, azon belül pedig a By Delimitert:

y06.PNGUgyebár most azt akarjuk elérni, hogy az oszlopunkban lévő értékeket vesszők nélkül, külön-külön sorokban jelenítsük meg, így a felugró ablakban egyrészt adjuk meg fentebb a vessző delimiter-típust, lentebb pedig azt, hogy sorokba akarjuk rendezni a szétválasztás után az értékeket:

y07_1.PNGEzzel már majdnem kész is vagyunk, a query editor ugyanis létrehozta az új oszlopot, annyi problémával, hogy vannak még üres soraink is - ezeket egyszerűen szűrjük ki:

y08.PNGAz így kapott eredményt aztán a Home ribbonfül Close & Load funkciójával töltsük vissza Excelbe:

y09.PNGÉs meg is van, amit szerettünk volna:

y10.PNG

May the POWER be with you

Az Excel standard funkciói mellett már jó ideje elég hasznos, ha ismerjük a "Power-programok" lehetőségeit is, ugyanis ezekkel az Excel korlátait könnyedén átléphetjük és teljesen új opciók tárulnak fel - gondoljunk itt a PowerPivotra, a PowerQueryre vagy éppen a PowerBI-ra. A mai posztban egy nagyon egyszerű példán keresztül azt fogjuk megnézni, hogy a PowerQuery mennyire egyszerűvé tesz számunkra korábban INDEX-MATCH kombinációkkal vagy más trükközéssel megoldható Exceles feladatokat.

Adott a következő két tábla:

x1.PNGHangsúlyozom, roppant leegyszerűsített példát használunk, a célunk szimplán egy VLOOKUP végrehajtása lesz, de PowerQuery segítségével, amelyen keresztül látni fogjuk, hogy ha a PQ-t használjuk, akkor nem igazán számít, hogy balra vannak az eredményértékek a keresett értékünktől, nem számít az sem, hogy több oszlopból kell kulcsot képeznünk és az sem fog már minket zavarni, ha egy kulcshoz több találatunk lehet - a PowerQuery mindent megold. Szóval a mai példában az a célunk, hogy a második számú tábla értékeihez a Városrész kulcs alapján keressük be az első táblánk megfelelő sorait.

Első lépésként álljunk rá az egyik táblánkra, majd a Data fülünk Get & Transform szekciójában (Excel 2013-tól felfelé már Get & Transform néven fut a PowerQuery) kattintsunk a From Table/Range funkcióra:

x3.PNGEzzel be is töltöttük az adott táblát a Query editorba. Viszont mivel több táblánk van, a Query Editorban a Home ribbonfül alatt a Close & Load funkciócsoportból válasszuk ki a Close & Load To... opciót:

x4.PNGMajd válasszuk ki a következő táblánkat Only Create Connection és "Add this data to the Data Model" opciók kiválasztásával és kattintsunk az OK gombra:

x5.PNGLátható is, hogy jobb oldalt megjelent a Queries & Connections menü, benne a két táblánkkal és soraikkal:

x6.PNGHa valamelyikre duplán kattintunk, visszajutunk a Query Editorba. Itt álljunk rá arra a táblára, amihez akarunk értékeket bekeresni, majd a Home ribbonfül Combine szekciójából válasszuk ki a Merge Queries funkciót:

x8.PNGÉs most jön egy fontos lépés, a felugró Merge ablakban, az első részben ugyebár ott lesz az a táblánk, amihez értékeket akarunk keresni, a második részhez pedig válasszuk ki azt a a táblát, amelyik tartalmazza a bekeresendő értékeket - Join típusként (Join Kind) pedig válasszuk ki a Left Outer típust:

x9.PNGEzt fogjuk látni a Query Editorban - megjelent a két oszlopunk mellett egy harmadik, Table tartalommal:

x10.PNGÉs ha a Table oszlopnév melletti két kis nyílra kattintunk, azonnal kibővül a táblánk és látni fogjuk, hogy sikerrel behoztuk a két baloldali oszlopunkat (erre szolgált a Left Outer Join típus):

x11.PNGEzután ha bezártuk a Query Editort, azt láthatjuk, hogy a jobboldali Queries & Connections menüben a második táblánk sorainak száma megnövekedett - hiszen behoztuk hozzá az összes egyedi értéket. Kattintsunk rá jobb gombbal, majd a Context menüből válasszuk ki a Load To menüt:

x12.PNGÉs ha megadtuk, hogy hova akarjuk betölteni az eredményt, meg is vagyunk a feladattal:

x13.PNG

A példa faék egyszerűségű volt, de remélhetőleg látható volt belőle, hogy sokkal gyorsabban, sokkal hatékonyabban tudunk keresni és táblákat egyesíteni PowerQueryvel, mintha szimplán függvényekkel próbáltunk volna hegeszteni.

Power BI slicer és hozzácsatolása kizárólag bizonyos vizualizációkhoz

A mai kis irományban nem egy probléma megoldását fogjuk feszegetni, inkább tanácsot akarok adni azon felhasználóknak, akik még a Power BI alapokkal küzdenek, de szeretnének mielőbb egészen elfogadható felhasználói szintre jutni és bizonyos lépcsőfokokon felmerülnek kérdések.

Adott tehát egy nevesített táblánk Excelben, amit szépen be is importálunk Power BI-ba:

capture1.PNGAhogy látható, ez egy roppantul leegyszerűsített adathalmaz, paraguayi városok és régiók lélekszámát tartalmazza több évre vonatkozóan. Szóval ha megvan az adatunk a BI-ban, szépen vizualizáljuk azokat a számokat, amelyeket a céljaink érdekében szeretnénk megmutatni - hangsúlyozom, eléggé leegyszerűsített példáról beszélünk és a vizualizációval sem ragadtattam el magam:

capture2.PNGAmit most meg fogunk csinálni az egy Slicer, amivel azt akarjuk elérni, hogy a felhasználónk kiválaszthassa azt, hogy milyen adatokat akar megjeleníteni a chartokon, de azok közül is csak néhányon. A Slicert magát a Visualizations alatt találjuk az egyik jobboldali menüsoron:

capture3.PNGHa beszúrjuk és hozzáadjuk a Régiót, mint Fieldet, a következőt látjuk:

capture4.PNGTehát ezen ha mondjuk Asunción régióra kattintunk, akkor az összes chartunk és vizualizációnk szűrni fog Asunciónra. Még mielőtt azonban továbblépünk, kicsit formázzuk meg a Slicert, hogy ne lógjon ki annyira a dashboardból.

A jobb oldali Visualizations menüsorban kattintsunk át a kis Teddy-henger ikonra és első lépésként a General szekcióban állítsuk át az Orientationt Horizontalra - valahogy számomra ez sokkal átláthatóbb megjelenítést mutat:

capture5.PNGAdjunk a Slicernek nevet a Title szekció alatt, adjunk hozzá Select All lehetőséget, rendezgessük kicsit a színeket és nagyjából elérünk egy ilyen helyzethez:

capture6.PNGTehát akár kész is lehetnénk, viszont beleütközünk egy problémába: ha bármelyik régióra kattintunk a slicerben, akkor az összes chartunk reagálni fog, valahogy így:

capture7.PNGEzt meg az esetek 99%-ában nem nagyon szeretnénk, úgyhogy kattintsunk a Format ribbonfül alatt az Interactions szekcióban az Edit Interactions funkcióra (fontos, hogy végig a Slicer legyen az aktív elemünk a munkalapon):

capture8.PNGLátható a képen is, hogy a funkció aktiválásával megjelent egy apró szűrő-ikon és egy stop-ikon dashboardunk összes eleme felett. Szépen kattintgassunk a stop-ikonra azon elemek felett, amelyeket nem szeretnénk összekötni a Slicerrel és máris rendeztük a kapcsolódási pontokat. És most már látható is a Slicer eredménye, ha bármelyik régióra kattintunk:

capture9.PNGÉn a két baloldali charton kapcsoltam ki az interakciót, így teljesen érthető módon csak a Városok lélekszáma chartom frissült a Slicerrel.

Örökmozgó szövegdoboz - ötletelés objektumok automatikus mozgatásáról

Adott egy rendkívül nagy adathalmazunk egy-két-három oszloppal és több tízezer sorral, amelyekből készül egy aprócska kalkulációs táblázatunk a nyers adatok mellé helyezve. Bárhova is görgetünk az adathalmazunkban, szeretnénk, ha ez a kis kalkulációs tábla végig velünk maradna - tehát kövesse a görgetésünket. Ez lenne a mai feladvány, kezdve az adathalmazzal:

tabla1.JPGEz meg a kis kalkulációs táblánk lenne, szimplán cellákba betöltve:

tabla2.JPGÉs valami ilyesmi eredményt akarunk elérni:

tabla3.JPGLátható, hogy az alapkalkulációkat egyszerűen csak beletettem egy szövegdobozba - azt akarjuk, hogy ez a szövegdoboz végig jöjjön velünk, ahogy görgetünk fel- vagy lefelé az adatok között. Azt már most kijelenthetjük gyorsan, hogy a szövegdoboz nem kezelhető megoldás erre a problémára, így szimplán jelöljük ki az eredeti kalkulációs táblázatunkat, majd nyomjunk egy CTRL+C-t és tegyük a vágólapra.

Ezután jobb egérgombbal kattintva hívjuk elő a context menünket és illesszük be a vágólapról a kalkulációt képként:

tabla4.JPGValahogy így fog majd kinézni:

tabla5.JPGAzt mindenképpen tegyük meg, hogy a képen állva a Formula Barban egy egyenlőségjel után adjuk meg azt a tartományt, amiből a képet készítettük, így automatikus frissülni fognak az értékeink a képen is:

tabla6.JPGUserform készítése nem az első helyen szerepel a megoldási javaslataim között, hiszen az már formátumát tekintve sem hasonlít igazán az elvárt eredményre, viszont javaslatként kipróbálhatjuk a legegyszerűbben megadható választ, a View ribbonfül Window szekciójából a Freeze Panes funkciót:

tabla9.JPGAhogy látható, ezzel rögzítettük szépen a képünket és bárhova is görgetünk, fixen látható lesz a sheetünk tetején, viszont ezzel meg az a probléma, hogy a felső néhány sor az adathalmazunkban is rögzült. Tehát megoldásnak megoldás, de nem tökéletes.

Egy fokkal jobb megoldás a munkalapon való kijelölés-változtatás eseményhez (SelectionChange) hozzárendelni egy aprócska VBA-kódot, amivel azt fogjuk elérni, hogy bármilyen kijelölés-változtatás esetén a kis képünk odaugrik, ahova akarjuk. Lépjünk tehát ALT+F11 lenyomásával át a VBA-editorba, majd kezdjük el szépen hozzárendelni a Worksheet_SelectionChange eseményhez a következő kódunkat.

Változókkal kezdünk, mint mindig, szükségünk van egy Object típusú változóra a képünknek, amit mozgatni fogunk, aztán kell kettő, mondjuk Double típusú változó a felfelé és a balra mozgatáshoz megadandó koordinátáknak, végül pedig még egy változót célszerű felvenni, ami mindig a legalsó sorunk koordinátáit tartalmazza.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim Kep As Object
Dim Fent As Double
Dim Balra As Double
Dim Alsosor As Range

Ha ezzel megvagyunk, az aktív munkalapunk látható részét fogjuk (ActiveWindow.VisibleRange), majd egy r nevű változóba beszámoljuk, hogy hány sor van éppen a képernyőn, majd egy c nevű változóba azt is beszámoljuk, hány oszlop látható, majd r és c változók fogják megmondani, hogy milyen cellakoordinátákat fog felvenni "Alsosor" nevű változónk, azaz pontosan melyik cella a legalsó és egyben legszélső cellánk a látható képernyőn.

With ActiveWindow.VisibleRange
r = .Rows.Count
c = .Columns.Count
Set Alsosor = .Cells(r, c)
End With

Tehát megvannak a koordináták, már csak meg kell mozdítanunk a képet minden egyes kijelölés-változtatásnál. Szépen adjuk meg, hogy melyik képről is beszélünk, az én esetemben ez az aktív munkalap első számú képe:

Set Kep = ActiveSheet.Pictures(1)

Azt elvileg tudjuk, hogy "Alsosor" nevű változónk Range típusú, és a tartományoknak van egy .Top nevű tulajdonsága, amely megadja az első sor teteje és a tartományunk teteje közötti távolságot. Ennek tükrében már értelmet is nyer a következő definiálás:

Fent = Alsosor.Top - Kep.Height - 120

Tehát fogjuk a látható képernyőnk legalsó sorát és kiszámoljuk az első sor és az utolsó sor közötti távolságot, amiből kivonjuk a kép magasságát és még 120 képpontot (mert nagyjából kikísérleteztem, hogy ennyi képpont kell még, hogy a kalkuláció oda kerüljön, ahova akarom), azaz 120 képponttal és a kép magasságával csökkentett értéket fogja felvenni a "Fent" nevű változó. Ugyanezen logika mellett működik a Range.Left tulajdonság is, csak ez a legelső oszloptól számol a tartományunk első oszlopáig:

Balra = Alsosor.Left - Kep.Width - 300

Ezután már nincs más hátra, mint a képünk koordinátáit a fenti két, "Fent" és "Balra" nevű változóból kinyerni, minden kijelölés-változtatásnál:

With Kep
.Top = Fent
.Left = Balra
End With
End Sub

És ezzel szépen jön velünk a kis kép, ahogy mozgunk és kattintunk a sheetünkön. Viszont ez még mindig csak két, nem teljesen az elvárásnak megfelelő megoldási javaslat volt - kedves Olvasók, bármilyen más ötlet?