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

Az Excel egyik legerősebb, de ritkán használt funkciója az a képesség, hogy nagyon egyszerűen létrehozhat automatizált feladatokat és egyéni logikákat a makrókon belül. A makrók ideális időt takarítanak meg a kiszámítható, ismétlődő feladatokra, valamint szabványosítják a dokumentumformátumokat - sokszor anélkül, hogy egyetlen kódsort kellene írni.

Ha kíváncsi arra, hogy mik a makrók, vagy hogyan lehet ezeket valóban létrehozni, akkor semmi gond - végigvezetjük az egész folyamaton.

Megjegyzés:  ugyanannak a folyamatnak működnie kell a Microsoft Office legtöbb verziójában. A képernyőképek kissé eltérhetnek.

Mi az a makró?

A Microsoft Office makró (mivel ez a funkció több MS Office alkalmazásra is vonatkozik) egyszerűen a dokumentumba mentett Visual Basic for Applications (VBA) kód. Összehasonlítható analógia céljából gondoljon egy dokumentumra, mint HTML-re, és egy makróra, mint Javascriptre. Nagyjából ugyanúgy, ahogy a Javascript képes kezelni a HTML-t egy weboldalon, a makró is manipulálhatja a dokumentumot.

A makrók hihetetlenül erősek, és nagyjából bármit megtehetnek, amit a fantáziád varázsol. Makróként elvégezhető funkciók (nagyon) rövid listája:

  • Stílus és formázás alkalmazása.
  • Az adatok és a szöveg kezelése.
  • Kommunikáljon adatforrásokkal (adatbázis, szöveges fájlok stb.).
  • Készítsen teljesen új dokumentumokat.
  • A fentiek bármelyik kombinációja, bármilyen sorrendben.

Makró létrehozása: Magyarázat példával

Kezdjük a kerti változat CSV fájljával. Itt semmi különös, csak egy 10 × 20-as számkészlet 0 és 100 között, mind sor, mind oszlopfejléccel. Célunk egy jól formázott, prezentálható adatlap elkészítése, amely az egyes sorok összesítő összesítését tartalmazza.

Amint fentebb említettük, a makró VBA kód, de az Excel egyik kedves dolga az, hogy létrehozhatja / rögzítheti őket nulla kódolással - amint itt tesszük.

Makró létrehozásához lépjen a Nézet> Makrók> Makró rögzítése elemre.

Rendeljen egy nevet a makróhoz (szóköz nélkül), majd kattintson az OK gombra.

Miután ez megtörtént, az összes műveletet rögzítjük - minden cellaváltás, görgetési művelet, ablak átméretezése, megnevezi.

Van néhány hely, amely azt jelzi, hogy az Excel rögzítési mód. Az egyik a Makró menü megtekintésével és annak megjegyzésével, hogy a Felvétel leállítása helyett a Makró rögzítése opciót választották.

A másik a jobb alsó sarokban található. A „stop” ikon azt jelzi, hogy makró módban van, és az itt lenyomásával leáll a felvétel (hasonlóképpen, ha nem rögzítési módban van, akkor ez az ikon a Makró rögzítése gomb lesz, amelyet a Makrók menü megnyitása helyett használhat).

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

Ezután alkalmazza a megfelelő képleteket:

  • = SUM (B2: K2)
  • = ÁTLAG (B2: K2)
  • = MIN (B2: K2)
  • = MAX (B2: K2)
  • = Közepes (B2: K2)

Jelölje ki az összes számítási cellát, és húzza az összes adatsor hosszát, hogy minden egyes sorra alkalmazza a számításokat.

Miután ez megtörtént, minden sorban meg kell jeleníteni a megfelelő összefoglalókat.

Most a teljes lap összesítő adatait szeretnénk megszerezni, ezért alkalmazunk még néhány számítást:

Illetőleg:

  • = SUM (L2: L21)
  • = ÁTLAG (B2: K21) * Ezt minden adatra ki kell számítani, mert a sorátlagok átlaga nem feltétlenül egyezik meg az összes érték átlagával.
  • = MIN (N2: N21)
  • = MAX (O2: O21)
  • = MEDIAN (B2: K21) * Minden adatra kiszámítva a fentiekkel megegyező okból.

Most, hogy elkészültek a számítások, alkalmazzuk a stílust és a formázást. Először az összes cellában alkalmazza az általános számformázást az Összes kijelölése (vagy Ctrl + A, vagy kattintson a sor- és oszlopfejléc közötti cellára) elemre, majd válassza a Fő menü menü „Vesszőstílus” ikont.

Ezután alkalmazzon néhány vizuális formázást mind a sor-, mind az oszlopfejlécekre:

  • Bátor.
  • Középre.
  • Háttér kitöltési szín.

És végül alkalmazzon valamilyen stílust az összegekre.

Ha minden elkészült, az adatlapunk így néz ki:

Mivel elégedettek vagyunk az eredményekkel, állítsa le a makró rögzítését.

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

