Ismerje meg, hogyan kell használni az Excel makrókat az unalmas feladatok automatizálásához

Az Excel egyik erősebb, de ritkán használt funkciója az, hogy a makrókon belül nagyon könnyen létrehozhat automatizált feladatokat és egyedi logikát. A makrók ideális módot nyújtanak arra, hogy időt takarítsanak meg a kiszámítható, ismétlődő feladatokra, valamint a dokumentumok formátumának szabványosítására - többször anélkül, hogy egyetlen sor kódot kellene írniuk.

Ha kíváncsi vagy arra, hogy milyen makrók vannak, vagy hogyan lehet őket ténylegesen létrehozni, nem probléma - mi végigkísérjük az egész folyamatot.

Jegyzet: ugyanazt a folyamatot a Microsoft Office legtöbb verziójában kell működnie. A képernyőképek kissé eltérőek lehetnek.

Mi az a makró?

A Microsoft Office makró (mivel ez a funkció több MS Office alkalmazásra is vonatkozik) egyszerűen Visual Basic for Applications (VBA) kód, amelyet egy dokumentumba mentettünk. Hasonlóképpen hasonlítsa össze a dokumentumot HTML-ként és makróként Javascript-ként. Azon a módon, hogy a Javascript a HTML-t webhelyen manipulálhatja, a makró manipulálhat egy dokumentumot.

A makrók hihetetlenül erőteljesek és meglehetősen sok mindent tehetnek, amit a képzeleted varázsol. A (nagyon) rövid funkciók listája a makróval:

• Stílus és formázás alkalmazása.
• Az adatok és a szöveg kezelése.
• Kommunikáció adatforrásokkal (adatbázis, szövegfájlok, stb.).
• Teljesen új dokumentumok létrehozása.
• A fentiek bármelyikének bármilyen kombinációja bármilyen sorrendben.

Makró létrehozása: Példa magyarázatra

Kezdjük a CSV-fajtájú kerti fajtával. Itt semmi különös, csak egy 10 × 20 sorszám 0 és 100 között egy sor és oszlop fejlécével. Célunk egy jól formázott, bemutatható adatlap készítése, amely összefoglaló összegeket tartalmaz az egyes sorokhoz.

Amint azt fentebb említettük, egy makró VBA kód, de az Excel egyik szép dologja az, hogy nulla kódolással létrehozhatja / rögzítheti őket - ahogy ezt itt fogjuk tenni.

Makró létrehozásához lépjen a Nézet> Makrók> Makró felvétele elemre.

Adja meg a makró nevét (nincs szóköz) és kattintson az OK gombra.

Ha ez megtörtént, minden a műveletek rögzítése - minden cellaváltozás, görgetési művelet, ablak átméretezése, megnevezi.

Van néhány hely, amelyek jelzik az Excel rekord módot. Az egyik a Makró menü megtekintése, és megjegyezve, hogy a Felvétel leállítása felváltotta a Record Makró opciót.

A másik a jobb alsó sarokban található. A „stop” ikon azt jelzi, hogy makró módban van, és a gomb megnyomásával leállítja a felvételt (ugyanúgy, ha nincs felvételi módban, ez az ikon a Record Macro gomb lesz, amelyet a Makrók menübe való belépés helyett használhat).

Most, hogy rögzítjük a makrónkat, alkalmazzuk az összegző számításokat. Először adja hozzá a fejléceket.

Ezután alkalmazza a megfelelő képleteket (illetve):

• = SUM (B2: K2)
• = ÁTLAG (B2: K2)
• = MIN (B2: K2)
• = MAX (B2: K2)
• = MEDIAN (B2: K2)

Most jelölje ki az összes számítási cellát, és húzza az összes adat sorunk hosszát az egyes sorok számításaihoz.

Ha ez megtörtént, minden sornak meg kell jelenítenie a megfelelő összefoglalókat.

Most szeretnénk megkapni a teljes lap összefoglaló adatait, így néhány további számítást alkalmazunk:

Illetőleg:

• = SUM (L2: L21)
• = AVERAGE (B2: K21) *Ezt az összes adatra kell kiszámítani, mert a sorok átlagainak átlaga nem feltétlenül egyenlő az összes érték átlagával.
• = MIN (N2: N21)
• = MAX (O2: O21)
• = MEDIAN (B2: K21) * Az összes adatra a fenti okok miatt számítva.

Most, hogy elvégeztük a számításokat, a stílust és a formázást alkalmazzuk. Először általános számformázást alkalmazzon az összes cellánál úgy, hogy a Mindet választja (vagy a Ctrl + A vagy a sor és az oszlopfejlécek közötti cellára kattintva), és válassza a „Comma Style” ikont a Home menü alatt..

Ezután alkalmazzon némi vizuális formázást mind a sor-, mind az oszlopfejlécekhez:

• Bátor.
• középre.
• Háttér kitöltés színe.

Végül, alkalmazzon néhány stílust az összes összegre.

Ha minden befejeződött, úgy néz ki az adatlap:

Mivel elégedettek vagyunk az eredményekkel, állítsa le a makró felvételét.

Gratulálunk - most létrehoztál egy Excel makrót.

Az újonnan felvett makró használatához az Excel munkafüzetet makró-alapú fájlformátumban kell mentenünk. Mielőtt azonban ezt megtennénk, először ki kell törölnünk az összes meglévő adatot, hogy ne legyen beágyazva a sablonunkba (az ötlet minden alkalommal, amikor ezt a sablont használjuk, akkor a legkorszerűbb adatokat importáljuk).

