szerelvény táblázatok különböző excel fájlok hálózati lekérdezés

Nyilatkozat a problémát

Nézzük meg egy szép megoldás, hogy egy nagyon gyakori helyzetek, amelyek előbb-utóbb szembe kell néznie a legtöbb Excel felhasználóknak meg kell, hogy gyorsan és automatikusan összegyűjti az adatokat a nagyszámú fájlokat egy összefoglaló táblázatban.

Tegyük fel, hogy van egy olyan mappa, amely több adatfájlok az ágak-városok:

A fájlok száma nem játszik szerepet, és a jövőben változhat. Minden fájlnak van egy listája nevű Sales. ahol van egy tábla adatokkal:

A sorok száma (megbízásokat) a táblázatokban, self-egy, különböző, de minden szabványos oszlopok.

Célkitűzés: adatokat gyűjteni az összes fájlt egy könyvet, majd automatikusan frissíti, ha hozzá, törölje másolat fájlokat, városok vagy sorok a táblázatokban. By konszolidált döntő asztal akkor lehet építeni bármilyen jelentések, táblázatok, szűrő, sort adatok stb A legfontosabb dolog -, hogy képes összegyűjteni.

Vedd fel a fegyvereket

1. lépés: Egyetlen fájl importálása, mint egy modell

Először is nézzük adatokat importálhat egy könyvet példaként Excel „felvette az ötletet.” Ehhez hozzon létre egy új, üres munkafüzetet, és.

Ez egy nagyon hatékony eszköz, amely lehetővé teszi, hogy „kész” az asztal alatt azt akarják nézni. Még egy felületes leírása minden funkciója venne egy száz oldalas, de ha nagyon röviden, akkor használja ezt az ablakot akkor:

• kiszűrni a felesleges adatokat, üres sorok, sorban hibák
• rendezés adatokat egy vagy több oszlopot
• megszabadulni az ismétlések
• Ossza agglutinálják szöveges oszlop (az elválasztó, a karakterek száma, stb)
• mert a szöveget annak érdekében (hogy eltávolítsuk a felesleges szóközöket, korrekt nyilvántartás stb)
• minden lehetséges módon lehet átalakítani adattípusok (számok szöveges átalakulhat normális számokat vagy fordítva)
• átültetés (elforgatott) kétdimenziós asztal és bővíteni a kereszt-táblázat a lapos
• így további oszlopok a táblában, és használja őket, hogy a képletek és függvények épített a Power Query nyelv M.
• .

Például, tegyük hozzá egy oszlopot a táblázatban a szöveg a hónap nevét, hogy később könnyebb volt, hogy építsenek egy Kimutatás jelentéseket. Ehhez kattintson jobb gombbal a fejléc Dátum oszlop, és válassza ki Duplicate oszlopon (Ismétlődő oszlop). majd kattintson a jobb gombbal a fejlécben egy oszlop megjelenő ismétlődő majd kattintson a Konvertálás - hónap - A hónap.

Kell egy új oszlopot a szöveget a hónap nevét minden egyes sorban. Duplán kattintva az oszlop fejlécére, akkor nevezd a másolás időpontja kényelmesebb hónapban. például.

Ha néhány oszlop program nem egészen helyesen azonosítja az adatok típusát, hogy segíthet kattintva ikonméretet a bal oldalon az egyes oszlopok:

Kizárása vonal hibák vagy üres string, valamint a felesleges vezetők, illetve az ügyfelek lehet egy egyszerű szűrő:

És minden történt az átalakítás rögzítik a jobb oldalon, ahol mindig lehet visszafordítani (X), vagy módosítsa a beállításokat (fogaskerék):

Egyszerű és elegáns, nem?

2. lépés: Átalakítás a funkció megkeresésünkre

Ezt követően megismételni minden történt az adatok átalakítása minden behozott könyv, meg kell konvertálni a kialakított kérelmet a funkció, ami aztán felhasználható, viszont minden kedves fájlokat. Ehhez, sőt, nagyon egyszerű.

Most óvatosan tesszük pár szerkesztések:

Jelentésük egyszerű: az első sorban (filepath) => fordul az eljárás olyan funkciót érv útvonalat. alatt és megváltoztatjuk a rögzített útvonal az értéke ennek a változónak.

Minden. Kattintson a Befejezés gombra, és meg kell látni:

