Itt a hétvége - három könnyed Excel-tipp, hogy jól induljon

2017. szeptember 22. - Office Guru

Napjaink internetének egyik rákfenéje a tömegével születő "clickbait" irományok folyama, amelyeknek fő célja a kattintások számának növelése és a látogatók becsábítása egy adott oldalra. Ezen "clickbait" cikkek egyik speciális alfaja szerintem a toplistázás - 7 dolog, amitől lefogysz, 11 dolog, amit a magyarok nem szeretnek vagy
éppen 8 film, amit látni kell. Nos, még mielőtt átmennék filozófiai elmélkedésbe, a következő posztban következzen három, kevésbé ismert Excel-funkció és lehetőség bemutatása, hiszen ilyen listás poszt már elég régen volt errefelé.

Az első ilyen lehetőség a dinamikus transpose funkciója, amelynek segítségével az oszlopokból generált soraink értékei frissülni fognak akkor is, ha az eredeti táblában változtatjuk az értéket, tehát nem kell újra transpose-t csinálnunk. Adott a következő példatábla:

uno.JPGJelöljük ki és CTRL+C segítségével tegyük a vágólapra:

dos.JPGEzután jön a jobb egérgombunk lenyomásával előcsalogatható Paste Special context menü használata, ahol a jobb alsó Transpose checkboxba kell pipát tennünk (ez valószínűleg senkinek nem okoz fejtörést):

tres.JPGEzután, ahogy látható is lentebb, megszületett az átalakított táblánk, ahol az oszlopok már a sorok és a sorok már oszlopok az eredetihez képest.

cuatro.JPGÚgy, hogy nem vesszük még le a kijelölést, kezdjük el szimplán bevésni a Formula Barba a következőt:

=TRANSPOSE(C3:F9)

Ahol a zárójelek közé az eredeti tábla tartományát tegyük. Még mielőtt ENTERT nyomnánk, figyeljünk rá, hogy most tömbről beszélünk, így CTRL+SHIFT+ENTER lenyomására van szükségünk. És láss csodát, innentől kezdve már szépen működik is a kapcsolat a két tábla között.

Azért ennek a technikának van legalább két hátulütője, az egyik hogy a formátum széthullik, a másik pedig az, hogy innentől kezdve az új táblánkat nem tudjuk már szerkezetileg módosítani, tehát új sorokat, oszlopokat nem tudunk hozzáadni beszúrással.

A következő tipp egyértelműen az alaptudás részét kellene, hogy képezze Excel-használat esetén, de sajnálatos módon még nálam is előfordul, hogy ha sorszámokat kell egy adott oszlopban kitöltenem egymás után, akkor egyszerűen csak elkezdem húzni lefelé és kész. Na de mi van, ha nagyon hosszú sorozatról beszélünk?

cinco.JPGItt lesz segítségünkre a Ribbonunk Home füle alatt található Editing szekció Fill menücsoportja:

seis.JPGItt ugyanis van egy Series funkció, amire ha rákattintunk, már adja is magát a megoldás:

siete.JPGSzimplán csak állítsuk be, hogy sorokról vagy oszlopokról beszélünk, milyen jellegű kitöltést akarunk és mi legyen a kezdő és záróérték, és máris kész vagyunk.

Az utolsó tipphez biztos sokaknak van saját élménye is, hiszen kivel nem fordult még elő, hogy a szépen otthonosra customizált Office-programjait egy gépcsere, egy új installáció miatt elveszítette és utána hetekbe telt visszahozni az eredeti állapotot, például a Quick Access Toolbar esetén, ahova gyakran használt funkcióinkat, saját makróinkat pakoltuk korábban.

Egyszerűen csak kattintsunk jobb gombbal bárhova a Toolbaron és válasszuk a Customize menüpontot:

ocho.JPGAz itt felugró ablakot már ismerjük, itt lehet hozzápakolni mindenféle funkciókat a toolbarhoz, ezúttal azonban a jobb sarokra kell fókuszálnunk, ahol van egy Export/Import gomb:

nueve.JPGÉs innentől kezdve már ki is menthetjük majd később visszaimportálhatjuk QAT-re vonatkozó beállításainkat:

diez.JPG

Burn down chart - egyszerű, hatásos fegyver egy projektvezető kezében

