Lentelių surinkimas iš skirtingų „Excel“ failų naudojant „Power Query“.

Problemos formulavimas

Pažvelkime į puikų sprendimą vienai iš labai standartinių situacijų, su kuriomis anksčiau ar vėliau susiduria dauguma „Excel“ vartotojų: reikia greitai ir automatiškai surinkti duomenis iš daugybės failų į vieną galutinę lentelę. 

Tarkime, kad turime šį aplanką, kuriame yra keli failai su duomenimis iš filialų miestų:

Failų skaičius neturi reikšmės ir gali keistis ateityje. Kiekvienas failas turi lapą pavadinimu Pardavimaskur yra duomenų lentelė:

Lentelių eilučių (užsakymų) skaičius, žinoma, skiriasi, tačiau stulpelių rinkinys visur yra standartinis.

Užduotis: surinkti duomenis iš visų failų į vieną knygą su vėlesniu automatiniu atnaujinimu, kai pridedami arba ištrinami miesto failai ar lentelės eilutės. Pagal galutinę konsoliduotą lentelę bus galima kurti bet kokias ataskaitas, suvestines lenteles, filtruoti-rūšiuoti duomenis ir pan. Svarbiausia, kad būtų galima rinkti.

Mes pasirenkame ginklus

Sprendimui mums reikia naujausios „Excel 2016“ versijos (būtina funkcija jau įdiegta pagal numatytuosius nustatymus) arba ankstesnių „Excel 2010–2013“ versijų su įdiegtu nemokamu priedu. „Power Query“ iš Microsoft (atsisiųskite jį čia). „Power Query“ yra ypač lankstus ir itin galingas įrankis, skirtas duomenims iš išorinio pasaulio įkelti į „Excel“, tada juos pašalinti ir apdoroti. „Power Query“ palaiko beveik visus esamus duomenų šaltinius – nuo ​​tekstinių failų iki SQL ir net „Facebook“ 🙂

Jei neturite „Excel 2013“ ar „Excel 2016“, tada negalite toliau skaityti (juokauju). Senesnėse „Excel“ versijose tokią užduotį galima atlikti tik programuojant makrokomandą „Visual Basic“ programoje (tai labai sunku pradedantiesiems) arba atliekant monotonišką rankinį kopijavimą (tai užtrunka ilgai ir generuoja klaidas).

1 veiksmas. Importuokite vieną failą kaip pavyzdį

Pirma, kaip pavyzdį importuokime duomenis iš vienos darbaknygės, kad „Excel“ „pasiimtų idėją“. Norėdami tai padaryti, sukurkite naują tuščią darbaknygę ir…

• jei turite „Excel 2016“, atidarykite skirtuką Duomenys ir tada Sukurti užklausą – iš failo – iš knygos (Duomenys – nauja užklausa – iš failo – iš „Excel“)
• jei turite „Excel 2010–2013“ su įdiegtu „Power Query“ priedu, atidarykite skirtuką „Power Query“ ir pasirinkite ant jo Iš bylos – Iš knygos (Iš failo – iš Excel)

Tada atsidariusiame lange eikite į mūsų aplanką su ataskaitomis ir pasirinkite bet kurį miesto failą (nesvarbu, kurį, nes jie visi yra tipiški). Po poros sekundžių turėtų pasirodyti Navigator langas, kuriame kairėje pusėje reikia pasirinkti mums reikalingą lapą (Pardavimas), o jo turinys bus rodomas dešinėje:

Jei paspausite mygtuką apatiniame dešiniajame šio lango kampe parsisiųsti (Įkelti), tada lentelė bus nedelsiant importuota į lapą pradine forma. Vienam failui tai gerai, bet turime įkelti daug tokių failų, todėl eisime šiek tiek kitaip ir spustelėkite mygtuką Taisymas (Redaguoti). Po to Power Query užklausų rengyklė turėtų būti rodoma atskirame lange su mūsų duomenimis iš knygos:

Tai labai galingas įrankis, leidžiantis „pabaigti“ lentelę pagal mums reikalingą vaizdą. Net paviršutiniškas visų jo funkcijų aprašymas užtruktų apie šimtą puslapių, tačiau, jei labai trumpai, naudodamiesi šiuo langu galite:

• filtruoti nereikalingus duomenis, tuščias eilutes, eilutes su klaidomis
• rūšiuoti duomenis pagal vieną ar daugiau stulpelių
• atsikratyti pasikartojimo
• padalykite lipnų tekstą stulpeliais (pagal skyriklius, simbolių skaičių ir pan.)
• sutvarkykite tekstą (pašalinkite papildomus tarpus, taisykite didžiąsias ir mažąsias raides ir pan.)
• visais įmanomais būdais konvertuoti duomenų tipus (paversti skaičius, pavyzdžiui, tekstą, įprastais skaičiais ir atvirkščiai)
• perkelti (pasukti) lenteles ir išplėsti dvimates kryžmines lenteles į plokščias
• pridėkite papildomų stulpelių į lentelę ir naudokite juose formules bei funkcijas naudodami Power Query integruotą M kalbą.
• ...

Pavyzdžiui, į savo lentelę įtraukime stulpelį su mėnesio tekstiniu pavadinimu, kad vėliau būtų lengviau kurti suvestinės lentelės ataskaitas. Norėdami tai padaryti, dešiniuoju pelės mygtuku spustelėkite stulpelio antraštę duomenysir pasirinkite komandą Pasikartoti stulpelį (Dubliuoti stulpelį), tada dešiniuoju pelės mygtuku spustelėkite pasikartojančio stulpelio antraštę ir pasirinkite Komandos Transformacija – mėnuo – mėnesio pavadinimas:

Turi būti sudarytas naujas stulpelis su kiekvienos eilutės tekstiniais mėnesio pavadinimais. Dukart spustelėję stulpelio antraštę galite pervardyti iš Kopijuoti datą į patogesnę Mėnuo, pvz.

Jei kai kuriuose stulpeliuose programa ne visai teisingai atpažino duomenų tipą, tai galite padėti spustelėdami formato piktogramą kairėje kiekvieno stulpelio pusėje:

Galite išskirti eilutes su klaidomis arba tuščias eilutes, taip pat nereikalingus vadovus ar klientus naudodami paprastą filtrą:

Be to, visos atliktos transformacijos fiksuojamos dešiniajame skydelyje, kur jas visada galima atsukti atgal (kryžius) arba pakeisti parametrus (pavarą):

Lengva ir elegantiška, ar ne?

2 veiksmas. Paverskime savo užklausą funkcija

Norėdami vėliau pakartoti visas kiekvienos importuotos knygos duomenų transformacijas, sukurtą užklausą turime konvertuoti į funkciją, kuri savo ruožtu bus pritaikyta visiems mūsų failams. Tai padaryti iš tikrųjų labai paprasta.

Užklausų rengyklėje eikite į skirtuką Rodinys ir spustelėkite mygtuką Išplėstinis redaktorius (Žiūrėti – išplėstinis redaktorius). Turėtų atsidaryti langas, kuriame visi ankstesni veiksmai bus parašyti kodo forma M kalba. Atminkite, kad failo, kurį importavome pavyzdyje, kelias yra užkoduotas kode:

Dabar atlikime keletą koregavimų:

Jų reikšmė paprasta: pirmoji eilutė (failo kelias)=> paverčia mūsų procedūrą funkcija su argumentu bylos kelias, o žemiau pakeičiame fiksuotą kelią į šio kintamojo reikšmę. 

Visi. Spustelėkite apdaila ir turėtum pamatyti tai:

Nebijokite, kad duomenys dingo – tiesą sakant, viskas gerai, viskas turėtų atrodyti taip 🙂 Sėkmingai sukūrėme savo pasirinktinę funkciją, kurioje įsimenamas visas duomenų importavimo ir apdorojimo algoritmas, nesusietas su konkrečiu failu . Belieka suteikti jam suprantamesnį pavadinimą (pvz gautiData) skydelyje dešinėje lauko pusėje Vardas ir tu gali pjauti Pagrindinis – uždarykite ir atsisiųskite (Pagrindinis – uždaryti ir įkelti). Atminkite, kad failo, kurį importavome pavyzdyje, kelias yra kode užkoduotas. Grįšite į pagrindinį „Microsoft Excel“ langą, tačiau dešinėje turėtų pasirodyti skydelis su sukurtu ryšiu su mūsų funkcija:

3 veiksmas. Visų failų surinkimas

