Bygg tabeller med olika rubriker från flera böcker

Formulering av problemet

Vi har flera filer (i vårt exempel - 4 stycken, i det allmänna fallet - så många du vill) i en mapp Rapport:

Inuti ser dessa filer ut så här:

Vart i:

• Databladet vi behöver kallas alltid Bilder, men kan finnas var som helst i arbetsboken.
  
• Bortom lakanet Bilder Varje bok kan ha andra blad.
• Tabeller med data har ett annat antal rader och kan börja med en annan rad på kalkylbladet.
• Namnen på samma kolumner i olika tabeller kan skilja sig åt (t.ex. Kvantitet = Kvantitet = Antal).
• Kolumner i tabeller kan ordnas i en annan ordning.

Uppgift: samla in försäljningsdata från alla filer från arket Bilder i en gemensam tabell för att sedan bygga en sammanfattning eller annan analys på den.

Steg 1. Förbereda en katalog med kolumnnamn

Det första du ska göra är att förbereda en uppslagsbok med alla möjliga alternativ för kolumnnamn och deras korrekta tolkning:

Vi konverterar den här listan till en dynamisk "smart" tabell med knappen Formatera som tabell på fliken Hem (Hem — Formatera som tabell) eller kortkommandon ctrl+T och ladda den i Power Query med kommandot Data – Från tabell/intervall (Data – från tabell/intervall). I de senaste versionerna av Excel har den bytt namn till Med löv (Från ark).

I fönstret Power Query-frågeredigerare tar vi traditionellt bort steget Ändrad typ och lägg till ett nytt steg istället för det genom att klicka på knappen f_xi formelfältet (om det inte är synligt kan du aktivera det på fliken översyn) och ange formeln där i det inbyggda Power Query-språket M:

=Table.ToRows(Source)

Detta kommando kommer att konvertera den som laddades i föregående steg Källa referenstabell till en lista som består av kapslade listor (List), som var och en i sin tur är ett par värden Det var-blev från en rad:

Vi kommer att behöva den här typen av data lite senare, när vi massa byter namn på rubriker från alla laddade tabeller.

När konverteringen är klar, välj kommandona Hem — Stäng och ladda — Stäng och ladda in... och typ av import Skapa bara en anslutning (Hem — Stäng&Ladda — Stäng&Ladda till... — Skapa endast anslutning) och gå tillbaka till Excel.

Steg 2. Vi laddar allt från alla filer som de är

Låt oss nu ladda innehållet i alla våra filer från mappen – för nu, som det är. Att välja lag Data – Hämta data – Från fil – Från mapp (Data — Hämta data — Från fil — Från mapp) och sedan mappen där våra källböcker finns.

Klicka på i förhandsgranskningsfönstret Konvertera (Omvandla) or byta (Redigera):

Och expandera sedan innehållet i alla nedladdade filer (Binär) knapp med dubbla pilar i kolumnrubriken Innehåll:

Power Query på exemplet med den första filen (Vostok.xlsx) kommer att fråga oss namnet på arket vi vill ta från varje arbetsbok – välj Bilder och tryck OK:

Efter det (faktiskt) kommer flera händelser som inte är uppenbara för användaren att inträffa, vars konsekvenser är tydligt synliga i den vänstra panelen:

1. Power Query tar den första filen från mappen (vi kommer att ha den Vostok.xlsx — se Filexempel) som ett exempel och importerar dess innehåll genom att skapa en fråga Konvertera exempelfil. Den här frågan kommer att ha några enkla steg som Källa (filåtkomst) Navigering (arkval) och eventuellt höja titlarna. Denna begäran kan bara ladda data från en specifik fil Vostok.xlsx.
   
2. Baserat på denna begäran kommer funktionen som är kopplad till den att skapas Konvertera fil (anges med en karakteristisk ikon f_x), där källfilen inte längre kommer att vara en konstant, utan ett variabelvärde – en parameter. Således kan den här funktionen extrahera data från vilken bok som helst som vi glider in i den som ett argument.
   
3. Funktionen kommer att tillämpas i tur och ordning på varje fil (binär) från kolumnen Innehåll – steg ansvarar för detta Ring anpassad funktion i vår fråga som lägger till en kolumn i listan med filer Konvertera fil med importresultat från varje arbetsbok:

   

4. Extra kolumner tas bort.
5. Innehållet i kapslade tabeller utökas (steg Utökad tabellkolumn) – och vi ser de slutliga resultaten av datainsamlingen från alla böcker:

   
   

Steg 3. Slipning

Den tidigare skärmdumpen visar tydligt att den direkta monteringen "som den är" visade sig vara av dålig kvalitet:

• Kolumnerna är omvända.
• Många extra rader (tomma och inte bara).
• Tabellrubriker uppfattas inte som rubriker och blandas med data.
  

Du kan åtgärda alla dessa problem mycket enkelt - justera bara frågan Konvertera exempelfil. Alla justeringar som vi gör av den kommer automatiskt att falla in i den tillhörande funktionen Konvertera fil, vilket innebär att de kommer att användas senare vid import av data från varje fil.

Genom att öppna en förfrågan Konvertera exempelfil, lägg till steg för att filtrera onödiga rader (till exempel efter kolumn Column2) och höjer rubrikerna med knappen Använd första raden som rubriker (Använd första raden som rubriker). Bordet kommer att se mycket bättre ut.

För att kolumner från olika filer automatiskt ska få plats under varandra senare måste de ha samma namn. Du kan utföra ett sådant massbyte enligt en tidigare skapad katalog med en rad M-kod. Låt oss trycka på knappen igen f_x i formelfältet och lägg till en funktion för att ändra:

= Table.RenameColumns(#”Elevated Headers”, Headers, MissingField.Ignore)

Denna funktion tar tabellen från föregående steg Förhöjda rubriker och byter namn på alla kolumner i den enligt den kapslade uppslagslistan Rubriker. Tredje argumentet MissingField.Ignorera behövs så att det inte uppstår ett fel på de rubriker som finns i katalogen men inte i tabellen.

Det är faktiskt allt.

Återgår till begäran Rapport vi kommer att se en helt annan bild – mycket snyggare än den föregående:

• Vad är Power Query, Power Pivot, Power BI och varför en Excel-användare behöver dem
  
• Samla in data från alla filer i en given mapp
• Samla data från alla ark i boken i en tabell