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

2018. június 14. - Office Guru

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.

Egy igazán kemény Pivot-dió

Van a Pivot-tábláknak több, roppant módon idegesítő tulajdonsága, amelyek többségét azért beállításokkal lehet kezelni (például, hogy az adatok frissítésével ne állítsa mindig át az oszlopok szélességét), viszont van olyan, amelyet VBA nélkül már csak igen bajosan lehet megoldani. Ennek ellenére azért én most megpróbálkozom egy igazán kemény dióval. Adott egy kis Pivot-tábla, amelyben az értékek nagyság szerint vannak sorba rendezve:

pivot1_1.JPGEbben a táblában a harmadik helyezett érték (a 3. legnagyobb) alatt mindig szeretnénk egy vonalat látni:

pivot2_1.JPGAlapesetben ha változtatunk az adatforrásunkon, majd utána az Analyze ribbonfül Data szekciójából nyomunk egy Refresht, akkor máris bukott a mutatványunk:

pivot3_1.JPG

pivot4_1.JPGA jobb egérgombbal való kattintás után előhívható menüből megnyitható PivotTable Options alatt is hiába keresgélünk, bizonyos formátumoknál a "Preserve cell formatting on update" működik, itt viszont nem (akár ki, akár be van kapcsolva):

pivot5_1.JPGHa pedig még plusz sorokat is hozzáadunk, akkor meg végképp szétesett a tábla:

pivot6_1.JPGEgy lehetséges megoldás a kívánt cél elérésére a Conditional Formatting használata - álljunk a Pivot-táblánkra, majd a Home ribbonfül Styles szekciójában hívjuk le a Conditional Formatting legördülő menüjét és hozzunk létre egy új Rulet:

pivot9_1.JPGFigyeljünk rá, hogy az Apply Rule az összes értéket tartalmazó cellánkra vonatkozzon! A Rule típusa legyen "Format only top or bottom ranked values". A Top 3 értékre tegyünk először alsó bordert, majd ugyanezeket a lépéseket ismételjük meg, de akkor már úgy, hogy a Top 2 értékről távolítsuk el az alsó bordert, tehát ezzel elértük, hogy a 3. értékünk mindig aláhúzott lesz:

pivot10_1.JPGMost már lehet frissítgetni:

pivot8_1.JPGAz aláhúzás ott lesz szépen a harmadik helyezett érték alatt:

pivot11_1.JPGA megoldás hiányossága, hogy a Row Label oszlopban már nincs aláhúzás, másrészt a borderek formázási lehetőségei elég korlátozottak Conditional Formattingban. Ennek tükrében pedig a lecke fel van adva minden kedves Olvasónak - ha valakinek van nem VBA-s ötlete a fenti problémára, ne fogja vissza magát!

Automatikusan frissülő Data Validation List VBA nélkül

Valószínűleg a következő feladatot megoldani sokkal egyszerűbb lenne egy VBA-kódsorral, de miután az Excel formuláival elérhető lehetőségek tárháza végtelen és jómagam is mindig függvényekben gondolkodom először, a mai posztban egy automatikusan frissülő Data Validation Listet fogok bemutatni, amelynek legördülője menüje a gépelésünktől függően változik.

Normál esetben ugyebár valahogy így néz ki a Data Validation Listünk - van egy lista, ez van a legördülő menüben és görgethetünk bőszen, ha hosszú listáról van szó:

dvlist1.JPGAzt szeretnénk tehát elérni, hogy a Data Validation Listet tartalmazó cellában ha begépelünk egy pár betűt, akkor az alapján a legördülő menü már csak azt ezt a részletet tartalmazó eredményeket mutassa. Első lépésként ehhez kapcsoljuk ki a Data Validationt:

dvlist1_5.JPGAz értékeket tartalmazó cellák elé szükségünk lesz egy segédoszlopra, amely egy első ránézésre komplikáltnak tűnő képletet fog tartalmazni:

=IF(ISNUMBER(SEARCH($E$3,$I$3:$I$16)),MAX($H$2:H2)+1,0)

dvlist2.JPGMit is csinálunk itt és leginkább miért? A képletünk magja a SEARCH függvény, ami ugyebár az első paramétereként megadott szöveget, szövegrészt, betűt fogja megkeresni a második paramétereként megadott szövegben és ha megtalálja, visszaadja azt a karakterszámot, ahol ez kezdődik. Jelen esetben tehát a data validation listet tartalmazó cellába beírt karaktereket fogja a data validation listünk értéklistájában megkeresni és ha az adott cellában megtalálja a szövegrészt, akkor visszadob egy számot, ahányadik karakternél ez a rész kezdődik. Egy szimpla ISNUMBER segítségével pedig TRUE vagy FALSE értékre alakítjuk az eddigi eredményeket, tehát összefoglalva lesz egy TRUE értékünk azon cellák előtt amelyben az általunk begépelt karakterekből talált a SEARCH és egy FALSE azok előtt, ahol nem.

