Az első Power BI dashboard Excelre húzva csak néhány másodperc

2018. április 22. - Office Guru

A mai poszttal kicsit kibővítjük a blog tárházát, ugyanis ma egy rém egyszerű Power BI dashboardot fogunk egy Excel-fájlból elkészíteni. A most már több éves múltra visszatekintő Power BI a vizualizáció, a dinamikus adatkezelés egyik új zászlóshajója a Microsoftnál, amelynek hatalmas előnye a kompatibilitás nagyjából minden adatforrással, ráadásul telefonon is szuper lehetőségeket kínál. Aki még nem ismerkedett meg a programmal, az bátran töltse le a Microsoft honlapjáról, az alapverzió teljesen ingyenes, de ha komolyabb üzleti célokra akarjuk használni (mint például automatikus adatfrissítés stb.), akkor már a zsebünkbe kell nyúlnunk.

Szóval ha megvan a program, akkor lássunk is neki a dolognak. Feltételezzük, hogy van egy Excel-fájlunk már megfelelő adatokkal, amiket vizualizálni szeretnénk - nem feltétel, de célszerű, ha ezek az adatok már táblákba vannak rendezve. Valahogy így:

bi1.PNGLátható, hogy a táblákat a példához már úgy alakítottam ki, hogy legyen egy elsődleges kulcs, amivel összeköthetők a táblák, ezt az irányt a későbbiekben is célszerű tartani, hozzátéve ismét, hogy enélkül is simán elműködhetünk a BI-ban. Power BI-ban a Home ribbonfül alatt lévő External Data szekció Get Data funkciója alatt nyomjunk az Excel-fájlra:

bi2.JPGItt azért még nyugodtan böngészhetünk, hiszen a program erőssége, hogy bőven nem csak Excellel dolgozhatunk, sőt! Szóval a kiválasztott Excel-fájl esetén választhatunk, hogy milyen objektumokat (táblákat) vagy sheeteket akarunk "behúzni" - én most csak a táblákat választom:

bi3.JPGÉs mehet is a Load:

bi4.JPGPár másodpercnyi várakozás után már láthatjuk is, hogy a jobb oldali Fields szekcióban meg is jelent a három táblánk (buta módon nem adtam nekik egyedi nevet, de így sem lesz gond):

bi5.JPGHa a baloldali vékony, fekete csíkon a "Relationship" nézetre váltunk, akkor láthatjuk is a fentebb már említett kapcsolódást az elsődleges kulcsokon keresztül:

bi6.JPGItt új kapcsolatokat építhetünk, de meg is szüntethetünk régebbieket is - Access-guruk értelemszerűen előnyben. Ezután visszatérve az eredeti nézetre, a Fields szekcióban lévő három tábla oszlopaiból válogathatunk, majd ezeket kijelölve a Visualizations szekcióból már pakolhatunk is a Dashboardba bármilyen megjelenítést:

bi7.JPGViszont ha a baloldali, fekete menüsoron nem a vizualizációs és nem a kapcsolati hálót bemutató nézetet, hanem a táblanézetet választjuk, akkor újabb lehetőségek nyílnak meg. Most én például létre fogok hozni egy új oszlopot jobb egérgombbal való kattintás után:

bi8.JPGÉs ahogy a képen is látható, a megnyitott táblám Mennyiség oszlopát és a RELATED függvény segítségével meghívott harmadik táblám Ár oszlopát szorzom össze (az azonosítás pedig a Típus elsődleges kulcson keresztül történik):

bi9.JPGÍgy amikor visszalépünk a vizualizációs nézetbe (használjuk bátran a vékony fekete csíkot a baloldalon), már ki is tudjuk jelölni az újonnan létrehozott, kalkulált oszlopot és be is tudjuk tenni a dashboardba, mint a szumma értékesítési számunk:

bi10.JPGHa nem tetszik a kinézete a kis boxunknak, akkor a Visualizations szekcióban át tudunk ugrani a beállításokra (a kis Teddy-hengert használjuk bátran), ahol nyugodtan formázhatjuk a betűt, a hátteret és így tovább:

bi11.JPGDe nem csak ilyen megjelenítésünk lehet, ha van várost, országot stb. tartalmazó oszlopunk, akkor a Visualizations szekcióból választhatjuk a Map előredefiniált formátumát, ami szépen be is fogja lőni nekünk az adott várost, országot a világ térképen, valahogy így:

