Indhold
Formulering af problemet
Lad os se på en smuk løsning til en af de meget standardsituationer, som de fleste Excel-brugere står over for før eller siden: Du skal hurtigt og automatisk indsamle data fra et stort antal filer til én finaletabel.
Antag, at vi har følgende mappe, som indeholder flere filer med data fra filialbyer:
Antallet af filer er ligegyldigt og kan ændre sig i fremtiden. Hver fil har et ark med navnet Salghvor datatabellen er placeret:
Antallet af rækker (ordrer) i tabellerne er selvfølgelig forskelligt, men kolonnesættet er standard overalt.
Opgave: at samle data fra alle filer i én bog med efterfølgende automatisk opdatering ved tilføjelse eller sletning af byfiler eller rækker i tabeller. Ifølge den endelige konsoliderede tabel, så vil det være muligt at bygge eventuelle rapporter, pivottabeller, filter-sorteringsdata osv. Det vigtigste er at kunne indsamle.
Vi vælger våben
Til løsningen har vi brug for den nyeste version af Excel 2016 (den nødvendige funktionalitet er allerede indbygget som standard) eller tidligere versioner af Excel 2010-2013 med det gratis tilføjelsesprogram installeret Strømforespørgsel fra Microsoft (download den her). Power Query er et super fleksibelt og superkraftigt værktøj til at indlæse data i Excel fra omverdenen og derefter strippe og behandle dem. Power Query understøtter næsten alle eksisterende datakilder – fra tekstfiler til SQL og endda Facebook 🙂
Hvis du ikke har Excel 2013 eller 2016, så kan du ikke læse videre (bare for sjov). I ældre versioner af Excel kan en sådan opgave kun udføres ved at programmere en makro i Visual Basic (hvilket er meget svært for begyndere) eller ved monoton manuel kopiering (som tager lang tid og genererer fejl).
Trin 1. Importer en fil som en prøve
Lad os først importere data fra én projektmappe som et eksempel, så Excel "optager ideen". For at gøre dette skal du oprette en ny tom projektmappe og...
- hvis du har Excel 2016, skal du åbne fanen data og så Opret forespørgsel – Fra fil – Fra bog (Data - Ny forespørgsel - Fra fil - Fra Excel)
- hvis du har Excel 2010-2013 med tilføjelsesprogrammet Power Query installeret, skal du åbne fanen Strømforespørgsel og vælg den Fra fil – Fra bog (Fra fil - Fra Excel)
Gå derefter til vores mappe med rapporter i vinduet, der åbnes, og vælg en af byfilerne (det er ligegyldigt hvilken, for de er alle typiske). Efter et par sekunder skulle Navigator-vinduet vises, hvor du skal vælge det ark, vi har brug for (Salg) i venstre side, og dets indhold vil blive vist i højre side:
Hvis du klikker på knappen i nederste højre hjørne af dette vindue Hent (Belastning), så importeres tabellen øjeblikkeligt til arket i sin oprindelige form. For en enkelt fil er dette godt, men vi skal indlæse mange sådanne filer, så vi vil gå lidt anderledes og klikke på knappen Korrektion (Edit). Derefter skal Power Query-forespørgselseditoren vises i et separat vindue med vores data fra bogen:
Dette er et meget kraftfuldt værktøj, der giver dig mulighed for at "færdiggøre" bordet til den visning, vi har brug for. Selv en overfladisk beskrivelse af alle dens funktioner ville tage omkring hundrede sider, men hvis det er meget kort, kan du bruge dette vindue:
- bortfiltrere unødvendige data, tomme linjer, linjer med fejl
- sortere data efter en eller flere kolonner
- slippe af med gentagelser
- opdel klæbrig tekst efter kolonner (ved skilletegn, antal tegn osv.)
- sæt tekst i rækkefølge (fjern ekstra mellemrum, ret store og små bogstaver osv.)
- konverter datatyper på alle mulige måder (vend tal som tekst til normale tal og omvendt)
- transponere (rotere) tabeller og udvide todimensionelle krydstabeller til flade
- føj yderligere kolonner til tabellen og brug formler og funktioner i dem ved hjælp af M-sproget indbygget i Power Query.
- ...
Lad os f.eks. tilføje en kolonne med månedens tekstnavn til vores tabel, så det senere ville være lettere at bygge pivottabelrapporter. For at gøre dette skal du højreklikke på kolonneoverskriften datoog vælg kommandoen Kopier kolonne (Dublet kolonne), og højreklik derefter på overskriften på den dubletkolonne, der vises, og vælg Kommandoer Transform – Måned – Måneds navn:
Der skal dannes en ny kolonne med månedens tekstnavne for hver række. Ved at dobbeltklikke på en kolonneoverskrift kan du omdøbe den fra Kopiér dato til en mere komfortabel Måned, for eksempel.
Hvis programmet i nogle kolonner ikke helt korrekt genkendte datatypen, så kan du hjælpe det ved at klikke på formatikonet i venstre side af hver kolonne:
Du kan ekskludere linjer med fejl eller tomme linjer såvel som unødvendige ledere eller kunder ved hjælp af et simpelt filter:
Desuden er alle udførte transformationer fastgjort i det højre panel, hvor de altid kan rulles tilbage (kryds) eller ændre deres parametre (gear):
Let og elegant, ikke?
Trin 2. Lad os omdanne vores anmodning til en funktion
For efterfølgende at gentage alle de datatransformationer, der er foretaget for hver importeret bog, skal vi konvertere vores oprettede anmodning til en funktion, som derefter vil blive anvendt på alle vores filer. At gøre dette er faktisk meget simpelt.
I forespørgselseditoren skal du gå til fanen Vis og klikke på knappen Avanceret editor (Vis — Avanceret editor). Et vindue skulle åbne, hvor alle vores tidligere handlinger vil blive skrevet i form af kode på M-sproget. Bemærk venligst, at stien til filen, som vi importerede til eksemplet, er hårdkodet i koden:
Lad os nu foretage et par justeringer:
Deres betydning er enkel: den første linje (filsti)=> gør vores procedure til en funktion med et argument filsti, og nedenfor ændrer vi den faste sti til værdien af denne variabel.
Alle. Klik på Finish og skal se dette:
Vær ikke bange for, at data er forsvundet – faktisk er alt i orden, alt skulle se sådan ud 🙂 Vi har med succes skabt vores brugerdefinerede funktion, hvor hele algoritmen til import og behandling af data huskes uden at være bundet til en bestemt fil . Det er tilbage at give det et mere forståeligt navn (f.eks getData) i panelet til højre i feltet Fornavn og du kan høste Hjem — Luk og download (Hjem - Luk og indlæs). Bemærk venligst, at stien til filen, som vi importerede til eksemplet, er hårdkodet i koden. Du vender tilbage til hovedvinduet i Microsoft Excel, men et panel med den oprettede forbindelse til vores funktion skulle vises til højre:
Trin 3. Indsamling af alle filerne
Alt det sværeste er bagud, det behagelige og lette forbliver. Gå til fanen Data – Opret forespørgsel – Fra fil – Fra mappe (Data — Ny forespørgsel — Fra fil — Fra mappe) eller, hvis du har Excel 2010-2013, på samme måde som fanen Strømforespørgsel. I det vindue, der vises, skal du angive den mappe, hvor alle vores kildebyfiler er placeret, og klikke OK. Det næste trin skulle åbne et vindue, hvor alle Excel-filer, der findes i denne mappe (og dens undermapper) og detaljer for hver af dem vil blive vist:
Klik Skift (Edit) og igen kommer vi ind i det velkendte forespørgselsredigeringsvindue.
Nu skal vi tilføje endnu en kolonne til vores tabel med vores oprettede funktion, som vil "trække" dataene fra hver fil. For at gøre dette skal du gå til fanen Tilføj kolonne – Brugerdefineret kolonne (Tilføj kolonne — Tilføj brugerdefineret kolonne) og indtast vores funktion i det vindue, der vises getData, som angiver den fulde sti til hver fil som et argument:
Efter at have klikket på OK den oprettede kolonne skal tilføjes til vores tabel til højre.
Lad os nu slette alle unødvendige kolonner (som i Excel, ved hjælp af højre museknap – Fjern), og efterlader kun den tilføjede kolonne og kolonnen med filnavnet, fordi dette navn (mere præcist byen) vil være nyttigt at have i de samlede data for hver række.
Og nu "wow-øjeblikket" - klik på ikonet med sine egne pile i øverste højre hjørne af den tilføjede kolonne med vores funktion:
… fjern markeringen Brug det originale kolonnenavn som præfiks (Brug originalt kolonnenavn som præfiks)og klik OK. Og vores funktion vil indlæse og behandle dataene fra hver fil, følge den registrerede algoritme og samle alt i en fælles tabel:
For fuldstændig skønhed kan du også fjerne .xlsx-udvidelserne fra den første kolonne med filnavne - ved standardudskiftning med "ingenting" (højreklik på kolonneoverskriften - Erstatning) og omdøb denne kolonne til By. Og ret også dataformatet i kolonnen med datoen.
Alle! Klik på Hjem – Luk og indlæs (Hjem — Luk og indlæs). Alle data indsamlet af forespørgslen for alle byer vil blive uploadet til det aktuelle Excel-ark i formatet "smart table":
Den oprettede forbindelse og vores montagefunktion behøver ikke på nogen måde at blive gemt separat – de gemmes sammen med den aktuelle fil på sædvanlig vis.
I fremtiden, med eventuelle ændringer i mappen (tilføje eller fjerne byer) eller i filer (ændring af antallet af linjer), vil det være nok at højreklikke direkte på tabellen eller på forespørgslen i højre panel og vælge kommando Opdater & Gem (Opdater) – Power Query vil "genopbygge" alle data igen på få sekunder.
PS
Ændring. Efter opdateringerne fra januar 2017 lærte Power Query, hvordan man indsamler Excel-projektmapper af sig selv, dvs. ingen grund til at lave en separat funktion længere – det sker automatisk. Således er det andet trin fra denne artikel ikke længere nødvendigt, og hele processen bliver mærkbart enklere:
- Vælg Opret anmodning – Fra fil – Fra mappe – Vælg mappe – OK
- Når listen over filer vises, skal du trykke på Skift
- I vinduet Query Editor skal du udvide kolonnen Binær med en dobbeltpil og vælge det arknavn, der skal tages fra hver fil
Og det er alt! Sang!
- Redesign af krydstavlen til en flad, velegnet til at bygge drejeborde
- Opbygning af et animeret boblediagram i Power View
- Makro til at samle ark fra forskellige Excel-filer til én