Innehåll
Formulering av problemet
Låt oss titta på en vacker lösning för en av de mycket standardsituationer som de flesta Excel-användare möter förr eller senare: du måste snabbt och automatiskt samla in data från ett stort antal filer till en sista tabell.
Anta att vi har följande mapp, som innehåller flera filer med data från filialstäder:
Antalet filer spelar ingen roll och kan komma att ändras i framtiden. Varje fil har ett ark som heter Försäljningvar datatabellen finns:
Antalet rader (order) i tabellerna är naturligtvis olika, men uppsättningen kolumner är standard överallt.
Uppgift: att samla in data från alla filer till en bok med efterföljande automatisk uppdatering när man lägger till eller tar bort stadsfiler eller rader i tabeller. Enligt den slutliga konsoliderade tabellen kommer det då att vara möjligt att bygga vilka rapporter som helst, pivottabeller, filtersorteringsdata etc. Huvudsaken är att kunna samla in.
Vi väljer vapen
För lösningen behöver vi den senaste versionen av Excel 2016 (den nödvändiga funktionaliteten är redan inbyggd i den som standard) eller tidigare versioner av Excel 2010-2013 med det kostnadsfria tillägget installerat Strömfråga från Microsoft (ladda ner den här). Power Query är ett superflexibelt och superkraftigt verktyg för att ladda data till Excel från omvärlden och sedan strippa och bearbeta dem. Power Query stöder nästan alla befintliga datakällor – från textfiler till SQL och till och med Facebook 🙂
Om du inte har Excel 2013 eller 2016 så kan du inte läsa vidare (skämtar bara). I äldre versioner av Excel kan en sådan uppgift endast utföras genom att programmera ett makro i Visual Basic (vilket är mycket svårt för nybörjare) eller genom monoton manuell kopiering (som tar lång tid och genererar fel).
Steg 1. Importera en fil som ett exempel
Låt oss först importera data från en arbetsbok som ett exempel, så att Excel "får upp idén". För att göra detta, skapa en ny tom arbetsbok och...
- om du har Excel 2016 öppnar du fliken Data och då Skapa fråga – Från fil – Från bok (Data - Ny fråga - Från fil - Från Excel)
- om du har Excel 2010-2013 med Power Query-tillägget installerat, öppna sedan fliken Strömfråga och välj på den Från fil – Från bok (Från fil — Från Excel)
Gå sedan till vår mapp med rapporter i fönstret som öppnas och välj någon av stadsfilerna (det spelar ingen roll vilken, eftersom de alla är typiska). Efter ett par sekunder bör Navigator-fönstret visas, där du måste välja det ark vi behöver (försäljning) på vänster sida, och dess innehåll kommer att visas på höger sida:
Om du klickar på knappen i det nedre högra hörnet av detta fönster Download (Ladda), då kommer tabellen omedelbart att importeras till arket i sin ursprungliga form. För en enskild fil är detta bra, men vi måste ladda många sådana filer, så vi kommer att gå lite annorlunda och klicka på knappen Korrigering (Redigera). Därefter bör Power Query-frågeredigeraren visas i ett separat fönster med våra data från boken:
Detta är ett mycket kraftfullt verktyg som låter dig "avsluta" bordet till den vy vi behöver. Till och med en ytlig beskrivning av alla dess funktioner skulle ta ungefär hundra sidor, men om det är mycket kortfattat kan du använda det här fönstret:
- filtrera bort onödig data, tomma rader, rader med fel
- sortera data efter en eller flera kolumner
- bli av med upprepning
- dela klibbig text efter kolumner (med avgränsare, antal tecken, etc.)
- ordna text (ta bort extra mellanslag, rätta skiftläge osv.)
- konvertera datatyper på alla möjliga sätt (förvandla siffror som text till normala siffror och vice versa)
- transponera (rotera) tabeller och expandera tvådimensionella korstabeller till platta
- lägg till ytterligare kolumner i tabellen och använd formler och funktioner i dem med M-språket inbyggt i Power Query.
- .
Låt oss till exempel lägga till en kolumn med månadens textnamn i vår tabell, så att det senare blir lättare att bygga pivottabellsrapporter. För att göra detta, högerklicka på kolumnrubriken datumoch välj kommandot Dubblettkolumn (Duplicera kolumn), och högerklicka sedan på rubriken i den dubblettkolumn som visas och välj Kommandon Transform – Månad – Månad Namn:
En ny kolumn ska skapas med månadens textnamn för varje rad. Genom att dubbelklicka på en kolumnrubrik kan du byta namn på den från Kopiera datum till en mer bekväm Månad, t.ex.
Om programmet i vissa kolumner inte riktigt kände igen datatypen, kan du hjälpa det genom att klicka på formatikonen till vänster om varje kolumn:
Du kan utesluta rader med fel eller tomma rader, såväl som onödiga chefer eller kunder, med ett enkelt filter:
Dessutom är alla utförda transformationer fixerade i den högra panelen, där de alltid kan rullas tillbaka (kors) eller ändra deras parametrar (växel):
Lätt och elegant, eller hur?
Steg 2. Låt oss omvandla vår begäran till en funktion
För att sedan upprepa alla datatransformationer som gjorts för varje importerad bok, måste vi konvertera vår skapade begäran till en funktion, som sedan kommer att tillämpas i sin tur på alla våra filer. Att göra detta är faktiskt väldigt enkelt.
I frågeredigeraren, gå till fliken Visa och klicka på knappen Avancerad redaktör (Visa — Avancerad redigerare). Ett fönster bör öppnas där alla våra tidigare åtgärder kommer att skrivas i form av kod på M-språket. Observera att sökvägen till filen som vi importerade för exemplet är hårdkodad i koden:
Låt oss nu göra ett par justeringar:
Deras betydelse är enkel: den första raden (filsökväg)=> gör vår procedur till en funktion med ett argument sökväg, och nedan ändrar vi den fasta sökvägen till värdet för denna variabel.
Allt. Klicka på Finish och borde se detta:
Var inte rädd att data har försvunnit – faktiskt är allt OK, allt ska se ut så här 🙂 Vi har framgångsrikt skapat vår anpassade funktion, där hela algoritmen för att importera och bearbeta data kommer ihåg utan att vara knuten till en specifik fil . Det återstår att ge det ett mer förståeligt namn (till exempel hämta data) i panelen till höger i fältet Förnamn och du kan skörda Hem — Stäng och ladda ner (Hem — Stäng och ladda). Observera att sökvägen till filen som vi importerade för exemplet är hårdkodad i koden. Du kommer tillbaka till huvudfönstret i Microsoft Excel, men en panel med den skapade anslutningen till vår funktion bör visas till höger:
Steg 3. Samla alla filer
Allt det svåraste ligger bakom, det trevliga och lätta återstår. Gå till fliken Data – Skapa fråga – Från fil – Från mapp (Data — Ny fråga — Från fil — Från mapp) eller, om du har Excel 2010-2013, liknande fliken Strömfråga. I fönstret som visas, ange mappen där alla våra källstadsfiler finns och klicka OK. Nästa steg bör öppna ett fönster där alla Excel-filer som finns i den här mappen (och dess undermappar) och detaljer för var och en av dem kommer att listas:
Klicka byta (Redigera) och återigen kommer vi in i det välbekanta frågeredigeringsfönstret.
Nu måste vi lägga till ytterligare en kolumn i vår tabell med vår skapade funktion, som kommer att "dra" data från varje fil. För att göra detta, gå till fliken Lägg till kolumn – Anpassad kolumn (Lägg till kolumn – Lägg till anpassad kolumn) och skriv in vår funktion i fönstret som visas hämta data, och anger för det som ett argument den fullständiga sökvägen till varje fil:
Efter att ha klickat på OK den skapade kolumnen ska läggas till i vår tabell till höger.
Låt oss nu ta bort alla onödiga kolumner (som i Excel, med höger musknapp – AVLÄGSNA), och lämnar bara den tillagda kolumnen och kolumnen med filnamnet, eftersom detta namn (mer exakt, staden) kommer att vara användbart att ha i den totala informationen för varje rad.
Och nu "wow-ögonblicket" - klicka på ikonen med sina egna pilar i det övre högra hörnet av den tillagda kolumnen med vår funktion:
… avmarkera Använd det ursprungliga kolumnnamnet som prefix (Använd det ursprungliga kolumnnamnet som prefix)och klicka OK. Och vår funktion kommer att ladda och bearbeta data från varje fil, följa den inspelade algoritmen och samla allt i en gemensam tabell:
För fullständig skönhet kan du också ta bort .xlsx-tilläggen från den första kolumnen med filnamn – genom standardersättning med “ingenting” (högerklicka på kolumnrubriken – Ersättning) och byt namn på den här kolumnen till Stad. Och korrigera även dataformatet i kolumnen med datum.
Allt! Klicka på Hem – Stäng och ladda (Hem — Stäng & ladda). All data som samlas in av frågan för alla städer kommer att laddas upp till det aktuella Excel-arket i formatet "smart table":
Den skapade anslutningen och vår monteringsfunktion behöver inte sparas separat på något sätt – de sparas tillsammans med den aktuella filen på vanligt sätt.
I framtiden, med eventuella ändringar i mappen (lägga till eller ta bort städer) eller i filer (ändra antalet rader), kommer det att räcka att högerklicka direkt på tabellen eller på frågan i den högra panelen och välj kommando Uppdatera & spara (Uppdatera) – Power Query kommer att "bygga om" all data igen på några sekunder.
PS
Ändring. Efter uppdateringarna från januari 2017 lärde sig Power Query hur man samlar in Excel-arbetsböcker av sig själv, det vill säga inget behov av att göra en separat funktion längre – det sker automatiskt. Det andra steget från den här artikeln behövs inte längre och hela processen blir märkbart enklare:
- Välja Skapa förfrågan – Från fil – Från mapp – Välj mapp – OK
- När listan med filer visas trycker du på byta
- I Query Editor-fönstret expanderar du kolumnen Binär med en dubbelpil och väljer arknamnet som ska tas från varje fil
Och det är allt! Låt!
- Gör om korsfliken till en platt som lämpar sig för att bygga pivotbord
- Skapa ett animerat bubbeldiagram i Power View
- Makro för att sammanställa ark från olika Excel-filer till ett