Általánosságban ha azt akarjuk megtudni, hogy dokumentumunkat (most a példában Excel-fájlról lesz szó, de ugyanez működik más Office-programoknál is) ki és mikor frissítette utoljára, akkor el sem indítjuk magát a programot, hanem kattintunk jobb gombbal a fájlon, kiválasztjuk a Properties menüpontot, majd a Details fül alatt láthatjuk a minket érdeklő információkat.

De ha esetleg ezt nehézkesnek tartjuk, akkor egy alig néhány soros makró segítségével szinte bármilyen információ (köztük az utolsó mentés dátuma mondjuk) megjelenítésére felhasználhatjuk az Excel Title Bart, a sort, ahol alapesetben csak a fájlunk neve látszik:

Alt+F11 lenyomásával ugorjunk is a VBA-editorba, ahol a Workbook objektumhoz szúrjuk be majd a Workbook Open eseményéhez tartozó kódunkat:

A pár soros makróhoz mindössze két, eddig még nem ismert objektumot illetve tulajdonságcsoportot kell megismernünk, ezek közül az első a Window objektum vagyis a Windows objektumcsoport, amely a Workbook objektum esetén az összes adott munkafüzeten belüli ablakot kezeli, tehát a ThisWorkbook.Windows(1) az első nyitott ablakra mutat. Ennek aztán van bőven tulajdonsága, ezek listáját megtalálhatjuk a Microsoft hivatalos oldalán példákkal együtt: ilyen például a WindowState amellyel teljes képernyőre tudjuk tenni a munkalapunkat vagy a Visible, amivel láthatóvá példának okáért. Minket azonban most a Caption érdekel, ugyanis ez a tulajdonság a Title Bar szövege.

Ezzel a kód első fele meg is van, hiszen ThisWorkbook.Windows(1).Caption utasítást kell majd egyenlővé tennünk a fájl nevével illetve az utána megjelenő, általunk akart információval.

Megint csak irány a Microsoft hivatalos oldala, ahol nagyon könnyen rálelhetünk a Workbook.BuiltinDocumentProperties tulajdonságra, amelynek segítségével legalább több tucat információt megtudhatunk dokumentumunkról, a teljes igénye nélkül például a nevét, utolsó nyomtatási dátumát, a teljes szerkesztési időt, az oldalszámot, a kategóriát és többek között az utolsó mentés dátumát is.

Eddig itt tartunk a kódban:

Azaz a munkafüzet megnyitásakor az alap Title Bar legyen egyenlő az alap Title Barral plusz ehhez fűzzük hozzá most a kért információt, ami nem más mint a fentiek alapján a ThisWorkbook.BuiltinDocumentProperties tulajdonságcsoport Last Save Time tulajdonsága.

Ha most bezárjuk és mentjük a doksit, majd újra megnyitjuk, már látjuk is az eredményt:

Hangsúlyozom, hogy ismét ne a konkrét példánál ragadjunk le, hanem annál, hogy a Windows objektumcsoport tulajdonságaival illetve a BuiltInDocumentProperties csoporttal mennyi minden elérhető még.

Manapság egyértelműen látható az a tendencia, hogy önmagában összeállítani egy Excel-táblát az eredményeinkkel nem üt túlságosan nagyot, adataink vizualizációjára, az infografikákra igen nagy hangsúlyt kell fektetnünk, ha le akarjuk nyűgözni a célközönségünket.

A következő rövid posztban egy olyan technikát mutatok be, amely igazából elég sokféle infografika elkészítésére ad lehetőséget, tehát a most bemutatott verzió csak egy, a végtelen számú opcióból.

Kezdjük azzal, hogy a Ribbonunk Insert füle alatt található Illustrations szekció Shapes menüjével szúrjunk be mondjuk egy egyszerű téglalapot:

Jelöljük ki, majd jobb gombbal való kattintás után válasszuk ki az Edit Points funkciót, ami lehetővé teszi, hogy default formánkat mindenféle irányba és méretbe alakítgassuk:

Az Edit Points kiválasztása után valahogy így néz majd ki téglalapunk, a pontok mentén tudunk görbíteni, szélesíteni, elhúzni stb.:

Én most szimplán behullámosítom két pont elhúzásával a téglalapomat:

