Jeg fikk nylig et spørsmål fra en leser om å kombinere flere regneark i samme arbeidsbok til ett enkelt regneark.
Jeg spurte ham om å bruke Power Query til å kombinere forskjellige ark, men da innså jeg at det kan være tøft for noen som er nye i Power Query.
Så jeg bestemte meg for å skrive denne opplæringen og vise de nøyaktige trinnene for å kombinere flere ark til ett bord ved hjelp av Power Query.
Under en video der jeg viser hvordan jeg kombinerer data fra flere ark/tabeller ved hjelp av Power Query:
Nedenfor er det skrevet instruksjoner om hvordan du kombinerer flere ark (i tilfelle du foretrekker skrevet tekst fremfor video).
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).
Kombiner data fra flere regneark ved hjelp av Power Query
Når du kombinerer data fra forskjellige ark med Power Query, er det nødvendig å ha dataene i en Excel -tabell (eller i det minste i navngitte områder). Hvis dataene ikke er i en Excel -tabell, fungerer ikke metoden som vises her.
Anta at du har fire forskjellige ark - øst, vest, nord og sør.
Hvert av disse regnearkene har dataene i en Excel -tabell, og tabellens struktur er konsistent (dvs. overskriftene er de samme).
Klikk her for å laste ned dataene og følge med.
Denne typen data er ekstremt enkel å kombinere ved hjelp av Power Query (som fungerer veldig bra med data i Excel -tabellen).
For at denne teknikken skal fungere best, er det bedre å ha navn på Excel -tabellene (arbeid uten den også, men det er lettere å bruke når tabellene er navngitt).
Jeg har gitt tabellene følgende navn: East_Data, West_Data, North_Data og South_Data.
Her er trinnene for å kombinere flere regneark med Excel -tabeller ved hjelp av Power Query:
- Gå til fanen Data.
- Klikk på "Få data" -alternativet i Get & Transform Data -gruppen.
- Velg alternativet "Fra andre kilder".
- Klikk på alternativet "Tom forespørsel". Dette åpner Power Query -editoren.
- Skriv inn følgende formel i formellinjen i spørreditoren: = Excel.CurrentWorkbook(). Vær oppmerksom på at Power Query -formlene er store og små bokstaver, så du må bruke den nøyaktige formelen som nevnt (ellers får du en feil).
- Trykk Enter. Dette viser deg alle tabellnavnene i hele arbeidsboken (den viser deg også de navngitte områdene og/eller tilkoblingene i tilfelle den finnes i arbeidsboken).
- [Valgfritt trinn] I dette eksemplet vil jeg kombinere alle tabellene. Hvis du bare vil kombinere spesifikke Excel-tabeller, kan du klikke på rullegardinikonet i navnoverskriften og velge de du vil kombinere. På samme måte, hvis du har navngitt områder eller tilkoblinger, og du bare vil kombinere tabeller, kan du også fjerne de navngitte områdene.
- I innholdsoverskriftscellen klikker du på pilen med dobbel spiss.
- Velg kolonnene du vil kombinere. Hvis du vil kombinere alle kolonnene, må du kontrollere at (Velg alle kolonner) er merket av.
- Fjern merket for alternativet "Bruk originalt kolonnenavn som prefiks".
- Klikk OK.
Trinnene ovenfor vil kombinere dataene fra alle regnearkene til en enkelt tabell.
Hvis du ser nøye ut, finner du den siste kolonnen (lengst til høyre) med navnet på Excel -tabellene (East_Data, West_Data, North_Data og South_Data). Dette er en identifikator som forteller oss hvilken post som kom fra hvilken Excel -tabell. Dette er også grunnen til at jeg sa at det er bedre å ha beskrivende navn for Excel -tabellene.
Her er noen få endringer du kan gjøre på de kombinerte dataene i selve Power Query:
- Dra og plasser Navn -kolonnen til begynnelsen.
- Fjern "_Data" fra navnekolonnen (slik at du sitter igjen med øst, vest, nord og sør i navnekolonnen). For å gjøre dette, høyreklikk på Navneoverskriften og klikk på Erstatt verdier. I dialogboksen Erstatt verdier erstatter du _Data med et tomt.
- Endre datakolonnen for å vise bare datoer (og ikke klokkeslettet). For å gjøre dette, klikk på Dato -kolonneoverskriften, gå til kategorien "Transform" og endre datatypen til Dato.
- Gi nytt navn til spørringen til ConsolidatedData.
Nå som du har de kombinerte dataene fra alle regnearkene i Power Query, kan du laste dem inn i Excel - som en ny tabell i et nytt regneark.
Å gjøre dette. følg trinnene nedenfor:
- Klikk på "Fil" -fanen.
- Klikk på Lukk og last til.
- I dialogboksen Importer data velger du Tabell og nye regnearkalternativer.
- Klikk Ok.
Trinnene ovenfor vil kombinere data fra alle regnearkene og gi deg de kombinerte dataene i et nytt regneark.
Ett problem du må løse når du bruker denne metoden
Hvis du har brukt metoden ovenfor for å kombinere alle tabellene i arbeidsboken, vil du sannsynligvis få et problem.
Se antall rader med de kombinerte dataene - 1304 (som er riktig).
Hvis jeg oppdaterer spørringen, endres antall rader til 2607. Oppdater på nytt, og den endres til 3910.
Her er problemet.
Hver gang du oppdaterer spørringen, legger den til alle postene i originaldataene til de kombinerte dataene.
Merk: Du vil bare møte dette problemet hvis du har brukt Power Query til å kombinere ALLE de utmerkede bordene i arbeidsboken. Hvis du valgte spesifikke tabeller som skal kombineres, står du ikke overfor dette problemet.La oss forstå årsaken til dette problemet og hvordan vi kan korrigere dette.
Når du oppdaterer en forespørsel, går den tilbake og følger alle trinnene vi tok for å kombinere dataene.
I trinnet der vi brukte formelen = Excel.CurrentWorkbook (), det ga oss en liste over alle bordene. Dette fungerte bra første gangen, da det bare var fire bord.
Men når du oppdaterer, er det fem tabeller i arbeidsboken - inkludert den nye tabellen som Power Query satte inn der vi har de kombinerte dataene.
Så hver gang du oppdaterer spørringen, bortsett fra de fire Excel -tabellene vi vil kombinere, legger den også til den eksisterende spørringstabellen til de resulterende dataene.
Dette kalles rekursjon.
Slik løser du dette problemet.
Når du setter inn = Excel.CurrentWorkbook () i Power Query -formellinjen og trykker enter, får du en liste over Excel -tabeller. For å være sikker på at du bare får kombinere tabellene fra regnearket, må du på en eller annen måte bare filtrere disse tabellene du vil kombinere og fjerne alt annet.
Her er trinnene for å sikre at du bare har de nødvendige tabellene:
- Klikk på rullegardinmenyen og hold markøren på tekstfiltre.
- Klikk på Inneholder alternativet.
- I dialogboksen Filterrader skriver du inn _Data i feltet ved siden av "inneholder" -alternativet.
- Klikk OK.
Det kan hende du ikke ser noen endring i dataene, men dette vil forhindre at den resulterende tabellen legges til igjen når spørringen oppdateres.
Vær oppmerksom på at vi i trinnene ovenfor har brukt “_Data”For å filtrere mens vi navngav tabeller på den måten. Men hva om bordene dine ikke er navngitt konsekvent. Hva om alle tabellnavnene er tilfeldige og ikke har noe til felles.
Her er måten å løse dette på - bruk filteret "ikke lik" og skriv inn navnet på spørringen (som ville være ConsolidatedData i vårt eksempel). Dette vil sikre at alt forblir det samme, og den resulterende spørretabellen som opprettes filtreres bort.
Bortsett fra det faktum at Power Query gjør hele denne prosessen med å kombinere data fra forskjellige ark (eller til og med det samme arket) ganske enkel, er en annen fordel ved å bruke den at den gjør den dynamisk. Hvis du legger til flere poster i noen av tabellene og oppdaterer Power Query, vil den automatisk gi deg de kombinerte dataene.Viktig merknad: I eksemplet som ble brukt i denne opplæringen, var overskriftene de samme. Hvis overskriftene er forskjellige, kombinerer og oppretter Power Query alle kolonnene i den nye tabellen. Hvis dataene er tilgjengelige for den kolonnen, vil de bli vist, ellers vil de vise null.
Du kan også like følgende opplæringsprogrammer for Power Query:
- Kombiner data fra flere arbeidsbøker i Excel (ved hjelp av Power Query).
- 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)
- Slå sammen tabeller i Excel ved hjelp av Power Query.
- Hvordan sammenligne to Excel -ark/filer
