tartalom
Az Excel erőteljes, de ugyanakkor nagyon ritkán használt képességgel rendelkezik, hogy makrók segítségével automatikus műveletsorozatokat hozzon létre. A makró ideális megoldás, ha ugyanazzal a feladattípussal foglalkozik, amely sokszor megismétlődik. Például adatfeldolgozás vagy dokumentum formázás szabványosított sablon szerint. Ebben az esetben nincs szükség programozási nyelvek ismeretére.
Kíváncsi vagy már, hogy mi az a makró, és hogyan működik? Akkor bátran folytassa – akkor lépésről lépésre elvégezzük veled a makró létrehozásának teljes folyamatát.
Mi az a Makró?
A Microsoft Office makrója (igen, ez a funkció a Microsoft Office csomag számos alkalmazásában ugyanúgy működik) egy programozási nyelv programkódja Visual Basic alkalmazásokhoz (VBA) a dokumentumban tárolva. Az egyértelműség kedvéért egy Microsoft Office dokumentumot össze lehet hasonlítani egy HTML oldallal, akkor a makró a Javascript analógja. Amit a Javascript képes a weboldal HTML-adataival csinálni, az nagyon hasonló ahhoz, amit a makró képes a Microsoft Office-dokumentumokban lévő adatokkal.
A makrók szinte bármit megtehetnek a dokumentumban. Íme néhány közülük (egy nagyon kis rész):
- Stílusok és formázások alkalmazása.
- Különféle műveletek végrehajtása numerikus és szöveges adatokkal.
- Használjon külső adatforrásokat (adatbázisfájlok, szöveges dokumentumok stb.)
- Hozzon létre egy új dokumentumot.
- Végezze el a fentieket bármilyen kombinációban.
Makró létrehozása – gyakorlati példa
Vegyük például a leggyakoribb fájlt CSV. Ez egy egyszerű, 10×20-as táblázat 0-tól 100-ig terjedő számokkal, oszlopok és sorok fejlécével. Az a feladatunk, hogy ezt az adathalmazt egy jól formázott táblázattá alakítsuk, és minden sorban összesítést generáljunk.
Mint már említettük, a makró a VBA programozási nyelven írt kód. De az Excelben kódsor írása nélkül is létrehozhatunk programot, amit most meg is teszünk.
Makró létrehozásához nyissa meg Megnézem (Típus) > makrók (Makró) > Makró rögzítése (Makró felvétel…)
Adjon nevet a makrónak (szóközök nélkül), és kattintson OK.
Ettől a pillanattól kezdve a dokumentummal végzett ÖSSZES művelet rögzítésre kerül: a cellák módosítása, a táblázat görgetése, sőt az ablak átméretezése is.
Az Excel két helyen jelzi, hogy a makró rögzítési mód engedélyezve van. Először is a menüben makrók (Makrók) – karakterlánc helyett Makró rögzítése (Makró rögzítése…) sor jelent meg Állítsa le a felvételt (Felvétel leállítása).
Másodszor, az Excel ablak bal alsó sarkában. Ikon megáll (kis négyzet) azt jelzi, hogy a makró rögzítési mód engedélyezve van. Ha rákattint, leáll a felvétel. Ellenkező esetben, ha a rögzítési mód nincs engedélyezve, egy ikon található a makrórögzítés engedélyezéséhez ezen a helyen. Ha rákattint, az ugyanazt az eredményt kapja, mint a felvétel menüben történő bekapcsolása.
Most, hogy a makró rögzítési mód engedélyezve van, térjünk rá a feladatunkra. Először is adjunk hozzá fejléceket az összesítő adatokhoz.
Ezután írja be a képleteket a cellákba a címsorok nevének megfelelően (az angol nyelvű képletek és az Excel verziók változatai vannak megadva, a cellacímek mindig latin betűk és számok):
- =SZUM(B2:K2) or =SZUM(B2:K2)
- =ÁTLAG(B2:K2) or =СРЗНАЧ(B2:K2)
- =MIN(B2:K2) or =MIN(B2:K2)
- =MAX(B2:K2) or =MAX(B2:K2)
- =MEDIÁN(B2:K2) or =MEDIÁN(B2:K2)
Most jelölje ki a képletekkel ellátott cellákat, és az automatikus kitöltés fogantyújának húzásával másolja a táblázatunk összes sorába.
Miután befejezte ezt a lépést, minden sornak rendelkeznie kell a megfelelő összeggel.
Ezután összefoglaljuk a teljes táblázat eredményeit, ehhez még néhány matematikai műveletet végzünk:
Illetőleg:
- =SZUM(L2:L21) or =SZUM(L2:L21)
- =ÁTLAG(B2:K21) or =СРЗНАЧ(B2:K21) – ennek az értéknek a kiszámításához pontosan a táblázat kezdőadatait kell venni. Ha az egyes sorok átlagainak átlagát vesszük, az eredmény más lesz.
- =MIN(N2:N21) or =MIN(N2:N21)
- =MAX(O2:O21) or =MAX(O2:O21)
- =MEDIÁN(B2:K21) or =MEDIÁN(B2:K21) – fontolóra vesszük a táblázat kiindulási adatainak felhasználását, a fent jelzett okból.
Most, hogy végeztünk a számításokkal, végezzünk néhány formázást. Először állítsuk be ugyanazt az adatmegjelenítési formátumot minden cellához. Jelölje ki az összes cellát a lapon, ehhez használja a billentyűparancsot Ctrl + Avagy kattintson az ikonra Jelölje ki az összes, amely a sor- és oszlopfejlécek metszéspontjában található. Ezután kattintson Vesszőstílus (Határozott formátum) fülre Kezdőlap (Itthon).
Ezután módosítsa az oszlop- és sorfejlécek megjelenését:
- Félkövér betűstílus.
- Középre igazítás.
- Színes kitöltés.
Végül pedig állítsuk be az összegek formátumát.
Így kell kinéznie a végén:
Ha minden megfelel Önnek, állítsa le a makró rögzítését.
Gratulálunk! Ön most rögzítette az első makrót Excelben.
A generált makró használatához el kell mentenünk az Excel dokumentumot olyan formátumban, amely támogatja a makrókat. Először is törölnünk kell minden adatot az általunk készített táblából, azaz üres sablont kell készítenünk. Az a helyzet, hogy a jövőben ezzel a sablonnal dolgozva a legfrissebb és releváns adatokat importáljuk bele.
Az összes cella adatból való törléséhez kattintson a jobb gombbal az ikonra Jelölje ki az összes, amely a sor- és oszlopfejlécek metszéspontjában található, és a helyi menüből válassza ki a lehetőséget töröl (Töröl).
Most a lapunk teljesen törlődik minden adattól, miközben a makró rögzítve marad. A munkafüzetet makróképes Excel-sablonként kell mentenünk, amely rendelkezik a kiterjesztéssel XLTM.
Fontos pont! Ha a fájlt a kiterjesztéssel menti XLTX, akkor a makró nem fog működni benne. A munkafüzetet egyébként elmentheti Excel 97-2003 sablonként, amelynek formátuma XLT, a makrókat is támogatja.
A sablon mentése után biztonságosan bezárhatja az Excelt.
Makró futtatása Excelben
Mielőtt felfednénk az általad létrehozott makró összes lehetőségét, helyesnek tartom, ha néhány fontos pontra figyelünk a makrók általánosságában:
- A makrók károsak lehetnek.
- Olvasd el újra az előző bekezdést.
A VBA kód nagyon erős. Különösen az aktuális dokumentumon kívüli fájlokon tud műveleteket végrehajtani. Például egy makró törölheti vagy módosíthatja a mappában lévő fájlokat A dokumentumokat. Emiatt csak megbízható forrásból származó makrókat futtasson és engedélyezzen.
Az adatformázó makró futtatásához nyissa meg az oktatóanyag első részében létrehozott sablonfájlt. Ha szabványos biztonsági beállításai vannak, akkor egy fájl megnyitásakor a táblázat felett figyelmeztetés jelenik meg, hogy a makrók le vannak tiltva, és egy gomb az engedélyezésükhöz. Mivel mi magunk készítettük a sablont és bízunk magunkban, nyomjuk meg a gombot Engedélyezze a tartalmat (Tartalmat is tartalmaz).
A következő lépés a legfrissebb adatkészlet importálása a fájlból CSV (egy ilyen fájl alapján hoztuk létre a makrónkat).
Amikor adatokat importál egy CSV-fájlból, az Excel bizonyos beállítások megadását kérheti az adatok megfelelő átvitele érdekében a táblázatba.
Ha az importálás befejeződött, lépjen a menübe makrók (Makrók) fülre Megnézem (Nézet), és válasszon ki egy parancsot Makrók megtekintése (Makró).
A megnyíló párbeszédpanelen látni fogunk egy sort a makrónk nevével FormatData. Válassza ki és kattintson futás (Végrehajtás).
Amikor a makró elindul, a táblázat kurzora celláról cellára ugrik. Néhány másodperc múlva ugyanazok a műveletek lesznek az adatokkal, mint a makró rögzítésekor. Amikor minden készen van, a táblázatnak ugyanúgy kell kinéznie, mint az eredetinek, amelyet kézzel formáztunk, csak a cellákban eltérő adatokkal.
Nézzünk a motorháztető alá: Hogyan működik a makró?
Mint már többször említettük, a makró egy programozási nyelv programkódja. Visual Basic alkalmazásokhoz (VBA). Amikor bekapcsolja a makrórögzítési módot, az Excel minden műveletet rögzít VBA-utasítások formájában. Egyszerűen fogalmazva, az Excel megírja helyetted a kódot.
A programkód megtekintéséhez a menüben kell lennie makrók (Makrók) fülre Megnézem (megtekintés) kattintás Makrók megtekintése (Makrók), és a megnyíló párbeszédpanelen kattintson a gombra szerkesztése (Változás).
Kinyílik az ablak. Visual Basic alkalmazásokhoz, amelyben az általunk rögzített makró programkódját fogjuk látni. Igen, jól értetted, itt módosíthatod ezt a kódot, és akár új makrót is létrehozhatsz. Az ebben a leckében a táblázattal végrehajtott műveletek az Excel automatikus makrórögzítésével rögzíthetők. De az összetettebb makrók, finoman hangolt sorrenddel és műveleti logikával, manuális programozást igényelnek.
Adjunk hozzá még egy lépést a feladatunkhoz…
Képzelje el, hogy az eredeti adatfájlunk adatok.csv valamilyen folyamat automatikusan hozza létre, és mindig ugyanazon a helyen tárolja a lemezen. Például, C:Datadata.csv – a frissített adatokat tartalmazó fájl elérési útja. A fájl megnyitásának és az adatok importálásának folyamata makróban is rögzíthető:
- Nyissa meg azt a sablonfájlt, ahová a makrót mentettük FormatData.
- Hozzon létre egy új makrót, melynek neve Adat betöltés.
- Makró rögzítése közben Adat betöltés adatok importálása fájlból adatok.csv – ahogy a lecke előző részében tettük.
- Ha az importálás befejeződött, állítsa le a makró rögzítését.
- Törölje az összes adatot a cellákból.
- Mentse el a fájlt makróképes Excel-sablonként (XLTM kiterjesztéssel).
Így ennek a sablonnak a futtatásával két makróhoz jut hozzá – az egyik betölti az adatokat, a másik formázza azokat.
Ha programozásba szeretne kezdeni, e két makró műveleteit egyesítheti egybe – egyszerűen a kód másolásával Adat betöltés a kód elejére FormatData.