A népszerű Waterfall/Bridge-diagram alapvetései

2017. április 22. - Office Guru

A Waterfall-chartot először a Mckinsey & Company tanácsadó cégének konzultánsai használták prezentációkban és elemzésekben, aztán rövidesen rendkívül elterjedtté vált a világon, boldog-boldogtalan ilyen jellegű diagramokkal mutatta meg eredményeinek alakulását - kezdetben ráadásul ez azért munkás is volt egy kicsit, hiszen például az Excel vagy a PowerPoint alapból nem is tudta ezt a diagramtípust. Egy Stacked Column Chartból lehetett általában kisebb módosításokkal elkészíteni, de aztán a 2016-os Office verzió már magával hozta azt, amiért sokan kérlelték a Microsoftot: az Excel és a PowerPoint is tudta kezelni ezt a típusú megjelenítést. Miután ennek a chartnak az ismerete a mai napig alapvetés bizonyos típusú elemzésekben, bemutatóknál, így következzen néhány lépésben a "vízesés" (bridge chartként is ismert) chart elkészítése. Adott a következő roppant egyszerű kis tábla:

stacked1.bmpEgyértelműen látszik, hogy mi a Waterfall-chart kiindulópontja és végeredménye: mindenféle értékek/eredmények alakulását mutatja meg a módosító tényezőkkel együtt.

Ha kijelöljük ezt a táblázatot teljes egészében, majd az Insert ribbonfül Charts szekciójában a Recommended Charts funkcióra kattintunk, akkor látjuk is, hogy az Excel már alapból odateszi nekünk ajánlott diagramtípusként a "vízesést":

stacked2.bmpÚgyhogy szúrjuk is szépen be ezt a diagramot és máris látjuk az alaptípusú Waterfallt:

stacked3.bmpAztán egyszerűen meg is foghatjuk ezt és átrakhatjuk egy PowerPoint diára, beillesztve szépen prezentációnk folyamába:

stacked4.bmpDe persze arra is van mód, ahogy fentebb már említettem, hogy PowerPointból készítsük el ezt a diagramot. Ehhez a prezentációs program Insert ribbonfülének Illustrations szekciójából szúrjunk egy Waterfall chartot:

stacked5.bmpÉs ezt egy automatikusan megnyíló Excel-ablakban szépen editálhatjuk is:

stacked6.bmpAnnyi fontos még, hogy a klasszikus nézet eléréséhez még egy lépést meg kell tennünk. Az eredményoszlopunk tömbjére kattintva jobb gombbal nyissuk meg a Format Data Point menüpontot, majd a Set as Total checkboxot pipáljuk be, ettől válik ugyanis igazi "honnan-hová" diagrammá a "vízesésünk".

stacked10.jpgEz volt az alapvető tudnivaló, amit mindenképpen fontos ismerni adatelemzéseknél, adatok bemutatásánál, de azért információként tároljuk el, hogy nem csak ilyen típusú "vízesést" tudunk készíteni, van ennek ugyanis többek között:

- fordított verziója, amikor nem oszlopokban, hanem sorokban jelenítjük meg az érintett volumeneket, tehát a legfelső sor a kiindulóállapot, a legalsó pedig az eredmény

- stacked verziója, amelyben minden egyes megjelenített tömböt még tovább lehet tagolni, tehát mintha a fenti példa esetében az adót tovább lehetne még bontani

- dupla verziója, amelyben megadhatunk egy terv- és egy tényértéket is, az egyiket mondjuk kitöltött tömbként, a másikat pedig csak körvonalként

- megosztott verziója, amikor a kiinduló tömböt megoszthatjuk és a megosztott részek alakulását követhetjük végig külön-külön megjelenített tényezőkkel (mintha a fenti példánál az Adó már a 2016-os záró értékben ki lett volna emelve és egy külön "bridge" segítségével az adó alakulását is végigkövetnénk)

Szóval van bőven lehetőség és kraft ebben a diagramtípusban, ne féljünk tőle, használjuk, tuningoljuk és színezzük bátran, biztosan sikert aratunk vele.

Az elfeledett Excel-funkciók nagykönyve - 1. A Consolidate

Rövidebb-hosszabb kutakodásaim és mindennapi tapasztalataim egyértelműen azt mutatják, hogy az Excelben bőven vannak úgymond "elfeledett" funkciók és gombok, amelyek nem sűrűn kerülnek használatra a napi feladatok során - nem azért, mert nem hasznosak, hanem egyszerűen nem épültek be úgy a köztudatba, mint egy VLOOKUP, egy PIVOT vagy egy Text to Columns.

