Naudojant „Power Query“ tapo lengviau dirbti su darbalapiuose ar net darbaknygėse išsklaidytais duomenimis.
Vienas iš dalykų, kai „Power Query“ gali sutaupyti daug laiko, yra tada, kai reikia sujungti skirtingo dydžio lenteles ir stulpelius pagal atitinkamą stulpelį.
Žemiau yra vaizdo įrašas, kuriame tiksliai parodau, kaip sujungti lenteles „Excel“ naudojant „Power Query“.
Jei norite skaityti tekstą, o ne žiūrėti vaizdo įrašą, toliau pateikiamos rašytinės instrukcijos.
Tarkime, kad turite lentelę, kaip parodyta žemiau:
Šioje lentelėje yra duomenys, kuriuos noriu naudoti, tačiau vis tiek trūksta dviejų svarbių stulpelių - „Produkto ID“ ir „Regionas“, kuriame veikia pardavimo atstovas.
Ši informacija pateikiama kaip atskiros lentelės, kaip parodyta žemiau:
Norėdami gauti visą šią informaciją į vieną lentelę, turėsite sujungti šias tris lenteles, kad galėtumėte sukurti „Pivot“ lentelę ir ją išanalizuoti arba naudoti kitiems ataskaitų teikimo/informacijos suvestinės tikslams.
Sujungdamas neturiu omenyje paprastos kopijos.
Turėsite susieti atitinkamus 1 lentelės įrašus su 2 ir 3 lentelės duomenimis.
Dabar galite tai padaryti pasikliaudami VLOOKUP arba INDEX/MATCH.
Arba, jei esate VBA šmaikštuolis, galite parašyti kodą, kad tai padarytumėte.
Tačiau šios parinktys yra daug laiko ir sudėtingos, palyginti su „Power Query“.
Šioje pamokoje parodysiu, kaip sujungti šias tris „Excel“ lenteles į vieną.
Kad ši technika veiktų, turite turėti jungiamuosius stulpelius. Pavyzdžiui, 1 ir 2 lentelėse bendras stulpelis yra „Prekė“, o 1 ir 3 lentelėse - „Pardavimų atstovas“. Taip pat atkreipkite dėmesį, kad šiuose jungiamuosiuose stulpeliuose neturėtų būti kartojimo.Pastaba: „Power Query“ gali būti naudojamas kaip priedas programoje „Excel 2010“ ir 2013 m. Ir yra integruota funkcija nuo „Excel 2016“. Atsižvelgiant į jūsų versiją, kai kurie vaizdai gali atrodyti kitaip (šioje pamokoje naudojami vaizdai yra iš „Excel 2016“).
Sujunkite lenteles naudodami „Power Query“
Aš pavadinau šias lenteles, kaip parodyta žemiau:
- 1 lentelė - Sales_Data
- 2 lentelė - Pdt_Id
- 3 lentelė. Regionas
Nebūtina pervadinti šių lentelių, tačiau geriau duoti pavadinimus, apibūdinančius, apie ką yra lentelė.
Vienu metu galite sujungti tik dvi „Power Query“ lenteles.
Taigi pirmiausia turėsime sujungti 1 lentelę ir 2 lentelę, o tada kitame etape į ją sujungti 3 lentelę.
1 lentelės ir 2 lentelės sujungimas
Norėdami sujungti lenteles, pirmiausia turite konvertuoti šias lenteles į jungtis „Power Query“. Kai turėsite ryšius, galėsite lengvai juos sujungti.
Štai kaip išsaugoti „Excel“ lentelę kaip ryšį „Power Query“:
- Lentelėje „Sales_Data“ pasirinkite bet kurį langelį.
- Spustelėkite skirtuką Duomenys.
- Grupėje „Gauti ir transformuoti“ spustelėkite „Iš lentelės/diapazono“. Tai atidarys užklausos redaktorių.
- Užklausų redaktoriuje spustelėkite skirtuką „Failas“.
- Spustelėkite parinktį „Uždaryti ir įkelti“.
- Dialogo lange „Importuoti duomenis“ pasirinkite „Tik sukurti ryšį“.
- Spustelėkite Gerai.
Pirmiau minėti veiksmai sukurtų ryšį su pavadinimu „Sales_Data“ (arba bet kokiu pavadinimu, kurį davėte „Excel“ lentelei).
Pakartokite aukščiau nurodytus veiksmus 2 ir 3 lentelėms.
Taigi, kai baigsite, turėsite tris ryšius (pavadinimu „Sales_Data“, „Pdt_Id“ ir „Region“).
Dabar pažiūrėkime, kaip sujungti „Sales_Data“ ir „Pdt_Id“ lenteles.
- Spustelėkite skirtuką Duomenys.
- Grupėje Gauti ir transformuoti duomenis spustelėkite Gauti duomenis.
- Išskleidžiamajame meniu spustelėkite Sujungti užklausas.
- Spustelėkite Sujungti. Bus atidarytas dialogo langas Sujungti.
- Sujungimo dialogo lange iš pirmojo išskleidžiamojo meniu pasirinkite „Sales_Data“.
- Antrame išskleidžiamajame meniu pasirinkite „Pdt_Id“.
- „Sales_Data“ peržiūroje spustelėkite stulpelį „Prekė“. Tai atlikus bus pasirinktas visas stulpelis.
- „Pdt_Id“ peržiūroje spustelėkite stulpelį „Elementas“. Tai atlikus bus pasirinktas visas stulpelis.
- Išskleidžiamajame meniu „Join Kind“ pasirinkite „Left Outer“ (visi iš pirmojo, atitinkantys iš antro).
- Spustelėkite Gerai.
Atlikus aukščiau nurodytus veiksmus, būtų atidarytas užklausos redaktorius ir parodyti duomenys iš „Sales_Data“ su vienu papildomu stulpeliu („Pdt_Id“).
„Excel“ lentelių sujungimas (1 ir 2 lentelės)
Dabar lentelių sujungimo procesas vyks užklausos redaktoriuje, atliekant šiuos veiksmus:
- Papildomame stulpelyje (Pdt_Id) antraštėje spustelėkite rodyklę su dviem smailėmis.
- Atsidariusiame parinkčių laukelyje panaikinkite visų stulpelių pavadinimų žymėjimą ir pasirinkite tik elementą. Taip yra todėl, kad esamoje lentelėje jau turime produkto pavadinimo stulpelį ir norime tik kiekvieno produkto produkto ID.
- Atžymėkite parinktį „Naudoti pradinį stulpelio pavadinimą kaip priešdėlį“.
- Spustelėkite Gerai.
Taip gausite gautą lentelę, kurioje yra visi įrašai iš lentelės „Sales_Data“, ir papildomas stulpelis, kuriame taip pat yra produktų ID (iš lentelės „Pdt_Id“).
Dabar, jei norite sujungti tik dvi lenteles, galite įkelti šią „Excel“.
Tačiau turime sujungti tris lenteles, todėl reikia daugiau nuveikti.
Turite išsaugoti šią gautą lentelę kaip ryšį (kad galėtume ją sujungti su 3 lentele).
Štai šie veiksmai, kaip išsaugoti šią sujungtą lentelę (su duomenimis iš lentelių „Sales_Data“ ir „Pdt_Id“):
- Spustelėkite skirtuką Failas
- Spustelėkite parinktį „Uždaryti ir įkelti“.
- Dialogo lange „Importuoti duomenis“ pasirinkite „Tik sukurti ryšį“.
- Spustelėkite Gerai.
Tai išsaugos naujai sujungtus duomenis kaip ryšį. Jei norite, galite pervardyti šį ryšį.
3 lentelės sujungimas su gauta lentele
Trečiosios lentelės sujungimas su gauta lentele (kurią gavome sujungę 1 lentelę ir 2 lentelę) yra visiškai tas pats.
Štai šie lentelių sujungimo veiksmai:
- Spustelėkite skirtuką Duomenys.
- Grupėje „Gauti ir transformuoti duomenis“ spustelėkite „Gauti duomenis“.
- Išskleidžiamajame meniu spustelėkite „Sujungti užklausas“.
- Spustelėkite „Sujungti“. Bus atidarytas dialogo langas Sujungti.
- Sujungimo dialogo lange iš pirmojo išskleidžiamojo meniu pasirinkite „Sujungti1“.
- Antrame išskleidžiamajame meniu pasirinkite „Regionas“.
- „Merge1“ peržiūroje spustelėkite stulpelį „Sales Rep“. Tai atlikus bus pasirinktas visas stulpelis.
- Regiono peržiūroje spustelėkite stulpelį „Pardavimų atstovas“. Tai atlikus bus pasirinktas visas stulpelis.
- Išskleidžiamajame meniu „Prisijungti prie rūšies“ pasirinkite Kairė išorė (viskas iš pirmo, atitinka iš antros).
- Spustelėkite Gerai.
Atlikus aukščiau nurodytus veiksmus, būtų atidarytas užklausų redaktorius ir rodomi „Merge1“ duomenys su vienu papildomu stulpeliu (Regionas).
Dabar lentelių sujungimo procesas vyks užklausos redaktoriuje, atliekant šiuos veiksmus:
- Papildomame stulpelyje (Regionas) antraštėje spustelėkite rodyklę su dviem smailėmis.
- Atsidariusiame parinkčių laukelyje panaikinkite visų stulpelių pavadinimų žymėjimą ir pasirinkite tik Regionas.
- Atžymėkite parinktį „Naudoti pradinį stulpelio pavadinimą kaip priešdėlį“.
- Spustelėkite Gerai.
Pirmiau minėti veiksmai suteiks jums lentelę, kurioje visos trys lentelės yra sujungtos („Sales_Data“ lentelė su vienu stulpeliu „Pdt_Id“ ir viena - regionui).
Štai kaip įkelti šią lentelę į „Excel“:
- Spustelėkite skirtuką Failas.
- Spustelėkite „Uždaryti ir įkelti“.
- Dialogo lange „Importuoti duomenis“ pasirinkite Lentelės ir Naujų darbalapių parinktys.
- Spustelėkite Gerai.
Taip gautumėte sujungtą lentelę naujame darbalapyje.
Vienas geriausių „Power Query“ dalykų yra tai, kad galite lengvai pritaikyti bet kokius pagrindinių duomenų pakeitimus (1, 2 ir 3 lentelės), tiesiog juos atnaujindami.
Pvz., Tarkime, kad Laura perkeliama į Aziją ir jūs gaunate naujus kito mėnesio duomenis. Dabar jums nereikia pakartoti aukščiau aprašytų veiksmų. Viskas, ką jums reikia padaryti, tai atnaujinti stalą ir jis viską padarys iš naujo.
Per kelias sekundes turėsite naują sujungtą lentelę.
Jums taip pat gali patikti šie „Power Query“ vadovėliai:
- Sujunkite duomenis iš kelių „Excel“ darbaknygių (naudodami „Power Query“).
- Sujunkite duomenis iš kelių darbalapių į vieną „Excel“ darbalapį.
- Kaip atsukti duomenis „Excel“ naudojant „Power Query“ (dar vadinamą „Get & Transform“)
- Gaukite failų pavadinimų sąrašą iš aplankų ir poaplankių (naudodami „Power Query“)