Ne ijedjen meg, hogy a hiányzó adatokat - sőt, minden rendben van, minden úgy van, ahogy kell kinéznie :) Sikeresen létre a felhasználó által definiált függvényt, ami eszébe jutott, az egész algoritmus és import adatokat anélkül, hogy kötve, hogy egy adott fájlt. Továbbra is, hogy ez egy értelmes nevet (pl getData) a jobb oldali panelen, a Név mezőben, és akkor nyomja Home - bezárása és a terhelés (Home - Close and Load). Megjegyezzük, hogy a kód kódolva a fájl elérési útját, amit behozott például. Ön visszatér a fő Microsoft Excel ablakot, de meg kell jelennie a jobb oldalon, hogy hozzon létre egy kapcsolatot a funkció:

3. lépés: összegyűjti az összes fájl

Most meg kell adni, hogy a tábla egy másik oszlopon mi létrehozunk egy függvényt, hogy a „pull” adatokat minden fájlt. Ehhez menj a lap Oszlop hozzáadása - Egyedi oszlopon (Add Column - Add Custom oszlop), és a megjelenő ablakban adja meg a függvény getData. mutatva meg azt a teljes elérési utat minden file:

Miután az OK gombra kattintva hozzon létre egy oszlopot felvenni a táblázatban a jobb oldalon.

Most távolítsa el az összes felesleges oszlopok (például az Excel, a jobb egérgombbal - Törlés), így csak a hozzáadott egy oszlop és egy oszlopot egy fájlnevet a neve (vagy inkább - a városban) hasznos lesz, hogy végleges adatok minden sorban.

És most „wow pillanat” - kattintson az ikonra a előirányzat a nyilak a jobb felső sarokban az oszlop beszállítási funkció:

törölje a jelet használja az eredeti oszlop nevét, mint egy előtag (Használja az eredeti oszlop neve előtag) és kattintson az OK gombra. És a funkció betöltéséhez és feldolgozza az adatokat az egyes fájlok követve az algoritmus rögzíteni és összegyűjtötte az összes az egész táblázatot:

A teljes szépségét is távolítsa el a hosszabbító .xlsx az első oszlopot a fájlok neveit - a standard változott „semmi” (a jobb gombbal az oszlop fejlécében - Cserélje) és nevezzük az oszlop City. És helyes adatok formátuma az oszlopban a dátum.

Mindent! Kattintson a fő - bezárása és a terhelés (Home - bezárása Load). Minden által gyűjtött adatok azt kérdezi a városok ki kell rakni az Excel táblázatot a jelenlegi formátumban „intelligens tábla”:

Kapcsolat létrehozásához és építsük funkciót nem kell külön fenntartása - ezek együtt menti az aktuális fájlt a szokásos módon.

A jövőben bármilyen változás a mappa (hozzáadás, törlés városok) vagy fájlokat (módosíthatja a sorok számát) elég lesz, hogy a jobb gombbal közvetlenül a tábla vagy lekérdezés a jobb oldali panelen, és válassza a Refresh (Frissítés) - Power lekérdezés „újraépíteni” minden adatot újra pár másodperc alatt.

Kapcsolódó linkek

Továbbfejlesztett „össze Binaries” élmény, ha az importáló bármely mappából

Mielőtt ez a kiadás, a felhasználók tudta kombinálni szöveg vagy CSV-fájlok csak. A kombájn nem fog működni más támogatott fájlformátumok (például Excel munkafüzeteket, JSON fájlok, stb), és ez nem teszik a szükséges átalakítások minden fájlt, mielőtt egyesíti őket egy asztalra (mint például eltávolítása az első sorban fejlécelemei).

Most, mint a hogyan legyen boldog és tapsoltak, mert Most ez nem szükséges, hogy megrontották kézzel módosítani a kódot.

Ugyanakkor (a munkám), amikor feltölti a fájlokat 1C az .xlsx formátumban, Teljesítmény Kérdés még mindig nem tudja lenyelni őket, amíg csak nem mentse el újra a fájlt az Excel (fájl megnyitása, mentése közel).

Az osztály! Csak tedd a frissítés, és megpróbálta: a betöltött fájl időnként közvetlenül funkció nélkül - két kattintással. Ahhoz, hogy. Azt, hogy a frissítéseket gyorsabban, mint írok cikkeket

Köszönöm, Basil! Te az én napom!