✅ Kombiner data fra flere regneark til et enkelt regneark i Excel

Indholdsfortegnelse

Kombiner data fra flere regneark ved hjælp af Power Query

Jeg fik for nylig et spørgsmål fra en læser om at kombinere flere regneark i den samme projektmappe til et enkelt regneark.

Jeg bad ham om at bruge Power Query til at kombinere forskellige ark, men så indså jeg, at det kan være svært for en ny til Power Query.

Så jeg besluttede at skrive denne vejledning og vise de nøjagtige trin til at kombinere flere ark til en enkelt tabel ved hjælp af Power Query.

Nedenfor en video, hvor jeg viser, hvordan jeg kombinerer data fra flere ark/tabeller ved hjælp af Power Query:

Nedenfor er der skrevet instruktioner om, hvordan du kombinerer flere ark (hvis du foretrækker skriftlig tekst frem for video).

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).

Kombiner data fra flere regneark ved hjælp af Power Query

Når du kombinerer data fra forskellige ark ved hjælp af Power Query, er det påkrævet at have dataene i en Excel -tabel (eller i det mindste i navngivne områder). Hvis dataene ikke er i en Excel -tabel, fungerer metoden vist her ikke.

Antag, at du har fire forskellige ark - øst, vest, nord og syd.

Hvert af disse regneark har dataene i en Excel -tabel, og tabellens struktur er konsistent (dvs. overskrifterne er ens).

Klik her for at downloade dataene og følge med.

Denne form for data er ekstremt let at kombinere ved hjælp af Power Query (som fungerer rigtig godt med data i Excel -tabel).

For at denne teknik fungerer bedst, er det bedre at have navne til dine Excel -tabeller (arbejde også uden det, men det er lettere at bruge, når tabellerne navngives).

Jeg har givet tabellerne følgende navne: East_Data, West_Data, North_Data og South_Data.

Her er trinene til at kombinere flere regneark med Excel -tabeller ved hjælp af Power Query:

Gå til fanen Data.
I gruppen Hent og transformer data skal du klikke på indstillingen ‘Hent data’.
Gå til indstillingen 'Fra andre kilder'.
Klik på indstillingen 'Tom forespørgsel'. Dette åbner Power Query -editoren.
I forespørgselseditoren skal du skrive følgende formel i formellinjen: = Excel.CurrentWorkbook(). Bemærk, at Power Query -formlerne er store og små bogstaver, så du skal bruge den nøjagtige formel som nævnt (ellers får du en fejl).
Tryk på Enter -tasten. Dette viser dig alle tabelnavnene i hele projektmappen (den viser dig også de navngivne områder og/eller forbindelser, hvis den findes i projektmappen).
[Valgfrit trin] I dette eksempel vil jeg kombinere alle tabellerne. Hvis du kun vil kombinere specifikke Excel-tabeller, kan du klikke på rullemenuen i navneoverskriften og vælge dem, du vil kombinere. På samme måde, hvis du har navngivet områder eller forbindelser, og du kun vil kombinere tabeller, kan du også fjerne disse navngivne områder.
I cellen Indholdsoverskrift skal du klikke på pilen med dobbelt spids.
Vælg de kolonner, du vil kombinere. Hvis du vil kombinere alle kolonner, skal du kontrollere, at (Vælg alle kolonner) er markeret.
Fjern markeringen i afsnittet ‘Brug det originale kolonnenavn som præfiks’.
Klik på OK.

Ovenstående trin kombinerer dataene fra alle regnearkene til en enkelt tabel.

Hvis du kigger godt efter, finder du den sidste kolonne (til højre) med navnet på Excel -tabellerne (East_Data, West_Data, North_Data og South_Data). Dette er en identifikator, der fortæller os, hvilken post der kom fra hvilken Excel -tabel. Dette er også grunden til, at jeg sagde, at det er bedre at have beskrivende navne til Excel -tabellerne.

Her er et par ændringer, du kan foretage de kombinerede data i selve Power Query:

Træk og placer kolonnen Navn til begyndelsen.
Fjern "_Data" fra navnekolonnen (så du står tilbage med øst, vest, nord og syd i navnekolonnen). For at gøre dette skal du højreklikke på overskriften Navn og klikke på Erstat værdier. I dialogboksen Erstat værdier skal du erstatte _Data med et tomt.
Skift kolonnen Data for kun at vise datoer (og ikke tidspunktet). For at gøre dette skal du klikke på kolonneoverskriften Dato, gå til fanen 'Transform' og ændre datatypen til Dato.
Omdøb forespørgslen til ConsolidatedData.