Mire mentem ezzel? Hát így önmagában semmire, ezért gyorsan készítek is egy szélességét tekintve az eredeti téglalappal megegyező méretű "magas" téglalapot:

Ebből eltávolítom a kitöltést és megvastagítom a körvonalakat (Format Ribbonfül alatt Shape Fill and Shape Outline):

Ezután következik néhány fontos lépés: elsőként a View Ribbonfül alatt található Show szekció Gridlines pipájának eltávolításával eltüntetem a táblázatkezelőm háttérvonalalait. Folytatásként az üres, nagy téglalapomba belehúzom a hullámosított eredetit, majd a jobb gombbal való kattintással előhívható Group funkció segítségével egyesítem őket. Nem maradt más hátra, mint hogy rádobjunk valamilyen információs szövegdobozt vagy feliratot és kész is:

Lássuk meg, hogy ez csak egyetlen kiragadott lehetőség volt, az Edit Points segítségével nagyjából a csillagos ég a határ, csinálhatunk félig tele poharat hullámzó vízzel, megcsinálhatjuk ezt körrel vagy háromszöggel is, de a színek és alakzatok hatalmas skáláját böngészve látható, hogy bőven tudunk még kreatívkodni.

A következő rövid posztban felvázolt történet nem mai darab, sőt mondhatni ősrégi, de a kérdés elég friss, úgyhogy álljon itt mindenki okulására - avagy hogyan szúrjunk be teljesen üres oszlopot Pivot-táblánkba.

Adott tehát egy teljesen hétköznapi Pivot-tábla, amelybe valamilyen okból, mondjuk vizuális hatás növelése érdekében szeretnénk beszúrni üres, színes elválasztó oszlopokat:

Nem ez az egyetlen megoldás, amit most fel fogok vázolni, de ez bátran használható régebbi Excel-verziókban és PowerPivotban is, bár nem tartom kizártnak, hogy vannak még ilyen megoldások. Szóval első lépésként a Pivot-táblánkra kattintva megjelenő PIVOTTABLE TOOLS Ribbon-fülek közül az ANALYZE fülre kattintva a Calculations funkciócsoport Fields, Items, & Sets gombja alól a Calculated Field funkckiót hívjuk meg.

Vigyünk fel mondjuk egy Blank névre hallgató kalkulált oszlopot, amely mögött a formula legyen =0, de használhatunk más, üres vagy egyszerűen átalakítható eredményt hozó kalkulációt is.

Ha ezt hozzáadjuk a Pivot-táblánkhoz, akkor ugyebár lesz egy alapból Sum of Blank nevet viselő és csupa nullát tartalmazó oszlopunk, amelyet aztán jobb gombbal kattintva szépen meg fogunk formázni, elsőként a Number Format menüpont alatt:

Az oszlopunkban található celláink formátuma legyen Custom formátum, majd három pontosvessző megadásával érjük el, hogy teljes egészében (láthatólag) üres celláink legyenek:

Ezután a következő lépésben a szintén jobb gombos kattintás után előcsalogatható Value Field Settings menüből tüntessük el az oszlopunk nevét, ami legyen mostantól egy egyszerű szóköz például:

Aztán innentől kezdve már csak a fantáziánk szabhat határt, hogy mennyire akarjuk a beszúrt oszlopainkat formázgatni, alakítgatni, szóval a fentebb felvázolt, manuális megoldás csak az első lépés volt Pivot-tábláink még vizuálisabb megjelenítése felé.

Ötletek, javaslatok jöhetnek kommentben!

A mostani poszt remélhetőleg sokak (köztük jómagam) számára is tanulságos írás lesz, hiszen egy olyan feladatra keresem a megoldást, amely rendszeresen szembejöhet velünk a mindennapokban (legalábbis az első része). Adott a következő roppant egyszerű Excel-tábla:

Már ránézésre egyértelmű az is, hogy mi itt a kérdés, simán keressük meg a megadott városhoz tartozó bevételi értéket a segédtáblában. Én most egyféle megoldást fogok felvázolni, de hozzászólásokban jöhet minden más javaslat is, hiszen ez tipikusan az a kérdés, ahol legalább féltucat függvényalapú megoldást el bírok képzelni (az egyebekről ne is beszéljünk).

