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:

Ez meg a kis kalkulációs táblánk lenne, szimplán cellákba betöltve:

És valami ilyesmi eredményt akarunk elérni:

Lá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:

Valahogy így fog majd kinézni:

Azt 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:

Userform 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:

Ahogy 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?

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:

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

Ahogy 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:

Fogtam é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:

Ez 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:

Lá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:

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:

Ahogy 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:

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

Itt 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:

É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:

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

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:

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

Innen szúrjunk be szépen egy Microsoft Web Browser controlt:

Ha 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:

Ezutá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:

Ehhez 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:

Ugyebá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:

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

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:

Valami ilyesmit fogunk látni:

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

Ezutá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:

Helyezzük egy üres munkalapunk valamelyik cellájára:

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

Innentő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:

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

=WEBSERVICE(A1)

A 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.

Ezutá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.

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:

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

Alapesetben 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:

A 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):

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

Egy 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:

Figyeljü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:

Most már lehet frissítgetni:

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

A 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!

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ó:

Azt 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:

Az é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)

Mit 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:

=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:

Tehá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:

É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:

A 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:

Mé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:

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

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:

Lá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:

Itt 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:

És mehet is a Load:

Pá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):

Ha 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:

Itt ú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:

Viszont 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:

É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):

Í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:

Ha 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:

De 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 ahogy ezen a Dél-Amerika térképen látható:

Tisztá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.

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:

Ez 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:

É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:

Ennek 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:

Ennek lenyomása után eltűnik a munkafüzet:

De 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?

Viszont, 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:

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:

Ezt 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:

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

Lá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):

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

De 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:

Ezzel 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.

É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:

A 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:

Ha 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:

Ezutá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:

É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:

Itt 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):

Szó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:

Hogy 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:

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.