A mindennapok során elég gyakran felmerül olyan igény, hogy Excelben elkészített táblázatainkat, diagramjainkat PowerPoint prezentációban mutassuk be, adjuk elő egy kis extra körítés kíséretében - és szintén nem ritkaság, hogy hetente ugyanarról a témáról, ugyanabban a struktúrában kell prezentálnunk, egyedül csak az adatok változnak. Ilyenkor jól jöhet egy olyan automatizmus, amelynek keretében az Excelből egy gombnyomásra tudunk PowerPoint diára helyezni dolgokat - a következőben egy ilyen, egyszerű kis VBA kódot fogunk megnézni.

Adott tehát a következő diagram, amelyet Excelben készítünk el minden héten a bevételek heti alakulása alapján:

Nyissuk meg ALT+F11 lenyomásával a VBA-editort, majd mielőtt elkezdenénk a kódot bevinni, végezzünk el egy beállítást. A Tools menü References menüpontjára kattintás után rendeljük hozzá projektünkhöz a Microsoft PowerPoint Object Libraryt:

Aztán már mehetünk is az editorba, hogy elkezdjük a tényleges munkát. Első lépésként mint mindig, most is deklaráljuk a változóinkat:

Sub ChartcopytoPPT()
Dim PowerPointApp As Object
Dim Presentation As Object
Dim PPTSlide As Object

Ahogy elég egyértelmű már az elnevezésből is, az első maga a PowerPoint applikáció, a második azon belül egy prezentáció, a harmadik pedig értelemszerűen egy dia - és elég egyértelmű, hogy ezekre miért is van szükségünk.

Hasonló kódok esetében ilyenkor általában mindig célszerű olyan vizsgálatot végezni, hogy fut-e már a Powerpoint vagy még nem, jelen kódban én most megengedem magamnak azt a luxust az egyszerűség jegyében, hogy ezt az ellenőrzést kihagyom. Így a változók után már indíthatunk is egy PowerPointot a PowerPointApp változó feltöltésével:

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

Ez az egyszerű kódocska nem fog túl nagy problémát okozni, de azért, hogy jól belénk ívódjon, célszerű belőni egy

Application.ScreenUpdating = False

utasítást is, ami nagyobb kódok esetén egyértelműen gyorsítja a futást, hiszen a képernyő nem frissül folyamatosan a kód futása közben.

Ha ez megvan, akkor jön a következő két változó feltöltése, elsőként a Presentation változó kezdő értékét állítjuk be egy új prezentáció létrehozásával:

Set Presentation = PowerPointApp.Presentations.Add

Majd folytatjuk azzal, hogy hozzáadunk egy diát ehhez a prezihez:

Set PPTSlide = Presentation.Slides.Add(1, 11)

Az első része ennek a feltöltésnek valószínűleg egyértelmű, az Add két paramétere közül az első pedig a slide számát jelenti, a második pedig az adott slide kinézetét, layoutját - jelen esetben ez egy szöveges headert tartalmazó dia lesz, a teljes lista megnézhető a Microsoft hivatalos oldalán.

Ezután már csak annyi van hátra, hogy megfogjuk az Excelben lévő diagramunkat és nyomunk rá egy másolást:

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Copy

Ez önmagában elég favágó kis kód, hiszen nevesítjük a pontos chartot, de értelemszerűen akinek több ilyen van, az nyugodtan bedobhat egy For ciklust, amellyel az összes chart objektumon végigmehet és mindegyiket átdobálhatja a PowerPointba.

A másolás után már csak a beillesztés van hátra:

PPTSlide.Shapes.PasteSpecial DataType:=2

Az adattípus paraméter sorszámát nem kell fejből tudnunk az összes változatra, a Microsoft hivatalos oldalán ott van az egész PasteSpecial metódus leírása pontos paraméterlistával - például jelen esetben a kettes egy default beillesztés, a kinézet és formátum megtartásával.

Ezután már csak annyi van hátra, hogy aktiváljuk és előhozzuk az eddig háttérben lévő PowerPointot, majd kipucoljuk a vágólapot:

PowerPointApp.Visible = True
PowerPointApp.Activate
Application.CutCopyMode = False

Ezután már csak hozzá kell rendelnünk a makrónkat egy gombhoz és egy kattintás múlva már elő is állt a prezentációnk:

Ahogy látszik, ez egy roppantul lebutított, egyszerű kis kód simán tehetünk bele mindenféle módosításokat a chartunk elhelyezkedését, méretét illetően, de elég könnyen ráhúzhatjuk több chart másolására is. Remélhetőleg ugródeszkának a fenti megfelelő lesz.

Kódunk szövegként:

Sub ChartcopytoPPT()
Dim PowerPointApp As Object
Dim Presentation As Object
Dim PPTSlide As Object
Set PowerPointApp = CreateObject(class:="PowerPoint.Application")

Application.ScreenUpdating = False

Set Presentation = PowerPointApp.Presentations.Add
Set PPTSlide = Presentation.Slides.Add(1, 11)

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Copy

PPTSlide.Shapes.PasteSpecial DataType:=2

PowerPointApp.Visible = True
PowerPointApp.Activate

Application.CutCopyMode = False

End Sub

Bármilyen Exceles Dashboardot vagy űrlapot készítünk, mindig nagyon jól mutat, ha valamilyen extrával meg tudjuk bolondítani, de akár akkor is jól jöhet a most bemutatásra kerülő kis extra, ha csökkenteni akarjuk a felhasználók elcsavargási idejét a táblázatkezelőtől. Ilyenkor jól jöhet, ha tudjuk, hogyan lehet Excelből böngészőt indítani és ott alapvető kommunikációt végrehajtani - az indítás egyébként nem annyira komplikált dolog, Internet Explorer esetében a kétirányú kommunikációval sincs baj, Chrome vagy Firefox esetén viszont ha vissza is akarunk tölteni adatot Excelbe, akkor már annyira nincs könnyű dolgunk (de kommentben jöhetnek az észrevételek). Mindenesetre ma azért ennyire még nem rohanunk előre, szimplán elindítjuk és egy egyszerű műveletet végrehajtunk Chrome-ban.

Készítünk valamilyen kis designt az általunk kitalált célra, én most egy egyszerű Google-keresést fogok imitálni és indítani Excelből:

A keresési szöveghez a mező egy Text Box, a gomb pedig szimplán egy Command Button, mindkettőt a Developer ribbonfül Controls szekciójának Insert menüjével szúrtam be.

Ehhez fogjuk majd hozzárendelni azt a rövid kis makrót, amit összeállítunk a VBA-editorban. A hozzárendelés roppant egyszerű, jobb gombbal kattintunk a Keresés feliratot viselő gombunkon, majd Assign macro:

A kódhoz Alt+F11 lenyomásával átmegyünk a VBA-editorba, majd definiálunk két változót, az egyiket chromedir néven a Google Chrome elérési útvonalára, illetve kereses néven egy másikat, a Text Boxunk értékének kinyeréséhez.

Sub kereses()
Dim chromedir As String
Dim kereses As String

Most szépen töltsük fel a két változónkat, a chromedir névre hallgatóba azt az elérési útvonalat tegyük, ahol a C meghajtónkon a "chrome.exe" található, a kereses névre hallgató változó pedig legyen egyenlő az adott sheetünkön található TextBox1 értékével:

chromedir = "C:\Program Files (x86)\Google\Chrome\Application\chrome.exe"
kereses = Worksheets("Sheet1").TextBox1.Value

Annak érdekében, hogy a keresés szóközzel elválasztott szövegek esetén is működjön, a szóközöket a Replace parancs segítségével cseréljük le + jelre:

kereses = Replace(kereses, " ", "+")

Majd ezután jön a miniprogram lelke, a Shell funkció, amely alapjáraton a paramétereként feltüntetett programot futtatja le, jelen esetünkben ez a Google Chrome lesz - paraméterként pedig értelemszerűen az url-t fogjuk felhasználni, méghozzá az előbb definiált kereses valtozó segítségével:

Shell (chromedir & " -url http://google.com" & "/search" & "?" & "q=" & kereses)

És meg is vagyunk a kóddal:

És mehet is szépen a keresés (persze értelemszerűen itt még nem az Excel fogja megjeleníteni az eredményt, hanem megnyitja a böngészőt):

Internet Explorer esetén kicsit egyszerűbb a dolgunk, ott értelemszerűen az InternetExplorer.application objektumot kell létrehoznunk (hasonlóan mondjuk az Outlookos levélküldés objektumos kezeléséhez), majd ennek paramétereivel/metódusaival tudunk játszadozni. Ez egy egyszerű példa:

Sub ietranslatevba()
Dim ie As Object
Dim bemeneti_nyelv As String, kimeneti_nyelv As String, szoveg As String
Set ie = CreateObject("InternetExplorer.application")
bemeneti_nyelv = "auto"
kimeneti_nyelv = "en"
szoveg = "Jó napot kívánok!"
ie.Visible = True
ie.navigate "http://translate.google.com/#" & bemeneti_nyelv & "/" & kimeneti_nyelv & "/" & szoveg
End Sub

És Internet Explorer esetén ráadásul a visszaadott eredményt jóval egyszerűbben be tudjuk rántani egy cellába, míg Chrome esetén extra libraryt, extra fejlesztést kell hozzáadnunk.

Aki a VBA-val való ismeretségének még csak az elején tart vagy éppen egy kezdő makrós tanfolyam első néhány óráján vesz részt, akkor igen gyakran előjön kérdésként vagy éppen tananyagként a Ribbon customizálása, azon belül is saját kis makrónk Ribbonra helyezése.

Úgyhogy a mai kérdés-válasz posztban azon egyszerű témát fogjuk körüljárni, hogy milyen lépéseket kell megtennünk saját makrónk egyedi Ribbonfülre helyezéséhez.

Adott a következő VBA-kódsor:

Értelemszerűen ahogy látjuk, ez nem csinál mást, mint bekéri tőlünk a nevünket, megvizsgálja, hogy adtunk-e meg legalább egy karaktert, majd szépen üdvözöl minket. Semmi komplikált történet.

A szubrutin neve nevbekero(), ezt fogjuk villámgyorsan a Ribbonra varázsolni. Túl sok lépés nem kell hozzá, a File Ribbonfül Options menüpontjának előhívása után a Customize Ribbon almenüt válasszuk:

Itt a jobb oldalon, fent lévő Customize the Ribbon Main Tabs menüpont választása után először adjunk egy új fület (New Tab), majd egy új szekciót (New Group):

Ez valahogy így fog utána kinézni:

Utána a bal oldalon fent a Choose commands from legördülő menüből válasszuk a Macros menüpontot:

Majd szimplán az itt található kódunkat az Add lenyomásával mozgassuk át a saját kis fülünk és szekciónk alá:

Ezzel kész is vagyunk, a Sajatmakrok fül alatt:

Már látjuk a saját kis kódunkat, szép formás ikonnal:

Ezzel alapvetően rendben is lennénk, viszont ha folyamatosan bármilyen Excelből szeretnénk használni, akkor készíthetünk belőle egy Add-int a korábbiak alapján.

És természetesen mehetünk a másik irányból is, azaz nem customizáljuk a Ribbont az Option menüpont alól, hanem először elkészítjük az Add-int, majd az itt letölthető Custom UI Editor segítségével készítünk custom ribbonfület, szekciót és ikont.

Ehhez nincs nagyon más tennivalónk, mint megnyitni az UI Editort:

Az Insert menü Sample XML menüje alól válasszuk az Excel - A Custom Tab menüpontot:

A gombra való kattintás után kapunk is egy mintát, amit nagyjából csak módosítanunk kell, mondjuk a lentiek szerint:

És kész is vagyunk!

A mai posztom nem lesz túlságosan hosszú és az is lehet, hogy ezeréves tapasztalattal bíró VBA-mágusok számára nem is bír semmilyen komoly értékkel, de ettől függetlenül megosztanám mindenkivel a kérdést és a választ is, hátha még jól jön majd valakinek.

A kérdés az, hogy a következő két utasítás közül az elsőnek miért 01, a másodiknak pedig miért 02 az eredménye? Ránézésre jónak tűnhet az első is, de mégis miért van akkor különbség?

Utasítás:
ActiveWorkbook.Sheets.Add(before:=Worksheets(Worksheets.Count)).Name = Format(Month(Now), "mm")
Eredmény:
01

Utasítás:
ActiveWorkbook.Sheets.Add(before:=Worksheets(Worksheets.Count)).Name = Format(Now(), "mm")
Eredmény:
02

Az alapvetően tisztán látható, hogy a második utasításban az adott pillanatban lévő dátumot (Now) Format segítségével úgy alakítjuk át, hogy kizárólag a hónap jelenjen meg belőle.

Az elsőben a problémát az okozza, hogy az Excel számként tárolja a dátumokat (ahogy azt már többször is megnéztük itt a blogon), azaz Month(Now) kettőt fog ugyan visszaadni (ez tisztán látható is, ha ellenőrizzük), viszont innentől kezdve a program a kettőt már nem hónapszámként fogja értelmezni, hanem logikája szerint kettes számként.

És mint ismert, 1900. január elseje az egyes számnak felel meg Excelben, így értelemszerűen a fentiek alapján a kettes szám nem a második hónapot fogja jelenteni, hanem 1900. január másodikát, innentől kezdve pedig már érthető, hogy 1900. január másodikában a hónap az január és nem február.

Folytatva az előző posztban feldobott labdát, ebben a rövidke írásban ismét egy olyan kérdést fogunk körbejárni, ami azóta akasztja meg hosszabb-rövidebb időre a CTRL+C és CTRL+V művészeket, amióta nagyjából világszerte elterjedt az Excel táblázatkezelője. Mindenkinek van egy saját kis módszere a kérdés megoldására, ebben a posztban most néhány lehetséges utat mutatok be én is.

A kérdés maga elég egyszerű: adott egy szép nap táblázatunk, adatok tömegének összevisszaságával, amelyben valamilyen feltétel szerint végrehajtunk egy szűrést (most a V betűvel kezdődő városok lesznek a minták), majd a leszűrt adatokat ugyanezen táblázat egy másik oszlopába szeretnénk másolni (E oszlop).

Valószínűleg mindannyian tudjuk mi történik akkor, ha szimplán kijelöljük a leszűrt adatokat, majd azokat megpróbáljuk simán beilleszteni a céloszlopba:

Egyértelműen látszik, hogy a beillesztésünknél az öt várost az E15-ös cellától kezdődően kezdte el berakni az Excel, azaz például Vicuña elbűvölő kis települése az E26 helyett az E16-ba került, így nem is látjuk, hiszen szűrésünkben az a cella nincsen benne. Ha valaki ezt nem veszi észre, akkor súlyos adatvesztések állnak elő, ami aztán később már nehezen lesz javítható és visszakövethető - vegyük figyelembe, hogy ez a példa ráadásul teljes mértékben bináris, ennél csak komplexebb táblák léteznek.

Ilyenkor a legbiztosabb és leginkább elterjedt megoldások a szűrő eltávolításával operáló műveletek, így például az, amikor a filter eltávolítása után sorbarendezzük a táblánkat és úgy már simán működik az E oszlopba történő másolás:

Ez persze a legegyszerűbb táblák esetén működik csak, ahol a sorbarendezésnek van értelme, ellenkező esetben hasonló manuális ügyködés az, amikor egy segédoszlopban valamilyen formában jelezzük, hogy mely cellákra szűrtünk, majd a filter eltávolítása után eszerint a segédoszlop szerint rendezzük sorba az adatainkat:

Utána pedig már simán mehet a Copy+Paste, hiszen az adataink egymást követő sorokban helyezkednek el.

Sokak számára valószínűleg meglepő módon a legegyszerűbb és leggyorsabb megoldások egyike egy szimpla formula alkalmazása:

Amennyiben egyenlővé tesszük az első célcellát az első másolandó település cellájával, majd ezt a formulát automatikus kitöltéssel "lehúzzuk", gyorsan elérjük a megfelelő eredményt:

És bármennyire hihetetlen, ez működik is szépen:

Ennek létezik a még hatékonyabb verziója, amikor a leszűrt adatállományon a célcellákat egyben kijelöljük, beírjuk a formula sorba az első cellára vonatkozó egyenlőséget, majd CTRL+ENTER billentyűkombinációval az egész tartományt kitöltjük automatikusan.

De szintén elég egyszerű megoldás a Fill Right/Fill Left billentyűkombinációs alkalmazása, amihez nem kell mást tennünk, mint a célcellák kijelölése után CTRL lenyomásával a forráscellák kijelölése:

Aztán már csak egy CTRL+R billentyűkombinációt kell lenyomnunk és kész is vagyunk! Se szűrés, se segédoszlop, se adatvesztés.

Persze azt célszerű megemlíteni, hogy ez utóbbi megoldás például nem működik sheetek vagy munkafüzetek között, de aki ilyesmire keres megoldást, az nyugodtan használhatja a korábbiakat. És akkor még VBA megoldást nem is dobtunk be a kalapba. A poszt végén pedig álljon itt egy humoros megoldási verzió, amivel egyszer volt szerencsém összefutni: a forrásoszlopban kijelölt cellákat képként beillesztve a célcellákba szintén elérhetjük a kívánt eredményt, bár utána már az egésszel túl sok mindent nem tudunk kezdeni.

Amióta nagyjából világ a világ, azóta az Excel jópár elgondolkodtató és fejtörést okozó kérdése közül az egyik a mai napig az, hogy miért nem lehet elrejtett munkalapokat egyszerre láthatóvá tenni, miért kell ezt manuálisan egyesével megcsinálnia egy mezei felhasználónak?

A következő posztban azokon a lehetőségeken fogok végigmenni, amelyeket leggyakrabban használnak a tapasztaltabb userek eme, időnként tényleg nagy szenvedéseket okozó, tervezett funkcionalitás elhárítása érdekében. És bár sokan úgy gondolják, hogy ez csak makróból megoldható, van azért beépített megoldási lehetőség is, ha nem is olyan könnyen használható.

Szóval adott a következő helyzet - ahogy a következő képen látható is, elrejtünk minden munkalapot az egyik munkafüzetünkben:

Na és most ezeket akarjuk láthatóvá tenni. Az alapmegoldás minden esetben a jobb gombbal való kattintás után megjelenő menüből az Unhide opció kiválasztása:

Majd az első sheet kiválasztása és okéra kattintás után szépen ismételjük a mozdulatsort, egészen addig, amíg el nem érjük a kívánt eredményt.

Ennek van a gyorsabb verziója, az ALT+O+H+U és ENTER billentyűkombináció igen gyors nyomogatása.

A következő gyakran alkalmazott, ám csak nem rendszeres igénynél használt megoldás, a VBA-editorból elérhető Immediate ablakból futtatható kódsor:

Ugyebár Excelből ALT+F11 lenyomása után belépünk a VBA-editorba, ott CTRL+G lenyomásával meghívjuk az Immediate ablakot, majd begépeljük az egyetlen soros parancsunkat. Jelen esetben is látható, hogy két : alkalmazásával tesszük lehetővé az Immediate ablak használatát, hiszen itt csak egyetlen sort tudunk lefuttatni, tehát kettőspontok nélkül nem működne ez a megoldás.

És ahogy arról majd egy későbbi posztban részletesebben is írok, az Immediate Window tökéletesen alkalmas változók aktuális értékének megállapítására, utasítások tesztelésére és hibakeresésre is, így jelen esetünkben most végigfutunk egy cikluson, azaz az összes worksheetünk Visible tulajdonságát True-ra állítjuk - ergó szépen megjelenítjük a korábban elrejtett munkalapokat.

Aztán van ennek a megfelelő szubrutinban történő kidolgozása, amikor nagyjából ugyanazt csináljuk, mint az Immediate ablakban, csak a megfelelő formai elemeket is megadjuk a kódsornak:

Sub elrejtesfeloldas()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub

Ugyanaz, mint az előbb, azaz az aktív munkafüzetünk összes munkalapján végigmegyünk és a Visible paramétert xlSheetVisible státuszra állítjuk.

A következő módszer az, amelyhez nincs szükség se Immediate ablakra, se VBA-kódsorra, csak kicsivel több előkészítő munkára. Készítsük elő úgy az Excel-munkafüzetünket, hogy az összes eddig elrejtett sheetet tegyük láthatóvá, vagy legalábbis azokat, amelyeknél az elrejtés után sűrűn előfordul az az igény, hogy mégis láthatóvá akarjuk őket tenni. Ha ezzel megvagyunk, akkor a View ribbonfül Workbook Views szekciójából válasszuk ki a Custom Views menüpontot:

A felugró Custom Views ablakban kattintsunk az Add gombra:

Adjunk nevet a nézetünknek:

Ha ez megvan, akkor nagyjából kész vagyunk, hiszen most készítettünk egy olyan custom nézetet, amelyben az általunk meghatározott munkalapok láthatóak, ergó bármikor elrejtjük őket, a Custom Views menüpontból előhívhatjuk a korábban meghatározott, minden meghatározott sheetet láthatóként mutató nézetet:

Ezek a leginkább elterjedt megoldási lehetőségek és ötletek, esetleg valamelyik kedves Olvasónak van egy másik (akár gyorsabb, akár nem) a tarsolyában?

Az idei év egyik első komoly Exceles kihívásában most akár otthonunkban ülve is megmutathatjuk a tudásunkat a táblázatkezelő többi, nagytudású felhasználójának, hiszen a DataPig Technologies (datapigtechnologies.com) versenyt hirdetett, amelyben a kihívás elég egyértelmű:

Valamilyen előre összeállított adathalmazból öt perc alatt készítsünk el egy minél szebb és teljes mértékben működő riportot/jelentést/dashboardot, majd az elkészítés folyamatáról rögzített (tehát maximum öt perces) vágatlan videót töltsük fel valamelyik internetes tárhelyre (OneDrive, Dropbox stb.) és küldjünk egy e-mailt a linkkel és jelentkezésünkkel a következő e-mail címre:

Mha105@yahoo.com

A videót aztán a szervezők feltöltik a külön erre a célra készített YouTube lejátszási listára, ahol az internet népe fodja eldönteni az eredményt, hiszen a győztest a kedvelések száma alapján fogják meghatározni - tehát nem a szervezők döntenek.

A határidő a jelentkezés és videó/link beküldésére február ötödike, vasárnap, a győztest pedig február 10-én hirdetik majd ki. Fontos kitétel, hogy nem lehet vágni, vagy bármilyen előre elkészített Pivot-táblát, objektumot másolni és felhasználni, teljesen az alapoktól, az Excel rendelkezésére álló funkcióinak felhasználásával kell elkészíteni a videónkat, amely jobb ha tartalmaz narrációt is.

És megéri küzdeni és próbálkozni, hiszen még van két hét a határidőig, a nyeremények pedig egy ilyen kihíváshoz mérten rendkívül jók:

Az első helyezettnek járó Xbox One, a második helyezettnek járó Fizbit Fitness karóra valamint a harmadiknak járó Amazon Fire HD8 mellett még három különdíjat is ki fognak osztani, úgyhogy tényleg megéri próbálkozni.

Mutassuk meg, hogy a magyar Excelesek is vannak olyan jók, mint bármelyik MVP bárhol a világban!

Az eredeti versenykiírás a DataPig Technologies weboldalán megtalálható.

Ahogy általában az Excelben lenni szokott, a ma bemutatásra kerülő kérdésre sem a posztban leírt megoldás az egyetlen lehetséges megoldás és akinek van kedve, nyugodtan ossza meg a saját verzióját, mindenesetre remélem, hogy többeknek segítséget tudok nyújtani a lentiekkel egy újabb lehetőség kiaknázására.

A kérdés alapvetően roppant egyszerű, van egy egyszerű Data Validation listünk:

Értelemszerűen a legördülő menüből a települések nevei közül tudunk választani, ahogy azt a Source mezőben szépen be is hivatkozzuk:

Eddig ez szerintem bármelyik Excel-felhasználónak ujjgyakorlat, ezért is tekintettem el ezen lépések részletes bemutatásától. Na de hogy érjük azt el, hogy a listában szereplő településeket csak egyetlen egyszer választhassuk ki, azaz ha az első mezőben kiválasztottuk Pilinyt, utána a lehetőségek között ez a nógrádi község már ne szerepeljen?

Ehhez segédoszlopokra (segédtáblára lesz szükségünk), magától értetődő módon csak a példában lesz ennyire látható, amit csinálok, ha élesben szeretnénk ezt a megoldást használni, mindenképpen rejtsük el a segédtáblát.

Szóval elsőként azon lista mellé, amelyből a legördülő menüt feltöltjük, egy plusz oszlopba szúrjuk be a következő függvényt:

=IF(COUNTIF($C$47:$C$51,M48>=1,"",ROW())

Mit is számolunk ezzel? Ugyebár a COUNTIF az első paramétereként megadott tartományban megnézi, hogy  abban hány, a második paraméterének megfelelő érték található, tehát a függvényünk belső része egy számot fog visszaadni, amely megmutatja, hogy a legördülő menüt használó listánkban hányszor szerepel az első településnév (jelen esetben ez most M48 azaz Piliny, de majd ha automatikus kitöltéssel lehúzzuk, nézi a következőt és így tovább). Ezt a számot egy IF függvény használja fel, azaz ha a COUNTIF által visszaadott szám 1 vagy esetleg nagyobb érték, akkor a függvény üres értéket ad vissza, ha pedig 0 a COUNTIF által visszaadott érték, akkor az adott sor számát adja eredményül.

Látható is, ha kiválasztjuk a legördülő menüt tartalmazó táblában az első sorban Litkét, akkor a segédtábla második segédoszlopában, tehát a Litke melletti cellában nincs érték, a többinél a sorszám szerepel.

Ezután még egy segédoszlopra lesz szükségünk, ahol a fentiekben létrehozott segédoszlopot és az eredeti adatokat is fel fogjuk használni. Ennek az új segédoszlopnak az első cellájába a következő függvényt kell beszúrnunk:

=IF(ROW(M48)-ROW(M$48)+1>COUNT(N$48:N$52),"",INDEX(M:M,SMALL(N$48:N$52,1+ROW(M48)-ROW(M$48))))

Első sokkolónak tűnhet, de alapvetően könnyen megfejthető, hogy mit is csinál ez a hosszú függvénysorozat, hiszen ha sorban haladunk az egyes beágyazott függvényeken, akkor előbb-utóbb kirajzolódik a cél és az eredmény.

Szóval HA az adott településünk SORSZÁMÁBÓL kivonjuk mindig az első településünk SORSZÁMÁT + egyet és ez az eredmény nagyobb, mint az első segédoszlopban sorszámot tartalmazó cellák száma (ahogy látható, Litke mellett nincs már sorszám, így ez a beágyazott COUNT négyet fog eredményként adni), akkor nem csinál semmit, viszont ha kisebb ez az érték, akkor a függvényünk INDEX-szel kezdődő részére lépünk. Vegyük észre, hogy minél több települést kiválasztottunk már, annál nagyobb az esélye, hogy üres lesz a függvényünk által visszaadott érték, hiszen például ha négyet már kiválasztottunk, akkor csak egyetlen sorszám marad a segédtáblában.

Ebben az INDEX-es részben azt fogjuk megnézni, hogy melyik az a település, amely nagyságát tekintve az "adott településünk SORSZÁMA plusz egyből mínusz mindig az első településünk SORSZÁMA" által meghatározott helyen áll. Tehát ezzel a résszel kezdjük elvadászni azokat a településeket, amelyek mellett még szerepel sorszám.

Ez most szörnyen hangzott, de egy gyors példán levezetve ez azt jelenti, hogy például Pilinyen végrehajtott vizsgálatunk eredménye az első lépésnél valahogy így néz ki:

48-48+1 = 1

Ami értelemszerűen kisebb mint az N oszlopban sorszámmal rendelkező települések száma, így az INDEX függvényrészre kell ugranunk.

Itt a SMALL függvényrészben az N oszlop sorszámai közül a legnagyobbbat kell megkeresnünk (hiszen 49-48 az 1), majd az ehhez tartozó településnevet kell visszaadnunk.

Így pedig már annyit kell csak csinálnunk, hogy a Data Validation listünkben a Source-ot át kell állítanunk erre a második segédoszlopra az eredeti helyett:

Így ha kiválasztunk mondjuk két települést a listáról, ezek a későbbi választási lehetőségek között már nem lesznek ott:

Azt azért mindenképpen meg kell jegyeznem itt a poszt végén, hogy az ötlet nem saját találmány, valamikor pár évvel ezelőtt egy Excel MVP könyvében láttam eredetileg.

Mai olvasói kérdéses posztomban egy néhány lépésben megoldható Conditional Formatting lehetőséget fogok megmutatni, amely a korábbiakhoz hasonlóan remélhetőleg mások számára is hasznos lehet majd. A kérdés nagyjából úgy szólt, hogy rá lehet-e venni a feltételes formázás ismétlődő formázási eszközét, hogy a különböző számú előfordulások között is különbséget tegyen.

Ha pontosan ezt az eszközt nem is tudjuk rávenni erre a feladatra, a Conditional Formatting magában lehetőséget ad arra, hogy bármennyi előfordulás között különbséget tegyünk, a mostani példámban a kétszer illetve háromszor előforduló értékekkel fogom ezt megmutatni.

Adott ez a tábla:

Az a cél, hogy a kétszer megjelenő megyéket kékkel, a háromszor megjelenőket zölddel jelölje meg a feltételes formázási eszköz. Ezt fel is véstem a munkalap jobb oldalára, hogy dinamikus hivatkozási lehetőséget adjak magamnak:

A megoldást egyébként Conditional Formatting esetén is a COUNTIF függvény rejti, amely az első paramétereként megadott tartományban megszámolja, hogy a második paramétereként szereplő érték hányszor fordul elő:

Ahogy látjuk, meg is van szépen az eredmény, amely szerint Baranyát, Hevest és Vast kellene majd a feltételes formázásnak is kijelölnie:

Jelöljük ki a táblában értelemszerűen azt a tartományt, ahol többszörös előfordulást keresünk (ez lehet akár a bevétel is), majd a Home ribbonfülön a Styles szekcióban nyomjunk a Conditional Formatting funkcióra és válasszuk a New rule menüpontot:

Itt pedig a Use a formula to determine...szabálytípust kell kiválasztanunk, majd a formula sorba a következőt kell megadnunk:

=COUNTIF($A$2:$A$15,A2)=$K$3

Azaz ha a COUNTIF vizsgálata (ahogy az előbb is láttuk) olyan eredményre jut, amely megegyezik a K3 cellában lévő számmal (jelen esetben ez most kettő), akkor a Format menüpont alatt beállítható formázást hajtsa végre az érintett cellákon:

Ugyanezt toljuk rá a hármas előfordulásra is:

És kész is vagyunk!

A mai posztban egy olyan olvasói kérdés kerül megválaszolásra, amely VBA-ban járatos hozzáértők számára valószínűleg nem jelent túlságosan komoly kihívást, azonban mégis úgy gondoltam, hogy megosztom a kérdést és a választ egy példán keresztül, mert a téma alapján eléggé valószínű, hogy egy, még az út elején járó felhasználó számára igencsak hasznos tudnivalóról van szó, ami ráadásul még igen látványos is lehet.

A kérdés nagyjából arra irányult, hogy milyen módszerrel lehet gombok, boxok vagy éppen scroll barok tulajdonságait VBA-ból állítgatni - a példa most egy egyszerű nyomógomb tulajdonságaira lesz kihegyezve.

Első lépésként lépjünk a Developer ribbonfül Controls szekciójának Insert menüjébe:

Innen pedig szúrjunk be szépen két Opciógombot egymás alá.

Ha ezzel megvolnánk, szúrjunk be ugyaninnen egy Command Buttont is (akciógomb):

Mi a cél? Alig egy tucat kódsor segítségével azt fogjuk elérni, hogy az első opciógomb bekapcsolásával az akciógombunk ki fog szürkülni és nem kattinthatóvá válik, míg a második gomb benyomásával ismét kattintható lesz, ráadásul a színe is megváltozik.

Csak a példa kedvéért rendeljünk hozzá az akciógombhoz valamiféle tényleges akciót, ennek érdekében kattintsunk duplán a gombon Design módban, majd a megnyíló VBA-editorban feldobott CommantButton1_Click() subrutinba írjuk be valamiféle eseményt:

Private Sub CommandButton1_Click()
Range("G14").Value = 20
End Sub

Azaz ha a gombra kattintunk, akkor a G14-es cella értéke 20-ra fog változni.

Ezután rendeljünk eseményeket/vizsgálatokat a két opciógombhoz is, kezdésként kattintsunk duplán Design módban az első opciógombra. Az így létrejött OptionButton1_Click() subrutinban egy If utasítást kell használnunk, amivel nagyjából azt a vizsgálatot fogjuk elvégezni, hogy ha az opciógomb állapota bekapcsolt (OptionButton1.Value = True), akkor módosítsa az akciógombunk színét (CommandButton1.BackColor) az inaktivitás jelzéseként egy másik színre, illetve az Enabled paraméterét (CommandButton1.Enabled) állítsa False-ra, azaz a gomb nem lesz kattintható.

Private Sub OptionButton1_Click()
If OptionButton1.Value = True Then
CommandButton1.BackColor = 14606046
CommandButton1.Enabled = False
End If
End Sub

Majd ezt szépen ismételjük meg a második opciógombbal is, értelemszerűen azzal a változtatással, hogy ha az a gomb lesz bekattintva, akkor a színt állítsuk vissza az eredeti színre vagy egy teljesen másik színre megint, illetve az Enabled paramétert állítsuk True értékre, így téve ismét kattinthatóvá a nyomógombot.

Private Sub OptionButton2_Click()
If OptionButton2.Value = True Then
CommandButton1.BackColor = 8882055
CommandButton1.Enabled = True
End If
End Sub

Most szépen visszaballaghatunk az Excel munkalapra, ahol egy rövid teszt meg is mutatja, hogy tökéletesen működik, amit szerettünk volna:

Innentől kezdve már csak a formázgatás, tuningolgatás van hátra, meg hát persze értelemszerűen a megfelelő integrálás egy már meglévő makrónkba, azzal az elég gyakran ismételgetett észrevétellel, hogy opciógombokat leginkább userformon célszerű használni - nem mintha persze ezzel így most gond lenne.