Yhdistä taulukot Excelissä Power Queryn avulla (helppo vaiheittainen opas)

Power Queryn avulla laskentataulukoihin tai jopa työkirjoihin hajallaan olevien tietojen käsittely on tullut helpommaksi.

Yksi niistä asioista, joissa Power Query voi säästää paljon aikaa, on se, että joudut yhdistämään erikokoisia taulukoita ja sarakkeita vastaavan sarakkeen perusteella.

Alla on video, jossa näytän tarkalleen, miten taulukot yhdistetään Excelissä Power Queryn avulla.

Jos haluat lukea tekstin mieluummin kuin katsoa videota, alla on kirjalliset ohjeet.

Oletetaan, että sinulla on alla olevan taulukon mukainen taulukko:

Tässä taulukossa on tiedot, joita haluan käyttää, mutta siitä puuttuu edelleen kaksi tärkeää saraketta - "Tuotetunnus" ja "Alue", jossa myyntiedustaja toimii.

Nämä tiedot toimitetaan erillisinä taulukoina alla olevan kuvan mukaisesti:

Jos haluat saada kaikki nämä tiedot yhteen taulukkoon, sinun on yhdistettävä nämä kolme taulukkoa, jotta voit luoda pivot -taulukon ja analysoida sen tai käyttää sitä muihin raportointi-/koontinäyttötarkoituksiin.

Ja yhdistämisellä en tarkoita yksinkertaista kopiota.

Sinun on yhdistettävä taulukon 1 asiaankuuluvat tietueet taulukon 2 ja 3 tiedoilla.

Nyt voit luottaa VLOOKUP- tai INDEX/MATCH -toimintoon.

Tai jos olet VBA -huijari, voit kirjoittaa koodin tämän tekemiseksi.

Nämä vaihtoehdot ovat kuitenkin aikaa vieviä ja monimutkaisia ​​verrattuna Power Queryyn.

Tässä opetusohjelmassa näytän sinulle, kuinka yhdistää nämä kolme Excel -taulukkoa yhdeksi.

Jotta tämä tekniikka toimisi, sinulla on oltava yhdistävät sarakkeet. Esimerkiksi taulukoissa 1 ja 2 yhteinen sarake on "Tuote" ja taulukoissa 1 ja 3 yhteinen sarake on "Myyntiedustaja". Huomaa myös, että näissä yhdistävissä sarakkeissa ei pitäisi olla toistoa.

Huomautus: Power Queryä voidaan käyttää apuohjelmana Excel 2010: ssä ja 2013: ssa, ja se on sisäänrakennettu ominaisuus Excel 2016: sta lähtien. Versiosi perusteella jotkin kuvat voivat näyttää erilaisilta (tässä opetusohjelmassa käytetyt kuvat ovat Excel 2016: sta).

Yhdistä taulukot Power Queryn avulla

Olen nimittänyt nämä taulukot alla olevan kuvan mukaisesti:

1. Taulukko 1 - Sales_Data
2. Taulukko 2 - Pdt_Id
3. Taulukko 3 - Alue

Näiden taulukoiden nimeäminen ei ole pakollista, mutta on parempi antaa nimet, jotka kuvaavat taulukon tarkoitusta.

Voit yhdistää Power Queryssä vain kaksi taulukkoa kerralla.

Joten meidän on ensin yhdistettävä taulukko 1 ja taulukko 2 ja yhdistettävä sitten taulukko 3 siihen seuraavassa vaiheessa.

Taulukon 1 ja taulukon 2 yhdistäminen

Taulukoiden yhdistämiseksi sinun on ensin muunnettava nämä taulukot yhteyksiksi Power Queryssä. Kun sinulla on yhteydet, voit helposti yhdistää ne.

Voit tallentaa Excel -taulukon yhteysnäytöksi Power Queryssä seuraavasti:

1. Valitse mikä tahansa Sales_Data -taulukon solu.
2. Napsauta Data -välilehteä.
3. Napsauta Hae ja muunna -ryhmässä "Taulukosta/alueelta". Tämä avaa kyselyeditorin.
4. Napsauta kyselyeditorissa Tiedosto -välilehteä.
5. Napsauta "Sulje ja lataa" -vaihtoehtoa.
6. Valitse Tuo tiedot -valintaikkunassa Luo vain yhteys.
7. Napsauta OK.

Yllä olevat vaiheet loisivat yhteyden nimeltä Sales_Data (tai minkä tahansa Excel -taulukkoon antamasi nimen).

Toista yllä olevat vaiheet taulukoille 2 ja 3.

Joten kun olet valmis, sinulla on kolme yhteyttä (nimellä Sales_Data, Pdt_Id ja Region).

Katsotaan nyt, miten Sales_Data- ja Pdt_Id -taulukot yhdistetään.

1. Napsauta Data -välilehteä.
2. Napsauta Hae ja muunna tiedot -ryhmässä Hae tiedot.
3. Valitse avattavasta valikosta Yhdistä kyselyt.
4. Napsauta Yhdistä. Tämä avaa Yhdistä -valintaikkunan.
5. Valitse Yhdistä -valintaikkunan ensimmäisestä avattavasta valikosta Sales_Data.
6. Valitse ”Pdt_Id” toisesta avattavasta valikosta.
7. Napsauta Sales_Data -esikatselussa tuote -saraketta. Tämä valitsee koko sarakkeen.
8. Napsauta Pdt_Id -esikatselussa Esine -saraketta. Tämä valitsee koko sarakkeen.
9. Valitse avattavasta "Join Kind" -valikosta "Left Outer (kaikki ensimmäisestä, vastaavat toisesta)".
10. Napsauta OK.