Az én megoldásom (mivel számokról van csak szó) a SUMIF függvény (de természetesen használható bővített verziója, a SUMIFS is), amely az első paramétereként megadott tartományban összesíti a második paramétereként megadott kritériumnak megfelelő értékeket - harmadik, nem kötelező paraméter pedig az összeadandó értékek tartományának külön meghatározása lehet.

Jelen példánknál ez azt jelenti, hogy a tábla lesz az első paraméter, a második paraméter a városunk neve, a harmadik paraméter pedig az első számértéket tartalmazó sortól kezdődően a táblánk megint:

És íme, az eredmény:

Na de mi is ezzel a probléma? Hát elég egyértelmű: kizárólag számokra használható, tehát ha mondjuk nem numerikus értékeink is vannak, akkor ez így már nem lesz jó - lásd a következő módosított példa:

De még mielőtt azonban erre is bemutatok egy lehetséges megoldást, hangsúlyozni kell, hogy egy ilyen jellegű probléma már nem mindennapos, az pedig, hogy formulával próbáljuk megoldani, még annál is ritkább, hiszen előbb alakítjuk "normál" módon kezelhető táblázattá vagy fordulunk VBA-hoz, minthogy függvények egybeágyazásán gondoljunk. Ettől függetlenül jó kihívás a gondolkodni vágyó elme számára egy ilyen probléma - lássuk is, hogy én mire jutottam.

Az én logikám az INDEX függvényen alapul, azon a függvényen, amely az első paramétereként megadott tartományban a második paramétereként megadott sor és a harmadik paramétereként megadott oszlop metszeti cellájának értékét adja vissza.

Azaz ha meg tudjuk határozni, hogy az adott város melyik sorban, illetve melyik oszlopban található, akkor már csak az INDEX formulába kell ezeket ágyaznunk és kész is a válasz.

Szóval adott az első kérdés: hogy határozzuk meg, hogy "Esztergom" értékünk melyik sorban található?

Én szokás szerint a SUMPRODUCT függvényhez nyúlok, annak is egy feltétellel kibővített verziójához. Ez a feltétel nagyjából annyiból fog állni, hogy az A2:C7 (a táblánk) összes értékét megvizsgáljuk, hogy valamelyik érték egyezik-e a megadott városunk nevével - azaz eredményként rengeteg nullát és egy egyest várunk, ott ahol van egyezés.

(A2:C7=G3)

Ezt a tömböt pedig szimplán összeszorozzuk egy, a ROW függvény által meghatározott, hivatkozásokat tartalmazó másik tömbbel, amely a táblánk összes sorát tartalmazza:

ROW(A2:C7)

A szorzás eredményeként lesz egy tömbünk, amelyben nullák és egy olyan szám szerepel, szóval ha ezt összegezzük egy SUMPRODUCT segítségével, akkor megkapjuk, hogy hányadik sorban szerepel az adott városunk:

Innen már nyílegyenes út vezet az oszlopszám meghatározásához, elég ismernünk a COLUMN függvényt, ami teljesen ugyanazt tudja, mint a ROW, csak oszlopokra, azaz oszlopok hivatkozásait adja vissza, tehát egy megadott celláról megmondja, hogy az hányadik oszlopban szerepel.

Teljesen ugyanazt kell csinálnunk tehát, mint a sor meghatározásánál, annyi kivétellel, hogy az első tömbünket a COLUMN által meghatározott, hivatkozásokat tartalmazó másik tömbbel szorozzuk össze:

Ezzel pedig az egész történetnek a végére is jutottunk, hiszen ezt a két függvényt kell csak beágyaznunk egy INDEX formulába, amelynek első paramétere értelemszerűen a táblánk, második paramétere a SUMPRODUCT és ROW függvények felhasználásával készült sorszám, a harmadik pedig a COLUMN száma:

=INDEX(A2:C7,SUMPRODUCT((A2:C7=G3)*ROW(A2:C7)),SUMPRODUCT((A2:C7=G3)*(COLUMN(A2:C7))))

Works like charm!

Folytatva a pár hónappal ezelőtt elkezdett sorozatot, ismét az Excel egyik titkos fegyveréről, a szinte mindenre képes SUMPRODUCT függvényről lesz szó, annak egy újabb felhasználási módjáról. Az alapdefiníció és funkcióleírás valószínűleg mindenkinek a könyökén jön ki: két vagy több tömb elemeit szorozza majd adja össze, azaz két tömb egymással egy sorban lévő elemeit összeszorozza majd az eredményeket szummázza.

