✅ Flet tabeller i Excel ved hjælp af Power Query (nem trin-for-trin vejledning)

Indholdsfortegnelse

Flet tabeller ved hjælp af Power Query

Med Power Query er det blevet lettere at arbejde med data spredt på tværs af regneark eller endda projektmapper.

En af de ting, hvor Power Query kan spare dig en masse tid, er, når du skal flette tabeller med forskellige størrelser og kolonner baseret på en matchende kolonne.

Nedenfor er en video, hvor jeg viser præcis, hvordan man fletter tabeller i Excel ved hjælp af Power Query.

Hvis du foretrækker at læse teksten frem for at se en video, er de skriftlige instruktioner herunder.

Antag, at du har en tabel som vist herunder:

Denne tabel har de data, jeg vil bruge, men det mangler stadig to vigtige kolonner - 'Produkt -id' og 'Region', hvor salgsrepræsentanten opererer.

Disse oplysninger leveres som separate tabeller som vist nedenfor:

For at få alle disse oplysninger til en enkelt tabel skal du flette disse tre tabeller, så du derefter kan oprette en pivottabel og analysere dem eller bruge dem til andre rapporterings-/dashboardformål.

Og ved at fusionere mener jeg ikke en simpel kopipasta.

Du skal kortlægge de relevante poster fra tabel 1 med data fra tabel 2 og 3.

Nu kan du stole på VLOOKUP eller INDEX/MATCH for at gøre dette.

Eller hvis du er en VBA whiz, kan du skrive en kode for at gøre dette.

Men disse muligheder er tidskrævende og komplicerede i forhold til Power Query.

I denne vejledning viser jeg dig, hvordan du fletter disse tre Excel -tabeller til en.

For at denne teknik skal fungere, skal du have forbindelsessøjler. For eksempel i tabel 1 og tabel 2 er den almindelige kolonne 'vare', og i tabel 1 og tabel 3 er den fælles kolonne 'Salgsrepræsentant'. Bemærk også, at der ikke bør gentages i disse forbindelsessøjler.

Bemærk: Power Query kan bruges som et tilføjelsesprogram i Excel 2010 og 2013 og er en indbygget funktion fra Excel 2016 og fremefter. Baseret på din version kan nogle billeder se anderledes ud (billedoptagelser, der bruges i denne vejledning, er fra Excel 2016).

Flet tabeller ved hjælp af Power Query

Jeg har navngivet disse tabeller som vist herunder:

Tabel 1 - Salgsdata
Tabel 2 - Pdt_Id
Tabel 3 - Område

Det er ikke obligatorisk at omdøbe disse tabeller, men det er bedre at give navne, der beskriver, hvad tabellen handler om.

På én gang kan du kun flette to tabeller i Power Query.

Så vi bliver først nødt til at flette tabel 1 og tabel 2 og derefter flette tabel 3 ind i den i det næste trin.

Sammenlægning af tabel 1 og tabel 2

For at flette tabeller skal du først konvertere disse tabeller til forbindelser i Power Query. Når du har forbindelserne, kan du let flette disse.

Her er trinene til at gemme en Excel -tabel som en forbindelse i Power Query:

Vælg en celle i tabellen Sales_Data.
Klik på fanen Data.
I gruppen Hent og transformer skal du klikke på 'Fra tabel/område'. Dette åbner forespørgselseditoren.
Klik på fanen 'Fil' i forespørgselseditoren.
Klik på "Luk og indlæs til".
I dialogboksen 'Importer data' skal du vælge 'Kun Opret forbindelse'.
Klik på OK.

Ovenstående trin ville skabe en forbindelse med navnet Sales_Data (eller et hvilket som helst navn, du har givet Excel -tabellen).

Gentag ovenstående trin for tabel 2 og tabel 3.

Så når du er færdig, har du tre forbindelser (med navnet Sales_Data, Pdt_Id og Region).

Lad os nu se, hvordan du fletter tabellen Sales_Data og Pdt_Id.

Klik på fanen Data.
Klik på Hent data i gruppen Hent og transformer data.
Klik på Kombiner forespørgsler i rullemenuen.
Klik på Flet. Dette åbner dialogboksen Flet.
I dialogboksen Flet skal du vælge 'Sales_Data' fra den første rulleliste.
Vælg 'Pdt_Id' fra den anden rullemenu.
I forhåndsvisningen af ‘Sales_Data’ skal du klikke på kolonnen ‘Item’. Hvis du gør dette, vælges hele kolonnen.
I forhåndsvisningen af 'Pdt_Id' skal du klikke på kolonnen 'Item'. Hvis du gør dette, vælges hele kolonnen.
I rullemenuen 'Join Kind' skal du vælge 'Venstre ydre (alt fra første, matchende fra andet)'.
Klik på OK.

