Telefonszámunk: 1-472-0679

Tartományok Kezelése

Tartománynevek elrejtése – VBA

A professzionális Excel felhasználók gyakran dolgoznak névtartományokkal. Ez ugyebár azt jelenti, hogy egy tartományt elnevezünk, és a képletekben a tartomány címe helyett a nevét használjuk. A tartományok elnevezését a szerkesztőléc bal oldalán lévő Név mezőben vagy a Képletek szalagon található Névkezelőben lehet megadni. A Dinamikus névtartományokról olvashatsz […]

Tovább...

Igazítás a kijelölés közepére – Makróval

Egy korábbi anyagban arról volt szó, hogy ha lehetséges, kerüljük a cella egyesítést (Merge Cells), és helyette inkább a kijelölés közepére igazítást (Center Across Selection) használjuk.

kijeloles-kozepere

Mivel ennek a menüpontnak az elérése több kattintást igényel, így érdemes lehet a PERSONAL makrómunkafüzetbe tenni egy olyan makrót, ami elvégzi az igazítást.

Az egyéni makró munkafüzet (PERSONAL.XLS(B)

Az egyéni makró munkafüzetről egy külön oktatóanyagban van szó. Röviden, ez egy olyan speciális makrófájl, ami ha létezik, akkor az Excel indításakor automatikusan megnyílik, de rejtett ablakban. A benne lévő makrók pedig láthatóvá, használhatóvá válnak. Kisebb segéd makrókat szoktunk bele tenni, amik felgyorsítanak egyes műveleteket, és hagyományos módon csak körülményesen tudnánk megcsinálni. Ilyen például egy cella tartalmának a kijelölés közepére igazítása.

Lépjünk át a Visual Basic Editor felületére (ALT+F11)

Ha nem látszódik, Kapcsoljuk be a Project Explorer ablakot (Ctrl+R). Ha még nem létezik a PERSONAL makrófüzet, akkor létre kell hozni az említett tutorial alapján.

A PERSONAL makrófüzetbe illesszük be a következő kódot.

1

Használata

  1. Egy cellába beírjuk a szöveget, majd kijelöljük azt a tartományt, amin belül középre szeretnénk igazítani.
    kijeloles-kozepere-1
  2. Lefuttatjuk a makrót. Rendelhetünk hozzá billentyűkombinációt, vagy gyors elérési eszköztár gombot is . Nem egyesíti a cellákat, mégis középen látszik.
    kijeloles-kozepere-2

Az egész folyamatot megtekinthetjük a VIDEÓ anyagban

  • Igazítás a kijelölés közepére makróval

Tovább...

Adatok módosítása szűrt tartományban – makró -VBA

Gyakori feladat, hogy egy szűrt táblázatban az egyik oszlopban az adatokat teljesen vagy részben másikra szeretnénk cserélni. Ezt kézi szerkesztéssel nagyon egyszerű megtenni, de ha a feladatot makró kódon belül szeretnénk megoldani (mert pl. egy nagyobb feladat része), akkor már nem annyira egyértelmű a helyzet.

Volt már egy korábbi anyag arról, hogy ha szűrt tartományba szeretnénk a vágólapról beilleszteni adatokat, de most nem erről van szó, hanem egyszerű felülírásról.

szurt_tartomany

Kézi módszer

Ez nagyon egyszerű, hiszen már jópár verzió óta az Excel kiválóan kezeli ezt  a problémát.

  1. Leszűrjük a táblát
  2. A módosítandó oszlopban az első elemet átírjuk
  3. Lehúzzuk az aljáig

Ha megszüntetjük a szűrést, akkor látni fogjuk, hogy csak az előzőleg szűrt adatok módosultak, a közbensők, amik a szűréskor rejtve voltak, nem változtak.

Makró – VBA módszer

Ha az előző műveletsort rögzítjük makróval, akkor a kódból nagyon gyorsan kiderül, hogy általánosan nem használható, tehát változó adattartalom esetén, ahol a szűrés más-más eredményt adna. Emiatt ezt a problémát kicsit komolyabban kell leprogramozni.

Elméleti háttér

Ha egy oszlop szűrt adatokat mutat, és abban módosítani szeretnénk, akkor előbb ki kell jelölni CSAK a látható cellákat.

  1. Kijelöljük a teljes, szűrt táblázatot (Ctl-A)
  2. Speciális kijelölés -> Csak a látható cellák

Látható cellák kijelölése

szurt_tablazat

Így viszont az oszlopban lévő adatok szétválnak egymástól, és ha újra kinyitjuk, azaz megszüntetjük a szűrést, akkor több, nem összefüggő tartományt fogunk látni.

Szurt-adatok-kijeloles-nem-osszefuggo

Tehát a makróval a kategória oszlopban lévő, látható tartományokat kell módosítani. Példánkban a “Beverages” szót, másik kategória névvel.

A Selection objektum

Ha több, nem összefüggő tartományt jelölünk ki, vagy hozunk létre a fenti módszerrel, attól az még a SELECTION objektummal lefedhető, és egy lépésben átírható a benne lévő összes cella értéke.

A Fejléc problémája

Mivel előre nem tudjuk, hogy milyen sorok fognak beleesni a szűrésbe, legegyszerűbb, ha a fejlécet is belevesszük a kijelölésbe. Viszont így annak az értéke is felülíródik. Ezt úgy oldjuk meg, hogy a felülírás előtt elmentjük a fejléc eredeti értékét, majd a végén visszaírjuk.

A VBA kód

A példában egy általánosabb megoldást adok, ahol a subrutin argumentumként kapja meg azt, hogy melyik oszlopban kell dolgoznia, és mire kell cserélni a szűrt elemeket.

A futás előtt feltételezi, hogy már a szűrés megtörtént, és az aktív cella a módosítandó oszlop fejlécén áll.

1

Letölthető gyakorló munkafüzet:  szurt_tartomany_modositasa

Az egészet működés közben, magyarázattal együtt láthatod a videóban

  • Szűrt adatok módosítása makróval

Tovább...

Az összes képlet átalakítása értékké egy kattintással

Gyakran felmerül az igény, hogy egy munkalapon a képleteket tartalmazó oszlopokat értékké alakítsuk. Ennek több oka is lehet:

  • Nem szeretnénk, hogy lássák a képleteket
  • Az értékké alakítással gyorsítjuk a működést, mert az Excel-nek nem kell állandóan újraszámolni  a képleteket

A probléma

A másolás / érték beillesztés műveletet mindenki ismeri. A baj az vele, hogy csak egy, összefüggő tartományban működik. Hiába jelölünk ki több tartományt pl. a Ctrl lenyomása mellett. A másolás is csak akkor működik, ha azonos a kijelölt területek sorainak a száma. De saját magára értékként nem lehet beilleszteni két egymással nem összefüggő területet.

Tobbszoros_kijeloles_ertek_beillesztes

A megoldás

A megoldás egy pár soros makró, ami kijelöli a képleteket tartalmazó cellákat (sorokat/oszlopokat), majd egy ciklussal végiglépked a kijelöléskor képződött területeken, és mindegyiket értékké alakítja.

1

A fenti makrót másoljuk egy makró-munkafüzet egyik  moduljába, vagy a Personal (Egyéni) makró munkafüzet ,megfelelő moduljába, és máris kipróbálhatjuk.

A kapcsolódó videóban láthatjuk a folyamatot és a működést is. Ezen kívül mi van még a videóban?

  • Speciális kijelölés lehetőségei
  • Képleteket tartalmazó cellák kijelölése
  • Egyéni makró munkafüzet létrehozása (ha még nincs)
  • Makró elhelyezése az egyéni makró munkafüzetben
  • Eszköztár gomb rendelése a makróhoz

Tovább...

Az aktív cella táblázatban van-e, és melyikben? – VBA

A makrófejlesztés egyik fontos része a “Bolond állóság” (FoolProof) biztosítása. Emiatt egy igényesebb VBA kód hibaellenőrzéseket is tartalmaz.

Az egyik ilyen eset lehet, hogy ellenőrizzük, az aktív cella egy korábban már táblázattá (Lista) alakított tartományban van-e?

Íme egy erre szolgáló függvény

1

Úgy működik, hogy ha az aktív cella benne áll egy táblázatban (listában), akkor létezik a .name tulajdonsága. Különben hibát ad, amit kezelünk.

A függvény a táblázat nevét, vagy üres stringet ad vissza.

Tovább...

Beillesztés szűrt táblázatba – VBA

Az előző részben azt mutattam meg, hogy hogyan lehet egy szűrt, azaz rejtett sorokat is tartalmazó tartományba adatokat beilleszteni makró használata nélkül.

Arra jutottunk, hogy ez csak akkor lehetséges, és úgy sem egyszerűen, ha ugyanazon szűrt táblázat egyik oszlopából szeretnénk másolni adatokat egy másik oszlopba. Mivel egy táblában vannak garantált, hogy a szűrés hatására mindkét oszlopban ugyanazok a sorok leszek rejtve.

szurt-taomany-masolas

A példában a szűrés után a C oszlop adatait másoljuk a B oszlopba.

De mi van akkor, ha a másolandó és a céltartomány nem ugyanabban a táblázatban van?

Esetleg másik lapon, ami akár nem is szűrt?

Ez esetben makrót kell használni. Szerencsétek van, mert elkészítettem, és itt közzéteszem a programot. Futtatáskor csak ki kell jelölni a forrás tartományt, majd a céltartomány első celláját, és a többit rábízni a makróra.

A makró használatát és működését Videóban is megmutatom.

A makró kódja

1

Letölthető munkafüzet: beillesztes-szurt-tartomanyba

  • Másolás szűrt tartományba makróval

Tovább...

Üres stringet tartalmazó cellák kijelölése vagy törlése

Ha egy tartományban képlettel számolunk, akkor nem tudunk visszaadni üres cellát. Leggyakrabban nullát, vagy üres stringet adunk vissza, amit az Excel másképp kezel bizonyos esetekben, mint az üres cellát.

A példában, ha az A1-ben nulla van, akkor üres stringet, különben 1-et adunk vissza:

=IF(A1=0,"",1)

Tegyük fel, hogy ez a képlet sok soron keresztül megy, és a végén grafikont kell belőle rajzolni. A problémát az okozza, hoyg az Excel grafikon motorja csak a valóban üres cellákat tekinti annak, és azokat nem rajzolja ki  a diagramra, de az üres stringet nullának tekinti, és kirajzolja.

Egyik megoldás az lehet, hogy a módosítjuk a képletet, és az üres string helyett hibát adunk, amit szintén figyelmen kívül hagy a grafikon.

=IF(A1=0,NA(),1)

Az NA() függvény a #N/A eredményt adja. Magyar megfelelője a HIÁNYZIK() függvény.

Másik lehetőség, hogy kijelöljük az oszlopot, sé egy kis makróval töröljük az összes üres stringet tartalmazó cella tartalmát.

Üres stringeket tartalmazó cellák kiürítése VBA kóddal

1 Tovább...

Számtartomány elemeinek szorzása konstanssal

Ha Excelben egy számtartomány minden értékét szeretnénk szorozni egy adott konstans értékkel, akkor a következő lehetőségeink vannak:

  1. Egy segédoszlopban mellette elvégezzük a számítást, majd értékként visszatesszük az eredményt az eredetire
  2. Speciális vágólap beillesztéssel, szorzás műveletet választva
  3. Alkalmazzuk a következő makrót, ami a bekért számmal szorozza az előzőleg kijelölt tartomány számait.
1

Mindez hogy működik, és hogy kell használni?

Kiderül a kapcsolódó videóból

Letölthető munkafüzet: tartomány-szorzas-konstanssal

  • Tartomány szorzása konstanssal

Tovább...

Érték beillesztése az összes képletre hipergyorsan VBA segítségével

Ha csak egy darab összefüggő tartományra szeretnénk a képleteket értékükkel helyettesíteni, akkor azt viszonylag gyorsan meg tudjuk csinálni az EXCEL által adott speciális beillesztési lehetőséggel.

De mi van akkor, ha egy munkalapon az összes képletet ki szeretnénk cserélni értékre? Hagyományos módszerrel egyenként kell megcsinálni. De ha az alábbi kis kódot elraktározzuk magunknak pl. a PERSONAL makrófüzetbe, akkor bármelyik dokumentumunknál nagyon gyorsan megtehetjük a cserét.

Háttér

Az Excel a speciális kijelölések segítségével megadja a módját, hogy az összes, képletet tartalmazó cellát egy lépésben kijelöljük. Ezt az Excel felületen így kell:

  1. F5
  2. Special… (Irányított…) gomb
  3. Képletek

Ugyanezt VBA kóddal

Selection.SpecialCells(xlCellTypeFormulas, 23).Select

Képletek cseréje nem összefüggő tartományoknál

Nagyon valószínű, hogy nem csak egy tartományban lesznek képletek. Így több, nem összefüggő tartományt jelöl ki az Excel. Ezekre viszont NEM LEHET értéket beilleszteni az Excel felületről.

Megoldás

A nem összefüggő tartományokat egy VBA ciklusban bejárva, egyesével cseréljük ki bennük a képleteket értékre.

A nem összefüggő tartományok bejárásának leírását lásd itt.

1 Tovább...

Nem összefüggő kijelölések bejárása VBA-ban

Az Excel használata közben többféleképpen keletkezhetnek nem összefüggő tartományok:

  • Mi jelöljük ki a Ctrl billentyűvel
  • Szűréssel keletkeznek
  • Speciális kijelölést használunk, pl.: Az összes képletet tartalmazó cella kijelölése
  • VBA kódból jelölünk ki nem összefüggően (lásd itt)
  • Egyéb ?

Akármilyen módon is keletkeztek a tartományok, a kezelésük már nem annyira egyszerű, sőt, bizonyos műveletek nem megengedettek ilyen kijelöléskor. Például nem lehet beilleszteni értékeket a vágólapról.

Elméleti háttér

Ha több tartományt jelölünk ki, akkor a kijelölésen (Selection) belül létrejön egy új kollekció “AREAS” (területek) néven. Próbáljuk ki, hogy kijelölünk néhány nem összefüggő tartományt CTRL lenyomásával, majd az Immedate ablakba beirjuk:

?Selection.areas.count

Válaszként megkapjuk, hogy hány tartományt jelöltünk ki. A képen látható, hogy három tartomány kijelölése után milyen választ kaptunk.

A gyűjtemény egyes területeire a SELECTION.AREAS(n) módon lehet hivatkozni.

A területek (areas) bejárása

Mivel ez is egy kollekció, a bejárásához legegyszerűbben egy for each ciklust lehet használni. A példában a kijelölt területek celláinak a számát írjuk ki egy üzenőboxba. A kód futtatása előtt legyen kijelölve legalább kettő, de jobb, ha több tartomány.

1 Tovább...