A Calculation Options megtévesztő csapdái

2018. szeptember 09. - Office Guru

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?

Tucatnyi soros alábontás lehetősége Pivot-táblában

Olvasói kérdés kerül terítékre a következő rövid irományban, amely önmagában nem mutat be semmi új funkciót vagy lehetőséget, de egyrészt remélhetőleg segítségül szolgálhat többeknek, másrészt talán más megoldási ötletek is felmerülnek a többi Olvasótól. A kérdés az volt, hogy innen:

innen1.JPGHogyan juthatunk el ide, úgy hogy közben a második táblánk mindig automatikusan frissül vagy frissíthető lesz majd:

innen2.JPGAhogy látható, az a cél, hogy a boltonkénti row label bontás után még legyen további alábontás row label tekintetében, azaz látható legyen egy helyen, hogy mondjuk "a" bolt esetén mennyi a darabszám vagy éppen az ár.

Ahogy megláttam a kérést, azonnal tudtam, hogy a választ egy Pivot-táblában keresném, csak hát nem igazán sikerült a standard lehetőségekkel előidéznem az elvárt formát. Tehát ebből a helyzetből indultam ki:

innen3.JPGFogtam és átalakítottam úgy az alaptáblázatot, hogy betömörítettem a darabszámot és az árat egy oszlopba, mellé pedig egy másik oszlopban kategorizáltam, hogy éppenséggel milyen szám szerepel az előző oszlopban, darabszám vagy ár:

innen4.JPGEz elkészíthető az eredeti táblázatból befüggvényezve, táblák használatával - azaz ha valaki beír a "price" oszlopba egy értéket, akkor az új, átalakított táblánkba kerüljön egy "price" kategóriás sor, ami az ár értéket fogja felvenni.

Ezután ebből az új táblából az Insert ribbonfül alatti Tables szekció PivotTable funkciójával beszúrunk egy Pivot-táblát, úgy hogy a "fruit" az oszlop, a "shop" és a kategóriánkat tartalmazó oszlop a sorok, az árat és a darabszámot ömlesztve tartalmazó oszlop szummája pedig a value:

innen5.JPGLátható, hogy meg is van az elvárt eredményünk - ha mégsem ezt látjuk azonnal, az azért van, mert még a PivotTable Tools Design ribbonfüle alatt a Report Layoutot át kell állítanunk tabuláris megjelenítésre:

innen6.JPG

Egy tájékoztató üzenet és magyarázata

A következő írásban egy nem túlságosan fajsúlyos, ám annál idegesítőbb Exceles-problémát fogok több oldalról körbejárni, amelyhez az apropót az adja, hogy a napokban én is jópár percet töltöttem a következő üzenet körüli kérdésekkel. A kiinduló helyzet szerint kezünkbe kerül egy Excel-fájl, amelyben dolgozunk, frissítünk cellákat stb., majd amikor menteni szeretnénk, a következő üzenettel szembesülünk:

capture1_2.JPGAhogy látható, ez nem hibaüzenet, hanem egy tájékoztatás, ami arra hívja fel a figyelmünket, hogy a fájlunkban olyan személyes adatok lehetnek, amelyet még a Document Inspector sem tudna eltávolítani (már persze ha lefuttatnánk). Miután a Document Inspector futtatását (és találatainak törlését, figyelembe vételét) a felhasználók többsége nagy ívben elkerüli, ezért az első gondolatunk biztosan az, hogy hol lehetne ezt az üzenetet kikapcsolni. Nos, Excel ribbonunk File fülére kattintva válasszuk az Options menüpontot, azon belül pedig a Trust Centert és a Trust Center Settings gombot:

capture5_2.JPGItt a baloldali menüsoron legalul bújik meg a Privacy Options, amely lap közepén található a Document-specific settings szekció:

capture2_2.JPGItt vegyük ki a pipát a "Remove personal information..." lehetőség kockájából és garantáltan nem jelenik meg többé a fenti üzenet. Persze ezzel vállalunk is egy kis kockázatot, hiszen így már figyelmeztetésünk sem lesz, hogy esetleg személyes adatok maradnak a fájlunkban (szerző neve stb.) - ami a mai GDPR-tól sújtott vérzivataros időkben fontos kérdés is lehet.

