tartalom
A probléma megfogalmazása
Nézzünk meg egy gyönyörű megoldást arra az igen szokásos helyzetre, amellyel előbb-utóbb a legtöbb Excel-felhasználó szembesül: gyorsan és automatikusan kell adatokat gyűjtenie nagyszámú fájlból egyetlen végső táblázatba.
Tegyük fel, hogy van a következő mappánk, amely több fájlt tartalmaz fióktelepi városok adataival:
A fájlok száma nem számít, és a jövőben változhat. Minden fájlnak van egy nevű lapja Értékesítésahol az adattábla található:
A táblákban a sorok (sorrendek) száma természetesen eltérő, de az oszlopkészlet mindenhol szabványos.
Feladat: az összes fájl adatainak összegyűjtése egy könyvbe, majd ezt követően automatikus frissítéssel városfájlok vagy táblázatsorok hozzáadásakor vagy törlésekor. A végleges konszolidált tábla szerint ezután lehet majd bármilyen riportot, pivot táblát, adatokat szűrni, stb. építeni. A lényeg, hogy gyűjteni tudjunk.
Fegyvereket választunk
A megoldáshoz szükségünk van az Excel 2016 legújabb verziójára (a szükséges funkcionalitás már alapértelmezés szerint be van építve), vagy az Excel 2010-2013 korábbi verzióira van szükségünk az ingyenes bővítménnyel. Teljesítmény lekérdezés a Microsofttól (töltse le innen). A Power Query egy rendkívül rugalmas és rendkívül hatékony eszköz, amellyel adatokat tölthet be az Excelbe a külvilágból, majd törölheti és feldolgozhatja azokat. A Power Query szinte az összes létező adatforrást támogatja – a szöveges fájloktól az SQL-ig és még a Facebookig is 🙂
Ha nincs Excel 2013 vagy 2016, akkor nem olvashat tovább (csak viccelek). Az Excel régebbi verzióiban egy ilyen feladatot csak Visual Basicben programozva (ami kezdőknek nagyon nehéz) vagy monoton kézi másolással (ami sokáig tart és hibákat generál) lehet ilyen feladatot megoldani.
1. lépés: Importáljon egy fájlt mintaként
Először példaként importáljunk adatokat egy munkafüzetből, hogy az Excel „felvegye az ötletet”. Ehhez hozzon létre egy új üres munkafüzetet, és…
- ha Excel 2016-tal rendelkezik, nyissa meg a lapot dátum és azután Lekérdezés létrehozása – Fájlból – Könyvből (Adatok – Új lekérdezés – Fájlból – Excelből)
- ha az Excel 2010–2013-as verziója telepítve van a Power Query bővítménnyel, akkor nyissa meg a lapot Teljesítmény lekérdezés és válassza ki rajta Fájlból – Könyvből (Fájlból – Excelből)
Ezután a megnyíló ablakban lépjen a jelentéseket tartalmazó mappánkra, és válassza ki a városi fájlok közül bármelyiket (nem mindegy, hogy melyiket, mert mindegyik jellemző). Néhány másodperc múlva meg kell jelennie a Navigátor ablaknak, ahol a bal oldalon kell kiválasztani a számunkra szükséges lapot (Sales), és a tartalma megjelenik a jobb oldalon:
Ha az ablak jobb alsó sarkában található gombra kattint Letöltés (Betöltés), akkor a táblázat azonnal az eredeti formájában importálódik a lapra. Egyetlen fájl esetén ez jó, de sok ilyen fájlt kell betöltenünk, ezért egy kicsit másképp járunk el, és kattintunk a gombra Javítás (Edit). Ezt követően a Power Query lekérdezésszerkesztőjének külön ablakban kell megjelennie a könyvből származó adatainkkal:
Ez egy nagyon hatékony eszköz, amely lehetővé teszi a táblázat „befejezését” a szükséges nézetre. Még az összes funkciójának felületes leírása is körülbelül száz oldalt venne igénybe, de ha nagyon röviden, akkor ennek az ablaknak a használatával:
- szűrje ki a felesleges adatokat, üres sorokat, hibás sorokat
- az adatokat egy vagy több oszlop szerint rendezi
- megszabadulni az ismétléstől
- a ragadós szöveget oszlopokra osztani (határolójelek, karakterek száma stb. szerint)
- a szövegek sorrendbe állítása (felesleges szóközök eltávolítása, kis- és nagybetűk javítása stb.)
- minden lehetséges módon konvertálja az adattípusokat (a számokat, például a szöveget normál számokká alakítja, és fordítva)
- transzponálja (forgatja) a táblázatokat, és bontsa ki a kétdimenziós kereszttáblákat laposra
- adjon hozzá további oszlopokat a táblázathoz, és használjon képleteket és függvényeket bennük a Power Querybe épített M nyelv segítségével.
- ...
Például adjunk hozzá egy oszlopot a hónap szöveges nevével a táblánkhoz, hogy később könnyebb legyen pivot tábla jelentéseket készíteni. Ehhez kattintson a jobb gombbal az oszlop fejlécére adatés válassza ki a parancsot Oszlop másolása (Duplikált oszlop), majd kattintson a jobb gombbal a megjelenő ismétlődő oszlop fejlécére, és válassza a Parancsok lehetőséget Átalakítás – Hónap – Hónap neve:
Minden sorhoz új oszlopot kell létrehozni a hónap szöveges neveivel. Ha duplán kattint egy oszlopfejlécre, átnevezheti az oszlopfejlécről Dátum másolása egy kényelmesebbre Hónap, például.
Ha egyes oszlopokban a program nem egészen pontosan ismerte fel az adattípust, akkor az egyes oszlopok bal oldalán található formátum ikonra kattintva segíthet:
Egy egyszerű szűrő segítségével kizárhatja a hibás vagy üres sorokat tartalmazó sorokat, valamint a szükségtelen vezetőket vagy ügyfeleket:
Ezenkívül az összes végrehajtott átalakítás a jobb oldali panelen van rögzítve, ahol mindig visszatekerhető (kereszt) vagy megváltoztatható paramétereik (fogaskerék):
Könnyű és elegáns, nem?
2. lépés. Alakítsuk át kérésünket függvényvé
Annak érdekében, hogy a későbbiekben megismételhessük az egyes importált könyveknél végrehajtott összes adatátalakítást, a létrehozott kérésünket függvényvé kell alakítanunk, amelyet azután az összes fájlunkra alkalmazni kell. Ennek megvalósítása valójában nagyon egyszerű.
A Lekérdezésszerkesztőben lépjen a Nézet fülre, és kattintson a gombra Speciális szerkesztő (Nézet – Speciális szerkesztő). Meg kell nyílnia egy ablaknak, ahol az összes korábbi műveletünket kód formájában írjuk M nyelven. Kérjük, vegye figyelembe, hogy a példához importált fájl elérési útja a kódban van kódolva:
Most hajtsunk végre néhány beállítást:
Jelentésük egyszerű: az első sor (fájlútvonal)=> az eljárásunkat argumentummal rendelkező függvényté alakítja fájl elérési út, alatta pedig módosítjuk a fix elérési utat ennek a változónak az értékére.
Összes. Kattintson befejez és ezt látnia kell:
Ne félj attól, hogy az adatok eltűntek – tulajdonképpen minden rendben van, mindennek így kell kinéznie . Még érthetőbb nevet kell adni (pl getData) a jobb oldali panelen a mezőben Keresztnév és arathatsz Kezdőlap — Zárja be és töltse le (Főoldal – Bezárás és betöltés). Kérjük, vegye figyelembe, hogy a példához importált fájl elérési útja a kódban van kódolva. Vissza fog térni a Microsoft Excel főablakába, de a jobb oldalon egy panelnek kell megjelennie a funkciónkkal létrehozott kapcsolattal:
3. lépés: Az összes fájl összegyűjtése
A legnehezebb rész hátravan, a kellemes és könnyű rész megmarad. Menjen a lapra Adatok – Lekérdezés létrehozása – Fájlból – Mappából (Adatok — Új lekérdezés — Fájlból — Mappából) vagy ha Excel 2010-2013 verziója van, a laphoz hasonlóan Teljesítmény lekérdezés. A megjelenő ablakban adja meg azt a mappát, ahol az összes forrásvárosi fájlunk található, és kattintson a gombra OK. A következő lépésben meg kell nyitnia egy ablakot, amelyben megjelenik az ebben a mappában (és almappáiban) található összes Excel-fájl és mindegyikük részletei:
Kattints Változtat (Edit) és ismét az ismerős lekérdezésszerkesztő ablakba jutunk.
Most egy újabb oszlopot kell hozzáadnunk a táblázatunkhoz a létrehozott függvényünkkel, amely minden fájlból „lehúzza” az adatokat. Ehhez lépjen a lapra Oszlop hozzáadása – Egyéni oszlop (Oszlop hozzáadása – Egyéni oszlop hozzáadása) és a megjelenő ablakban írjuk be a függvényünket getData, argumentumként megadva az egyes fájlok teljes elérési útját:
Miután rákattintott OK a létrehozott oszlopot a jobb oldali táblázatunkhoz kell hozzáadni.
Most töröljük az összes felesleges oszlopot (mint az Excelben, a jobb egérgombbal – eltávolít), csak a hozzáadott oszlopot és a fájlnévvel rendelkező oszlopot hagyja meg, mert ez a név (pontosabban a város) hasznos lesz az egyes sorok összesített adatában.
És most a „wow pillanat” – kattintson a saját nyilakkal ellátott ikonra a funkciónkkal hozzáadott oszlop jobb felső sarkában:
… törölje a jelölést Használja az eredeti oszlop nevét előtagként (Használja az eredeti oszlopnevet előtagként)és kattintson OK. Funkciónk pedig minden fájlból betölti és feldolgozza az adatokat, követve a rögzített algoritmust, és mindent egy közös táblázatba gyűjtve:
A teljes szépség érdekében eltávolíthatja az .xlsx kiterjesztést az első oszlopból a fájlnevekkel – szabványos „semmi” szóval helyettesítve (jobb gombbal kattintson az oszlop fejlécére – Helyettes), és nevezze át ezt az oszlopot erre: Város. És javítsa ki az adatformátumot is a dátummal rendelkező oszlopban.
Összes! Kattintson Kezdőlap – Bezárás és betöltés (Főoldal – Bezárás és betöltés). A lekérdezéssel összegyűjtött összes adat az összes városra vonatkozóan „intelligens tábla” formátumban kerül feltöltésre az aktuális Excel lapra:
A létrehozott kapcsolatot és az összeállítási funkciónkat semmilyen módon nem kell külön elmenteni – a szokásos módon az aktuális fájllal együtt kerülnek mentésre.
A jövőben a mappában (városok hozzáadása vagy eltávolítása) vagy a fájlokban (a sorok számának módosítása) végzett bármilyen változás esetén elegendő, ha jobb gombbal közvetlenül a táblázatra vagy a jobb oldali panelen lévő lekérdezésre kattint, és kiválasztja a parancs Frissítés és mentés (Frissítés) – A Power Query néhány másodpercen belül újra „újraépíti” az összes adatot.
PS
Módosítás. A 2017. januári frissítések után a Power Query megtanulta, hogyan lehet önállóan gyűjteni az Excel-munkafüzeteket, vagyis többé nem kell külön függvényt készíteni – ez automatikusan megtörténik. Így a cikk második lépésére már nincs szükség, és az egész folyamat észrevehetően egyszerűbbé válik:
- A pop-art design, négy időzóna kijelzése egyszerre és méretének arányai azok az érvek, amelyek a NeXtime Time Zones-t kiváló választássá teszik. Válassza a Kérelem létrehozása – Fájlból – Mappából – Mappa kiválasztása – OK
- Miután megjelenik a fájlok listája, nyomja meg a gombot Változtat
- A Lekérdezésszerkesztő ablakban bontsa ki a Bináris oszlopot egy dupla nyíllal, és válassza ki az egyes fájlokból átvenni kívánt lapnevet
És ennyi! Dal!
- A kereszttábla áttervezése laposra, amely alkalmas forgóasztalok építésére
- Animált buborékdiagram készítése a Power View-ban
- Makró különböző Excel-fájlok lapjainak összeállításához