Useiden työkirjojen tietojen yhdistäminen Excelissä (Power Queryn avulla)

Power Querystä voi olla paljon apua, kun haluat yhdistää useita työkirjoja yhdeksi työkirjaksi.

Oletetaan esimerkiksi, että sinulla on myyntitiedot eri alueilta (itä, länsi, pohjoinen ja etelä). Voit yhdistää nämä tiedot eri työkirjoista yhteen laskentataulukkoon Power Queryn avulla.

Jos sinulla on nämä työkirjat eri paikoissa/kansioissa, on hyvä siirtää nämä kaikki yhteen kansioon (tai luoda kopio ja sijoittaa työkirjan kopio samaan kansioon).

Joten aluksi minulla on neljä työkirjaa kansiossa (kuten alla).

Tässä opetusohjelmassa käsitellään kolmea skenaariota, joissa voit yhdistää eri työkirjojen tiedot Power Queryn avulla:

• Jokaisessa työkirjassa on Excel -taulukon tiedot, ja kaikkien taulukoiden nimet ovat samat.
• Jokaisessa työkirjassa on tiedot, joilla on sama laskentataulukon nimi. Näin voi olla, kun kaikissa työkirjoissa on taulukko nimeltä "yhteenveto" tai "data" ja haluat yhdistää nämä kaikki.
• Jokaisessa työkirjassa on monta arkkia ja taulukkoa, ja haluat yhdistää tietyt taulukot/taulukot. Tämä menetelmä voi olla hyödyllinen myös silloin, kun haluat yhdistää taulukon/taulukot, joilla ei ole johdonmukaista nimeä.

Katsotaanpa kuinka yhdistää näiden työkirjojen tiedot kussakin tapauksessa.

Jokaisessa työkirjassa on Excel -taulukon tiedot, joilla on sama rakenne

Alla oleva tekniikka toimisi, kun Excel -taulukot on rakennettu samalla tavalla (samat sarakkeiden nimet).

Kunkin taulukon rivien määrä voi vaihdella.

Älä huolestu, jos joissakin Excel -taulukoissa on lisäsarakkeita. Voit valita mallista yhden taulukoista (tai "avaimen", kuten Power Query sitä kutsuu), ja Power Query käyttää sitä yhdistääkseen kaikki muut Excel -taulukot siihen.

Jos muissa taulukoissa on lisäsarakkeita, ne jätetään huomiotta ja vain mallissa/avaimessa määritetyt sarakkeet yhdistetään. Jos esimerkiksi valitsemassasi mallipohjassa/avaintaulukossa on 5 saraketta ja jossakin muussa työkirjassa olevassa taulukossa on 2 ylimääräistä saraketta, ne jätetään huomiotta.

Nyt minulla on neljä työkirjaa kansiossa, jotka haluan yhdistää.

Alla on tilannekuva eräässä työkirjassa olevasta taulukosta.

Tässä on vaiheet näiden työkirjojen tietojen yhdistämiseksi yhdeksi työkirjaksi (yhtenä taulukkona).

1. Siirry Data -välilehdelle.
2. Napsauta Hae ja muunna -ryhmässä avattavaa Uusi kysely -valikkoa.
3. Vie hiiri "Tiedostosta" -kohtaan ja napsauta "Kansio".
4. Kirjoita Kansio -valintaikkunaan sen kansion tiedostopolku, jossa tiedostot ovat, tai napsauta Selaa ja etsi kansio.
5. Napsauta OK.
6. Napsauta avautuvassa valintaikkunassa yhdistämispainiketta.
7. Napsauta "Yhdistä ja lataa".
8. Valitse avautuvasta Yhdistä tiedostot -valintaikkunasta Taulukko vasemmasta ruudusta. Huomaa, että Power Query näyttää taulukon ensimmäisestä tiedostosta. Tämä tiedosto toimisi mallina (tai avaimena) muiden tiedostojen yhdistämisessä. Power Query etsisi nyt taulukkoa 1 muista työkirjoista ja yhdistäisi sen tähän.
9. Napsauta OK.

