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

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

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.

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:

É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

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:

Ezt szépen elmentjük:

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

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

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:

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

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

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

A 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

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

A kedves Olvasó egy olyan megoldást szeretett volna, amelyben valaminek az árát két tényező alapján határozná meg, az egyik tényező oszlopban, a másik sorban található. Valahogy így:

Ahogy látható, a megoldás egy egyszerű VLOOKUP lesz, amelyben:

=VLOOKUP(oszlopban keresett érték, a teljes táblázat, MATCH(sorban keresett érték, sor teljes tartománya, 0), FALSE)

Azaz nagyjából a VLOOKUP "hányadik oszlop" paramétere egy beágyazott MATCH függvény eredménye lesz, amely MATCH függvény a sorban fogja megkeresni a második tényezőt és így adja vissza nekünk a megfelelő oszlopszámot. De erről korábban már volt szó - ezért a rövid válasz.

Bár már az Excel egy igen előrehaladott verziójánál járunk, mégis minden egyes nap találkozunk és találkozhatunk olyan felhasználói igényekkel, amelyeket beépített funkciókkal képtelen vagyunk elérni - legyen szó akár faék egyszerűségű felvetésekről. Ilyen például az az igény, hogy egy Excel-fájlban azoknál a celláknál, ahol az eredményt formulák adják meg, ne legyen látható a formula és még esetleg az adott cella védett is legyen, tehát ne lehessen törölni a formulát. Agyonképletezett dashboardok megőrzése az utókornak vagy éppen egy tesztkérdéseket tartalmazó munkalap lehet az oka egy ilyen igénynek, amelyre nem is teljesen igaz az a válasz, hogy nincs ilyen standard megoldás.

Ahogy ezen a példán is látható, van egy egyszerű szummánk az egyik oszlopban, ami két másik cella tartalmát összesíti:

Célunk, hogy a felhasználó ne láthassa, milyen képlet van az adott cella mögött és esetleg ne is tudja azt módosítani. Első lépésként jobb gombbal a formulát tartalmazó cellára állva hívjuk elő a Context menüt, majd válasszuk a Format Cells menüpontot:

Itt pedig a Protection fül alatt kell bepipálnunk a Hidden checkboxot (és esetleg a Locked checkboxot) és már majdnem ott is vagyunk. Ami még hiányzik az az, hogy a Review ribbonfül alatt található Changes szekcióból a Protect Sheet menüt válasszuk, majd védjük le a munkalapot.

Így innentől kezdve, ha ráállunk erre a cellára, akkor többé már nem lesz látható a formula:

Amiért ezt a megoldást sokan nem szeretik az az, hogy hiába pipálunk be a levédésnél minden lehetőséget, hogy felhasználóink az összes felkínált tevékenységet meg tudják tenni, mégis van egy-két standard funkció, ami már így sem érhető el, ráadásul egy levédett munkalap mindig veszélyes, hiszen a jelszó nem minden user számára ismert (na, nem mintha ez sok VBA-guru számára a legcsekélyebb problémát is jelentené).

Erre is elég sokféle VBA-s megoldás van, amelyekben egy közös vonás mindig van, a kódok azt figyelik, hogy egy adott tartomány vagy kijelölés cellájában-celláiban van-e formula és ha igen, azonnal lockolja a cellát és elrejti a formulát, majd levédi a munkalapot - ha nincs formula a cellában, akkor értelemszerűen nem történik semmi.

A mai posztban készítünk egy roppant egyszerű kis verziót ebből a kódból, ami ugyan nem fedi le az összes hibalehetőséget, nem fog tartományokat kezelni, de remélhetőleg kiindulópontnak jó lesz azon Olvasó számára, aki pont ilyesmire keresi a választ.

Szépen lépjünk be ALT+F11 lenyomásával a VBA-editorba, majd kezdjünk is neki a kódunknak, aminél fontos, hogy az adott munkafüzet SheetSelectionChange eseményéhez rendeljük hozzá. Azaz ha változik a kijelölésünk, azaz bárhova átkattintunk a sheeten, azonnal fut a kód.