Ehhez válassza ki az összes cellát, és törölje azokat.

Az adatok törlése után (de a makrók még mindig szerepelnek az Excel-fájlban) menteni szeretnénk a fájlt makro-képes sablon (XLTM) fájlként. Fontos megjegyezni, hogy ha ezt normál sablon (XLTX) fájlként menti, akkor a makrók lesznek nem képesnek kell lennie belőle. Alternatívaként mentheti a fájlt örökölt sablonfájlként (XLT), amely lehetővé teszi a makrók futtatását.

Miután mentette a fájlt sablonként, menjen előre és zárja be az Excel programot.

Excel makró használata

Mielőtt lefedi az újonnan rögzített makró alkalmazását, fontos, hogy a makrókról általában néhány pontot fedezzünk fel:

• A makrók rosszindulatúak lehetnek.
• Lásd a fenti pontot.

A VBA-kód valójában meglehetősen erőteljes és manipulálhatja a fájlokat az aktuális dokumentum hatókörén kívül. Például egy makró módosíthatja vagy törölheti a Saját dokumentumok mappában található véletlenszerű fájlokat. Mint ilyen, fontos meggyőződni róla csak makrók futtatása megbízható forrásokból.

Adatformátum makró használatához nyissa meg a fent létrehozott Excel sablonfájlt. Ha ezt megteszi, feltételezve, hogy engedélyezettek a szabványos biztonsági beállítások, akkor a munkafüzet tetején egy figyelmeztetés jelenik meg, amely szerint a makrók le vannak tiltva. Mivel bízunk a saját általunk létrehozott makróban, kattintson a "Tartalom engedélyezése" gombra.

Ezután a CSV-ből a legfrissebb adatkészletet fogjuk importálni (ez a forrás, amelyet a munkalap a makró létrehozásához használt).

A CSV-fájl importálásának befejezéséhez szükség lehet néhány opció beállítására, hogy az Excel helyesen értelmezze (pl. Határoló, jelenlévő fejlécek stb.).

Miután az adatokat importáltuk, egyszerűen menjen a Makrók menübe (a Nézet lap alatt), és válassza a Makrók megtekintése lehetőséget.

Az eredményül kapott párbeszédablakban látjuk a fentebb rögzített „FormatData” makrót. Válassza ki, majd kattintson a Futtatás gombra.

Futtatás után a kurzor néhány pillanatig körbejárható, de ahogy látni fogja a manipulált adatokat pontosan ahogy felvettük. Amikor mindent elmondunk és megteszünk, úgy néz ki, mint az eredeti - kivéve a különböző adatokat.

Keresi a motorháztető alatt: mi teszi a makrót

Néhányszor már említettük, hogy a makrót a Visual Basic for Applications (VBA) kód vezérli. Amikor egy makrót „rögzít”, az Excel valóban lefordítja mindazt, amit csinál, a saját VBA utasításaiba. Egyszerűen fogalmazva - nem kell kódot írni, mert az Excel írja a kódot.

Ha meg szeretné tekinteni a makrót futtató kódot, a Makrók párbeszédpanelen kattintson a Szerkesztés gombra.

A megnyíló ablakban megjelenik a forráskód, amelyet a műveletek során rögzítettünk a makró létrehozásakor. Természetesen szerkesztheti ezt a kódot, vagy akár teljesen új makrókat hozhat létre a kód ablakon belül. Míg az ebben a cikkben használt felvételi művelet valószínűleg a legtöbb igénynek felel meg, a fokozottan testreszabott műveletek vagy feltételes műveletek megkövetelik a forráskód szerkesztését.

Például egy lépéssel tovább ...

Hipotetikusan feltételezzük, hogy a forrásadatfájlunk, az data.csv egy olyan automatizált folyamat által állítódik elő, amely mindig elmenti a fájlt ugyanarra a helyre (pl. C: Az Data.csv mindig a legfrissebb adatok). A fájl megnyitásának és importálásának folyamata könnyen elkészíthető egy makróba is:

1. Nyissa meg az Excel-sablonfájlt, amely tartalmazza a „FormatData” makrót.
2. Jegyezze fel a „LoadData” nevű új makrót.
3. A makró felvételével importáljon olyan adathordozót, mint amilyennek szokott volna.
4. Az adatok importálása után állítsa le a makró felvételét.
5. Az összes cellaadat törlése (válassza az összeset, majd törölje).
6. Mentse el a frissített sablont (ne felejtse el használni a makróhoz engedélyezett sablonformátumot).

Ha ez megtörténik, a sablon megnyitásakor két makró lesz - az egyik, amely betölti az adatainkat, és a másik, amely formázza azt.

Ha tényleg szeretted volna, hogy egy kis kódszerkesztéssel szennyezd be a kezedet, könnyen kombinálhatod ezeket a műveleteket egyetlen makróra, átmásolva a „LoadData” -ból előállított kódot, és beillesztheted a kód elejére a “FormatData” -ból.

Töltse le ezt a sablont

Az Ön kényelmét illetően mind az ebben a cikkben készített Excel sablont, mind pedig egy mintaadatfájlt adtunk hozzá.

Töltse le az Excel makró sablont a How-To Geek-ről