Na de ez csak a legalapvetőbb felhasználási módja, a múltban írtam már egyéb lehetőségekről is, ma pedig ismét bővítjük ezen tudásanyagot. A következő példatáblában tegyük fel, hogy az elmúlt két hétben egyik kollégánk ledolgozott óráinak számát látjuk, a munkaidő következetesen nyolc óra, ha valaki ennél többet dolgozik, az nem számít, ha kevesebbet, akkor értelemszerűen a kevesebb óra kerül majd figyelembe vételre az elszámolásnál:

A fenti definícióval egyébként meg is határoztam a feladatot: hogyan lehet egyetlen képlettel (VBA-val még egyszerűbb amúgy) meghatározni, hogy pontosan mennyi óra kerül elszámolásra? Ismétlem, a 8 óra feletti időtartam nem számít, ha viszont kevesebbet dolgoztunk, akkor a ledolgozott órák számítanak.

Ha simán szummázunk, látjuk, hogy összesen 86 óra 32 percet dolgozott a kolléga, de valójában ennél jóval kevesebbet fog majd az elszámolásában látni, hiszen a 8 óra feletti rész nem számít.

Ha pontosan akarjuk kiszámolni, láthatjuk, hogy így valójában csak 77 óra 38 perce kerül majd elszámolásra:

A probléma megoldása ott rejlik a manuálisabb, segédoszlopos megoldásban, hiszen azt a képletet kell összehoznunk egy cellába, ami az összes ledolgozott idő szummájából levonja az érintett sorok nyolc órán felüli részét.

Tehát kezdjük a képletünket egy egyszerű szummával, hiszen ebből vonjuk majd le a többletidőt:

A következő lépésben azt a kérdést kell megválaszolnunk, hogy hogyan tudjuk meghatározni egy tömbre vonatkozóan a tömb összes eleme és a 8 óra közötti differenciát. A TIME függvényt fogjuk bevetni, ami az első, második és harmadik paramétereként megadott óra, perc, másodperc értékeket alakítja számmá, azaz TIME(8,0,0) 0.33-at fog eredményül adni, hiszen ez pont egy nap harmadrésze. Minden egyes sor esetében ebből a meghatározott számból vonjuk ki az aznapi ledolgozott időt (tehát tömbfüggvényként fogjuk kezelni ezt a formulát) és ha ez a különbség kisebb, mint nulla, akkor TRUE, ellenkező esetben FALSE értéket dobjon vissza. CTRL+SHIFT+ENTER lenyomásával tömbfüggvényként használjuk.

{=(TIME(8,0,0)-B2:B11<0)}

A következő kép részkalkulációjában látható, hogy milyen tömböt kapunk eredményként:

TRUE értéket látunk majd a tömbben ott, ahol 8 óránál többet dolgozott az illető, FALSE értéket pedig ott, ahol kevesebbet.

Ne felejtsük el, hogy ez még csak a végső tömbfüggvényünk második része (az első a ledolgozott idő sima szummája volt), úgyhogy jön a harmadik fázis, ahol az is kiderül, hogy mire fogjuk a FALSE/TRUE értékeket felhasználni. Azt már korábbi posztokban taglaltam és fel is használtam, hogy mi történik akkor, ha a TRUE illetve FALSE értéket megszorozzuk egy számmal: TRUE esetén a számot, FALSE esetén nullát kapunk vissza.

Ergó most van egy tömbünk TRUE és FALSE értékekkel, tehát azoknál a soroknál, ahol TRUE értéket kaptunk eredményül, meg tudjuk határozni, hogy pontosan számértékben mekkora időmennyiséggel dolgozott többet a kolléga. Ehhez nem teszünk mást, mint simán összeszorozzuk a TRUE/FALSE tömbünket az egyes sorokra kiszámolt időkülönbséggel (ami 8 órához képest többlet esetleg hiány):

{=(TIME(8,0,0)-B2:B11<0)*(TIME(8,0,0)-B2:B11)}