Az újonnan rögzített makrónk használatához el kell mentenünk az Excel munkafüzetünket egy makrót támogató fájlformátumban. Mielőtt azonban ezt megtennénk, először meg kell tisztítanunk az összes létező adatot, hogy az ne legyen beágyazva a sablonunkba (az elképzelés minden egyes alkalommal, amikor ezt a sablont használjuk, a legfrissebb adatokat importáljuk).

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

Az adatok törlésével (de a makrók még mindig szerepelnek az Excel fájlban) el akarjuk menteni a fájlt makróképes sablonként (XLTM). Fontos megjegyezni, hogy ha ezt szabványos sablon (XLTX) fájlként menti, akkor a makrókat nem lehet futtatni belőle. Alternatív megoldásként mentheti a fájlt régi sablonként (XLT), amely lehetővé teszi a makrók futtatását.

Miután elmentette a fájlt sablonként, folytassa és zárja be az Excel programot.

Excel makró használata

Mielőtt bemutatnánk, hogyan alkalmazhatjuk ezt az újonnan rögzített makrót, fontos, hogy néhány pontot áttekintünk a makrókról általában:

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

A VBA kód valójában meglehetősen nagy teljesítményű, és az aktuális dokumentum hatókörén kívüli fájlokat is kezelhet. Például egy makró megváltoztathatja vagy törölheti a Saját dokumentumok mappában található véletlenszerű fájlokat. Mint ilyen, fontos, hogy csak megbízható forrásokból futtassa a makrókat.

Az adatformátum makrónk használatához nyissa meg a fent létrehozott Excel sablon fájlt. Ha ezt megteszi, feltételezve, hogy engedélyezve vannak a szabványos biztonsági beállítások, a munkafüzet tetején megjelenik egy figyelmeztetés, amely szerint a makrók le vannak tiltva. Mivel megbízunk egy magunk által létrehozott makróban, kattintson a "Tartalom engedélyezése" gombra.

Ezután a legfrissebb adathalmazokat importáljuk egy CSV-ből (ez az a forrás, amelyre a munkalap létrehozta a makrónkat).

A CSV fájl importálásának befejezéséhez lehet, hogy be kell állítania néhány beállítást annak érdekében, hogy az Excel megfelelően értelmezze (pl. Elválasztójel, fejlécek stb.).

Az adatok importálása után egyszerűen lépjen a Makrók menübe (a Nézet fül alatt), és válassza a Makrók megtekintése lehetőséget.

A kapott párbeszédpanelen a fent rögzített „FormatData” makrót látjuk. Jelölje ki és kattintson a Futtatás gombra.

Miután elindult, láthatja, hogy a kurzor néhány pillanatra megugrik, de ahogy látja, az adatokat pontosan úgy manipulálják , ahogyan mi rögzítettük őket. Ha mindent elmondtunk és elkészültünk, annak ugyanúgy kell kinéznie, mint az eredeti - kivéve más adatokat.

Nézd a motorháztető alatt: Mitől működik a makró?

Mint már néhányszor említettük, a makrót a Visual Basic for Applications (VBA) kód vezérli. Amikor makrót „rögzít”, az Excel tulajdonképpen mindent lefordít a megfelelő VBA utasításokba. Leegyszerűsítve: nem kell semmilyen kódot írni, mert az Excel írja neked a kódot.

A makrót futtató kód megtekintéséhez kattintson a Makrók párbeszédpanelen a Szerkesztés gombra.

A megnyíló ablakban megjelenik a forráskód, amelyet a makrókészítés során a műveleteinkből rögzítettünk. Természetesen szerkesztheti ezt a kódot, vagy akár új makrókat is létrehozhat teljesen a kódablak belsejében. Bár az ebben a cikkben használt felvételi művelet valószínűleg megfelel a legtöbb igénynek, a jobban testreszabott vagy feltételes műveletekhez a forráskód szerkesztése szükséges.

A példánkat egy lépéssel messzebbre tesszük ...

Hipotetikusan feltételezzük, hogy a forrásadat-fájlunkat, a data.csv-t egy automatizált folyamat állítja elő, amely mindig ugyanarra a helyre menti a fájlt (pl. C: \ Data \ data.csv mindig a legfrissebb adat). A fájl megnyitásának és importálásának folyamata könnyen elkészíthető makróként is:

  1. Nyissa meg a „FormatData” makrónkat tartalmazó Excel sablonfájlt.
  2. Rögzítsen egy új makrót „LoadData” néven.
  3. A makrórögzítéssel importálja az adatfájlt a szokásos módon.
  4. Az adatok importálása után állítsa le a makró rögzítését.
  5. Törölje az összes cellaadatot (jelölje ki az összeset, majd törölje).
  6. Mentse a frissített sablont (ne felejtsen el makróval engedélyezett sablonformátumot használni).

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

Ha valóban be akarta koszolni a kezét egy kis kódszerkesztéssel, könnyedén egyesítheti ezeket a műveleteket egyetlen makróba, ha lemásolja a „LoadData” -ból előállított kódot, és beilleszti a „FormatData” kód elejébe.

Töltse le ezt a sablont

Az Ön kényelme érdekében belefoglaltuk a cikkben készített Excel sablont, valamint egy minta adatfájlt, amellyel játszhat.

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