Aki már jó ideje gyűri a táblázatkezelők családjának legismertebb tagját, az Excelt, az biztosan találkozott már azzal az igénnyel vagy kéréssel, hogy egy meghatározott számsorozatból egy bizonyos végösszeget kiadó számokat kellett megadnia.

Az ilyen problémáknak több megoldási módszere is létezik, ezek közül a leginkább alkalmazott az valamilyen custom VBA-s megoldás, de az Excel újabb verzióinak már van erre beépített megoldása is a Solver személyében.

A Solver Add-Int egyszerűen, az Excel Options alatt tudjuk bekapcsolni:

Értelemszerűen az Options alatt az Add-Ins almenüre kattintunk.

Majd a felugró ablakban, ha a Solver még nem aktív, akkor a Manage Excel Add-Ins menü melletti Go gombra kattintva megjelenő Add-Ins ablakban tudjuk bekapcsolni. Ha ez megvan, akkor a Data fülön megjelenő új, Analysis szekcióban meg is találjuk a gombját:

Ha ezzel megvagyunk, már nézhetjük is a konkrét feladványt:

Elég egyértelmű mi a célunk, meghatározni azon számokat az oszlopban, amelyek a célszámunkat fogják pontosan visszaadni. A példa alapvetően egyszerű és nagyon gyorsan ki is található, mely számok adják ki az 1033-as végösszeget, de most nem ez a lényeg.

A Solveres megoldáshoz most két segédoszlopot fogok létrehozni, az egyiket Szorzó néven, a másikat Számok2 néven.

Utóbbi elég egyszerű tartalommal bír, a Számok oszlopban található számok és a Szorzó oszlopban található szorzók szorzata szerepel itt. A Szorzó oszlopban egyesek szerepelnek, a végeredmény meghatározásához van erre szükségünk, a Solver ugyanis ezen szorzókat fogja egyesnek meghagyni vagy átírni nullára, attól függően, hogy melyik számra lesz szükség a célszám meghatározásához.

A végeredmény eléréséhez még két segédcella kell majd nekünk:

A Teljes összeg értelemszerűen a Számok2 oszlop teljes szummáját mutatja, a Különbség pedig a Célszám és a Teljes összeg különbsége - a Solvert fogjuk megkérni arra, hogy a Különbséget vigye le pontosan nullára, azaz határozza meg azon számok halmazát, amelyek kiadják célszámunkat.

Most szépen kattintsunk a Solver funkcióra a Data fülön.

Ezt az ablakot kell kitöltenünk ahhoz, hogy a Solver megadja nekünk a megoldást.

Set Objective mezőbe írjuk, hogy melyik cellának akarunk célértéket megadni, jelen esetben ez most a Különbség cellánk, azaz G6 lesz.

Mit szeretnénk ettől a cellától? Hogy nulla legyen.

Hogy hozza ki a Solver ezt az eredményt? Értelemszerűen a "változó" celláink módosításával, azaz a Szorzó oszlopot adjuk be a By Changing Variable Cells mezőbe:

Ezután már csak ezen "változó" celláink értékeinek kell határokat szabnunk, tehát a Solvert terelgetnünk kell, hogy csak 0 vagy 1 értékeket használjon a Szorzó oszlopban.

Ezt három feltétellel fogjuk elérni, egyrészt definiáljuk, hogy a Szorzó oszlopban vagy 0 vagy annál nagyobb érték kell, hogy szerepeljen, azt is meghatározzuk, hogy csak 1 vagy annál kisebb érték lehet ezen cellákban és végül kikötjük, hogy csak egész szám kerülhet ide - azaz konkrétan 0 és 1 maradt a Solver számára. Ez így néz ki a Solver ablakában:

Ezután pedig már csak a Solve gombra kell kattintanunk és láss csodát, meg is van, hogy mely számokra van szükségünk az 1033-hoz:

És ehhez nem volt szükségünk VBA-tudásra, szimplán csak bekapcsoltuk a Solver Add-Int, ami ráadásul ennél sokkal-sokkal többre képes. De erről majd később.

Simplex LP megoldási metódus egy lineáris programozási feladat szimplex módszerrel történő megoldását jelenti, ezt használjuk akkor, ha elég egyértelmű a célunk és változóink.

A most következő posztban ismét visszatérek egyik kedvenc témámhoz, a Powerpoint prezentációkban alkalmazható kreatív, ám egyszerű megoldásokhoz, ami jelen esetben egy prezentációban könnyen bevethető zoomolást fog jelenti - nagyjából egy meglévő képünk valamely részére kívánjuk majd felhívni nézőink figyelmét.

Adott a következő diagram, amit én már képként fogok használni, de bármilyen más képpel végrehajtható a következő kis animációs trükk, úgyhogy nem kell ragaszkodnunk egy charthoz:

Szépen illesszük be egy diára, majd ezt a diát a jobb egérgombbal előcsalogatható menü Duplicate Slide opciójával duplázzuk meg:

Ezután a duplikációnak számító diánk Insert Ribbonfülének Illustrations szekciójában található Shapes menüből szúrjunk be egy ovális formát:

Ezt a formát aztán szépen formázzuk olyan alakúra és méretűre, amekkora pont lefedi azt a területet, amit később majd zoomolni akarunk. Én most Avaruára fogok koncentrálni, így az ovális formám pontosan azt fogja lefedni:

Értelemszerűen a Format Ribbonfül Shape Fill és Shape Outline parancsainak felhasználásával távolítsuk el a kitöltésünket és vastagítsuk meg a körvonalat:

Ha ez megvan, akkor ismét az egész képet kell kijelölnünk, majd a megjelenő Format Ribbonfül alatt a Size szekcióban található Crop menü alatti kis nyílra kell kattintanunk és a Crop to Shape opciónál ugyanazt az ovális formát kell választanunk, amit fentebb megtettünk eredetileg:

Így nézünk most ki:

Menjünk szépen vissza a Format Ribbonfül Size szekciójába, ott is ismét a Crop menü alatt kis nyílra kell kattintanunk, majd az Aspect Ratiot 1:1-re kell állítanunk:

Ezután szép aprólékos munkával méretezzük rá a Cropot a már ott lévő fekete körvonalunkra. Fontos a pontosság, hiszen ha valahol kicsúszunk, akkor az később elég igénytelenül fog kinézni:

Ha szépen beméreteztük, akkor kattintsunk ki a képből valahova a dián és ezt fogjuk látni:

A megmaradt körvonalat és képet jelöljük ki, majd CTRL+X segítségével vágjuk ki a duplikált diáról és egy az egyben illesszük be az eredetire. Ezt fogjuk látni:

A kijelölést még mindig a most beillesztett körvonalon és képen tartva, kattintsunk az Animations Ribbonfülre:

Ezután először csak a fekete körvonalat jelöljük ki, majd az Entrance szekcióból szúrjunk be rá egy Wheel típusú animációt:

Ha ez megvan, akkor ugyebár az történik majd kattintásra, hogy a prezentációnkban ezen a dián Avarua szépen körbe fog rajzolódni egy fekete körvonallal. Viszont nekünk ez nem elég, úgyhogy a körvonalat most már a kör alakú kis képpel együtt kijelölve az Animations Ribbonfül Advanced Animation szekciójában kattintsunk az Add Animation menüre, majd az Emphasis csoportból állítsunk be egy Grow/Shrink típusú animációt.

A következő kép jobb oldalán láthatjuk, hogy hol is tartunk most az animálásban:

Kattintásra tehát megrajzolódik a körvonal, majd még egy kattintásra (de ezt kattintás nélkül hozzá is köthetjük az előző esemény befejeződéséhez) szépen zoomolódni fog a körvonal és a kis kép is:

Roppant gyorsan összedobható, tényleg vizuális kis animáció, aminek segítségével könnyen felhívhatjuk a figyelmet prezentációnk valamely fontos adatára.

Hosszabb ideig tartó, különböző okoknak köszönhető szünetelés után ismét poszttal jelentkezem végre a blog hasábjain, de indulásnak egy régóta esedékes, könnyen fogyasztható írás következzen, ami egy szimpla kis makrós megoldást tartalmaz. A cél az Excelben az egy kijelölésen vagy éppen cellán, jobb egérgombbal történő kattintás után előugró menü módosítása, első körben mindenképpen csak már alapból meglévő funkciók hozzáadásával.

2013-as Excelben, jobb egérgombbal történő kattintás után a következő menüt látjuk alapesetben:

Tegyük fel, hogy ehhez szeretnénk hozzáadni valamilyen általunk gyakran használt funkciót, ami eddig nem volt így elérhető - érdemes azért hozzátenni, hogy a billentyűkombinációk gyakorlásával és használatával ezek az igények elég gyorsan eliminálhatóak. Mindenesetre a cél tehát egy újabb menüpont hozzáadása, jelen esetben ez legyen a Freeze Pane parancs, hiszen ennek elérése alapesetben Ribbonról történne.

Ennek hozzáadása egy öt soros kód segítségével pillanatok alatt megtörténhet, úgyhogy ugorjunk is át gyorsan ALT+F11 lenyomásával a VBA Editorunkba.

Első lépésként létrehozunk egy CommandBar típusú változót NewMenu néven, amely változótípus menüket és megjelenített eszköztárakat tartalmazó gyűjtemények tárolására szolgál. Kapásból egy lendülettel be is állítjuk a kezdő értékét, ami az Application objektum CommandBar tulajdonságai közül a Cell objektumot fogja visszaadni, ami nem más, mint a cellákra, kijelölésekre vonatkozó menü - amit jobb gombbal való kattintással csalogatunk elő.

Dim NewMenu As CommandBar
Set NewMenu = Application.CommandBars("Cell")

És innen pedig már nincs más hátra, mint az előbb említett CommandBar("Cell") objektum Add metódusának felhasználása, hogy hozzáadjuk a szükséges menüpontunkat. Ennek a metódusnak öt lehetséges paramétere van:

- Type (itt a típust tudjuk definiálni)
- Id (ez a konkrét, már beépített funkció/control azonosítója)
- Parameter (custom funkciók esetén ezt felhasználhatjuk információk tárolására)
- Before (ez a menüben való elhelyezkedést jelenti, az itt megadott sorszám elé kerül beszúrásra az új funkciónk)
- Temporary (ha True lesz ez a paraméter, akkor bezáráskor törlődik az új funkció/control a menüből)