A burn down chart egy olyan diagram, ami általánosságban azt mutatja meg, hogy mennyi feladat van még hátra egy adott időperióduson belül, azaz mondjuk egy projekt esetén az x tengelyen jelenítjük meg az időintervallumot, az y tengelyen pedig a megoldandó problémák számát. Például a jelenleg hátralévő feladatainkra készíthetünk egy becslést, hogy nagyjából milyen ütemben haladunk és ez alapján meg tudjuk becsülni a befejezés időpontját - erre pedig tudunk majd tenni egy tényleges haladási sebességet, szóval összességében egy ilyen burn down chart rendkívül egyszerű módon mutatja meg projektünk alakulását. Nézzük is meg két rövid példán!

A következő példatábla egy adott időpillanatban elvárt és tényleges problémaszámokat mutatja egy projekten belül:

bd1.JPGHa most szimplán kijelöljük ezt a táblát és az Insert ribbonfül Charts szekciójából beszúrunk egy kétdimenziós vonaldiagramot, akkor máris láthatunk egy (nem is annyira) kezdetleges verziót egy burn down chartra:

bd2.JPGEzzel az egyszerű példával szerint nagyjából mindenki számára egyértelmű válhatott, hogy mi is az a burn down chart, mindenesetre az biztos, hogy jóval több adatpont esetén jóval hatásosabb és jóval nagyobb létjogosultsága van egy ilyen diagramnak.

Vagy csak képzeljünk el egy olyan helyzetet, amikor tudjuk, hogy a mai napon van még 15 megoldandó projektes feladatunk és a cél, hogy ez nulla legyen mondjuk december elsején. És ehhez nincs adott időpontra vonatkozó elvárt eredményünk, csak a kiinduló állapotot, a célt és a chart bemutatásának idején fennálló státuszt akarjuk prezentálni. Mint mondjuk ez a mintatábla:

bd3.JPGEbben a formában úgy tűnhet, hogy nem tudunk beszúrni rá egy kétdimenziós vonaldiagramot, mert az nem fogja azt mutatni, amit szeretnénk. Vagyis úgy tűnik, mintha nem azt mutatná, hiszen ha kijelöljük a táblát és az Insert ribbonfül Charts szekciójából beszúrjuk a kétdimenziós vonaldiagramot, akkor ezt látjuk:

bd4.JPGInnen viszont már csak egyetlen lépés hiányzik a végcélhoz - kattintsunk jobb gombbal a chart területén, majd válasszuk a Select Data opciót:

bd5.JPGItt válasszuk a bal sarokban a Hidden and Empty Cells gombot és már adja is magát a megoldás:

bd6.JPGSzimplán csak pipáljuk be a Connect data points with line rádiógombot és láthatjuk is az eredményt:

bd7.JPGHasonlít az előző példához, de célját és a megmutatandó sátuszt tekintve mégis más.

Szóval nagyon röviden, nagyon egyszerű bemutatással ilyen lenne egy burn down chart, amelyre lehet jókat és rosszakat is mondani, de ismerni sosem árt. Egyébként alapvetően az egyik leggyakoribb kritika, ami az ilyen burn down chartot éri, az az, hogy a projekt vezetője vagy a csapat nagyjából olyan időbecslést ad, amilyet akar a köztes időpontokra, tehát a projekt végéig lehet olyan kimutatást összehozni, ami alapján a csapat végig túlteljesít, miközben valójában nem is. Erre szoktak beletenni felülvizsgálati pontokat, amikor újraértékelésre kerülnek az eredetileg meghatározott időpontok.

Ha nem tetszik a Slicer vagy csak más megoldás után kutatsz - a Camera Tool segít!

Az Excelnél eléggé hozzászokhattunk már ahhoz a csodálatos lehetőséghez, hogy a legtöbb problémára nem csak egy, de több megoldás és válasz létezik, nincs ez másként annál az igénynél sem, amikor mondjuk egy legördülő menüből kiválasztott elemhez tartozó chartot akarjuk éppen megjeleníteni. Egy ilyen esetnél a legegyszerűbb megoldás az újabb Excel-verziókban a Slicer használata, amiről már elég sokat beszéltünk is itt korábban - de ha valaki nem preferálja ezt (vannak azért formázást illetően bizonyos korlátai), akkor igazán váratlan helyről, a Camera eszköztől is jöhet támogatás. Adott csak a példa kedvéért az alábbi két chart és táblázat:

1.JPGAz egyik havi, a másik negyedéves adatokat tartalmaz, a célunk pedig az, hogy egy dashboardban a user által egy legördülő menüből kiválasztott opciónak megfelelő diagramot jelenítse meg. Első lépésünk most az lesz, hogy egy másik sheeten vagy az adott dashboardon létrehozzuk a legördülő menüt a Data ribbonfül Data Tool szekciójának Data Validation funkciójával, hogy megadjuk a usernek a választási lehetőséget:

2.JPGEzután jön a kulcsmomentum a feladatban, amikor is a Formulas ribbonfül Defined Names szekciójából elindítjuk a Name Managert és létrehozunk három nevesített tartományt. Az első tartományunk az első chartot tartalmazó cellák halmaza, a második tartományunk pedig a második chartot tartalmazó cellák halmaza lesz, ahogy a lenti képen is látható például a Negyedéves adatok esetén:

3.JPGVegyük észre, hogy a Negyedéves nevesített tartományunk pontosan azon cellák halmaza, amelyeken a Negyedéves diagramunk szerepel. Ha ez megvan, akkor létrehozzuk a harmadik nevesített tartományt is, a mi esetünkben ennek neve a "Nézet" lesz és a legördülő menüt tartalmazó cellára hivatkozik egy INDIRECT függvénnyel megspékelve. Az INDIRECT abban segít majd nekünk, hogy a meghivatkozott F1 cella értéke (tehát jelen esetben a "HAVI" vagy "NEGYEDÉVES" opciók alapján meg fogja keresni és vissza fogja adni az ezen a néven futó tartományt.

4.JPGTehát még egyszer, a következő három nevesített tartományra van szükségünk:

5.JPGKövetkező lépésként, ha még nem tettük meg, az Excel Options Quick Access Toolbar menüjében adjuk hozzá a QAT-hez a Camera Toolt a lentieknek megfelelően, mert ez az egyetlen esélyünk, hogy ehhez a funkcióhoz hozzáférjünk:

6.JPGEzután kattintsunk szépen a QAT-n megjelent kamera ikonra, majd nagyjából oda, ahol szeretnénk megjeleníteni a chartjainkat, csináljunk egy fényképet. Ez valahogy most így fog megjelenni, a lefényképezett cellát fogja nekünk mutatni első körben:

7.JPGNincs más hátra, mint ezt a fényképet a Formula baron egyenlővé tenni a "Nézet" nevű nevesített tartománnyal és máris működik a dolog:

8.JPG

Elmélkedés az XLSB-ről - előnyök és hátrányok

Nagy Excel-fájlokkal való ügyködés, azok tárolása és mozgatása kapcsán elég gyakran felmerülő tanács a "Mentsd le xlsb-be!" javaslat, amely aztán sokakban félelmet is kelt, hogy nem fogja-e ez a formátum képletek, formázások vagy éppen makrók elvesztését okozni. És bár nincs új a nap alatt, az internet korában meg főleg, azért a következő posztban kicsit körbejárom az XLSB formátum előnyeit, hátrányait és megpróbálom eloszlatni a bináris fájllal szembeni ellenérzéseket is. Alapvetően egy XLSX valójában tömörített, összecsomagolt XML szövegfájlokat tartalmaz, (amelyeket egyébként meg is tudunk nézni, ha átnevezzük a kiterjesztést mondjuk .zip-re), míg az XLSB a régi iskola gyermeke, azaz egy egyszerű bináris fájl, mint a régi szép időkben az XLS volt. És ebből adódik a legnagyobb előnye is, a mérete - egyértelműen és észrevehetően kevesebb helyet foglal a merevlemezünkön, a megtakarítás elérheti akár a 10-20%-ot is, tehát értelemszerűen az XLSB-nek kisméretű fájlok esetén nincs igazából létjogosultsága (sőt, van rá példa, hogy bizonyos méret alatt az XLSB nagyobb méretű lesz, mint az eredeti fájl).

hemota1.JPG

hemota2.JPGHacsak nem iszonyatosan hosszú formulákat alkalmazunk, mert a 8192-es karakterszámos limitet az XLSB esetén figyelmen kívül hagyhatjuk - egy XLSX nem fogja támogatni az ennél hosszabb függvényeinket. És ami a legjobb, hogy a mérete mellett a megnyitási sebessége is sokkal gyorsabb, köszönhetően annak a ténynek, hogy egyszerűen bináris adatokat tölt be az Excel, XML fájlok lefordítása helyett - a megnyitási idő akár 50%-kal is csökkenhet. Persze, ha minden ilyen szép és jó volna, akkor feltehetnénk a kérdést, hogy mi szükség van egyáltalán XLSX-re és XLSB-re - nos a legnagyobb probléma az utóbbi bináris formátummal az, hogy az XLSB nem kompatibilis az Excel korábbi verzióival (2007 előttiekre gondoljunk) és más táblázatkezelőkkel (mint például az OpenOffice).

hemota3.JPGEzt az alapvetően tényleg figyelemre érdemes hátrányt leszámítva a többi kellemetlenség igazából nem is lényeges, hiszen az, hogy az Excel Ribbont nem tudjuk customizálni/módosítani (tehát először visszamegyünk XLSX formátumra, majd a módosítás után ismét XLSB-be) vagy hogy XLSB megnyitásánál nem tudjuk, hogy az adott fájl tartalmaz-e makrókat - ezek olyan kellemetlenségek, amelyek nem összemérhetőek a helytakarékossági szempontokkal (és azzal se törődjünk nagyon, hogy XLSB-t az Excel összeomlása után egy kicsit nehezebben tudunk helyreállítani, mint XLSX formátumú fájlokat).

Ettől függetlenül persze a felesleges sorok törlésével, felesleges formátumok elhagyásával, az automatikus kalkuláció kikapcsolásával és egyáltalán, felesleges adatok generálásának elkerülésével jelentős megtakarítást érhetünk el anélkül is, hogy formátumok között váltogatunk. És nem, nem igazak azok a feltételezések sem, miszerint az XLSB-ben gyorsabban lehet dolgozni vagy éppen nem működnek bizonyos funkciók, mert erről szó sincs, használjuk bátran, ha szükséges. Arra azért figyeljünk, hogy ha nagy bőszen elkezdünk XLSB fájlokat generálni, tájékoztassuk erről felhasználóinkat, kollégáinkat is, mert a tapasztalatok azt mutatják, hogy alapvetően óvatosságot szül a formátumot nem ismerőkben, ha egy XLSB fájlt kapnak levélben vagy találnak a folderükben.

Due consigli - két Exceles jótanács egy erős olasz fekete mellé

A mai rövid poszt két, Excel munkafüzet védettségével kapcsolatos témát fog feszegetni, remélhetőleg úgy, hogy az legalább egy-két olvasó számára hasznosnak bizonyul majd.

Az első témát egy munkalap levédésével kezdjük, ahogy a lenti képen is látható, még a cellák kijelölésének lehetőségétől is megfosztjuk a kedves felhasználót:

protect1.JPGÍgy pedig, amikor ez egy másik felhasználó kezébe kerül, akkor szegény nagyon nem tud mit csinálni vele, akár képet is kaphatott volna:

protect2.JPGÉrtelemszerűen törölni vagy hozzáadni nem lehet, de miután a cellák kijelölését is beállítottuk a levédésnél, így még másolni sem lehet az adatokat további felhasználás céljából. Legalábbis ezt hiszi az eredeti fájl levédője. Ugyanis bár egérrel a kijelölés nem fog menni, a bal sarokban található Name Box használatával bármilyen tartomány vagy cella kijelölhető, szimplán csak írjuk be a tartományt és nyomjunk egy Entert:

protect3.JPGEzután vagy az egér jobbgombos Context menüjével vagy CTRL+C lenyomásával, de már be tudjuk rakni a vágólapra a kívánt adatokat:

protect4.JPGMajd be is tudjuk illeszteni, ahova csak akarjuk:

protect5.JPGSzolgáljon tehát ez tanulságként azért, hogy a cellák kijelölésének lehetőségétől is megfosztani a felhasználókat, eléggé radikális módszer, csak kivételes esetekben alkalmazzuk.

A másik apró tanács a Protection kérdését egy másik irányból közelíti meg, azaz néhány mondatban azt fogom leírni, hogy tudjuk megakadályozni, hogy felhasználóink mozgassák sheetjeinket, átnevezzék őket stb. Rengeteg Exceles anyagban, könyvben, tipplistában elő szokott ez fordulni, mégis azt tapasztalom, hogy elég sokszor merül fel a kérdés, "hogy tudnám lockolni a munkalap nevét".

A megoldást a Review ribbonfül Changes szekciója rejti, itt bújik meg ugyanis a Protect Workbook lehetősége, amely pont ezt az igényt fogja teljesíteni.

protect6.JPGEgyszerűen kattintsunk rá és máris látni fogjuk, hogy mennyire egyszerű az egész:

protect7.JPGEgyik tanács sem nevezhető világmegváltó újdonságnak, de remélhetőleg egy reggeli kávé mellé tökéletes napindító lehet annak, aki még nem ismerte őket.

A varázslatos, de elég haszontalannak tartott XOR

Valószínűleg eme blog látogatóinak többsége elég jól ismeri az AND (a paramétereiként megadott argumentumokat vizsgálja meg és ha mindegyik teljesül, akkor TRUE-t ad vissza, minden más esetben FALSE értéket), OR (csak akkor ad vissza FALSE értéket, ha mindegyik paramétereként megadott argumentum FALSE) és NOT (TRUE-t FALSE-ra állít és fordítva) formulákat és már valószínűleg ezek használata sem túlságosan gyakori, hiszen egy IF és legrosszabb esetben egy IF-OR kombinációval elég sok fejtörő megoldható - így aztán jómagam egyetlen egyszer sem láttam éles, mindennapi használatban a jobb sorsra is érdemes XOR függvényt.

Definíciója szerint a XOR függvény a "kizáró OR" végrehajtására szolgál, ami gyökeres ellentéte az OR-nak, ami egy "diszjunkciós OR" formula. Utóbbi mindig TRUE-t ad, ha bármelyik argumentum TRUE és csak akkor ad FALSE eredményt, ha mindegyik argumentum FALSE, míg a XOR kizárólag abban és csakis abban az esetben ad TRUE eredményt, ha csak egyetlen érték TRUE, ha két TRUE és két FALSE van (két logikai érték esetén), akkor FALSE az eredmény. Kettőnél több argumentum esetén a XOR csak akkor ad vissza TRUE értéket, ha a TRUE értékek száma páratlan, páros számnál az eredmény FALSE.

xor01.JPGAlapvetően Excelben nem igazán elterjedt és gyakori a használata, semmiképp annyira mint matematikában vagy az információelméletben hibák felismerésére. Utóbbinál előkerülhet a paritásellenőrzés kérdése is, amely hálózaton át küldött adatok sértetlen megérkezésének validációjánál merülhet fel - az elküldendő adatokat bitek blokkjaira bontjuk, majd a blokkokban lévő 1 értékű biteket megszámoljuk és így meghatározzuk a "paritásbit" értékét. Ha a bitek száma páros, akkor a paritásbit értéke 0, ha páratlan akkor 1 (páros paritás esetén). Na ez a XOR egyik tipikus mintapéldája.

És hogy milyen esetekben lehet használni a XOR függvényt? Például képzeljük el, hogy boltok esetében azt akarjuk megvizsgálni, hogy tartották magukat egyetlen beszállítóhoz és nem váltogatták-e nap-nap után őket, akkor erre például jó lehet a XOR:

xor02.JPGDe azért gyorsan belátható, hogy nincs igazi létjogosultsága ennek a függvénynek, hiszen a fenti példa esetében még az AND/OR/IF/NOT végtelen számú kombinációja helyett is egyszerűbb, ha szimplán egy =L4<>M4 összehasonlítást hajtunk végre mondjuk az első sornál. Több argumentumnál persze az AND/OR/NOT/IF nagyjából bármeddig folytatható egymásba ágyazása fog segíteni, de igazából ilyen jellegű esetet nehezen tudok elképzelni Excelben - viszont erre például már tökéletes lenne a XOR.

Kedves Olvasó, tudsz olyan valódi életből vett példát Excelben, ahol a XOR a legjobb megoldás?

Két módszer üres könyvtárak gyors törlésére

Újabb olvasói kérdés kerül feltérképezésre a következő posztban, méghozzá egy olyan, amelynek megoldása valószínűleg sokak számára segítség lenne, ha egyáltalán ilyen feladatok esetén felmerülne ötletként az automatizálás/nagyobb csomagban történő végrehajtás. A kérdés alapvetően VBA-ra irányul, méghozzá arra, hogy hogyan lehet Excelből egy bizonyos könyvtár összes üres alkönyvtárát törölni.

Mielőtt rátérnénk az Excelre, azért meg kell említeni, hogy van ennél egyszerűbb megoldás is, méghozzá a parancssor használata. Windows 7 esetében a Start menü keresőmezőjébe írjuk be, hogy cmd és nyissuk meg a parancssort, majd szimplán a következő paranccsal hajtsuk végre, amit szeretnénk:

robocopy c:\officeguru\proba c:\officeguru\proba /S /MOVE

Alapvetően a Robust File Copy nagy mennyiségű fájl mozgatásában segítene, de kihasználjuk azt a lehetőséget, hogy a /S paraméterrel ki tudjuk kapcsolni az üres folderek másolását - azaz az elsőként megadott könyvtárból a másodikként megadott könyvtárba (tehát konkrétan ugyanoda) másolja a nem üres foldereket. Ha pedig a /MOVE paramétert is hozzátesszük, akkor mozgatni fog a parancs, tehát nem másol - így látható, hogy hogyan fogjuk elérni a megfelelő eredményt.

folders0.JPGMindenesetre ettől függetlenül fókuszáljunk kedves Olvasónk kérdésére, azaz nézzük meg, hogy VBA-ban hogy oldjuk meg ezt a problémát.

Adott tehát három folder a proba mappánkban, ebből kettőben nincs semmi, egyben pedig van egy fájl. Értelemszerűen a két üres könyvtárat akarjuk törölni:

folders1.JPG

Első lépésként a VBA-editorba lépünk ALT+F11 billentyűkombinációval, majd nekilátunk a kódunknak, méghozzá a változók definiálásával. Rövid kódsorunk magját és legfontosabb elemét a FileSystemObject objektum adja, amely objektumalapú lehetőséget ad számunkra, hogy hozzáférjünk a számítógépünk fájlrendszeréhez. Egyszerűen csak létre kell hoznunk egy FileSystemObject objektumot VBA-ban a Scripting.FileSystemObject szintaxis segítségével, aztán már törölhetünk, beolvashatunk vagy akár létre is hozhatunk fájlokat. Hogy jól tudjuk használni a szintaxist, érdemes megjegyezni, hogy a FileSystemObject a Microsoft Scripting Runtime Libraryben található dll-ként.

Sub konyvtartorles()
Dim fso As Scripting.FileSystemObject
Dim fokonyvtar As Scripting.Folder
Dim alkonyvtar As Scripting.Folder

FSO a fentebb már említett FileSystemObject objektum, amelyet azért hozunk létre, hogy hozzáférjünk a fájlrendszerhez, majd be is állítjuk az FSO objektumot:

Set fso = New Scripting.FileSystemObject

"fokonyvtar" és "alkonyvtar" változóknak már a neve magáért beszél, hogy mire fog szolgálni, most szépen defináltuk őket folderként. Ezután defináljuk a főkönyvtárunk útvonalát:

fokonyvtarut = "C:\proba"

Majd beállítjuk a fokonyvtar változót a FileSystemObject.GetFolder metódus segítségével, amely metódus egy folder objektumot ad vissza eredményül.

Set fokonyvtar = fso.GetFolder(fokonyvtarut)

Ezután már csak egy For Each...Next ciklus van hátra, amelyben megnézzük, hogy a főkönyvtárunk összes alkönyvtára közül melyikben vannak fájlok és melyikben nincsenek, majd töröljük az üreseket. Hasonlóan a fokonyvtarhoz, definiáljuk a subfolderek eléséri útját is, majd a GetFolder metódussal hivatkozunk is erre az objektumra. Ezt azért a ciklus belsejében tesszük meg, mert a jelenlegi kódban egyetlen főkönyvtár van, de bármennyi alkönyvtár lehet.

For Each alkonyvtar In fokonyvtar.SubFolders
utvonal = fokonyvtarut & "\" & alkonyvtar.Name
Set alkonyvtar = fso.GetFolder(utvonal)
If alkonyvtar.Files.Count = 0 Then alkonyvtar.Delete
Next alkonyvtar
End Sub

Ahogy látható, a subfoldereken egyesével fogunk végigmenni és megvizsgáljuk, hogy vannak-e benne fájlok, mert ha nincsenek (.files.count = 0), akkor az adott alkönyvtárat törölni fogjuk. És így megyünk tovább.

Automatikus értesítésküldés Excelből meghatározott időpontban

A legutóbbi posztban említett olvasói kérdés második részére fogok kitérni ebben az írásban röviden, azaz hogyan lehet egy munkalapon lévő feladatlistából azokról az feladatokról automatikus emailt küldeni, amelyeknek lejárt a határideje vagy éppen itt az idő egy figyelmeztetésre. Azt tehát már megbeszéltük korábban, hogyan lehetne egy makrót Excelen kívülről elindítani, most nézzük meg, hogy milyen módon küldünk meghatározott időpontban levelet Outlookon keresztül.

Ebben az írásban még két éve már átnéztük egyszer az automatikus levélküldés makróját, most pedig ugyanezt a kódot fogjuk kicsit kiegészíteni. Adott ez a roppant egyszerű táblázat:

levelk.JPGÉrtelemszerűen bővíthetjük e-mailcímmel, standard szöveggel, címzettel, cc-vel, de a logikát ezen a két oszlopon keresztül is könnyedén meg fogjuk érteni. Szóval a 2015-ös poszt kódját mindösszesen két extra sorral (meg a lezárókkal) kell kibővítenünk a With..End With utasítás előtt a példánkban, méghozzá ezzel a kettővel:

For i = 4 To Range("C65536").End(xlUp).Row
If Cells(i, 3) = Date Then
.
.
.
End If
Next

Mit is csinál ez? A negyedik sortól kezdődően a C oszlop 65536. soráig bezárólag létező tartományban megnézi melyik az utolsó sor, amiben érték van és eddig fogja ismételni a ciklust, majd megnézi az összes ilyen sort a negyediktől kezdődően - majd ha az ebben található érték megegyezik a mai dátummal, akkor folytatja a kódot az e-mail megnyitásával és mezőinek feltöltésével. És egészen addig csinálja ezt, amíg van értékkel bíró cella a C oszlopban. A levelet csak megjeleníti, nem fogja automatikusan még elküldeni.

Aztán ha már ilyen a példatábla, akkor még a Tárgyat írjuk át, hogy a B oszlopból vegye fel az adott sor értékét:

.Subject = Cells(i, 2).Value

És nagyjából kész is vagyunk az alapokkal, innentől kezdve már lehet tuningolni, ahogy szeretnénk - a többi e-mail mezőt is feltölthetjük az Excelből, rakhatunk be egy "Elküldött" oszlopot, amelyben a makrónk megvizsgálja, hogy van-e IGEN válasz és csak arra küld, ahol nincs ilyen és így tovább.

Szövegként a teljes kód a 2015-ös alappal:

Private Sub X()
Dim Outlookprogi As Object
Dim Email As Object

Set Outlookprogi = CreateObject("Outlook.Application")
Set Email = Outlookprogi.CreateItem(0)

On Error Resume Next

For i = 4 To Range("C65536").End(xlUp).Row
If Cells(i, 3) = Date Then
With Email
.To = "officeguruhelp@gmail.com"
.cc = ""
.Subject = Cells(i, 2).Value
.Body = "Problema"
.Attachments.Add
.display
End With
End If
Next
Set Email = Nothing
Set Outlookprogi = Nothing
End Sub

Makró futtatása Excelen kívülről - elmélkedés a lehetőségekről

Nemrégiben jutott el hozzám egy elég gyakran felmerülő feladatra vonatkozó kérés-kérdés (automatikus levélküldés meghatározott időpontokban meghatározott e-mailcímekre), amelyre egy későbbi posztban majd ki fogok térni részletesen, most azonban a kérdés egyetlen részét fogom csak kiragadni, nevezetesen azt az igényt, hogy makrónk az Excel elindítása nélkül fusson le.

Tudomásom szerint csak VBA és Excel felhasználásával ez nem kivitelezhető (más eszközökkel persze van rá mód), valamilyen formában szükségünk van az Excel elindítására - aztán mondjuk a program elindulásával már azonnal futhat is a szükséges kis program.

Vagy készíthetünk egy egyszerű shortcutot is, ami az Excelünket és a makrónkat indítja el és ezt a shortcutot mondjuk Windows 7 vagy korábbi verzió esetén beillesztjük a Start menüből elérhető Startup könyvtárba, hogy kicsikarjuk a gép indulásakor az automatikus elindulást. Későbbi Windows esetén szimplán írjuk a Run ablakba a "shell:startup" parancsot és máris nagyjából ugyanott tartunk.

Van még egy elég gyakran használt és elterjedt metódus, ami viszont már nem szimplán Excel, hanem VBScript, amelyben .vbs kiterjesztéssel bíró programot tudunk készíteni - a VBScript nyelv a VB nyelv részhalmaza és ráadásul csak Windows rendszereken használható, ahol .vbs kiterjesztésű programjainkat viszont roppant könnyedén tudjuk futtatni.

Ezt fogjuk most is kihasználni, ugyanis egy egyszerű kis makrót bizonyos szempontból programként fogunk futtatni egy dupla kattintással, méghozzá a következő módon.

Van egy egyszerű kis kódunk, ami egy rövid üzenetet fog futása esetén kiírni a felhasználónak:

 

autom1.JPG

autom2.JPG

Ezt szépen elmentjük:

autom3.JPGEzután pedig létrehozunk Windows intézőben két kattintással egy pelda névre hallgató .vbs kiterjesztésű fájlt:

autom4.JPGEzt pedig a következő tartalommal kell feltöltenünk:

Set ObE = CreateObject("Excel.Application")
ObE.Application.Run "'C:\User\autom.xlsm'!Sheet1.Example"
ObE.DisplayAlerts = False
ObE.Application.Quit
Set ObE = Nothing

A sorok többségét már korábbi posztokban megismertük, de alapvetően az ObE változó egy új Excel "instance" elindulása lesz, amelyből futtatni fogjuk a Run metódussal a meghatározott helyen lévő fájlunk megfelelő sheetjén lévő megfelelő névre hallgató makrónkat. Ezután az Application.DisplayAlerts tulajdonságának kikapcsolásával mindenféle üzenetet kikapcsolunk, majd bezárjuk az Excelt és ki is ürítjük az ObE változót.

Ezután már csak dupla kattintás kell a .vbs kiterjesztésű fájlra és elindul egy Excel, lefut a definiált makró, majd bezárul az Excel. Egyszerű és hatásos, ráadásul rendkívüli módon elterjedt és ismert módszer.

Vágólapos elmélkedés Excelből: ürítsünk, na de hogyan?

A mai posztban egy vágólapot feszegető kérdést fogunk körbejárni, amelyben a kedves Olvasó arra keresi a választ, hogy hogyan lehetne elérni, hogy egyetlen beillesztés után Excel vágólapunk üres legyen ismét és a vágólap tartalma ne őrződjön meg tovább.

Alapesetben az Excel Clipboard funkciót nem lehet teljesen eliminálni egy egyszerű checkbox ki-be pipálgatásával, így a következőkben néhány lehetséges áthidaló megoldást fogok bemutatni. Azért itt tegyük tisztába, hogy van egy Office-vágólap (24 elemre) és egy Windows-vágólap is (egyetlen elemre) és érdekes, de CTRL+C lenyomásával az Office-vágólapra pakolunk, CTRL+V lenyomásával pedig a Windows-vágólapról illesztünk be - hacsak manuálisan nem lépünk közbe a Clipboard varázslónk segítségével.

1. Szóval első opcióként a Home ribbonfül Clipboard szekciójában válasszuk az apró kis nyilacskát a jobb sarokban, hogy az egyéb lehetőségeket is láthassuk:

clip1.JPGEzzel meg fog jelenni bal oldalt a Clipboard menüsáv, benne listázva az éppen a vágólapon lévő elemeink:

clip2.JPGÉrtelemszerűen tehát ha valamit vágólapra helyezünk, majd beillesztünk, utána a Clear All gomb használatával törölni tudjuk a régi elemeket. Nem egy hatékony megoldás, de megoldás, ha a sima Escape billentyűs menekülés már nem segít.

2. Használjunk egy roppant egyszerű VBA-s megoldást, amelynek első lépéseként mondjuk hozzunk létre egy gombot a munkalapunkon (vagy akár a Ribbonhoz is adhatunk ilyen gombot), amelyhez rendeljünk hozzá egy makrót.

clip4.JPGEz a makró nagyjából egy egyetlen soros kis kód lesz, egy Application.CutCopyMode = False tulajdonságállítással.

clip3.JPGA gomb nem igazán életszerű megoldás, hiszen valószínűleg ezt a Clipboard kiürítéses megoldást állandóan használni szeretnénk, de ennek érdekében bármilyen más VBA-kód végére odaszúrhatjuk ezt az egyetlen sort. Ezt egyébként főleg rögzített makrókban találhatjuk.

3. A harmadik megoldás is VBA, ráadásul elegánsabb, amelyet meghatározott időközönként, gombhoz hozzárendelve, Ribbonra helyezve is tudunk használni.

VBA-editorban először is deklaráljunk három funkciót a következőképp:

Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long
Private Declare Function CloseClipboard Lib "user32" () As Long

A Private azt fogja elmondani a compilernek, hogy ez a funkció a jelenlegi osztályban vagy modulban lesz csak definiálva, más osztályokból nem érhető majd el. A Declare értelemszerűen azt mondja meg a compilernek, hogy a funkció később (mondjuk egy külső osztályban) kerül majd definiálásra, míg a Function maga a funkció, ami visszaad egy értéket. Ezután jön a funkció neve, majd a Lib, ami megmondja, hogy az adott funkció hol van definiálva - a mi esetünkben ez a user32.dll. A többi rész pedig magáért beszél.

A funkcióban a (ByVal hwnd As Long) azt a célt segít elérni, hogy olyan funkciót használhassunk, ami közvetlenül nem támogatott a VBA-ban, "hwnd" a "handle of the window" nem egy pontos cím, de a Windowst fogja útbaigazítani, mert a hwnd Windows API funkciók meghívásánál megadandó argumentum. Például hwnd kell akkor is, ha azt akarjuk megnézni, hogy az aktuális ablakunk mérete milyen.

Innentől kezdve már csak futtatnunk kell a három funkciót, amikor csak akarjuk - automatikusan bizonyos lépések után, gombnyomásra stb.

Sub ClearClipboard()
OpenClipboard (0&)
EmptyClipboard
CloseClipboard
End Sub

clip5.JPGFontos tudni, hogy a hármas pontban taglalt megoldás semmilyen formában nem saját, rengetegen használják úton-útfélen évek óta és én is csak javasolni tudom a bevetését szükség esetén.