Ismétlődő munkafolyamatok automatizálása Excelben
E havi blogomban ismétlődő munkafolyamatok automatizálásáról lesz szó Excelben. Az Excel széleskörű képességei miatt az egyik leggyakrabban használt programcsomag, és a programozási lehetőségeivel valóban kiaknázhatja a benne rejlő potenciált, automatizálva az unalmas, ismétlődő feladatokat. A Visual Basic (VBA) eszköze teljesen új világot nyit meg az automatizálás terén, és a blogban megmutatom, hogyan érdemes használni az Excel automatizálására.
3. Pontosság: logikus lépések minimalizálják a hibákat.
4. Döntéshozatal: jobb minőségű adatok, könnyebben olvasható fájlok.
Visual Basic for Applications (VBA) egy objektum orientált programozási nyelv, amely a Microsoft-termékek, köztük az Excel, a Word, a PowerPoint és az Access funkcióinak kiterjesztésére és fejlesztésére használható.
Lényegében lehetővé teszi, hogy egyedi bővítményeket, funkciókat és műveleteket kódoljon az Excel munkalapjaiba.
A VBA használatával automatizálhatja az ismétlődő feladatokat, valamint egyedi űrlapok, adatjelentések és irányítópultok létrehozását.
VBA programot két féle módon hozhatunk létre: az Excelbe épített Makró rögzítővel, vagy Visual Basic program írásával.
VBA program létrehozása Makró rögzítővel
Képzeljük el, hogy van egy webshopunk, ahová a látogatók feliratkoznak. A feliratkozókat le tudjuk tölteni egy Excel file-ba, ahol az első oszlop az e-mail cím, a második a név.
Van egy hírlevél küldő programunk is, amely a neveket olyan Excelből tudja beolvasni, amelyben az első oszlop a név és a második az e-mail. A hírlevél küldő rendszerben a tagok adatbázisát minden hírlevél kiküldése előtt aktualizálni kell, hiszen két hírlevél között új feliratkozók lettek. Tehát minden aktualizálás előtt el kell végezni azt a műveletet, hogy az első és második oszlopot felcseréljük. Ennek az egyszerű műveletnek a rögzítéséhez kattintsunk az Excel Fejlesztési eszközök menüjére. Ezt fogjuk látni:
A bal oldalon látható Makró rögzítés vezérlőre kattintsunk először. Felugrik egy ablak, amely mutatja, hogy a program milyen néven lesz mentve. Ezután az Excel táblázatban végezzük el a következő műveleteket. Jelöljük ki az első oszlopot, majd kattintsunk a Másolás-ra. Jelöljük ki a harmadik oszlopot és kattintsunk a Beillesztésre. Utána ismét jelöljük ki az első oszlopot és Töröljük azt. Kattintsunk a Rögzítés vége (amely az előbbi gomb helyén van) vezérlőre. Most válasszuk ki a Makrók vezérlőt és a felugró ablak jobb oldalán a Szerkesztés gombra. Láthatjuk a VBA programot.
A Makrók gombra kattintással megjelenő felugró ablak jobb felső sarkában található Indítás vezérlővel futtathatjuk ezt a programot, de az Egyebek-nél hozzárendelhetünk egy billentyűparancsot is, amelyre lefut a program.
VBA program írása
Ez a blog nem azt a célt szolgálja, hogy megtanítsa Önt a Visual Basic program írására. A program utasításaival mindent meg lehet csinálni az Excel sablon adataival: kijelölhetjük a munkalapokat, a cellákat, matematikai műveleteket végezhetünk a cellák adataival, kereshetünk az adatok között, vagy szűrhetjük azokat stb. A VBA nagy elnye, hogy létrehozhat Űrlapokat (UserForm), amelyekkel már komoly alkalmazásokat lehet készíteni. (Erről majd egy másik alkalommal írok.) Ha mélyebben szeretne elmélyülni a VBA programozásban, keresse fel ezt az oldalt!
Az alábbiakban néhány tanácsot adok azoknak akik már járatosak a VBA területén:
- Nem kell mindent programozni, néha csak fejlett képletekre van szükség.Például a SUMPRODUCT a sor- és oszlopfeltételekhez, VLOOKUP az adatok kinyeréséhez táblázatokból, ismétlődések eltávolítása, ElseIf a többszörös HA feltételekhez stb. A VBA-ban az "ElseIf" utasítást a feltételes utasításokban (például az "If" utasításban) használják további feltételek meghatározására, amelyeket akkor kell használn, ha az eredeti "If" feltétel hamis. Tegyük fel, hogy a vásárlásokat az ügyfelek földrajzi elhelyezkedése alapján szeretné kategorizálni.A kezdeti „Ha” utasítás így szólhat: „Ha a hely = Siófok”, ebben az esetben az utasítást egy olyan függvénynek vagy képletnek kell követnie, amelyet végrehajtani szeretne. Ha ez után ElseIf utasítást használunk egy másik feltételre, amikor az eredeti If utasítás hamis (ebben az esetben, ha a rendelés társított helye nem Siófok), az ElseIf utasítás lényegében azt mondja, hogy „ha a hely nem Siófok, akkor ellenőrizze, hogy az Zamárdi-e”. Ehhez ezután ismét meghatározhat egy függvényt vagy képletet, amelyet végrehajtani szeretne abban az esetben ha a második állítás igaz.
- Segítő oszlopok használata – ez megkönnyíti a számításokat. A segédoszlopok a feltételes formázáshoz is jól jöhetnek.
- Tömbök használata – Ez azt jelenti, hogy az adatokat egy olyan változótípusba kell helyezni, amely memóriahatékonyabb. Ha nagy adattartományokon dolgozik, helyezze a tartományt egy tömbbe! A tömbfüggvény lehetővé teszi, hogy tetszőleges számú különböző elemet definiáljon, amelyek ugyanazon a belső adattípuson osztoznak, mint kapcsolódóan vagy egy csoportként/változóként.A tömbök a leghasznosabbak az adattartományok manipulálására és az adatok nagy tömegeinek gyorsabb összefoglalására.Tegyük fel például, hogy a tejtermékek a készlet egyik összetevője, de sokféle tejterméke van.Egy tömb használatával beállíthatja a „tejterméket” változóként, míg az egyes terméknevek elemként illeszkednének a változóba – például a „2%-kal csökkentett zsírtartalmú bio tej” a „tejtermékek” alatt található alkategóriaként.Tehát ha mondjuk 28 különböző tejterméke van, ahelyett, hogy 28 különböző változót hozna létre, létrehozhat egy változót, amely 28 elemből áll.A képletek összeállítása vagy automatizálása során ezt változó szinten is megteheti anélkül, hogy 28 különálló elemet kellene figyelembe vennie. Sokkal egyszerűbbé és tisztábbá teszi az adatkezelést.
- Ennek megfelelően nevezze el a VBA-modulokat, hogy tudja, milyen kódot tartalmaz.
- Alapértelmezés szerint a VBA megkülönbözteti a kis- és nagybetűket. Tehát az IF utasítások használatakor tartsa szem előtt az esetet. Az LCase használatával mindig átalakíthatja a dolgokat kisbetűsre, hogy biztosan megtörténjen a megfelelő összehasonlítás.
- Az aláhúzás karakterrel folytathatja a VBA utasítást egy új sorban . Így könnyebben olvasható lesz a kód.
- Használja a WITH-t a VBA-kód lerövidítéséhez. Ezt általában csak egy tartomány egyszeri begépelésére használják, majd a WITH használatával több műveletet hajthat végre a tartománnyal.
- Kisebb makrók létrehozásakor nem kell minden változót deklarálni. Ezzel helyet takaríthat meg, és a kód könnyebben olvasható lesz. Változók deklarálása csak akkor szükséges, ha tömböket, dátumokat használ, vagy ha más függvények meghatározott változótípust igényelnek. Nagyobb makrók esetén a változók deklarálása segíthet egyértelművé tenni az összes használt változó nevét és miértjét (megjegyzések hozzáadása a tetejére). Itt található további információ a változók deklarálásáról .
- A VBA helyi ablaka segítségével nyomon követheti és megtekintheti a változókon belüli adatokat. Ez különösen a tömbök megtekintésénél hasznos. Csak egy töréspontot kell hozzáadnia a kódhoz. Jelöljön ki egy sort, majd kattintson az F9-re. Aztán amikor a kód lefut, ott leáll, és a sor feletti összes változó megjelenik a helyi ablakban.
Az automatizálás előnyei és hátrányai
Előnyök:
1. Automatizálás: az adatok formázása, másolása, rendezése, riportkészítés és lépések automatizálása.
2. Hatékonyság: ismétlődő feladatok egyszerűsítése, időmegtakarítás.3. Pontosság: logikus lépések minimalizálják a hibákat.
4. Döntéshozatal: jobb minőségű adatok, könnyebben olvasható fájlok.
Hátrányok:
1. Tanulási görbe: kezdetben tanulni kell a VBA-t, de hosszú távon megtérül.2. Túlzott komplexitás: néha túl bonyolult megoldásokat eredményezhet.
Példák automatizálásra
Első ilyen projektem egy egy havonta készülő főkönyvi kivonatra alapozott menedzsment kontrolling rendszer volt. A cég több üzletágat (profit központot) üzemeltetett és több kölséghellyel rendelkezett. Az automatizálási feladat lényege az volt, hogy a főkönyvi kivonaton végigszaladva a program külön lapokra gyűjtse az üzletágak és költséghelyek tételeit és az azonos költségnemeket összeadva egy olyan táblázatba gyűjtse, amely a terv adatokat is tartalmazzák. Erről írtam egy könyvet Excel alapú pénzügyi kontrolling rendszer címmel.
Hihetetlenül hasznos az ajánlat-, szerződés-, számlaküldés automatizálása a szerződések adatbázisából és ehhez csatlakoztathat egy e-mail küldő rendszert is: a program elkészíti az ajánlatokat, szerződéseket és számlákat és azokat elküldi az ügyfélnek.
Egy vámtarifáló cégnél a tarifálási folyamat automatizálása volt a feladat. A beérkező több ezer árutételt tartalmazó szállítmányhoz tartozó listákat tarifálni kell a magyar előírások szerint. Amikor egy tételt tarifálnak, az bekerül egy adatbázisba, hogy legközelebbi ilyen tétel tarifálása egy keresési és egy másolási művelettel elvégezhető legyen. Ebben a VBA programban ez a keresés és másolás az automatizál folyamat.
Egy másik jelentős időmegtakarítás a jelentéskészítés VBA-val történő automatizálása. Az alábbi, rövid videóban azt láthatja, hogy egy nagy értékesítési táblázatból, hogyan gyűjti ki egy VBA program a riportban kért adatokat.
Következő blogom az Excel alapú üzleti beszámolók automatikus létrehozásával foglalkozik a Power Query-re és Power Pvot-ra fókuszálva.
Hír forrásai:
- How to automate repetitive workflows with Visual Basic in Excel: https://parabola.io/blog/vba-automation
- Excel Automation Explained: VBA Code + Sample Workbooks: https://www.surveyking.com/blog/excel-automation-explained/