Back in the business - Pivot-jótanácsok kezdésnek

2018. február 11. - Office Guru

Volt pár komolyabb projekt és változás, ami miatt sajnos nem tudtam annyit beletenni a blogba (konkrétan semmit), mint szerettem volna, nem tudtam levelekre sem válaszolni, de most úgy tűnik, hogy ismét folytatódhat az Office-bütykölgetés. A visszatérés alkalmából két olyan egyszerű kis Pivot-táblás tanáccsal kezdenék, ami az elmúlt üresjárati időszak során nekem időt takarított meg különféle feladatok során.

Az első megismeréséhez nézzük a következő adathalmazt:

pivot1.JPGCsináljunk belőle az Insert ribbonfül alatt, a Tables szekcióban található PivotTable funkcióval egy Pivot-táblát:

pivot2.JPGIde jutottunk:

pivot3.JPGAhogy látható, szűrőként használjuk a Régió oszlopban szereplő három értéket:

pivot4.JPGEnnél a háromnál talán még nem is ennyire látható a funkció hasznossága, de jóval több filter esetén mindenképpen hasznos annak a funkciónak az ismerete, amellyel szűrőfeltételként külön-külön sheetre szét tudjuk dobálni a Pivot-táblánkat. Ehhez nem kell mást tenni, mint a Pivot-táblán állva a megjelenő Analyze ribbonfül alatt kattintsunk a PivotTable szekcióban található Options menüre, majd azon belül a Show Report Filter Pages lehetőségre:

pivot5.JPGÉs íme, meg is van az eredmény:

pivot6.JPGNagyon fontos tudnivaló, hogy ilyenkor az adat nem változik, tehát az összes sheeten elérhető az összes régió adata!

A következő Pivot-tanács is egy egyszerű adattáblával kezdődik:

pivot7.JPGCsinálunk belőle egy teljesen normális Pivot-táblát:

pivot8.JPGIsmét igaz, hogy egy jóval komplexebb táblánál sokkal jobban látható lenne a bemutatásra kerülő funkció hasznossága, de a kérdés az, hogy hogyan adhatjuk meg a Pivot-táblán belül a a teljes darabszámra jutó sérült darabok százalékos arányát? A Pivot-táblán állva az Analyze ribbonfül alatt található Calculations szekcióban válasszuk ki a Fields, Items & Sets menüt, majd a Calculated Field lehetőséget:

pivot9.JPGEzután már csak az új mező (oszlop) nevét és a kalkuláció alapját kell megadnunk:

pivot10.JPGÉs meg is van a kalkulált oszlopunk, amelyre aztán további kalkulációkat építhetünk fel.

pivot11.JPG

Szövegdobozból fájlba, fájlból szövegdobozba - a korábban tanultak felhasználása egy konkrét problémán

Exceles TextBoxokról korábban már volt szó itt a blogon, akinek van kedve, nyugodtan szemezgessen:

TextBox témák

Mindenesetre a mai rövid, "gyorssegély" posztban ezekből szemezgetve készítek el egy apró kódsort (vagyis kettőt), amely segítségével Exceles TextBoxunk tartalmát fogjuk elmenteni egy fájlba, például egy .txt kiterjesztésű szövegfájlba illetve onnan vissza is fogunk olvasni szövegeket. 

Adott tehát a következő TextBox:

cel1.JPGNyugodtan pakolhatunk mellé egy gombot, amelynek megnyomása után kerül át a szövegünk a fájlunkba, de akár magára a TextBoxra való kattintással is indíthatunk egy ilyen eseményt:

cel2.JPGJelen példámban utóbbinál maradunk, azaz a TextBox1_Click eseményhez rendeljük hozzá VBA-editorban a következő aprócska kódot:

cel3.JPG
Látható, hogy elsőként a "szovegem" nevet viselő változónk felveszi az aktív munkalapunkon található, TextBox 1 nevet viselő textboxunk tartalmát, majd megnyitjuk a C meghajtónkon található x.txt nevű fájlt, amelyhez az Open utasítást fogjuk használni, a megfelelő szintaxis alapján, amelyben a For után következő Append azt jelzi majd, hogy egy már meglévő fájl szövegéhez akarunk hozzápakolni (ellenkező esetben Output jön a For után), az As után következő #1 pedig a paraméterként elvárt fájl sorszáma.

A Print utasítás tehát ebbe a megnyitott fájlba rakja bele a "szovegem" változó tartalmát, tehát a TextBoxunk szövegét, majd Close utasítással be is zárjuk. Értelemszerűen ez nem csak .txt kiterjesztésű fájl esetén működik, használhatjuk Word-dokumentumnál is.