A lenti segédkalkulációban látszik, hogy jelenleg milyen tömbünk van, TRUE és FALSE értékek helyett a pontos számértékként megjelenő időtöbbleteket látjuk:

A SUMPRODUCT függvényt most azért fogjuk bevetni, hogy összeadjuk ezeket a differenciákat a tömbben, majd az egész eredményt szépen hozzáadjuk az eredeti ledolgozott idő összegünkhöz. Azért hozzáadjuk, mert a SUMPRODUCT összegzésünk eredménye egy negatív szám, ergó ha kivonnánk, akkor a -- okán hozzáadnánk, ami nem a valós eredményt tükrözné, hiszen duplán elszámolnánk a többletmunkát. A helyes eredmény tehát:

Nem tudom, hogy a következő problémának már van-e megoldása valamelyik frissítésben, egyáltalán probléma-e, de akárhogy is legyen, a következő kis Excel-történetet akár bugnak is nevezhetjük, amely bár senki életét nem fogja megkeseríteni, egy kis munkaközi szünetben azonban talán jól eladható kis sztori. Excel 2013-ról beszélünk.

Adott a következő apró tábla (de lehet bármilyen táblázat, a hiba reprodukálható):

Jelöljük ki mondjuk az ötös sort és jobb gomb lenyomása után a menüből válasszuk a Cut (Kivágás) funkciót:

Szépen el is kezd a villogó keret futkározni a sorunk körül:

Kattintsunk a hetes sorra majd a jobb gomb ismételt lenyomása után illesszük be a kivágott sorunkat (Insert Cut Cells):

Meg is történik a csere:

Ezután nyomjunk egy Undot, hogy visszalépjünk egy lépést a történetben:

Ekkor hol is tartunk? A kivágásra ítélt ötös sorunk elvileg ott van a vágólapon, tehát bárhova máshova beilleszthetnénk.

Próbáljuk meg most mondjuk a hatos sor fölé beszúrni:

Oppá, nem történt semmi!

A hibát egyébként más táblázatoknál, más parancsoknál is elő tudjuk csalogatni, szimpla cellák kivágásánál majd beillesztésénél vagy akár formátumok másolásánál is.

Mindenesetre annak érdekében, hogy azért tényleg valami hasznos is kikeveredhessen ebből a posztból, a végére álljon itt egy rövid kérdés megválaszolása, amely nagyjából arról szól, hogyan tüntessük el az Excel és egyéb Office-programok standard, template választós bejelentkező képernyőjét:

Nyomás az Excel Optionsbe, ahol a legelső, a General fül alatt, legalul a Start up options szekcióban találjuk a megoldást, a Show the Start screen when this application starts opciónál kell kivennünk a pipát:

A mai posztban ismét csak visszatérek a különféle kérdések megválaszolásához, ezúttal két egyszerűbb táblázatkezelős funkció bemutatására kerül sor, és bár biztos vagyok benne, hogy sokak számára már ismertek, remélem lesz olyan kedves Olvasó, akinek sikerül segíteni vagy saját feladata megoldásához ötletet adni.

Első kérdés: Lehet-e Excelben karakterszám alapján szűrni?

A válasz röviden igen, ráadásul nem is kell hozzá trükközni, a megoldás ott van leírva a Custom AutoFilter funkció formján. A következő táblázaton fogom bemutatni, hogy is kell ezt megtenni:

A szűrő beszúrása után lenyitjuk a lehetőségek kis ablakát, ahol a megszokott opciókat láthatjuk, de mi most ezeket hagyjuk és menjünk a Text Filters alatt található Custom Filter menübe:

És itt látjuk is a fentebb már említett funkcióleírást, kérdőjelek használatával a karakterszámra tudunk szűrni - annyi kérdőjelet használjunk, ahány karakterről beszélünk, lehet egyenlőséget vagy akár kisebb-nagyobb feltételt is megadni.

Csillag használatával pedig karaktersorozatokat tudunk helyettesíteni, ergó töredékekre is tudunk szűrni, azaz például olyan szavakra, amelyek tartalmazzák az "office" szócskát mondjuk.

Második kérdés: Hogyan és mire használható a Camera funkció Excelben?