Tämä lataa lopullisen tuloksen (yhdistetyt tiedot) aktiiviselle laskentataulukollesi.

Huomaa, että tietojen lisäksi Power Query lisää työkirjan nimen automaattisesti yhdistetyn datan ensimmäiseksi sarakkeeksi. Tämä auttaa pitämään kirjaa siitä, mitä tietoja mistä työkirjasta tuli.

Jos haluat ensin muokata tietoja ennen niiden lataamista Exceliin, valitse vaiheessa 6 Yhdistä ja muokkaa. Tämä avaa lopullisen tuloksen Power Query -editorissa, jossa voit muokata tietoja.

Muutama asia tietää:

• Jos valitset Excel -taulukon malliksi (vaiheessa 7), Power Query käyttää tämän taulukon sarakkeiden nimiä muiden taulukoiden tietojen yhdistämiseen. Jos muissa taulukoissa on lisäsarakkeita, ne jätetään huomiotta. Jos näissä muissa taulukoissa ei ole saraketta, joka on mallitaulukossasi, Power Query merkitsisi sille vain nullin.
• Sarakkeiden ei tarvitse olla samassa järjestyksessä kuin Power Query käyttää sarakkeiden otsikoita sarakkeiden kartoittamiseen.
• Koska olet valinnut taulukon 1 avaimeksi, Power Query etsii taulukon 1 kaikista työkirjoista ja yhdistää ne kaikki. Jos se ei löydä saman nimistä Excel -taulukkoa (taulukko 1 tässä esimerkissä), Power Query antaa sinulle virheen.

Uusien tiedostojen lisääminen kansioon

Otetaan nyt hetki ja ymmärretään, mitä teimme yllä olevilla vaiheilla (mikä kesti vain muutaman sekunnin).

Yhdistimme neljän eri työkirjan tiedot yhteen taulukkoon muutamassa sekunnissa edes avaamatta työkirjoja.

Mutta se ei ole kaikki.

Power Queryn todellinen POWER on, että nyt kun lisäät tiedostoja kansioon, sinun ei tarvitse toistaa mitään näistä vaiheista.

Sinun tarvitsee vain siirtää uusi työkirja kansioon, päivittää kysely, ja se yhdistää automaattisesti kaikkien kansion työkirjojen tiedot.

Jos esimerkiksi yllä olevassa esimerkissä lisään uuden työkirjan - "Mid-West.xlsx" kansioon ja päivitä kysely, se antaa minulle heti uuden yhdistetyn tietojoukon.

Voit päivittää kyselyn seuraavasti:

• Napsauta hiiren kakkospainikkeella laskentataulukkoon ladattua Excel-taulukkoa ja valitse Päivitä.
• Napsauta hiiren kakkospainikkeella kyselyä Työkirjan kysely -ruudussa ja valitse Päivitä
• Siirry Tiedot -välilehteen ja napsauta Päivitä.

Jokaisessa työkirjassa on tiedot, joilla on sama laskentataulukon nimi

Jos sinulla ei ole tietoja Excel -taulukossa, mutta kaikki taulukon nimet (joista haluat yhdistää tiedot) ovat samat, voit käyttää tässä osiossa esitettyä menetelmää.

Sinun on oltava varovainen muutamissa asioissa, kun kyse on vain taulukkotiedoista eikä Excel -taulukosta.

• Laskentataulukon nimien tulee olla samat. Tämä auttaa Power Queryä käymään läpi työkirjasi ja yhdistämään laskentataulukoiden tiedot, joilla on sama nimi jokaisessa työkirjassa.
• Power Query erottaa isot ja pienet kirjaimet. Tämä tarkoittaa, että laskentataulukko nimeltä "data" ja "Data" katsotaan erilaisiksi. Samoin sarakkeen, jonka otsikko on "Store" ja "store", pidetään erilaisena.
• Vaikka on tärkeää, että sarakkeiden otsikot ovat samat, ei ole tärkeää, että järjestys on sama. Jos East.xlsx -sarake 2 on West.xlsx -sarake 4, Power Query vastaa siihen oikein kartoittamalla otsikot.