bi12.JPGTisztában vagyok vele, hogy egy tapasztalt Power BI használónak ez a poszt az értéktelen kategóriába tartozik, de mielőtt komolyabb problémákat és kihívásokat feszegetnék, szerettem volna egy kis ismétlést tartani, plusz talán van olyan, akinek ez a rövid kis bevezető is segítség volt.

Hoztam is munkalapot meg nem is

Azt elég gyorsan megtanulja az összes Excel-felhasználó, hogy minden munkafüzetünknek kell lennie legalább egy munkalapjának, tehát egy darab, utolsó sheetet nem lehet törölni és elrejteni sem. Ha ez így van, akkor mégis, hogy sikerült ezt összehozni, teszi fel a kérdést egyik Olvasóm:

hide3.JPGEz nem látszik a screenshoton, de ez egy rendes névvel, mérettel bíró Excel-fájl, amelyben megnyitás után nem látunk semmit Excelben, mintha egyetlen sheetet sem tartalmazna a fájl. De ez hogy lehetséges?

Igazából azért működik a dolog, mert nem a munkalap, hanem maga a munkafüzet van elrejtve. Hiszen ha egy munkafüzetben az utolsó sheetre kattintunk és megpróbáljuk elrejteni, akkor ezt az üzenetet kapjuk:

hide1.JPGÉs VBA Editorból sem megy, hiába megyünk át ALT+F11 lenyomásával arra a felületre, az adott munkalap tulajdonságai között nem tudjuk a Visible propertyt átállítani Very Hiddenre:

hide6.JPGEnnek tükrében tehát egyértelmű, hogy nem lehet elrejteni egyetlen munkalapot, így marad kizárólag a munkafüzet elrejtése. Ezt a következőképpen tudjuk megtenni: a View ribbonfül alatt lévő Window szekcióban van egy Hide utasítás:

hide2.JPGEnnek lenyomása után eltűnik a munkafüzet:

hide3_1.JPGDe ha belépünk az Options menübe és megpróbáljuk lementeni a fájlt, azt látjuk, hogy nincs aktív Save és Save As opció. Akkor mégis hogyan tovább?

hide4.JPGViszont, ha megpróbáljuk szimplán bezárni az elrejtett munkafüzetünket, akkor felugrik a jólismert, mentésre kérdező ablak és így már sikerrel el tudunk menteni egy látszólag semmit sem tartalmazó fájlt:

hide5.JPG

Az elszabadult borderek nyomában

A borderek annyira alapvetőek és maguktól értetődőek, hogy használni őket tényleg roppant egyszerű és az egyik leggyakrabban használt funkciónak számítanak az Excelben - álljon ám itt a következőkben három olyan probléma, amelyek mindegyike borderekhez köthető és a három közül az egyik az én gyűlölt ellenségeim listáján is rajta van.

Az első bemutatásához kezdjünk egy egyszerű kis táblával:

border1.JPGEzt a táblát jelöljük ki borderestül, mindenestül és tegyük a vágólapra (Copy = CTRL+C), majd Paste Special segítségével illesszük be, méghozzá képként:

border2.JPGLegalsó sorban balról a harmadik a mi opciónk. Ha beillesztjük képként, valami ilyesmit fogunk látni:

border3.JPGLátható, hogy a baloldali és a felső vonalunk elvékonyodott, tehát nem igazán sikerült egy az egyben végrehajtanunk azt, amit szerettünk volna. Homályos magyarázatot már sikerült találnom erre a - szerintem - hibára, de végülis minket úgyis csak a megoldás érdekel, szóval mit tehetnénk?

Az első lehetőség az, hogy nem csak a táblát, hanem legalább még egy-egy cellát kijelölünk körben (persze érdemes a cellák átméretezését is beiktatni):

border4.JPGÍgy már jól fog menni a képként beillesztés:

border5.JPGDe ez nem is igazi segítség, mert el kell térnünk valamelyest az eredeti elképzelésünktől, viszont a következő megoldási javaslat már sokkal jobb ennél (annak ellenére, hogy a végeredménye annak sem az, amit igazán szeretnénk). A beillesztett képre kattintva a Ribbonon megjelenő Picture Tools ribbonfül Size szekciójából válasszuk ki a Crop funkciót:

border6.JPGEzzel szépen el tudjuk tüntetni a jobb oldali és az alsó oldalról is a vastag bordert, hogy legalább arányos maradjon a képünk.

border7.JPGÉs hogy mi is a konkluzió? Használjunk képernyőfotó készítő alkalmazást, mondjuk a Windowsba beépített Snipping Tool is tökéletes lehet - az Excel sajnos nem képek manipulálására lett kitalálva.

Borderekhez kapcsolódó idegesítő funkciót fogunk a folytatásban is taglalni, amelyhez a következő táblánk lesz a példa:

border8.JPGA képből nem jön át, de az Átlag oszlop első értéke egy kalkulált érték, amely az Autó és Repülőgép oszlopok számainak átlagát mutatja - és értelemszerűen ezt az első cellába kalkulált képletet szeretnénk "lehúzni" (automatikusan kitölteni) az egész Átlag oszlopban. Ha viszont megtesszük, ezt kapjuk:

border9.JPGHa el akarjuk kerülni azt, hogy az első cellánk felső, vastag bordere is másolásra kerüljön és nem akarunk utólag sem formázni, akkor lehúzás helyett rakjuk be a vágólapra az első cellánkat CTRL+C lenyomásával, majd a jobb gombbal előhívható menüsorból a Paste Special csoport alatt az All except borders opciót válasszuk:

border10.JPGEzután pedig jelöljük ki azon cellákat, ahova a képletünket be akarjuk illeszteni és máris láthatjuk a tökéletes eredményt:

border11.JPGÉs még mindig nincs vége a borderek szapulásának, ugyanis a következőkben még mindig a borderek hibái lesznek terítéken, de ezúttal már bevonjuk a WORDöt is. Tegyük fel, hogy az a feladatunk, hogy egy Excel-táblát, képként illesszünk be egy Word-dokumentumba. Alapvetően erre több lehetőségünk van, de ha a default funkciókat használjuk, akkor az eredményünk ugyanaz lesz, mint amit mindjárt látni fogunk. Szóval én a tábla kijelölése után a Home ribbonfül Clipboard szekciójából a Copy menü alatti Copy as Picture menüpontot választom:

border12.JPGItt megint lesz néhány lehetőségünk, de célszerű az As shown when printed opciót választani, főleg, ha egyszer tervezünk nyomtatni is (és WORD esetén ez elég gyakori):

border13.JPGSzóval ha ezt így utána CTRL+V lenyomásával beillesztjük a WORD-be, akkor furcsaságot tapasztalunk, ugyanis ezt vagy valami nagyon hasonlót fogunk látni:

border14.JPGHogy a hirtelen megjelenő vastag bordereinket el tudjuk tüntetni, a kép kijelölése után a Picture Tools ribbonfül Size szekciójában méretezzük kicsit át a képünk magasságát, és máris rendben leszünk:

border15.JPG

Valószínűleg egyébként nem csak ezek a megoldások léteznek és lehet, hogy nem is ezek a legjobbak, úgyhogy bárkinek van valami jó kis tanácsa, ne habozzon megosztani.

Kódból mozgassuk az egeret és kattintgassuk bőszen!

Alapvetően VBA-t főleg az Office-programok manipulálására, adatok mozgatására, formázására, levelek küldésére, Access-adatbázisok frissítésére stb. használunk, ha ki kell nyúlnunk mondjuk Excelen kívülre, akkor ott azért beleütközhetünk korlátokba, ezért általában bizonyos, Officeon kívüli dolgok megvalósításához inkább már más megoldások szoktak képbe jönni. Ettől függetlenül simán megtörténhet, hogy egy makró részeként meg akarunk nyitni egy weblapot, majd a weblapon be akarunk pötyögni valamit és esetleg egy gombra is rá akarunk nyomni. A weblap megnyitásról korábban már volt szó itt a blogon, úgyhogy a mai posztban az egér kurzorját fogjuk megmozdítani és kattintunk is vele kettőt, felhasználva az erre a célra leggyakrabban használt standard módszert.

Csak azért, hogy legyen valamilyen keretünk, adott a következő gomb:

mozgatas1.JPGMegnyomása után szeretnénk, ha az egérkurzorunk elmozdulna lefelé és duplán belekattintana az F14-es cellába. Ennek érdekében rendeljünk hozzá egy makrót a gombhoz, majd az ALT+F11 lenyomásával lépjünk be VBA-ba. Mielőtt a konkrét mozgatós és kattintós szubrutinba ugranánk, deklarálunk kell egy funkciót a kurzorunk pozíciójának beállításához és az egérgombok eseményeihez egy szubrutint. Ez alapvetően standard, tehát be is másolom ide:

Public Declare Function SetCursorPos Lib "user32" (ByVal x As Long, ByVal y As Long) As Long
Public Declare Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)

Az Excelnek ugyebár nincs beépített funkciója a kurzor pozíciójának meghatározásához vagy beállításához, de egy API segítségével hozzáférhetünk a user32.dll-ben tárolt SetCursorPos funkcióhoz (a GetCursosPos segítségével a jelenlegi pozíciót kaphatjuk vissza), ami ugyebár segíteni fog beállítani a kurzor helyét. ByVal X és ByVal Y a konkrét koordináták változói. A második deklaráció szintén a user32.dll-ben tárolt mouse_event szubrutint fogja megszólítani, ami segít az egér lehetőségeit használni. A dwFlags paraméter a konkrét egéreseményeket jelenti, ide kerülhet például egy MOUSEEVENTF_LEFTDOWN esemény, ami egy bal egérgomb lenyomását jelenti. A dx az egér kurzorának abszolút helyzetét jelenti az X tengely mentén, a dy pedig az y tengely mentén, dwExtraInfo pedig esetlegesen szükséges extra információk begyűjtését teszi lehetőve a dll-ből. Ezután pedig jöjjön a két, bal egérgomb lenyomásához kapcsolódó esemény deklarálása, ez a MOUSEEVENTF_LEFTUP és MOUSEEVENTF_LEFTDOWN.

Public Const MOUSEEVENTF_LEFTDOWN = &H2
Public Const MOUSEEVENTF_LEFTUP = &H4

A kérdés biztos felmerül bennetek, hogy mi a fene az a &H2 vagy éppen &H4. Amikor egy szám előtt &H áll, az azt jelenti, hogy az utána következő karakter egy hexadecimális szám, tehát &H2 konkrétan a kettes szám.

Ha megvannak ezek a deklarációk, akkor jöhet a konkrét mozgató szubrutin:

Private Sub Mozgatas()
SetCursorPos 400, 180
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
End Sub

A SetCursorPos funkció értelemszerűen X és Y tengelyen megadott pixelszám szerint elmozdítja a kurzort, példánk esetében tehát a kurzor új koordinátája a képernyőn X: 400 és Y: 180. A mouse_event funkció első paramétere a konkrét egéresemény, jelen esetben a bal egérgomb lenyomása, második paramétere az x, harmadik paramétere az y koordináta, de mi most csak kattintani szeretnénk, ezért ezek nullák. A negyedik paraméter a dwData, itt csak akkor vesz fel értéket, ha az egér görgőjével akarunk valamit csinálni, tehát az első paraméter a MOUSEEVENTF_WHEEL lenne. Az utolsó nulla pedig a dwExtraInfo, nekünk erre nincs szükségünk.

És azért dupláztuk meg a sorokat, mert lenyomjuk-felengedjük-lenyomjuk-felengedjük, tehát duplakattintást szimulálunk.

mozgatas2.JPG

Két tavaszköszöntő Excel-jótanács

Simán lehet, hogy a következő két kis szösszenet unalomig ismételt ujjgyakorlat lehet egyes Olvasóknak, de egyrészt ismétlés a tudás anyja, másrészt a múlt heti tapasztalataim alapján hajlamos az ember magától értetődő dolgokat roppant gyorsan elfelejteni, ha nem fut időnként bele egy kapcsolódó kérdésbe. Adott egy kis példatábla:

dv1.JPGDirekt láthatóvá tettem a formulákat, hogy a kérdés is egyértelmű legyen - hangsúlyozva, hogy egyébként több tucat sheetet és több száz kalkulált cellát tartalmazó Excel munkafüzetnél is simán működik a következő jótanács. Ha valamilyen oknál fogva valamelyik képletünkben le kell cserélnünk egy range nevét, egy konstans értéket vagy egy cellahivatkozást, akkor semmiképpen ne álljunk neki egyesével másolgatni, hanem szimplán nyomjunk egy CTRL+H-t és hívjuk elő a Replace funkciót.