Szerintem - és ezzel biztos lesznek, akik vitatkozni fognak - ilyen funkciónak nevezhetjük a a Ribbonunk Data füle alatt, a Data Tools szekcióban elérhető Consolidate funkciót is, pedig elég sok feladat elvégzésében lehetne segítségünkre.

apertura2.jpgIlyen feladat például egy egyszerű összehasonlítás két tábla között, amelyre használhatunk mindenféle praktikákat, mondjuk egy VLOOKUP függvényt oda-vissza és így tovább, pedig a Consolidate funkció is segíthet ebben. Adott a következő mintatábla:

apertura1.jpgAzt szeretnénk megtudni, hogy az 1967-es és a 2010-es chilei labdarúgó-bajnokság Apertura szezonjának csapatai közül melyek szerepeltek mindkét esztendőben. Néhány apró kattintással összesíthetjük is ezt az elemzést, amelyhez elsőként gyorsan megduplikáljuk a két oszlopot valahogy így:

apertura3.jpgRákattintunk a Consolidate funkcióra, majd a Function legördülő menüjét Countra állítva, hozzáadjuk a két hivatkozásunkat - az egyik értelemszerűen a két 1967-es oszlop lesz, a másik pedig a két 2010-es oszlop:

apertura4.jpgAnnak érdekében, hogy megfelelő összesítő táblát kapjunk, a Top row és a Left column Labels checkboxokat is pipáljuk be. Fontos tudni, hogy ezt elvégezhetjük nem csak ugyanazon a sheeten, de akár különböző munkafüzetekben is.

Nyomunk egy okét és máris kész az elemzésünk a várt eredménnyel:

apertura5.jpgDe ez csak egy volt a Consolidate sok lehetősége közül, például szummarizálni is hasonlóan egyszerűen tudunk vele. Adott mondjuk két sheeten két kis tábla adott havi bevételadatokkal (és akár lehet ez különböző Excel-fájlokban is):

apertura7.jpg

apertura8.jpgEzután a Consolidate gombra való kattintás után válasszuk ki a Sum funkciót, hivatkozásként jelöljük meg a két táblánkat, majd kattintsunk az okéra és kész is vagyunk:

apertura9.jpgapertura10.jpgVegyük mindenképpen észre, hogy a Create links to source data checkbox bekapcsolásával dinamikusan változhatnak értékeink a forrásadat változása alapján. És a fentiekkel a Consolidate funkciónak csak éppen a felszínét kapargattuk - mindenki bátran próbálkozzon vele, határozottan segítség a mindennapi feladatokban.

PowerPoint: egy érdekesség és egy furcsaság az elbow connectorról

A most következő posztban ismét visszatérünk a PowerPointhoz, ugyanis az Office prezentációs célokat szolgáló moduljának elbow connector névre hallgató formájával fogok szórakozni - bemutatok egy kis ügyeskedést illetve körüljárunk egy sokak által programhibának tartott viselkedést.

Az elbow connector (maradni fogok valószínűleg végig az angolnál) formáit legtöbbször folyamatábráknál, döntési fáknál szokták felhasználni, de ezen felhasználási módok során vannak korlátok, amelyek időnként fejtörést okozhatnak - ezek közül az egyik például az, amikor azt szeretnénk elérni, hogy a szakaszunknak ne csak az alapértelmezett egy, de két, vagy akár több "könyöke" legyen.

Nézzük meg egy ilyen, több könyökkel bíró elbow connector elkészítési folyamatát! Első lépésként a diánkra szúrjunk be egy téglalap-formát az Insert ribbonfül Illustrations szekciójában található Shapes menü alól:

bug1.jpgA kitöltést távolítsuk el azonnal (No Fill a Format ribbonfül alatt), majd jobb gombbal kattintva a formára, válasszuk az Edit Points menüpontot:

bug7.jpgEzután szépen jelöljük ki a téglalap "felső" oldalát:

bug8.jpgÚjra jobb gombbal történő kattintás után a Delete Segment funkció segítségével távolítsuk el ezt a "felső" oldalt:

bug9.jpgAlapesetben az egyik oldal el fog görbülni kicsit a törlés után:

bug10_1.jpgJobb gombbal való kattintás után az Edit Points menüpont segítségével irányba tudjuk mozdítani ezt az oldalt, hogy ebbe a helyzetbe kerüljünk:

bug11.jpgEzután visszatérünk az Insert ribbonfülhöz, amelynek Illustrations szekciójából megint beszúrunk egy formát, ezúttal egy nyilat:

bug12.jpgSzúrjunk be ezután majd még egy ugyanilyen nyílat és mindkettőt passzoljuk hozzá a "felső" oldal nélküli téglalap-formánk szabadon álló végeihez, valahogy így:

bug13.jpgMajd mindhárom formát (két nyíl és egy téglalap) jelöljük ki a CTRL nyomvatartása mellett és nyomjunk jobb gombot az egerünkön, hogy lehetőségünk legyen a Group paranccsal egyesíteni/csoportosítani őket. És innentől kezdve kész a két könyökkel bíró elbow connector, ami tetszőleges forgatható, tágítható, kicsinyíthető stb.

bug14.jpgUgyanennek az elbow connectornak egyébként van egy olyan tulajdonsága/viselkedése, amely sokak szerint a PowerPoint bugjainak egyike, bár jómagam inkább elvárt viselkedésnek tartom ezt, mint hibának. Hogy miről van szó? Nézzük meg!

Szúrjunk be az Insert ribbonfül alól két darab téglalap-formát:

bug1_1.jpg

bug2.jpgFontos, hogy elhelyezkedésük teljesen ugyanolyan legyen, tehát középpontjaik egy egyenesen helyezkedjenek el.

Ezután szúrjunk be egy elbow connectort szintén a Shapes menüpontból:

bug3.jpgEzzel az elbow connectorral aztán próbáljuk meg a felső téglalapunk "alsó" oldalának középpontját összekötni az alsó téglalapunk "felső" oldalának középpontjával. Hiába állítottuk pontosan középre mindkét formát, az elbow connector mégis megtörik középen, ott, ahol elvileg egyenesnek kellene lennie, hiszen a középpontok pontosan ugyanazon az egyenesen helyezkednek el.

bug4.jpgSúlyos problémáról nem beszélünk, hiszen könnyen javítható, elég hozzá jobb gombbal való kattintás után a Size and Position menübe lépni:

bug5.jpgMajd a Height értékét nullára korrigálni:

bug6.jpgA kérdés viszont marad: ez egy hiba vagy sem? Az elbow connector alapvetően nem egyenes nyíl, tehát a megtörés (a "könyök") elvárt reakció, de két egymásra teljesen illeszkedő középpont esetén ez valahogy mégsem áll össze teljesen egyértelműen.

Zenelejátszás Excelből - Jokermegoldás programfuttatáshoz

Ha már hétvége és napsütés (kissé masszívabb szélrohamokkal), akkor itt a blog hasábjain is vegyük kicsit szórakoztatóbbra az irányt, így a mai posztban ennek megfelelően (a korábbi Google-keresős vagy YouTube-lejátszós írások után) Excelből történő zenelejátszás kerül röviden terítékre. Magát a műveletet egy VBA-kódsorral fogjuk elvégezni, de elsőként hajtsuk végre a megfelelő előkészületeket, kezdjük mindjárt a playlistünkkel, amelyből választhat majd a kedves user.

play1.jpgCsinálhatjuk ezt kézzel, csinálhatjuk makróval, de csinálhatjuk command promptból is, a lényeg, hogy minél egyszerűbben készítsük el a playlistet (ez lehet egy könyvtárban lévő összes fájl listázása stb.).

Folytatásként csináljunk valamiféle kis designt a lejátszónknak, majd adjunk hozzá egy Data Validation Listet a fájlokból való választás lehetőségének létrehozása okán:

play3.jpg
Itt most nem töltöttem azzal időt, hogy a meghajtó (könyvtár) nevét eltávolítsam, de értelemszerűen még tovább lehet csiszolni ezen a listán.

Aztán a kis Play gombunkhoz (amelyet akár elkészíthetünk saját magunk is néhány alakzat felhasználásával) rendeljük hozzá a makrónkat a jobb gombbal való kattintással előhívható Context menü Assign Macro utasításával:

play2.jpg
Ezután már nincs más hátra, mint a VBA-kódunk megalkotása, amelyhez ALT+F11 lenyomásával ballagjunk át szépen a VBA-editorba. A kód hosszúságát tekintve roppant egyszerű, úgyhogy be is illesztem ide egy az egyben:

Sub MP3player()
ActiveSheet.OLEObjects.Add(Filename:=ActiveCell.Text, Link:=True).Select
Selection.Verb
Selection.Delete
End Sub

Azt ugye már korábbi posztokból megtanultuk, hogy az OLEObjects általában egy Active-X controlt, egy linkelt vagy beágyazott objektumot testesít meg, zárójelben mögötte nevesíthetjük is, hogy éppen melyiket. Az OLEObjects Verb metódusával pedig igazából műveletet tudunk végezni ezzel az objektummal, elég sok lehetőségünk van, amelyeket azonban az adott objektum forrásprogramja is meghatároz. Amennyiben nem adjuk meg a Verb paraméterét, akkor a default műveletet, a szerkesztést, lejátszást stb. fogja végrehajtani, amire pont szükségünk is van jelen esetben.