Ebből következik, hogy nekünk most a típus, az Id és a Before paraméterek kellenek.

A típus nagyjából azt határozza meg, hogy gombot akarunk-e, combo boxot, legördülő menüt stb., a mi esetünkben gombnál maradunk, így a típus neve msoControlButton (ezekről tökéletes kis lista érhető el a Microsoft hivatalos oldalán). A Before az legyen egyértelműen egy egyes, hiszen a jobb láthatóság kedvéért most a legelső helyre pakoljuk az új funkciót a menüben, az ID-nál viszont le kell vadásznunk a hozzáadandó parancs egyedi azonosítóját. Ezt az ID listát saját magunk is meghívhatjuk makróból egy rövidebb kód segítségével (lehet, hogy erre is majd sort kerítek egy posztban később, ha igény van rá), de elég sok listát is találhatunk az interneten - a lényeg, hogy roppant könnyen kideríthető, hogy a Freeze Panes id-ja a 443.

Így kódunk el is készült:

Ha ezt lefuttatjuk, már láthatjuk is az eredményt:

Ha esetleg valamilyen okból ezt törölni is szeretnénk, írjunk egy hasonló Subot, amit meghívhatunk majd egy főmakróban és abban a szubrutinban mindössze ezt a sort:

NewMenu.Controls.Add Type:=msoControlButton, ID:=443, before:=1

Le kell cserélnünk erre:

NewMenu.FindControl(ID:=443).Delete

Ha lefuttatjuk, töröltük is az új funkciót/controlt.

A mai írás témája egy alapjában véve igen érdekes ám nem túlságosan létfontosságú kis Excel-elmélkedés lesz, amely az ezen blog hasábjain már többször kínpadra került kalkulációs pontosság egyik újabb furcsa, ám magyarázható megnyilvánulását fogja körbejárni. Jómagam eme problémával az Excellel töltött pályafutásom során mindösszesen egyszer találkoztam éles helyzetben, úgyhogy valószínűleg sokak számára teljesen ismeretlen is lesz a kérdés.

A problémát elég egyszerűen látni fogjuk, ha megnézzük a lenti képet:

És a fenti felvetés nem csak ennél az egyszerű példánál merülhet fel, hasonlót könnyedén tudunk előállítani pozitív és negatív számok papíron nullát eredményező összesítésével is.

A legjobb az egészben viszont az, hogy ez nem hiba, az egész viselkedés az Institute of Electrical and Electronics Engineers eredetileg kizárólag villamosmérnököket később aztán már informatikusokat is tömörítő szakmai szolgáltató szervezet egyik számformátumának követelményéből adódik, ami azt írja elő, hogy a számokat bináris formátumban kell tárolni. Binárisan azonban bizonyos számokat képtelenség teljes pontossággal tárolni, így akár elsőre egyetlen pontos értéknek tűnnek, valójában kerekített értékekről beszélünk. Ez most laikusként konyhanyelvre lefordított magyarázat, de remélhetőleg tükrözi az IEEE 754/1985 lebegőpontos számformátum követelményének hivatalos megfogalmazását.

Tehát ha bármikor ilyen problémával szembesülnénk, akkor roppant gyorsan lépjünk rajta túl és próbáljunk előrántani a többféle áthidaló megoldás közül egyet:

1. Leggyorsabb és legegyszerűbb egy ROUND használata, amivel eredményünket gyorsan lekerekíthetjük a ténylegesen elvártra:

2. Az Excel Options alatt elérhető Precision As Displayed funkciót is bekapcsolhatjuk (nem tanácsos egyébként), hiszen ez automatikusan a cella formátumának megfelelő tizedesjegyig fogja lekerekíteni az adott számunkat:

Már elég régen nem képletezgettünk az OfficeGuru berkein belül, úgyhogy a mai posztban egy olyan kis fejtörőt fogunk körbejárni, amibe a mindennapok során könnyen belefuthatunk, hiszen a VLOOKUP kétség nélkül az egyik leggyakrabban használt Excel-függvény. Mi történik akkor, ha a VLOOKUP függvényünkkel keresett értékünket a formula többször megtalálja a meghatározott tömbben? Alapvetően mindig az első értéket fogja nekünk visszaadni, ami akár meg is tévesztheti a táblánkat később elemző kollégákat, felhasználókat.

Úgyhogy most egy olyan megoldást nézünk meg, amelynek segítségével ilyen esetekben listázható az összes találat és azzal sem árulok el valószínűleg nagy titkot, ha már előre megírom, nem a VLOOKUP függvény fog nekünk segíteni.

Szóval adott a következő táblázat:

Értelemszerűen a Város oszlop első cellájában szereplő városhoz szeretnénk az összes értéket bekeresni, nem csak az elsőt. Tömbfüggvény lesz a megoldás, amelynek "motorja" egy IF formula lesz, amelyben azt vizsgáljuk meg, hogy városneveink között melyek azok, amelyek egyeznek az eredetileg megadottal:

IF($H$5=$D$4:$D$14,

Ennek a vizsgálatnak az eredménye egy TRUE illetve FALSE értékeket tartalmazó tömb lesz majd, pontosan annyi ilyen értékkel, ahány város van az eredeti listánkban.

Ezt folytatva, most az a két paraméter következik az IF-ből, ami egyrészt meghatározza, hogy mi történjen a feltétel teljesülése esetén, másrészt ami megmondja, hogy mi történjen a feltétel nem teljesülésekor.

Értelemszerűen a nem teljesülés elég egyszerű, szimplán maradjon üresen a mező, hiszen nincs egyező értékünk a városnevek között, viszont ha teljesül, akkor most a sorszámokat kell begyűjtenünk. Vigyázzunk, mert Kecskemét ránézésre a 4. sorban van, valójában viszont a meghivatkozott város-tömbünk első sorában!

Ennek megfelelően az IF második paramétereként a

ROW($D$4:$D$14)

nem lesz jó, mert ez egy ilyen tömböt hozna létre:

{4,5,6,7,8,9,10,11,12,13,14},

ami később rossz értékek bekereséséhez vezethet, hiszen a tömb soraira kell koncentrálnunk.

Azaz egy olyan tömböt kell létrehoznunk, ami így nézne ki:

{1,2,3,4,5,6,8,9,10}

Értelemszerűen a tömbben az összes sorszám most csak a példa kedvéért szerepel, valójában mondjuk EGER város esetén a tömb így néz ki:

{4,7,9}

Szóval a megfelelő tömböt egy egyszerű logikai művelettel fogjuk megszerezni, azaz minden sorszámból kivonjuk a legalacsonyabb sorszám értékét, majd minden eredményhez hozzáadunk egyet:

ROW($D$4:$D$14)-MIN(ROW($D$4:$D$14))+1

Azaz kész az IF függvényünk is:

IF($H$5=$D$4:$D$14,ROW($D$4:$D$14)-MIN(ROW($D$4:$D$14))+1,"")

Mit fog tenni? Ahogy már végigmentünk rajta, ha a $D$4:$D$14 táblában van a megadott városnévvel megegyező érték, akkor kiírja az adott sorok számát egy tömbbe.

Az okoskodásnak még nincs vége, hiszen most van egy tömbbünk 1-2-3 stb. sorszámmal, azoknak az értékeit be kellene keresnünk.

Még mindig a ROW függvényt használjuk összehozva egy SMALL függvénnyel, amely utóbbi az első paramétereként megadott tartományból veszi ki a második paramétereként megadott legkisebb értéket:

Pl.: SMALL(A1:A5,4) - az A1:A5 tartomány 4. legkisebb értékét adja vissza.

És ha a ROW függvényt simán bármelyik oszlop első cellájára állítjuk, akkor egy tömbfüggvény esetén ha lehúzzuk a formulánkat, akkor értelemszerűen a ROW eredménye is növekszik (azaz egyről kezdünk majd jön a második és harmadik legkisebb érték):

SMALL(IF($H$5=$D$4:$D$14,ROW($D$4:$D$14)-MIN(ROW($D$4:$D$14))+1,""),ROW(L1))

Azaz látható, hogy az IF formulánk által visszaadott tömbből először a legkisebb sorszámot, majd a következő és az azután következő értékeket fogja ez a tömbfüggvény visszaadni, értelemszerűen növekvő sorrendben.

Ergó ha azt a tömböt nézzük, amit az IF-nél beszéltünk Eger kapcsán:

{4,7,9}

Akkor a SMALL(....,ROW(L1)) először a 4-est fogja listázni, aztán a hetes sort, majd végül a kilencest.

De ez még mindig elég, mert még az értékeket is vissza kell kapnunk, amire tökéletes választás lesz az INDEX függvény, hiszen azt már ezerszer átbeszéltük, hogy ennek a formulának a segítségével egy tábla megadott sorában, oszlopában vagy sor és oszlop kereszteződésében található értéket kaphatunk vissza.

Ezzel pedig össze is állhat, hogy mi lesz a végső eredményünk - az INDEX első paramétereként megadjuk a városokhoz tartozó bevételek tömbjét, második paramétereként pedig a fentebb már kielemzett SMALL-IF egybeágyazást és így az eredményünk tömbfüggvényként lehúzva mindhárom EGER értéket visszadobja.

={INDEX($E$4:$E$14,SMALL(IF($H$5=$D$4:$D$14,ROW($D$4:$D$14)-MIN(ROW($D$4:$D$14))+1,""),ROW(B1)))}

Komplexebb folyamatok kialakítása, többféle adathalmazt használó dashboardok kidolgozása esetén elég gyakran lehet szükségünk Access adatbázisok használatára Excelben, amelynek lehet több formája is, most a legegyszerűbbről fogunk beszélni - azaz egy makróval szimplán egy Access adatbázis meghatározott táblájából Excelünk egyik munkalapjára másolunk adatokat. Ott aztán feldolgozhatjuk, szűrhetjük, átalakíthatjuk és így tovább.

Adott tehát a következő roppant egyszerű tábla egy Access adatbázisban:

A célunk is roppant egyszerű - ezeket az adatokat egy VBA-kódsor segítségével másoljuk be egy meghatározott Excel-fájl valamelyik sheetjére. Az Excel megnyitása után Alt+F11 lenyomásával gyorsan masírozzunk is át a VBA-editorba, ahol hozzá is láthatunk a kód kidolgozásának.

Ahogy mindig, most is a változók definiálásával kezdünk és ez most különösen fontos, hiszen itt már meghatározzuk az Access-kapcsolatfelvételt. Azt előzetesen tudnunk kell, hogy a Microsoft által kifejlesztett ADO (ActiveX Data object) arra szolgál, hogy egy fejlesztő úgy írhasson programokat adatbázisokra, hogy ne legyen szükséges az adatbázis felépítésének teljes ismerete, csak a hozzá való kapcsolódás. És most, a kódunk változókat deklaráló részénél ezt ki is fogjuk használni, hiszen először létrehozunk egy változót ADO objektumra, majd ezzel együtt létre is hozunk egy ilyen adatbázis-kapcsolódási objektumot:

Dim cnn As New ADODB.Connection

A connection object tárolja a kapcsolódásról szóló információkat és a módszereket a csatlakozásra, így a kódban néhány sorral később majd szükségünk lesz a connection object Open metódusának használatára, amely konkrétan meghatározza a kapcsolódás paramétereit, így például az Open metódus Provider paramétere az adatbázis típusát, Data Source paramétere pedig a konkrét elérési útvonalat adja meg.

A recordset ADO objektum az előzőekhez képest rekordok csoportjára hivatkozik az adatbázisban, ami lehet konkrét egy tábla, de lehet egy lekérdezés eredménye is - mostani kódunkban értelemszerűen tehát szükség van egy vadiúj recordset létrehozására:

Dim rs As New ADODB.Recordset

Szükségünk lesz még két String típusú változóra, az egyik a recordsetünket majd feltöltő lekérdezésre, a másik pedig az adatbázisunk elérési útvonalának tárolására:

Dim query As String
Dim Path As String

Most pedig gyors lendülettel fel is töltjük a változókat, ahol szükséges, így a Path nevű változónkba az elérési útvonalat írjuk be, a query névre hallgatóba pedig azt a lekérdezést, ami meghatározza a másolandó adatainkat, a mi esetünkben ez szimplán SELECT * FROM status (ez a táblám neve az Accessben).

Ezután jön az a lépés, amiről fentebb már volt szó, azaz az adatbázisunkat meg is nyitjuk, tehát a connection objektum Open metódusát használjuk és megadjuk az adatbázis típusát (értelemszerűen Access esetén a Provider könnyen meghatározható) és elérési útvonalát, ahogy fentebb már leírásra került:

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & Path & ";"

Ezután jön nagyjából a lelke az egész kódnak, hiszen elkezdünk a recordsettel dolgozni. A Recordset objektum Open metódusát fogjuk felhasználni, amelynek segítségével egy táblát vagy lekérdezésünk eredményét tudjuk megnyitni paraméterek felhasználásával.

rs.Open query, cnn, adOpenStatic, adLockReadOnly

Ez lesz a felhasznált kódsorunk itt, amely értelemszerűen az Open metódus paramétereit tartalmazza (mind opcionális, sorban: Source, ActiveConnection, CursorType, LockType, Options). Elsőként a query jön a Sourcehoz, ami a lekérdezésünket jelenti, mint forrást. Aztán a cnn, ami az aktív kapcsolatot állítja be, majd az adOpenStatic kurzortípus, ami egy statikus kurzor, azaz egy statikus másolathoz férünk hozzá adatainkról, törléseket, változtatásokat nem fogunk látni (ennek ellenpárja tehát értelemszerűen az adOpenDynamic). A negyedik használt paraméterünk pedig az adLockReadOnly, ami pedig azt határozza meg, hogy miközben feldolgozunk, milyen lock legyen az adatainkon, azaz az adLockReadOnly csak olvasható adatokat jelent.
Itt tartunk most:

Nagyon kevés van már csak hátra, most jön a Recordset objektum CopyFromRecordset metódusának bevetése, ami jelen esetben az első sheetünk A1 cellájába fogja bemásolni az előbb megnyitott rs Recordsetünket:

Sheet1.Range("A1").CopyFromRecordset rs

Már csak az van hátra, hogy bezárjuk a recordsetünket és a kapcsolatot is befejezzük, majd mindkét változót kiürítsük:

rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
Exit Sub
End Sub

Kódunk lefuttatása után pedig ott is az eredmény az első munkalapunkon:

Innentől kezdve pedig a határ a csillagos ég, hiszen egyrészt több táblából is szedhetünk össze adatokat, csak úgy kell megírnunk az SQL-t, másrészt az Excelben pedig már bármit megcsinálhatunk ezen adatokkal.

Szövegként a kód:

Sub access()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim query As String
Dim Path As String
Path = "C:\Database1.accdb"
query = "SELECT * FROM status"
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & Path & ";"
rs.Open query, cnn, adOpenStatic, adLockReadOnly
Sheet1.Range("A1").CopyFromRecordset rs
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
Exit Sub
End Sub

Hihetetlen, de tényleg igaz: a projektmenedzsmentben a mai napig rendszeresen alkalmazott Gantt-diagram első verzióját még a 19. században készítették el, ezt tökéletesítette aztán a névadó amerikai Henry Gantt még a 20. század elején az I. világháború alatt.

Gantt-diagram készítési technikákkal tele van az internet, mégis a mai poszt egy ilyen diagram rendkívül egyszerű elkészítési módjáról fog szólni, felelevenítve egy-két, talán már régen elfelejtett technikát - hiszen Ganttot lehet elég egyszerűen, szimplán cellák kiszínezésével is csinálni. De most értelemszerűen nem így fogunk tenni, egy egyszerű diagramot használunk a megfelelő cél elérésére, hiszen az bármikor updatelhető a későbbiek során.

Kezdjük is a leginkább megszokott kiinduló táblájával a Gantt-diagramnak:

A dolgozók neve helyett nyugodtan képzelhetünk/írhatunk projektszakaszokat is, a lényeg úgyis a dátumokban rejlik. Ezután egy teljesen egyszerű és még üres Stacked Bar diagramot szúrjunk be a munkalapunkra az Insert Ribbonfül Charts szekciójából:

Kattintsunk jobb gombbal az üres diagramunkon és válasszuk a Select Data menüpontot:

A felugró ablakban értelemszerűen az Add gomb segítségével adjunk adatokat a charthoz, méghozzá úgy, hogy a Series neve az Oktatás kezdete cellánk (tehát az elnevezés), míg az értékek az oktatás kezdetét jelző időpontok cellái legyenek:

Tegyük meg ugyanezt egy másik adatsorral is, ez pedig legyen az Időtartam oszlopunk - az Időtartam elnevezés lesz a Series neve, az értékek pedig értelemszerűen a napok, hónapok stb.:

Maradva még mindig a Select Data Source ablakban, az Y tengelyünkre varázsoljuk rá az adott kolléga, feladat, projektszakasz nevét, szimplán az Edit gombra való kattintással majd az érintett cellák kijelölésével:

Ezután jön az egész történet kulcsa, kattintsunk a kék adatsorunkra a diagramban (vagy arra, amelyik a kezdés időpontját mutatja), majd jobb gombbal való kattintás után Format Data Series:

A jobb oldalon felugró formázási lehetőségeknél a kitöltést és a keretet is tüntessük el:

Ezután már csak az marad, hogy formázgassuk a megmaradt adatsort, ahogy szeretnénk - a gap növelésével a különböző sorok között, a méret növelésével, csökkentésével stb.:

És ezzel készítettünk is egy iszonyúan egyszerű (ez hangsúlyozandó, hiszen komplexebb projektek esetén ez bőven nem lesz elég semmire) Gantt-chartot, amelynek előnyei mellett van egy hátránya is: túl lelkes projektkezelők akár túl is lőhetik a célt és valami olyan komplex dolog születhet belőle, aminél egy manuálisan összerakott apró tábla is többet érhet. A Gantt erőssége igazából más projektmenedzsment eszközzel (és mondjuk automatizmussal) kombinálva adja ki magát és ilyen eszközöket a továbbiakban is szeretnék majd megmutatni.

Annak ellenére, hogy még 2014-ben a Microsoft bejelentette, hogy már nem fogja folytatni a Microsoft InfoPath modult a jövőben, a mai napig elég sok ilyen form van használatban, nem csoda, hogy a redmondi cég papíron 2023-ig támogatni fogja ezt az applikációt. Egyébként nem a koncepció szűnt meg, hanem sokkal inkább az integráció felé megy el a Microsoft, hiszen ezen formok jelentős részét SharePointokkal összekötve használták, így nem meglepően a SharePoint Server 2016-os verziója is magában foglal már ilyen jellegű funkciókat, de az Office 365 csomagjában is ott van még mindig az InfoPath.

Hozzám mindig közel is állt ez a modul, hiszen ha az embernek volt ideje, nagyon tetszetős és hatékony formokat tudott összeállítani, akár probléma bejelentéséről, akár közös rendezvény szervezéséről volt szó. A mai posztban egy gyakran felmerülő InfoPath kérdést fogok megválaszolni: hogy lehet azt elérni, hogy az elkészült és megnyitott formot a felhasználó egy Submit vagy akármilyen más névre hallgató gomb segítségével el tudjon küldeni egy bizonyos konstans vagy megadott mezőből származó e-mail címre?

Adott a következő, roppant egyszerű kis InfoPath form:

Erre fogunk most beszúrni egy Submit gombot, ami majd egy e-mail címre küldi el a formot. Első lépésként a Home Ribbonfül Controls szekciójából szúrjunk be egy gombot (Button):

Kattintsunk jobb gombbal és válasszuk a gomb tulajdonságait:

A felugró ablak General fülén, az Action legördülő menüt állítsuk át Submitra, majd kattintsunk a Submit Options gombra:

Az újabb ablakban két dolgot kell tennünk. Egyrészt megengedjük usereinknek, hogy elküldjék a formot (Allow users to submit this form pipa), másrészt az Add gombra is kattintanunk kell, hogy összehozzunk egy data connectiont a gombhoz:

Ezután már csak azt kell megadnunk, hogy milyen e-mail címre, milyen tárggyal és milyen szöveggel menjen az e-mail - itt a lehetőségeink egyébként jóval nagyobbak, hiszen az f(x) gombra kattintva képletet is tudunk bevinni, azaz megadhatjuk például, hogy a form melyik mezőjéből milyen értéket vegyen fel az e-mail.

A Next gombra való kattintás után már csak azt kell eldöntenünk, hogy az e-mail mit küldjön még - legyen-e csatolmány vagy csak a form aktív képe:

Ezután már csak egy óriási okét kell nyomnunk és kész is vagyunk:

Ahogy látjuk, innentől kezdve már szépen hasít is az e-mailes küldés:

Nem egy ördöngősség volt a fentebb taglalt dolog és elképzelhető, hogy sokaknak ez a kisujjában van már, de a poszt pont azoknak szól, akik érdeklődnek ilyen jellegű megoldások iránt még a leállított fejlesztés ellenére is.

Nem állítom, hogy az évszázad problémája Excelben, de ettől függetlenül hasznos lehet egy olyan egyszerű kis trükk ismerete, amelynek segítségével a Conditional Formatting Harvey Balls típusú ikonkészletét a Conditional Formatting bizonyos mértékű kiiktatásával, bármilyen színben és méretben alkalmazni tudjuk.

Hiszen miután ezek a kis labdák egy normál cella méretére, ikonméretűre vannak kidolgozva, nagyításuk során könnyen "elpixelesednek", színük változtatására pedig nincs egyszerű mód - eléggé kötött az alkalmazásuk, ahogy látható is a lenti példán:

Mit is kellene most tennünk? A megoldást a mindenféle más formáknál és trükkökknél igencsak hasznos Segoe UI Symbol Font jelenti, annak is a Geometric Shapes subsetje, ahol szépen meg fogjuk találni a Harvey-labdákat:

Ezeket a rövidesen értelmet nyerő másolás okán szúrjuk be egy munkalapra, nagyjából teljesen mindegy, hogy hova, hiszen csak rövid ideig lesz rájuk szükségünk:

Vegyük észre, hogy ezek már értelemszerűen könnyedén méretezhetőek és színezhetőek:

Ezután következő lépésként szépen ezt az öt ikont vigyük fel Custom Number formátumként a CTRL+1 billentyűkombinációval megnyitható Format Cells Number füle alatt és innentől kezdve már a Conditional Formattingban ezek meghivatkozható, felhasználható formátumok lesznek.

Most az előbb már Segoe UI Symbol fonttal megalkotott Harvey-labdáink mellé szúrjuk be az általunk meghatározott értékhatárokat:

Aki pedig sokat dolgozott már Conditional Formattinggal, az valószínűleg azonnal látja, hogy mi is itt a befejező lépésünk - egyszerűen készítünk öt formázási feltételt a színes, nagyméretű Harvey-labdákat igénylő táblán, valahogy így:

Mit is csináltunk? Egyszerűen megfogtuk a formázandó, Harvey-labdákra alakítandó táblánkat, kijelöltük, majd a Home Ribbonfül alól beillesztettünk öt formázási feltételt a New Formatting Rule - Use a formula to determine which cells... felhasználásával, ahol minden feltételnél értelemszerűen az adott, előbb meghatározott Custom Number formatot válasszuk ki:

A feltételek pedig magukért beszélnek, legyen teljes a kör, ha nagyobb az ehhez megadott határértéknél a szám, aztán félkör ha annál az értéknél nagyobb és így tovább.

Aztán ha ezt ráküldjük a kijelölt táblánkra, kész is a megoldás - formázható, színezhető, méretezhető Harvey Balls. És ha akarnánk, még a számértéket is hozzá tudjuk csapni a Custom Number Format további tupírozásával.

Sokszor igazán komplex kódok nélkül fel tudjuk úgy turbózni egyszerű kis Excel-tábláinkat, hogy lenyűgözhetjük felhasználóinkat vagy akár meg is könnyíthetjük az életüket. Ennek most egy példáját mutatom be azzal a VBA-kóddal, amely azt teszi lehetővé, hogy egy "Instructions" vagy "Használati utasítás" vagy bármilyen más névre hallgató első sheet legördülő menüjéből választhatjuk ki azon munkalapok nevét, amelyeket meg akarunk éppen jeleníteni. Így nem kell bogarászni a sok sheet között egyesével, szimplán csak választ egyet a user és csak az fog látszani - persze ezt még csoportokra is oszthatjuk és hasonló nyalánkságokkal csinosítgathatjuk, de az alaplogika akkor már nem változik.

Mindennek a kiindulási alapja egy Data Validation List lesz, úgyhogy még a VBA Editor előtt ezt kell elkészítenünk. Valahol a kiinduló munkalapunkon vagy bármely más technikai célokat szolgáló sheeten készítsünk el egy ehhez hasonló listát:

Ennek a tartománynak aztán persze célszerű nevet adni, hogy bővítése esetén automatikusan változzon a legördülő menünk is, de a lényeg, hogy a lista elkészülte után a Ribbonunk Data füle alatt található Data Tools szekció Data Validation funkcióját kell használnunk:

Itt készítsünk egy List típusú adatvalidációt, amelyhez a lista legyen az előbb elkészített tartományunk, akár szimplán cellákként, akár nevesített tömbként meghivatkozva:

És íme, már a felület meg is van, ami mögé a kódot kell majd beillesztenünk:

Szépen mehetünk is át a VBA Editorba ALT+F11 alkalmazásával, ahol a kiinduló munkalapunkra a megszokott alapesettől eltérően a Worksheetünk Change eseményéhez fogunk beszúrni egy futtatandó kódot, azaz az ezen a munkalapon történő változások indítják el a program futását:

Két változóra lesz mindenképpen szükségünk, elsőként definiáljunk egy ws névre hallgató Worksheet típusú változót, amelyet majd a ciklusunkban fogunk felhasználni, másodsorban kell majd egy "kival" névre hallgató String típusú változó is, amelyben a kiválasztott lehetőség nevét tároljuk el. Értelemszerűen tehát a deklarálás után már be is adhatjuk "kival" változónk értékét, ami ugyebár annak a cellának az értéke lesz, ahol kiválasztjuk a sheetünk nevét (vagy a Minden opciót):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim kival As String
kival = Worksheets("Kiinduló Sheet").Range("H7").Value

Amint azt tudjuk, a Target egy Range típusú hivatkozás, azaz itt megadhatjuk azon tartományt, amelynek változása esetén történjen az eseményünk, azaz ha a Target.Address = Range("H7").Address, akkor jöjjön a vizsgálatunk és a sheetek megjelenítése vagy éppen eltüntetése.

És itt most egy Select...Case utasítást fogunk bevetni, ami meghatározott utasítások közül fogja azt lefutni, ami megfelel egy bizonyos kifejezés értékének. Jelen esetben ez az érték a Target hivatkozásunk értéke, azaz Target.Value.

Itt tartunk tehát most:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim kival As String
kival = Worksheets("Kiinduló Sheet").Range("H7").Value
If Target.Address = Range("H7").Address Then
Select Case Target.Value

És most már szépen csak végig kell mennünk a lehetőségeken. Nézzük a legegyszerűbbet, a "Minden" opció kiválasztását. Ebben az esetben a munkafüzetünk összes munkalapján menjen végig a program és minden munkalap Visible tulajdonságát állítsa láthatóra, amit egy For Each...Next ciklussal a korábbi posztok alapján már könnyen meg tudunk csinálni.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim kival As String
kival = Worksheets("Kiinduló Sheet").Range("H7").Value
If Target.Address = Range("H7").Address Then
Select Case Target.Value
Case "Minden"
For Each ws In ActiveWorkbook.Sheets
ws.Visible = xlSheetVisible
Next ws

A másik esetünk pedig valamely munkalap nevének kiválasztása lesz (azaz jelen esetünkben Case Else, azaz bármilyen más esetben), amelyhez viszont nem lesz elég egy For Each...Next ciklus használata, abba még egy IF utasítást is bele kell csempésznünk.

De még mielőtt szépen leírjuk, hogy mi is történjen, ismerkedjünk meg az InStr funkcióval. Ez nem tesz mást, mint az első paramétereként megadott karakterszámtól kezdve a második paramétereként megadott szövegben megkeresi a harmadik paramétereként megadott szöveget. Ha megtalálja, akkor visszaadja azt a karakterszámot, ahol ez a szöveg kezdődik a másikban, ergó ha nullánál nagyobb a visszaadott érték, akkor van egyezésünk, tehát az adott munkalap neve megegyezik a kiválasztott értékkel.

InStr(1, ws.Name, kival): az első karaktertől kezdve megnézi, hogy az adott Worksheet nevében benne van-e a "kival" változó értéke.

Innentől kezdve pedig mehet a kód megírása a már megszokott "regényformátumban" is, azaz munkafüzetünk minden egyes munkalapja esetén nézze meg, hogy az adott munkalap nevében benne van-e a "kival" változó értéke - ha igen, akkor legyen látható az a cella, ellenkező esetében ne. Itt annyival még meg lehet dobni az If utasítást, hogy a "Kiinduló Sheet" névre hallgató, legördülő menüt tartalmazó munkalapunk mindig legyen látható.

Ez valahogy így néz ki:

Case Else
For Each ws In ActiveWorkbook.Sheets
If InStr(1, ws.Name, kival) > 0 Then
ws.Visible = xlSheetVisible
Else
If ws.Name <> "Kiinduló Sheet" Then
ws.Visible = xlSheetHidden
End If
End If
Next ws

Összességében tehát a For Each...Next és If már megtanult utasításai mellett a Worksheetek Visible tulajdonságát és az InStr utasítást kell ismernünk a kód összeállításához.

És ezzel már szépen meg is van a működő programunk, bármilyen sheet kiválasztása esetén csak az jelenik meg plusz a kiinduló munkalapunk:

Szövegként a kód:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim kival As String
kival = Worksheets("Kiinduló Sheet").Range("H7").Value
If Target.Address = Range("H7").Address Then
Select Case Target.Value
Case "Minden"
For Each ws In ActiveWorkbook.Sheets
ws.Visible = xlSheetVisible
Next ws
Case Else
For Each ws In ActiveWorkbook.Sheets
If InStr(1, ws.Name, kival) > 0 Then
ws.Visible = xlSheetVisible
Else
If ws.Name <> "Kiinduló Sheet" Then
ws.Visible = xlSheetHidden
End If
End If
Next ws
End Select
End If
End Sub