A hogyan kérdésre egyszerűbb megadni talán a választ, hiszen mindössze három lépés elég hozzá, hogy használjuk ezt a funkciót. Első lépésként az Excel Options alatt vagy a Customize Ribbon menüvel a Ribbonra, vagy a Quick Access Toolbar menüvel a QAT-re tegyük ki a Camera alkalmazást.

Ha megvan:

Akkor már csak használni kell, jelöljük ki azt a tartományt, amit le szeretnénk fényképezni, majd kattintsunk a kis fényképezőgép ikonra:

Ezután pedig már csak azt kell meghatározni, hogy hova illesszük be az elkészült fényképet, amit aztán méretezhetünk, formázhatunk és mozgathatunk.

Mire használhatjuk? Alapvetően én eddig főleg dashboardoknál használtam, ahol például egy legördülő menüből kiválasztott lehetőség alapján jeleníthettük meg a kis képünket, de egyszerű vizualizációra is használható eredmények prezentálásakor. Sőt akár ennek segítségével az egyébként elég küzdelmes úton kinyomtatható anyagokat is be tudjuk egyetlen lapra varázsolni. Ha esetleg valakit bővebben érdekelne a Camera funkció, az jelezze kommentben és szentelek neki egy külön posztot.

Bármikor könnyedén előfordulhat egy feladat elvégzése során, hogy mondjuk a végeredményt egy WORD dokumentumban szeretnénk látni, akár például ügyfelenként, csapatonként, városonként, amit aztán természetesen publikálni, levélben küldeni is szeretnénk. A következő rövid kis posztban azt fogom megmutatni, hogyan tudunk a WORD szövegszerkesztőjén belülről makróval megnyitni más WORD dokumentumokat, azokban kicserélni részeket, majd újra lementeni őket - értelemszerűen a levélküldésről már volt párszor szó itt a blogon, úgyhogy az a lépés már tisztázott.

A mostani példánk első lépéseként a "mesterfájlunkba" szúrjunk be egy szimpla gombot a Ribbon Developer füle alatt található Controls szekcióból, amit aztán formázzunk meg kedvünk szerint:

Jobb gombbal kattintás után a View Code menü kiválasztásával a VBA-editorba jutunk, ahol a gombra kattintás eseményéhez fogunk egy kódot rendelni.

A már azért sokak által valószínűleg ismert Documents.Open metódussal kezdünk, amely után első és egyetlen kötelező paraméterként a FileName paramétert használjuk a fájlunk nevével és elérési útjával, ezzel fogjuk elérni a fájl megnyitását:

Ennek a metódusnak viszont van még jópár nem kötelező paramétere is, mint például a ReadOnly, ami lehetővé teszi, hogy írásvédettként nyithatjuk meg a fájlt, a Visible, ami lehetővé teszi, hogy úgy nyissuk meg a fájlt, hogy a képernyőn ne látszódjon vagy éppen a Revert, amivel azokat a helyzeteket tudjuk kezelni, ha már nyitva van egy ugyanilyen nevű fájlunk.

Ezután szimplán csak egy With...End With utasítást fogunk felhasználni, amelyen belül a Selection objektum Find tulajdonságát fogjuk paraméterezni. Alapesetben a Selection a szövegünk kijelölt részét jelenti, amennyiben pedig ilyen nincs, akkor egészen az insertion pontig (ez az a villogó karakterjel, ahol folytathatjuk gépelésünket) történő kijelölésként értelmezi. A Find tulajdonság pedig egy olyan objektumot ad vissza nekünk, amely keresésünk feltételeit tartalmazza.

Ahogy látható ebből a rövid kis kódrészletből, a Find objektumnak aztán van bőven tulajdonsága, a Text értelemszerűen megkeresi a Lafayette szócskát a szövegünkben, a Replacement.Text lecseréli ezt OfficeGurura, de hogy ez ténylegesen meg is történjen, az Execute metódust is használnunk kell, ami lefuttatja a keresési-cserélési műveletünket.

Ennek is van jópár paramétere, például a Replace paraméter azt határozza meg, hogy az összes találatot cserélje-e (wdReplaceAll) vagy sem vagy mondjuk a Forward, ami a keresést folytatja tovább a dokumentumban a következő találatig.

Aztán utolsó lépésként lementhetjük más néven a dokumentumunkat és be is zárjuk, mintha mi sem történt volna:

Amikor táblázataink, kalkulációnk, "dashboardjaink" feltuningolására gondolunk Excelben, nem kell mindig A4-es lapok sokaságát kitöltő kódsorokban gondolkodni, egy aprócska kis újítással, vizuális automatizációval is le tudjuk nyűgözni táblázatunk felhasználóit. Erre mutatok a következőben egy egyszerű példát, amely nagyjából nem más, mint egy On/Off kapcsoló és az így vezérelt szűrés egy táblázatban.

Adott tehát a táblázatunk, amelyre egy automatikus szűrést fogunk rövidesen ráhúzni:

A Ribbonunk Insert füle alatt található Illustrations szekcióból a Shapes menüpont segítségével szúrjunk be egy téglalapot:

Aztán a Format Ribbonfül alatt szépen formázzuk meg ezt a téglalapot, a keretét, a háttérszínt stb.:

Ezután ugyanúgy a Shapes menüpontból szúrjunk be egy kört:

Formázzuk meg és helyezzük el a téglalapra úgy, ahogy szeretnénk, írjunk hozzá szöveget és értelemszerűen a Shape Properties alatt állítsuk be, hogy egyéb cellák méretének állítása ne változtassa formáink méretét:

Ezután kattintsunk jobb gombbal a kis körünkre (ami innentől kezdve már gombként fog majd funkcionálni) és rendeljünk hozzá egy makrót a kattintás eseményre:

És ezzel át is jutottunk a Visual Basic Editorba, ahol elvégezzük a szükséges kódolást, hogy elérjük, amit szeretnénk:

Alapvetően elég egyszerű az a terv, amit most végre fogunk hajtani, hiszen egyrészt a gombra való kattintás esetén megvizsgáljuk, hogy van-e szűrés a táblánkon, ha nincs, értelemszerűen szűrünk és elmozdítjuk a körünket plusz megváltoztatjuk a színét. A másik irányban pedig nagyjából ennek az ellenkezőjét tesszük.

Szóval első lépésként megnézzük egy If függvénnyel, hogy az aktív sheetünkön van-e bekapcsolva Filter, mert ha nincs (FALSE), akkor értelemszerűen beállítjuk a táblánkra, majd a tartomány első oszlopában leszűrünk a megadott feltételre:

Ha ezt megtettük, akkor kell egyrészt elmozdítanunk a gombunkat, plusz megváltoztatnunk a színét.

A gombunk jelenleg default "Oval 2" néven fut, úgyhogy az ActiveSheet.Shapes.Range(Array("Oval 2")) utasítással hivatkozunk rá, a két metódus illetve tulajdonság, amit pedig használni fogunk az IncrementLeft és a Fill.Forecolor.RGB.

Előbbi a meghatározott objektumot mozgatja balra a megadott pontértékkel, utóbbi pedig az objektum színét fogja a megadottra állítani. RGB (0,0,0) a fekete színt jelenti - azaz 21.75 ponttal elcsúsztatjuk a formát és átállítjuk a színét feketére.

Ne felejtsük, hogy még mindig az If műveletben vagyunk, az előbb vizsgáltuk, hogy mi történjen ha nincs bekapcsolva semmilyen Filter, most azt nézzük meg, hogy mi történjen, ha már be van kapcsolva.

Ebben az esetben elsőként kapcsolja ki a szűrést a kattintásunk, majd mozgassa vissza a fentebb említett 21.75 pontértékkel (negatív számot adjunk tehát meg) a gombot és állítsa a színét pirosra. RGB (255,0,0) a piros színkódja.

És kész is vagyunk, próbáljuk ki működés közben:

Ahogy látható, ez egy roppant egyszerű kis példa volt, aminél biztos, hogy létezik jobb megoldás is, de remélhetőleg sokak számára ez jó kiindulási alap lehet egy jóval fejlettebb dashboard elkészítéséhez.

Ha adott két oszlopnyi számadatunk, amelyeket össze akarunk hasonlítani, akkor az Excel elég sok lehetőséget kínál számunkra, hogy ezt megtegyük. Most azokról az esetekről nem ejtek szót, amikor a célunk két oszlop duplikációnak, eltéréseinek kimutatása esetleges törlése, mert erről már volt itt poszt korábban, most egyszerűen csak azokat a lehetőségeket szeretném listába gyűjteni a teljesség igénye nélkül, amelyekkel adatokat tudunk összehasonlítani.

Az első képen látszik is, hogy konkrétan mire gondolok, a célunk annak meghatározása, hogy mely sorokban nagyobb a márciusi értékünk, mint a februári, amely eredmény aztán további elemzési lehetőségeket kínálhat - persze ez csak egy roppantul leegyszerűsített példa, a cél a lehetőségek összegyűjtése.

Szóval mi az első, amire bárki gondolna egy ilyen kérdés kapcsán?

1. IF

Igen, az IF függvény lenne az, amiről már jó sokat írtam és az Excel felhasználók túlnyomó többsége ismeri és stabilan használja is. Szimplán csak első paraméterbe bedobjuk azt a logikai tesztet, amit el akarunk végezni (jelen esetben a két oszlop összehasonlítását), majd a második illetve harmadik paraméterbe beírjuk, hogy mit tegyen a formula, ha a logikai tesztünk igaz vagy hamis eredménnyel végződik.

2. GESTEP

Többféle felhasználási módja is van a GESTEP függvénynek, ezek közül az egyik az ilyen jellegű összehasonlítások elvégzése, hiszen az első paramétereként megadott számot hasonlítja össze a második paramétereként megadott "thresholddal", azaz felső értékkel (ha ezt elhagyjuk, akkor mindig nullához hasonlít) és attól függően ad vissza egy 1-est vagy egy 0-t, hogy alatta vagy felette van-e a mi értékünk. Jelen esetben tehát egyik oszlopunk az összehasonlítandó szám, a másik a felső érték.

3. SUMPRODUCT

A kedvencem. Egy rendkívül sokoldalú tömbfüggvény (itt bővebben róla), amely összehasonlítást is lehetővé tesz. Mivel alapesetben most a célunk az, hogy 0-t vagy 1-t kapjunk vissza, ezért dupla mínuszjellel kezdjük a függvényt, ami TRUE és FALSE értékeket alakít számokká. Ezután tekintsünk el attól, hogy mi a SUMPRODUCT alapfunkciója és egyszerűen a két tömbünk összehasonlítását szúrjuk be, mint első tömbparaméter majd CTRL+SHIFT+ENTER és kész is vagyunk. Az eredmény nem olyasmi, mint amit az IF vagy a GESTEP adna vissza, de egy az egyben megtudjuk, hogy hány olyan esetünk van, ahol márciusban nagyobb az értékünk, mint februárban.

4. AND

Az AND formulával több feltételt és logikai tesztet tudunk ráengedni táblázatainkra, de itt most erről szó sincs, egyszerűen csak összehasonlítjuk a két oszlopot, mint egy sima IF és máris kapunk egy TRUE vagy FALSE értéket. Ha át akarunk váltani számokra, simán csak szorozzuk meg az eredeti AND függvényt eggyel.

5. VBA

Nagyon egyszerűen és gyorsan el tudjuk végezni az összehasonlításunkat VBA-ban is, a már itt is jópár alkalommal átbeszélt technikákra van csupán szükségünk, azaz leginkább csak egy FOR...NEXT utasításra. Első lépésként definiálunk három változót, egyet az aktív sheetünknek, kettőt pedig sima Integerként a ciklushoz szükséges számainknak.

Az aktív sheetünknek létrehozott változót értelemszerűen feltöltjük az aktív sheetünkkel, x változónkba pedig betöltjük táblánk utolsó sorának számát.

Ezután már csak a ciklusunk van hátra, azaz jelen esetünkben a harmadik sorunktól kezdve az utolsó sorunkig elvégezzük azt a vizsgálatot, hogy minden sorban összehasonlítjuk a március és február oszlopokat és amennyiben március a nagyobb, akkor egyet írunk a meghatározott (most I) oszlopba, egyébként pedig 0-t.

Ha ezt lefuttatjuk (rendelhetjük gombhoz, eseményhez, amit csak akarunk), akkor meg is kapjuk azt az eredményt, amit más úton is elértünk már.

Én eddig jutottam a lehetőségek halmazában első gondolattal, de valószínűleg csak a felszínt kapargattam. Szóval milyen egyéb lehetőségeink/módszereink vannak még, amivel ugyanezt az összehasonlítást el tudjuk végezni?