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

2017. november 18. - Office Guru

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

Sorbarendezés formulával - nem a legegyszerűbb módszer, de érdekes

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:

rank1.JPGLá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:

rank2.JPGLá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:

rank3.JPGA 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.

rank1_1.JPGA 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:

rank4.JPG=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.

rank5.JPG

Alfanumerikus randomizálás - avagy véletlen generálás betűkkel és egyéb karakterekkel

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.

Dinamikusan módosuló Pivot-tábla mezőlista

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:

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

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

03.JPGHa 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:

04.JPGBő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:

05.JPGVan 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:

06.JPGA 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:

07.JPG=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:

08.JPG

09.JPG

Játszadozás Data Validation Listekkel - egymáshoz kapcsolódás és statikus nyíl megjelenítése

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.

capture1_1.JPG

capture2_1.JPGA 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:

capture3_1.JPG

capture4_1.JPGUgyebá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"))

capture5_1.JPGEzzel 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:

capture6_1.JPGAz 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:

capture7_1.JPGAzt 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:

capture12.JPGEzutá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.

capture13.JPG

Összefűzés formátumokkal együtt - VBA nélkül

Nem kell hozzá felmérést elvégezni, hogy biztosak lehessünk benne, az Excel legtöbbet használt függvényei között az első helyen álló VLOOKUP mögött szorosan ott szerepel a CONCAT vagy CONCATENATE függvény is, amelyről már hosszasan értekeztem itt többször is a blogon, legutóbb például az ezen formuláknak tökéletes alternatívát kínáló TEXTJOIN kapcsán.

A mai írásban azt fogjuk feszegetni, hogy hogyan lehet különböző cellák tartalmát úgy összefűzni, hogy azok eredeti formátuma megmaradjon. Adott mondjuk a következő három cella, amelyeket szeretnénk összefűzni:

1_1.JPGNézzük meg, hogy mi történik, ha CONCAT/CONCATENATE függvénnyel megcsináljuk az összefűzést:

2_1.JPGCélcellánk alapformátumával egyáltalán nem foglalkoztunk előzetesen, így defaultként, hogy csak a szövegünk formátuma maradt meg, a számunk és a dátumunk esetén elveszítettük a formázást:

3_1.JPGAki már szembesült ilyen problémákkal, az tudja, hogy megoldásként a TEXT függvény segíthet nekünk első körben, ami az első paramétereként megadott értéket konvertáljá át szöveggé a második paramétereként megadott formátum szerint.

Ha ezt ráhúzzuk a jelenlegi példánkra, akkor ez azt jelenti, hogy a D oszlopunkban szereplő dátumot szövegformátumra fogjuk konvertálni:

 

4_1.JPG

5_1.JPGAhogy láthatjuk, ez tökéletesen bevált, így ennek mintájára fogjuk a számunk formátumát is megőrizni az utókornak, de itt előzetesen elmélkedjünk egy kicsit. Először jobb gombbal kattintsunk a számunkat tartalmazó cellán, válasszuk ki a Format Cells menüpontot, majd nézzünk körül a Custom formátumok között:

6_1.JPGUgyebár most az próbáljuk megtudni, hogyan tudjuk a TEXT függvény felhasználásával megőrizni számunk eredeti formátumát, tehát mit kell a TEXT második paramétereként megadnunk, hogy azt kapjuk vissza, amit szeretnénk. Ahogy látható a fenti képen is, én kapásból a #,##0.00 előre definált formátumra ugrottam rá, mert ez milyen formátumot is jelent?

Egyrészt lesz benne ezres elválasztó vessző (#,##), másrészt kettő tizedesjegyre fog kerekíteni (0.00), úgyhogy ez most tökéletes lesz nekünk. Adjuk be a fentebb említett szövegformátumos TEXT formulához hasonlóan most a számot tartalmazó cellánkhoz a CONCATENATE függvénybe ezt.

 

7_1.JPG

8_1.JPGÉs látható, hogy tökéletesen működik a dolog, most már nincs más hátra, mint a szövegformátumot és a számformátumot tartalmazó TEXT formulánkat egy CONCATba ágyazni:

9.JPGÉs kész is vagyunk.

De ezenkívül is van még megoldás, az egyikhez például a WORDöt hívjuk segítségül!

Jelöljük ki a három cellánkat, majd CTRL+C és CTRL+V alkalmazásával helyezzük át WORDbe táblaként:

10.JPGJelöljük ki, amit beillesztettünk, majd a fent megjelenő LAYOUT Ribbonfül alatt a Data szekcióból válasszuk a Convert to Text funkciót:

11.JPGA felugró kis ablakban a "Separate text with..." lehetőségek közül válasszuk ki az Other opciót és tegyünk bele egy szóközt:

12.JPGHa megnyomjuk az okét, a következőt fogjuk látni:

13.JPGÉs ha ezt az átalakított szöveget most visszapakoljuk egy Excel-cellába, akkor ezt fogjuk látni:

14.JPGDe azért ezek a megoldások alapvetően elég lassúak és manuálisak, bizonyos jellegű formázásokat nem is tudunk így kezelni (pl. színeket stb.), plusz sok cella és sok különféle formátum esetén nehézkes is a használatuk, úgyhogy olyankor már érdemes VBA-hoz fordulni. Ennek tükrében csak a tényleg elvetemült érdeklődők és Camera Tool fanatikusok olvassanak tovább, ugyanis a következőben egy olyan megoldást fogok bemutatni, ami beépített funkció segítségével tud színezést, boldolást stb. minden formázást egyesíteni több cella esetén.

Adott tehát a következő két cella, amelyek értékeit szeretnénk egy cellában megjeleníteni összefűzve, úgy, hogy a formázások is megmaradjanak:

15.JPGSima CONCAT alkalmazásával az eredmény összejön, csak a formázások vesznek el:

16.JPGDe ne adjuk fel, fogjuk az összefűzött cellát tartalmazó oszlopunkat, nézzük meg, hogy milyen széles és pontosan olyan szélességre állítsuk az összefűzendő cellákat tartalmazó oszlopokat is, hogy azok összege megegyezzen az összefűzést tartalmazó cella szélességével:

17.JPGA munkalapunkon valahol egy elrejtett részen két cellát tegyünk egyenlővé az összefűzendő celláinkkal, tehát az egyik cella az Autó cellára fog mutatni, a másik pedig a 43-as számra:

18.JPGJelöljük ki ezt a két új, elrejtett cellát, majd a jobb gombbal előhívható context menü Format Cells menüpontjában válasszuk a Border fület:

19.JPGItt kapcsoljuk ki a külső (Outline) és belső (Inside) vonalakat is.

Ezután pedig már csak a Camera Toolt kell bevetnünk (ugyebár ezt egyetlen módon hívhatjuk csak elő, ha előtte hozzáadtuk a Quick Access Toolbarhoz), méghozzá oly módon, hogy az eredeti CONCAT függvényt tartalmazó cella tartalmazza azt a "fényképet", ami a két, "rejtett" cellánkra mutat:

20.JPGTúl sok időt nem töltöttem ezzel most, úgyhogy kicsit nyomorultul fest szegényke, de remélhetőleg ebből is látható volt az, hogy a Camera Tool megint olyasmiben segíthet nekünk, amit nem is gondoltunk volna előzetesen.

Munkalapok összevonása, összemásolása - 1. rész: Beépített funkciók

Nagyon gyakran felmerülő igény az Excel használata során különböző munkalapok összemásolása egy munkalapra, akár csak az átláthatóság, akár konszolidált adatkezelés céljából - akárhogy is, az esetek többségében a sima CTRL+C és CTRL+V megoldáshoz fordulnak a felhasználók. Pedig vannak más megoldások is, ezek közül mutatok be most többet is. Adott tehát a következő kis tábla az első sheeten, és egy hasonló van a másodikon is, ezeket szeretnénk összesíteni:

merge1.JPGMár egyszerűen csak a Ribbon füleinek böngészése alapján is szemet szúrhat a Data fül alatt megbúvó Consolidate funkció, amely már a leírása alapján is valamilyen művelet alapján tömbösít, konszolidál adatokat több munkalapról:

merge2.JPGDe ha rákattintunk a funkcióra és felugrik a műveleti ablak, már látjuk is a gyenge pontját ennek az opciónak - sima összemásolást nem tudunk vele elvégezni, csak műveletek alapján tudunk konszolidálni, ahogy a funkció neve is utal rá:

merge4.JPGAhogy látható a fenti képen is, egy sima összegzést választottam a Function legördülő menüjéből, majd megadtam a konszolidálandó tartományokat, mindkét sheeten kizárólag a számokat tartalmazó oszlopokat, majd bepipáltam a Top row label checkboxot, hogy legyen fejléce az új táblámnak:

merge5.JPGLátható, összeadta a sorokban szereplő számokat, ahogy a funkciónál ki is választottuk az összegzést. Értelemszerűen persze ha az egész táblát adjuk be referenciaként mindkét sheetről, és beadjuk a Left Column checkbox pipáját is, akkor más lesz az eredmény:

merge51.JPG

merge52.JPGDe ez csak azért néz most ilyen jól ki, mert nincs két egyforma városnevünk, ami alapján összegezhetne a konszolidációs funkció. Szóval ha tényleg szeretnénk adatokat konszolidálni, akkor használható a Consolidate, ha szimplán csak összemásolást szeretnénk, akkor másfelé kell kutakodnunk. De ne gondoljunk kapásból VBA-kódra, az Excel még mindig rejt magában beépített lehetőségeket a fenti célra. Hozzunk létre egy új sheetet, mondjuk Összes néven:

merge6.JPGEzután ugyanezen a sheeten maradva a Data fül alatt lévő From Other Sources legördülő menüből válasszuk ki a From Microsoft Query opciót:

merge7.JPGMiután elindult a Microsoft Query, a felugró adatforrás ablakban válasszuk az Excel Files* menüpontot:

merge8.JPGEzután válasszuk ki azt a munkafüzetet, amelyben a két (vagy több) összemásolandó sheet található:

merge9.JPG

merge10.JPGAz ezután felugró Add Tables ablakot Close gombra kattintással zárjuk be:

merge11.JPGEzután válasszuk a fenti ikonsorról az SQL feliratú kis gombot, ami után látható válik egy SQL statement ablak:

merge12.JPGEzután jön maga a statement, ami a következő lesz:

SELECT *

FROM "C:\book1.xlsx"."Sheet1$"

Union

SELECT *

FROM "C:\book1.xlsx2"."Sheet2$"

merge13.JPGTehát válasszuk ki (SELECT) az első sheet minden adatát majd egyesítsük (Union) a második sheet összes adatával.

Ha erre most okét nyomunk, már látni is fogjuk az eredményünket Microsoft Queryben:

merge14.JPGMár nem maradt hátra más, mint a File menüben taláható Return Data to Microsoft Excel funkcióval visszaküldeni az eredményt az Összes névre hallgató sheetre:

merge15.JPGÉs íme:

merge16.JPGA következő posztban további összemásolási lehetőségeket fogunk még majd áttekinteni, remélhetőleg azonban a fenti is használható alternatívát jelent egyes Olvasók számára.

TEXTJOIN: a legújabb barátunk, amely új alapokra helyezi az összefűzés tudományát

A következő poszt alapötlete igazából azok számára lesz azonnal érthető, akik mondjuk SQL-lekérdezéseket is írnak vagy használnak, de a felvetett problémára adott ötletek nagy valószínűséggel olyan kérdésekben is segítséget nyújtanak, amelynek nem kötődnek az SQL-hez. Tegyük fel a következő kérdést: ha akarunk egy SQL-lekérdezést írni, amelyben szerepel egy IN operátor is, vajon mi a legegyszerűbb megoldás, hogy rengeteg értéket be tudjunk tenni az IN mögé a zárójelbe?

Adott tehát a következő példa, értékek felsorolása, amelyeket az IN mögé szeretnénk beerőltetni:

capture1.JPGAz első ötlet, ami beugrik a többségnek, egy sima összefűzés függvény nélkül, szimplán

="'"&A3"',"

használatával.

capture2.JPGHa ezt "lehúzzuk" (automatikus kitöltés), látjuk is szépen az eredményt, ezt már csak be kell másolnunk mondjuk az SQL Developerünkbe az IN mögé, úgy hogy az első és utolsó értékünk elé és mögé egy zárójelet is pakolunk:

capture3.JPGEgy másik megoldás lehet az Excel 2016-os verziójában bevezetett TEXTJOIN funkció, amely több tartományban, cellában szereplő értéket fűz össze elválasztók (delimiter) alapján. Az első paramétereként adjuk meg az elválasztót, a második kötelező paraméter azt határozza meg, hogy üres cellákat kihagyjunk-e vagy sem az összefűzésből, a harmadik kötelező paraméter pedig maga az összefűzendő szöveg első értéke vagy akár egy cellatartomány. Vannak még opcionális paraméterek, amelyek mindegyike az összefűzendő cellákra utal, tehát ha a harmadik paraméterként nem tartományt adunk meg, hanem egy cellát, akkor további paraméterekben írnunk kell tovább az összefűzendő értékeket. Azaz a mostani példán például

=TEXTJOIN(CHAR(10),TRUE,A:A)

azt fogja jelenteni, hogy az ENTER (erre utal a Char(10), mert a speciális elválasztókat, mint egy új sor vagy egy sortörés, csak karakterszáma alapján tudunk beadni) delimiterrel elválasztott cellákat fogja összefűzni, üres cellák kihagyásával az A oszlopban.

capture4.JPGÉs látható az eredmény is:

capture5.JPGÉs ezt pedig az elsőként felvázolt ötlettel kombinálva szépen elő tudjuk készíteni az IN mögé bemásolásra, valahogy így:

capture6.JPGÉs ezután már meg is van a kész eredményünk:

capture7.JPGPersze van még opcióként a beépített CONCAT vagy CONCATENATE függvény is, de mindkettőnél alapvetően az a fő probléma, hogy nagyon hosszú értéksor esetén nagyjából lehetetlen a használata, mert túl sok a manuális munka vele:

capture8.JPG

Egyébként a CONCAT és a CONCATENATE első ránézésre teljesen egyformának tűnik, de nem csak ránézésre egyformák, pontosan ugyanazt tudják, előbbi a funkció nevének lerövidítése érdekében került be "új funkcióként" az Excel 2016-os verziójába illetve nagy valószínűséggel azért, hogy a Google Sheets által is használt, ugyanilyen nevű funkcióval azonos néven fusson. A CONCATENATE pedig azért maradt benne, hogy megmaradjon a kompatibilitás az Excel korábbi verzióival is.

Mindenesetre azt pedig a fentiek ismeretében beláthatjuk elég gyorsan, hogy a TEXTJOIN elég jó barátunk, hiszen alig egy-két kattintással akár sok ezer értéket is össze tudunk fűzni pillanatok alatt.

Animált kézírás PowerPointban - dedósnak tűnhet, de sok bemutatóban lehet helye

Hétvégi lazításként, egy alapvetően elég pofás és roppant egyszerűen elkészíthető PowerPoint animációról lesz szó itt a blogon, ez pedig nem más, mint a más prezentációkészítő alkalmazásoknál is előszeretettel alkalmazott kézzel írás animációja. Hangsúlyozom, ha a téma engedi vagy éppen megpróbáljuk a lehető legjobban kitágítani a PowerPoint animációk lehetőségeit, akkor bátran ötleteljünk és vállalkozzunk!

Tehát adott egy diánk, rajta pedig egy szöveg, amit kézzel szeretnék animált formában majd kiírni a bemutatónk során:

hand1.JPGÉrtelemszerűen célszerű olyan betűtípust választani, ami hasonlít a kézírásra, mint például a Lucida Handwriting.

Ezután a Ribbonunk Animations füle alatt adjunk hozzá egy animációt a szöveghez (Add Animation), méghozzá Appear típusút:

hand2.JPGA jobb oldalt elérhető Animation Pane alatt láthatjuk is (ha nincs ott, akkor szintén az Animations ribbonfül alatt tudjuk bekapcsolni) a hozzáadott animációt. Kattintsunk jobb gombbal rá és válasszuk az Effect Options menüt:

hand3.JPGA megjelenő ablak első fülén az Animate Text legördülő menüt állítsuk By Letter típusra ("betűnként"), majd egy normális írási sebességet állítsunk be hozzá:

hand4.JPGA már előre megálmodott és előkészített képünk beszúrása a következő lépés, azaz az Insert ribbonfül Images szekciójának Pictures funkciójával szúrjuk be a diára azt a képet, ami egy tollat, egy ceruzát, ecsetet vagy egy ilyen eszközt fogó kezet ábrázol. Ha megvan, akkor az Animations ribbonfül alól erre is szúrjunk be egy animációt:

hand5.JPGEz pedig ne a felkínált opciókból kerüljön kiválasztásra, hanem kérjük meg a PowerPointot, hogy mutasson még nekünk mozgásos opciókat, a More Motion Paths menü alatt. Itt pedig a Zigzag a tökéletes választás számunkra:

hand6.JPGHa erre okét nyomtunk, akkor az Animations ribbonfül Animation szekciójában meg is jelentek opciók a cikkcakkozásra, úgyhogy mi most válasszuk a Custom Patch opciót:

hand7.JPGEzután nem kell mást tennünk, mint szépen nekilátni és a cikkcakkokkal nagyjából hasonló utat bejárni, mint amit akkor tennénk, ha tényleg kézzel írnánk. És ha megvagyunk, akkor az Animation Pane alatt megjelent második animációnkra kattintsunk jobb gombbal és állítsuk be úgy, hogy ugyanakkor induljon, mint az előbb már animált szövegünk:

hand8.JPGMiután alapesetben az animált tollunknak az animáció első lépéseként az első betű fölé kell mozognia, így célszerű az első, a szöveget betűnként megjelenítő animációra egy kis csúszást beállítani, tehát mondjuk 1.25 másodperccel később kezdődjön, mint a toll animációja:

hand9.JPGAzaz most valahogy így állunk:

hand10.JPGLátható, hogy megvan a tollunk és annak útvonala, plusz megvan a szövegünk betűnkénti animálása is és még utóbbira be is raktunk 1.25 másodpercnyi késlekedést, hogy a toll éppen időben odaérjen kezdésre. És ha ezt most elindítjuk, láthatjuk is az eredményünket:

hand11.JPG