Sujunkite duomenis iš kelių „Excel“ darbaknygių (naudodami „Power Query“)

„Power Query“ gali labai padėti, kai norite sujungti kelias darbaknyges į vieną darbaknygę.

Pvz., Tarkime, kad turite įvairių regionų (Rytų, Vakarų, Šiaurės ir Pietų) pardavimo duomenis. Šiuos duomenis iš skirtingų darbaknygių galite sujungti į vieną darbalapį naudodami „Power Query“.

Jei šias darbaknyges turite skirtingose ​​vietose/aplankuose, patartina jas perkelti į vieną aplanką (arba sukurti kopiją ir įdėti tą darbaknygės kopiją į tą patį aplanką).

Taigi, pirmiausia turiu keturias darbo knygas aplanke (kaip parodyta žemiau).

Dabar šioje pamokoje apžvelgiu tris scenarijus, kuriuose galite sujungti duomenis iš skirtingų darbaknygių naudodami „Power Query“:

• Kiekvienos darbaknygės duomenys yra „Excel“ lentelėje, o visų lentelių pavadinimai yra vienodi.
• Kiekvienoje darbaknygėje yra duomenys su tuo pačiu darbalapio pavadinimu. Taip gali būti, kai visose darbaknygėse yra lapas, pavadintas „santrauka“ arba „duomenys“, ir jūs norite visa tai sujungti.
• Kiekvienoje darbaknygėje yra daug lapų ir lentelių, ir jūs norite sujungti konkrečias lenteles/lapus. Šis metodas taip pat gali būti naudingas, kai norite sujungti lentelę/lapus, kurie neturi nuoseklaus pavadinimo.

Pažiūrėkime, kaip kiekvienu atveju sujungti šių darbo knygų duomenis.

Kiekvienos darbaknygės duomenys yra tos pačios struktūros „Excel“ lentelėje

Žemiau pateikta technika veiktų, kai jūsų „Excel“ lentelės būtų struktūrizuotos vienodai (tie patys stulpelių pavadinimai).

Kiekvienos lentelės eilučių skaičius gali skirtis.

Nesijaudinkite, jei kai kuriose „Excel“ lentelėse yra papildomų stulpelių. Galite pasirinkti vieną iš lentelių kaip šabloną (arba „raktą“, kaip tai vadina „Power Query“), o „Power Query“ naudotų jį, kad su juo sujungtų visas kitas „Excel“ lenteles.

Jei kitose lentelėse yra papildomų stulpelių, jie bus ignoruojami ir bus sujungti tik tie, kurie nurodyti šablone/rakte. Pvz., Jei pasirinktame šablone/raktų lentelėje yra 5 stulpeliai, o vienoje iš kitos darbaknygės lentelių yra 2 papildomi stulpeliai, tie papildomi stulpeliai bus ignoruojami.

Dabar aplanke turiu keturias darbaknyges, kurias noriu sujungti.

Žemiau yra lentelės, kurią turiu vienoje iš darbaknygių, momentinė nuotrauka.

Štai veiksmai, kaip sujungti šių darbaknygių duomenis į vieną darbaknygę (kaip vieną lentelę).

1. Eikite į skirtuką Duomenys.
2. Grupėje Gauti ir transformuoti spustelėkite išskleidžiamąjį meniu Nauja užklausa.
3. Užveskite žymeklį ant „Iš failo“ ir spustelėkite „Iš aplanko“.
4. Dialogo lange Aplankas įveskite aplanko, kuriame yra failai, failo kelią arba spustelėkite Naršyti ir suraskite aplanką.
5. Spustelėkite Gerai.
6. Atsidariusiame dialogo lange spustelėkite jungimo mygtuką.
7. Spustelėkite „Sujungti ir įkelti“.
8. Atsidariusiame dialogo lange „Sujungti failus“ kairėje srityje pasirinkite lentelę. Atminkite, kad „Power Query“ rodo lentelę iš pirmojo failo. Šis failas veiktų kaip šablonas (arba raktas) kitiems failams sujungti. „Power Query“ dabar ieškos „1 lentelės“ kitose darbaknygėse ir sujungs ją su šia.
9. Spustelėkite Gerai.

Galutinis rezultatas (bendri duomenys) bus įkeltas į jūsų aktyvų darbalapį.

Atminkite, kad kartu su duomenimis „Power Query“ automatiškai prideda darbaknygės pavadinimą kaip pirmąjį sujungtų duomenų stulpelį. Tai padeda sekti, kokie duomenys buvo gauti iš kokios darbo knygos.

Jei pirmiausia norite redaguoti duomenis prieš įkeldami juos į „Excel“, 6 veiksme pasirinkite „Sujungti ir redaguoti“. Tai atvers galutinį rezultatą „Power Query“ redaktoriuje, kuriame galėsite redaguoti duomenis.

Keletas dalykų, kuriuos reikia žinoti:

• Jei kaip šabloną pasirinksite „Excel“ lentelę (7 veiksme), „Power Query“ naudos šios lentelės stulpelių pavadinimus, kad sujungtų kitų lentelių duomenis. Jei kitose lentelėse yra papildomų stulpelių, jie bus ignoruojami. Jei tose kitose lentelėse nėra stulpelio, kuris yra jūsų šablonų lentelėje, „Power Query“ tiesiog užrašo „null“.
• Stulpeliai neturi būti tokia tvarka, kaip „Power Query“ naudoja stulpelių antraštes stulpeliams susieti.
• Kadangi kaip raktą pasirinkote 1 lentelę, „Power Query“ ieškos 1 lentelės visose darbaknygėse ir sujungs jas visas. Jei „Excel“ lentelė neranda to paties pavadinimo (šio pavyzdžio 1 lentelė), „Power Query“ pateiks klaidą.

Naujų failų pridėjimas prie aplanko

Dabar skirkime minutę ir suprasime, ką padarėme atlikdami aukščiau nurodytus veiksmus (tai užtruko tik kelias sekundes).

Keturių skirtingų darbaknygių duomenis sujungėme į vieną lentelę per kelias sekundes, net neatidarę nė vienos darbaknygės.

Bet tai dar ne viskas.

Tikroji „Power Query“ GALIA yra ta, kad dabar, kai į aplanką įtraukiate daugiau failų, jums nereikia kartoti nė vieno iš šių veiksmų.

Viskas, ką jums reikia padaryti, kad perkeltumėte naują darbaknygę į aplanką, atnaujintumėte užklausą ir ji automatiškai sujungtų visų to aplanko darbaknygių duomenis.

Pavyzdžiui, aukščiau pateiktame pavyzdyje, jei pridėsiu naują darbaknygę - „Mid-West.xlsx“ į aplanką ir atnaujinkite užklausą, ji iškart suteiks man naują kombinuotą duomenų rinkinį.

Štai kaip atnaujinti užklausą:

• Dešiniuoju pelės mygtuku spustelėkite „Excel“ lentelę, kurią įkėlėte į darbalapį, ir spustelėkite Atnaujinti.
• Dešiniuoju pelės mygtuku spustelėkite užklausą srityje „Darbaknygės užklausa“ ir spustelėkite Atnaujinti
• Eikite į skirtuką Duomenys ir spustelėkite Atnaujinti.

Kiekvienoje darbaknygėje yra duomenys su tuo pačiu darbalapio pavadinimu

Jei neturite duomenų „Excel“ lentelėje, bet visi lapų pavadinimai (iš kurių norite sujungti duomenis) yra vienodi, galite naudoti šiame skyriuje parodytą metodą.

Turite būti atsargūs, kai tai tik lentelės duomenys, o ne „Excel“ lentelė.

• Darbo lapų pavadinimai turi būti vienodi. Tai padės „Power Query“ peržiūrėti jūsų darbaknyges ir sujungti duomenis iš darbalapių, turinčių tą patį pavadinimą kiekvienoje darbaknygėje.
• „Power Query“ skiria didžiąsias ir mažąsias raides. Tai reiškia, kad darbalapis pavadinimu „duomenys“ ir „Duomenys“ laikomi skirtingais. Panašiai skirtingi laikomi stulpeliai su antrašte „Parduotuvė“ ir vienas su „parduotuvė“.
• Nors svarbu turėti tas pačias stulpelių antraštes, nėra svarbu turėti tą pačią tvarką. Jei „East.xlsx“ 2 stulpelis yra „West.xlsx“ 4 stulpelis, „Power Query“ jį teisingai atitiks, susiejusi antraštes.

Dabar pažiūrėkime, kaip greitai sujungti duomenis iš skirtingų darbaknygių, kuriose darbalapio pavadinimas yra tas pats.

Šiame pavyzdyje turiu aplanką su keturiais failais.

Kiekvienoje darbaknygėje turiu darbalapį pavadinimu „Duomenys“, kuriame yra tokio formato duomenys (atkreipkite dėmesį, kad tai nėra „Excel“ lentelė).

Štai veiksmai, kaip sujungti duomenis iš kelių darbaknygių į vieną darbalapį:

1. Eikite į skirtuką Duomenys.
2. Grupėje Gauti ir transformuoti spustelėkite išskleidžiamąjį meniu Nauja užklausa.
3. Užveskite žymeklį ant „Iš failo“ ir spustelėkite „Iš aplanko“.
4. Dialogo lange Aplankas įveskite aplanko, kuriame yra failai, failo kelią arba spustelėkite Naršyti ir suraskite aplanką.
5. Spustelėkite Gerai.
6. Atsidariusiame dialogo lange spustelėkite jungimo mygtuką.
7. Spustelėkite „Sujungti ir įkelti“.
8. Atsidariusiame dialogo lange „Sujungti failus“ kairėje srityje pasirinkite „Duomenys“. Atminkite, kad „Power Query“ rodo darbalapio pavadinimą iš pirmojo failo. Šis failas veiktų kaip raktas/šablonas kitiems failams sujungti. „Power Query“ peržiūrės kiekvieną darbaknygę, suras lapą pavadinimu „Duomenys“ ir sujungs visa tai.
9. Spustelėkite Gerai. Dabar „Power Query“ peržiūrės kiekvieną darbaknygę, joje suraskite darbalapį pavadinimu „Duomenys“ ir sujunkite visus šiuos duomenų rinkinius.

Galutinis rezultatas (bendri duomenys) bus įkeltas į jūsų aktyvų darbalapį.

Jei pirmiausia norite redaguoti duomenis prieš įkeldami juos į „Excel“, 6 veiksme pasirinkite „Sujungti ir redaguoti“. Tai atvers galutinį rezultatą „Power Query“ redaktoriuje, kuriame galėsite redaguoti duomenis.

Kiekvienoje darbaknygėje yra duomenų su skirtingais lentelių pavadinimais arba lapų pavadinimais

Kartais galite negauti struktūrinių ir nuoseklių duomenų (pvz., Lentelės tuo pačiu pavadinimu arba darbalapis tuo pačiu pavadinimu).

Pavyzdžiui, tarkime, kad gavote duomenis iš asmens, kuris sukūrė šiuos duomenų rinkinius, bet pavadino darbalapius kaip Rytų duomenys, Vakarų duomenys, Šiaurės duomenys ir Pietų duomenys.

Arba asmuo galėjo sukurti „Excel“ lenteles, tačiau skirtingais pavadinimais.

Tokiais atvejais vis tiek galite naudoti „Power Query“, tačiau turite tai padaryti atlikdami keletą papildomų veiksmų.