Azaz kódunk első sorában az aktív sheetünkön létrehozunk egy objektumot, amelynek FileName paramétere (tehát neve) az aktív cellánkban szereplő érték lesz (tehát a kiválasztott mp3 fájl teljes útvonala), a Link paramétert pedig azért kell True értékre állítanunk, hogy ez az új OLE objektum a FileName paraméterben megadott fájlhoz linkelésre is kerüljön. Ezt utána értelemszerűen kiválasztjuk, majd megy rá a Verb metódus, amiről fentebb illetve korábbi posztokban már írtam. Ha ez megtörtént, akkor töröljük az objektumot a kódunk harmadik sorával.

És innentől kezdve már működik is a dolog, ha kiválasztjuk a listából a megfelelő számot és a Play gombra kattintunk, már indul is az alapértelmezett lejátszóval a zeneszámunk. Értelemszerűen hibakezelésre még szükség van a kódban, ahogy az Application.ScreenUpdating babrálása is jól jöhet, de a lelke ez a három sor az egész műveletnek. És ami a legjobb az egészben, az az, hogy ez nem zenére működik csak, bármilyen fájlokkal meg tudjuk csinálni ugyanezt, azoknál kiválasztás esetén értelemszerűen az alapértelmezetten hozzárendelt program indul - txt-nél pl. a NotePad és így tovább.

Hasznos Excel-dashboard alkatrész: a hőmérős diagram

Ha az ember elég sok prezentációt, dashboardot készít a mindennapokban vagy készített elég sokat a múltban, akkor igyekezni kell mindig megújulni és újabb kreatívabb megjelenítési formákat bevetni, különben egy idő után már csak magunkat ismételjük. A következő posztban egy ilyen megjelenítési forma kerül bemutatásra, amely bár elég népszerű volt már a múltban is mindenféle minőségben és formában, eme poszt szerzője nem is túlságosan régen készítette el az első ilyet saját maga - remélhetőleg lesz olyan, akinek ez most szintén segítség lesz.

A célunk, hogy Excelben készítsünk egy hőmérős diagramot, amelynél értelemszerűen a legfontosabb első lépésünk az, hogy meghatározzuk, tényleg erre van-e szükségünk, hiszen egy ilyen hőmérő alapesetben elég korlátozott témakörben használható - főként jelenlegi állapot és célérték összehasonlítására.

Ha eldöntöttük, hogy tényleg ilyesmit akarunk, akkor kezdjünk is neki! Adott tehát az adathalmazunk.

thermo1.jpgÉrtelemszerűen figyeljünk oda arra, hogy aktuális érték és célérték is álljon rendelkezésre, különben nem lesz könnyű megvalósítani a hőmérőt és nincs is nagyon értelme. Jelöljük ki a két értéket (aktuális és cél), majd az Insert ribbonfül Charts szekciójából szúrjunk be egy kétdimenziós, Clustered Chartot:

thermo2.jpgAz ezután megjelenő Design ribbonfül Data szekciójában nyomjunk rá a Switch Row/Column funkcióra:

thermo3.jpgAz így megjelent diagramban, a célértéket megjelenítő oszlopra kattintsunk jobb gombbal, majd Format Data Series:

thermo4.jpgItt a Plot Series On menüpont alatt kattintsuk át a rádiógombot Secondary Axisra:

thermo5.jpgA bal- illetve jobb oldalon lévő tengelyfeliratokat a jobb gombbal történő kattintás után előhívható Axis Options menüből módosítanunk kell úgy, hogy a léptékek illetve a maximumérték ugyanannyi legyen:

thermo6.jpgEzután a most már egymást teljesen fedő oszlopokra kattintsunk, majd formázzuk meg a nagyobbikat (a célértéket), méghozzá úgy, hogy a Format ribbonfül alatt töröljük a kitöltést (Shape Fill - No Fill) illetve adjunk neki egy erősebb körvonalat:

thermo7.jpgEzzel még nem ér véget a formázgatás, most kell például törölnünk a jobb oldali tengelyfeliratokat, a címet, a gridline-okat illetve most kell átméreteznünk a diagramot is úgy, hogy nagyjából hőmérőre emlékeztető vastagságot és formát kapjunk:

thermo8.jpgA bal oldali axisra jobb gombbal kattintva előhívjuk megint az Axis Options-t, majd a Tick Marks alatt a hőmérőre hasonlítás érdekében még tegyünk be beosztást jelölő "vonalkákat" is:

thermo9.jpgHa felmerülne valakiben olvasás közben a kérdés, hogy mégis hogyan lehet átállítani a "folyadék" színét, akkor ez a kép remélhetőleg segítség lesz:

thermo10.jpgA Format ribbonfül Current Selection szekciójából kell kiválasztanunk a Series 2 objektumot, ha a "folyadékot" akarjuk babrálni. Visszatérve a diagramunkhoz, még egy lépés van hátra, méghozzá az, hogy a hőmérőnk alját is elkészítsük. Lehet elkeskenyedő hegyes vége, mint egy lázmérőnek, de lehet gömbölyű is, döntsük el, de például egy gömbölyű részhez az Insert ribbonfül Illustrations szekciójából kell beszúrnunk egy ovális formát:

thermo11.jpgFormázzuk meg majd illesszük hozzá az eddigiekhez és kész is vagyunk:

thermo12.jpg

Fókuszálás és elhomályosítás PowerPoint prezentációnk diáin

Egy jó, figyelemfelkeltő, ütős prezentáció összeállítása mindig időigényes és kreatív folyamat vagy legalábbis annak kell lennie, különben beleeshetünk jónéhány prezentációs csapdába, amelyek után az összhatás nem igazán az lesz, amit szeretnénk. Úgyhogy folytatva a korábbi hasonló Powerpointos posztok hagyományait, a mai írásban is egy aprócska technikát fogok bemutatni, azzal a célzattal, hogy ez és az ehhez hasonló technikák összegyúrásával mindenki tényleg össze tudjon hozni egy hatásos prezentációt, ha szüksége lesz rá.

A mai poszt célja egy olyan animációs módszer bemutatása roppant egyszerűen és röviden, amelynek során a diánkon lévő kép bizonyos részletére tudunk fókuszálni, úgy, hogy közben a háttér szépen elhalványul. A jobb megértés érdekében álljon itt egy kép:

prez1.jpgA térképen majd Nuku'alofát, Tonga fővárosát fogjuk élesen hagyni, míg a térkép többi része elhalványul, aztán majd a fővárosról dobálunk fel hasznos információkat az elhalványult térképrészleteken.

Először is az Insert ribbonfül Illustrations szekciójából a Shapes menüből szúrjunk be egy téglalapot és pontosan helyezzük a térképünkre:

prez2.jpg

prez3.jpgAhogy látható is, defaultban piros a kitöltés, tehát nem is látjuk most a térképet. Ezután ezt a pirossal kitöltött formát mozgassuk kissé le a térképről, pont annyira, hogy azért lássuk már a fővárost a térképen. Ezután ismét a Shapes menüből szúrjunk be ezúttal egy ovális formát:

prez4.jpgMajd a formát a következő képen látottaknak megfelelően helyezzük a fővárosra, értelemszerűen ezúttal kitöltés nélkül (No Fill).

prez5.jpgHa visszahúzzuk az eredeti téglalap-formát a térképünkre, akkor valami ilyesmit fogunk látni:

prez6.jpgTehát már látjuk a kört, nagyjából Nuku'alofa magasságában. Ezután két dolgot kell megtennünk: egyrészt CTRL lenyomásával kattintsunk a körünkre és a téglalapunkra is egyszerre, majd a Format Ribbonfül alatt található Insert Shapes szekcióból a Merge Shapes gombra kattintva egyesítsük ezt a két formát (Combine opció).

prez7.jpgMajd erre az egyesített formára jobb gombbal kattintva válasszuk az Outline menüt, majd távolítsuk el a keretet:

prez8.jpgUgyanitt a Fill menüből színezzük fehérre a formát. Ezt kapjuk:

prez9.jpgEzután ha jobb gombbal ismételten az egyesített formánkra kattintunk, akkor a legördülő menüből válasszuk a Format Shape menüpontot, majd az oldalt felbukkanó opcióablakban a Transparency értékét állítsuk mondjuk 20% körülire:

prez10.jpgEzután már csak arra van szükségünk, hogy ugyanezen egyesített formát kijelölve, az Animations ribbonfül alatt szúrjunk be erre a formára egy Fade stílusú animációt:

prez11.jpgMajd az esetlegesen az elhomályosítás, kiélesítés után megjelenő extra szövegdobozunkra is tegyünk rá egy Fade típusú animációt:

prez12.jpgÉs ha most elindítjuk az animációt, már láthatjuk is, hogy az alapból megjelenő sima térkép előbb elhomályosul, élesen hagyva a Nuku'alofa körüli kört, majd ezután megjelenik a szövegdobozunk is a hasznosnak vélt információkkal:

prez13_1.jpg

Persze itt még nyugodtan változtathatunk rengeteg dolgon, például az animációt elindító eseményen (kattintás stb.), az animáció sebességén és így tovább, de remélhetőleg ez az egyszerű kis leírás segítségül fog szolgálni egyik-másik Olvasónak.

Variációk egy témára: dinamikus tartományok és automatikusan bővülő formulák

Az egyik kedves Olvasó vetett fel egy kérdést nemrégiben egy, valószínűleg mindennapos Exceles formula problémával kapcsolatban, úgyhogy a következő posztban ezen kérdéskör körül fogok ötletelni.

A példa a következő kis táblán egyszerűen bemutatható:

sumext1.jpg

sumext2.jpgHogy érjük el azt, hogy a H4-es cellában lévő összegzés mindig magától frissüljön, ha a C-D oszlopokban található táblázatunkhoz új sorokat adunk hozzá?

A legegyszerűbb és legkönnyebben megadható válasz az Excel automatikus "hivatkozás-bővítés" funkciója, azaz ha bárhova beszúrunk egy üres sort a táblába az első és az utolsó érték közé, akkor a függvény automatikus frissülni fog és beleveszi a frissen beszúrt sorunk értékét is:

sumext3.jpgÉrtelemszerűen ez okozhat problémákat, ha rengeteg oszlopunk van vagy ha minden sorunk mellett van egy beképletezett másik cella is, de például ha az összegzésünk egy másik sheeten található és a táblánk is egyszerű, akkor ennél nincs jobb megoldás (mondjuk ez nem is megoldás, hanem maga a program nyújtotta automatizmus). Viszont ha mindig az utolsó sor után adunk hozzá egy újat (mondjuk napokat), akkor ez nem működő opció.

Erre lehetőségünk az, hogy a formulánkban nem a pontos tartományt, hanem akár az egész oszlopot meghivatkozzuk:

sumext4.jpgDe hivatkozhatunk egy elég nagy tartományt is, mondjuk D10:D100000, ha az lefedi jó előre a hozzáadandó celláinkat.

Kezelhetőség szempontjából biztosan nem a legegyszerűbb megoldás, de biztosan működő ötlet a táblafunkció használata, ha extra sor akár hozzáadásáról akár beszúrásáról van szó. A táblázatunk kijelölése után az Insert ribbonfül Tables szekciójának Table opcióját választva szúrjunk be egy táblát (figyelve arra, hogy van-e már fejlécünk vagy nincs):

sumext5.jpgInnentől kezdve pedig akár a sorok közé, akár a sorok után szúrunk be új adatot, a tábla automatikusan frissülni fog:

sumext6.jpgViszont legnagyobb valószínűség szerint a fentiekhez képest jóval kényelmesebb és stabilabb megoldást kínálnak a dinamikus tartományok, amely megoldásokban az a közös, hogy egytől-egyig mindet a Formulas ribbonfül Defined names szekciójában fogjuk megalkotni a Define Name menüpont segítségével:

sumext7.jpgA következőkben néhány ilyen nevesített tartományhoz rendelt formulát fogunk megnézni, amelyek mindegyike segíteni fog a fent felvetett kérdés megoldásában.

Az elsőben a Range-ként elnevezett tartományunk Refers to sorába a következő formulát írjuk:

=OFFSET(Sheet1!$D$4,0,0,COUNT(Sheet1!$D:$D),1)

A korábbi posztokban mindkét függvényt megismertük már, de alapvetően ez a formula azt teszi, hogy táblánk első számértékétől kezdve (ez a D4-es abszolút hivatkozás) visszaad egy olyan celltartományt, amely ettől a D4-es cellától annyi cellával lefelé nyúlik, ahány számértékünk van a D oszlopban (ez lesz a COUNT függvény eredménye, jelen esetben hét).

Ezután pedig már szimplán elég ha a SUM függvényt erre a Range nevű nevesített tartományra húzzuk rá:

sumext8.jpgÉs akkor ennek az OFFSET-es nevesített tartományos hivatkozásnak használhatjuk jópár másik verzióját is, például ezt:

=OFFSET($D$4,0,0,MATCH(1E+306,$D:$D,1),1)

Ez valamelyest hasonló az előbbihez, azzal a változással, hogy a MATCH függvény alkalmazásával tartományunk mindig az utolsó számot tartalmazó celláig terjed majd.

Utána pedig értelemszerűen erre a nevesített tartományra kell az érintett alapfüggvényt elkészítenünk.

sumext9.jpg

Néhány gondolat a nevezetes 400-as hibaüzenetről

Alapvetően ha maguknak az Office programjainak a hibaüzenetei nem is igazán szoktak mindig sokat elárulni a hiba természetéről, addig alapesetben azért a Visual Basicben valamennyivel könnyebb dolgunk van, hiszen az esetek jelentős részében legalább egy általános üzenetet kapunk, amely üzenetablak Debug gombjának lenyomásával a problémás sort is be tudjuk azonosítani. Lásd például ezt a hírhedt Excel-hibaüzenetet:

error2.pngÉs ez pedig a másik oldal lenne, két VBA-hibaüzenettel:

error3.png

error4.jpgPersze itt most azért eléggé általánosítottam, hiszen akár fordított felállásban is tudtam volna hibaüzeneteket hozni, VBA esetében például mindenképpen a nehezen megfejthető hibaüzenetek listáján előkelő helyen szerepelne a hírhedt 400-as hiba:

error1.pngNem megfejthetetlen, az nem kérdés, de a megszokottól kicsit eltérően itt nem kapunk segítséget első körben, magunknak kell utánajárni a problémát okozó sornak. Leggyakrabban egyébként ilyet akkor láthatunk, ha a kódunkban valami olyasmire hivatkozunk, ami (már) nem létezik - például egy nem létező munkafüzetre vagy olyan változót akarunk mondjuk használni, amit nem deklaráltunk stb. (tehát nagyjából egy Application-defined or object-defined errornak feleltethető meg). Viszont a megoldás mindenképpen csak a manuális soronkénti ellenőrzés lehet, hiszen nem tudhatuk hol bújik meg a téves hivatkozás - bár azt azért ki tudjuk deríteni, hogy pontosan milyen hibánk van.

Ehhez nem kell mást tennünk, mint az adott kód változóinak deklarálása után beszúrni a következő sort:

On Error GoTo Errorcatch (vagy akár Errortrap, errorcheck és így tovább)

Majd az End Sub lezáró sor elé a következőt behelyezni:

Exit Sub
Errorcatch: MsgBox Err.Description

Így a 400-as hiba helyett valamivel több információt már be tudunk gyűjteni a problémáról egy felugró üzenetből és ha már tudjuk, hogy merrefelé keresgéljünk, akkor a beazonosítás is könnyebb lehet breakpointok beszúrásával vagy a sorok lépésenkénti futtatásával. De szívesen fogadok kommentben észrevételeket ennek a hibaüzenetnek a történetéről (az tény, hogy már a 97-es Excelben is elő tudtuk idézni), hiszen a fentiekben csak roppant általános jellemzőket foglaltam össze róla.

Könnyed ünnepi jótanács: a Spin Button használata

Minimális VBA-ismeret nélkül is rendkívül jól használható vizuális elem egy Excel-táblázatban a Spin Button, amelynek nyilaival szépen tudunk le- és felfelé mozogni egy listában és utána az aktuális értékkel mindenféle műveleteket is el tudunk végezni, anélkül, hogy a konkrét listát látnánk. Nézzünk meg erre egy roppant egyszerű kis példát!

Az Excel megfelelő sheetjén állva a Developer ribbonfül Controls szekciójából szúrjunk be egy Spin Buttont:

spin1.jpgMajd építsünk fel köré egy kis designt, amilyet éppen akarunk:

spin2.jpgAz én kis táblám csak a példa kedvéért négy magyar város és ezek lélekszáma lesz, tehát a nyilak segítségével a városok között tudunk majd váltogatni és ezen kiválasztott értékhez függvénnyel keressük majd be a lélekszámot. Értelemszerűen a segédtábla bárhol lehet az Excelben, most csak az egyszerűség kedvéért van a Spin Button közelében.

Kattinsunk jobb gombbal a Spin Buttonra, majd válasszuk a Format Control menüt, amelyben a Control fül lesz fontos nekünk. Első lépésként a Cell link mezőbe egy jelenleg még üres segédcella nevét írjuk be (nálam ez most véletlenszerűen az O3 lesz), amit szintén célszerű elrejtenünk egy "éles" munkalapnál:

spin3.jpgKövetkező lépésként lépjünk bele abba a cellába, ahol majd a nyíl nyomogatása után szeretnénk megjeleníteni a lista értékeit és a következő formulát írjuk be:

=INDEX(M4:M7,O3)

spin4.jpgUgyebár azt már kívülről fújjuk, hogy az INDEX függvény az első paramétereként megadott tömbből a második paramétereként megadott számú sorban lévő értéket adja vissza, így látható, hogy a négy település neve lesz a tömb, a Spin Buttonhoz linkelt O3 cella pedig a sorszámot fogja adni.

spin5.jpgAz O3 linkelt cella pedig a Spin Button nyilai alapján kapja az értéket, attól függően, hogy éppen hol állunk a tartományban. Fontos tennivaló még a maximum és minimum értékek beállítása, ugyanis ellenkező esetben lenne rengeteg #REF hibaüzenetünk is. Szóval kattintsunk még egyszer jobb gombbal a Spin Buttonra, majd a Format Control menü Control fülén állítsuk be a minimum és maximum értéket:

spin6.jpgÉs ezzel kész is vagyunk, a lélekszám bekeresését már nem fogom printscreenelni, azt a messze legismertebb Excel-függvénnyel, a VLOOKUP formulával könnyedén meg tudjuk oldani.

Data validation lista automatikus feltöltése munkalapunk chartjaival

A néhány hete közzétett automatikus Excel-PowerPoint kapcsolatos poszthoz kapcsolódóan érkezett az egyik kedves Olvasótól egy kérdés, amellyel arra keresi a választ, hogy van-e mód arra, hogy Excelből ne az összes chartot vagy egy specifikusan megnevezett chartot másoljunk át egy PowerPoint diára, hanem a felhasználó kezébe adjuk a választási lehetőséget. Ez is egyértelműen megvalósítható feladat, a következő posztban be is mutatok egy lehetséges irányt.

Szóval adott a következő Excel néhány előre beszúrt példadiagrammal:

listofcharts.jpgSzúrjunk be a Data ribbonfül Data Tools szekciójából egy Data Validationt:

listofcharts1.jpgData Validation Listre lesz szükségünk, amelynek forrása legyen egy üres, később elrejtésre kerülő sheet egy jelenleg még üres tartománya:

listofcharts2.jpgEzután pedig már csak össze kell dobnunk a minimális designt, tehát lesz egy kis magyarázat, a data validation lista plusz egy gomb, amelyhez a PowerPointba másoló kis makrónkat tudjuk majd hozzárendelni.

listofcharts3.jpgNa de itt álljunk is meg, mert mostani posztunk legfontosabb tennivalója következik, azaz megírni azt a kis VBA-kódot, ami majd automatikusan frissíti a Data Validation listánk forrástartományát, így lehetővé téve, hogy a felhasználó az adott Excel-fájlunk aktuális chartjai közül választhasson.

Alt+F11 lenyomásával menjünk át a VBA-editorba, majd essünk is neki a kis kódunk megírásának, méghozzá a változók deklarálásával:

Sub Chartlist()
Dim chartobjs As Long
Dim listobj As String

A chartobjs kreatív nevet viselő változó fogja tárolni Excel fájlunk összes chartját az adott sheeten, a listobj változó pedig az összeszedett chartokat fogja a ciklus során végig tárolni és innen populálódnak majd a tartományunk cellái.

Most jöjjön a ciklusunk, amellyel szépen kitöltjük a Sheet2 megfelelő celláit, azaz a Data Validation lista forrástartományát:

With ActiveWorkbook.Sheets(1)
For chartobjs = 1 To .ChartObjects.Count

Az aktív munkafüzetünk első sheetjén szereplő összes charton fog végigmenni a ciklus az elsőtől az utolsóig, és minden egyes chart esetében a következőt fogja végrehajtani:

listobj = .ChartObjects(chartobjs).Name
Sheet2.Cells(chartobjs, 1) = listobj

Tehát a listobj változót teszi egyenlővé az adott cikluskörnek megfelelő számú chart nevével, majd a nevet beírja a chart sorszámának megfelelő számú cellába az első oszlopban a kettes sheeten.

Aztán megyünk a következő chartra a ciklusban és így tovább:

Next chartobjs
End With
End Sub

Ha megvagyunk a kóddal, akkor a lefuttatás után ezt fogjuk látni a második sheeten:

listofcharts4.jpgÉs ha ezt látjuk a második sheeten, akkor az is egyértelmű, hogy mit látunk az első sheeten:

listofcharts5.jpgInnentől kezdve pedig már csak arra van szükségünk, hogy beolvassuk a kiválasztott chartunk nevét, majd erre a chartra lefuttassuk a másoló makrót és máris a felhasználóra bíztuk az input meghatározását. Fontos megemlíteni a poszt végén, hogy ez egy nagyon alap kód, tehát bőven lehet finomítani még azzal, hogy mondjuk az összes sheeten vizsgálja a chartokat (még egy For ciklus), plusz minden indításnál fusson le a makró, hogy a legördülő menü friss legyen mindig és így tovább.