Hogyan fejlesszük tovább jelenlegi Excel alapú nyilvántartásainkat?
Közzététel szerző: Fodor Tamás itt adatbázis · Szerda 24 Jan 2024
Tags: Excel, nyilvántartás, adatbázis, karbantartás
Tags: Excel, nyilvántartás, adatbázis, karbantartás
Mikro- és kisvállalatok, egyéni vállalkozók általában nem engedhetik meg, hogy drága szoftvereket vegyenek üzletük menedzseléséhez. Ezért ezeket a feladatokat sokszor saját fejlesztésű Excel táblázatokban oldják meg.
Az utóbbi hónapokban egyre többen keresnek meg, hogy a jelenleg Excel táblázatban létező nyilvántartásukat alakítsam át, fejlesszem tovább, hogy hatékonyabban tudjanak dolgozni. Ilyen például egy zenei koncert szervező, egy VÁM tarifálással foglalkozó cég nyilvántartása, vagy tendereknél az ajánlatok bekérésére szolgáló ártükör fejlesztése.
Milyen problémákkal találkozom ilyen esetekben?
Struktúra
A struktúrális probléma általában abból ered, hogy a felhasználó egy munkalapon próbál megoldani mindent: az összes adat és képlet itt szerepel: az ügyfél, az alkatrészek, munkák, résztvevők, a szerződések stb. A rossz szerkezetet színezéssel próbálják átláthatóbbá tenni. Hiányzik a file-okban és rekordokban történő gondolkodás, hogy legyen egy munkalap az ügyfelekre, amelyben egy-egy rekord az ügyfél összes adatát tartalmazza (név, cím telefon, email stb.), egy másik pl. a szerződésekre, egy harmadik az alvállalkozókra stb. Ezeket más-más munkalapon, vagy munkafüzetben (file-ban) lehet elhelyezni.
Képletek
A felhasználó általában nem egy képzett Excel szakértő, csak az alapvető képleteket ismeri, így korlátozott a megoldási képesség. Tekintse meg a Microsoft weboldalát, amelyen felsorolják az összes képletet! Ha megnézzük a SUM funkciót, látható, hogy ebből 9 létezik. Például nagyon hasznos SUMIF. amely azon cellák értékét adja össze, amelyek megfelelnek egy kritériumnak. Ide tartozik a dinamikus tartományok használata is, amely nagyon megkönnyíti pl. a képletekkel rendelkező tartományok adatokkal való bővítését vagy csökkentését. Ilyenkor a tartomány automatikusan változik a képletekkel együtt.
Power Query
A legtöbb felhasználó szintén nem ismeri Power Query beépített eszközt, amellyel lekérdezéseket hozhat létre, szerkeszthet és futtathat, adatokat importálhat különböző forrásokból, például weboldalakról, adatbázisokból, fájlokból stb. A Power Query segítségével tisztíthatja, szűrheti, összevonhatja, transzponálhatja és átalakíthatja az adatokat, mielőtt betöltené azokat a Excel munkalapra vagy az adatmodellbe. A Power Query előnye, hogy automatizálhatja az adatok frissítését, és elmentheti a lekérdezéseket újrafelhasználásra.
Visual Basic programozás
A felhasználótól nem várható el, hogy ismerje a Visual Basic VBA programnyelvet és programozást. A VBA egy programozási nyelv, amelyet a Excel és más Microsoft Office alkalmazások makróinak írására használhat. A VBA segítségével testre szabhatja a Excel működését, létrehozhat saját függvényeket, eljárásokat, felhasználói felületeket, adatmodelleket és alkalmazásokat. A VBA előnye, hogy nagyobb ellenőrzést és rugalmasságot biztosít az adatok kezelésében, és lehetővé teszi a bonyolultabb és kreatívabb feladatok elvégzését.
A VBA pl. az alábbi problémákra lehet megoldás.
Adatbevitel és karbantartás
A felhasználók általában a táblázatokba közvetlenül írják be az adatokat vagy javítják azokat.. Ennek több hátránya van: nehéz keresni egy nagyobb táblázatban, a táblázat nincs védve, így könnyen előfordul, hogy elrontunk egy helyes adatot. A VBA lehetőséget teremt arra, hogy adatbeviteli és karbantartó űrlapot készítve végezzük el ezeket a műveleteket, miközben a táblázatunk védett állapotban marad. Az adatok bevitelekor formai és tartalmi ellenőrzések végezhetők. Az adatbevitel megkönnyítése érdekében az űrlapon legördülő listák is létrehozhatók. Az alábbi képen egy ilyen űrlapot láthatunk. (Megjegyzem, hogy az Excelnek van egy beépített adatbeviteli eszköze, azonban ez gyakran nem elég.)
Az űrlap segítségével a következő műveletek végezhetők el.
Mezők törlése
A beviteli űrlap mezőit törli.
Keresés
A vezérlőre kattintva felugrik egy ablak, amelybe a keresendő kifejezést kell beírni. Bármire kereshetünk. Megkeresi azt a rekordot, amelyben ez a kifejezés található. Ekkor felugrik egy választó ablak (OK vagy Mégsem). Az OK gombra kattintva megkeresi a következő, a kifejezést tartalmazó rekordot. A mégse leállítja a keresést.
Következő rekord
Megkeresi az utána következő rekordot.
Előző rekord
Megkeresi az előtte lévő rekordot.
Mentés
Elmenti az űrlapon megjelenített rekordot. Ez lehet egy új rekord, amelyhez az adatokat előzetesen bevittük. A rekord új sorszámot kap és az utolsó sorba kerül. Lehet régi rekord, amelyet megkerestünk és valamilyen módosítást (pl. másik zenekar is) végeztünk. A rekord sorszáma megmarad.
Rekord törlése
Kitörli azt a rekordot, amely az űrlapon van.
Automatizálás
A VBA programokkal automatizálhatunk különböző műveleteket, amelyek manuálisan hosszú időt vennének igénybe. Például egy olyan feladat, hogy egy táblázatba, amely hivatkozásokat tartalmaz egy másik nagy táblázatra, be akarunk írni bizonyos adatokat a másik nagy táblázatból, akkor egy olyan program, amely megkeresi a nagy listában a hivatkozott adatot, ez automatizálható.
Üzleti dokumentumok készítése
A Visual Basic programmal a táblázat adataiból elkészíthetjük az üzleti dokumentációkat, pl. ajánlatokat, szerződéseket, számlákat stb. A következő ábrán egy ajánlatot láthatunk.
Összefoglalás
Az Excel egy népszerű és sokoldalú szoftver, amelyet adatok importálására, elemzésére, átalakítására (matematikai, logikai, szöveg, statisztikai és más műveletek végzésére), megosztására használhat. Az Excel két olyan funkciót is tartalmaz, amelyek tovább növelik az adatkezelési képességeit: a Power Query-t (PQ) és a Visual Basic for Applications-t (VBA). Az Excel előnye a PQ és a VBA használatával az, hogy gyorsabban, hatékonyabban és intelligensebben dolgozhat az adatokkal, és kihasználhatja az Excel teljes potenciálját.
Az alábbi blokkséma egy CRM (Customer Relation Management) rendszert mutat. Ez az ábra egyben a rendszer vezérlőpultja, ahol a VBA programok az ábra egyes négyszögeire kattintva indíthatók.