Ha a másik irányba szeretnénk kommunikálni, az a fenti kód alapján egyébként könnyen összerakható, de kicsit megkeverjük tanulási célzattal - szóval a lényeg, hogy egy .txt kiterjesztésű fájlból akarunk szöveget a TextBoxunkba varázsolni. 

Elsőként hozzunk létre három változót:

Sub txttoTextBox()

Dim file As String, ff As Integer, szoveg As String

file = "C:\x.txt" 

szoveg = Space(FileLen(file))

ff = FreeFile

Mit is csinálunk? A "file" névre hallgató változónk fogja a fájlunkhoz tartalmazni az elérési útvonalat, a "szoveg" névre hallgató változó pedig annyi szóközt fog felvenni, ahány byte méretű a megadott fájlunk (ezt adja meg a FileLen utasítás), nagyjából előkészítve a terepet a "betöltésre", hiszen ennyi karaktert fogunk ide betolni. Az ff = FreeFile pedig nem mást fog megadni, mint amit az előbb #1-ként, fájl sorszámként hivatkoztunk meg a másik kódban, ez ugyanis megadja a a következő szabad fájl-sorszámot nekünk. 

A kód további részében három utasítást fogunk használni:

Open file For Binary As #ff

Get #ff, ,szoveg

Close #ff
ActiveSheet.TextBoxes("TextBox 1").Text = szovegEnd Sub

Az Open utasítás szintaxisát követve most a megadott fájlunkat bináris módban fogjuk megnyitni a paraméterként elvárt sorszámot az ff változóból kinyerve. A Get utasítás az egész kód lelke, ami a megadott sorszámú fájlból (ezt hozza az ff változó) olvassa be az adatokat a harmadik paraméterként megadott változóba (ez a szoveg változó). Vegyük észre, hogy a két vessző között nincs semmi, oda kerülne ugyanis az opcionális második paraméter, amellyel megadhatnánk, hogy honnan kezdje el a fájlból kiszedni az adatokat. 

A Close utasítást már ismerjük fentebbről, ezzel zárjuk be a fájlt, az utolsó sorunk pedig az aktív munkalapunk TextBox 1 névre hallgató textboxába tölti be "szoveg" változónk tartalmát. 

Karácsonyi Excel-poszt

Az év ezen időszakában (még akkor is ha az utolsó pillanatokig megy a munka) általában mindenki kienged egy kicsit, lazít, igyekszik töltődni a következő évre, úgyhogy én sem fogok komoly témákat feszegetni itt a blogon, a következő posztban szórakozni fogunk egy kicsit, méghozzá egy karácsonyfával. Nem is akármilyennel, az Excelben farigcsálunk egy kezdetlegeset.

Első lépésként szimplán színezzünk ki néhány cellát zölddel a fenyőt tűleveleinek, illetve néhányat barnával a törzsének:

kari1.JPGHa ez megvan, akkor itt az idő a díszítésre, amelyhez először is az összes zöld cellát töltsük ki egy RANDBETWEEN függvénnyel, amellyel generáljunk egy és három közötti számokat. Valahogy így:

kari2.JPG

Ezután a Home ribbonfülünk Conditional Formatting funkcióját felhasználva szúrjunk be egy új szabályt (New Rule), méghozzá egy "Format all cells based on their values" típusút, úgy, hogy a szabályunk formázási stílusa ikonkészlet (Icon Sets) legyen és azon belül is mondjuk a három színes köralakú ikon:

kari3.JPGÉrtelemszerűen a szabályokat jól kell beállítanunk, tehát valószínűleg célszerűbb nem százalékos formátumban küszködni, hanem a kalkuláció típusát Numberre állítani. Ha ezzel megvagyunk és érvényesítjük is a Conditional formattingot, akkor valami ilyesmit kapunk:

kari4.JPGAzt ugye tudjuk, hogy alapból az Excelünk automatikus kalkulációra van beállítva, amit egyrészt a File menü Options almenüjében tudnánk kikapcsolni vagy a Formulas ribbonfülünk Calculation Options menüjében:

kari5.JPGDe most nem az a célunk, hogy ezt kikapcsoljuk, ellenkezőleg, azt akarjuk, hogy a RANDBETWEEN egy bizonyos ideig folyamatosan újrakalkulálódjon, tehát az F9 nyomogatása vagy más cellák kiválasztása helyett, teljesen magától frissüljön a "fánk".

Ehhez pedig csak egy nagyon egyszerű kis kód kell VBA-ban, méghozzá egy ilyen:

Dim i As Long

   For i = 1 To 10

      Application.Calculate

     Application.Wait Now + #12:00:01 AM#

