Exceles fejtörők után most ismét a másik kedvenc témám kerül terítékre, azaz Powerpointban fogunk kicsit rajzolgatni, ami bár elsőre valószínűleg ostobaságnak tűnhet, de csak azok számára, akiknek nem volt még égető szükségük egy igazán figyelemfelkeltő, vizuális prezentáció elkészítésére igen rövid időtávon belül. Úgyhogy rá is térnék a mai "rajzunkra", ami elég egyszerű és nem túlságosan kreatív kis forma lesz, mégis hasznos lehet egy-egy diánk feldobására, ráadásul roppant gyorsan elkészíthető: tóba hulló vízcsepp avagy egy pillangó szárnyának apró csapása akár vihart is előidézhet sok ezer kilométerrel távolabb.
Első lépésként a Ribbonunk Home füle alatt található Drawing szekcióból szúrjunk be egy kört a diánkra, majd a szintén eme szekcióban található Shape Fill funkció segítségével távolítsuk el a kitöltést:
Még mindig a Drawing funkciócsoportban maradunk és a Shape Outline funkció használatával vastagítsuk meg most már kitöltetlen körünk körvonalait:
Ezután következő lépésként CTRL+C és CTRL+V használatával másoljuk le körünket, majd az új másolatot kijelölve, jobb gombbal való kattintás után lépjünk a Format Shape menücsoportba, ahol a Size paramétercsoportban előbb állítsuk be a Lock aspect ratio pipát (így megtartjuk körünk arányait az eredetihez képest), majd értelemszerűen a Height értéket növeljük meg egy kicsit:
Ezután ezt az új kört húzzuk rá az eredetire, majd most már a másolatot másoljuk le megint és azzal játsszuk el ugyanígy az előbb említett méretnövelést:
Csináljuk ezt egy darabig, amíg a megfelelő számú hullámunk nem lesz meg körökből:
Az összes kört kijelölve és jobb gombbal kattintva csoportosítsuk össze őket a Group funkció használatával:
Ezután az így létrejött egyesített formára kattintsunk jobb gombbal és a Format Shapes menücsoportban a 3-D Rotation funkciócsoport alatt állítsunk be egy Off Axis döntést:
Mi következik most? A csepp, ami aláhullik tavunkba, ezt pedig a Drawing szekcióból egy Teardrop forma beszúrásával tudjuk létrehozni:
Alapesetben ez a forma meg van döntve 45 fokban, úgyhogy a Format Shape menücsoportban a Rotation paraméter -45 fokra állításával szépen egyenesbe tudjuk hozni:
Aztán színezgessük, formázgassuk, építsünk köré kék hátteret, amit szeretnénk:
Ezt szeretnénk összesíteni mondjuk akár heti, kétheti vagy negyedéves periódusokra (szándékosan nem írtam havit és évest, mert azok azért egyszerűbbek még formulákkal is). Ne gondolkodjunk azonnal formulákban, az Excel PIVOT-tábláinak van egy csodálatos kis beépített funkciója, amit most mindjárt meg is mutatok.
Ezután kattintsunk az első sorunkra (az első Row Labelre), ami jelen esetben 1/1/2015 és kattintsunk a jobb egérgombbal, majd válasszuk ki a Group funkciót:
A felugró kis ablak pedig szerintem már magától értetődően kínálja fel a lehetőségeket, amelyekre szükségünk van.
Ha havi vagy éves szinten összesítenénk, akkor azt a funkció defaultban kínálja nekünk (negyedévenként szintén), de ha bizonyos meghatározott napszám szerint összesítenénk, akkor kattintsunk csak a Grouping By Days opcióra és a Number of days mezőbe írjuk be a napok számát, amely szerint összesítenénk, tehát kéthetinél 14-et, hetinél hetet.
Ha megvan, kattintsunk okét és már rendben is vagyunk, formulák és trükközés nélkül ott van, amit akartunk:
Annyit szeretnénk csak, hogy mostantól minden egyes megnyitáskor csak az adott hónap sheetje legyen látható, a többi legyen rejtett. Úgyhogy most menjünk a VBA-editorba és Workbookunk Open (megnyitási) eseményéhez rendeljünk hozzá egy kódot, amit el is kezdünk szépen feltölteni, szokás szerint első lépésként a változók, konstans értékek létrehozásával. Most amire szükségünk lesz, az az, hogy betöltsük sheetjeink számát egy változóba:
Ha ez megvan, akkor nagyjából már csak egy For...Next ciklusra van szükségünk, amely egy IF vizsgálatot fog végrehajtani annyiszor, ahány sheetünk van. A megszokott módon, tehát az első sheettől kezdve az AllSheets változónkban meghatározott darabszámig nézzük meg, hogy a Sheetünk neve megegyezik-e a fentebb már deklarált currentperiod változóval és ha nem, akkor rejtse el az adott munkalapot.
És ezzel már kész is vagyunk, úgy működik, mint a szél, ráadásul nem is terheltük meg az agyunkat így az év első napján.
Az összes ilyen problémánál a megoldás kulcsa az ötlet, amellyel elérhetjük a célunkat, a megvalósítás már önmagában nem nagy dolog, gondoljunk csak vissza mondjuk az egy cellába beszúrható több link kérdésköréről. Az ötlet itt egy SUBSTITUTE függvény és egy IF egymásba ágyazása lenne a következő metódus szerint.
Csak a megértés szempontjából a megoldás következő részét egy újabb oszlopban fogom felvázolni a kis táblázatunkban, amely rész nem más lesz, mint a fenti SUBSTITUTE megismétlése, azzal a különbséggel, hogy most nem fűzzük össze a szóközöket az első karakterekkel, szimplán lecseréljük őket semmire (""):
Következő lépésként egy LEN függvényt kell beiktatnunk az eddigi megoldás-részeinkbe, ami ugyebár nem csinál mást, mint a paramétereként megadott szöveg karakterszámát adja vissza (ez mondjuk trailing space levadászására is jó lehet).
Mihez kezdjünk ezzel? A kulcs abban rejlik, hogy az első karaktereket is eltávolító megoldás-részünket még el kell osztanunk kettővel is, hiszen azzal meg tudjuk határozni, hogy pontosan hány kezdőbetűt vettünk ki amiatt, hogy megegyezik az első szó kezdőbetűjével:
Ugyebár ha az összes kezdőbetű azonos, akkor pontosan azt a számot kapjuk meg, ahány szóközünk van a szövegben - következésképpen már csak meg kell vizsgálnunk, hogy az első és a második megoldás-részünk megegyezik-e, mert ha igen, az azt jelenti, hogy minden kezdőbetűnk azonos a szövegben. Ezt a vizsgálatot pedig egy szimpla IF függvénnyel tudjuk elvégezni így:
Most készítsünk ebből egy teljesen egyszerű oszlopdiagramot (persze lehet akár háromdimenziós oszlop vagy "bar" is) mondjuk a lentieknek megfelelően:
Ennek az elkészítéséről már nem fogok részletesen írni, korábbi posztokban részletesen taglaltam a diagramok elkészítésének menetét, ha valakinek segítségre lenne szüksége, jelezze nekem valamilyen kommunikációs csatornán. Az igényesebb megvalósítás érdekében (persze azért az igazi igényességtől ez messze lesz) szúrjunk be még a Ribbonunk Developer füle alatt található Controls szekció Insert parancsával egy gombot is, ami kattintással fogja elindítani a kis színezésünket.
Ehhez fogjuk aztán majd hozzárendelni azt a makrót, ami az x tengelyünk elnevezései közül mondjuk Gyömrő esetében átszínezi az oszlopot, mert például erre a városra szeretnénk nagyon koncentrálni bemutatónkban (de hangsúlyozom, lehet akár érték, akár más paraméter alapján is átdolgozni a makrót).
És máris a VBA-editorban vagyunk, ahol szépen a kattintás eseményünkhöz kidolgozhatjuk a kódot, amit szeretnénk kattintás esetén lefuttatni. Ahogy megszoktuk, első lépésként kezdjük változók deklarálásával, méghozzá kapásból néggyel.
Most lássunk hozzá és kezdjük feltölteni a változóinkat, azaz állítsunk be kezdőértéket, legalább a táblázatoshoz illetve az adatmezőshöz. Amikor egy Chart aktív objektum, akkor az ActiveChart tulajdonsággal tudunk rá hivatkozni, így az első esetben c változónk kezdőértéke legyen az ActiveChart, tehát a diagramunk - itt álljunk meg arra a gondolatra, hogy ha nincs éppen kijelölve a diagramunk vagy más diagramok is vannak más sheeteken a táblázatunkban, akkor mindenképpen aktiválni kell a megfelelőt az Activate utasítással. Itt most annyiban leegyszerűsítettem a kódot, hogy ettől eltekintek.
A ciklusban felhasználásra kerülő l változónkba pedig töltsük be az aktív diagramunk első adatsorának adatmező darabszámát, avagy VBA-ra átfordítva az:
Sok már nincs hátra, ahogy kitalálhattuk, következik a ciklusunk maga, azaz a másik változónkba töltött egyes kezdőértéktől egészen az l változónkba az előbb már betöltött adatmező darabszámig futtasson egy IF utasítást, méghozzá a következőkben kifejtettet.
Ezt a részt könnyedén kivehettem volna a posztból, de úgy gondoltam talán hasznos lehet egy kis közös problémamegoldás is, szóval mit jelenthet az üzenet? Az üzenet szerint egyértelműen valamelyik változónkkal lehet a probléma. Na de melyikkel? Ahogy végigfutjuk ezt a tényleg roppant aprócska kis kódot, egyből szemünkbe ötlik a fent már emlegetett megoldás, azaz c változónk kezdőértékének ActiveChartra való beállítása. Hiszen ha a gombra kattintunk, akkor nem lehet kijelölve a diagramunk!
Azt szeretném elérni, hogy a Teljesült státusznál a háttér zöld legyen, a Folyamatban státusznál narancssárga, míg a Még nem kezdődött el státusznál legyen piros. A Ribbonunk Home füle alatt lévő Styles szekcióból tehát hívjuk meg a Conditional Formattingot és hozzunk létre egy új szabályt (New Rule):
Itt az utolsó lehetőséget, a Formula alapján történő formázási szabályt válasszuk:
A Formula sorába írjuk be a következőt:
Majd okézzuk el. Ha most megnézzük a táblánkat, láthatjuk, hogy ha a listából kiválasztjuk a Teljesült státuszt, akkor a háttér értelemszerűen zöldre vált:
A további teendőnk ismét az F5-ös cellán állva két új szabály létrehozása Conditional Formattingon belül az előbbieknek megfelelően, a narancssárga illetve a piros szín szabályaira. Ha az elkészült, akkor már csak másolnunk kell ezt a cellát a Státusz oszlopunkon belül és meg is van a végeredményünk:
Mondjuk, hogy ez inkább bemelegítő gyakorlat volt, hiszen valószínűleg sokak számára nem írtam újdonságot, úgyhogy folytassuk is a másik kérdéssel, amire választ keresünk és találunk.
A feladatunk annyi lenne, hogy a Projekt állapota sorban jelenítsük meg a Folyamatban szót, ha bármelyik értékünk a feladatlistában még mindig nincs Teljesült státuszban, ha pedig minden Teljesült státuszban van, akkor legyen az összesített állapot is Teljesült. Mindenképpen szeretnénk, hogy üres cellák esetén is helyesen működjön és ne zavarja meg az adott cellák formátuma sem. Ezekkel a feltételekkel az IF használata már kicsit nehézkesebb lesz, ezért váltsunk át az én titkos kedvencemre is, a SUMPRODUCT függvényre, amelyről tudjuk az általános definíciót, azaz hogy két vagy több tömb elemeit szorozza majd adja össze, tehát a két tömb egymással egy sorban lévő elemeit összeszorozza majd az eredményeket szummázza. De bővebben róla példákkal itt:
Ezután a formákra kattintva megjelenő Drawing Tools Format füle alatt a Shape Styles csoport menüi segítségével, előbb színezzük őket különböző színekre (Shape Fill), majd a Shape Outline menüvel távolítsuk el a körvonalakat is:
A megszokott módon csoportosítsuk össze a három formánkat, mondjuk CTRL lenyomása mellett kattintva kijelöljük egyszerre mind a hármat, majd jobb gombbal kattintva a Group menü segítségével egyesítsük őket egy formává a könnyebb kezelés érdekében:
A most már egy formának számító ábránkra ismét jobb gombbal kattintva menjünk be a Format Shape menübe, hiszen itt fogunk konkrétan lépcsőket varázsolni a téglalapjainkból:
A jobb oldalt megjelenő menüstruktúrában a középső kis ikonra kattintva jutunk a formánkra vonatkozó Shape hatásokhoz, ahol elsőként a 3-D Rotationre van szükségünk és az előre beállított döntések közül válasszuk ki a tőlünk hátrafelé megdőlő (Relaxed) perspektívanézetet:
Ezután értelemszerűen ugyanitt, a Perspective beállítási lehetőség alatt a szög növelésével-csökkentésével még állítgathatjuk, ahogy akarjuk a döntést:
Ezután szintén itt, a formánkra vonatkozó Shape hatások beállításainál menjünk át a a 3-D Format alá, ahol tegyünk egy kis kiemelkedést még a formánkra a Top Bevel beállításával, mondjuk Cross típussal:
Aztán a mélységet (Depth) is állítsuk legalább harminc pontos nagyságra (ne felejtsük el, hogy most a három formánkból létrejött egységről van szó):
Most valahol itt járunk:
Ezután jön az utolsó lépés, ami konkrétan a lépcső kialakítása lesz, ezt a 3-D Rotation alatt tudjuk beállítani, méghozzá a Distance from ground menü segítségével. Ki kell jelölnünk az egységes formánkon belül a második alkotóelemet (ki tudjuk, nincs ok az aggodalomra), majd itt állítsuk a Distance from ground értéket 25 pontra, majd a harmadiknál 50 pontra:
És meg is vagyunk, innen kezdve pedig már színezgethetünk, árnyékolgathatunk, nyilakat rajzolgathatunk, szövegeket helyezhetünk el és értelemszerűen bővíthetjük is:
Első lépésként szépen lépjünk be a VBA editorba, majd jobb gombbal való kattintás után a bal oldali projekt ablakban, szúrjunk be egy modult, itt fogjuk majd szépen deklarálni a funkciónkat, hiszen ezúttal egy funkcióra lesz majd szükségünk, amit meghívhatunk az Excelben. Szóval ha létrehoztuk a modulunkat, hozzuk létre a funkciót neve illetve mögötte zárójelben paraméterei megadásával:
Következő lépésként a CellColorValue nevű változónkat állítsuk be úgy, hogy majd a funkciónk első paramétereként megadott szín colorindexét vegye fel:
Ahogy erről már volt szó, ezt a .Interior.ColorIndex utasítással tudjuk megtenni.
Szerintem az egyetlen ismeretlen rész ebben a ciklusban a WorksheetFunction.Sum nevű utasítás, amely egy olyan utasítás, ami a zárójelben mögötte megadott számokat, argumentumokat, tehát tartományt fog összegezni.
Függvényünk most már használható, úgyhogy szépen menjünk vissza Excelbe, ahol például Miskolc esetében
Ahogy már volt róla szó, a With...End With parancs a közötte felsorolt utasításokat hajtja végre ugyanarra az objektumra vonatkozóan, tehát jelentős mennyiségű gépelést megspórol nekünk, és ezt most itt is be fogjuk vetni, méghozzá az Me objektum felhasználásával. Mi is az az Me?
Ezután jön az a lépés, amelyben egy For Each...In Next ciklus bevetésével az első munkalapunktól kezdve végigmegyünk az összes munkalapunkon és megnöveljük r változónk értékét eggyel. Erre azért van szükségünk, mert ebből a változóból fogjuk kiolvasni azt, hogy hányadik sheeten vagyunk, hiszen a Summary sheeten annyiadik sorba kell majd a linket is bevinni az adott munkalapra. Célszerű beletenni egy egyszerű IF utasítást is, hogy ezt csak akkor csinálja meg, ha nem a Summary sheetünkről van szó, mert azzal eléggé nem a kívánt eredményt érnénk el:
Most van hátra még egy, esetleg két lépés, attól függően, hogy akarunk-e az aktuális sheetek első cellájába egy Summary sheetre mutató linket vagy sem. Mindenesetre mi most teszünk minden sheet A1-es cellájába egy visszafelé mutató linket, amelynek első lépéseként elnevezzük minden egyes sheetünk A1-es celláját egy konstans "Start_" szöveg és az adott worksheet index tulajdonsága segítségével, amely utóbbi az adott sheet helyzetét adja vissza sheetjeink halmazában.
Utolsó lépésként pedig nagyjából ugyanezekkel az utasításokkal és paraméterekkel Me (azaz a Sheet1) megfelelő celláit is frissítjük a sheetek linkjeivel:
Persze, érthető, hogy ha egy küldő 10-20 levelet küld nekünk naponta, akkor az ő üzenetei között pár hónap távlatából is megtalálni egy bizonyos ügy szövegét, szinte lehetetlen erőfeszítésnek tűnhet, de ez még mindig nem magyarázza, hogy miért nem lehet ezt a funkciót kikapcsolni - az ilyen típusú és formájú üzenetek felett egyébként is eljárt már az idő. Szóval egyszerű megoldás nincs, VBA-val megoldható az ügy, de talán akkora fáradtságot nem ér meg. Mindenesetre ha valakit érdekel a megoldás, akkor jelezze és ejtek róla szót, de ismétlem, ezzel még együtt lehet élni.
Sokáig fel sem tűnik a felhasználónak, de mostantól a leveleink főablakánál fent van egy apró kis gomb, amellyel a korábbi Filter opció helyett már kapásból tudunk az olvasatlan leveleinkre szűrni:
Én mondjuk mindent elkövettem mindig, hogy az Inbox minimális mennyiségű, tényleg csak a fontos kérdéseket, nyitott ügyeket tartalmazó levelekből álljon, de azok számára, akik rendszereznek a bejövő postafiókon belül, ez jó lehetőség.
Nincs új a nap alatt, de azért gondoltam megemlítem, hiszen így roppant könnyedén láthatjuk betűinket bármilyen képernyőn és ez mondjuk egy sötét szobában vagy egy apró kivetítőnél hasznos lehet.