Sette sammen tabeller fra forskjellige Excel-filer med Power Query

Formulering av problemet

La oss se på en vakker løsning for en av de helt standardsituasjonene som de fleste Excel-brukere møter før eller siden: du må raskt og automatisk samle inn data fra et stort antall filer til en slutttabell. 

Anta at vi har følgende mappe, som inneholder flere filer med data fra filialbyer:

Antall filer spiller ingen rolle og kan endres i fremtiden. Hver fil har et ark som heter Salghvor datatabellen er plassert:

Antall rader (ordre) i tabellene er selvfølgelig forskjellig, men settet med kolonner er standard overalt.

Oppgave: å samle data fra alle filer i én bok med påfølgende automatisk oppdatering når du legger til eller sletter byfiler eller rader i tabeller. I følge den endelige konsoliderte tabellen vil det da være mulig å bygge opp eventuelle rapporter, pivottabeller, filter-sorteringsdata osv. Hovedsaken er å kunne samle inn.

Vi velger våpen

For løsningen trenger vi den nyeste versjonen av Excel 2016 (den nødvendige funksjonaliteten er allerede innebygd i den som standard) eller tidligere versjoner av Excel 2010-2013 med det gratis tillegget installert Strømforespørsel fra Microsoft (last den ned her). Power Query er et superfleksibelt og superkraftig verktøy for å laste data inn i Excel fra omverdenen, for deretter å fjerne og behandle dem. Power Query støtter nesten alle eksisterende datakilder – fra tekstfiler til SQL og til og med Facebook 🙂

Hvis du ikke har Excel 2013 eller 2016, så kan du ikke lese videre (bare tuller). I eldre versjoner av Excel kan en slik oppgave bare utføres ved å programmere en makro i Visual Basic (som er svært vanskelig for nybegynnere) eller ved monoton manuell kopiering (som tar lang tid og genererer feil).

Trinn 1. Importer én fil som et eksempel

Først, la oss importere data fra en arbeidsbok som et eksempel, slik at Excel "plukker opp ideen". For å gjøre dette, lag en ny tom arbeidsbok og...

• Hvis du har Excel 2016, åpner du fanen Data og deretter Opprett spørring – Fra fil – Fra bok (Data – Ny spørring – Fra fil – Fra Excel)
• hvis du har Excel 2010-2013 med Power Query-tillegget installert, åpner du fanen Strømforespørsel og velg på den Fra fil – Fra bok (Fra fil - Fra Excel)

Deretter, i vinduet som åpnes, går du til mappen vår med rapporter og velger en av byfilene (det spiller ingen rolle hvilken, fordi de alle er typiske). Etter et par sekunder skal Navigator-vinduet vises, der du må velge arket vi trenger (Salg) på venstre side, og innholdet vil vises på høyre side:

Hvis du klikker på knappen i nedre høyre hjørne av dette vinduet Last ned (Laste), vil tabellen umiddelbart importeres til arket i sin opprinnelige form. For en enkelt fil er dette bra, men vi må laste inn mange slike filer, så vi vil gå litt annerledes og klikke på knappen Korreksjon (Redigere). Etter det skal Power Query-spørringseditoren vises i et eget vindu med våre data fra boken:

Dette er et veldig kraftig verktøy som lar deg "fullføre" bordet til den visningen vi trenger. Selv en overfladisk beskrivelse av alle funksjonene vil ta omtrent hundre sider, men hvis du bruker dette vinduet veldig kort, kan du:

• filtrere ut unødvendige data, tomme linjer, linjer med feil
• sortere data etter én eller flere kolonner
• bli kvitt repetisjon
• del klebrig tekst etter kolonner (med skilletegn, antall tegn osv.)
• sette tekst i rekkefølge (fjern ekstra mellomrom, rett inn store og små bokstaver osv.)
• konverter datatyper på alle mulige måter (gjør tall som tekst til normale tall og omvendt)
• transponere (rotere) tabeller og utvide todimensjonale krysstabeller til flate
• legg til flere kolonner i tabellen og bruk formler og funksjoner i dem ved å bruke M-språket innebygd i Power Query.
• ...

La oss for eksempel legge til en kolonne med månedens tekstnavn i tabellen vår, slik at det senere blir enklere å bygge pivottabellrapporter. For å gjøre dette, høyreklikk på kolonneoverskriften dataog velg kommandoen Dupliser kolonne (Duplikatkolonne), og høyreklikk deretter på overskriften til duplikatkolonnen som vises, og velg Kommandoer Transform – Måned – Månedsnavn:

En ny kolonne skal dannes med månedens tekstnavn for hver rad. Ved å dobbeltklikke på en kolonneoverskrift kan du endre navn på den fra Kopier dato til en mer komfortabel Måned, f.eks.

Hvis programmet i noen kolonner ikke helt korrekt gjenkjente datatypen, kan du hjelpe det ved å klikke på formatikonet på venstre side av hver kolonne:

Du kan ekskludere linjer med feil eller tomme linjer, samt unødvendige ledere eller kunder, ved å bruke et enkelt filter:

Dessuten er alle utførte transformasjoner festet i det høyre panelet, hvor de alltid kan rulles tilbake (kryss) eller endre parametrene deres (gir):

Lett og elegant, ikke sant?

Trinn 2. La oss forvandle forespørselen vår til en funksjon

