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:

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

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

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

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

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

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

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

Jelö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:

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

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

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

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

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

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

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

Jelö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:

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

Tú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.

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:

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

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

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

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

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

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

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

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

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

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

Ezután jön maga a statement, ami a következő lesz:

SELECT *

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

Union

SELECT *

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

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

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

És íme:

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

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:

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

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

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

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

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

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

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

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.

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:

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

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

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

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

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

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

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

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

Azaz most valahogy így állunk:

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

Napjaink internetének egyik rákfenéje a tömegével születő "clickbait" irományok folyama, amelyeknek fő célja a kattintások számának növelése és a látogatók becsábítása egy adott oldalra. Ezen "clickbait" cikkek egyik speciális alfaja szerintem a toplistázás - 7 dolog, amitől lefogysz, 11 dolog, amit a magyarok nem szeretnek vagy
éppen 8 film, amit látni kell. Nos, még mielőtt átmennék filozófiai elmélkedésbe, a következő posztban következzen három, kevésbé ismert Excel-funkció és lehetőség bemutatása, hiszen ilyen listás poszt már elég régen volt errefelé.

Az első ilyen lehetőség a dinamikus transpose funkciója, amelynek segítségével az oszlopokból generált soraink értékei frissülni fognak akkor is, ha az eredeti táblában változtatjuk az értéket, tehát nem kell újra transpose-t csinálnunk. Adott a következő példatábla:

Jelöljük ki és CTRL+C segítségével tegyük a vágólapra:

Ezután jön a jobb egérgombunk lenyomásával előcsalogatható Paste Special context menü használata, ahol a jobb alsó Transpose checkboxba kell pipát tennünk (ez valószínűleg senkinek nem okoz fejtörést):

Ezután, ahogy látható is lentebb, megszületett az átalakított táblánk, ahol az oszlopok már a sorok és a sorok már oszlopok az eredetihez képest.

Úgy, hogy nem vesszük még le a kijelölést, kezdjük el szimplán bevésni a Formula Barba a következőt:

=TRANSPOSE(C3:F9)

Ahol a zárójelek közé az eredeti tábla tartományát tegyük. Még mielőtt ENTERT nyomnánk, figyeljünk rá, hogy most tömbről beszélünk, így CTRL+SHIFT+ENTER lenyomására van szükségünk. És láss csodát, innentől kezdve már szépen működik is a kapcsolat a két tábla között.

Azért ennek a technikának van legalább két hátulütője, az egyik hogy a formátum széthullik, a másik pedig az, hogy innentől kezdve az új táblánkat nem tudjuk már szerkezetileg módosítani, tehát új sorokat, oszlopokat nem tudunk hozzáadni beszúrással.

A következő tipp egyértelműen az alaptudás részét kellene, hogy képezze Excel-használat esetén, de sajnálatos módon még nálam is előfordul, hogy ha sorszámokat kell egy adott oszlopban kitöltenem egymás után, akkor egyszerűen csak elkezdem húzni lefelé és kész. Na de mi van, ha nagyon hosszú sorozatról beszélünk?

Itt lesz segítségünkre a Ribbonunk Home füle alatt található Editing szekció Fill menücsoportja:

Itt ugyanis van egy Series funkció, amire ha rákattintunk, már adja is magát a megoldás:

Szimplán csak állítsuk be, hogy sorokról vagy oszlopokról beszélünk, milyen jellegű kitöltést akarunk és mi legyen a kezdő és záróérték, és máris kész vagyunk.

Az utolsó tipphez biztos sokaknak van saját élménye is, hiszen kivel nem fordult még elő, hogy a szépen otthonosra customizált Office-programjait egy gépcsere, egy új installáció miatt elveszítette és utána hetekbe telt visszahozni az eredeti állapotot, például a Quick Access Toolbar esetén, ahova gyakran használt funkcióinkat, saját makróinkat pakoltuk korábban.

Egyszerűen csak kattintsunk jobb gombbal bárhova a Toolbaron és válasszuk a Customize menüpontot:

