Nagyon sokan, nagyon sokféleképpen használjuk az Excelt, de az biztos, hogy igen elterjedt a használata ott, ahol realizálódott bevételeket és előrejelzéseket kell készíteni, rengeteg különféle módszer létezik egy jó forecast elkészítésére táblázatkezelőnkben, én most egy apró trükköt akarok csak megmutatni, amely kicsit felgyorsíthatja a munkánkat, hiszen egy lépésben érhetjük el azt, amit amúgy kicsit más módon közelítenénk meg. Adott tehát mondjuk a lenti minta, amelyet tegyük fel március 31-én készítettünk az addig realizált bevételekről és ebbe a táblába már beleszerkesztettük a következő három hónapra tervezett számainkat is:

Készítsünk ebből egy teljesen egyszerű kis vonaldiagramot a lentieknek megfelelően (ez csak egy gyorsan összedobott minta, nyugodtan tuningolhatjuk, csinosítgathatjuk azok alapján is, amiket írtam már itt anno):

Ahogy látjuk, ebből nem derül ki az, hogy mely periódusra van már valós adatunk és mely periódusokra vonatkozóan rendelkezünk csak előrejelzéssel, amit persze gyorsan orvosolhatunk ha nem az egész tábla alapján készítjük el diagramunkat, hanem két adattartományból, de van azonban egy egyszerűbb megoldás is, amellyel ugyanazon a grafikonon belül tudjuk érzékeltetni a különbséget.

Első lépésként a Ribbonunk Insert füle alatt található Illustrations szekcióból a Shapes funkció segítségével szúrjunk be egy kockaformát a diagramunkra:

Jobb gombbal kattinsunk a téglalapunkra és Format Shapes menü alatt hajtsunk végre két módosítást rajta - egyrészt legyen fehér a kitöltés, másrészt tüntessük el a körvonalakat:

Ezt kaptuk:

Már csak egy lépés van hátra, mégpedig az, hogy az átláthatóságát ennek a fehér téglalapnak annyira megváltoztassuk, hogy a mögötte lévő tartalom látszódjon, de a fehér szín miatt a színek kicsit legyenek fakóbbak:

És ezzel kész is vagyunk, egyértelműen elkülöníthető a két adathalmazunk.

Ismét csak egy olvasói kérdés fogja a mai poszt témáját képezni, ugyanis nemrégiben kaptam egy olyan levelet, amelyben egy kedves Excel-felhasználó arra volt kíváncsi, hogy lehet bizonyos függvényeket minden sheetre alkalmazni, ergó például, hogy lehet megkapni a legkisebb bevételt produkáló város nevét, ha a városok és a boltok külön-külön sheeten találhatóak, mint mondjuk ezen a képen:

A nehézséget nem az okozza, hogy megtaláljuk, melyik a legkisebb értékünk az összes sheeten, hiszen ahhoz elég egy MIN függvény és a sheetek halmaza, ahol keresgélünk, a mi esetünkben valahogy így:

=MIN(Eger:Budapest!C4:C8)

Tehát az Eger sheettől kezdve egészen a Budapest sheetig megnézzük a C4:C8 tartományokat és az összesből fogjuk visszakapni a legkisebb értéket - ha a sheeteken nem ugyanakkora a tartományunk mérete, akkor értelemszerűen külön sheetekre lebontva kell megadni, hol vizsgálódjunk.

A probléma valódi nehézsége akkor jön, amikor nem csak az értékre, hanem az értékhez tartozó megnevezésre vagy a sheet nevére vagyunk kíváncsiak, mert akkor már összetettebb függvényre, tömbfüggvényre van szükségünk.

Első lépésként, csak az egyszerűsítés érdekében, a Ribbonunk Formulas fülén található Name Manager segítségével hozzunk létre egy nevesített tartományt, amely a Cover sheetünkre sorban begépelt munkalap-nevekből áll:

Első lépésként egy INDIRECT formulára lesz szükségünk, ami lehetővé teszi, hogy a konkrét sheetek beírása nélkül meg tudjuk hivatkozni a nekünk szükséges tartományokat. Egy egyszerű példán:

=SUM(A1:A2)

értelemszerűen összeadja a két cella tartalmát, viszont

