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:

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

Né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:

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

É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))}

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

Ez 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?

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:

Tehá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):

Lá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.

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

Kipró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.

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

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

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.

Ez 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).

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

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

Ezutá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:

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

Elké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.

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:

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

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

Első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.

Ezutá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.

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

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

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:

A 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?

Ahogy 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?

A 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?

Egyé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?

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

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:

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

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

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

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

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

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

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

A közelmúlt egyik Facebookon érkezett kérdését továbbgondolva a mai posztban a sorbarendezés formulával kezelhető módszerét fogom körbejárni röviden, hiszen alapvetően az emberek többsége (és be kell vallanom, hogy én is) a legnagyobb vagy legkisebb értékek megkeresésére a Home ribbonfül Editing szekciójának Sort & Filter almenüjét használná - sorbarendezne, szűrne és így tovább. Esetenként még be lehet vetni egy PIVOT-táblát is, de a legritkább esetben nyúlunk formulákhoz, pont ezért fogok most két problémára két lehetséges választ adni, nem azért, mert mostantól enélkül nem lehet élni - egyszerűen azért, hogy új lehetőségekkel is megismerkedjünk.

Adott a következő tábla:

Láthatjuk, hogy három különböző megye járásait és a rájuk vonatkozó bevételadatokat tartalmazza az első három oszlop, a két üres oszlop fejléce pedig megmutatja, hogy pontosan mit is keresünk. Kezdésként a "Városok sorrendje" feliratú oszlopba szeretnénk beírni 1-től 19-ig a számokat a bevétel nagysága szerint.

Ez azért elég egyszerű történet, simán használjuk a beépített RANK (vagy RANK.EQ vagy RANK.AVG) függvényt:

Látható, hogy alapvetően két paramétert kell megadnunk, egyrészt magát az értéket az első paraméterben, amit be akarunk rangsorolni, másrészt második paraméterben azt a tartományt, amin belül rangsorolni akarunk. Van egy opcionális harmadik paraméter is, a csökkenő/növekvő sorrend megadására adva lehetőséget.

Látható is, hogy ez megfelelően működik is:

A másik kérdés egy kicsit azért komplexebb, hiszen a "Megyén belüli városok sorrendje" oszlopban a B oszlopban szereplő megyék szerint szeretnénk rangsorolni, azaz azt akarjuk megmondani, hogy mondjuk Caazapá megyében melyik járásban volt a legmagasabb a bevétel, melyikben a második legmagasabb és így tovább - mindezt értelemszerűen megyénként.

A feladathoz mindössze a COUNTIFS függvényre van szükségünk, amely a COUNTIF "többesszámú" verziója és azon cellák számát adja vissza nekünk, amelyek egy vagy több feltételnek megfelelnek. Használhatunk dátum, szám vagy akár szöveges feltételeket is, plusz logikai operátorokat, mint < vagy >. És egyébként ez már meg is mutatja, hogy ez valójában majd nem az adott értéket rangsorolja, mint a RANK, hanem azt írja majd ki, hogy hány darab nagyobb érték van az adott cellánk értékénél a megadott feltételek (jelen esetben Megye) alapján.

Figyeljünk rá, hogy mivel a legnagyobb értéknél 0 nagyobb érték van, a végeredményhez, majd +1-et mindig hozzá kell adnunk. Szóval ez lenne a megoldás:

=COUNTIFS($B$2:$B$20,B2,$D$2:$D$20,">"&D2)+1

Az első paraméter tehát a megyék oszlopa, ez ugyanis az első feltétel-tartományunk, a második paraméter a megyék oszlopának első értéke, hiszen mindig az aktuális sor megyéje lesz a feltétel, a harmadik paraméter a bevételek oszlopa, hiszen ez a második feltétel-tartományunk, a negyedik paraméter pedig egy logikai operátorral az aktuális sorunk bevétel értéke. Látható, hogy megnézzük, hogy az adott megyén belül az adott bevételértékhez képest hány darab nagyobb érték van még - és hogy pontos értékünk legyen, egyet mindenképpen adjunk hozzá a végeredményhez.

