MENEDZSERWEBSHOP
KKV-k fejlődését segítem!
Tartalomhoz ugrás
Hogyan készíthetünk automatikus üzleti beszámolókat Excelből
Fodor Tamás vezetési tanácsadó
Közzététel szerző: Fodor Tamás itt excel · Szerda 27 Már 2024
Tags: Excel, beszámoló, riport, automatikus, makró, VBA

Az üzleti riportokról

A cégeknél általában szabályozzák a beszámolási rendszert, amely szöveges részeket, táblázatokat és diagramokat is tartalmaznak. Ezek a szabályok a beszámolás időpontját, formáját és tartalmát rögzítik. A riportok naponta, hetente, havonta és évente készülhetnek.
A táblázatos, mutatószám alapú  beszámolók három típusba sorolhatók: analitikus-, működési- és stratégiai alapú.
Analitikai jelentések időben, struktúrában részletesen elemzik a mutatószámokat. E jelentések statikus változata általában múltbeli, terv-tény vagy strukturális értékeket mutatnak, míg az interaktív jelentések lehetővé teszik a felhasználók számára az adatok dinamikus vizsgálatát különböző szempontok szerint. Használhatók az üzleti tervezés, a menedzsment intézkedések és a folyamatjavítás során.
Operatív jelentések elsősorban a szervezet napi tevékenységeire összpontosítanak. Információt nyújtanak e tevékenységekben részt vevők számára a döntések meghozatalához vagy a cselekvések meghozatalához. Például egy pénzügyi jelentésben a tartozásokról és követelésekről szóló napi jelentések felhasználhatók azok minimális szinten tartásához.
Stratégiai jelentések célja a vállalat pillanatnyi helyzetének értékelése a stratégiai célokhoz viszonyítva.
A beszámolók elsősorban mutatószámokat tartalmaznak, amelyekhez magyarázatul, indoklásként, elemzésként szöveges információk is adódnak.
Az adatokat és mutatószámokat tartalmazó riportok sokszor Excel táblázatokból készülnek. Mai blogomban ezekkel foglalkozom.
   
Az üzleti jelentések létrehozása az Excelben különböző automatizálási technikákkal egyszerűsíthetők. Vizsgáljunk meg néhány módszert, de most a  Power Query-re és a Power Pivot-ra fókuszálok, a makrókat csak érintem.

1.Makrók használata (VBA):

A makrók lehetővé teszik az ismétlődő feladatok automatizálását az Excelben. Rögzíthet egy sor műveletet, és később visszajátszhatja őket. Ezzel a módszerrel a múltkori blogomban foglalkoztam részletesebben  Most röviden.
Makró létrehozása:
Engedélyezze a Fejlesztőeszközök lapot (ha még nem látható).
Kattintson a "Makró rögzítése" gombra, és hajtsa végre a szükséges lépéseket (pl. Formázás, számítások).
Gyorsbillentyű hozzárendelése a makró futtatásához.
A makrók Visual Basic (VBA) programok, amelyeket program írással is létre lehet hozni. Ehhez ismerni kell a Visual Basic programozási nyelvet. Az Excelben a VBA segítségével létrehozhatunk űrlapokat, amelyekkel az adatműveletek (bevitel, mentés, keresés, módosítás, törlés) egyszerűen elvégezhetők.

2. Power Query

Példán keresztül mutatom be, hogy a Power Query milyen kiváló, adatelőkészítő és átalakító eszköz (motor) az adatkinyerési, -átalakítási és -betöltési  feladatok automatizálására. Az alábbi négy fázis foglalja össze, az adatok a Power Queryn keresztül történő mozgatására:
  1. Információkat  importál adatbázisból, táblázatból, helyi fájlból vagy webhelyről.
  2. Összevonhatja a különböző helyekről származó adatokat egyetlen adathalmazzá.
  3. Az adathalmazból eltávolítja, kiszűri a felesleges adatokat (tisztítás), kiszámol, hozzáad új adatokat és formázza azokat.
  4. Egy új Excel munkalapba tölti az adatokat, amelyek manuálisan vagy automatikusan (makróval) frissíthetők.
A Power Query használatának lépései:
  • Lépjen az "Adatok" fülre, és válassza az "Adatok beolvasása" vagy az "Adatok beolvasása és átalakítása" lehetőséget.
  • Csatlakozzon az adatforráshoz (pl. Excel fájlokhoz, adatbázisokhoz, webszolgáltatásokhoz).
  • Alakítsa át az adatokat (szűrés, egyesítés, matematikai műveletek, függvények).
  • Töltse be az átalakított adatokat egy új munkalapra vagy adatmodellbe.
Az alábbi képen egy koncertek szervezésére vonatkozó szerződéseket tartalmazó munkalapot lát.

Forrás file a PQ-hoz

A file 16 oszlopot mutat, amelyek közül a fontosabbak: a szerződésszám, az ügyfél neve, a zenész neve, a szerződéskötés időpontja és a szerződés összege. Egy-egy szerződésnél több, a koncerten fellépő zenész neve is szerepel  Egy olyan munkalapot szeretnénk előállítani, amelyben csak az ügyfelenkénti szerződések (a zenészek nem) azok összegei és a szerződéskötés hónapja, valamint a szerződések összegének 6%-os adója szerepel. A Power Query-ben tehát a felesleges oszlopokat törölni kell, az ismétlődéseket törölni kell (ezzel csak egy sor marad a szerződésre). Ezek a tisztítás műveletei. Ezen kívül az Időpont dátumából meg kell határozni annak hónap értékét és ki kell számolni a szerződés értékének 6 %-át. Ezek új oszlopok és műveletek hozzáadását jelentik. Az alábbi két ábrán ezek a műveletek látszanak.

Egyéni oszlop a hónap meghatározásához PQ-ban Matematikai művelethez új oszlop PQ-ban

A műveletek után  Power Query szerkesztő táblázata így fog kinézni.

PQ szerkesztő

Jobb oldalon láthatók az elvégzett tisztítási, bővítési és formázási műveletek: oszlopok törlése, ismétlődések eltávolítása, átnevezések, típus módosítások, egyéni oszlopok hozzáadása.
A következő tábla a Power Queryvel letöltött munkalapot mutatja az újonnan létrehozott oszlopokkal.

Új munkaéap Power Query segítségével

A Power Query sokoldalú lehetőségének kihasználásához szintén célszerű azt megtanulni.

3. Kimutatás (Pivot) táblák és diagramok, Power Pivot

Kimutatás tábla

A Pivot tábla az Excelben egy rendkívül hasznos eszköz, amely lehetővé teszi a nagy adathalmazok gyors és könnyű elemzését. Lényegében csoportosításokban összegezheted az értékeket, ami megkönnyíti az adatok értelmezését és információk kinyerését.

Előnyei:
  • Gyors adatelemzés: a Pivot táblával könnyedén elemezhetők nagy mennyiségű adatok.
  • Csoportosítás és összegzés: összegezhetők az értékek pl. vevőnként, időkként (pl hó, év) vagy más kategóriánként.
  • Finomhangolás: rengeteg beállítási lehetőség áll rendelkezésre, így testre szabhatók a kimutatások.
  • Hatékony munkavégzés: a Pivot tábla segítségével ugyanaz a munka sokkal gyorsabban elvégezhet, mint hagyományos Excel műveletekkel vagy makrókkal.
 
  • Hátrányok:
    • Tanulási görbe: kezdetben talán bonyolult és nehéz, de tanulással és gyakorlással könnyen elsajátítható.
    • Néha korlátozott: bár sok beállítási lehetőség van, néha előfordulhat, hogy nem tud minden igényt kielégíteni.

    A Pivot tábla elkészítése egyszerű, biztos vagyok benne, hogy a legtöbb Excel felhasználó tudja. Ha nem, látogasson el a következő oldalra. Egy kimutatás táblából a szokásos módon készíthetünk diagramot.A fenti példából az alábbi kimutatásokat és diagramot készítettem.

    Kimutatás diagrammal

    Ezen a táblán havonként és összesen láthatjuk a három cég (AS, IM és MG) szerződésállományát.
    Ha még sosem találkozott a Pivot táblával, érdemes elkezdened megismerkedni vele. Ha már használja, folyamatosan fejlesztheti a tudásodat, mert mindig vannak új trükkök és beállítások, amiket érdemes megtanulni. A Pivot tábla segítségével hatékonyabban dolgozhat és gyorsabban juthat az eredményekhez.

    Power Piwot

    A Power Pivot egy Excel-be beépülő modul, amely lehetővé teszi adatmodellek létrehozását, táblák közötti kapcsolatok kialakítását és számítások végrehajtását. Néhány előnye és hátránya:

    Előnyök:
    1. Több táblából készíthetz egyetlen kimutatást: a források lehetnek ugyanabban a munkafüzetben, több munkalapon vagy különböző Excel fájlokban.
    2. Többmillió sort képes kezelni: nagy adathalmazokkal is hatékonyan dolgozik.
    3. Hatékony adattömörítés: az adatokat tömörítve nem lesznek nagyok a létrehozott fájl-ok.
    4. DAX függvények: Komplexebb kalkulációkra használhatók, mint a hagyományos Excel kimutatásokban elérhetők.
    Hátrányok:
    1. Tanulási görbe: a Power Pivot használatának elsajátításához időre és tanulásra van szükség.
    2. Nem minden Excel verzióban elérhető: a legrégebbi verziókban nem érhető el.

    Összességében a Power Pivot hatékony eszköz az adatelemzéshez és adatmodellezéshez, különösen nagy adathalmazok esetén.
    A Power Pivot telepítését pl. a Microsoft Terméktámogatás oldalán ismerheti meg.

    Példánknál maradva azt szeretnénk, ha a koncerteket tartalmazó táblázatból kiindulva csinálhatnánk egy kimutatást, amely a zenészek nevét, címét, adószámát és díját mutatja. A zenész címe és adószáma azonban egy másik munkalapon van. A Power Pivot szerkesztőjében a zenészek nevei segítségével összekapcsolhatjuk a két táblát (munkalapot).

    Power Pivot: két munkalap összekapcsolása.

    Az összekapcsolás után pl. az alábbi kimutatást hozhatjuk létre.

    Kimutatás Power Pivot-tal

    Összefoglalva elmondható, hogy Excel alapú üzleti riportokat makrókkal vagy az Excel beépített funkcióival, a Power Query-vel, a Pivot táblázat készítővel, vagy a Power Pivot add-in segítségével készíthetünk. A riportok automatizálása számos előnnyel jár:
              • időt takarít meg és biztosítja a adatok pontosságát,
              • biztosítja a felhasználó orientált megjelenési formát,
              • a stratégiai jelentéseknél feltételeket adhatunk meg,
              • automatikusan készíthetők pl. PDF file-ok,
              • automatikusan megoszthatók a résztvevők között (pl. e-mail-lel).                                  

    Hír forrásai:


    Vissza a tartalomhoz