Tovább folytatva idegesítő alapbeállítások és funkciók elleni harcunkat Excelben, most vegyük górcső alá a negatív idő megjelenítésének problémáját, amely első látásra-hallásra nevetségesnek tűnik, de ha mondjuk egy munkaidő-nyilvántartó fájlt szeretnénk készíteni, akkor lehet jelentősége annak, hogy az előírtnál kevesebbet dolgoztunk. Persze máshogy is meg lehet ezt a problémát oldani, de ahogy a képen is látjuk, alapesetben ##### jut nekünk osztályrészül, ha simán kivonjuk egymásból időinket:

Egy rendkívül gyors, ám óvatosan kezelendő megoldás az Excel Options Advanced füle alatt elérhető "Use 1904 date system" bekapcsolása, amely után egyértelműen látható az, amit szerettünk volna elérni:

Ezzel azonban igencsak csínján kell bánnunk, hiszen eléggé gyorsan tönkrevághatjuk más, szintén dátumokra épülő képleteinket is ugyanabban a workbookban, szóval inkább maradjunk egy függvényes megoldásnál, amely a TEXT formula képében siet segítségünkre.

Excelben a szövegformátum elég sok olyan érték megjelenítését is lehetővé teszi, amelyeket mondjuk számként nem tudnánk elérni, például így van esélyünk a 0001 értéket beadnunk egy cellába anélkül, hogy az mondjuk automatikusan 1-re változzon és a szövegformátum azt is lehetővé teszi, hogy negatív időt jelenítsünk meg.

A TEXT függvény pedig pontosan ezt teszi, fogja az első paramétereként megadott értéket és a második paraméterként megadott formában szövegként adja vissza nekünk.

Jelen esetben ahogy a képen is látható, fogjuk két időpontunk különbségét, majd összehasonlítjuk a kötelező munkaidővel és az egész eredményt szöveggé alakítjuk:

=TEXT((F6-E6)-J3,"H:MM")

És ezzel kész is vagyunk, úgy jeleníthetjük meg az időnket, ahogy akarjuk, persze azért mindig koncentráljunk arra, hogy ledolgozott munkaidőnk sose menjen negatívba...

De álljunk csak meg a poszt végén még egy szóra, hiszen anno engem is majd megőrjített a kérdés, hogy mi szükség volt két időrendszerre az Excelben, tehát mi különbség van az 1904-es és 1900-as idők között? Linkelhetném a Microsoft hivatalos oldalát is, de alapvetően elég egyszerű a magyarázat: az 1900-as időszámítási rendszert anno azért fejlesztették ki, hogy kompatibilis legyen a Lotus 1-2-3 által készített táblázatokkal (amelyek ezt a számítási módot használták dátumaikhoz), míg az 1904-es rendszert azért, hogy a Macintosh felhasználói is használhassák az Excelt, hiszen a korai Mac gépek nem támogatták az 1904. január elseje előtti dátumok használatát. Szóval attól függően, hogy éppen Macről vagy Windowsról származó Excel fájllal szembesülünk, mindenképpen figyelembe kell vennünk ezeket a dátumbeállításokat, hogy későbbi problémákat el tudjunk kerülni.

Korábbi posztokban már feszegettem egy-két idegesítő, általában nem felhasználóbarát Excel-funkciót és az biztos, hogy erre a listára nálam a Pivot-táblák egyik igencsak fárasztó alapbeállítása, azaz értékeink alapból összegzése szerepel - hiszen ha csak megszámolni akarjuk az értékeket, az mindenképpen manuális állítgatást igényel. Vagy mégsem?

Elsőként azonban mindenképpen meg kell értenünk, hogy mi alapján defaultolódik értékeink kezelése a Pivot-táblában, hiszen ez nem beállítás függvénye, hanem adatainkon múlik. Ha tehát van egy ötvenezer számot tartalmazó listánk, akkor az a Pivotban biztos, hogy szummaként fog összegezni, ám ha akár csak egyetlen egy üres vagy szöveget tartalmazó cella is belekerül ebbe a listába, már egyből count lesz az alapbeállítás. Ugyanez igaz az errorokra (pl. mondjuk #N/A!) vagy éppen Boolean (TRUE, FALSE) értékekre is, tehát ha defaultolni akarjuk Pivot táblánkat értékkezelési metódusát, az adatainkat kell megvizsgálnunk.

VBA-ban persze meghekkelhetjük ezt a történetet is, de alapvetően fókuszáljnk az adatainkra, hiszen ez a legegyszerűbb megoldás, első körben például mindenképpen csak a konkrét adattartományunkat jelöljük ki, hiszen ha az 50 ezres listánkhoz még az oszlop további több tízezer üres sorát is hozzájelöljük, akkor egyértelmű, hogy mi lesz az alapbeállítás Pivot-táblánkban.

Legjobb fegyvertársunk ennek a problémának a felszámolásában az értékeink kijelölése után megnyomott CTRL+G parancsra előugró Go To lesz, annak is Special almenüje:

Tehát ha mondjuk üres celláinkat akarjuk eliminálni, akkor itt a Special képernyőn kattintsunk a Blanks rádiógombra majd okézzük le - így kijelöljük az összes üres cellánkat, majd ha beírtuk, mivel szeretnénk kitölteni őket (mondjuk egy nullával), akkor a CTRL+ENTER megnyomásával az összeset feltöltjük értékkel és máris megvan a default szummázás a Pivot-tábla értékeinél.

Ugyanígy ha a Formulas rádiógombra kattintunk a Go To Special almenüjében, akkor meghatározhatjuk, hogy mely eredményeket vizsgáljunk meg: nekünk értelemszerűen általában a hibák vagy a logikai eredmények (Igaz, Hamis) kellenek, hogy így generáljuk meg a default szummázást a Pivot-táblában.

Értelemszerűen ha a másik irányba szeretnénk eljutni, akkor egyszerűen adjunk hozzá egy plusz üres sort a kijelölésünkhöz és abból készítsünk Pivot-táblát, így pedig egyértelmű, hogy count lesz az alapbeállítás.

Biztos vagyok benne, hogy ez csak egyetlen megoldási lehetőség, hozzászólásban szívesen látok más ötleteket is!

Ha prezentációkról van szó, az nem kérdés, hogy már leáldozott a szöveges felsorolásokat és tömörebb szövegrészeket tartalmazó bemutatók korszaka, jelenleg minden a vizualitásról szól, minél jobban keltsük fel nézőközönségünk figyelmét és mégis, minél rövidebben, tartalmasabban mondjuk is el bemutatónk témáját, eredményeinket, céljainkat. Ehhez rengeteg eszközt felhasználhatunk, csinálhatunk képes diagramot, ahogy tegnap írtam, de készíthetünk egy következő évre vonatkozó tervet egy út ábrázolásával valamelyik diánkon.

Első lépésként fogjuk teljesen üres diánkat Powerpointban és a Ribbonunk Home füle alatt található Drawing szekcióból kiválasztva kezdjünk el rajzolni egy görbe vonalat, valahogy így:

Valójában ez lesz majd az út maga, csak az ahhoz vezető út még jópár formázással lesz kikövezve, elsőként a szintén a Drawing szekcióban található Shape Outline menüpont alatt állítsuk vonalunk színét feketére, illetve vastagságát legalább 100-150 pontosra:

Ha ezzel megvagyunk, akkor kapunk egy hatalmas nagy, görbe fekete vonalat, ezt kellene megdupláznunk, hiszen az utunkra még majd szükség lesz felezővonalra is és ha pontosan ugyanazt a nyomvonalat akarjuk követni, akkor nem rajzolhatjuk újra. Ergó szimplán CTRL+C és CTRL+V kombinációkkal duplázzuk meg a fekete vonalunkat:

Utána az újonnan létrehozott vonalunkra kattintsunk és formázzuk meg szépen, elsőként állítsuk Shape Outline alatt a színét fehérre, szintén ugyanitt a Weight alatt állítsuk néhány pontosra a méretét, majd húzzuk rá fekete vonalunkra:

Még mindig a fehér csíkot kijelölve irány vissza a Shape Outline és a Dashes menüpont alatt állítsuk szaggatottra a vonalat, hogy ténylegesen felezővonalunk legyen:

Amit már korábban, a diagramoknál megtanultunk, az most itt is fontos lesz, hiszen ki kell jelölnünk mindkét vonalunkat (a vastagot és a vékonyat is), majd jobb gombbal való kattintás után a Group alatt egyesítsük őket, hogy véletlenül se csússzanak majd szét. Ezután szintén jobb gombos kattintás után Format Shape menüpontba lépjünk be:

Itt a 3-D Rotation menüpont alatt állítsunk Presetet, méghozzá a perspektívások közül:

Következő lépésként zoomoljunk egy kicsit távolabb, majd tágítsuk ki az utcánkat jó nagyra, ezután pedig vágjuk ki és illesszük vissza, immár viszont képként:

Így képként tudunk már bánni vele, azaz használhatóvá válik a képekre vonatkozó plusz Ribbonfül, a Picture Tools Format elnevezésű fülecske, amely alatt Crop menü használatával átméretezhetjük és eltávolíthatjuk a nem szükséges részeket:

Tegyük is ezt meg és szépen méretezzük bele a kis utunkat a diába, valahogy így:

Ezután pedig már előttünk a lehetőségek tárháza, írjunk címet a diához, állítsunk be esetleg hátteret, animációt, szúrjunk be különböző formákból zászlókat, nyilakat vagy éppen táblákat, amelyekkel jelezni tudjuk évünk fontos fordulópontjait és végül tudásunk magabiztosságával felvértezve hasítsunk a prezentációval:

A következő rövid posztban igazából ismét egy akár haszontalannak is nevezhető Exceles megoldást fogok bemutatni, amely nélkül bőven lehet élni, de játéknak és esetleg diagramjaink feltuningolására, csinosítására tökéletes lehet, de haszonértékét majd mindenki eldönti saját maga - nekem tetszik.

Adott a következő kis táblánk, amelyet igazán különleges diagramban szeretnénk bemutatni, egy olyanban, amelyben az oszlopokból egy kép áll össze, de valamennyire mégis kiolvasható belőle az az adat, amely tökéletesen prezentálja eredményeinket:

Fogjuk ezt a kis táblánkat, majd Ribbonunk Insert füléről a Charts szekcióból szúrjunk be egy kétdimenziós oszlopdiagramot, ami most mindenképpen legyen Stacked diagram (a második ebben a sorban), hiába tűnik elsőre ez ostobaságnak.

Létrejött táblázatunk diagramterületére kattintva jobb egérgombbal (vagy akár CTRL+1 is működhet) hívjuk elő a Format Plot Area menüpontot:

Itt a Fill menüben válasszuk a Képpel való kitöltés lehetőségét és szúrjuk be azt a képet, amelyet fel akarunk majd használni az oszlopainkhoz:

Ez jelenleg még elég gagyi kis dolognak tűnik, de ne adjuk fel, lesz ez még bőven jobb:

Most viszont térjünk vissza picit eredeti táblázatunkhoz és adjunk hozzá egy plusz segédoszlopot, ami az én esetemben a MAX nevet fogja kapni és ide azt a differenciát számoltatom ki az Excellel, ami az általam kitűzött maximális eladási értékhez (150) képest elmaradás az adott városban:

Ezt a plusz oszlopot értelemszerűen hozzá is kell adnunk diagramunkhoz ezután, amit úgy tudunk megtenni, hogy magára a diagramterületre kattintva aktiváljuk a Chart Tools Ribbonfülcsoportot, annak Design fülén pedig megtaláljuk a Select Data menüpontot, amellyel szépen ki tudjuk bővíteni táblánkat.

Az így létrejött diagramban kattintsunk az egyik oszlopra majd hívjuk meg a Format Data Point menüt:

Itt a Series Options menü alatt elimináljuk az egyes oszlopok közötti távolságot nullára a Gap Width csúszka nulla százalékra húzásával:

Ezután kattintsunk függőleges tengelyünkre jobb gombbal és a Format Axis menüpont alatt állítsuk be minimumértéknek a nullát (vagy az elvárt minimum értékünket), maximumnak pedig a maximálisan teljesíthető/kitűzött maximumértéket:

Ha ez megvan, akkor ballagjunk szépen vissza ismét a diagramterületre és ezúttal válasszuk ki a kisebbik, segédoszlopon alapuló oszloprészünket valahol, majd itt is hívjuk meg a Format Data Series menüpontot és állítsunk be a Fill menü alatt valamilyen egyszínű hátteret:

Ezután kattintsunk tényleges értékeket hordozó oszloprészünkre, majd itt is hívjuk elő a Format Data Series menüt és Border Color alatt ugyanazt a színt állítsuk be, amelyet az előbb a segédoszlopon alapuló oszloprészre is belőttünk. Ezután Border Styles menüre lépjünk és állítsuk a határunk szélességét legalább 5-10 pt közötti értékre:

Legfontosabb lépésként pedig még ugyanitt a Fill menü alatt kapcsoljuk ki a kitöltést teljes egészében:

Ezzel pedig már kész is vagyunk, tuningoljuk fel még egy kicsit, színezzünk, adjunk hozzá magyarázatot, értékeket stb., aztán gyönyörködjünk munkánkban!

Nem vagyok egy túlságosan stresszes vagy sodrából könnyen kihozható ember, de eléggé szokott idegesíteni egy-két hiányosság/nehezen kezelhető funkció Excelben és ezek közül a lista egyik előkelő helyezettje az, amikor több cellában lévő szövegeimet szeretném egyesíteni Merge & Center segítségével, de mindig csak az egyik cella értékét tartja meg, a többi cellában szereplő adataim elvesznek.

Van ugyan egy beépített funkció a programban, amivel ezt elvileg ki lehetne küszöbölni, de ennek eléggé limitáltak a "képességei", például számokat, képleteket nem is tud kezelni, de ami a leginkább fájó, hogy csak azonos oszlopban szereplő cellákkal tud foglalkozni. Ez a Ribbonunk Home füle alatt található Editing szekcióban szereplő Fill, azon belül is a Justify. Adottak tehát a következő cellák (szám, képlet nélkül ugyanabban az oszlopban):

Szimplán jelöljük ki őket majd kattintsunk a Fill menüben a Justify menüpontra és lám, íme az eredmény:

Fontos, hogy oszlopunk olyan széles legyen, hogy beleférjen az egyesített szöveg!

A probléma megoldására vannak más lehetőségek is, használhatnánk függvényes módszert is, de ismét csak egy apró makrót rántsunk elő a tarsolyból, ami segíthet megoldani ezt a helyzetet - ezt aztán gombhoz rendelhetjük hozzá, de akár rávarázsolhatjuk Ribbonunkra is, mint saját kis funkció (erről a csodás kis lehetőségről még majd később) és utána bármikor használhatjuk.

Szóval adott a következő kis szöveg, különböző cellákban (ez a megoldás egyébként számokra és képletekre is ráhúzható):

Csinálhatnánk kapásból gombbal, de először csak írjuk meg a kis makrónkat, úgyhogy most menjünk át a Developer fül alól a VBA editor felületre, majd ott az Insert menü alól szúrjunk be egy új procedúrát, egy Subot (a végletekig ismételt fontos különbség a Sub és Function között, hogy utóbbi ad vissza értéket, előbbi nem), amit nevezzünk el biztosan felismerhető néven, nálam ez a MergeText() lesz.

Ezután deklaráljunk egy változót szöveg típusként, ami konkrétan majd az egyesített szövegünket fogja tartalmazni, ahogy szépen egyesével feltöltjük majd egy ciklus segítségével. Ha akarunk szóközt a celláink tartalma között, akkor mindenképpen célszerű CONST utasítással létrehoznunk egy konstanst, mondjuk Spacekarakter néven, ami egy szóközzel lesz majd egyenlő - ezt később még fel fogjuk használni.

Elég sok kódban láttam már és én is talán túlságosan bátran használom, de ha el akarjuk kerülni a felesleges hibaüzeneteket és a teljes leállást, akkor beszúrhatunk egy On Error Resume Next utasítást, ami az On Error utasítás azon szintaxisa, amely lehetővé teszi, hogy hiba esetén egyszerűen a következő sorra ugorjon a kis program és onnan folytassa a végrehajtást. Az On Errornak egyébként meghatározhatunk olyan kitételeket is, amellyel megmondhatjuk például neki, hogy hiba esetén mely sorra ugorjon a program - mondjuk oda, ahova beépítettük a hibakezelési részünket.

A következő lépés pedig már magának programunknak a lelke, egy For Each...Next utasítás, amely általánosságban tömbökre, tartományokra használható, hiszen különböző utasításokat hajt végre tartományunk minden egyes elemén.

Az alap szintaxisa szerint (még nem mélyednék bele nagyon):

FOR EACH "elem" IN "csoport/tartomány"
"fusson le/történjen meg ez"
NEXT "elem"

Ergó most
FOR EACH cell IN Selection
NEXT CELL

felhasználásával a FOR EACH és NEXT sorok közé beékelt utasítást az összes cellánkra le tudjuk majd futtatni. És hogy mi legyen ez az utasítás, azt már azért könnyen ki tudjuk logikázni, hiszen a fentebb már deklarált MergeText változót kezdjük el szépen feltölteni celláink értékével, valahogy így:

MergeText = MergeText & cell.Value & Spacekarakter

Mit is csinál ez? Először az első cellánk értékéhez (erre szolgál a cell.Value) hozzápakol egy szóközt (& karakterrel összefűzünk), majd betölti az akkor még üres változónkba. Következő lépésként az ebben a változóban már benne lévő "első cella értéke + Space" értékhez hozzápakolja a következő cella értékét és egy újabb szóközt és ezt egészen addig folytatja, amíg a kijelölésünk végéhez nem ér.

Ezután már az eredményünk ott csücsül a MergeText változóban, úgyhogy fel kellene csak használnunk, amit meg tudunk tenni nagyon egyszerűen is, betöltve egy teljesen egyszerű cellába, de akár használhatjuk a With...End With utasítást.

Ennek alap szintaxisa a következő:

With "mivel csinálja a következő utasításokat"
.Utasítás1
.Utasítás2
End With

Azaz azzal az objektummal, amit megadunk neki, több utasítást is végre tudunk hajtani, anélkül, hogy külön-külön ezeket be kellene adnunk a programnak.

Jelen példánkban mondjuk csináljunk ilyeneket:

Selection.Clear - kitörli a kijelölésünkből a tartalmat
Selection.Cells(1).Value - kijelölésünk első cellájának értéke a MergeText változó lesz
Selection.Merge - kijelölésünk összes celláját összevonja egy cellává
Selection.WrapText = True - ez pedig azért elég egyértelmű

Tehát így néz ki a teljes kód:

Ezután amire szükségünk van, az az, hogy ezt rendeljük hozzá egy gombhoz, amelyet a már megismert módon tudunk elkészíteni:

Innentől kezdve pedig láss csodát, gombunkra kattintva a fentebb felhozott példát simán egyesíteni tudjuk egy cellába, azaz megoldva ez az idegesítő probléma:

Tovább folytatva apró kis makrókról szóló posztsorozatomat, most következzen egy olyan kis VBA-kód leírása, amely alapvetően haszontalannak is nevezhető eredményt produkál, mégis mivel jelentősen javíthatja táblázataink kinézetét és felhasználhatóságát illetve mivel ez is tökéletes kiindulópont lehet egy komolyabb VBA-fejlesztgetéshez, amondó vagyok, hogy vágjunk bele!

A célállapot egyszerűen megfogalmazható, a lentebb látható kiindulótáblázatunkra ráhúzni egy olyan kódot, amelynek eredményeként aktív, kijelölt cellánk sorát és oszlopát is kijelöljük, megkönnyítve ezzel sokoszlopos, soksoros táblázataink áttekinthetőségét.

Erre is van többféle megoldás, én már láttam Conditional Formattingos és komplexebb VBA-s megoldást is, mindenesetre mostani megoldásunk első lépéseként kattintsunk jobb gombbal az adott sheetünk nevére majd a legördülő menüből válasszuk ki a View Code menüpontot:

A VBA-editor felületre jutunk a kattintás után, ahol a fenti két legördülő menüből egyrészt a Worksheetet kell választanunk, eseményként pedig a SelectionChange menüpontot, azaz adott sheetünkhöz rendelünk hozzá egy kijelölési eseményt. Ezután a program automatikusan létrehozza az eseményt:

Hozzunk ezután létre négy változót (hogy miért négyet, az mindjárt kiderül majd), mindet egész szám, azaz Integer típussal:

Sorszam változónkba majd az aktuális sor számát, Oszlopszam változónkba pedig az aktuális oszlop számát fogjuk betölteni, a másik két változó, x és y pedig egy olyan ciklusban lesz majd a segítségünkre, ahol az aktuális sorunk illetve oszlopunk számáig kijelöljük a tartományt. Remélhetőleg ez így érthető volt, ha mégsem, a későbbi lépéseknél egyértelművé válik majd.

Úgyhogy ahogy akkor csináljuk is meg az első két változónk feltöltését, az előbbi leírás alapján elég egyértelmű hogyan:

Célszerű ide beszúrni egy "kifehérítést" egy, celláinkhoz tartozó Interior objektum ColorIndexének fehérre állításával, azaz:

Cells.Interior.ColorIndex = 0

Ezzel már egyébként majdnem kész is vagyunk, hiszen most jön a legfontosabb része programunknak, a két For-Next ciklus. Ez a ciklus arra szolgál, hogy egy meghatározott VBA-kódot meghatározott számban futtassunk le, szintaktikája elég egyszerű:

FOR "Egyik változó" = Kezdőérték to "Másik változó"
Mit csináljon a kód
Next "Egyik változó"

Azaz az "Egyik változó"-ba betöltjük a kezdőértékünket (egyet, nullát stb.), majd innentől kezdve a másik változónkba töltött értékig annyiszor le fogja futtatni a kódot, amit a FOR és a NEXT közé ágyaztunk.

Azaz amit most tennünk kell, az az, hogy az első sortól (illetve oszloptól) addig a sorig és oszlopig megváltoztatni celláink hátterét, amekkora oszlop- illetve sorszámot a két erre kijelölt változónkba betöltöttünk, majd lezárni a ciklust.

Egy cella hátterszínét VBA-ban úgy tudjuk megváltoztatni, hogy fogjuk az érintett cellát, majd a hozzárendelt Interior objektum ColorIndexét megváltoztatjuk (a színek listája bárhol hozzáférhető könnyedén). Egy cellát úgy tudunk kijelölni, hogy használjuk a Cells(sor, oszlop) parancsot.

Tehát a következő képen szereplő kódrész már érthetővé is válik:

És ezzel kész is vagyunk! Ismételni tudom csak magam, tisztában vagyok vele, hogy nem egy bonyolult VBA-kódról beszélünk, de kiindulási alapnak az otthoni gyakorláshoz tökéletes lehet! Ötletek, problémák, kritikák jöhetnek!

Másoláshoz maga a kód:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Sorszam As Integer, Oszlopszam As Integer, x As Integer, y As Integer

Cells.Interior.ColorIndex = 0
Sorszam = ActiveCell.Row
Oszlopszam = ActiveCell.Column

For x = 1 To Sorszam
Cells(x, Oszlopszam).Interior.ColorIndex = 37

For y = 1 To Oszlopszam
Cells(Sorszam, y).Interior.ColorIndex = 37

Next y
Next x
End Sub

Az egyik dinamikus listás poszthoz érkezett hozzászólásban volt egy érdekes felvetés egymáshoz kapcsolódó dropdown menükről, azaz az egyikben kiválasztott érték meghatározza a következő értékét, majd az pedig az azután következő lista értékeit is, valahogy a példa szerint így:

Tehát ha kiválasztunk egy autómárkát, utána már csak ennek a márkának a típusai közül választhassunk, majd színeket is a specifikus típusokhoz rendeltek közül. Atzsnek volt egy jó kis megoldása (köszönet érte!) szintén hozzászólásban, de én is megosztanám a saját verziómat, hátha ebből is tud valaki ötletet meríteni. Halkan megjegyezném, hogy itt mondjuk már be lehetne dobni egy kis VBA-t is, de maradjunk a "sima" Exceles megoldásnál.

Ebben a megoldás szükségünk lesz egy segédtáblára, ami lehet mondjuk az elrejtett Sheet2-n vagy bárhol máshol és ebben a táblában kell szépen felsorolnunk, hogy az Autó listánk mit tartalmazzon, az adott autómárkához milyen típuslistánk legyen és az adott autó-típus kombinációhoz milyen színeket engedélyezünk. Valami ilyesmit csináljunk:

A következő lépésben Ribbonunk Formulas füle alatt a Defined Names szekcióban található Name Managert hívjuk elő és az összes különálló halmazunkra (autómárkák listája, adott típusok márkalistái és típusok színlistái) készítsünk egy elnevezett tartományt, a következő példa mintájára:

Végeredményünk pedig majd az összes speciális tartomány létrehozása után valami hasonló lista lesz majd (más-más nevekkel, de a lényeg, hogy értelemszerűen be tudjuk azonosítani a név alapján, mit tartalmaz az adott tartomány):

Ezután menjünk vissza arra a sheetünkre, ahol beszúrtuk a három leendő listánkhoz a helyet, majd álljunk az Autó listához kijelölt mezőnkre és a Ribbon Data füle alatt található Data Tools szekcióból szúrjunk be egy Data Validation Listet, amelynek forrása legyen az az elnevezett tartomány, amely autómárkáinkat tartalmazza:

Következő lépéssel már majdnem ott is vagyunk a végén, álljunk szépen arra a mezőre, ahol a típust akarjuk majd az autó márkája alapján listázni és ide is szúrjunk be egy Data Validation Listet, amelynek forrása az Autó listánk helye, jelen esetben C4 cella lesz, kibővítve a már megismert INDIRECT függvénnyel, azaz a meghivatkozott C4 cella értéke alapján (jelen esetben egy autómárka neve) fogja megkeresni az ezen a néven futó tartományt, tehát automatikusan frissíti majd ezt a listát az elsőnél kiválasztott érték alapján:

A SZÍN cellánál pedig értelemszerűen ugyanezt a metódust kell követnünk, csak ott a Data Validation List forrásaként a típus kiválasztására kijelölt cellát kell megadnunk, tehát az E4-es cellát:

És kész is vagyunk:

Egyszerűbb táblázatok, kisebb listák esetén nem nagyon van értelme az ilyen automatizmusnak, de igazán komplex adathalmazok, felhasználói beavatkozást igénylő listák vagy éppen feltuningolt dashboardok esetén mindenképpen hasznos lehet egy olyan VBA-megoldás, amellyel felhasználó által kiválasztott paraméterek mentén automatikusan tudunk filterezni. A következő posztban erre mutatok egy egyszerűbb példát, amely az érdeklődők számára már jó kiindulási alap lehet további műveletek automatizálására.

Fogjuk a következő mintatáblánkat, amely megyénként és városonként tartalmaz bevételi értékeket - erre fogunk majd gyorsan egy kis automatikus szűrést varázsolni:

Ezután, bár nem létfontosságú, de célszerű beszúrni egy táblát a Ribbon Insert füle alatt található Tables szekció Table menüje segítségével a kis táblázatunkra, hogy később aztán könnyebben hivatkozhassunk rá:

Ha ezt megtettük, egyrészt megjelenik a Ribbonon a táblára használható Design fül, ahol egyrészt csinosítgathatjuk listánkat, másrészt itt láthatjuk azt is, hogy mostantól Table2 néven hivatkozhatunk majd rá (persze ez átírható bármi másra).

Következő lépésként döntsük el, hogy pontosan hogy is akarunk majd szűrést automatizálni, tehát például drilldown menüből válasszon majd a user és arra szűrjünk makróval vagy szövegboxba írja be a szükséges információkat stb.

Én most a példámban szövegboxba fogom bekérni az információkat (de ugyanezzel a logikával működik más elemekkel is), úgyhogy most szépen menjünk a Ribbon Developer fülére és a Controls szekcióban az Insert menü alól szúrjunk be két Active-X-es szövegdobozt, majd valami kis leírást is mellékeljünk, hogy mit is várunk ezekbe a dobozokba:

Vegyük észre, hogy a boxok beszúrásával a Controls szekcióban be is kapcsoltuk a Tervező (Design) módot, amelyre azért lesz szükségünk továbbra is, mert mindkét szövegboxunkat meg kell kicsit formáznunk. Kattintsunk jobb gombbal az egyiket kijelölve, majd a legördülő menüből válasszuk a Properties menüpontot:

Itt két dolgot tegyünk meg (tehetünk többet is, de maradjunk az egyszerű lépéseknél), egyrészt a BackColor tulajdonságnál válasszunk ki más háttérszínt (engem megőrjít a szimpla fehér) illetve a LinkedCell tulajdonságnál adjuk meg mondjuk a Q1 cellát:

Ezt ismételjük meg a másik szövegboxnál is, de ott Q2 cellát kapcsoljuk hozzá a mezőnkhöz. Ezután egyébként a Q oszlopot akár el is rejthetjük, hiszen ezekre a segédcellákra másoknak nem lesz szüksége (igen, tudom, hogy ez csak felesleges lépés, de haladjunk szépen lassan a folyamatok megértésében). Ezzel egyébként azt tettük, hogy a szövegboxainkba beírt értékek bekerülnek majd a Q1 illetve a Q2 cellákba.

A hangulat kedvéért még varázsoljunk egy gombot is a táblánk mellé szintén az Insert menüből a Command Buttonra való kattintással, majd utána ha már létrehoztuk, jobb gombbal itt is menjünk be Properties menübe, hogy legalább a Caption tulajdonságot módosítsuk és beírjunk valami szöveget a gombunkhoz, valahogy így:

Nagyjából most itt tartunk, sok már nincs hátra, hogy működőképes legyen az aprócska kis makrónk:

Innen jön a VBA-lépés, ahova úgy jutunk át legegyszerűbben, hogy jobb egérgombot nyomunk a gombunkon, majd a View Code menüre kattintunk:

Ezzel pedig létre is hoztuk a kattintás eseményt, azaz az eseményt, amely akkor fog bekövetkezni, ha userünk rákattint a gombra:

Innentől pedig már csak két lépés és két sornyi kód van hátra, méghozzá a következő kód két sorban és két verzióban:

ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=2, Criteria1:=[q1]

Mit is tesz ez a sor? Fogja Table2 tartományunkat az aktív sheeten (ha szimplán csak összeolvassuk a kód első részét, akkor is értelmezhető ez), majd tesz rá egy Filtert - ezután pedig szűrni fog a tábla második oszlopára (Field:= paraméter határozza meg) a Criteria1:= paraméternél megadott értékre, jelen esetben Q1 cellára, ahova az első szövegboxunkba beírt eredményt tároltuk be.

A másik sor a fentiek és a második szövegboxba kért információ alapján:

ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=3, Criteria1:=">[q2]"

Az aktív sheeten fogja Table2 táblánkat, tesz rá egy filtert, majd a tábla harmadik oszlopában le fog szűrni a kritériumban megadott paramétert teljesítő értékekre, azaz azokra, amelyek értéke nagyobb, mint a Q2 cella (figyeljünk rá, hogy Q2-ben számként legyen az értékünk!).

Most menjünk vissza az Excelhez, a Controls szekcióban kapcsoljuk ki a Tervező módot majd próbálgassuk csak művünket:

Ugye milyen izgalmas volt ez az egyszerű feladat, ha még nem mélyedtünk el VBA-ban?

Csak okulásként álljon itt egy lista a táblákkal kapcsolatos egyéb VBA-parancsokról, amelyet mindenképpen használjunk fel egy kis gyakorlásra szobánk áldásos magányában:

Több, bizonyos szempontból megváltozott vagy nehezen megszokható újítás mellett az Excel 2013 végre olyan funkciókat is hozott magával, amelyek létezéséről éveken át csak álmodozott a mezei Excel-user és itt a blogon is írtam már olyan problémákról, amelyeknek megoldása már nem függvény vagy képletezés, hanem egy beépített funkció a program 2013-as verziójában - jelen esetben a különösen hasznos és a mindennapi feladatokat segítő Flash Fill funkciójáról lesz szó.

Mire is használható ez a tényleg varázslatos kis funkció? A következőkben ezt fogom három példán illusztrálni és mindenkit csak arra buzdítok, hogy barátkozzon vele meg minél előbb. A funkciót magát Ribbonunk Data fülének Data Tools szekciójában találjuk: Egy jó lehetőség a használatára pont az a példa, amit az egyik hozzászóló kérdezett valamelyik nap, azaz meghatározott ismétlődések végrehajtása, jelen esetben például az, hogy egy lakcímből kinyerjük a házszámot:

Az első két sorhoz szimplán írjuk be mi a házszámot és innentől kezdve bízzuk magunkat a Flash Fillre, amit vagy a konkrét funkcióra kattinva vagy a CTRL+E billentyűkombinációt lenyomva hívhatunk meg, ha elfogadjuk a felkínált kitöltési metódust:

Ugyanígy alkalmazható akkor, ha egy szövegből szeretnénk kinyerni meghatározott minta szerint bizonyos szövegrészeket - például a következő példában azt, hogy milyen nemzetiségű turistákról beszélünk:

Összefűzésre is tökéletesen használható, azaz ha egyszer megadjuk a funkciónak, hogy milyen sorrendben, milyen minta szerint pakolja össze celláink tartalmát, a Flash Fill szépen megspórolja nekünk a függvényezést, Autofillezést és mindezt egy lépésben végrehajtja.

Sőt, függvényekre is használható, ha például egy függvényünkben az egyik paraméter egy cellában található érték bizonyos formája, akkor ha ezt a módosított formájában (tehát mondjuk csak egy keresztnevet egy névből) írjuk be a formulához, akkor a Flash Fill szépen ezzel a logikával fogja meggenerálni a többi sorhoz is a függvényt.

Ugye milyen jó kis barátot találhatunk a 2013-as Exelben?

Nemrégiben szembesültem egy érdekes problémával Excelben a mindennapok során, amelyre lehet, hogy többeknek már régóta ott volt a megoldás a tarsolyában, de okulásként és hát azért, hogy minden nap tanuljunk valami újat, megosztanám azt a megoldást, amire én jutottam - aztán ha valakinek van jobb, más ötlete, jelezze bátran!

Valószínűleg Data Validation listát és a hozzá tartozó legördülő menüt bárki tud készíteni Excelben, ez egy nagyon hasznos és szép funkció, ahogy látható is ezen a képen: 

Könnyedén el tudjuk készíteni a Ribbonunk Data fülének Data Tools szekciójában található Data Validation funkció segítségével, aztán a listából kiválasztott/validált értékre építhetünk a későbbiekben függvényeket, más műveleteket is, tehát VBA ismeretek nélkül is tudunk egy kis automatizmust vinni táblázatainkba - ezzel szerintem senkinek semmi gondja.

Na de mi van akkor, ha menet közben bővül a listánk és meg szeretnénk takarítani azt az időt, amelyet a validációnk bővítése jelentene? Persze, lehet erre azt mondani, hogy jelöljük ki validációra a full oszlopot vagy tartományt, ami megoldás lehet (és mennyire straightforward!), de azért adjuk meg magunknak azt a bizalmat, hogy ennél többre is képesek vagyunk.

Ugyanis erre (és sokminden másra) a dinamikus frissítésre tökéletesen használható az Insert menü Tables szekciójában található Table funkció, úgyhogy szépen jelöljük is ki autóink listáját és szúrjunk be rá egy táblázatot:

Ezután a Formulas Ribbonfül Defined Names szekciójában található, roppantul hasznos és használatra igen ajánlott Name Manager funkcióhoz forduljunk és hozzunk létre egy új, általunk választott névre hallgató tartományt, amely az előbb létrehozott táblánkra mutat, valahogy így:

Innentől pedig adja magát mit is kell tennünk, szépen menjünk vissza a Data fülre és a Data Validation alatt módosítsuk úgy listánkat, hogy ne konkrét cellatartományt adjunk meg, hanem az előbb elnevezett tartományunkat:

És ezzel kész is, szépen automatikusan bővülni is fog a lista validációnk, ahogy bővítjük magát a listát - és ezt a logikát, ötletet tucatnyi, hasonló kérdéskörben lehet hasznosítani.

A poszt végére kicsit más, afféle extra, mostanában kaptam a kérdést egy szűrési problémával kapcsolatban, amelyre a választ az Excel korlátai jelentik: azaz 10 ezernél több értéket tartalmazó listánkba szűrőt beszúrva ne lepődjünk meg, ha a filter legördülő listájában néhány értéket már nem látunk viszont, ugyanis a filter maximális legördülő listás korlátozása 10 ezer, afeletti értékszám esetén már nem kapunk teljes képet a drilldownban.