Katsotaanpa nyt, kuinka nopeasti yhdistää tietoja eri työkirjoista, joissa laskentataulukon nimi on sama.

Tässä esimerkissä minulla on kansio, jossa on neljä tiedostoa.

Jokaisessa työkirjassa minulla on laskentataulukko, jonka nimi on Data, joka sisältää tiedot seuraavassa muodossa (huomaa, että tämä ei ole Excel -taulukko).

Tässä on vaiheet useiden työkirjojen tietojen yhdistämiseksi yhdeksi laskentataulukkoksi:

1. Siirry Data -välilehdelle.
2. Napsauta Hae ja muunna -ryhmässä avattavaa Uusi kysely -valikkoa.
3. Vie hiiri "Tiedostosta" -kohtaan ja napsauta "Kansio".
4. Kirjoita Kansio -valintaikkunaan sen kansion tiedostopolku, jossa tiedostot ovat, tai napsauta Selaa ja etsi kansio.
5. Napsauta OK.
6. Napsauta avautuvassa valintaikkunassa yhdistämispainiketta.
7. Napsauta "Yhdistä ja lataa".
8. Valitse avautuvasta Yhdistä tiedostot -valintaikkunasta vasemmasta ruudusta Data. Huomaa, että Power Query näyttää laskentataulukon nimen ensimmäisestä tiedostosta. Tämä tiedosto toimisi avaimena/mallina muiden tiedostojen yhdistämisessä. Power Query käy läpi jokaisen työkirjan, etsii taulukon nimeltä Data ja yhdistää kaikki nämä.
9. Napsauta OK. Nyt Power Query käy läpi jokaisen työkirjan, etsii siitä taulukkoa nimeltä "Data" ja yhdistää sitten kaikki nämä tietojoukot.

Tämä lataa lopullisen tuloksen (yhdistetyt tiedot) aktiiviselle laskentataulukollesi.

Jos haluat ensin muokata tietoja ennen niiden lataamista Exceliin, valitse vaiheessa 6 Yhdistä ja muokkaa. Tämä avaa lopullisen tuloksen Power Query -editorissa, jossa voit muokata tietoja.

Jokaisessa työkirjassa on tiedot, joilla on erilaiset taulukon tai arkin nimet

Joskus et ehkä saa jäsenneltyjä ja johdonmukaisia ​​tietoja (kuten taulukoita, joilla on sama nimi tai laskentataulukko, jolla on sama nimi).

Oletetaan esimerkiksi, että saat tiedot joltain, joka loi nämä tietojoukot, mutta antoi laskentataulukoille nimeksi East Data, West Data, North Data ja South Data.

Tai henkilö on saattanut luoda Excel -taulukoita, mutta eri nimillä.

Tällaisissa tapauksissa voit silti käyttää Power Queryä, mutta sinun on tehtävä se muutamalla lisävaiheella.