Alapvetően tehát ha nem nagyon érdekelnek minket bizonyos személyes információk a fájljainkban és az sem zavar mondjuk, hogy felhasználóink megtudják, hogy mi készítettünk egy bizonyos fájlt, akkor a fent említett a legjobb megoldás kétség nélkül.

Viszont ha óvatosabbak akarunk lenni, akkor van egy másik út is. Alapvetően ugyanis ez az üzenet akkor jön elő, ha lefuttattuk a Document Inspectort a fájlon (ezt a File ribbonfül Info menüjében tudjuk megtenni), ahol a futtatás után látjuk is, hogy mi a probléma:

capture4_2.JPG

capture3_2.JPGÉs ahogy látjuk, az Excel maga esélyt is kínál arra, hogy másképp oldjuk meg a kiinduló problémánkat az üzenettel, ugyanis ha az előző képen látott "Allow this information to be saved in your file" funkciót választjuk az Inspect után, akkor onnantól kezdve már többé nem jelenik meg a fenti üzenet - úgy, hogy közben a Privacy Options alatt a pipát nem is bántottuk.

De akkor sem kell megijedni, ha a poszt olvasása közben benézünk az Excel Privacy Options menüpontjába és azt látjuk, hogy az első megoldásként felkínált "Remove personal information..." lehetőség egyáltalán nem is aktív:

capture6_2.JPGEz ugyanis azt jelenti, hogy még nem is futott Document Inspector a fájlon, tehát nem fogunk figyelmeztető üzenettel szembesülni sem.

HTML-kód használata Excelben

A legutóbbi, RSSekre vonatkozó poszt sokakban elindított valamit, jópár kérdés érkezett hozzám a témában azóta, úgyhogy a következő poszt valamilyen szinten hasonló témát fog feszegetni, méghozzá HTML használatát Excelben. Tehát adott például egy HTML-kód az időkép "third party" oldalán, amit szeretnénk használni a dashboardunkban valamilyen módon. A most bemutatásra kerülő módszer alapvetően nem az egyetlen lehetőség, de szerintem a leggyorsabb és legegyszerűbb. Első lépésként rakjuk be valamelyik cellánkba a kódot:

web1.JPGHa ezzel megvagyunk, akkor a Developer ribbonfül Controls szekciójának Insert menüjében lépjünk a More Controls alpont alá:

web2.JPGInnen szúrjunk be szépen egy Microsoft Web Browser controlt:

web3.JPGHa esetleg valakinek ez nem működne és kapna egy "Cannot insert..." hibát, akkor a registryt kell egy kicsit hegeszteni, hogy pontosan mit, azt a Microsoft hivatalos oldalán megtaláljuk - ha mégis kellene valakinek segítség, kommenteljen.

Tehát most valahol itt tartunk:

web4.JPGEzután szintén a Developer ribbonfül Controls szekciójából szúrjunk be egy egyszerű kis gombot, ami majd végrehajtja nekünk az átalakítást:

web5.JPGEhhez pedig rendeljük hozzá VBA-editorban (ALT+F11 vagy a context menüből az Assign macro menüpont) a következő kódot:

Sub Button6_Click()
WebBrowser1.Navigate ("about:blank")
WebBrowser1.Document.Write (Range("C2"))
WebBrowser1.Refresh
End Sub

Mit is csinálunk most? Ugyebár a beszúrt WebBrowserünk az egyes számú, tehát az első sorban a WebBrowser1, Excelben működő kis böngészőjét üres képernyőre váltjuk, ez kötelező, hogy először betöltsünk egy valid, üres lapot. A WebBrowswer control maga egyébként azt teszi lehetővé, hogy az Internet Explorer böngésző funkcionalitását használjuk más applikációkban vagy éppen HTML-dokumentum megtekintőként is felfoghatjuk. A második sorral pedig konkrétan a C2 cellában megadott HTML-kóddal fogjuk feltölteni a WebBrowser objektumunkat, ergó megnyitjuk azt az oldalt, amit a C2-ben megadtunk, majd még ráküldünk egy Refresht, azaz újra betöltjük a jelenleg megjelenített dokumentumot a WebBrowser controlba.

Ezután ha rákattintunk a gombunkra, a következő fog történni:

web7.JPGUgyebár a HTML-kódunkban a linknek adtunk egy Display nevet, ez a Click here, ha rákattintunk a browserben, máris megy a történet:

web8.JPG