Next i

kari6_1.JPGAzaz van egy For ciklusunk, amiben nem más történik, minthogy újrakalkulálunk egyet (Application.Calculate) majd várunk egy másodpercet (Application.Wait) és utána ismételjük ezt többször, jelen esetben tízszer. Tehát itt szépen be tudjuk állítgatni, hogy hány másodpercig szeretnénk ezt futtatni, mert ha ezt lefuttatjuk, akkor szépen villogni is fog a fánk.

Ezzel a poszttal kívánok tehát minden kedves Olvasónak boldog karácsonyt és jó pihenést erre a pár napra!

Userformos keresés implementálása adatbázisra, több oszlopon egyszerre

Egyik kedves Olvasóm kérdése volt az ötletadója ennek a posztnak, ugyanis bár ő maga már jóval túllépett az alapokon, úgy gondoltam, egy ilyen jellegű megoldás kezdetleges kialakításának bemutatása mindenki számára hasznos lehet. Adott egy adatbázis, oszlopokkal, sorokkal, mindenféle adatokkal, mint a következő példa:

listbox1.JPGEbben a posztban készíteni fogunk egy olyan userformot, amelynek a segítségével a felhasználónk roppant egyszerűen, akár már csak az első betű beírása után szűrni tud az adatbázis bármelyik oszlopában. Első lépésként a Developer ribbonfülünk Controls funkciójának Insert menüjéből szúrjunk be egy Command Buttont, amelyet aztán a jobb gombbal történt kattintás után formázzunk is meg, ahogy szeretnénk:

listbox2.JPGEzután, még mindig Design Modeban, kattintsunk duplán erre a gombra, majd a megnyíló VBA-editorban a CommandButton1 Click eseményéhez rendeljünk hozzá egy UserForm2.Show utasítást (értelemszerűen a UserForm2 helyettesítendő az adott userform nevével), azaz a gombra való kattintással elindítjuk a keresést segítő userformot.

listbox3.JPGHa már úgyis a VBA-editorban vagyunk, szépen menjünk az Insert menüpontra, ahonnan szúrjunk egy Userformot. Ha beszúrtuk, akkor a Toolbox segítségével (ha ezt nem látnánk úgy, ahogy a lenti képen, akkor a View menüben találjuk meg az indító parancsát) pakoljunk a formra legalább egy Listboxot és egy Textboxot, plusz célszerű esetleg valamiféle dizájnelemet vagy szöveget is rátenni:

listbox4.JPGAlapvetően a listboxunkat fogjuk a keresés eredményének megjelenítésére használni, a textboxban pedig gépelni fog a felhasználónk, úgyhogy a listboxot mindenképpen konfigurálnunk egy kicsit. Kattintsunk rá jobb gombbal, majd a Properties alatt formázgassuk meg, de a ColumnCount paramétert mindenképpen állítsuk annyira, ahány oszlopot akarunk kezelni:

listbox5.JPGAztán nagyjából már csak a kód van hátra a Textboxunk mögött. Kattintsunk a mezőre jobb gombbal, majd View Code menüpont segítségével menjünk át a Textboxunk Change eseményéhez:

listbox6.JPGTehát ez a kód akkor fog lefutni, ha bármi változás történik a szövegdobozunkban (ergó elkezdünk gépelni). Első lépésként töröljük ki a Listboxunk aktuális tartalmát. Az "me" parancsról már írtam itt korábban, ez mindig arra a "szülő" objektumra hivatkozik, amelyikben a kód benne van, jelen esetben tehát az "me" a userformot jelenti - azaz a userformunk Listbox1-éről beszélünk. Ezután definiáljunk egy i nevű változót, amely nagyjából a vizsgálandó sorokat jelenti, ezért lesz a következő sorunk egy For .. To..., ugyanis amennyiben elkezdünk gépelni, akkor a később következő kódrészeket meg fogjuk nézni az A oszlop összes (A:A), nem üres (CountA("A:A")) során, a headert leszámítva (For i = 2).

listbox7.JPGDe egy újabb For ciklussal kell folytatnunk, hiszen soronként mind a három oszlopot meg kell vizsgálnunk, hiszen nem tudhatjuk, hogy a felhasználó az adatbázis három oszlopa közül melyikben keresne éppen. És hogy az így kialakult mátrix elemein milyen vizsgálatot is fogunk elvégezni?

Elsőként a Length névre keresztelt változóval vetessük fel a Textboxunkba írt szöveg karakterszámát. Ezután viszont jön a tényleges vizsgálat, a kód lelke, azaz ha (If) az adott munkalapunk előbb létrehozott mátrixának (minden sor minden oszlopát vizsgáljuk) aktuálisan vizsgált cellája (.Cells(i,f)) értékének (.Value) bal oldaláról levágunk annyi karaktert (Left), amennyit a felhasználó éppen beírt a textboxba és ez a levágott rész pontosan megegyezik a textboxba beírt szöveggel (és hát persze valami van a textboxban, ergó me.textbox1.text <> ""), akkor (Then) jön a képbe a Listbox AddItem metódusa, amelynek segítségével a listboxban megjelenített értékek listájához tudunk egy újabb értéket hozzáadni. Ezzel ugyanis szépen hozzáadjuk annak a sornak az első oszlopában szereplő értéket a listboxhoz, amely sorban a fenti vizsgálat egyezést talált bármelyik oszlopban.

listbox8.JPGA kód zárórészével pedig igazából már csak az előbb AddItemmel hozzáadott, első oszlopban szereplő érték mellé kell a második és harmadik oszlopban szereplő értékeket is beraknunk a Listboxba, amit pedig egy újabb For segítségével fogunk elérni, méghozzá úgy, hogy amennyiben a fenti vizsgálat talált egy egyezést, akkor még lesz egy, két lépésből álló ciklusunk (For m = 1 to 2), amelyben a listboxunk második és harmadik oszlopába betesszük az adott sor második és harmadik oszlopában szereplő értéket. Alapvetően ez így elég egyértelmű lenne, kivéve azt a mínusz egyest a listbox List tulajdonságában (ez utóbbi egyébként egy bizonyos elemet jelent a listboxban) szereplő listbox.listcount tulajdonság mögött, ami egyébként arra használatos, hogy megtudjuk egy listbox elemeinek számát. Szóval amit tudnunk kell erről az az, a ListCount mindig eggyel kezdi a számlálást az elemeknél, viszont a listbox nullánál kezdődik, ergó ez azt jelenti, hogy

.List(ListBox1.ListCount - 1, 1)

az első sorban és második oszlopban szereplő elemet fogja jelenteni, a -1,2 pedig a harmadikat.

listbox9.JPGÉs innentől kezdve már csak a For ciklusok kötelező Next elemeit kell betennünk és lezárhatjuk a szubrutint. Íme az eredmény:

listbox10.JPG

A hétvége sztárja a MÓDUSZ - egy kis fejtörővel

A most hétvégi posztban nem fogunk túlságosan komoly témákat feszegetni, inkább csak egy egyszerű fejtörőt próbálunk majd megoldani, amely megoldás során egy kevéssé ismert, ám rendkívül hasznos kis funkciót is meg fogunk ismerni. Adott ez a tábla:

example1.JPGEzzel fogunk most egy kicsit játszani. Az első kérdés alapvetően igen egyszerű lesz, mondjuk meg, hogy melyik a leggyakoribb érték a táblában, első körben eltekintve attól, hogy több ugyanolyan számú előfordulás is lehet. A megoldáshoz nem kell túlgondolkodnunk a dolgot, egyszerűen csak használjuk az Excel korábbi verzióiban már ismert MODE formulát, ami az újabb Excelekben már MODE.MULT és MODE.SNGL funkcióként van jelen. Ez nem más egyébként, mint a módusz, ami egy sorozat leggyakrabban előforduló elemét jelenti.

A MODE.MULT és a MODE.SNGL is a móduszt fogja megadni nekünk, a különbség a két függvény között akkor jelenik meg, ha több módusz is van (több leggyakoribb érték), mert ekkor a MULT egy tömböt ad vissza az összes módusszal, az SNGL pedig a legalacsonyabb, legelső helyen álló móduszt adja vissza. Ha a MODE.MULT-ot nem tömbfüggvényként használjuk, akkor ugyanúgy működik, mint az SNGL.

Visszatérve példánk kérdéséhez, a megoldás tehát mondjuk:

example2.JPG

example3.JPGNézzük tovább a játszadozást, mondjuk hogyan adjuk meg pl. a második legnagyobb előfordulással bíró értéket? Ismételten eltekintve az egyezőségre való fókuszálástól, azt hagyjuk most meg magára a funkcióra a fentebb elírtak szerint.

Alapvetően itt már tömbfüggvényt fogunk használni, méghozzá azért, mert első körben egy IF segítségével a tömb összes, legnagyobb előfordulással bíró értékét üres cellára állítjuk, majd az így megmaradt tömb legnagyobb előfordulással bíró értéke lesz a második móduszunk. Valahogy így:

example4_1.JPGÉs figyeljünk, hogy tömbökről lévén szó, CTRL+SHIFT+ENTER segítségével tömbfüggvényt kell használni. Ha megtettük, már látjuk is az eredményt:

example5_1.JPGÉrtelemszerűen ez azért nem egy olyan elmélet, amit könnyű lenne megvalósítani, mondjuk az n-edik előfordulás felkutatására. A fentiekkel egyébként azt is tisztáztuk remélhetőleg, hogy egyezőség esetén milyen érték jönne vissza a MODE függvénytől, tehát ha a példatáblánkban az 54 és az 57 ugyanannyiszor fordulna elő, akkor az első előfordulással bíró szám lenne a visszakapott érték.

De mit tennénk akkor, ha mondjuk nem az elsőt vagy a másodikat akarjuk külön-külön összekaparni, hanem meg akarjuk nézni az első három leggyakoribb előfordulással bíró értéket?

Nagyjából hasonló irányban kell gondolkodnunk, mint a 2. módusz felkutatásánál, azaz amit tenni fogunk az egy hasonló tömbfüggvény lesz:

{=MODE.MULT(IF(COUNTIF(H$5:H5,B$2:E$7),"",B$2:E$7))}

example6_1.JPGMi is történik itt? A leggyakoribb előfordulás beazonosítása okán, a COUNTIF segítségével megnézzük, hogy a H5-ben szereplő, jelenleg üres cellánk értéke megtalálható-e a táblánkban, ha pedig esetleg igen, akkor az IF segítségével üresre állítjuk az ezeket az értékeket tartalmazó cellákat, majd a MODE.MULT megmondja a móduszt. Tekintve tehát, hogy a H5-ös üres cellával kezdünk, a H6-ban a móduszt kapjuk, de amikor majd ezt a tömbfüggvényt "lehúzzuk", automatikusan kitöltjük, akkor ugyebár a COUNTIF már a H6-ot fogja majd megnézni, ahol az 57-es szám szerepel, tehát az összes 57-es helyett üres cella lesz majd, így értelemszerűen a MODE.MULT által visszaadott érték a második leggyakoribb előfordulás lesz. És így tovább:

example7.JPGEz egy nagyon érdekes függvény és egy nagyon érdekes téma, szóval bármilyen észrevétel és ötlet szívesen látott! És hogy adjak egy kis fejtörőt nektek is: hogyan érnétek el azt, hogy a MODE.MULT mondjuk ne a default alapján határozza meg több leggyakoribb előfordulás esetén a visszaadott értéket, hanem mondjuk a legkisebb páros számot adja vissza a leggyakoribb előfordulások közül?

think.jpg

Cellaformátum meghatározása függvénnyel - aztán VBA-val

Rendhagyó módon a mai poszt nem egy konkrét probléma megoldásáról fog szólni, hanem ahogy a múltban már többször tettem, egy érdekes kérdést járok körbe, keveset használt funkciók bemutatásával. Ma ez a kérdéskör a cellák formátuma lesz, méghozzá abból az aspektusból, hogy hogyan tudjuk megállapítani függvény segítségével egy celláról, hogy annak mi a formátuma. Persze ez alapvetően a Ribbonunk Home fülén lévő Number szekcióban is látható, de elképzelhető olyan helyzet, ahol bizonyos formátumok esetén visszajelzést akarunk adni a felhasználónak, kollégának.

Adott tehát a következő tábla, különböző formátumban lévő cellákkal:

01_2.JPGTehát a cél függvénnyel megmondani, hogy milyen formátumban van az adott cella. Az első formula, ami beugrik, az a TYPE, amely a paramétereként megadott cella adattípusát fogja megadni öt kategória szerint (ezek egyébként látszanak is a függvény leírásában):

02_2.JPG

03_2.JPGLátható tehát, hogy alapvetően ugyan nyújt információt ez a függvény, de a Number és Text formátumok közötti különbséget leszámítva, nem jutottunk sokkal közelebb az elvárt eredményünkhöz.

Ennél sokkal hasznosabb és hatékonyabb a CELL függvény, amelynek első paramétereként azt adhatjuk meg, hogy milyen információt akarunk kinyerni a celláról (van itt lehetőségünk bizonyos színinformáció begyűjtésére vagy akár sor- és oszlopszám meghatározására stb.), és itt van egy olyan információtípus, hogy "Format", ami a második paramétereként megadott celláról fog szöveges kódot visszaadni, ami segít meghatározni a formátumát egy részletes kódmapping segítségével.

04_2.JPGA Help és a Microsoft hivatalos oldala is szépen megadja nekünk a kódlistát, például azt, hogy a G az a "General" formátumot jelképezi, a P2 a "Percentage" formátumot, az F2 a "0.00" típusú számformátumot és így tovább.

05_3.JPGKipróbálhatjuk még a CELL függvény "Type" paraméterét is, de ez igazából azért elég gyenge, három eredményt tud visszaadni: b, ha a cella üres, l, ha a cella szöveget tartalmaz és v, ha bármi mást. Szóval ez azért nem visz közelebb a megoldáshoz.

06_1.JPGDe ha csinálunk egy kis elemzést a beadott adatainkról, hogy végül sikerült-e mindent beazonosítanunk ezen formulák segítségével:

07_2.JPGAkkor azt láthatjuk, hogy az utolsó dátumunkat egyik formula visszaadott értéke sem segít beazonosítani, hiszen tudjuk, hogy nem szöveg (1 és v), a formátumra pedig G-t ad vissza és nem egy speciális kódot, így ott maradtunk meglőve.

Ekkor már célszerű VBA-hoz fordulni, méghozzá egy Custom funkció megírásával (amit már megtettünk korábban itt a blogon). Szóval menjünk át ALT+F11 segítségével a VBA editorba, ahol jobb gombos kattintás után Insert-Module lenyomásával hozzunk létre egy új modult, ahol pedig csináljuk meg a funkciónkat.

08_2.JPG

Mivel elég rövid ez a kód, nem bajlódtam külön végigmenni a sorokon, úgyhogy most nézzük meg, miről is van szó.

Első körben tehát létrehozzuk a TypeExtra névre hallgató függvényünket, amelynek egyetlen paramétere lesz, a cella, amelynek formátumát szeretnénk meghatározni. Application.Volatile segítségével beállítjuk, hogy custom funkciónk mindig újrakalkulálódjon, ha történik valami a munkalapunkon.

A kód másik része pedig a CASE utasítás, amelynek segítségével a SELECT CASE után megadott kifejezést validáljuk a SELECT...END SELECT között megadott esetekre ("Casekre"), azaz jelen esetünkben azt, hogy melyik igaz a SELECT...END SELECT között felsorolt esetekből.

Jelen példánkban most csak két vizsgálatot végzek, az ISDATE funkcióval megnézzük, hogy a beadott cella valid dátum-e vagy sem (az ISDATE True vagy False értéket ad vissza), az INSTR funkciót pedig a Time formátum meghatározására használjuk fel. Alapesetben az INSTR formula VBA-ban egy szövegen belül egy megadott szövegrész, karakter helyzetét adja vissza, az első paramétereként megadott karakteről kezdve a második paramétereként megadott szövegben, úgy hogy a harmadik paramétereként megadott szövegrészt-karaktert keresi. Jelen esetben ez a :, hiszen egy TIME formátum azt mindenképpen tartalmaz és ha talál legalább egy :-t, akkor a függvényünk a "Time" szót adja vissza (ez persze azért félrevezető is lehet bizonyos speciális esetekben).

És kész is vagyunk.

09_1.JPG

A kockára váltó smiley titka

cut0.JPGEz lenne tehát az olvasói felvetés, következzen egy lehetséges megoldás ennek kiküszöbölésére. Szóval alapvetően ha Outlookban beírunk egy kettőspontot és egy zárójelet, egy szóköz lenyomása után automatikusan átalakul mosolygós hangulatjellé - feltéve persze, ha a megfelelő beállításaink már eredetileg is megvoltak (például a Show Picture Placeholders és társai).

cut1.JPGEgy lehetséges megoldás az lehet, ha a Home ribbonfülünk Symbols szekciójában a Symbol menüre kattintunk:

cut2.JPGMajd a Symbols fül alatt a Wingdings font 74-es Unicode számú karakterét, a mosolygós arcocskát választjuk ki. Ezt így simán be is tudnánk szúrni, de az nem hosszútávú megoldás, ezért a mosolygós smileyn állva kattintsunk a lentebb látható AutoCorrect gombra:

cut3.JPGEzután pedig a felugró ablak Replace mezőjébe írjuk be a kettőspont-zárójel kombinációt, majd kattintsunk a Replace gombra, hogy az eredetileg már itt lévő átalakításunkat lecseréljük az újra:

cut4.JPGÉs innentől kezdve szépen működik is:

cut5.JPGElképzelhető, hogy nem ez a legjobb megoldás és ez nem is az eredeti kiváltó okot szünteti meg, de tüneti kezelésként mindenképpen segítség, amíg elmélkedünk egy másik lehetőségen.

Automatikus frissítés legördülő menüre könyvtárstruktúrából

A mostani posztban bemutatásra kerülő megoldás egyes részeiről valószínűleg volt már szó korábban is, de így egyben még nem mentünk végig a folyamaton. Az eredeti kérés arra irányult volna, hogy hogyan lehetne egy legördülő menüben bizonyos fájlokat listázni automatikusan, azaz egy user által megadott elérési úton található fájlok kerülnek egy legördülő menübe, amivel később aztán műveleteket lehet végrehajtani.

Valami ilyesmire kell gondolni, a Könyvtár mezőt a user tölti ki, a megoldásunk pedig frissíti a Fájlok listájában található legördülő menüt:

1_3.JPGA megoldás nagyjából úgy fog kinézni, hogy a felhasználó megad egy elérési útvonalat, majd a Developer ribbonfül Controls szekciójának Insert menüjéből beszúrt gombra kattint:

1a.JPGKattintsunk erre a gombra jobb egérgombbal majd Assign Macro utasítással rendeljünk hozzá egy makrót. Amint ez megvan, dobjuk össze a kis kódunkat:

2_3.JPGElsőként tehát ahogy látható is, létre fogunk hozni öt változót, ezek közül az első egy FileSystemObject lesz, ami rövid kódsorunk magját és legfontosabb elemét adja és objektumalapú lehetőséget ad számunkra, hogy hozzáférjünk a számítógépünk fájlrendszeréhez. A második változónkat a folderobjektumunknak hozzuk létre, a harmadik változónkat pedig a fájlrendszerünk fájlobjektumának. Értelemszerűen ezeknek ezért "folder" és "file" a típusuk. A "path" változó fogja eltárolni a user által megadott elérési utat, az i pedig majd a ciklusunknál segít nekünk.

És ahogy mindig, most folytassuk a kódunkat a változók feltöltésével, elsőként a "path" változó vegye fel a user cellájának értékét. Ezután hozzunk létre egy új fájlrendszerobjektumot, majd az ezen belül elérhető GetFolder metódus, a paramétereként beadott elérési útra visszaadott értékét töltsük be a root változóba.

3_3.JPGEzután jön a ciklusunk, amelyben a könyvtárunkban található összes fájlon végig fogunk menni (For Each file in root.Files), majd levágjuk az utolsó három karaktert a teljes elnevezésből (Right(file.Name, 3)) és ha ez kisbetűvel írva mondjuk "txt", akkor a B oszlopunk i-edik (ahogy halad a ciklus) sorában szereplő cella értéke a fájl neve lesz. Majd megyünk a következő fájlra és végül befejezzük a kódot.

4_3.JPGPersze ezzel még nincs vége, hiszen csak azt értük el, hogy mostantól frissítés esetén a B oszlopunk frissülni fog (ez persze lehet egy rejtett sheeten is, most csak a példa kedvéért van ennyire látható helyen). De a Data Validation Listünket is be kell jól állítanunk a Fájlok listája mezőnél, hogy mindig automatikusan felkapja a pluszként bejött sorokat.

5_3.JPGKattinsunk a cellára, majd a Data ribbonfülünk Data Tools szekciójából szúrjunk be egy Data Validation Listet. A listánk Source paraméteréhez a szimpla kijelölés helyett a következő formában írjuk be a forrásunkat:

=OFFSET($B$1,0,0,COUNTA(B:B))

Ezzel mit fogunk elérni? Mint ugyebár tudjuk már, az OFFSET egy olyan cella vagy tartomány hivatkozását/elérési útját adja vissza, ami a függvény paramétereként megadott sorszámra vagy oszlopszámra van a megadott cellától. Jelen példánknál maradva, a $B$1 cellától indulunk ki, majd COUNTA segítségével megnézzük, hogy hány darab nem üres cella van a B oszlopban és ez fogja megadni a tartományunkat a dropdown számára, tehát ez mindig automatikusan frissíti a legördülő menüt. És kész vagyunk.

6_3.JPG

Kérdezz-felelek sorok és oszlopok számáról

Egy Exceles olvasói kérdés megválaszolása kapcsán gondoltam, hogy csinálok egy kis kérdezz-felelek játékot, amelynek végeredménye a kérdés egy bizonyos fajta megoldása lesz - bizonyos fajta, mert biztos, hogy van jópár másik lehetséges megoldás is.

Szóval adott a következő két tábla:

1_2.JPGA játék első részében az "Azonosítók" névre hallgató táblára fogunk fókuszálni. Jöjjön tehát az első kérdés, amelyre válaszoljunk formulával - hányadik sorban van a munkalapon a C3-as cella egyes száma?

2_2.JPGAhogy látható, a megoldást a ROW függvény mondja meg, roppant egyszerű módon, a C3-as cellát megadva paraméterként.

Második kérdés: hányadik sorban van az "Azonosítók" táblán belül a C3-as cella, értelemszerűen a tábla fejléce nélkül?

3_2.JPGA válaszunkat még mindig a ROW adja vissza, azzal a módosítással, hogy a munkalapon belüli sorszámból kivonjuk a táblánk első sorában lévő cella sorszámát (jelen esetben ez most 3-3), majd mindig hozzáadunk egyet, hogy így kapjuk meg a relatív pozíciót (3-3+1 = 1, aztán 4-3+1=2 és így tovább).

Folytassuk tovább most azzal a kérdéssel, hogy hányadik oszlopban van a C3-as cella értéke?

4_2.JPGEgyértelműen a megoldást most a COLUMN függvény adja meg, amely a paramétereként beadott C3-as cella munkalapon belüli oszlopszámát adja vissza.

Na de a táblán belül hányadik oszlopban van a C3-as cella értéke?

5_2.JPGTeljesen ugyanúgy járunk el, mint a második kérdésünknél, annyi kivétellel, hogy most a COLUMN függvényt használjuk a relatív oszlopszám meghatározására.

Tehát ezzel most már meg tudjuk adni, hogy egy adott táblán belül mi a pontos, relatív sor- és oszlopkoordinátája az adott cellánknak. És miután ezt most már ismerjük, elég könnyen meg tudjuk mondani azt is, hogy egy ugyanekkora méretű táblában (jelen esetben ez most az Érték névre hallgató kis tábla), az adott azonosítóhoz milyen érték tartozik.

Ehhez az INDEX függvényt hívjuk segítségül, amely megmondja, hogy az első paramétereként megadott tartományon belül (ez majd a második, "Érték" nevű táblánk lesz), a második paramétereként megadott sorszám és harmadik paramétereként megadott oszlopszám metszetében milyen értékünk található. Ezzel pedig már érthető is, hogy mit fogunk csinálni:

6_2.JPG

7_3.JPG

8_2.JPG

Két Outlook egyperces péntek estére

A leggyakrabban használt Office-modulok közül kérdés nélkül az Outlook az, amelyikről a legkevesebbet lehet írni, egyszerűen csak azért, mert a rengeteg lehetőség ellenére a felhasználási mód roppant egyszerű, főleg ha nem használjuk extra dolgokra (pl. formok). Így nem meglepő módon én sem írok róla túl sokat a blogon, de hogy kicsit kivételt tegyünk a folyamatos Excel-posztok áradatában, két múltbeli Outlookos kérdés megoldását fogom bemutatni a továbbiakban.

Az első kérdés arra irányult, hogy míg az Outlook korábbi verzióiban (2007 és azelőtt) megosztott naptárban (Shared Calendar) lehetett Private foglalást, megbeszélést rögzíteni, addig később (2010, 2013, 2016) ez a gomb már nem inaktívvá vált egy Shared Calendar esetén. Tehát egy új megbeszélést szeretnénk berögzíteni:

05_1.JPGDe hiába keressük a meghívó Appointment ribbonfülén a Tags szekcióban a Private kis lakat-ikonját, az már szépen ki van szürkítve:

01_1.JPGDe nem szabad feladnunk, a megoldás nincs messze, kattinsunk csak a File fülre, majd a középső menüből válasszuk ki a Properties ikont:

02_1.JPGA felugró ablak több szempontból is érdekes és hasznos az ismerete, hiszen többek között itt tudunk például levélküldést időzíteni vagy azt beállítani, hogy a levelünk elolvasásáról kapjunk mondjuk értesítést. De most minket az első, Settings szekció érdekel, ugyanis itt van egy Sensitivity névre hallgató menü, amely alapesetben Normal értéken áll.

Ezt állítsuk át Private státuszra:

03_1.JPGÉs már működik is a megosztott naptáron belüli Private foglalás:

04_1.JPGA másik kérdés, ami a mai posztban terítékre kerül, a rendszeres (recurring) Out of Office üzenet beállítása, ami alapesetben nem lehetséges beépített funkció segítségével, tehát minden alkalommal magunknak kell be- és kikapcsolni az értesítő üzenetet távollétünkről. Ennek áthidalására használják elég sokan azt a megoldást, hogy ütemeznek egy megbeszélést:

05_2.JPGMajd a meghívó Recurrence menüjére kattintva be kell állítanunk, hogy mikor is nem vagyunk elérhetőek visszatérő jelleggel:

07_1.JPGMajd a Recurrence menü mellett megbúvó Show As legördülő menüből kell kiválasztanunk az Out of Office státuszt:08_1.JPG