Innehåll
Formulering av problemet
Som indata har vi en Excel-fil, där ett av arken innehåller flera tabeller med försäljningsdata i följande form:
Anteckna det:
- Tabeller i olika storlekar och med olika uppsättningar av produkter och regioner i rader och kolumner utan någon sortering.
- Tomma rader kan infogas mellan tabeller.
- Antalet bord kan vara vilket som helst.
Två viktiga antaganden. Det antas att:
- Ovanför varje tabell, i den första kolumnen, finns namnet på chefen vars försäljning tabellen illustrerar (Ivanov, Petrov, Sidorov, etc.)
- Namnen på varor och regioner i alla tabeller är skrivna på samma sätt – med en case-noggrannhet.
Det slutliga målet är att samla in data från alla tabeller till en platt normaliserad tabell, bekvämt för efterföljande analys och att bygga en sammanfattning, dvs i denna:
Steg 1. Anslut till filen
Låt oss skapa en ny tom Excel-fil och markera den på fliken Data Kommando Hämta data – från fil – från bok (Data — Från fil — Från arbetsbok). Ange platsen för källfilen med försäljningsdata och välj sedan det ark vi behöver i navigatorfönstret och klicka på knappen Konvertera data (Omvandla data):
Som ett resultat bör all data från den laddas in i Power Query-redigeraren:
Steg 2. Städa upp soporna
Ta bort automatiskt genererade steg modifierad typ (Ändrad typ) и Förhöjda rubriker (Marknadsförda rubriker) och bli av med tomma rader och rader med totaler med hjälp av ett filter null и Totalt vid den första kolumnen. Som ett resultat får vi följande bild:
Steg 3. Lägga till chefer
För att senare förstå var vems försäljning är, är det nödvändigt att lägga till en kolumn i vår tabell, där det i varje rad kommer att finnas ett motsvarande efternamn. För detta:
1. Låt oss lägga till en extra kolumn med radnummer med hjälp av kommandot Lägg till kolumn – Indexkolumn – Från 0 (Lägg till kolumn — Indexkolumn — Från 0).
2. Lägg till en kolumn med en formel med kommandot Lägga till en kolumn – Anpassad kolumn (Lägg till kolumn – Anpassad kolumn) och introducera följande konstruktion där:
Logiken i den här formeln är enkel – om värdet för nästa cell i den första kolumnen är "Produkt", betyder det att vi har snubblat över början av en ny tabell, så vi visar värdet för den föregående cellen med chefens namn. Annars visar vi ingenting, dvs null.
För att få fram den överordnade cellen med efternamnet hänvisar vi först till tabellen från föregående steg #”Index lagt till”, och ange sedan namnet på kolumnen vi behöver [Kolumn1] inom hakparenteser och cellnumret i den kolumnen inom parentes. Cellnumret kommer att vara en mindre än det nuvarande, vilket vi tar från kolumnen index, Respektive.
3. Det återstår att fylla i de tomma cellerna med null namn från högre celler med kommandot Transformera – Fyll – Ner (Transformera — Fyll — Ner) och ta bort kolumnen som inte längre behövs med index och rader med efternamn i den första kolumnen. Som ett resultat får vi:
Steg 4. Gruppering i separata tabeller efter chefer
Nästa steg är att gruppera raderna för varje chef i separata tabeller. För att göra detta, på fliken Transformation, använd kommandot Gruppera efter (Transformera – Gruppera efter) och i fönstret som öppnas väljer du kolumnen Manager och operationen Alla rader (Alla rader) för att helt enkelt samla in data utan att använda någon aggregeringsfunktion på dem (summa, genomsnitt, etc.). P.):
Som ett resultat får vi separata tabeller för varje chef:
Steg 5: Förvandla kapslade tabeller
Nu ger vi tabellerna som ligger i varje cell i den resulterande kolumnen Alla data i hyfsad form.
Ta först bort en kolumn som inte längre behövs i varje tabell chef. Vi använder igen Anpassad kolumn fliken Transformation (Transform – Anpassad kolumn) och följande formel:
Sedan, med en annan beräknad kolumn, höjer vi den första raden i varje tabell till rubrikerna:
Och slutligen utför vi huvudtransformationen – vi vecklar ut varje bord med hjälp av M-funktionen Tabell. Ta bort andra kolumner:
Namnen på regionerna från rubriken kommer in i en ny kolumn och vi får en smalare, men samtidigt en längre normaliserad tabell. Tomma celler med null ignoreras.
För att bli av med onödiga mellankolumner har vi:
Steg 6 Expandera kapslade tabeller
Det återstår att expandera alla normaliserade kapslade tabeller till en enda lista med knappen med dubbla pilar i kolumnrubriken:
... och vi får äntligen vad vi ville ha:
Du kan exportera den resulterande tabellen tillbaka till Excel med kommandot Hem — Stäng och ladda — Stäng och ladda in... (Hem — Stäng&Ladda — Stäng&Ladda till...).
- Bygg tabeller med olika rubriker från flera böcker
- Samla in data från alla filer i en given mapp
- Samla data från alla ark i boken i en tabell