Végül pedig a listánk sorrendisége okán csinálunk még egy IF függvényt, azaz ha az eddigiek eredménye TRUE volt, akkor vegye a legelső cellától az aktuális celláig terjedő intervallumot, majd az ebben a tartományban lévő legmagasabb értékhez adjon hozzá egy egyest - ellenkező esetben legyen nulla az eredmény. Ahogy látható a fenti képen is, ezzel azt értük el, hogy szép növekvő számsorrendben jelennek majd meg azok a cellák, ahol a begépelt szövegrészünk megtalálható.

Most következik az a lépés, hogy ebből megcsináljuk a folyamatosan frissülő Data Validation Listet.

Menjünk át a munkafüzetünkön egy üres helyre, majd építsünk fel egy ilyen képletet:

dvlist4.JPG=IFERROR(VLOOKUP(ROWS($K$3:K3),H3:I16,2,0),"")

Ennek a képletnek a lelke a ROWS függvény, ami a paramétereként megadott tartományban megmutatja, hogy az adott cella hányadik sorban szerepel - jelen esetben tehát $K$3:K3 tartományban a K3 cella az 1, a $K$3:K4 tartományban K4 a kettő és így tovább. Utána ezt a számot fogjuk VLOOKUP-pal felkeresni az előbb létrehozott segédoszlopban és ha megtaláljuk, akkor kiíratjuk a mellette található listaelemet - így értelemszerűen annyi értékünk lesz ebben az új kis táblában, ahány helyen az előbb a SEARCH megtalálta a gépelt szövegrészünk.

Aztán már csak annyi kell, hogy egy IFERROR segítségével az N/A-kat üres cellára írjuk át.

Az eredmény pedig ez lett:

dvlist5.JPGTehát ha beírom a San szócskát a Data Validation Listre kijelölt cellába, akkor a segédoszlopban három helyen lesz számunk növekvő sorrendben és így a VLOOKUP három értéket fog a második segédtáblába tölteni.

Még egy példa:

dvlist6.JPGÉs most jön az utolsó lépés, hiszen az látható, hogy létrejött a Data Validation Listünkhöz tartozó adathalmaz és ez frissülni is fog a gépelésünk után, de a kérdés, hogy hogyan fogjuk elérni, hogy mindig csak annyi itemünk legyen a legördülő menüben, amennyi a segédtáblánkban megjelent? A választ az OFFSET és a COUNTIF párosa fogja megadni.

Én már kapásból a FORMULAS ribbonfül Defined Names szekciójában található Name Managert fogom frissíteni, ahol létrehozok egy új, nevesített tartományt, amelynek forrása egy OFFSET-COUNTIF kombó lesz:

dvlist7.JPGA képletünk tehát

=OFFSET($K$3,,,COUNTIF($K$3:$K$16,"?*"))

A beágyazott COUNTIF nem csinál mást, mint megnézi, hogy az új segédtáblánk rögzített területén hány darab, bármit is tartalmazó cella található, majd visszaad egy darabszámot - az OFFSET pedig az első paramétereként megadott cellától (ez most a $K$3, a segédtáblánk legfelső cellája) annyi sorral bővíti ki a tartományt, amennyit a COUNTIF visszaadott. Az OFFSET-nek ugyebár az első paramétere a sorszám, tehát hány sorral mozogjunk, második paramétere az oszlopszám, tehát hány oszlopot lépjünk - de ezek most nekünk nem kellenek, ezért van ott két tartalom nélküli vessző, a harmadik paraméter viszont a "magasság", tehát hány soros tartományról beszélünk.

És ezzel elkészült a DL_List nevezetű nevesített tartomány, amit már csak hozzá kell tennünk egy, a DATA ribbonfül Data Tools szekciójából beszúrható Data Validation List forrásaként:

dvlist8.JPGMég ne felejtsünk el átlépni ugyanezen az ablakon az Error Alert fülre, ahol ki kell kapcsolnunk a "Show error alert..." nevű checkboxot, hogy egyáltalán tudjunk gépelni a Data Validationt tartalmazó cellába:

dvlist8_5.JPGÉs ezzel elkészültünk, úgy működik, mint a karikacsapás:

dvlist9.JPG

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

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.