Nu hvor du har de kombinerede data fra alle regnearkene i Power Query, kan du indlæse dem i Excel - som en ny tabel i et nyt regneark.

At gøre dette. følg nedenstående trin:

Klik på fanen ‘Filer’.
Klik på Luk og indlæs til.
I dialogboksen Importer data skal du vælge Indstillinger for tabel og nyt regneark.
Klik på Ok.

Ovenstående trin kombinerer data fra alle regnearkene og giver dig de kombinerede data i et nyt regneark.

Et problem, du skal løse, når du bruger denne metode

Hvis du har brugt ovenstående metode til at kombinere alle tabellerne i projektmappen, står du sandsynligvis over for et problem.

Se antallet af rækker med de kombinerede data - 1304 (hvilket er rigtigt).

Nu, hvis jeg opdaterer forespørgslen, ændres antallet af rækker til 2607. Opdater igen, og den ændres til 3910.

Her er problemet.

Hver gang du opdaterer forespørgslen, tilføjer den alle poster i de originale data til de kombinerede data.

Bemærk: Du står kun over for dette problem, hvis du har brugt Power Query til at kombinere ALLE de fremragende tabeller i projektmappen. Hvis du har valgt bestemte tabeller, der skal kombineres, står du ikke over for dette problem.

Lad os forstå årsagen til dette problem, og hvordan vi retter det.

Når du opdaterer en forespørgsel, går den tilbage og følger alle de trin, vi tog for at kombinere dataene.

I det trin, hvor vi brugte formlen = Excel.CurrentWorkbook (), det gav os en liste over alle tabellerne. Dette fungerede fint første gang, da der kun var fire borde.

Men når du opdaterer, er der fem tabeller i projektmappen - inklusive den nye tabel, som Power Query indsatte, hvor vi har de kombinerede data.

Så hver gang du opdaterer forespørgslen, bortset fra de fire Excel -tabeller, som vi vil kombinere, tilføjer den også den eksisterende forespørgselstabel til de resulterende data.

Dette kaldes rekursion.

Sådan løser du dette problem.

Når du har indsat = Excel.CurrentWorkbook () i formularlinjen i Power Query og trykket på enter, får du en liste over Excel -tabeller. For at sikre, at du kun får kombineret tabellerne fra regnearket, skal du på en eller anden måde kun filtrere disse tabeller, som du vil kombinere, og fjerne alt andet.

Her er trinene til at sikre, at du kun har de nødvendige tabeller:

Klik på rullemenuen, og hold markøren på tekstfiltre.
Klik på indstillingen Indeholder.
I dialogboksen Filtrer rækker skal du indtaste _Data i feltet ud for indstillingen 'indeholder'.
Klik på OK.

Du kan muligvis ikke se nogen ændringer i dataene, men hvis du gør dette, forhindres den resulterende tabel i at blive tilføjet igen, når forespørgslen opdateres.

Bemærk, at vi i ovenstående trin har brugt "_Data”For at filtrere, da vi navngav tabeller på den måde. Men hvad nu hvis dine borde ikke hedder konsekvent. Hvad hvis alle bordnavne er tilfældige og ikke har noget tilfælles.

Her er måden at løse dette på - brug filteret 'gør ikke lig med' og indtast navnet på forespørgslen (hvilket ville være ConsolidatedData i vores eksempel). Dette sikrer, at alt forbliver det samme, og den resulterende forespørgselstabel, der oprettes, filtreres fra.

Bortset fra det faktum, at Power Query gør hele denne proces med at kombinere data fra forskellige ark (eller endda det samme ark) ganske let, en anden fordel ved at bruge det, at det gør det dynamisk. Hvis du tilføjer flere poster til en af tabellerne og opdaterer Power Query, får du automatisk de kombinerede data.

Vigtig note: I eksemplet, der blev brugt i denne vejledning, var overskrifterne de samme. Hvis overskrifterne er forskellige, kombinerer og opretter Power Query alle kolonnerne i den nye tabel. Hvis dataene er tilgængelige for den kolonne, vil de blive vist, ellers vil de vise null.

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