Havi riportok összesítése, táblák összefűzése excellel (Excel Merge, Power Query)

Hogyan tudok több excel táblát összesíteni egyetlen file-ban egyszerűen

Az utóbbi időben egyre többen keresnek meg azzal, hogy segítsek a havi riportálási folyamatukat automatizálni. A folyamat általában a következő:

• van egy adatbázis, ahonnan havi szinten excelbe nyerik ki az adatokat
• ezeket az excel táblákat egy mappába mentik le
  • minden hónapban ugyanúgy néznek ki a táblák: ugyanaz a sheet (munkalap) neve ahol az adatok találhatók és standardizált a file-ok elnevezése (pl. HC_riport_2020_01_01.xlsx – ahol mindig csak a dátum változik).
  • Egyszer-egyszer előfordul, hogy új oszlopok kerülnek bele a riportba, vagy az oszlopok sorrendje megcserélődik, esetleg kevesebb oszlop található valamelyik táblában, ezért pl. VBA (markó) segítségével csak nehézkezesen lehetne automatizálni a táblák havi szintű összefűzését (viszont a lenti megoldással ez is működik)
• a feladat pedig az, hogy egy olyan könnyen elemezhető excel adatbázis álljon össze, ami tartalmazza az összes megadott helyre/mappába lementetett excel táblát amit havi szinten egyetlen gombnyomással lehet frissíteni

Videó: havi riportok összesítésének automatizálása Excelben

Ebben az online videóban azt mutatom meg, hogyan tudsz kevesebb, mint 10 perc alatt egy mappában található bármennyi excel táblát összefűzni (merge) PowerQuery segítségével:

PowerQuery merge lépésről lépésre

A táblák előkészítése:

1. Mentsd le egy külön mappába azokat a táblákat, amiket összesíteni szeretnél
2. Ügyelj arra, hogy a táblák elnevezése egységes legyen, és valami alapján időrendi sorrendbe lehessen őket állítani!
   • Erre egy jó megoldás lehet, ha a file neve melett az aktuális dátumot is feltünteted (pl. HC_riport_2020_01_01)
   • Ez akkor igazán fontos, ha egy idővonalat szeretnél csinálni, pl. meg szeretnéd vizsgálni, hogy egy embernek hogyan változott a fizetése az egyes hónapokban, vagy az árbevétel havonta hogyan alakult stb.
3. Ellenőrizd, hogy minden excelben ugyanott van-e a fejléc (legjobb, ha első sorban van), és a munkalap (sheet) neve minden táblánál megegyezik-e! (az nem gond, ha több munkalap is van valamelyik táblában, de ahonnan az adatokat szeretnéd behúzni, annak a munkalapnak ugyanolyan elnevezést kell, hogy kapjon, mint a többi excelben)

Összesítő Excel előkészítése

1. Nyiss egy üres Excel táblát
2. A menüsoron keresd meg a Data (Adatok) részt
3. Bal oldalon vagy középen látod a Get Data lehetőséget. Arra kattints!
4. From file
5. From folder
6. A felugró ablakba másold be a mappa elérési útvonalát (ez az a mappa, ahova lementetted a táblázatokat), majd katt az OK gombra!
7. Ezután felugrik egy olyan ablak, ami kilistázza a mappában lévő összes excel táblát. A jobb alsó sarokban nyisd le a Combine gombot, és válaszd a Combine&Load lehetőséget
8. Ha jól csináltad, akkor megjelenik egy ablak, ahol kiválaszthatod, hogy a mappában lévő melyik file legyen a minta, és meg tudod adni, hogy melyik munkalapon vannak az adatok (ezért is kell odafigyelni, hogy minden excelben ugyanaz legyen a sheet neve)
9. Már csak annyi a dolgod, hogy leokézd, és be is töltődik az összesített adatbázis

Innentől kezdve pedig csupán annyi a dolgod minden hónapban, hogy lemented a legfrissebb havi riportot a szokásos mappába, és a Data/Refresh All gomb megnyomásával ráfrissítesz az excel adatbázisra. A PowerQuery pedig automatikusan összehúzza a táblákat.

Így neked nem kell felesleges időt pazarolnod arra, hogy minden hónap elején egymás alá másolgasd a riportokat, és nem kell bonyolult VBA kódokat írnod, mert egyetlen gombnyomásra bármikor be tudod frissíteni az adatbázisodat.

Oké, az összesítés megvan. Hogyan tovább?

A táblák összefűzése csak az első lépés. Innentől jön a munka neheze, ami szintén szerencsére automatizálható:

• a táblázatra hivatkozva készíts kimutatásokat (pivot), amik mindig be fognak frissülni a legfrissebb adatokkal, mikor a Data/Refresh all lehetőségre nyomsz
• add hozzá a táblázatot a PowerPivot adatbázishoz, és kösd össze más adatforrásokból származó adatokkal (pl. más excel táblákkal)
• készítsd dinamikusan firssülő Excel Dashboardot
• engedd szabadra a fantázidád, és használd fel minden eddigi tudásod, hogy minél többmindent automatizálj, így minél kevesebb időt kelljen manuális feladatokkal töltened!