Azt ugyebár a legtöbb Excel-felhasználó tudja, hogyan generálhat véleletlenszerűen számokat, többek között például a RANDBETWEEN vagy éppen a RAND függvények segítségével - előbbinél ugyebár a tartományt tudjuk megadni, utóbbinál pedig 0 és 1 között fogunk számot generálni, amit aztán megszorozhatunk mondjuk százzal, kerekíthetjük egészre és így tovább. Na de mi van akkor, ha alfanumerikus értéket szeretnénk véletlenszerűen generálni? Ez tipikusan egy olyan példa, hogy ha a formulás megoldást ismerjük, akkor nagyjából ismerjük a VBA-s megoldás logikáját is, hiszen teljesen ugyanúgy gondolkodunk mindkét esetben.

Elsőként lássuk a függvényes megoldást! Mielőtt ugyebár meg akarjuk alkotni a kis függvényünket, tudnunk kell, hogy mit is szeretnénk - kisbetűt, nagybetűt, speciális karaktert, számokat stb.. Tegyük fel mostani példánknál, hogy számokat és kis- valamint nagybetűket szeretnénk majd mondjuk 8 karakter hosszúságban generálni.

Ez azt jelenti számunkra, hogy három lehetőségünk van minden karakter esetében: kisbetű, nagybetű vagy éppen szám. Hogy melyik legyen, azt véletlenül kell eldöntenünk, tehát a függvényünk egyik eleme biztosan egy

RANDBETWEEN(1,3)

lesz. Ha ennek eredménye mondjuk egy, akkor legyen szám, ha kettő, akkor nagybetű, ha három, akkor kisbetű - melyik függvény tud az első paramétere alapján választani a további paraméterei között? A CHOOSE a mi barátunk, ami ugyebár az első paramétereként megadott index alapján választja ki, hogy a további paramétereinként megadott értékek/formulák közül melyiket adja vissza.

Még egy fontos dolgot érdemes előkeresnünk nagy hirtelen a függvény összepakolása előtt - ez pedig egy ASCII táblázat, hogy tudjuk mely ASCII értékek adnak vissza számot és melyek betűt. Azaz valószínűleg függvényünknek egy számjegyet kell visszaadnia és ezt a számjegyet egy CHAR függvény segítségével fogjuk értékké alakítani. Ennyi bevezető után ez lenne a megoldás egy karakterre:

=CHAR(CHOOSE(RANDBETWEEN(1,3),RANDBETWEEN(48,57),

RANDBETWEEN(65,90),RANDBETWEEN(97,122)))

A fentiek alapján azért elég egyértelmű, hogy mi is történik itt, a CHOOSE első paramétereként megadott RANDBETWEEN visszaad egy értéket 1 és 3 között, majd ezen érték alapján fogja visszaadni nekünk az adott számú paraméterben lévő RANDBETWEEN eredményét. Ezek a RANDBETWEEN függvények és számaik pedig az ASCII táblából fogják megadni a szükséges karaktert, legyen az kisbetű, nagybetű avagy szám (ugyebár az ASCII táblában 48 és 57 között vannak a számok, 65 és 90 között a nagybetűk, 97 és 122 között pedig a kisbetűk). Egy 8 karakterből álló érték esetén pedig a fenti formulát kell megismételnünk nyolcszor, tehát az látható, hogy bár megoldásnak megoldás, elég munkás is lehet, ha sok karakterről beszélünk.

Ebből a szempontból tehát mindenképpen jobb a VBA-s megoldás, ami egyébként hasonló logikával működik. Az egyszerűség kedvéért most csak számokkal és nagybetűkkel dolgozom, illetve egy változóba be is fogjuk pakolni a teljes, 8 karakterből álló értéket, de értelemszerűen még egy IF segítségével a kisbetűt is be tudjuk tenni könnyedén.

Első lépésként a szokott módon definiálunk két változót, egyet a végeredmény tárolására, egyet pedig a ciklushoz:

Sub randomizer()
Dim random As String
Dim i As Integer
Randomize

A Randomize funkció meghívása azért szükséges a kódunkban, mert ennek segítségével fogjuk biztosítani, hogy a későbbiekben használt Rnd funkció tényleg véletlen számokat generáljon és ne legyen ismétlődés. Innentől kezdve pedig jön a ciklus:

For i = 1 To 8
If Int((2 * Rnd) + 1) = 1 Then
random = random & Chr(Int(26 * Rnd + 65))
Else
random = random & Int(10 * Rnd)
End If
Next i

Elsőre tűnik csak komplexnek, pedig nem az, nagyjából ugyanaz van itt, mint a RANDBETWEEN és CHOOSE formuláknál, azaz elsőként csinál egy "RANDBETWEENT" VBA-ban is (If Int((2 *Rnd) + 1) = 1), azaz az Rnd funkció standard utasításával generálunk vagy egy egyest vagy egy kettest. Amennyiben egyes az eredmény, akkor a következő fog történni:

random = random & Chr(Int(26 * Rnd + 65))

Azaz a random változóban eddig szereplő értékhez hozzáad egy olyan karaktert, amelyet egy 65 és 91 között generált egész szám jelent az ASCII táblában. Ugyebár az Int azért kell, hogy egész számról legyen szó, a Chr pedig azért kell, hogy az ASCII táblából adja vissza az értékünket.

Az Rnd funkcióval számot egyébként a következőképp tudunk generálni véletlenszerűen:

Int ((Felső értékhatár - Alsó értékhatár + 1) * Rnd + Alsó értékhatár)

Szóval visszatérve a kódunkhoz, amennyiben az első feltétel nem teljesül, tehát kettest generáltunk, akkor az Else-re ugrunk:

random = random & Int(10 * Rnd)

Ezzel pedig a random változónkba fog bekerülni az eddigi értékek mellé egy szám 1 és 10 között - vegyük észre, hogy itt nincs Char utasítás, hiszen csak egy egész számra van szükségünk, nem pedig speciális karakterre, betűre.

Sub randomizer()
Dim random As String
Dim i As Integer
Randomize
For i = 1 To 8
If Int((2 * Rnd) + 1) = 1 Then
random = random & Chr(Int(26 * Rnd + 65))
Else
random = random & Int(10 * Rnd)
End If
Next i
ActiveSheet.Range("A1").Formula = random
End Sub

És ezzel egyébként kész is a kód, hiszen utolsó lépésként szimplán beírjuk az A1 cellába az eredményt.

Az elmúlt hetek során velem is előfordult az, ami egyik olvasómmal esett meg a napokban - adott volt egy masszív Excel-tábla és egy hozzá tartozó Pivot, majd az Excel-tábla módosítása után hiába nyomogatott a felhasználó a Refresh gombra, nem sikerült az újonnan hozzáadott adatokat egyszerűen belevarázsolni a Pivotba. Hogy miről is van szó? Nézzük a lenti kis táblázatot:

Szúrjunk be egy Pivot-táblát az Insert ribbonfül Tables szekciója alatt lévő PivotTable funkcióval:

Látjuk, szépen meg is jelent a Field Listben az összes oszlopunk fejléce:

Ha most például a Típus és a Város közé szúrnánk be egy új oszlopot, a Pivot szépen frissülne egy Refresh után, de tegyünk mondjuk egy plusz oszlopot a tábla végéhez:

Bőszen nyomogathatjuk a megjelent Analyze ribbonfül Data szekciójában a Refresh gombot, a Field Listben egyszerűen nem fogjuk megtalálni az újonnan hozzáadott fejlécünket:

Van jópár megoldás ennek orvoslására, pont a Refresh mellett van a Change Data Source opció is, készíthetünk táblát is, most azonban a nevesített tartományok megoldását hívjuk segítségül.

Ugyebár a Formulas ribbonfül Defined Names szekciójában van a Name Manager, nyissuk meg:

A New gombra való kattintással hozzunk létre egy új nevesített tartományt, ami legyen az eredeti táblánk kijelölve, de nem szimpla hivatkozással, hanem megturbózva egy OFFSET függvénnyel:

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

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 hány nem üres van az első sorban és ez adja meg a tartományunkat.

Ezután amikor létrehozzuk a Pivot-táblánkat vagy módosítjuk a forrást, használjuk az elnevezett tartományt, mert innentől kezdve automatikusan érzékelni fogja a sorok vagy oszlopok hozzáadását és automatikusan hozza is a Pivot-tábla Field Listjén:

A mai posztot egy bocsánatkéréssel kezdem, hiszen sajnos az elmúlt hetekben nem igazán jutott idő a blogra, de ennek persze azért volt olyan előnye is, hogy ebben a néhány napban azért sikerült pár érdekes problémával és megoldással találkoznom, úgyhogy megint bőven van miről írni. Ma két olvasói felvetés gyors megválaszolásával melegítünk be, az első az ezen a linken már hosszassabban kifejtett, egymáshoz kapcsolódó Data Validation Listekhez kapcsolódik - ugye ez az a megoldás, amikor egy listából kiválasztott értékünk meghatározza a következő lista értékeit és így tovább.

A probléma ezzel a megoldással az, hogy ha már kiválasztottunk értékeket minden listánkból és utána visszamegyünk az első legördülő menübe, akkor hiába választunk más értéket, a kapcsolódó listákban default értékként a korábban kiválasztott elemek szerepelnek:

Ugyebár Toyotát választottam az Autó legördülő menüben, így jó lenne, ha nem maradna a Típus legördülő menünél egy Audi típus. Ennek kiküszöbölésére az egyik megoldás, ha az Autó legördülő menünk Data Validation beállításait megbütyköljük, azaz a Source mezőhöz beírunk egy feltételt:

=IF(F4="",Autó,INDIRECT("XY"))

Ezzel azt érjük el, hogy ha az első kapcsolódó legördülő menü (jelen példánkban ez a típus, az F4 cella) cellája nem üres, akkor nem tudjuk kiválasztani az Autó legördülő menünket egyáltalán, tehát konkrétan elakad a történet. Ennek súlyos hátránya, hogy állandóan törölgetni kell a korábbi kiválasztásokat, amiről persze tájékoztatni kell a felhasználókat, ellenkező esetben felesleges kommunikációval tölthetjük az időnket. Mindenesetre egymásnak ellentmondó kiválasztások nem maradhatnak.

A másik megoldás egy apró makró használata a VBA-editorban:

Az adott munkalapunk változás eseményéhez fogunk egy kódot hozzárendelni, méghozzá úgy, hogy megnézzük, hogy a változás hányadik oszlopban történt (If Target.Column = 4) és ha ez az az oszlop a negyedik (jelen példánk Autó legördülő listájának cellája), akkor megnézi, hogy az adott oszlopban van-e Data Validation Listünk (If Target.Validation.Type = 3) és ha ez is teljesül, akkor az adott data validation listától kettővel jobbra található cellát kiüríti (Target.Offset(0,2).ClearContents).

A másik kérdés a Data Validation listák mellett megtalálható nyilacskát érinti, amely ugyebár csak akkor jelenik meg, ha a listát tartalmazó cellán állunk - vajon elérhetjük-e valahogy azt, hogy mindig látszódjon nyíl az ilyen cellák mellett? Nos, beépített funkcionalitással nem (de javaslatok jöhetnek), viszont egy egyszerű kis linkeléssel megoldható a dolog. Szóval adott ez a helyzet:

Azt szeretnénk, hogy bár nem állunk a Típus melletti legördülő menü cellájában, mégis lássuk azt, hogy ez egy Data Validation List. Én egyszerűen csak készítettem egy képernyőfelvételt a Snipping Toollal egy Data Validation List nyiláról, majd beszúrtam a mellette lévő cellába:

Ezután pedig a kis ikonra való jobb gombbal kattintás után kiválasztottam az Assign Macro utasítást a context menüből, és hozzárendeltem egy

Range("F4").Select

utasítást, azaz ha valaki a kis nyílra kattint, akkor azonnal megjelenik a "rendes" nyíl is, hiszen az F4-es, data validation listet tartalmazó cellára visz minket a kattintás. Gagyi és sok ilyen listánál már eléggé melós dolog, de ettől függetlenül egyszerű és működik.