Hvordan slå sammen data fra flere Excel-arbeidsbøker. Si at du har en mappe full av Excel-filer. Hver har ett regneark med forskjellig antall rader. Du kan importere alle filene i mappen til en enkelt Excel-arbeidsbok.
Se på video
- Gjennomgå Clean Data med Power Query podcast # 2037
- Power Query kan nå kombinere alle Excel-filer i en mappe.
- Forbedret: De sletter automatisk overskriftene fra alle unntatt den første filen.
- Du velger hvilken fil du vil bruke som prøvefil.
- Velg hvilket regneark, tabell, navngitt område som skal importeres
- Bruk spørringseditoren til å gjøre noen transformasjoner
- Lukk og last for å kombinere alle filer
- Senere, oppdater spørringen for å få den oppdatert
Videoutskrift
Lær Excel fra Podcast, episode 2077: Slå sammen arbeidsbøker
Hei, velkommen tilbake til netcast, jeg er Bill Jelen. For bare 40 episoder siden snakket vi om rengjøringsdata med Power Query, hvordan du tar en hel haug med CSV-filer og tar dem med i en enkelt Excel-fil. Vel, denne funksjonen har nettopp blitt dramatisk bedre.
Så jeg har her i dag er en mappe, og i denne mappen er det en hel serie med månedlige Excel-filer, 22 av dem i nesten to år. Jeg må kombinere alle disse filene. Nå har disse alle samme kolonneoppsett, ok? Så de har alle ni kolonner, alle har de samme overskriftene og så videre, ok. Så vi skal kombinere alle disse uten makro.
Hvis du er i Excel 2016, kommer du til Data-fanen for å få og transformere data. Hvis du er i Excel 2010 eller 2013, kommer du til å laste ned et flott produkt fra Microsoft som heter Power Query. Du kan laste ned hele greia og få den installert på mindre enn ett minutt, ok.
Så vi kommer hit for å få data, fra en fil, men ikke fra en enkelt arbeidsbok. Vi ønsker å få det fra en slik mappe. Ok, her er de. De ber deg bla til mappen, men jeg skal bare klikke der og lime inn Ctrl + V fordi jeg limte inn katalogen, klikk OK. Så de viste meg en forhåndsvisning av dataene jeg har. Jeg åpner denne lille rullegardinmenyen Kombiner her og velger Kombiner & rediger. OK, og deretter er trinnet her at vi skal velge en fil som er en god representativ fil, ok, og de velger bare den første som tilfeldigvis er alfabetisk. Det spiller ingen rolle hvilken du velger. Og så denne prøvespørringen vi bygger fra den første filen vi velger, i dette tilfellet vil jeg ha Sheet1.Hvis du har flere ark, kan du si Ark2 eller arket som heter Inntektsoppgave eller Navneområdet eller en tabell. Og de viste meg hvordan det ser ut, og jeg klikker OK.
Nå skjer det mye i denne skjermen. Det de faktisk har gjort her er at de har opprettet en Sample Query, ahh Sample Query, og deretter opprettet den som en funksjon og kjørt den funksjonen mot alt i mappen. De legger til en ny kolonne som forteller meg hvor filen kom fra, og i dette spesielle tilfellet vet jeg allerede hvor den kom fra fordi vi har en dato fylt ut her. Så jeg trenger ikke det, jeg skal høyreklikke og fjerne det. Og kanskje er det noen data der inne som jeg ikke trenger. Kanskje disse dataene kommer til en kunde, så jeg vil ikke vise fortjenesten, slik at jeg kan fjerne den kolonnen og til og med fjerne kostnadskolonnen, og kanskje er det noen irriterende ting som, du vet, de brukte dårlige overskrifter og jeg ønsker å gi nytt navn til disse overskriftene. Så høyreklikk og Gi nytt navn, vi vil kalle det QTY for Quantity.Kanskje ting er i feil rekkefølge, kanskje jeg vil ta denne varen og flytte den til et annet sted, ok? Bare alle disse små irriterende tingene du kanskje har, og selvfølgelig her borte, gjør de alle de anvendte trinnene, og så kan du fortsette å gjøre alle Power Query-ting du trenger å gjøre. Men i dette tilfellet er det bare enkelt. Jeg vil bare få inn dataene. Så jeg kommer til å lukke og laste, noen sekunder går og det er det. Jeg har min finale. La oss bare sortere dette etter dato; ser du der vi går fra januar 2018 helt ut til oktober 2019. Hvor kult er det.du utfører alle trinnene som er brukt, og deretter kan du fortsette å gjøre alle Power Query-ting du trenger å gjøre. Men i dette tilfellet er det bare enkelt. Jeg vil bare få inn dataene. Så jeg kommer til å lukke og laste, noen sekunder går og det er det. Jeg har min finale. La oss bare sortere dette etter dato; ser du der vi går fra januar 2018 helt ut til oktober 2019. Hvor kult er det.du utfører alle trinnene som er brukt, og deretter kan du fortsette å gjøre alle Power Query-ting du trenger å gjøre. Men i dette tilfellet er det bare enkelt. Jeg vil bare få inn dataene. Så jeg kommer til å lukke og laste, noen sekunder går og det er det. Jeg har min finale. La oss bare sortere dette etter dato; ser du der vi går fra januar 2018 helt ut til oktober 2019. Hvor kult er det.
Her er den fantastiske, fantastiske tingen om dette. La oss si at noe endres i mappen vår. Så tilbake her i Måned-filene, jeg får to nye filer, Lim inn, og nå er det to poster her, to flere Excel-filer som jeg må kombinere. Bare kom tilbake til Excel-filen som for øyeblikket har 516 rader, finn MonthFiles og klikk på Oppdater-knappen. Og så raskt har jeg nå de nye platene fra november og desember. Power Query er et utrolig nytt verktøy gratis for Excel 2010, gratis for Excel 2013. Det var vakkert når de kunne kombinere CSV-filer, men nå som de også kan kombinere Excel-filer, bare fenomenale.
Så jeg har oppdatert boken min, Power Excel med, 2017-utgaven, inkluderer Power Query, Power BI og alle de nye verktøyene. Sjekk det ut. Klikk på “i” øverst til høyre.
Greit, oppsummering av denne episoden: Vi besøker Clean Data med Power Query-podcast nr. 2037 der vi kombinerer flere CSV-filer. Vi kan nå bruke det samme verktøyet, en forbedret versjon av det verktøyet, for å kombinere alle Excel-filer i en mappe. En annen forbedring, endringen fra 2037, sletter de automatisk overskriftene fra alle unntatt den første filen og markedsfører disse overskriftene i den første filen. Du velger hvilken fil du vil bruke som prøvefil. I mitt tilfelle brukte jeg januar. Arbeid - Velg hvilket regneark, tabell eller navngitt område som skal importeres, og gjør deretter transformasjoner i spørredigereren. Vi klikker Lukk og last, det vil kombinere alle filene. Og hvis du får flere filer droppet i mappen eller noen filer tatt bort eller noen filer endret, er det bare å oppdatere spørringen, så gjør du alle disse trinnene igjen. Power Query,et helt fantastisk verktøy.
Jeg vil takke deg for at du var innom. Vi sees neste gang for en ny netcast fra.
Last ned fil
Last ned eksempelfilen her: Podcast2077.xlsm