Yllä olevat vaiheet avaavat kyselyeditorin ja näyttävät Sales_Data -tiedot yhdellä lisäsarakkeella (Pdt_Id).

Excel -taulukoiden yhdistäminen (taulukot 1 ja 2)

Taulukoiden yhdistäminen tapahtuu nyt kyselyeditorissa seuraavien vaiheiden avulla:

1. Napsauta ylimääräisessä sarakkeessa (Pdt_Id) otsikon kaksoissuuntaista nuolta.
2. Poista avautuvasta asetusruudusta kaikkien sarakkeiden nimet ja valitse vain kohde. Tämä johtuu siitä, että tuotenimi -sarake on jo olemassa olevassa taulukossa, ja haluamme vain kunkin tuotteen tuotetunnuksen.
3. Poista valinta ”Käytä alkuperäisen sarakkeen nimeä etuliitteenä”.
4. Napsauta OK.

Tämä antaisi sinulle tuloksena olevan taulukon, jossa on kaikki Sales_Data -taulukon tietueet ja ylimääräinen sarake, jossa on myös tuotetunnuksia (Pdt_Id -taulukosta).

Jos nyt haluat yhdistää vain kaksi taulukkoa, voit ladata tämän Excelin.

Meillä on kuitenkin kolme taulukkoa yhdistettäväksi, joten työtä on vielä tehtävä.

Tuloksena oleva taulukko on tallennettava yhteydeksi (jotta voimme yhdistää sen taulukkoon 3).

Tässä on vaiheet, joilla tämä yhdistetty taulukko (Sales_Data- ja Pdt_Id -taulukon tiedot) tallennetaan yhteydeksi:

1. Napsauta Tiedosto -välilehteä
2. Napsauta "Sulje ja lataa" -vaihtoehtoa.
3. Valitse Tuo tiedot -valintaikkunassa Luo vain yhteys.
4. Napsauta OK.

Tämä tallentaa juuri yhdistetyt tiedot yhteydeksi. Voit nimetä tämän yhteyden uudelleen, jos haluat.

Taulukon 3 yhdistäminen tuloksena olevaan taulukkoon

Kolmannen taulukon yhdistäminen tuloksena olevaan taulukkoon (jonka saimme yhdistämällä taulukot 1 ja 2) on täsmälleen sama.

Seuraavat vaiheet yhdistävät nämä taulukot:

1. Napsauta Data -välilehteä.
2. Napsauta Hae ja muunna tiedot -ryhmässä Hae tiedot.
3. Valitse avattavasta valikosta Yhdistä kyselyt.
4. Napsauta "Yhdistä". Tämä avaa Yhdistä -valintaikkunan.
5. Valitse Yhdistä -valintaikkunan ensimmäisestä avattavasta valikosta Yhdistä1.
6. Valitse ”Alue” toisesta avattavasta valikosta.
7. Napsauta Yhdistä1 -esikatselussa Myyntiedustaja -saraketta. Tämä valitsee koko sarakkeen.
8. Napsauta alueen esikatselussa "Myyntiedustaja" -saraketta. Tämä valitsee koko sarakkeen.
9. Valitse avattavasta 'Join Kind' -valikosta Left Outer (kaikki ensimmäisestä, vastaavat toisesta).
10. Napsauta OK.

Yllä olevat vaiheet avaavat kyselyeditorin ja näyttävät yhdistämisen1 tiedot yhdellä lisäsarakkeella (alue).

Taulukoiden yhdistäminen tapahtuu nyt kyselyeditorissa seuraavien vaiheiden avulla:

1. Napsauta ylimääräisessä sarakkeessa (Alue) otsikon kaksoissuuntaista nuolta.
2. Poista avautuvasta asetusruudusta kaikkien sarakkeiden nimet ja valitse vain Alue.
3. Poista valinta ”Käytä alkuperäisen sarakkeen nimeä etuliitteenä”.
4. Napsauta OK.

Yllä olevat vaiheet antavat sinulle taulukon, jossa kaikki kolme taulukkoa on yhdistetty (Sales_Data -taulukko, jossa on yksi sarake Pdt_Id ja yksi alueelle).

Voit ladata tämän taulukon Exceliin seuraavasti:

1. Napsauta Tiedosto -välilehteä.
2. Napsauta 'Sulje ja lataa'.
3. Valitse Tuo tiedot -valintaikkunassa Taulukko ja uudet laskentataulukot.
4. Napsauta OK.

Näin saat yhdistetyn taulukon uuteen laskentataulukkoon.

Yksi Power Queryn parhaista puolista on se, että voit helposti mukauttaa kaikki muutokset perustiedoissa (taulukko 1, 2 ja 3) yksinkertaisesti päivittämällä ne.

Oletetaan esimerkiksi, että Laura siirretään Aasiaan ja saat uusia tietoja seuraavalle kuukaudelle. Nyt sinun ei tarvitse toistaa yllä olevia vaiheita uudelleen. Sinun tarvitsee vain päivittää pöytä ja se tekee kaiken uudestaan ​​puolestasi.

Muutaman sekunnin kuluttua saat uuden yhdistetyn taulukon.

Saatat pitää myös seuraavista Power Query -opetusohjelmista:

• Yhdistä useiden työkirjojen tiedot Excelissä (Power Queryn avulla).
• Yhdistä useiden laskentataulukoiden tiedot yhdeksi Excel -laskentataulukkoksi.
• Tietojen purkaminen Excelissä Power Queryn avulla (aka Get & Transform)
• Hanki luettelo tiedostojen nimistä kansioista ja alikansioista (Power Queryn avulla)