David fra Florida stiller dagens spørsmål:
Jeg har to arbeidsbøker. Begge har de samme dataene i kolonne A, men de resterende kolonnene er forskjellige. Hvordan kan jeg slå sammen de to arbeidsbøkene?
Jeg spurte David om det er mulig at den ene arbeidsboken har flere poster enn den andre. Og svaret er ja. Jeg spurte David om nøkkelfeltet bare vises en gang i hver fil. Svaret er også ja. I dag skal jeg løse dette med Power Query. Power Query-verktøyene finnes i Windows-versjoner av Excel 2016+ i delen Get & Transform i Data-fanen. Hvis du har Windows-versjoner av Excel 2010 eller Excel 2013, kan du laste ned Power Query-tillegget for disse versjonene.
Her er Davids arbeidsbok 1. Den har produkt og deretter tre kolonner med data.
Her er Davids arbeidsbok 2. Den har produktkode og deretter andre kolonner. I dette eksemplet er det ekstra produkter i arbeidsbok2, men løsningene fungerer hvis en arbeidsbok har ekstra kolonner.
Her er trinnene:
-
Velg Data, Få data, Fra fil, Fra arbeidsbok:
Last inn data fra en fil - Bla til den første arbeidsboken og klikk OK
- Velg regnearket til venstre i dialogboksen Navigator. (Selv om det bare er ett regneark, må du velge det.) Du vil se dataene til høyre.
- I dialogboksen Navigator åpner du rullegardinmenyen Last og velger Last til …
- Velg Bare Opprett en tilkobling og trykk OK.
-
Gjenta trinn 1-5 for den andre arbeidsboken.
Opprett en forbindelse til arbeidsboken Hvis du har gjort begge arbeidsbøkene, bør du se to tilkoblinger på spørrings- og tilkoblingspanelet til høyre på Excel-skjermen.
Tilkoblinger til begge arbeidsbøkene Fortsett med trinnene for å slå sammen arbeidsbøkene:
-
Data, få data, kombinere spørsmål, slå sammen.
Slå sammen to spørsmål med forskjellige kolonner - Velg den første spørringen fra rullegardinmenyen øverst i Merge-dialogboksen.
- Velg den andre spørringen fra den andre rullegardinmenyen i Merge-dialogboksen.
- Klikk på produktoverskriften i toppforhåndsvisning (dette er nøkkelfeltet. Merk at du kan velge flere eller flere nøkkelfelt ved å Ctrl + klikke)
- Klikk på overskriften Produktkode i den andre forhåndsvisningen.
-
Åpne Join Type og velg Full Ytre (Alle rader fra begge)
Trinn 8 - 12 illustrert her -
Klikk OK. Forhåndsvisning av data viser ikke de ekstra radene og viser bare "Tabell" gjentatte ganger i den siste kolonnen.
Dette ser ikke lovende ut - Legg merke til at det er et "Utvid" -ikon i overskriften til DavidTwo. Klikk på ikonet.
-
Valgfritt, men jeg fjerner alltid avmerkingen "Bruk originalt kolonnenavn som prefiks". Klikk OK.
Utvid feltene fra arbeidsbok 2 Resultatene vises i denne forhåndsvisningen:
Alle poster fra arbeidsboken - I Power Query bruker du Hjem, Lukk og last.
Her er den vakre funksjonen: Hvis de underliggende dataene i en arbeidsbok endres, kan du klikke på Oppdater-ikonet for å hente nye data inn i resultatarbeidsboken.
Merk
Ikonet for Oppdater er vanligvis skjult. Dra venstre kant av spørrings- og tilkoblingsruten til venstre for å avsløre ikonet.
Se på video
Videoutskrift
Lær Excel fra Podcast, episode 2216: Kombiner to arbeidsbøker basert på en vanlig kolonne.
Hei, velkommen tilbake til netcast, jeg er Bill Jelen. Dagens spørsmål er fra David, som var på seminaret mitt i Melbourne, Florida, for Space Coast Chapter of the IIA.
David har to forskjellige arbeidsbøker der kolonne A er felles mellom dem begge. Så her er arbeidsbok 1, her er arbeidsbok 2 - begge har produktkode. Denne har gjenstander som den første ikke har, eller omvendt, og David vil kombinere alle kolonnene. Så vi har tre kolonner her og fire kolonner her. Jeg legger begge disse i samme arbeidsbok, i tilfelle du laster ned arbeidsboken for å jobbe sammen. Ta hver av disse, flytt den ut til sin egen arbeidsbok og lagre den.
Greit, for å kombinere disse filene, skal vi bruke Power Query. Power Query er innebygd i Excel 2016. Hvis du er i Windows-versjonen av 10 eller 13, kan du gå ut til Microsoft og laste ned Power Query. Du kan starte fra en ny tom arbeidsbok med et tomt regneark. Du kommer til å lagre denne filen - Lagre som, du vet, kanskje arbeidsbok, for å vise resultatene av kombinerte filer .xlsx. Ok? Og det vi skal gjøre er at vi skal gjøre to spørsmål. Vi skal gå til Data, Få data, Fra fil, Fra arbeidsbok, og så velger vi den første filen. I en forhåndsvisning velger du arket som inneholder dataene dine, og vi trenger ikke gjøre noe med disse dataene. Så bare åpne lastboksen og velg Last til, bare opprett forbindelse, klikk OK. Perfekt. Nå skal vi gjenta det for det andre elementet - Data, fra fil,Velg DavidTwo fra en arbeidsbok, velg arknavnet, og åpne deretter lasten, Last til, bare opprett en forbindelse. Du ser her i dette panelet, vi har begge forbindelsene til stede. Ok.
Nå er det faktiske arbeidet - Data, Get Data, Combine Queries, Merge, og velg deretter DavidOne, DavidTwo i dialogboksen Merge, og dette neste trinnet er helt uintuitivt. Du må gjøre dette. Velg kolonnen eller kolonnene til felles - så Produkt og produkt. Ok. Og vær veldig forsiktig her med sammenføyningstypen. Jeg vil ha alle rader fra begge deler fordi man kan ha en ekstra rad, og jeg må se det, og så klikker vi OK. Ok. Og her er det første resultatet. Det ser ikke ut som det virket; det ser ikke ut som det la til de ekstra elementene som var i fil 2. Og vi har denne kolonnen 5 - den er null nå. Jeg skal høyreklikke kolonne 5 og si, Fjern den kolonnen. Så åpne dette utvidelsesikonet og fjern merket for Bruk original kolonnenavn som prefiks, og BAM! det fungerer. Så de ekstra elementene som var i fil 2, som ikke er i fil 1,vises.
Ok. Nå i dagens fil ser det ut som at denne produktkodekolonnen er bedre enn denne produktkolonnen, fordi den har ekstra rader. Men det kan være en dag i fremtiden der Arbeidsbok 1 har ting som Arbeidsbok 2 ikke har. Så jeg kommer til å la dem begge være der, og jeg kommer ikke til å bli kvitt noen nullstyper, som om, selv om denne raden nederst ser ut til å være helt null, kan det i fremtiden være en situasjon der vi har noen få nuller her fordi noe mangler. Ok? Så til slutt, Close & Load, og vi har våre seksten rader.
La oss si at noe endres i fremtiden. Greit, så vi går tilbake til en av de to filene, så endrer jeg klassen for Apple til 99, og la oss til og med sette inn noe nytt og lagre denne arbeidsboken. Ok. Og hvis vi vil at flettefilen vår skal oppdateres, kom hit - nå, se opp, når du gjør dette første gang, kan du ikke se Oppdater-ikonet - du må ta tak i denne linjen og dra den over . Og vi vil gjøre Oppdater, og 17 rader lastet, vannmelon vises, Apple endres til 99 - det er en vakker ting. Nå, hei, vil du lære om Power Query? Kjøp denne boka av Ken Puls og Miguel Escobar, M er for (DATA) AAP. Jeg får deg opp i fart.
Avslutning i dag: David fra Florida har to arbeidsbøker som han vil kombinere; de har begge de samme feltene i kolonne A, men de andre kolonnene er forskjellige; den ene arbeidsboken kan ha ekstra ting som ikke er i den andre, og David vil ha dem; det er ingen duplikater i noen av filene; vi skal bruke strømforespørsel for å løse dette, så start i en ny tom arbeidsbok på et tomt regneark; du kommer til å gjøre tre spørsmål, først en - Data, Fra fil, arbeidsbok, og deretter Last til bare opprettet tilkobling; det samme for den andre arbeidsboken, og deretter Data, Få data, Slå sammen, velg de to tilkoblingene, velg kolonnen som er vanlig i begge - i mitt tilfelle Produkt - og deretter fra Join Type, vil du fullstendig bli med alt fra fil 1, alt fra fil 2. Og så er det vakre hvis de underliggende dataene endres,du kan bare oppdatere spørringen.
For å laste ned arbeidsboken fra dagens video, besøk URL-en i YouTube-beskrivelsen.
Vel, hei, jeg vil som David for å møte opp til seminaret mitt, jeg vil takke deg for at du var innom. Vi sees neste gang for en ny netcast fra.
Last ned Excel-fil
For å laste ned excel-filen: combine-based-on-common-column.xlsx
Power Query er et fantastisk verktøy i Excel.
Excel-tanken om dagen
Jeg har bedt mine Excel Master-venner om deres råd om Excel. Dagens tanke å tenke på:
"Trykk alltid på F4 når du leser rekkevidde eller matrise i en funksjon"
Tanja Kuhn