dv2.JPGA mostani példában az egyik rátacellát akarjuk lecserélni a másikra, így a Replace ablakban megadhatjuk, hogy pontosan mit keresünk a munkafüzet-munkalap formuláiban, majd azt egy gombnyomással le is tudjuk cserélni. Ne felejtsük el, totálisan egyértelműnek tűnik, de simán meg lehet róla feledkezni.

A másik kérdés a Ribbonunk Data füle alól elérhető Data Tools szekció Data Validation funkciójára vonatkozik, azt próbáljuk ugyanis megvalósítani, hogy többféle feltételnek megfelelő adatot tudjunk az adatvalidációt tartalmazó cellába befogadni. Hogy értsük pontosan miről is van szó, álljon itt ez a kis összefoglaló kép:

dv3.JPGTehát azt szeretném elérni, hogy a Data Validationt tartalmazó cellába 0 és 1000 közötti számok vagy a második feltétel-oszlopban megadott szövegek kerülhessenek csak, minden más esetben tagadja meg az Excel az adatbevitelt. Fontos tudni, hogy ez csak egyetlen példa, akár három feltétellel, sokkal hosszabb listákkal és komplexebb szabályokkal is felépíthetünk adatvalidációt. Szóval most lépjünk be a Data Validation menübe:

dv4.JPGAz Allow legördülő menüben válasszuk a Custom opciót, majd a Formula sorba vigyük be a szükséges formulát:

dv5.JPG=OR(AND(C10>$F$13,C10<$F$14),COUNTIF($G$13:$G$15,C10))

Azt ugye már megtanultuk, hogy az OR funkció segítségével azt tudjuk megnézni, hogy a paramétereiként megadott argumentumok bármelyike igaz-e és ennek megfelelően ad vissza IGAZ-HAMIS értéket. Az AND hasonló, csak ott a megadott argumentumok mindegyikének teljesülése esetén jön csak vissza IGAZ érték. A COUNTIF pedig megszámolja, hogy az első paramétereként megadott tartományban hány olyan érték van, amelyik megfelel a második paramétereként megadott feltételnek. És ha ezt így együtt nézzük, látjuk, hogy a függvényünk csak abban az egy esetben adhat vissza TRUE értéket, ha a számunk 0 és 1000 között van vagy a 2. feltételben szereplő szövegek közül valamelyiket tartalmazza a bevitt adat. Mert ha nem így van, akkor ez történik:

dv6.JPG

Joyplot-chart - kreatív és különleges diagram időpillanatok adatainak bemutatására

Az Excel egyik legnagyobb fegyverét a benne rejlő szabadság és a funkciók sokasága adja, már itt a blogon is többször néztük meg, hogy egyetlen problémára hány különféle megoldást lehet adni - és nincs ez másként a chartok esetén sem, ugyanis az alapból beépített diagramokhoz képest jóval többet érhetünk el egy kis kreativitással, abból pedig nem szenved hiányt az a sok ezer Excel-fanatikus bütykölő, akik újabbnál-újabb megoldásokkal állnak elő. Egy ilyen újdonság az ún. joyplot-chart, amelyet nem túl könnyű előállítani és kizárólag egyetlen típusú adathalmaz esetén van értelme használni, ettől függetlenül a következő posztban ennek a diagramnak az elkészítésén fogunk végigszaladni.

Adott a következő táblázat, amely három, különböző méretű bolt vevőszámát mutatja adott időpillanatokban:

joyplot.JPGErre az Insert ribbonfül Charts szekciójából szúrjunk be egy kétdimenziós Area chartot:

joyplot2.JPGMár ez is megfelelő ábrázolás, célunknak megfelelő vizualizáció lenne, de a joyplot lényege még csak most következik. Ahány boltunk (vagy bármilyen egységünk) van, szúrjunk be annyi "különbség-sort", azaz azokat a sorokat, amelyek el fogják választani egymástól az area chartunk területi egységeit:

joyplot3.JPGFigyeljünk rá, hogy a pontos elválasztás érdekében az értékek minden időpontnál ugyanazok legyenek és "különbség-soronként" növekedjen az értékük.

Ha ezzel megvagyunk, akkor a Chart Tools ribbonszekció Design füle alól bökjünk a Select Data lehetőségre, majd az Add gomb lenyomásával az egyes "különbség-sorokat" adjuk seriesként hozzá a chartunkhoz, valahogy így:

joyplot4.JPGFigyeljünk rá oda, hogy a legkisebb "különbség-sor" legyen az első boltunk (egységünk) után, és felváltva jöjjön a többi. A sorrendet a Legend Entries ablakocska jobb felső sarkában található nyilakkal tudjuk menedzselni.

Innentől kezdve már látható, hogy mit is kell tennünk, szépen egyesével kattintsunk a "különbség-soraink" területeire a chartban, majd a Format ribbonfül Shape Styles szekciójában a Shape Fill menüpontból állítsuk a kitöltésüket fehérre (vagy amilyen a chartunk alap háttérszíne):

joyplot5.JPGEzután már csak meg kell formáznunk kicsit a diagramot és elő is állt valami ilyesmi:

joyplot6.JPG

Ahogy látható, a joyplot-chartnak akkor van igazán létjogosultsága, ha több különféle egység bizonyos időpillanataiban mutatott eredményét, darabszámát stb. szeretnénk különlegesebb módon megjeleníteni.

Dashboardból prezentáció

Az Excel és a PowerPoint között nem csak az a közös, hogy egy programcsomagba tartoznak és egy cég fejlesztései, hanem az is, hogy mindennapos gyakorlat Excel adathalmazok valamilyen formában történő bemutatása diákon keresztül, így a mai posztban Excel-adatokat fogunk átbűvészkedni PowerPointba egy roppant egyszerű VBA-kód segítségével, ami jó alapot fog szolgáltatni későbbi, hasonló Excel-PowerPoint kapcsolatos posztokhoz. 

Szóval adott a következő egyszerű kis tábla:

pp1.JPG
Ezt és mondjuk tucatnyi másik társát szeretnénk áthegesztni egy PowerPoint diasorozatra automatikusan, hiszen akár rendszeres időközönként is várhatnak tőlünk ilyen jellegű tömény adathalmazt egy prezentáció sokadik diáján. 

ALT+F11 lenyomásával lépjünk be VBA-editorba, majd hozzuk össze a kis kódunkat, amit aztán gombhoz rendelhetünk és a megfelelő táblák elkészítése után automatikusan legenerálhatjuk a templatünket is. A kódunk szokás szerint a változók deklarálásával kezdődik:

Sub ExcelbolPPT()

Dim rng As Range

Dim PP As Object

Dim prezi As Object

Dim dia As Object

Dim kep As Object

Az rng nevű változónk maga a dashboardunk tartománya lesz az Excel munkalapon, a PP-t magának a PowerPoint applikációnak hozzuk létre, a prezi nevű változót egy prezentációnak a PowerPointon belül, a dia nevűt ezen prezentáció egy diájának, a kep nevűt pedig a dashboardunk képformátumának, amikor már a dián leszünk. 

A következő szegmensünkben egyrészt kicsit optimalizálni fogjuk a kódot (vagyis inkább gyorsítani) valamint a változókat feltölteni:

Application.ScreenUpdating = False

Set rng = ThisWorkbook.ActiveSheet.Range("A1:H7")

Set PP = CreateObject(class:="PowerPoint.Application")

Set prezi = PP.Presentations.Add

Set dia = prezi.Slides.Add(1, 11)

Azt már tudjuk, hogy az Application.ScreenUpdating kikapcsolásával nem fogjuk látni, mit csinál a makró, ergó egy kicsit gyorsabb lesz. Az rng változónk értelemszerűen felveszi a makrónknak is helyt adó munkafüzet aktív sheetjének megfelelő tartományát, a PP, objektum típusú változó pedig egy PowerPoint elindítása lesz a CreateObject funkció segítségével - a class paraméter fogja megadni, hogy milyen applikációról is beszélünk. A prezi nevű változó az elindított PowerPoint applikáción belül fog létrehozni egy új prezentációt, míg a dia változó a prezi változóban lévő prezentációhoz fog felvenni egy diát (a két paraméter közül az egyes a dia indexét jelenti, azaz ez lesz az első számú dia, a 11-es pedig az adott dia kinézetének paramétere, jelen esetben azt mutatja, hogy csak címsorunk van).