Értelemszerűen a Click here-részt kiiktathatjuk, ahogy a gombot is, de talán így jobban követhető volt a dolog.

Webservice - RSS-ek használata Excelben

Egy kedves Olvasóm építget egy kis Exceles dashboardot, amelybe szeretne egy egyszerű időjárásos szekciót beépíteni, úgyhogy a következő posztban bemutatok egy ilyen, automatikusan frissülő adatokkal operáló megoldást. Amúgy ez az igény hasonló a legtöbb Exceles feladathoz - nem csak egyetlen megoldás létezik: jelen esetben például nyugodtan fordulhatunk a Power BI-hoz, de VBA-ból is leprogramozhatunk néhány soros kóddal egy ilyen frissülést, a mai poszt viszont kizárólag Excelből fog operálni.

Első körben válasszuk ki, hogy melyik oldalról akarjuk beemelni az időjárási adatainkat, fontos, hogy az adott szolgáltató elérhetővé tegye xml formátumban (rssként) is az előrejelzését. Én most a köpönyeg.hu-t választom, ahol szimplán csak az rss-ikonra kattintok a jobb felső sarokban az oldalon:

1_4.JPGValami ilyesmit fogunk látni:

3_4.JPGMegfogom a böngésző címsorából az elérhetőséget és vágólapra helyezem:

2_4.JPGEzután az Excel Data ribbonfülén a Get External szekcióban a From Web opciót válasszuk, majd a felugró ablakban adjuk meg az RSS-elérhetőségünket:

4_4.JPG

5_5.JPGHelyezzük egy üres munkalapunk valamelyik cellájára:

6_4.JPGLátható, hogy meg is érkeztek a később már automatikusan is frissíthető adataink:

7_4.JPG

8_3.JPGInnentől kezdve pedig már csak be kell hivatkozni a megfelelő cellát a dashboardunk megfelelő részéhez és olyanra formázni, amilyenre szeretnénk. De ez volt egyébként a létező legegyszerűbb megoldás, ennél egy fokkal hatékonyabb és szebb megoldás a Webservice formula használata. Ezt a következőképp tudjuk megcsinálni.

Elsőként illesszük be az xml-es rss linkünket egy cellába a munkalapon:

9_1.JPGEzután egy másik cellába nyerjük ki a linkről az adatokat a WEBSERVICE nevű formula segítségével:

=WEBSERVICE(A1)

10_1.JPGA Webservice-formula a webről hoz be nekünk adatokat a paramétereként megadott URL alapján és azonnal látható is lesz, hogy abban a cellában, ahova a fenti formulát beírtuk, meg fog jelenni az xml tartalma.

Már ebből is lehet hegesztgetni, hogy kinyerjük az adatokat, de a Webservice-függvény általában kéz a kézben jár a FILTERXML formulával, ami egy XML-tartalomból ad nekünk vissza meghatározott adatot a második paramétereként megadott XPath alapján. Anélkül, hogy bármilyen mélységben belemennénk ebbe, az XPath az ún. XSLT-standard részeként arra használható, hogy egy XML dokumentum attribútumain vagy elemein navigáljunk keresztül.

Tehát egy újabb cellába írjuk be a következőt:

=FILTERXML(A3,"(//description)[last()]")

Az első paraméter, az A3, az a cella, ahova az előbbi WEBSERVICE formulával kinyertük a valid XML-formátumú tartalmunkat, a második paraméter pedig az XPath-formátumban megadott szövegrész, amit az XML-ben akarunk levadászni.

//description beírásával az XML-ben megkeressük az első Description előfordulást, az utána következő [last()] argumentummal pedig azt érjük el, hogy ne az első, hanem az utolsó Description előfordulást kapjuk vissza - hiszen az XML-ben, ha jól megnézzük, ott van az, amit keresünk.

11_1.JPGEzután már csak annyi van hátra, hogy egy SEARCH formulát és egy MID valamint egy LEN formulát egybeágyazzunk (vagy akármilyen más darabolós megoldást), hogy kinyerjük azt a szövegrészt, amit akarunk - gondolok itt mondjuk a helységre vagy az adott napi hőmérsékleti adatokra. És ezzel már meg is van az automatikusan frissülő időjárási adatunk, már csak bele kell varázsnolnunk egy szép napocskás, felhős, esős stb. formátumú mini-dashboardba.