Sain äskettäin lukijaltani kysymyksen useiden laskentataulukoiden yhdistämisestä samaan työkirjaan yhdeksi laskentataulukkoksi.
Pyysin häntä käyttämään Power Queryä eri arkkien yhdistämiseen, mutta sitten ymmärsin, että Power Querylle uudelle henkilölle tämän tekeminen voi olla vaikeaa.
Joten päätin kirjoittaa tämän opetusohjelman ja näyttää tarkat vaiheet useiden arkkien yhdistämiseksi yhdeksi taulukkoksi Power Queryn avulla.
Alla on video, jossa näytän, kuinka yhdistän tietoja useista taulukoista/taulukoista Power Queryn avulla:
Alla on kirjalliset ohjeet useiden arkkien yhdistämisestä (jos pidät mieluummin kirjoitetusta tekstistä kuin videosta).
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ä useiden laskentataulukoiden tiedot Power Queryn avulla
Kun tietoja yhdistelemällä eri levyt käyttäen Virta Query, sitä tarvitaan saada tiedot Excel-taulukko (tai ainakin nimettyjä alueita). Jos tiedot eivät ole Excel -taulukossa, tässä esitetty menetelmä ei toimi.
Oletetaan, että sinulla on neljä eri arkkia - itä, länsi, pohjoinen ja etelä.
Jokaisella näistä laskentataulukoista on Excel -taulukon tiedot, ja taulukon rakenne on johdonmukainen (eli otsikot ovat samat).
Lataa tiedot napsauttamalla tätä ja seuraa.
Tällainen data on erittäin helppo yhdistää Power Queryn avulla (joka toimii todella hyvin Excel -taulukon tietojen kanssa).
Jotta tämä tekniikka toimisi parhaiten, on parempi nimetä Excel -taulukot (toimi myös ilman sitä, mutta sitä on helpompi käyttää, kun taulukot on nimetty).
Olen antanut taulukoille seuraavat nimet: East_Data, West_Data, North_Data ja South_Data.
Tässä on vaiheet useiden laskentataulukoiden yhdistämiseksi Excel -taulukoihin Power Queryn avulla:
- Siirry Data -välilehdelle.
- Napsauta Hae ja muunna tiedot -ryhmässä Hae tiedot.
- Valitse Muista lähteistä -vaihtoehto.
- Napsauta Tyhjä kysely -vaihtoehtoa. Tämä avaa Power Query -editorin.
- Kirjoita kyselyeditorissa seuraava kaava kaavapalkkiin: = Excel.Nykyinen työkirja(). Huomaa, että Power Query -kaavat erottavat isot ja pienet kirjaimet, joten sinun on käytettävä tarkkaa kaavaa, kuten on mainittu (muuten saat virheen).
- Paina Enter -näppäintä. Tämä näyttää kaikki taulukon nimet koko työkirjassa (se näyttää myös nimetyt alueet ja/tai yhteydet, jos niitä on työkirjassa).
- [Valinnainen vaihe] Tässä esimerkissä haluan yhdistää kaikki taulukot. Jos haluat yhdistää vain tietyt Excel-taulukot, voit napsauttaa avattavaa kuvaketta nimen otsikossa ja valita yhdistettävät taulukot. Samoin, jos olet nimennyt alueita tai yhteyksiä ja haluat vain yhdistää taulukoita, voit poistaa myös nimetyt alueet.
- Napsauta Sisältöotsikko -solussa kaksoissuuntaista nuolta.
- Valitse sarakkeet, jotka haluat yhdistää. Jos haluat yhdistää kaikki sarakkeet, varmista, että (Valitse kaikki sarakkeet) on valittuna.
- Poista valinta kohdasta Käytä alkuperäistä sarakkeen nimeä etuliitteenä.
- Napsauta OK.
Yllä olevat vaiheet yhdistäisivät kaikkien laskentataulukoiden tiedot yhteen taulukkoon.
Jos tarkastelet tarkasti, viimeisessä sarakkeessa (oikeassa reunassa) on Excel -taulukoiden nimi (East_Data, West_Data, North_Data ja South_Data). Tämä on tunniste, joka kertoo, mikä tietue mistä Excel -taulukosta on peräisin. Tästä syystä sanoin, että on parempi käyttää kuvaavia nimiä Excel -taulukoille.
Tässä on muutamia muutoksia, joita voit tehdä Power Queryn yhdistettyihin tietoihin:
- Vedä ja aseta Nimi -sarake alkuun.
- Poista ”_Data” nimisarakkeesta (niin ettet jätä itä-, länsi-, pohjois- ja etelänimiä nimisarakkeeseen). Voit tehdä tämän napsauttamalla hiiren kakkospainikkeella Nimi-otsikkoa ja napsauttamalla Korvaa arvot. Korvaa Korvaa arvot -valintaikkunassa _Tiedot tyhjällä.
- Vaihda Data -sarakkeeseen niin, että se näyttää vain päivämäärät (eikä aikaa). Voit tehdä tämän napsauttamalla Päivämäärä -sarakkeen otsikkoa, siirtymällä Muunna -välilehdelle ja muuttamalla tietotyypiksi Päivämäärä.
- Nimeä kysely uudelleen nimellä ConsolidatedData.
Nyt kun sinulla on Power Queryn kaikkien laskentataulukoiden yhdistetyt tiedot, voit ladata ne Excelissä - uuden taulukon uutena taulukkona.
Tehdä tämä. noudata seuraavia ohjeita:
- Napsauta "Tiedosto" -välilehteä.
- Napsauta Sulje ja lataa.
- Valitse Tuo tiedot -valintaikkunassa Taulukko- ja Uusi laskentataulukko -vaihtoehdot.
- Napsauta OK.
Yllä olevat vaiheet yhdistävät kaikkien laskentataulukoiden tiedot ja antavat yhdistetyt tiedot uuteen laskentataulukkoon.
Yksi ongelma, joka sinun on ratkaistava tätä menetelmää käytettäessä
Jos olet yhdistänyt kaikki työkirjan taulukot yllä olevalla menetelmällä, sinulla on todennäköisesti ongelma.
Katso yhdistettyjen tietojen rivien määrä - 1304 (mikä on oikein).
Jos päivitän kyselyn, rivien lukumääräksi muuttuu 2607. Päivitä uudelleen ja se muuttuu arvoon 3910.
Tässä on ongelma.
Aina kun päivität kyselyn, se lisää kaikki alkuperäisen datan tietueet yhdistettyihin tietoihin.
Huomautus: Tämä ongelma ilmenee vain, jos olet yhdistänyt Power Queryä KAIKKI EXCEL -TAULUKOT työkirjassa. Jos valitsit yhdistettäväksi tiettyjä taulukoita, et kohdata tätä ongelmaa.Ymmärrämme tämän ongelman syyn ja kuinka korjata se.
Kun päivität kyselyn, se palaa taaksepäin ja seuraa kaikkia vaiheita, jotka olemme tehneet tietojen yhdistämiseksi.
Vaiheessa, jossa käytimme kaavaa = Excel.CurrentWorkbook (), se antoi meille luettelon kaikista taulukoista. Tämä toimi hyvin ensimmäisellä kerralla, koska pöytiä oli vain neljä.
Mutta kun päivität, työkirjassa on viisi taulukkoa - mukaan lukien uusi taulukko, jonka Power Query lisäsi ja johon meillä on yhdistetyt tiedot.
Joten joka kerta, kun päivität kyselyn, neljän yhdistettävän Excel -taulukon lisäksi se lisää myös olemassa olevan kyselytaulukon tuloksiin.
Tätä kutsutaan rekursioksi.
Voit ratkaista tämän ongelman seuraavasti.
Kun lisäät = Excel.CurrentWorkbook () Power Query -kaavapalkkiin ja painat enter -näppäintä, saat luettelon Excel -taulukoista. Varmista et saa yhdistää taulukot työtilasta, sinun täytyy jotenkin suodattaa vain näitä taulukoita, jotka haluat yhdistää ja poistaa kaiken muun.
Voit varmistaa, että sinulla on vain tarvittavat taulukot seuraavasti:
- Napsauta avattavaa valikkoa ja siirrä kohdistin tekstisuodattimien kohdalle.
- Napsauta Sisältää -vaihtoehtoa.
- Kirjoita Suodata rivejä -valintaikkunaan _Data kenttään Sisältää -vaihtoehto.
- Napsauta OK.
Et välttämättä huomaa mitään muutosta tietoihin, mutta näin estää tuloksena taulukon lisätään uudestaan kun kysely päivittyy.
Huomaa, että yllä olevissa vaiheissa olemme käyttäneet "_Tiedot”Suodattaa, kun nimimme taulukot tällä tavalla. Mutta entä jos taulukoita ei ole nimetty johdonmukaisesti. Mitä jos kaikki taulukon nimet ovat satunnaisia eikä niillä ole mitään yhteistä.
Tässä on tapa ratkaista tämä - käyttää ’ei vastaa’ suodatin ja anna kyselyn nimeä (joka olisi ConsolidatedData meidän esimerkki). Tämä varmistaa, että kaikki pysyy ennallaan ja tuloksena luotu kyselytaulukko suodatetaan pois.
Sen lisäksi, että Power Query tekee tämän koko prosessin yhdistämisestä eri taulukoista (tai jopa samasta taulukosta) melko helppoa, sen käytön toinen etu on se, että se tekee siitä dynaamisen. Jos lisäät taulukoihin lisää tietueita ja päivität Power Queryn, se antaa sinulle automaattisesti yhdistetyt tiedot.Tärkeä huomautus: Tässä opetusohjelmassa käytetyssä esimerkissä otsikot olivat samat. Jos otsikot ovat erilaisia, Power Query yhdistää ja luo kaikki uuden taulukon sarakkeet. Jos kyseisen sarakkeen tiedot ovat saatavilla, ne näytetään, muuten ne näyttävät tyhjiä.
Saatat pitää myös seuraavista Power Query -opetusohjelmista:
- Yhdistä useiden työkirjojen tiedot Excelissä (Power Queryn avulla).
- Tietojen purkaminen Excelissä Power Queryn avulla (aka Get & Transform)
- Hanki luettelo tiedostojen nimistä kansioista ja alikansioista (Power Queryn avulla)
- Yhdistä taulukot Excelissä Power Queryn avulla.
- Kuinka verrata kahta Excel -taulukkoa/tiedostoa