For å gjenta alle datatransformasjonene som er gjort for hver importert bok, må vi konvertere den opprettede forespørselen vår til en funksjon, som deretter vil bli brukt på alle filene våre. Å gjøre dette er faktisk veldig enkelt.

I Query Editor, gå til View-fanen og klikk på knappen Avansert redaktør (Vis — Avansert redaktør). Et vindu skal åpnes der alle våre tidligere handlinger vil bli skrevet i form av kode på M-språket. Vær oppmerksom på at banen til filen som vi importerte for eksempelet er hardkodet i koden:

La oss nå gjøre et par justeringer:

Betydningen deres er enkel: den første linjen (filbane)=> gjør prosedyren vår til en funksjon med et argument filesti, og nedenfor endrer vi den faste banen til verdien av denne variabelen. 

Alle. Klikk på Finish og burde se dette:

Ikke vær redd for at dataene har forsvunnet – faktisk er alt i orden, alt skal se slik ut 🙂 Vi har med suksess laget vår egendefinerte funksjon, der hele algoritmen for import og behandling av data huskes uten å være knyttet til en bestemt fil . Det gjenstår å gi det et mer forståelig navn (for eksempel getData) i panelet til høyre i feltet Fornavn og du kan høste Hjem — Lukk og last ned (Hjem – Lukk og last). Vær oppmerksom på at banen til filen som vi importerte for eksempelet er hardkodet i koden. Du kommer tilbake til hovedvinduet i Microsoft Excel, men et panel med den opprettede forbindelsen til funksjonen vår skal vises til høyre:

Trinn 3. Samle alle filene

Alt det vanskeligste ligger bak, det hyggelige og enkle gjenstår. Gå til fanen Data – Lag spørring – Fra fil – Fra mappe (Data — Ny spørring — Fra fil — Fra mappe) eller, hvis du har Excel 2010-2013, på samme måte som fanen Strømforespørsel. I vinduet som vises, spesifiser mappen der alle kildebyfilene våre er plassert og klikk OK. Det neste trinnet skal åpne et vindu der alle Excel-filene som finnes i denne mappen (og dens undermapper) og detaljer for hver av dem vil bli oppført:

Klikk Endring (Redigere) og igjen kommer vi inn i det velkjente søkeredigeringsvinduet.

Nå må vi legge til en annen kolonne i tabellen vår med vår opprettede funksjon, som vil "trekke" dataene fra hver fil. For å gjøre dette, gå til fanen Legg til kolonne – egendefinert kolonne (Legg til kolonne – Legg til egendefinert kolonne) og skriv inn funksjonen vår i vinduet som vises getData, og spesifiserer for det som et argument den fullstendige banen til hver fil:

Etter å ha klikket på OK den opprettede kolonnen skal legges til i tabellen vår til høyre.

La oss nå slette alle unødvendige kolonner (som i Excel, ved å bruke høyre museknapp – fjerne), og la bare den lagt til kolonnen og kolonnen med filnavnet, fordi dette navnet (mer presist byen) vil være nyttig å ha i de totale dataene for hver rad.

Og nå "wow-øyeblikket" - klikk på ikonet med sine egne piler i øvre høyre hjørne av den tilføyde kolonnen med vår funksjon:

… fjern merket Bruk originalt kolonnenavn som prefiks (Bruk originalt kolonnenavn som prefiks)og klikk OK. Og funksjonen vår vil laste og behandle dataene fra hver fil, følge den registrerte algoritmen og samle alt i en felles tabell:

For fullstendig skjønnhet kan du også fjerne .xlsx-utvidelsene fra den første kolonnen med filnavn – ved standard erstatning med “ingenting” (høyreklikk på kolonneoverskriften – Erstatning) og gi nytt navn til denne kolonnen til City. Og korriger også dataformatet i kolonnen med datoen.

Alle! Klikk på Hjem – Lukk og last (Hjem — Lukk og last). Alle data som samles inn av spørringen for alle byer vil bli lastet opp til det gjeldende Excel-arket i "smart tabell"-formatet:

Den opprettede tilkoblingen og vår monteringsfunksjon trenger ikke på noen måte å lagres separat – de lagres sammen med gjeldende fil på vanlig måte.

I fremtiden, med eventuelle endringer i mappen (legge til eller fjerne byer) eller i filer (endre antall linjer), vil det være nok å høyreklikke direkte på tabellen eller på spørringen i høyre panel og velge kommando Oppdater og lagre (Forfriske) – Power Query vil "gjenoppbygge" alle dataene igjen i løpet av noen få sekunder.

PS

Endring. Etter januar 2017-oppdateringene lærte Power Query hvordan man samler Excel-arbeidsbøker av seg selv, det vil si at det ikke lenger er nødvendig å lage en egen funksjon – det skjer automatisk. Dermed er det andre trinnet fra denne artikkelen ikke lenger nødvendig, og hele prosessen blir merkbart enklere:

1. Velg Opprett forespørsel – Fra fil – Fra mappe – Velg mappe – OK
2. Etter at listen over filer vises, trykk Endring
3. I Query Editor-vinduet utvider du Binær-kolonnen med en dobbel pil og velger arknavnet som skal hentes fra hver fil

Og det er alt! Sang!

• Redesign av tverrtappen til en flat en som er egnet for å bygge pivotbord
• Bygge et animert boblediagram i Power View
• Makro for å sette sammen ark fra forskjellige Excel-filer til ett