=SUM(INDIRECT("A"&H10&":A"&H11)

a H10 cellából kiveszi, hogy mi legyen szummánk első száma, tehát A hány, illetve H11-ből, hogy mi legyen a második szám, tehát A hány.

A mi konkrét példánknál maradva:

=INDIRECT("'" & Munkalapjaim & "'!C4:C8")

ami nagyjából ennek a tartománynak felel meg:

{"'Eger'!C4:C8";"'Miskolc'!C4:C8";"'Székesfehérvár'!C4:C8";"'Budapest'!C4:C8"}

Az első lépés ezzel kipipálva, hiszen megvan sheetjeink és tartományaink "tartománya". Most nézzük meg, hogy a legkevesebb bevétel száma (amit a fentebb már említett MIN függvénnyel szerezhetünk meg, tehát hivatkozhatjuk is a már kinyert értéket, de be is ágyazhatjuk a függvényt magát) melyik sheeten található, ehhez pedig használjunk egy COUNTIF formulát:

=COUNTIF(INDIRECT("'" & Munkalapjaim & "'!C4:C8"),F9)

Tömbfüggvényként futtatva, ez visszaadja nekünk, hogy mely sheetünk található az F9 cellába korábban már kinyert legkevesebb bevétel száma. Hiszen tudjuk, például a

=COUNTIF(A2:A100,"Szevasz")

visszaadja azon cellák számát, amelyek Szevasz értéket tartalmaznak a megadott tartományban, ergó a fenti példánk COUNTIF függvénye, megadja, hogy mely sheeten hány cellában van F9 értékünk.

Innen pedig mire van szükségünk? Megnézni, hogy melyik sheeten van egyes eredményünk, amihez tökéletes segítség a MATCH függvény, hiszen ahogy már írtam róla, ez a formula visszaadja egy megadott érték helyét egy megadott tartományban.

Azaz

=MATCH(1,A10:A20,0)

megadja, hogy az A10:A20 tartományban hányadik helyen található az egyes érték, nullával garantáljuk a teljes egyezést.

Ha a mi példánkat folytatjuk, akkor a következőre lesz szükségünk:

=MATCH(1,COUNTIF(INDIRECT("'" & Munkalapjaim & "'!C4:C8"), F9),0)

Ez megnézi, hogy hol található egyes érték az előbb, a COUNTIF segítségével létrehozott {0;1;0;0} tömbünkben és pontos egyezés esetén visszaadja annak helyét.

És már csak egyetlen egy dologra van szükségünk, hogy megkapjuk az eredményünket, ez pedig nem más, mint egy INDEX, amivel ki kell íratnunk a Munkalapjaim nevesített tartományból annak a városnak/sheetnévnek a nevét, amelyre az egyes érték, az előbb kinyert egyes érték vonatkozik, azaz:

=INDEX(Munkalapjaim, MATCH(1,COUNTIF(INDIRECT("'" & Munkalapjaim & "'!C4:C8"), F9), 0))

CTRL+SHIFT+ENTER-rel kell végrehajtanunk, hiszen tömbökről beszélünk, majd látni is fogjuk az eredményt, azaz melyik sheetünkön van a legkisebb érték.

Hiszen mit tesz az INDEX függvény? Egy megadott tartományban a sorszám és az oszlopszám alapján visszaadja az ott szereplő értéket, tehát

=INDEX(A2:B100,2,1)

Az A2:B100 tartomány második sorának első oszlopában szereplő értéket adja eredményül.

De a lényeg, hogy mostani feladványunkat megoldottuk, bőven felturbózhatjuk még ezt a tömbfüggvényt azzal, hogy akár kattintható linkké alakítjuk az eredményünket, de teljesen ugyanezzel a logikával a VLOOKUP is működővé varázsolható, azaz meg tudjuk azt is mondani, hogy mely város mely boltja termeli a legkisebb bevételt. De ez legyen inkább egy házi feladat mindenkinek, ha úgy gondoljátok, érdemes megosztani azt a megoldást is, jelezzétek!

Legutóbbi posztomban néhány nappal ezelőtt két Excel 2013-as "jóbarátot" mutattam be, de igazságtalan lenne, ha más Office-modulok kapcsán nem mutatnék be egy-két ilyen hasznos újdonságot (amelyek azért általánosságban ellensúlyozzák azokat a nehézségeket, amelyeket az új verzió bizonyos változásai okoznak), kezdve is rögtön a második leginkább ismert és használt modullal, a Word 2013-mal.

Anno szinte hitetlenkedve bámultam a monitorra, amikor ráeszméltem, hogy a Word 2013 képes pdf kiterjesztésű fájlokat nem csak megnyitni, de még szerkeszteni is, azaz nincs már szükségünk más szoftverre, ha egy pdf-ben akarunk módosítani valamit.

Fogjuk szimplán a kis fájlunkat és Worddel indítsuk el (ne az alapértelmezett olvasóval), majd a felugró üzenetet okézzuk le:

Az eredmény pedig magáért beszél, ha bizonyos mértékig a grafikus részek nem is lesznek olyanok, mint magában a pdf-ben, a kapott fájlt már nyugodtan szerkeszthetjük és használhatjuk a későbbiekben:

A képek, chartok és egyéb objektumok szerkesztése és alakítgatása is jelentős változáson ment keresztül, sokkal több lehetőségünk és előre elkészített opciónk van formázgatni tartalmunkat, mint például egy kép esetén:

Ha kijelöljük a képünket (chartunkat, objektumunkat stb.), akkor a Ribbonon láthatóvá válnak az adott objektumra vonatkozó speciális fülek és ezzel együtt a sok-sok új lehetőség például a Layoutokhoz kapcsolódóan:

A kommentek, megjegyzések rendszere is változott annyiban, hogy most már egyes beszúrt javaslatok, kommentek kapcsán az adott kommentablakban tudunk kollégánkkal, partnerünkkel eszmét cserélni, ami jelentős leegyszerűsíti ezt a folyamatot - ha emlékszünk rá még, milyen volt Word 2007-ben ugyanezt megoldani.

Szóval adott egy ilyen megjegyzésünk:

Amelyet valaki el akar fogadni, meg akar vitatni akkor szimplán a beszúrt kommenten belül egy ENTER lenyomásával vagy a Ribbonon lévő Comments szekció Comment opciójával válaszolni tud a beírt javaslatra:

Értelemszerűen ezek most csak kiragadott példák voltak, de már ezekből is látható, hogy vannak olyan változtatások, amelyek igazából az egyszerű felhasználók felhasználói élményét hivatottak javítani és én azt mondom, hogy ez sikerült is.

Anno amikor először elkezdtem használni Excel 2013-at a 2007-es verzió után, kicsit ódzkodtam tőle és a vártnál nehezebben barátkoztam meg dolgokkal, de az biztos, hogy a beépített új funkciók maximális mértékben elnyerték a tetszésemet és lenyűgöztek, annyi mindent adnak készen az ember kezébe, hogy csak győzze kapkodni a fejét. A mai posztban ebből mutatok be kettőt, két olyat, amely nekem igencsak megkönnyíti a dolgomat a mindennapokban, hiszen jelentősen minimalizálja a kattintások számát és az eltöltött idő mennyiségét bizonyos célok elérése érdekében.

Az első ilyen funkció nem más, mint a Quick Analysis tool, amely konkrétan a fenekünk alá tol elemzéseket, grafikonokat - automatikusan létrehozva és megjelenítve őket. Magát ezt a lehetőséget nagyon könnyen elérhetjük, ha szépen kijelöljük a cellánkat, majd a jobb sarokban megjelenő apró kis ikonra kattintunk:

Itt aztán elszabadulhat a képzelőerőnk, formázhatunk, összegezhetünk, átlagolhatunk, diagramokat szúrhatunk be, adatelemzést végezhetünk, ráadásul mindezt valós időben, dinamikusan megjelenítve, ergó még le sem kell okézni semmit, ha simán ráhúzzuk az egeret a Countra, akkor már látjuk is az eredményt:

Ugyanez igaz a diagramnál is, ha szimplán az egeret a kördiagram ikonjára húzzuk, már látszik is az eredmény:

Persze ezek csak ajánlott, default példák, ha nem tetszik, amit látunk, még mindig fordulhatunk a régi módszerhez, de nekem személy szerint igencsak elnyerte tetszésemet a lehetőség.

A másik funkció, amit megemlítenék most, az a már mások által is egekig magasztalt Slicer, ami Pivot-táblás elemzésünket könnyítheti meg jelentősen és ráadásul igényes módon.

Adott mondjuk a következő kis táblázat:

Csináljunk ebből egy teljesen átlagos kis Pivot-táblát, ami soronként megmutatja, hogy egy-egy városban mennyi a teljes bevétel az összes városrészt tekintve:

Na de mi van, ha ezt meg akarjuk bontani városrészekre? Alapvetően az első beidegződés szerint egy Filtert tennénk a Pivot-táblánkra, hogy így megoldjuk a dolgot, ami jó is, de próbáljuk csak ki a Slicert:

Az Insert Slicer gombra kattintva meg tudjuk határozni, hogy mely oszlopunkra akarjuk alkalmazni ezt az extra szűrést:

Szépen létre is jött a kis Slicerünk a Pivot-tábla mellett a Városrészre vonatkozó szűrővel:

Ezen pedig szépen kattintgatva változni fog a Pivot-táblánkban az érték és az összesítés is, ami első körben még lehet kicsit gagyinak tűnhet, de próbáljuk ezt ki komplex Pivot-táblák esetén!

A mai posztban ismét szemezgetek a beérkező kérdésekből, ötletekből (bárkinek bármi Office-kérdése van, bátran írjon!), hiszen elég sokfajta problémával küzdünk nap mint nap, tehát lehet, hogy másnak is jól fog jönni, ha egy-egy kérdés válaszát közzéteszem itt.

A mai téma egy egészen friss kérdés: a kedves beküldő azt szerette volna elérni, hogy egy neveket és lejárati dátumokat tartalmazó táblázatban valahogy kiemelésre kerüljenek a már lejárt dátumok.

Azaz ha a lenti mintatáblázatot nézzük, akkor belépéskor egyértelműen lássuk, hogy mely dolgozók szerződése van lejárt állapotban, sőt még jobb lenne, ha erről belépéskor már üzenetet is kapnánk.

Az alapmegoldás egyértelműen a Conditional Formatting használata, szépen jelöljük ki a megfelelő dátumot tartalmazó oszlopot (célszerű egész oszlopot és nem tartományt kijelölni, hogy a későbbi bővítés ne okozzon pluszmunkát), majd a Ribbonunk Home füle alatt található Styles szekcióból hívjuk elő a Conditional Formatting legördülő menüjét és kattintsunk a New Rule menüpontra:

Itt válasszuk a második szabálytípust, azaz Format only cells that contain típust, majd a szabályt úgy állítsuk fel, hogy azon cellaértékek esetén, amelyek mondjuk kisebbek mint a mai dátum, állítsa a cella hátterét pirosra:

Használjuk bátran a már korábban megismert =TODAY() függvényt, amely szépen megadja mindig a mai napot a szabályunkba. Ha ezt leokézzuk, azonnal láthatóvá válik az eredmény:

Értelemszerűen ezt bármikor csiszolhatjuk még azzal, hogy a mai nap beleszámít-e vagy sem, de akár percre pontosan is belőhetjük a formázási feltételt stb.

Na de ez még nem lenne annyira szuper, ha nem építenénk be egy aprócska kis VBA-kódot az Excel fájlunkba, ami annyit fog intézni nekünk, hogy a fájl minden egyes megnyitásakor apró pop-up üzenetben ki fogja írni azon dolgozóink nevét, akiknek már lejárt a szerződése.

Úgyhogy menjünk is át gyorsan a VBA szerkesztőbe, majd itt a fájlunkon belül található ThisWorkbook objektumba vigyük fel a következőt:

Private Sub Workbook_Open()
Call popupuzenet
End Sub

Ez annyit fog csak tenni, hogy minden egyes megnyitáskor meghívja a popupuzenet subot, ami majd értelemszerűen a pop-up üzenetet fogja feldobni - persze általában nem ez a sorrend szokott lenni, de most biztosra akarok menni, nehogy menet közben elmaradjon.

Ezután pedig hozzuk létre a Sheet1 objektumon belül a popupuzenet subot, hogy az megcsinálja nekünk a szükséges felugró ablakot és üzenetet. Miután korábbi posztokban már kódolgattunk kicsit VBA-ban, egy-két dolgot annyira részletesen már nem fogok bemutatni, mint például most a változók meghatározását. Legyen elég annyi, hogy hozzunk létre három változót, egyet oszlopunk maximális sorszámának tárolására, egyet az aktuális sorunk számának tárolására és egyet szövegként, konkrétan az üzenetünk szövegének tárolására:

Ha ezzel megvagyunk, akkor töltsük fel az uzi és sorszam változókat a kezdőértékeikkel! Uzi változónk tartalmazza majd azt a szöveget, amelyhez hozzárakjuk a már lejárt szerződéssel bíró dolgozóink nevét, sorszam változónk pedig azt az értéket fogja tartalmazni, ahány sorunk van a lejárati dátumot tartalmazó, jelen esetben G oszlopunkban:

Erről azért még beszéljünk egy kicsit, az uzi változó feltöltése valószínűleg nagyjából egyértelmű, azt érdemes megjegyezni, hogy a vbCrLf utasítás sortörést hajt végre, azaz mintha nyomnánk egy ENTER-t szövegünk után. A sorszam változó feltöltése sem annyira komplikált, mint elsőre tűnik, fogjuk az első sheetünket a ThisWorkbook.Worksheets("Sheet1") utasítással, Range("G" & Rows.Count) segítségével pedig G oszlopunkban megszámoljuk a sorok számát (ergó akár írhatnánk azt is, hogy Range("G5:G15"), csak azzal deklarálnánk a tartományunk maximális mértékét, majd End(xlUp).Row segítségével megszerezzük, hogy pontosan hányadik az utolsó olyan cella az oszlopban, amely még tartalmaz értéket - mintha nyomnánk egy CTRL+Lefelé nyilat a tartományunkon belül. Ergó ezzel a sorszam változót feltöltöttük tartományunk nagyságával, tehát meddig terjed "lefelé".

A következő és egyben nagyjából az utolsó lépés is a megoldásban az, hogy egy For..Next ciklus segítségével minden egyes cellánkban ellenőrizzük le, hogy a dátum korábbi-e mint a mai nap vagy sem és ahol már lejárt a határidő, ott töltsük be az ehhez tartozó nevet az uzi változónkba, hogy meg tudjuk jeleníteni.

A For...Next utasítás a közötte szereplő utasításokat hajtja végre annyiszor ahányszor erre utasítjuk, azaz például a

For a = 5 to sorszam
...
Next a

egészen addig fogja a ... helyére írt utasításokat végrehajtani az ötös értéktől kezdve, amíg el nem éri a sorszam változó értékét. A mi esetünkben ezt még majd egy kicsit meg kell tuningolni egy If utasítással is és valahogy így fog majd kinézni a megoldás:

Azaz miután az ötödik sortól kezdődik a tartományunk, így ettől a sortól kezdve egészen a sorszam változóban szereplő értékig ismételje az If..End If közé zárt utasítást, tehát nézze meg, hogy aktuális sheetünk G oszlopának éppen a változóban szereplő sorában a dátum és a mai dátum különbsége nullánál nagyobb vagy kisebb-e:

ThisWorkbook.Worksheets("Sheet1").Range("G" & a) - Date <= 0 Then

VBA-ban a Date utasítás jelenti a mai dátumot, tehát mintha TODAY() függvényt használnánk Excelben.

Szóval ha a különbség kisebb, mint nulla akkor (Then) az uzi változóban eddig szereplő üzenetünket (amit az előbb már beírtunk konstansként az uziba) bővítse ki a sheetünk F oszlopának aktuális sorában szereplő nevével és adjon hozzá még egy szóközt, majd folytassa a ciklust.

uzi = uzi & ThisWorkbook.Worksheets("Sheet1").Range("F" & a).Value & " "

Ezzel már csak egy lépés maradt hátra, MsgBox utasítással (ez használható üzenetek megjelenítésére a képernyőn, rengeteg különféle paramétere van), írassuk ki az uzi változót a képernyőre a Workbook minden egyes elindulása esetén, valahogy így:

A mai posztban nem egy konkrét probléma megoldásával fogok foglalkozni, hanem bemutatok egy kevésbé ismert érdekességet és egy olyan lehetőséget Excelben, amely a CTRL+C - CTRL+V billentyűkombinációt igen sűrűn használó és emiatt annak korlátaival is tisztában lévő felhasználók számára lehet hasznos.

Az érdekesség nem nagy dolog, de valószínűleg nem sokan ismerik az Excelnek azt a defaultolási módszerét, amelyet a Ribbon Data fülének Data Tools szekciójában található Text to Columnsba van beépítve. Ugyebár az közismert, hogy ha mondjuk txt-ből másolunk valamit Excelbe, akkor az egyetlen oszlopba fog bekerülni, hiába legyen szó hosszú tartalomról vagy sok oszlopról a txt fájlunkban és itt általában mindenki a Text to Columns funkcióhoz fordul segítségért.

Ahogy a következő kis képen látható is, ha mondjuk txt-ből beillesztek egy szövegrészt Excelbe, akkor az egész egyetlen cellában fog átláthatatlanul megjelenni:

Ilyenkor szépen fogjuk a Text to Columns funkciót:

Kiválasztjuk a Delimited fájltípust, hiszen egy szöveg esetén szóközzel tudjuk leginkább elválasztani egymástól szavainkat és szétdobálni őket különböző cellákba:

Majd szimplán Finishre kattintunk és el is készült az, amit szerettünk volna eredetileg is elérni.

És itt a történetben az érdekes csavar, hiszen Excelünk egészen addig, amíg nem zárjuk be (és hát elég sokan élünk a non-stop hibernáció világában hónapokig le nem állított gépekkel) meg fogja jegyezni ezt a beállítást, tehát a következő hasonló txt-ből történő beillesztésnél már alapból szét fogja dobálni szavainkat a szóközök mentén.

A másik hasznos lehetőség a CTRL+V igen sűrűn használt beillesztési billentyűkombinációjára vonatkozik, ami legtöbbször azért lehet frusztráló, mert alapesetben nem tudja kezelni azokat az elvárásainkat, hogy Paste Special as Values, tehát értékként, mindenféle formázás és képlet nélkül illesszen be a vágólapról (itt szokott képbe jönni a Quick Access Toolbarra helyezett Paste Special as Value gombocska).

Ha biztosak vagyunk benne, hogy számunkra a CTRL+V hasznosabb lenne Paste Special as Values-ként, akkor szimplán vegyük fel makróval ezt a beállítást és rendeljük hozzá ugyanehhez a billentyűkombinációhoz.

Ribbonunk Developer füle alatt találjuk a Code szekciót, azon belül pedig simán kattintsunk a Record Macro gombra:

Billentyűkombinációként értelemszerűen tehát a CTRL+V-t választottam ki, majd leokézom és szépen rögzítem a Paste Special as Values típusú beillesztést:

Majd a rögzítéssel leállításával kész is vagyunk, mostantól már úgy működik a CTRL+V ahogy szerettük volna - persze azért az ilyesmivel csínján bánjunk, mert érdekességnek jó, de igencsak megkeverheti a napi munkánk megszokott rutinját.

Valószínűleg egyetlen Excel-felhasználónak sem kell bemutatni a Conditional Formatting igen hasznos funkcióját, amelynek segítségével egyszerűen és könnyen elérhetünk olyan dinamikusan változó táblázatszerkezetet, amellyel átláthatóan ki tudjuk emelni a számunkra érdekes, fontos adatokat.

Szintén csak valószínűsíteni tudom, hogy az olyan alapfelvetések megoldása, mint az összes üres cella kiszínezése vagy bizonyos feltételeknek megfelelő cellák hátterének megváltozása nem okozhat senkinek gondot, de azt tudjuk ugye, hogy eme állapot elérésének számokat tartalmazó tartományok esetén van egy még könnyebb módja?

Tehát ha mondjuk azt szeretnénk, hogy a lenti táblázatban látható értékek közül különböző színnel jelenjenek meg bizonyos határ feletti számok, akkor elég szimplán csak a számformátumot customizálni, hiszen ez a most már több mint 30 éves Excel "eredeti Conditional Formatting" megoldása:

Jelöljük tehát ki a tartományunkat, majd CTRL+1 lenyomásával hívjuk elő a Format Cells cellák formázására vonatkozó ablakát, ahol a Number fül alatt ballagjunk szépen a kategórialista legaljára és kattintsunk a Custom lehetőségre:

Itt a Type alatt tudunk különféle speciális számformátumokat alkotni, többet már előre definiáltan láthatunk is itt, de azt, ami minket érdekel még nem, ugyanis nekünk a következő lesz szükségünk:

[Red][>=80];[Blue][>=30];0

Mit is fog ez csinálni? Fogja a 80-nál nagyobb számainkat és pirosra színezi őket, a 30-nál nagyobbakat kékre, az egyik feltételt sem teljesítő értékeink pedig maradnak a default font színnél, ezért a 0 a végén.

És szépen minden változásra is reagál a custom formázásunk, hiszen bármelyik számot megváltoztatva változni fog a szín is - a korlátok itt mondjuk erősebben érezhetőek, mint a Conditional formattingnál, hiszen Custom formatként összes két feltételt adhatunk meg, tehát a default font színnel együtt ez csak három színezési lehetőséget jelent.

Amiért nekem különösen tetszik ez az ősrégi megoldás, az pont ebből adódik, hiszen nem sokan tudnák megfejteni első ránézésre, hogy mi alapján színeződnek celláink értékei, ha még egy nyomorult Conditional Formatting Rule sincs tartományunkon.

Vicces, ugye?

Egy korábbi posztban már bemutattam egy érdekesebb diagramos megoldást, akkor oszlopdiagramunkat a megszokott színek helyett egy képpel ábrázoltuk, most ezt a sorozatot folytatva szintén oszlopdiagramot fogunk készíteni, csak ezúttal töltöttségjelzőként (vagy fene tudja, hogy hívják az ilyesmit) fogjuk majd oszlopainkat ábrázolni.

Adott tehát a szokásos kis táblánk városokkal és bevételekkel valamint kiadásokkal:

Ebből szépen készítsünk egy egyszerű, kétdimenziós Clustered diagramot a Ribbonunk Insert füle alatt található Column menüből:

Úgyhogy most valahogy így néz ki a munkalapunk:

Ezután jelöljük ki oszlopainkat a diagramban, majd jobb gomb lenyomása után válasszuk ki a Format Data Series menüpontot:

Itt a Series Options alatt az oszlopok egymásra illeszkedését (Series Overlap) állítsuk maximálisra, azaz a piros és kék oszlopaink egymásra fognak kerülni:

Jelöljük ki Bevételt mutató (jelen esetben kék) oszlopainkat és ismét menjünk vissza a Format Data Series menübe:

Itt egyrészt a Fill menü alatt kapcsoljuk ki a kitöltést (No Fill), majd a Border Color menü alatt állítsuk pirosra (hiszen az Kiadás oszlopaink színe) a Bevétel oszlopok keretét:

Ha ez megvan, akkor következő lépésként, jelöljük ki a Kiadás oszlopunkat, majd jobb gomb lenyomása után adjunk hozzá Adatmegjelenítést (Add Data Labels) és ugyanezt csináljuk meg a Bevétel oszlopokkal is:

Most már azok a lépések jönnek, amelyek bárki saját szája íze szerint meg tud csinálni, formázgassuk, csinosítgassuk, távolítsuk el a Gridlinet (Chart Tools Layout füle alatt Axes szekció Gridlines menüpontja), akár Y tengelyünk értékeit is, majd jelentsük be, hogy készen vagyunk:

Saját véleményem szerint az egyszerűbb kis VBA-kódok használati rangsorában egyértelműen előkelő helyen találhatóak az e-mail küldő makrók, főként vállalati környezetben, ahol esetenként 10-20-100 levelet kell ugyanazzal a szöveggel, más-más címezetteknek elküldeni valamilyen csatolmánnyal - magam is legalább tíz különféle verziójú mailküldő makrót láttam már Outlookra optimalizálva, úgyhogy a mostani posztban egy ilyen megoldás igen leegyszerűsített verzióját fogom bemutatni VBA-ban. Hangsúlyozom előzetesen, hogy rendkívül sokrétű, sokféle módon elkészíthető makrós megoldásról van szó, az Excelben külön felhasználói felületet építhetünk gombokkal, mezőkkel, színes, csilli-villi cuccokkal, én most azonban kizárólag az alapokra fogok koncentrálni, aki elég elhivatott vagy lelkes, az innen már úgyis könnyen tovább tud lépni (megjegyzés: a korábban itt már bemutatott dolgokat fel fogjuk használni, úgyhogy időnként már csak hivatkozom arra a megszerzett tudásunkra).

Első lépésként készítsünk valami hasonló kis táblát az Excelben (vagy amilyen formában, kinézettel akarjuk), innen fogja majd a kis programunk felvenni az értékeket az e-mailhez:

Ezután szépen ALT+F11 lenyomásával sétáljunk át a VBA editorba, ahol lássunk neki az aprócska makró összeállításának, méghozzá kezdjük kapásból azzal, hogy létrehozunk két objektumot, az egyiket az Outlook elindítására, a másikat pedig az E-mail létrehozására, hiszen ezzel férünk majd hozzá az Outlook összes tulajdonságához, metódusaihoz (követve a mintát, amelyet a Microsoft hivatalos leírásában is találjuk):

Az objektumok tehát már megvannak, most töltsük is fel őket, az Outlookprogi objektumunk maga az Outlook program lesz, tehát hozzuk létre:

CreateObject("Outlook.Application")

Értelemszerűen ha mondjuk a WORD-öt akarjuk meghívni, akkor Word.Applicationt használunk.

E-mail objektumunkat pedig ebből következően már az előbb feltöltött Outlookprogi objektum egyik tulajdonságával töltjük fel, azaz a CreateItemmel, hiszen ez fog létrehozni nekünk egy új levelet az Outlook programjában (értelemszerűen egyéb utasításokat is használhatunk más célokra, más programokban is):

Outlookprogi.CreateItem(0)

Miután már megvan az Email nevű objektumunk, használhatjuk annak tulajdonságait is, azaz hozzáférünk a To, a CC vagy akár a csatolmány tulajdonságokhoz is, azaz ezt most úgy képzeljük el, hogy van már egy üresen megnyitott levelünk Outlookban, amelyet fel kell töltenünk tartalommal.

Ahogy megismertük már, a With...End With parancs a közötte felsorolt utasításokat hajtja végre ugyanarra az objektumra vonatkozóan, tehát jelentős mennyiségű gépelést megspórol nekünk, amit pedig itt is fogunk hasznosítani, hiszen Email objektumunk tulajdonságait fogjuk módosítani a következő módon:

Ahogy látható a To értéket az első sheetünk első sorának második oszlopából vesszük fel, CC-t most üresen hagyjuk (de azért idetettem, hiszen ez általában fontos opció szokott lenni), hiszen nem várunk ilyen értéket, BCC-be célszerű valamilyen közös postafiókot, vagy saját e-mail címünket megadnunk, a működés biztosítása érdekében, Subjectbe meghívjuk az első sheetről a megfelelő cellát megint, ahogy tesszük ezt az e-mailünk szövegével is, plusz szépen hozzácsatolunk egy fájlt is a saját meghajtónkról. Fontos, hogy a .Display utasítással nem küldjük még el az e-mailt, hanem megnézhetjük a Send gombra kattintás előtt, ha ezt nem akarjuk megtenni, akkor simán használjunk helyette .Send utasítást és akkor nem is zavar minket semmilyen felugró ablak.

Még egy lépésünk van hátra az alapkódban (ezt mindenképpen célszerű megtenni), ez pedig az, hogy ürítsük ki két változóként deklarált objektumunkat, hogy az újabb használatnál a korábbi adatok már ne zavarjanak be a történetbe, plusz az sem mindig garantált, hogy az applikációt nem változtatjuk meg.

Azaz ez az egész kódunk:

Dobjunk hozzá össze egy apró kis gombot a Ribbonunk Developer füle alatt található Controls szekcióba lévő Insert felhasználásával, majd a gombunk kattintás eseményéhez rendeljük hozzá a fenti makrót, így mostantól már gombkattintsára generálódik az e-mail:

Az elmúlt munkanapok igencsak megterhelőek voltak agysejtjeim számára, úgyhogy most nem vagyok túlságosan jó állapotban ahhoz, hogy komplex problémákra prezentáljak megoldásokat, így a következőkben következzen inkább néhány olyan Excel-tipp, amelyek nem biztos, hogy annyira hasznosak, de hogy le tudunk vele nyűgözni bárkit egy Excelhez kapcsolódó bemutató vagy beszélgetés kapcsán, az biztos. Folytatva tehát korábbi sorozatomat, következzen 6 vicces, érdekes és talán hasznos Excel-tudnivaló:

1. Dupla kattintás az Office-gombon:

Bevallom őszintén, ezt én soha nem fogom megszokni, de azért vicces, ha akár csak véletlenül is duplán kattintunk a nagy Office-gombunkon és ezzel bezárjuk a programot magát.

2. Ha már dupla kattintás, Ribbonunk bármely fülére duplán kattintva minimalizálhatjuk a Ribbont, hogy így több helyünk maradjon mondjuk egy dashboard készítése közben.

3. És legyen még mindig a dupla kattintgatás, jöjjön egy tényleg hasznos kis lehetőség: bármely olyan funkció ikonjára duplán kattintva, amelynél az ismételt használatnak van értelme, elérhetjük, hogy többször ne kelljen az ikonra kattintani és bekapcsolva marad a funkció egészen addig, amíg ismét nem kattintunk rá!

4. Hibakeresésre és mások logikájának lekövetésére fontos ismerni azt az opciót, amelyet az Excel Options alatt az Advanced fülön elérhető Allow editing directly in cells pipa kivétele jelent. Ha ez be van pipálva, akkor dupla kattintással egy cellára, konkrétan szerkeszteni tudjuk cellánk tartalmát, viszont ha kikapcsoljuk az Allow editinget, akkor dupla kattintással szépen azokon a cellákon ugrálhatunk végig, amelyeket cellánk tartalma meghivatkozik:

5. Formázzuk meg Comment mezőinket, hiszen mennyire más, tényleg figyelemfelkeltő lenne, ha nem a megszokott kocka mutatná kommentjeinket, hanem akár gondolatbuborék jelenne meg celláink mellett? Fogjunk egy átlagos commentmezőt:

Az Excel Options Customize füle alatt adjuk hozzá Quick Access Toolbarunkhoz a Change Shape parancsot, valahogy így:

Ezután már csak annyit kell tennünk, hogy kijelöljük Comment mezőnket és a Toolbarról kiválasztjuk a most hozzáadott Change Shape parancsot majd kiválasztjuk azt a formát, amilyenre módosítani akarjuk megjegyzésünk formáját:

És láss csodát:

6. Velem időnként előfordul nagy táblázatokban, hogy egyszerűen túlgörgetek a kijelölt tartományon és utána időbe kerül visszakeresni a kijelölést, erre tökéletes a CTRL+BACKSPACE billentyűkombináció, amellyel azonnal ismét odajutunk a kijelölt tartományunkhoz.