Power Query abil on töölehtede või isegi töövihikute vahel hajutatud andmetega töötamine muutunud lihtsamaks.
Üks asi, kus Power Query võib palju aega säästa, on see, kui peate sobiva veeru põhjal ühendama erineva suurusega tabelid ja veerud.
Allpool on video, kus näitan täpselt, kuidas Excelis tabeleid Power Query abil ühendada.
Kui eelistate teksti vaatamist video vaatamise asemel, siis allpool on kirjalikud juhised.
Oletame, et teil on järgmine tabel:
Selles tabelis on andmed, mida ma tahan kasutada, kuid sellel puuduvad endiselt kaks olulist veergu - „Toote ID” ja „Piirkond”, kus müügiesindaja tegutseb.
See teave on esitatud eraldi tabelitena, nagu on näidatud allpool:
Kogu selle teabe koondamiseks ühte tabelisse peate need kolm tabelit ühendama, et saaksite seejärel luua liigendtabeli ja seda analüüsida või kasutada seda muudel aruandluse/juhtpaneeli eesmärkidel.
Ja ühendamise all ei pea ma silmas lihtsat koopiapastat.
Peate tabeli 1 asjakohased kirjed kaardistama tabeli 2 ja 3 andmetega.
Nüüd saate seda teha VLOOKUP või INDEX/MATCH abil.
Või kui olete VBA nipsakas, saate selle tegemiseks kirjutada koodi.
Need valikud on aga Power Queryga võrreldes aeganõudvad ja keerulised.
Selles õpetuses näitan teile, kuidas ühendada need kolm Exceli tabelit üheks.
Selle tehnika toimimiseks peavad teil olema ühendusveerud. Näiteks tabelites 1 ja 2 on ühine veerg „Toode” ning tabelites 1 ja 3 on tavaline veerg „Müügiesindaja”. Samuti pange tähele, et nendes ühendusveergudes ei tohiks olla kordusi.Märkus. Power Queryt saab kasutada lisandmoodulina rakendustes Excel 2010 ja 2013 ning see on alates Excel 2016 sisseehitatud funktsioon. Teie versiooni põhjal võivad mõned pildid tunduda teistsugused (selles õpetuses kasutatud pildid on pärit Excel 2016 -st).
Tabelite ühendamine Power Query abil
Nimetasin need tabelid järgmiselt:
- Tabel 1 - Müügi_andmed
- Tabel 2 - Pdt_Id
- Tabel 3 - Piirkond
Nende tabelite ümbernimetamine ei ole kohustuslik, kuid parem on anda nimed, mis kirjeldavad tabeli sisu.
Ühe korraga saate Power Query'is ühendada ainult kaks tabelit.
Seega peame esmalt ühendama tabeli 1 ja tabeli 2 ning seejärel ühendama tabeli 3 järgmises etapis.
Tabeli 1 ja tabeli 2 ühendamine
Tabelite ühendamiseks peate need tabelid esmalt Power Query ühendusteks teisendama. Kui ühendused on loodud, saate need hõlpsalt ühendada.
Exceli tabeli salvestamiseks ühendusena Power Query's toimige järgmiselt.
- Valige tabelis Sales_Data suvaline lahter.
- Klõpsake vahekaarti Andmed.
- Klõpsake rühmas Hangi ja teisenda nuppu „Tabelist/vahemikust”. See avab päringu redaktori.
- Klõpsake päringuredaktoris vahekaarti „Fail”.
- Klõpsake suvandit „Sule ja laadi”.
- Valige dialoogiboksis „Andmete importimine” „Loo ainult ühendus”.
- Klõpsake nuppu OK.
Ülaltoodud toimingud loovad ühenduse nimega Sales_Data (või mis tahes nimega, mille olete andnud Exceli tabelile).
Korrake ülaltoodud samme tabeli 2 ja tabeli 3 puhul.
Nii et kui olete lõpetanud, on teil kolm ühendust (nimega Sales_Data, Pdt_Id ja Region).
Nüüd vaatame, kuidas ühendada tabel Sales_Data ja Pdt_Id.
- Klõpsake vahekaarti Andmed.
- Klõpsake rühmas Andmete hankimine ja teisendamine käsku Hangi andmed.
- Klõpsake rippmenüüs käsku Kombineeri päringuid.
- Klõpsake nuppu Ühenda. See avab dialoogiboksi Ühendamine.
- Valige dialoogiboksis Ühendamine esimesest rippmenüüst „Müügi_andmed”.
- Valige teisest rippmenüüst „Pdt_Id”.
- Eelvaate „Müügiandmed” klõpsake veerul „Üksus”. Seda tehes valitakse kogu veerg.
- Klõpsake eelvaate „Pdt_Id” veerul „Üksus”. Seda tehes valitakse kogu veerg.
- Valige rippmenüüst „Liitu liikidega” „Left Outer (kõik esimesest, sobivad teisest)”.
- Klõpsake nuppu OK.
Ülaltoodud toimingud avavad päringute redaktori ja näitavad teile müügiandmete andmeid koos ühe täiendava veeruga (Pdt_Id).
Exceli tabelite ühendamine (tabel 1 ja 2)
Nüüd toimub tabelite ühendamise protsess päringuredaktoris järgmiste sammudega:
- Täiendavas veerus (Pdt_Id) klõpsake päises olevat topeltnoolt.
- Tühjendage avanevas suvandikastis kõigi veergude nimed ja valige ainult üksus. Selle põhjuseks on asjaolu, et meil on juba olemasolevas tabelis tooteveerg ja soovime iga toote jaoks ainult toote ID -d.
- Tühjendage märkeruut „Kasuta veergude esialgset nime eesliitena”.
- Klõpsake nuppu OK.
See annaks teile tulemustabeli, kus on kõik tabeli Sales_Data kirjed ja täiendav veerg, millel on ka toote ID -d (tabelist Pdt_Id).
Nüüd, kui soovite ühendada ainult kaks tabelit, saate selle Exceli laadida.
Meil on aga kolm tabelit ühendada, seega tuleb veel tööd teha.
Selle tulemuseks oleva tabeli peate salvestama ühendusena (et saaksime seda kasutada tabeli 3 ühendamiseks).
Ühendatud tabeli (tabeli Sales_Data ja Pdt_Id andmetega) ühendamiseks salvestamiseks toimige järgmiselt.
- Klõpsake vahekaarti Fail
- Klõpsake suvandit „Sule ja lae”.
- Valige dialoogiboksis „Andmete importimine” „Loo ainult ühendus”.
- Klõpsake nuppu OK.
See salvestab äsja ühendatud andmed ühendusena. Soovi korral saate selle ühenduse ümber nimetada.
Tabeli 3 ühendamine tulemustabeliga
Kolmanda tabeli ühendamise protsess tulemustabeliga (mille saime tabeli 1 ja tabeli 2 ühendamisel) on täpselt sama.
Tabelite ühendamiseks toimige järgmiselt.
- Klõpsake vahekaarti Andmed.
- Klõpsake rühmas Andmete hankimine ja teisendamine nuppu „Hangi andmed”.
- Klõpsake rippmenüüs nuppu „Ühenda päringud”.
- Klõpsake "Ühenda". See avab dialoogiboksi Ühendamine.
- Valige dialoogiboksis Ühendamine esimesest rippmenüüst „Ühenda1”.
- Valige teises rippmenüüs „Piirkond”.
- Klõpsake eelvaate „Ühenda1” veerul „Müügiesindaja”. Seda tehes valitakse kogu veerg.
- Piirkonna eelvaates klõpsake veerul „Müügiesindaja”. Seda tehes valitakse kogu veerg.
- Valige rippmenüüst „Liitu liikiga” Left Outer (kõik esimesest, sobivad teisest).
- Klõpsake nuppu OK.
Ülaltoodud toimingud avavad päringute redaktori ja näitavad teile Merge1 andmeid koos ühe täiendava veeruga (piirkond).
Nüüd toimub tabelite ühendamise protsess päringuredaktoris järgmiste sammudega.
- Täiendavas veerus (Piirkond) klõpsake päises olevat kahekordse teraga noolt.
- Tühjendage avanevas suvandikastis kõigi veergude nimed ja valige ainult piirkond.
- Tühjendage märkeruut „Kasuta veergude esialgset nime eesliitena”.
- Klõpsake nuppu OK.
Ülaltoodud sammud annaksid teile tabeli, milles on kõik kolm tabelit ühendatud (tabel Sales_Data, mille üks veerg on Pdt_Id ja üks piirkonna jaoks).
Tabeli Excelisse laadimiseks toimige järgmiselt.
- Klõpsake vahekaarti Fail.
- Klõpsake nuppu „Sule ja lae”.
- Valige dialoogiboksis „Andmete importimine” suvandid Tabel ja uued töölehed.
- Klõpsake nuppu OK.
See annaks teile saadud ühendatud tabeli uuel töölehel.
Üks parimaid asju Power Query puhul on see, et saate hõlpsasti kohandada kõiki muudatusi alusandmetes (tabel 1, 2 ja 3), lihtsalt värskendades neid.
Oletame näiteks, et Laura viiakse Aasiasse ja saate järgmise kuu kohta uued andmed. Nüüd ei pea te ülaltoodud samme uuesti kordama. Peate ainult tabelit värskendama ja see teeb kõik teie jaoks uuesti.
Mõne sekundi pärast saate uue ühendatud tabeli.
Samuti võivad teile meeldida järgmised Power Query õpetused:
- Kombineerige Excelis mitme töövihiku andmed (Power Query abil).
- Ühendage mitme töölehe andmed Excelis üheks tööleheks.
- Kuidas Exceli andmeid Power Query abil (aka Get & Transform) kasutada
- Failide nimede loendi hankimine kaustadest ja alamkaustadest (Power Query abil)