Visa sunkiausia dalis atsilieka, maloni ir lengva dalis išlieka. Eikite į skirtuką Duomenys – Sukurti užklausą – Iš failo – Iš aplanko (Duomenys – nauja užklausa – iš failo – iš aplanko) arba, jei turite Excel 2010–2013, panašiai kaip skirtuke „Power Query“. Atsidariusiame lange nurodykite aplanką, kuriame yra visi šaltinio miesto failai, ir spustelėkite OK. Kitas veiksmas turėtų atidaryti langą, kuriame bus išvardyti visi šiame aplanke (ir jo poaplankiuose) rasti „Excel“ failai ir kiekvieno iš jų informacija:

Spauskite pokytis (Redaguoti) ir vėl patenkame į pažįstamą užklausų rengyklės langą.

Dabar į lentelę turime įtraukti dar vieną stulpelį su mūsų sukurta funkcija, kuri „ištrauks“ duomenis iš kiekvieno failo. Norėdami tai padaryti, eikite į skirtuką Pridėti stulpelį – tinkintas stulpelis (Pridėti stulpelį – pridėti pasirinktinį stulpelį) ir pasirodžiusiame lange įveskite mūsų funkciją gautiData, nurodydami kaip argumentą visą kelią į kiekvieną failą:

Spustelėję OK sukurtas stulpelis turėtų būti įtrauktas į mūsų lentelę dešinėje.

Dabar ištrinkite visus nereikalingus stulpelius (kaip „Excel“, dešiniuoju pelės mygtuku – pašalinti), paliekant tik pridėtą stulpelį ir stulpelį su failo pavadinimu, nes šį pavadinimą (tiksliau – miestą) bus naudinga turėti bendruose kiekvienos eilutės duomenyse.

O dabar „vau momentas“ – spustelėkite piktogramą su savo rodyklėmis viršutiniame dešiniajame pridėto stulpelio kampe su mūsų funkcija:

… atžymėti Naudokite pradinį stulpelio pavadinimą kaip priešdėlį (Kaip priešdėlį naudokite pradinį stulpelio pavadinimą)ir spauskite OK. Mūsų funkcija įkels ir apdoros duomenis iš kiekvieno failo, vadovaudamasi įrašytu algoritmu ir viską surinkdama į bendrą lentelę:

Norėdami visiško grožio, taip pat galite pašalinti .xlsx plėtinius iš pirmojo stulpelio su failų pavadinimais – standartiškai pakeisdami juos „nieko“ (dešiniuoju pelės mygtuku spustelėkite stulpelio antraštę – Pavaduojantis narys) ir pervardykite šį stulpelį į Miestas. Taip pat pataisykite duomenų formatą stulpelyje su data.

Viskas! Spustelėkite Pagrindinis puslapis – uždaryti ir įkelti (Pagrindinis – uždaryti ir įkelti). Visi pagal užklausą surinkti duomenys apie visus miestus bus įkelti į dabartinį Excel lapą „išmaniosios lentelės“ formatu:

Sukurto ryšio ir mūsų surinkimo funkcijos jokiu būdu nereikia saugoti atskirai – jie išsaugomi kartu su esamu failu įprastu būdu.

Ateityje, atlikus bet kokius pakeitimus aplanke (pridedant ar pašalinus miestus) arba failuose (pakeitus eilučių skaičių), pakaks dešiniuoju pelės mygtuku spustelėti tiesiai ant lentelės arba dešiniajame skydelyje esančios užklausos ir pasirinkti komandą Atnaujinkite ir išsaugokite (Atnaujinti) – „Power Query“ per kelias sekundes vėl „atkurs“ visus duomenis.

PS

Pataisa. Po 2017 m. sausio mėn. atnaujinimų Power Query išmoko pati rinkti Excel darbaknyges, ty nebereikia daryti atskiros funkcijos – tai vyksta automatiškai. Taigi antrojo žingsnio iš šio straipsnio nebereikia ir visas procesas tampa pastebimai paprastesnis:

1. Pasirinkti Sukurti užklausą – iš failo – iš aplanko – pasirinkite aplanką – gerai
2. Kai pasirodys failų sąrašas, paspauskite pokytis
3. Užklausų rengyklės lange išplėskite dvejetainį stulpelį dviguba rodykle ir pasirinkite lapo pavadinimą, kurį norite paimti iš kiekvieno failo

Ir viskas! Daina!

• Perprojektuokite skersinį stalą į plokščią, tinkamą sukamiesiems stalams statyti
• Animacinės burbulinės diagramos kūrimas Power View
• Makrokomandas, skirtas lakštams iš skirtingų Excel failų sujungti į vieną