1. Eikite į skirtuką Duomenys.
2. Grupėje Gauti ir transformuoti spustelėkite išskleidžiamąjį meniu Nauja užklausa.
3. Užveskite žymeklį ant „Iš failo“ ir spustelėkite „Iš aplanko“.
4. Dialogo lange Aplankas įveskite aplanko, kuriame yra failai, failo kelią arba spustelėkite Naršyti ir suraskite aplanką.
5. Spustelėkite Gerai.
6. Atsidariusiame dialogo lange spustelėkite mygtuką Redaguoti. Bus atidarytas „Power Query“ redaktorius, kuriame pamatysite išsamią informaciją apie visus aplanke esančius failus.
7. Laikykite nuspaudę „Control“ klavišą ir pasirinkite stulpelius „Turinys“ ir „Pavadinimas“, dešiniuoju pelės mygtuku spustelėkite ir pasirinkite „Pašalinti kitus stulpelius“. Taip bus pašalinti visi kiti stulpeliai, išskyrus pasirinktus.
8. Užklausų rengyklės juostelėje spustelėkite „Pridėti stulpelį“, tada spustelėkite „Tinkintas stulpelis“.
9. Dialogo lange Pridėti tinkintą stulpelį pavadinkite naują stulpelį kaip „Duomenų importavimas“ ir naudokite šią formulę = „Excel“ darbo knyga ([TURINYS]). Atminkite, kad ši formulė skiria didžiųjų ir mažųjų raidžių ir ją turite įvesti tiksliai taip, kaip aš čia parodžiau.
10. Dabar pamatysite naują stulpelį, kuriame yra lentelė. Dabar leiskite man paaiškinti, kas čia atsitiko. Pateikėte „Power Query“ darbaknygių pavadinimus, o „Power Query“ iš kiekvienos darbaknygės (kuri šiuo metu yra lentelės lange) paėmė tokius objektus kaip darbalapiai, lentelės ir pavadinti diapazonai. Galite spustelėti tuščią vietą šalia teksto Lentelė ir apačioje pamatysite informaciją. Šiuo atveju, kadangi kiekvienoje darbaknygėje yra tik viena lentelė ir vienas darbalapis, galite matyti tik dvi eilutes.
11. Stulpelio „Duomenų importavimas“ viršuje spustelėkite dvigubos rodyklės piktogramą.
12. Atsidariusiame stulpelio duomenų laukelyje panaikinkite žymėjimą „Naudoti originalų stulpelį kaip priešdėlį“, tada spustelėkite Gerai.
13. Dabar pamatysite išplėstinę lentelę, kurioje matysite vieną eilutę kiekvienam lentelės objektui. Šiuo atveju kiekvienos darbaknygės lapo objektas ir lentelės objektas yra išvardyti atskirai.
14. Stulpelyje „Kind“ filtruokite sąrašą, kad būtų rodoma tik lentelė.
15. Laikykite nuspaudę valdymo klavišą ir pasirinkite stulpelį Pavadinimas ir duomenys. Dabar dešiniuoju pelės mygtuku spustelėkite ir pašalinkite visus kitus stulpelius.
16. Stulpelyje Duomenys spustelėkite dvigubos rodyklės piktogramą viršutiniame dešiniajame duomenų antraštės kampe.
17. Atsidariusiame stulpelio duomenų laukelyje spustelėkite Gerai. Tai sujungs visų lentelių duomenis ir bus rodoma „Power Query“.
18. Dabar galite atlikti bet kokią reikiamą transformaciją, tada eikite į skirtuką Pagrindinis ir spustelėkite Uždaryti ir įkelti.

Dabar leiskite man pabandyti ir greitai paaiškinti, ką mes čia padarėme. Kadangi lapų pavadinimuose ar lentelių pavadinimuose nebuvo nuoseklumo, naudojome = Excel. Darbaknygės formulę, kad gautume visus „Power Query“ darbaknygių objektus. Šie objektai gali apimti lapus, lenteles ir pavadintus diapazonus. Kai turėjome visus objektus iš visų failų, mes juos filtravome, kad galėtume atsižvelgti tik į „Excel“ lenteles. Tada išplėtėme duomenis lentelėse ir sujungėme visa tai.

Šiame pavyzdyje mes filtravome duomenis, kad galėtume naudoti tik „Excel“ lenteles (13 veiksme). Jei norite sujungti lapus, o ne lenteles, galite filtruoti lapus.

Pastaba - ši technika suteiks jums bendrus duomenis, net jei stulpelių pavadinimai neatitinka. Pvz., Jei „East.xlsx“ turite klaidingai parašytą stulpelį, gausite 5 stulpelius. „Power Query“ užpildys duomenis stulpeliuose, jei juos suras, o jei neras stulpelio, reikšmę nurodys kaip „null“.

Panašiai, jei bet kurioje lentelės darbalapyje yra papildomų stulpelių, jie bus įtraukti į galutinį rezultatą.

Dabar, jei gaunate daugiau darbaknygių, iš kurių reikia sujungti duomenis, tiesiog nukopijuokite ir įklijuokite jas į aplanką ir atnaujinkite „Power Query“