Az itt felugró ablakot már ismerjük, itt lehet hozzápakolni mindenféle funkciókat a toolbarhoz, ezúttal azonban a jobb sarokra kell fókuszálnunk, ahol van egy Export/Import gomb:

És innentől kezdve már ki is menthetjük majd később visszaimportálhatjuk QAT-re vonatkozó beállításainkat:

A burn down chart egy olyan diagram, ami általánosságban azt mutatja meg, hogy mennyi feladat van még hátra egy adott időperióduson belül, azaz mondjuk egy projekt esetén az x tengelyen jelenítjük meg az időintervallumot, az y tengelyen pedig a megoldandó problémák számát. Például a jelenleg hátralévő feladatainkra készíthetünk egy becslést, hogy nagyjából milyen ütemben haladunk és ez alapján meg tudjuk becsülni a befejezés időpontját - erre pedig tudunk majd tenni egy tényleges haladási sebességet, szóval összességében egy ilyen burn down chart rendkívül egyszerű módon mutatja meg projektünk alakulását. Nézzük is meg két rövid példán!

A következő példatábla egy adott időpillanatban elvárt és tényleges problémaszámokat mutatja egy projekten belül:

Ha most szimplán kijelöljük ezt a táblát és az Insert ribbonfül Charts szekciójából beszúrunk egy kétdimenziós vonaldiagramot, akkor máris láthatunk egy (nem is annyira) kezdetleges verziót egy burn down chartra:

Ezzel az egyszerű példával szerint nagyjából mindenki számára egyértelmű válhatott, hogy mi is az a burn down chart, mindenesetre az biztos, hogy jóval több adatpont esetén jóval hatásosabb és jóval nagyobb létjogosultsága van egy ilyen diagramnak.

Vagy csak képzeljünk el egy olyan helyzetet, amikor tudjuk, hogy a mai napon van még 15 megoldandó projektes feladatunk és a cél, hogy ez nulla legyen mondjuk december elsején. És ehhez nincs adott időpontra vonatkozó elvárt eredményünk, csak a kiinduló állapotot, a célt és a chart bemutatásának idején fennálló státuszt akarjuk prezentálni. Mint mondjuk ez a mintatábla:

Ebben a formában úgy tűnhet, hogy nem tudunk beszúrni rá egy kétdimenziós vonaldiagramot, mert az nem fogja azt mutatni, amit szeretnénk. Vagyis úgy tűnik, mintha nem azt mutatná, hiszen ha kijelöljük a táblát és az Insert ribbonfül Charts szekciójából beszúrjuk a kétdimenziós vonaldiagramot, akkor ezt látjuk:

Innen viszont már csak egyetlen lépés hiányzik a végcélhoz - kattintsunk jobb gombbal a chart területén, majd válasszuk a Select Data opciót:

Itt válasszuk a bal sarokban a Hidden and Empty Cells gombot és már adja is magát a megoldás:

Szimplán csak pipáljuk be a Connect data points with line rádiógombot és láthatjuk is az eredményt:

Hasonlít az előző példához, de célját és a megmutatandó sátuszt tekintve mégis más.

Szóval nagyon röviden, nagyon egyszerű bemutatással ilyen lenne egy burn down chart, amelyre lehet jókat és rosszakat is mondani, de ismerni sosem árt. Egyébként alapvetően az egyik leggyakoribb kritika, ami az ilyen burn down chartot éri, az az, hogy a projekt vezetője vagy a csapat nagyjából olyan időbecslést ad, amilyet akar a köztes időpontokra, tehát a projekt végéig lehet olyan kimutatást összehozni, ami alapján a csapat végig túlteljesít, miközben valójában nem is. Erre szoktak beletenni felülvizsgálati pontokat, amikor újraértékelésre kerülnek az eredetileg meghatározott időpontok.

Az Excelnél eléggé hozzászokhattunk már ahhoz a csodálatos lehetőséghez, hogy a legtöbb problémára nem csak egy, de több megoldás és válasz létezik, nincs ez másként annál az igénynél sem, amikor mondjuk egy legördülő menüből kiválasztott elemhez tartozó chartot akarjuk éppen megjeleníteni. Egy ilyen esetnél a legegyszerűbb megoldás az újabb Excel-verziókban a Slicer használata, amiről már elég sokat beszéltünk is itt korábban - de ha valaki nem preferálja ezt (vannak azért formázást illetően bizonyos korlátai), akkor igazán váratlan helyről, a Camera eszköztől is jöhet támogatás. Adott csak a példa kedvéért az alábbi két chart és táblázat:

Az egyik havi, a másik negyedéves adatokat tartalmaz, a célunk pedig az, hogy egy dashboardban a user által egy legördülő menüből kiválasztott opciónak megfelelő diagramot jelenítse meg. Első lépésünk most az lesz, hogy egy másik sheeten vagy az adott dashboardon létrehozzuk a legördülő menüt a Data ribbonfül Data Tool szekciójának Data Validation funkciójával, hogy megadjuk a usernek a választási lehetőséget:

Ezután jön a kulcsmomentum a feladatban, amikor is a Formulas ribbonfül Defined Names szekciójából elindítjuk a Name Managert és létrehozunk három nevesített tartományt. Az első tartományunk az első chartot tartalmazó cellák halmaza, a második tartományunk pedig a második chartot tartalmazó cellák halmaza lesz, ahogy a lenti képen is látható például a Negyedéves adatok esetén:

Vegyük észre, hogy a Negyedéves nevesített tartományunk pontosan azon cellák halmaza, amelyeken a Negyedéves diagramunk szerepel. Ha ez megvan, akkor létrehozzuk a harmadik nevesített tartományt is, a mi esetünkben ennek neve a "Nézet" lesz és a legördülő menüt tartalmazó cellára hivatkozik egy INDIRECT függvénnyel megspékelve. Az INDIRECT abban segít majd nekünk, hogy a meghivatkozott F1 cella értéke (tehát jelen esetben a "HAVI" vagy "NEGYEDÉVES" opciók alapján meg fogja keresni és vissza fogja adni az ezen a néven futó tartományt.

Tehát még egyszer, a következő három nevesített tartományra van szükségünk:

Következő lépésként, ha még nem tettük meg, az Excel Options Quick Access Toolbar menüjében adjuk hozzá a QAT-hez a Camera Toolt a lentieknek megfelelően, mert ez az egyetlen esélyünk, hogy ehhez a funkcióhoz hozzáférjünk:

Ezután kattintsunk szépen a QAT-n megjelent kamera ikonra, majd nagyjából oda, ahol szeretnénk megjeleníteni a chartjainkat, csináljunk egy fényképet. Ez valahogy most így fog megjelenni, a lefényképezett cellát fogja nekünk mutatni első körben:

Nincs más hátra, mint ezt a fényképet a Formula baron egyenlővé tenni a "Nézet" nevű nevesített tartománnyal és máris működik a dolog:

Nagy Excel-fájlokkal való ügyködés, azok tárolása és mozgatása kapcsán elég gyakran felmerülő tanács a "Mentsd le xlsb-be!" javaslat, amely aztán sokakban félelmet is kelt, hogy nem fogja-e ez a formátum képletek, formázások vagy éppen makrók elvesztését okozni. És bár nincs új a nap alatt, az internet korában meg főleg, azért a következő posztban kicsit körbejárom az XLSB formátum előnyeit, hátrányait és megpróbálom eloszlatni a bináris fájllal szembeni ellenérzéseket is. Alapvetően egy XLSX valójában tömörített, összecsomagolt XML szövegfájlokat tartalmaz, (amelyeket egyébként meg is tudunk nézni, ha átnevezzük a kiterjesztést mondjuk .zip-re), míg az XLSB a régi iskola gyermeke, azaz egy egyszerű bináris fájl, mint a régi szép időkben az XLS volt. És ebből adódik a legnagyobb előnye is, a mérete - egyértelműen és észrevehetően kevesebb helyet foglal a merevlemezünkön, a megtakarítás elérheti akár a 10-20%-ot is, tehát értelemszerűen az XLSB-nek kisméretű fájlok esetén nincs igazából létjogosultsága (sőt, van rá példa, hogy bizonyos méret alatt az XLSB nagyobb méretű lesz, mint az eredeti fájl).

Hacsak nem iszonyatosan hosszú formulákat alkalmazunk, mert a 8192-es karakterszámos limitet az XLSB esetén figyelmen kívül hagyhatjuk - egy XLSX nem fogja támogatni az ennél hosszabb függvényeinket. És ami a legjobb, hogy a mérete mellett a megnyitási sebessége is sokkal gyorsabb, köszönhetően annak a ténynek, hogy egyszerűen bináris adatokat tölt be az Excel, XML fájlok lefordítása helyett - a megnyitási idő akár 50%-kal is csökkenhet. Persze, ha minden ilyen szép és jó volna, akkor feltehetnénk a kérdést, hogy mi szükség van egyáltalán XLSX-re és XLSB-re - nos a legnagyobb probléma az utóbbi bináris formátummal az, hogy az XLSB nem kompatibilis az Excel korábbi verzióival (2007 előttiekre gondoljunk) és más táblázatkezelőkkel (mint például az OpenOffice).

Ezt az alapvetően tényleg figyelemre érdemes hátrányt leszámítva a többi kellemetlenség igazából nem is lényeges, hiszen az, hogy az Excel Ribbont nem tudjuk customizálni/módosítani (tehát először visszamegyünk XLSX formátumra, majd a módosítás után ismét XLSB-be) vagy hogy XLSB megnyitásánál nem tudjuk, hogy az adott fájl tartalmaz-e makrókat - ezek olyan kellemetlenségek, amelyek nem összemérhetőek a helytakarékossági szempontokkal (és azzal se törődjünk nagyon, hogy XLSB-t az Excel összeomlása után egy kicsit nehezebben tudunk helyreállítani, mint XLSX formátumú fájlokat).

Ettől függetlenül persze a felesleges sorok törlésével, felesleges formátumok elhagyásával, az automatikus kalkuláció kikapcsolásával és egyáltalán, felesleges adatok generálásának elkerülésével jelentős megtakarítást érhetünk el anélkül is, hogy formátumok között váltogatunk. És nem, nem igazak azok a feltételezések sem, miszerint az XLSB-ben gyorsabban lehet dolgozni vagy éppen nem működnek bizonyos funkciók, mert erről szó sincs, használjuk bátran, ha szükséges. Arra azért figyeljünk, hogy ha nagy bőszen elkezdünk XLSB fájlokat generálni, tájékoztassuk erről felhasználóinkat, kollégáinkat is, mert a tapasztalatok azt mutatják, hogy alapvetően óvatosságot szül a formátumot nem ismerőkben, ha egy XLSB fájlt kapnak levélben vagy találnak a folderükben.

A mai rövid poszt két, Excel munkafüzet védettségével kapcsolatos témát fog feszegetni, remélhetőleg úgy, hogy az legalább egy-két olvasó számára hasznosnak bizonyul majd.

Az első témát egy munkalap levédésével kezdjük, ahogy a lenti képen is látható, még a cellák kijelölésének lehetőségétől is megfosztjuk a kedves felhasználót:

Így pedig, amikor ez egy másik felhasználó kezébe kerül, akkor szegény nagyon nem tud mit csinálni vele, akár képet is kaphatott volna:

Értelemszerűen törölni vagy hozzáadni nem lehet, de miután a cellák kijelölését is beállítottuk a levédésnél, így még másolni sem lehet az adatokat további felhasználás céljából. Legalábbis ezt hiszi az eredeti fájl levédője. Ugyanis bár egérrel a kijelölés nem fog menni, a bal sarokban található Name Box használatával bármilyen tartomány vagy cella kijelölhető, szimplán csak írjuk be a tartományt és nyomjunk egy Entert:

Ezután vagy az egér jobbgombos Context menüjével vagy CTRL+C lenyomásával, de már be tudjuk rakni a vágólapra a kívánt adatokat:

Majd be is tudjuk illeszteni, ahova csak akarjuk:

Szolgáljon tehát ez tanulságként azért, hogy a cellák kijelölésének lehetőségétől is megfosztani a felhasználókat, eléggé radikális módszer, csak kivételes esetekben alkalmazzuk.

A másik apró tanács a Protection kérdését egy másik irányból közelíti meg, azaz néhány mondatban azt fogom leírni, hogy tudjuk megakadályozni, hogy felhasználóink mozgassák sheetjeinket, átnevezzék őket stb. Rengeteg Exceles anyagban, könyvben, tipplistában elő szokott ez fordulni, mégis azt tapasztalom, hogy elég sokszor merül fel a kérdés, "hogy tudnám lockolni a munkalap nevét".

A megoldást a Review ribbonfül Changes szekciója rejti, itt bújik meg ugyanis a Protect Workbook lehetősége, amely pont ezt az igényt fogja teljesíteni.

Egyszerűen kattintsunk rá és máris látni fogjuk, hogy mennyire egyszerű az egész:

Egyik tanács sem nevezhető világmegváltó újdonságnak, de remélhetőleg egy reggeli kávé mellé tökéletes napindító lehet annak, aki még nem ismerte őket.

Valószínűleg eme blog látogatóinak többsége elég jól ismeri az AND (a paramétereiként megadott argumentumokat vizsgálja meg és ha mindegyik teljesül, akkor TRUE-t ad vissza, minden más esetben FALSE értéket), OR (csak akkor ad vissza FALSE értéket, ha mindegyik paramétereként megadott argumentum FALSE) és NOT (TRUE-t FALSE-ra állít és fordítva) formulákat és már valószínűleg ezek használata sem túlságosan gyakori, hiszen egy IF és legrosszabb esetben egy IF-OR kombinációval elég sok fejtörő megoldható - így aztán jómagam egyetlen egyszer sem láttam éles, mindennapi használatban a jobb sorsra is érdemes XOR függvényt.

Definíciója szerint a XOR függvény a "kizáró OR" végrehajtására szolgál, ami gyökeres ellentéte az OR-nak, ami egy "diszjunkciós OR" formula. Utóbbi mindig TRUE-t ad, ha bármelyik argumentum TRUE és csak akkor ad FALSE eredményt, ha mindegyik argumentum FALSE, míg a XOR kizárólag abban és csakis abban az esetben ad TRUE eredményt, ha csak egyetlen érték TRUE, ha két TRUE és két FALSE van (két logikai érték esetén), akkor FALSE az eredmény. Kettőnél több argumentum esetén a XOR csak akkor ad vissza TRUE értéket, ha a TRUE értékek száma páratlan, páros számnál az eredmény FALSE.

Alapvetően Excelben nem igazán elterjedt és gyakori a használata, semmiképp annyira mint matematikában vagy az információelméletben hibák felismerésére. Utóbbinál előkerülhet a paritásellenőrzés kérdése is, amely hálózaton át küldött adatok sértetlen megérkezésének validációjánál merülhet fel - az elküldendő adatokat bitek blokkjaira bontjuk, majd a blokkokban lévő 1 értékű biteket megszámoljuk és így meghatározzuk a "paritásbit" értékét. Ha a bitek száma páros, akkor a paritásbit értéke 0, ha páratlan akkor 1 (páros paritás esetén). Na ez a XOR egyik tipikus mintapéldája.

És hogy milyen esetekben lehet használni a XOR függvényt? Például képzeljük el, hogy boltok esetében azt akarjuk megvizsgálni, hogy tartották magukat egyetlen beszállítóhoz és nem váltogatták-e nap-nap után őket, akkor erre például jó lehet a XOR:

De azért gyorsan belátható, hogy nincs igazi létjogosultsága ennek a függvénynek, hiszen a fenti példa esetében még az AND/OR/IF/NOT végtelen számú kombinációja helyett is egyszerűbb, ha szimplán egy =L4<>M4 összehasonlítást hajtunk végre mondjuk az első sornál. Több argumentumnál persze az AND/OR/NOT/IF nagyjából bármeddig folytatható egymásba ágyazása fog segíteni, de igazából ilyen jellegű esetet nehezen tudok elképzelni Excelben - viszont erre például már tökéletes lenne a XOR.

Kedves Olvasó, tudsz olyan valódi életből vett példát Excelben, ahol a XOR a legjobb megoldás?