Ezután jön a kódunk harmadik része, az érdemi másolás és beillesztés:

rng.Copy

dia.Shapes.PasteSpecial DataType:=2

Set kep = dia.Shapes(dia.Shapes.Count)

kep.Left = 300

kep.Top = 152

PP.Visible = True

PP.Activate

End Sub

Az nem kérdés tehát, hogy az rng változóban lévő Excel-táblát fogjuk a vágólapra helyezni (másolni), majd utána ezt beillesztjük a PowerPoint-diánkra a Shapes.PasteSpecial metódus segítségével, amelynek DataType paraméterével határozhatjuk meg, hogy milyen formában szeretnénk is beilleszteni (képként, értékként stb.).

Ezután jön a feladat legkevésbé szeretett része, a pixelenkénti hegesztgetés, hogy hogyan is akarjuk látni a Dashboardunkat a dián. Elsőként a kep változónkba betöltjük a legutóbb beillesztett Shape-t (látjuk, hogy először megszámoljuk, hány Shape van egyáltalán a dián, hiszen ha van 3 darab, akkor minket a harmadik érdekel, mint legutoljára beillesztett), majd ezt fogjuk tologatni a dián mondjuk balról és fentről. Ha szépen meghatároztuk az elhelyezkedést, akkor már csak meg kell jelenítenünk a PowerPointot és azt kell aktív ablakba hoznunk, hogy lássuk az eredményünket.

eredmeny.JPG

Back in the business - Pivot-jótanácsok kezdésnek

Volt pár komolyabb projekt és változás, ami miatt sajnos nem tudtam annyit beletenni a blogba (konkrétan semmit), mint szerettem volna, nem tudtam levelekre sem válaszolni, de most úgy tűnik, hogy ismét folytatódhat az Office-bütykölgetés. A visszatérés alkalmából két olyan egyszerű kis Pivot-táblás tanáccsal kezdenék, ami az elmúlt üresjárati időszak során nekem időt takarított meg különféle feladatok során.

Az első megismeréséhez nézzük a következő adathalmazt:

pivot1.JPGCsináljunk belőle az Insert ribbonfül alatt, a Tables szekcióban található PivotTable funkcióval egy Pivot-táblát:

pivot2.JPGIde jutottunk:

pivot3.JPGAhogy látható, szűrőként használjuk a Régió oszlopban szereplő három értéket:

pivot4.JPGEnnél a háromnál talán még nem is ennyire látható a funkció hasznossága, de jóval több filter esetén mindenképpen hasznos annak a funkciónak az ismerete, amellyel szűrőfeltételként külön-külön sheetre szét tudjuk dobálni a Pivot-táblánkat. Ehhez nem kell mást tenni, mint a Pivot-táblán állva a megjelenő Analyze ribbonfül alatt kattintsunk a PivotTable szekcióban található Options menüre, majd azon belül a Show Report Filter Pages lehetőségre:

pivot5.JPGÉs íme, meg is van az eredmény:

pivot6.JPGNagyon fontos tudnivaló, hogy ilyenkor az adat nem változik, tehát az összes sheeten elérhető az összes régió adata!

A következő Pivot-tanács is egy egyszerű adattáblával kezdődik:

pivot7.JPGCsinálunk belőle egy teljesen normális Pivot-táblát:

pivot8.JPGIsmét igaz, hogy egy jóval komplexebb táblánál sokkal jobban látható lenne a bemutatásra kerülő funkció hasznossága, de a kérdés az, hogy hogyan adhatjuk meg a Pivot-táblán belül a a teljes darabszámra jutó sérült darabok százalékos arányát? A Pivot-táblán állva az Analyze ribbonfül alatt található Calculations szekcióban válasszuk ki a Fields, Items & Sets menüt, majd a Calculated Field lehetőséget:

pivot9.JPGEzután már csak az új mező (oszlop) nevét és a kalkuláció alapját kell megadnunk:

pivot10.JPGÉs meg is van a kalkulált oszlopunk, amelyre aztán további kalkulációkat építhetünk fel.

pivot11.JPG