Ovenstående trin åbner forespørgselseditoren og viser dig dataene fra Sales_Data med en ekstra kolonne (af Pdt_Id).

Fletning af Excel -tabellerne (tabel 1 og 2)

Nu vil processen med at flette tabellerne ske i forespørgselseditoren med følgende trin:

I den ekstra kolonne (Pdt_Id) skal du klikke på den dobbeltspidsede pil i overskriften.
Fjern markeringen af alle kolonnenavne i indstillingsboksen, der åbnes, og vælg kun element. Dette skyldes, at vi allerede har kolonnen produktnavn i den eksisterende tabel, og vi kun vil have produkt -id'et for hvert produkt.
Fjern markeringen af indstillingen 'Brug det originale kolonnenavn som præfiks'.
Klik på Ok.

Dette ville give dig den resulterende tabel, der har hver post fra Sales_Data -tabellen og en ekstra kolonne, der også har produkt -id'er (fra tabellen Pdt_Id).

Hvis du nu kun vil kombinere to tabeller, kan du indlæse denne Excel, du er færdig.

Men vi har tre borde, der skal flettes, så der er mere arbejde, der skal udføres.

Du skal gemme denne resulterende tabel som en forbindelse (så vi kan bruge den til at flette den med tabel 3).

Her er trinene til at gemme denne flettede tabel (med data fra Sales_Data og Pdt_Id -tabellen) som en forbindelse:

Klik på fanen Filer
Klik på "Luk og indlæs til".
I dialogboksen 'Importer data' skal du vælge 'Kun opret forbindelse'.
Klik på OK.

Dette vil gemme de nyligt flettede data som en forbindelse. Du kan omdøbe denne forbindelse, hvis du vil.

Flette tabel 3 med den resulterende tabel

Processen med at flette den tredje tabel med den resulterende tabel (som vi fik ved at flette tabel 1 og tabel 2) er nøjagtig den samme.

Her er trinene til at flette disse tabeller:

Klik på fanen Data.
Klik på ‘Hent data’ i gruppen Hent og transformer data.
Klik på 'Kombiner forespørgsler' i rullemenuen.
Klik på 'Flet'. Dette åbner dialogboksen Flet.
I dialogboksen Flet skal du vælge 'Flet 1' fra den første rulleliste.
Vælg 'Region' fra den anden rullemenu.
Klik på kolonnen "Salgsrepræsentant" i forhåndsvisningen "Flet1". Hvis du gør dette, vælges hele kolonnen.
Klik på kolonnen "Salgsrepræsentant" i forhåndsvisningen af regionen. Hvis du gør dette, vælges hele kolonnen.
Vælg venstre ydre i rullemenuen 'Join Kind' (alle fra første, matchende fra anden).
Klik på OK.

Ovenstående trin åbner forespørgselseditoren og viser dig dataene fra Merge1 med en ekstra kolonne (region).

Nu vil processen med at flette tabellerne ske i forespørgselseditoren med følgende trin:

I den ekstra kolonne (Region) skal du klikke på den dobbeltspidsede pil i overskriften.
Fjern markeringen af alle kolonnenavne i indstillingsboksen, der åbnes, og vælg kun Region.
Fjern markeringen af indstillingen 'Brug det originale kolonnenavn som præfiks'.
Klik på Ok.

Ovenstående trin ville give dig en tabel, der har alle de tre tabeller fusioneret (Sales_Data -tabel med en kolonne for Pdt_Id og en for Region).

Her er trinene til at indlæse denne tabel i Excel:

Klik på fanen Filer.
Klik på 'Luk og indlæs til'.
I dialogboksen 'Importer data' skal du vælge indstillinger for tabel og nye regneark.
Klik på OK.

Dette ville give dig den resulterende fusionerede tabel i et nyt regneark.

En af de bedste ting ved Power Query er, at du nemt kan rumme eventuelle ændringer i de underliggende data (tabel 1, 2 og 3) ved blot at opdatere dem.

Antag for eksempel, at Laura bliver overført til Asien, og du får nye data for den næste måned. Nu behøver du ikke at gentage ovenstående trin igen. Alt du skal gøre er at opdatere bordet, og det vil gøre alt igen for dig.

Inden for få sekunder har du det nye flettede bord.

Du kan også lide følgende Power Query -selvstudier: