Floridalainen David kysyy tänään:
Minulla on kaksi työkirjaa. Molemmilla on samat tiedot sarakkeessa A, mutta loput sarakkeet ovat erilaisia. Kuinka voin yhdistää nuo kaksi työkirjaa?
Kysyin Davidilta, onko mahdollista, että yhdessä työkirjassa on enemmän tietoja kuin toisessa. Ja vastaus on kyllä. Kysyin Davidilta, näkyykö avainkenttä vain kerran jokaisessa tiedostossa. Vastaus on myös kyllä. Tänään ratkaisen tämän Power Queryllä. Power Query -työkalut löytyvät Excel 2016+: n Windows-versioista Tiedot-välilehden Hae ja muunna -osiosta. Jos sinulla on Excel 2010: n tai Excel 2013: n Windows-versiot, voit ladata Power Query -apuohjelman kyseisille versioille.
Tässä on Davidin työkirja 1. Siinä on Tuote ja sitten kolme saraketta tietoja.
Tässä on Davidin työkirja 2. Siinä on tuotekoodi ja sitten muut sarakkeet. Tässä esimerkissä työkirjassa2 on ylimääräisiä tuotteita, mutta ratkaisut toimivat, jos kummassakin työkirjassa on ylimääräisiä sarakkeita.
Tässä ovat vaiheet:
-
Valitse Tiedot, Hae tietoja, Tiedostosta, Työkirjasta:
Lataa tiedot tiedostosta - Selaa ensimmäiseen työkirjaan ja napsauta OK
- Valitse Navigator-valintaikkunassa vasemmalla oleva laskentataulukko. (Vaikka taulukoita on vain yksi, sinun on valittava se.) Näet tiedot oikealla.
- Avaa Navigator-valintaikkunassa Lataa-valikko ja valitse Lataa…
- Valitse Luo vain yhteys ja paina OK.
-
Toista vaiheet 1-5 toiselle työkirjalle.
Luo yhteys työkirjaan Jos olet tehnyt molemmat työkirjat, sinun pitäisi nähdä kaksi yhteyttä Excel-näytön oikealla puolella olevassa Kyselyt ja yhteydet -paneelissa.
Yhteydet molempiin työkirjoihin Jatka työkirjojen yhdistämisen vaiheita:
-
Tiedot, Hae tietoja, Yhdistä kyselyt, Yhdistä.
Yhdistä kaksi kyselyä, joilla on erilaiset sarakkeet - Valitse Yhdistä-valintaikkunan avattavasta ylävalikosta ensimmäinen kysely.
- Valitse Yhdistä-valintaikkunan toisesta avattavasta valikosta toinen kysely.
- Napsauta Tuote-otsikkoa ylimmässä esikatselussa (tämä on avainkenttä. Huomaa, että voit valita kaksi tai useampia avainkenttiä painamalla Ctrl + napsauttamalla)
- Napsauta tuotekoodin otsikkoa toisessa esikatselussa.
-
Avaa Liity tyyppi ja valitse Täysi ulkoinen (kaikki rivit molemmista)
Vaiheet 8-12 kuvataan tässä -
Napsauta OK. Tietojen esikatselu ei näytä ylimääräisiä rivejä, ja vain taulukko näkyy toistuvasti viimeisessä sarakkeessa.
Tämä ei näytä lupaavalta - Huomaa, että DavidTwo-otsikossa on "Laajenna" -kuvake. Napsauta sitä kuvaketta.
-
Valinnainen, mutta poistan aina "Käytä alkuperäisen sarakkeen nimeä etuliitteenä" -valinnan. Napsauta OK.
Laajenna työkirjan 2 kentät Tulokset näkyvät tässä esikatselussa:
Kaikki tietueet kummastakin työkirjasta - Käytä Power Query -sovelluksessa Koti, Sulje ja lataa.
Tässä on kaunis ominaisuus: jos kummankin työkirjan taustalla olevat tiedot muuttuvat, voit napsauttaa Päivitä-kuvaketta vetääksesi uusia tietoja tulostietokirjaan.
Huomautus
Päivitä-kuvake on yleensä piilotettu. Tuo kuvake näkyviin vetämällä Kyselyt ja yhteydet -ruudun vasenta reunaa vasemmalle.
Katso video
Videon transkriptio
Opi Excel Podcastista, Jakso 2216: Yhdistä kaksi työkirjaa yhteisen sarakkeen perusteella.
Hei, tervetuloa takaisin netcastiin, olen Bill Jelen. Tämän päivän kysymys on Davidilta, joka oli seminaarissani Melbournessa, Floridassa, IIA: n avaruusrannikon luvulle.
Davidilla on kaksi erilaista työkirjaa, joissa sarake A on molempien välillä yhteinen. Joten tässä on työkirja 1, tässä työkirja 2 - molemmilla on tuotekoodi. Tässä on kohteita, joita ensimmäisessä ei ole, tai päinvastoin, ja David haluaa yhdistää kaikki sarakkeet. Joten meillä on kolme saraketta täällä ja neljä saraketta täällä. Laitoin molemmat samaan työkirjaan, jos lataat työkirjan toimimaan. Ota jokainen näistä, siirrä se omalle työkirjaansa ja tallenna se.
Selvä, näiden tiedostojen yhdistämiseksi aiomme käyttää Power Queryä. Power Query on sisäänrakennettu Excel 2016. Jos sinulla on Windows 10- tai 13-versio, voit mennä Microsoftiin ja ladata Power Query. Voit aloittaa uudesta tyhjästä työkirjasta tyhjällä laskentataulukolla. Aiot tallentaa tämän tiedoston - Tallenna nimellä, tiedät ehkä työkirja, näyttääksesi yhdistettyjen tiedostojen .xlsx-tulokset. Hyvä on? Ja mitä aiomme tehdä, teemme kaksi kyselyä. Menemme kohtaan Tiedot, Hae tietoja, Tiedostosta, Työkirjasta ja valitsemme sitten ensimmäisen tiedoston. Valitse esikatselussa taulukko, jossa on tietosi, eikä meidän tarvitse tehdä mitään näille tiedoille. Joten avaa vain latauslaatikko ja valitse Lataa, vain luo yhteys, napsauta OK. Täydellinen. Toistamme nyt toisen kohteen - Data, tiedostosta,Valitse työkirjasta DavidTwo, valitse taulukon nimi ja avaa sitten kuorma, Lataa vain, luo vain yhteys. Näet täällä tässä paneelissa, meillä on molemmat yhteydet läsnä. Hyvä on.
Nyt varsinainen työ - Data, Hae tietoja, Yhdistä kyselyt, Yhdistä ja valitse sitten Yhdistä-valintaikkunassa DavidOne, DavidTwo, ja tämä seuraava vaihe on täysin epäjohdonmukainen. Sinun täytyy tehdä tämä. Valitse sarake tai sarakkeet yhteisesti - niin Tuote ja Tuote. Hyvä on. Ja sitten, ole varovainen täällä liitostyypin kanssa. Haluan kaikki rivit molemmista, koska yhdellä voi olla ylimääräinen rivi, ja minun täytyy nähdä se, ja sitten napsautamme OK. Hyvä on. Ja tässä on alkuperäinen tulos. Se ei näytä toimivan; se ei näytä lisänneen tiedostossa 2 olevia ylimääräisiä kohteita. Ja meillä on tämä sarake 5 - se on nyt tyhjä. Napsautan hiiren kakkospainikkeella saraketta 5 ja sanon, Poista sarake. Joten avaa tämä laajennuskuvake ja poista valinta kohdasta Käytä alkuperäistä sarakkeen nimeä etuliitteenä ja BAM! se toimii. Joten ylimääräiset kohteet, jotka olivat tiedostossa 2, jotka eivät ole tiedostossa 1,eivät näy.
Hyvä on. Nyt tämän päivän tiedostossa näyttää siltä, että tämä tuotekoodisarake on parempi kuin tämä tuotesarake, koska siinä on ylimääräisiä rivejä. Mutta tulevaisuudessa saattaa olla päivä, jolloin työkirjassa 1 on asioita, joita työkirjassa 2 ei ole. Joten aion jättää molemmat siellä, enkä aio päästä eroon mistään nollista, koska, kuten, vaikka tämä rivi alareunassa näyttää olevan täysin tyhjä, saattaa tulevaisuudessa olla tilanne, jossa meillä on muutama tyhjä täällä, koska jotain puuttuu. Hyvä on? Joten lopuksi Sulje ja lataa, ja meillä on kuusitoista riviä.
Sanotaan nyt, tulevaisuudessa, että jokin muuttuu. Selvä, joten palaamme yhteen näistä kahdesta tiedostosta ja vaihdan luokan Apple arvoon 99, ja lisätään jopa jotain uutta ja tallennetaan tämä työkirja. Hyvä on. Ja sitten, jos haluamme yhdistämistiedostomme päivittyvän, tule tänne - nyt, varo, kun teet tämän ensimmäisen kerran, et näe Päivitä-kuvaketta - sinun on tartuttava tähän palkkiin ja vedettävä se yli . Ja teemme Päivitä, ja 17 riviä ladattu, vesimeloni ilmestyy, Apple muuttuu 99: ksi - se on kaunis asia. Hei, haluatko oppia Power Querystä? Osta tämä kirja Ken Puls ja Miguel Escobar, M is for (DATA) MONKEY. Annan sinut vauhtiin.
Päätös tänään: Floridan Davidilla on kaksi työkirjaa, jotka hän haluaa yhdistää; niillä molemmilla on samat sarakkeet A, mutta kaikki muut sarakkeet ovat erilaisia; yhdessä työkirjassa voi olla ylimääräisiä kohteita, joita ei ole toisessa, ja David haluaa niitä; kummassakaan tiedostossa ei ole kaksoiskappaleita; aiomme käyttää virtakyselyä tämän ratkaisemiseksi, joten aloita uudesta tyhjästä työkirjasta tyhjällä laskentataulukolla; aiot tehdä kolme kyselyä, ensin yhden - Data, Tiedostosta, Työkirja ja sitten Lataa vain luotuun yhteyteen; sama asia toiselle työkirjalle, ja sitten Data, Hae tiedot, Yhdistä, valitse kaksi yhteyttä, valitse sarake, joka on yhteinen molemmissa - minun tapauksessani Tuote - ja sitten Liitytyypistä, jonka haluat liittyä täydellisesti kaikki tiedostosta 1, kaikki tiedostosta 2. Ja sitten on kaunis asia, jos taustalla olevat tiedot muuttuvat,voit vain päivittää kyselyn.
Voit ladata työkirjan tämän päivän videosta käymällä YouTube-kuvauksen URL-osoitteessa.
No, hei, haluan kuin David esiintyisi seminaarissani, haluan kiittää sinua pysähtymisestä. Nähdään ensi kerralla toisesta netcastista.
Lataa Excel-tiedosto
Excel-tiedoston lataaminen: combine-based-on-common-column.xlsx
Power Query on hämmästyttävä työkalu Excelissä.
Päivän Excel-ajatus
Olen pyytänyt Excel Master -kaveriltani neuvoja Excelistä. Tämän päivän ajatus miettiä:
"Paina aina F4, kun luet aluetta tai matriisia toiminnossa"
Tanja Kuhn