Alapesetben indítsunk azzal, hogy mindig feloldjuk a munkalap védettségét az általunk meghatározott jelszóval, majd a kijelölt cella Locked és Hidden tulajdonságát kapcsoljuk nulla fokozatba, azaz mintha a pipa nem lenne ott ebben a két checkboxban. Ezt úgy érjük el, hogy felhasználjuk az adott kijelölés (Selection) Locked és FormulaHidden tulajdonságát, amelyeket Falsera kell állítanunk.

Private Sub Workbook_SheetSelectionChange(ByVal sheet As Object, ByVal Target As Range)
sheet.Unprotect Password:="officeguru"
With Selection
.Locked = False
.FormulaHidden = False

Vegyük észre, hogy a With...End With utasítást használtuk, ami ugyebár végrehajtja azokat az egy bizonyos objektumra, struktúrára vonatkozó utasításokat, amelyek a With és az End With között helyezkednek el.

A folytatás pedig a kód lelke, azaz megnézzük, hogy a kijelölés változásával az újonnan kiválasztott cella (Target) HasFormula tulajdonsága igaz vagy hamis, azaz tartalmaz-e formulákat vagy sem.

Azaz megfogalmazva nagyjából azt fogjuk tenni, hogy ha az új kijelölésünk tartalmaz formulát (If Target.HasFormula), akkor (Then) az új kijelölés (With Target) locked és hidden tulajdonságát (.Locked és .FormulaHidden) állítsa igazra, majd lezárjuk a With utasítást.

Private Sub Workbook_SheetSelectionChange(ByVal sheet As Object, ByVal Target As Range)
sheet.Unprotect Password:="officeguru"
With Selection
.Locked = False
.FormulaHidden = False
End With
If Target.HasFormula Then
With Target
.Locked = True
.FormulaHidden = True
End With

Ami még hátra van az az, hogy ilyen esetekben (amikor az új kijelölés tartalmaz formulát) a munkalapot védjük is le a jelszavunkkal, illetve a UserInterFaceOnly argument igazra állításával engedélyezzük azt, hogy a jelszóval védett munkalapon továbbra is futtathassunk VBA-kódot.

Private Sub Workbook_SheetSelectionChange(ByVal sheet As Object, ByVal Target As Range)
sheet.Unprotect Password:="officeguru"
With Selection
.Locked = False
.FormulaHidden = False
End With
If Target.HasFormula Then
With Target
.Locked = True
.FormulaHidden = True
End With
sheet.Protect Password:="officeguru", UserInterFaceOnly:=True
End If
End Sub

És ennyi a történet. Nem a legszebb megoldás és egyértelműen nem gyorsítja a fájlunkban a munkát, de ha el akarjuk kerülni a teljes levédést és inkább ki-be kapcsolgatnánk, akkor errefelé keresgéljünk.

Manapság a prezentációk készítése során a legnagyobb kihívást nem a tartalom, hanem inkább annak kreatív, újdonság erejével ható megjelenítése jelenti, így nem csoda, ha tömegével találhatunk az interneten pénzért vagy ingyen diákat, templateket kínáló oldalakat, ahonnan aztán könnyebben lehet a vizuális megjelenítést csiszolgatni prezinkben. Egy-egy szuper kis megoldás láttán az ember el is gondolkodik, hogy vajon mi a megvalósítás háttere, mennyire munkás egy-egy designos diát összedobni - nos, a mai posztban egy egyszerűbb kis trükköt nézünk meg együtt.

Üres diánkat kezdjük egy, az Insert ribbonfül Illustrations szekciójának Shapes menüjéből beszúrt kis kockával:

Ebből most csináljunk például négyet, de lehet akár öt, nyolc vagy kettő is:

Majd mindegyik kis kockát másoljuk le, hozzuk létre még egyszer, majd az újonnan létrehozott kockákra kattintva (értelemszerűen egyszerre csak eggyel kezdve), a megjelenő Format ribbonfül alatt az Edit Shape menüből válasszuk az Edit Points funkciót:

Szépen addig módosítgassuk a formáját a kis kockának, míg nagyjából el nem érünk valami ilyesmit az első kocka felett elhelyezkedő másik kockával:

Tehát van egy alsó sorunk, négy kockával, efelett pedig szintén négy kocka, amelyikből a bal szélsőt és a jobb szélsőt szépen megformázzuk az Edit Shape segítségével, hogy valami ilyesmi legyen az eredmény:

Ezután kattintsunk a felső sor első kockájára, majd a megjelenő Format ribbonfül alatt a Shape Fill menüből lépjünk a More Fill Colors almenüre:

Itt válasszunk az eredeti színnél valamivel sötétebb színt:

És ezt ismételjük el mind a négy kockapárunknál, hogy valami ilyesmi eredményt érjünk el:

Ezután az Insert ribbonfül Shapes menüjéből szúrjunk be egy egy lekerekített téglalapot:

Valahogy így:

Elsőként állítsuk át a kitöltő színét olyanra, mint az alsó sor bal szélső kockájának színe, majd a jobb gombbal előhívható Context menüből válasszuk a Send to Back funkciót és küldjük "háttérbe" ezt az alakzatot:

És ezt tegyük meg négyszer:

Ha ez megvan, akkor kicsit a méreteket hozzuk összhangba, hogy nagyjából itt tartsunk kinézetben:

Ezután a két szélső, nagyjából félbevágott trapéznak tűnő alakzatot ismét az Edit Points funkcióval szerkesszük meg úgy, hogy a felső széleik pontosan a felettük elhelyezkedő, lekerekített téglalap széleivel vágjanak egybe:

Ezután a lekerekített téglalapok kitöltő színét állítsuk át olyanra, mint a legalsó sorban hozzájuk tartozó kocka színe:

Ezután már csak csiszolgatni kell a formákat, elsőként az Insert menüből szúrjunk be egy TextBoxot, ami majd az értékeinket fogja tartalmazni:

Tehetünk rá ikonokat, szöveget, bármit, amit csak akarunk:

És ezután finomítgassuk, ahogy nekünk tetszik:

A mai poszt nem lesz túl hosszú, hiszen egyrészt maga a kérdés is eléggé rövid (bár életszerű), másrészt a megoldás is mindössze néhány kattintás - ettől függetlenül remélhetőleg ez az írás segítség lesz néhány Olvasónak. Adott tehát az alábbi cella:

Milyen módon érjük el azt, hogy az egy cellában, egymás alatt sortöréssel felsorolt értékek külön cellákban jelenjenek meg egymás alatt?

Első lépésként menjünk a Ribbonünk data füle alatt található Data Tools szekcióba, ahonnan hívjuk meg a Text to Columns funkciót:

Az első ablakban válasszuk a Delimited opciót:

És a második lépést meghatározó ablakban jön az egész művelet kulcsa, az elválasztók (Delimiters) típusai közül válasszuk az Other típust, majd az üres mezőbe kattintás után nyomjunk egy CTRL+J-t. Miért pont CTRL+J-t? Mert az ASCII karaktercsomagban a sortörés a tízes karakter, aminek az ASCII control kódja a CTRL+J, tehát nagyjából azt adtuk be a Text to Columns funkciónak, hogy a sortöréseknél vágja szét a cella tartalmát:

És ezután már szimplán csak végigkattintjuk a hátralévő lépéseket és már látjuk is az eredményt:

Ezután már csak egyetlen lépés van hátra, méghozzá az oszlopba rendezés. Ehhez először CTRL+C-vel másoljuk az egymás mellett elhelyezkedő cellákat a vágólapra, majd jobb egérgomb lenyomása után lépjünk a Paste Special menübe:

Itt már csak egy Transpose flaget kell betennünk és mehet is az OK:

És íme, itt az eredmény, amit kerestünk:

Korábbi posztokban már részlegesen érintettük ezt a témát, de most olvasói kérdésre válaszként következzen annak bemutatása, hogy például egy Excelben készített űrlap vagy adatlap TextBoxként beszúrt mezőiből hogyan tudunk egyszerűen adatot kinyerni, hogy aztán később ezekkel az értékekkel műveleteket hajthassunk végre. Értelemszerűen az Excel Developer ribbonfülének Controls szekciójából beszúrható TextBoxról van szó:

Íme egy roppant egyszerű kis űrlap, ami nagyjából bármilyen feladatnál képezheti a kis dashboardunk, munkalapunk részét:

Az elvárás pedig az, hogy valahol a sheeten vagy akár egy másik sheeten elhelyezett kis táblázat megfelelően kitöltődjön, amennyiben a megfelelő mezőket kitöltötte a felhasználó:

A legegyszerűbb megoldás, amit bevetnék egy ilyen esetben, valószínűleg a TextBox tulajdonságai között fellelhető LinkedCell tulajdonság lenne, amelynek megadásakor automatikusan töltődne a kapcsolt cella:

Na de ha valamilyen okból makrót szeretnék (például mert különféle ciklusokat szeretnénk használni, hogy az egyszer beírt adatok akár tárolódjanak egy sheeten), akkor jön jól az az alapként használható kód, amelyet az ALT+F11 lenyomásával előhívható VBA-editorba pötyögünk be. Elsőként egy OLEObject típusú változó deklarálásával kezdünk, amely ugyebár egy Active-X controlt/ beágyazott OLE-objektumot tud felvenni egy adott munkalapon (OLE mint Object Linking and Embedding).

A változó deklarálása után egyetlen ciklusra van csak szükségünk, ami egyszerű konyhanyelven nem fog mást tenni, mint sorban megnézi (For Each Box) a Sheet1 munkalap (In Worksheets ("Sheet1") összes OLEObjektumát (.OleObjects), majd ha talál köztük olyat, amelynek a progID-ja (If Box.progID =) a TextBoxunk progID-ja, akkor (Then) az adott TextBox bal legfelső cellájától számítva hét oszloppal arrébb található (Box.TopLeftCell.Offset(0,7)) cellát töltse ki az adott TextBox értékével (Box.Object.Value).

Vegyük észre az eddig még nem használt progID tulajdonságot, amely egy adott OLEObject elméleti/programbeli azonosítóját adja vissza. Ugyanígy egy adott OLEObject TopLeftCell tulajdonsága az adott objektum bal, legfelső celláját adja vissza.

Több kérdést kaptam már a múltban arra vonatkozóan, hogyan lehet egy Clustered/Stacked Column vagy éppen Bar diagram esetén az alapvetően téglalap-formájú oszlopokat lekerekíteni. A következő posztban két nagyon egyszerű módszert mutatok be ennek az eredménynek az elérésére.

Ez a kiinduló helyzet, a Ribbon Insert fülének Charts szekciójából szúrjunk be egy tetszőleges Column (vagy éppen Bar) típusú Chartot:

Az első módszer eléggé triviálisan adja magát, szintén az Insert ribbonfülre kattintunk, itt az Illustrations szekcióból a Shapes almenüt válasszuk ki:

Innen pedig szúrjunk be egy tetszőleges formát, nem muszáj ragaszkodnunk a kerekhez:

Ezután szimplán kijelöljük a beszúrt formát, nyomunk egy CTRL+C-t, majd az összes oszlopot kijelölve (tehát a Data Seriest) CTRL+V lenyomásával egyszerűen beillesztjük:

És ez ilyen simán működik más formákra is:

Ez pedig az, amit kerestünk:

A formákkal úgy játszhatunk, ahogy csak akarunk, tehát ha a fenti például túlságosan lekerekített, akkor változtassunk az eredeti formán és illesszük be újra.

A másik módszer kizárólag chart-toolokkal dolgozik, tehát eléggé át kell dolgoznunk a diagramunk struktúráját. Első lépésként a chartunk kijelölése után megjelenő Design ribbonfülre kell kattintanunk, ahol a Select Data menüt válasszuk:

Az itt felugró ablakban kattintsuk az Add gombra a Legend Entries (Series) szekciónál, majd a képen látható módon adjuk hozzá még egyszer ugyanazt az adattartományt egy új "Series"-ként:

Ezután még mindig a chartot kijelölve szintén a Design ribbonfül alól válasszuk ki a Change Chart Type menüpontot, ahol a másodikként beszúrt Seriest állítsuk egy alap-Scatter chartra:

Innentől kezdve pedig már adja magát a tennivaló, a diagram-oszlopaink tetején megjelenő kis pontokat válasszuk ki, majd a Format Data Series menüpontot válasszuk (jobb gombbal való kattintás után) és a jobb oldalt megjelenő menüben formázzuk meg a Marker pontokat:

1. Változtassuk meg a pontok méretét
2. A kitöltés legyen Solid
3. Ne legyenek körvonalak (No Border)
4. És végül a szín legyen ugyanolyan, mint az oszlopoké

És szintén itt, a Line beállításainál egyszerűen válasszuk ki, hogy ne legyen összekötő vonal (No Line).

Valahogy így: