Hozzon létre táblázatokat különböző fejlécekkel több könyvből

A probléma megfogalmazása

Több fájlunk van (példánkban – 4 darab, általános esetben – amennyit csak akar) egy mappában Jelentések:

Belül ezek a fájlok így néznek ki:

Ahol:

• A szükséges adatlapot mindig hívjuk képek, de bárhol szerepelhet a munkafüzetben.
  
• A lapon túl képek Minden könyvnek lehetnek más lapjai is.
• Az adatokat tartalmazó táblázatok sorszáma eltérő, és a munkalapon eltérő sorral kezdődhetnek.
• A különböző táblákban lévő azonos oszlopok neve eltérhet (pl. Mennyiség = Mennyiség = Mennyiség).
• A táblázatok oszlopai eltérő sorrendben is elrendezhetők.

Feladat: az összes fájl értékesítési adatait gyűjtsük össze a lapról képek egy közös táblázatba, hogy a későbbiekben összefoglalót vagy bármilyen más elemzést építsünk rá.

1. lépés: Oszlopnevek könyvtárának elkészítése

Az első teendő, hogy készítsen egy kézikönyvet az oszlopnevek összes lehetséges lehetőségével és azok helyes értelmezésével:

Ezt a listát dinamikus „intelligens” táblázattá alakítjuk a lapon található Táblázat formázása gombbal Kezdőlap (Főoldal — Táblázat formázása) vagy billentyűparancsot Ctrl+T és a paranccsal töltse be a Power Querybe Adatok – Táblázatból/Tartományból (Adatok – táblázatból/tartományból). Az Excel legutóbbi verzióiban átnevezték erre Levélekkel (lapról).

A Power Query lekérdezésszerkesztő ablakában hagyományosan töröljük a lépést Megváltozott típus és a gombra kattintva adjon hozzá egy új lépést helyette f_xa képletsorban (ha nem látható, akkor a lapon engedélyezheti Felülvizsgálat), és írja be a képletet a beépített M Power Query nyelven:

=Table.ToRows(Forrás)

Ez a parancs átalakítja az előző lépésben betöltött parancsot forrás referenciatáblázatot beágyazott listákból álló listává (List), amelyek mindegyike egy értékpár Volt-lett egy sorból:

Az ilyen típusú adatokra kicsit később lesz szükségünk, amikor az összes betöltött tábla fejléceit tömegesen nevezzük át.

Az átalakítás befejezése után válassza ki a parancsokat Kezdőlap — Bezárás és betöltés — Bezárás és betöltés… és az import típusa Csak hozzon létre egy kapcsolatot (Főoldal — Bezárás&Betöltés — Bezárás&Betöltés ide... — Csak kapcsolat létrehozása) és menj vissza az Excelbe.

2. lépés: Az összes fájlból mindent úgy töltünk be, ahogy van

Most töltsük be az összes fájl tartalmát a mappából – egyelőre úgy, ahogy van. Csapatok kiválasztása Adatok – Adatok lekérése – Fájlból – Mappából (Adatok — Adatok lekérése — Fájlból — Mappából) majd azt a mappát, ahol a forráskönyveink vannak.

Az előnézeti ablakban kattintson a gombra Megtérít (Átalakítás) or Változtat (Edit):

Ezután bontsa ki az összes letöltött fájl tartalmát (Bináris) gombot dupla nyíllal az oszlop fejlécében Tartalom:

Power Query az első fájl példáján (Vostok.xlsx) megkérdezi tőlünk az egyes munkafüzetekből kivenni kívánt lap nevét – válasszon képek és nyomja meg az OK gombot:

Ezt követően (valójában) több, a felhasználó számára nem nyilvánvaló esemény következik be, amelyek következményei jól láthatóak a bal oldali panelen:

1. A Power Query átveszi az első fájlt a mappából (mi lesz Vostok.xlsx — lát Fájl példa) példaként, és lekérdezés létrehozásával importálja annak tartalmát Mintafájl konvertálása. Ez a lekérdezés néhány egyszerű lépést tartalmaz, mint pl forrás (fájl hozzáférés) Navigáció (lapkiválasztás) és esetleg a címek emelése. Ez a kérés csak egy adott fájlból tud adatokat betölteni Vostok.xlsx.
   
2. A kérés alapján létrejön a hozzá tartozó függvény Fájl konvertálása (jellemző ikon jelzi f_x), ahol a forrásfájl már nem állandó, hanem változó érték – paraméter. Így ez a függvény bármely könyvből kinyerhet olyan adatokat, amelyeket argumentumként belecsúsztunk.
   
3. A függvény felváltva kerül alkalmazásra az oszlop minden fájljára (binárisan). Tartalom – lépés felelős ezért Egyéni funkció hívása a lekérdezésünkben, amely egy oszlopot ad hozzá a fájlok listájához Fájl konvertálása az egyes munkafüzetekből importált eredményekkel:

   

4. A további oszlopok eltávolításra kerülnek.
5. A beágyazott táblák tartalma kibővül (lépés Kibővített táblázat oszlop) – és az összes könyv adatgyűjtésének végeredményét látjuk:

   
   

3. lépés. Csiszolás

Az előző képernyőképen jól látható, hogy a közvetlen összeszerelés „ahogy van” rossz minőségűnek bizonyult:

• Az oszlopok felcserélve vannak.
• Sok extra sor (üres és nem csak).
• A táblázat fejléceit nem tekinti fejlécnek, és összekeverik az adatokkal.
  

Ezeket a problémákat nagyon egyszerűen kijavíthatja – csak módosítsa a Mintafájl konvertálása lekérdezést. Az általunk elvégzett összes módosítás automatikusan a hozzá tartozó Fájl konvertálása funkcióba kerül, ami azt jelenti, hogy később az egyes fájlokból származó adatok importálásakor használatosak lesznek.

Egy kérés megnyitásával Mintafájl konvertálása, adjon hozzá lépéseket a szükségtelen sorok szűréséhez (például oszlop szerint Column2) és a gombbal emelje fel a címsorokat Használja az első sort fejlécként (Használja az első sort fejlécként). Az asztal sokkal jobban fog kinézni.

Ahhoz, hogy a különböző fájlokból származó oszlopok később automatikusan egymás alá illeszkedjenek, azonos nevet kell adni. Egy ilyen tömeges átnevezést végrehajthat egy korábban létrehozott könyvtár szerint, egy sor M-kóddal. Nyomjuk meg újra a gombot f_x a képletsorban, és adjon hozzá egy függvényt a módosításhoz:

= Táblázat.Oszlopok átnevezése (#”Emellett fejlécek”, Fejlécek, hiányzó mező. Figyelmen kívül hagyás)

Ez a függvény átveszi az előző lépés táblázatát Emelt fejlécek és a benne lévő összes oszlopot átnevezi a beágyazott keresési lista szerint Headlines. Harmadik érv Hiányzó mező. Figyelmen kívül hagyja azért van szükség, hogy azokon a címsorokon, amelyek a címtárban vannak, de nem szerepelnek a táblában, ne forduljon elő hiba.

Valójában ez minden.

Visszatérve a kérésre Jelentések teljesen más képet fogunk látni – sokkal szebb, mint az előző:

• Mi az a Power Query, Power Pivot, Power BI, és miért van rájuk szüksége egy Excel-felhasználónak
  
• Adatgyűjtés egy adott mappában lévő összes fájlból
• Adatgyűjtés a könyv összes lapjáról egy táblázatba