Med Power Query har det blitt enklere å jobbe med data spredt over regneark eller til og med arbeidsbøker.
En av tingene der Power Query kan spare deg for mye tid er når du må slå sammen tabeller med forskjellige størrelser og kolonner basert på en matchende kolonne.
Nedenfor er en video hvor jeg viser nøyaktig hvordan du slår sammen tabeller i Excel ved hjelp av Power Query.
Hvis du foretrekker å lese teksten fremfor å se på en video, er de skriftlige instruksjonene nedenfor.
Anta at du har et bord som vist nedenfor:
Denne tabellen har dataene jeg vil bruke, men den mangler fortsatt to viktige kolonner - "Produkt -ID" og "Region" der selgeren opererer.
Denne informasjonen er gitt som separate tabeller som vist nedenfor:
For å få all denne informasjonen til en enkelt tabell, må du slå sammen disse tre tabellene slik at du deretter kan lage en pivottabell og analysere den, eller bruke den til andre rapporterings-/dashbordformål.
Og med sammenslåing mener jeg ikke en enkel kopieringspasta.
Du må kartlegge de relevante postene fra tabell 1 med data fra tabell 2 og 3.
Nå kan du stole på VLOOKUP eller INDEX/MATCH for å gjøre dette.
Eller hvis du er en VBA -suser, kan du skrive en kode for å gjøre dette.
Men disse alternativene er tidkrevende og kompliserte sammenlignet med Power Query.
I denne opplæringen vil jeg vise deg hvordan du slår sammen disse tre Excel -tabellene til en.
For at denne teknikken skal fungere, må du ha tilkoblingskolonner. For eksempel, i tabell 1 og tabell 2, er den vanlige kolonnen "vare", og i tabell 1 og tabell 3 er den vanlige kolonnen "Salgsrepresentant". Vær også oppmerksom på at det ikke bør gjentas i disse tilkoblingskolonnene.Merk: Power Query kan brukes som et tillegg i Excel 2010 og 2013, og er en innebygd funksjon fra Excel 2016 og fremover. Basert på din versjon kan noen bilder se annerledes ut (bildeopptak som brukes i denne opplæringen er fra Excel 2016).
Slå sammen tabeller ved hjelp av Power Query
Jeg har navngitt disse tabellene som vist nedenfor:
- Tabell 1 - Salgsdata
- Tabell 2 - Pdt_Id
- Tabell 3 - Region
Det er ikke obligatorisk å gi disse tabellene et nytt navn, men det er bedre å gi navn som beskriver hva tabellen handler om.
På en gang kan du bare slå sammen to tabeller i Power Query.
Så vi må først slå sammen tabell 1 og tabell 2 og deretter slå sammen tabell 3 i den i neste trinn.
Sammenslåing av tabell 1 og tabell 2
For å slå sammen tabeller må du først konvertere disse tabellene til tilkoblinger i Power Query. Når du har tilkoblingene, kan du enkelt slå dem sammen.
Her er trinnene for å lagre en Excel -tabell som en tilkobling i Power Query:
- Velg en celle i tabellen Sales_Data.
- Klikk kategorien Data.
- I Get & Transform -gruppen klikker du på ‘From Table/Range’. Dette åpner spørringsredigereren.
- Klikk på "Fil" -fanen i spørreditoren.
- Klikk på alternativet "Lukk og last inn".
- Velg "Bare opprett tilkobling" i dialogboksen "Importer data".
- Klikk OK.
Trinnene ovenfor vil opprette en forbindelse med navnet Sales_Data (eller et hvilket som helst navn du har gitt Excel -tabellen).
Gjenta trinnene ovenfor for tabell 2 og tabell 3.
Så når du er ferdig, har du tre tilkoblinger (med navnet Sales_Data, Pdt_Id og Region).
La oss nå se hvordan du slår sammen tabellen Sales_Data og Pdt_Id.
- Klikk på fanen Data.
- Klikk på Hent data i gruppen Hent og transformer data.
- Klikk på Kombiner forespørsler i rullegardinmenyen.
- Klikk på Slå sammen. Dette åpner dialogboksen Flett.
- I dialogboksen Flett velger du ‘Sales_Data’ fra den første rullegardinmenyen.
- Velg 'Pdt_Id' fra den andre rullegardinmenyen.
- Klikk på "Element" -kolonnen i forhåndsvisningen "Sales_Data". Hvis du gjør dette, vil du velge hele kolonnen.
- I forhåndsvisningen "Pdt_Id" klikker du på "Element" -kolonnen. Hvis du gjør dette, vil du velge hele kolonnen.
- I rullegardinlisten "Bli med" velger du "Venstre ytre (alt fra første, matchende fra andre)".
- Klikk OK.
Trinnene ovenfor åpner forespørgselseditoren og viser deg dataene fra Sales_Data med en ekstra kolonne (av Pdt_Id).
Slå sammen Excel -tabellene (tabell 1 og 2)
Nå vil prosessen med å slå sammen tabellene skje i Query -editoren med følgende trinn:
- I den ekstra kolonnen (Pdt_Id) klikker du på pilen med dobbel spiss i overskriften.
- I alternativboksen som åpnes, fjerner du merket for alle kolonnenavnene og velger bare Element. Dette er fordi vi allerede har produktnavnkolonnen i den eksisterende tabellen, og vi bare vil ha produkt -ID -en for hvert produkt.
- Fjern merket for alternativet "Bruk det originale kolonnenavnet som prefiks".
- Klikk Ok.
Dette vil gi deg den resulterende tabellen som har hver post fra Sales_Data -tabellen og en ekstra kolonne som også har produkt -ID (fra Pdt_Id -tabellen).
Hvis du bare vil kombinere to tabeller, kan du laste inn denne Excel -filen du er ferdig med.
Men vi har tre bord å slå sammen, så det er mer arbeid som må gjøres.
Du må lagre denne resulterende tabellen som en tilkobling (slik at vi kan bruke den til å slå den sammen med tabell 3).
Her er trinnene for å lagre denne sammenslåtte tabellen (med data fra Sales_Data og Pdt_Id -tabellen) som en tilkobling:
- Klikk kategorien Fil
- Klikk på alternativet "Lukk og last til".
- Velg "Bare opprett tilkobling" i dialogboksen "Importer data".
- Klikk OK.
Dette vil lagre de nylig sammenslåtte dataene som en tilkobling. Du kan gi nytt navn til denne tilkoblingen hvis du vil.
Sammenslåing av tabell 3 med den resulterende tabellen
Prosessen med å slå sammen den tredje tabellen med den resulterende tabellen (som vi fikk ved å slå sammen tabell 1 og tabell 2) er nøyaktig den samme.
Her er trinnene for å slå sammen disse tabellene:
- Klikk på fanen Data.
- Klikk på "Få data" i gruppen Hent og transformer data.
- Klikk på "Kombiner forespørsler" i rullegardinmenyen.
- Klikk på "Slå sammen". Dette åpner dialogboksen Flett.
- I dialogboksen Flett velger du "Flette1" fra den første rullegardinmenyen.
- Velg 'Region' fra den andre rullegardinmenyen.
- I forhåndsvisningen "Merge1" klikker du på "Salgsrepresentant" -kolonnen. Hvis du gjør dette, vil du velge hele kolonnen.
- Klikk på "Salgsrepresentant" -kolonnen i forhåndsvisningen av regionen. Hvis du gjør dette, vil du velge hele kolonnen.
- Velg Venstre ytre (alle fra første, matchende fra andre) i rullegardinmenyen "Bli med snill".
- Klikk OK.
Trinnene ovenfor åpner forespørgselseditoren og viser deg dataene fra Merge1 med en ekstra kolonne (region).
Nå vil prosessen med å slå sammen tabellene skje i Query -editoren med følgende trinn:
- I tilleggskolonnen (region) klikker du på pilen med dobbel spiss i overskriften.
- I alternativboksen som åpnes, fjerner du merket for alle kolonnenavnene og velger bare Region.
- Fjern merket for alternativet "Bruk det originale kolonnenavnet som prefiks".
- Klikk Ok.
Trinnene ovenfor gir deg en tabell som har alle de tre tabellene slått sammen (Sales_Data -tabell med en kolonne for Pdt_Id og en for Region).
Her er trinnene for å laste denne tabellen i Excel:
- Klikk kategorien Fil.
- Klikk på "Lukk og last til".
- I tabellen "Importer data" velger du alternativer for tabell og nye regneark.
- Klikk OK.
Dette vil gi deg den resulterende sammenslåtte tabellen i et nytt regneark.
En av de beste tingene med Power Query er at du enkelt kan imøtekomme eventuelle endringer i de underliggende dataene (tabell 1, 2 og 3) ved å oppdatere dem.
Anta for eksempel at Laura blir overført til Asia, og du får nye data for den neste måneden. Nå trenger du ikke gjenta trinnene ovenfor igjen. Alt du trenger å gjøre er å oppdatere bordet, og det vil gjøre alt igjen for deg.
I løpet av sekunder får du det nye sammenslåtte bordet.
Du kan også like følgende opplæringsprogrammer for Power Query:
- Kombiner data fra flere arbeidsbøker i Excel (ved hjelp av Power Query).
- Kombiner data fra flere regneark til et enkelt regneark i Excel.
- Slik fjerner du data i Excel ved hjelp av Power Query (også kalt Get & Transform)
- Få en liste over filnavn fra mapper og undermapper (ved hjelp av Power Query)