1. Siirry Data -välilehdelle.
2. Napsauta Hae ja muunna -ryhmässä avattavaa Uusi kysely -valikkoa.
3. Vie hiiri "Tiedostosta" -kohtaan ja napsauta "Kansio".
4. Kirjoita Kansio -valintaikkunaan sen kansion tiedostopolku, jossa tiedostot ovat, tai napsauta Selaa ja etsi kansio.
5. Napsauta OK.
6. Napsauta avautuvassa valintaikkunassa Muokkaa -painiketta. Tämä avaa Power Query -editorin, jossa näet kaikkien kansion tiedostojen tiedot.
7. Pidä Control-näppäintä painettuna ja valitse Sisältö- ja Nimi-sarakkeet, napsauta hiiren kakkospainikkeella ja valitse Poista muut sarakkeet. Tämä poistaa kaikki muut sarakkeet paitsi valitut sarakkeet.
8. Napsauta Kyselyeditori -valintanauhassa Lisää sarake ja napsauta sitten Muokattu sarake.
9. Nimeä uusi sarake Lisää mukautettu sarake -valintaikkunassa nimeksi "Tietojen tuonti" ja käytä seuraavaa kaavaa = Excel.Workbook ([SISÄLTÖ]). Huomaa, että tämä kaava erottaa isot ja pienet kirjaimet ja sinun on syötettävä se juuri tällä tavalla.
10. Nyt näet uuden sarakkeen, johon on kirjoitettu taulukko. Selitän nyt mitä täällä tapahtui. Annoit Power Querylle työkirjojen nimet, ja Power Query on hakenut objekteja, kuten laskentataulukoita, taulukoita ja nimettyjä alueita kustakin työkirjasta (joka on tällä hetkellä Taulukko -solussa). Voit napsauttaa taulukon vieressä olevaa tyhjää tilaa ja näet tiedot alareunassa. Tässä tapauksessa, koska jokaisessa työkirjassa on vain yksi taulukko ja yksi laskentataulukko, näet vain kaksi riviä.
11. Napsauta kaksoisnuolikuvaketta Tietojen tuonti -sarakkeen yläosassa.
12. Poista avautuvasta sarakkeen dataruudusta valinta Käytä alkuperäistä saraketta etuliitteenä ja valitse sitten OK.
13. Nyt näet laajennetun taulukon, jossa näet yhden rivin jokaiselle taulukon objektille. Tässä tapauksessa jokaisen työkirjan taulukko- ja taulukko -objekti luetellaan erikseen.
14. Suodata Laji -sarakkeessa luettelo niin, että se näyttää vain taulukon.
15. Pidä ohjausnäppäintä painettuna ja valitse Nimi ja tiedot -sarake. Napsauta nyt hiiren kakkospainikkeella ja poista kaikki muut sarakkeet.
16. Napsauta Data-sarakkeessa kaksoisnuolikuvaketta dataotsikon oikeassa yläkulmassa.
17. Napsauta avautuvassa sarakkeen dataruudussa OK. Tämä yhdistää kaikkien taulukoiden tiedot ja näkyy Power Queryssä.
18. Nyt voit tehdä tarvittavat muutokset ja siirtyä sitten Etusivu -välilehteen ja napsauttaa Sulje ja lataa.

Yritän nyt selittää nopeasti, mitä teimme täällä. Koska arkkien nimissä tai taulukon nimissä ei ollut johdonmukaisuutta, haimme kaikki Power Queryn työkirjojen objektit hakulomakkeella = Excel.Workbook. Nämä objektit voivat sisältää taulukkoja, taulukoita ja nimettyjä alueita. Kun meillä oli kaikki objektit kaikista tiedostoista, suodatimme ne ottamaan huomioon vain Excel -taulukot. Sitten laajensimme taulukoiden tietoja ja yhdistimme kaikki nämä.

Tässä esimerkissä suodatimme tiedot käyttämään vain Excel -taulukoita (vaiheessa 13). Jos haluat yhdistää taulukot eikä taulukoita, voit suodattaa taulukot.

Huomaa - tämä tekniikka antaa sinulle yhdistetyt tiedot silloinkin, kun sarakkeiden nimet eivät täsmää. Jos esimerkiksi East.xlsx -tiedostossa on sarake, joka on kirjoitettu väärin, saat viisi saraketta. Power Query täyttää tiedot sarakkeisiin, jos se löytää ne, ja jos se ei löydä saraketta, se ilmoittaa arvon nolla -arvoksi.

Samoin, jos sinulla on ylimääräisiä sarakkeita missä tahansa taulukon laskentataulukossa, ne sisällytetään lopputulokseen.

Jos nyt saat lisää työkirjoja, joista haluat yhdistää tietoja, kopioi ja liitä ne kansioon ja päivitä Power Query