Indhold
Formulering af problemet
Som inputdata har vi en Excel-fil, hvor et af arkene indeholder flere tabeller med salgsdata i følgende form:
Noter det:
- Tabeller i forskellige størrelser og med forskellige sæt af produkter og regioner i rækker og kolonner uden nogen sortering.
- Der kan indsættes tomme linjer mellem tabeller.
- Antallet af borde kan være et hvilket som helst.
To vigtige antagelser. Det antages, at:
- Over hver tabel, i den første kolonne, er der navnet på den leder, hvis salg tabellen illustrerer (Ivanov, Petrov, Sidorov osv.)
- Navnene på varer og regioner i alle tabeller er skrevet på samme måde - med en sagsnøjagtighed.
Det ultimative mål er at indsamle data fra alle tabeller til en flad normaliseret tabel, praktisk til efterfølgende analyse og opbygning af et resumé, dvs. i denne:
Trin 1. Opret forbindelse til filen
Lad os oprette en ny tom Excel-fil og vælge den på fanen data Kommando Hent data – fra fil – fra bog (Data — Fra fil — Fra projektmappe). Angiv placeringen af kildefilen med salgsdata, og vælg derefter det ark, vi skal bruge i navigatorvinduet, og klik på knappen Konverter data (Transformer data):
Som et resultat heraf skal alle data fra den indlæses i Power Query-editoren:
Trin 2. Ryd op i skraldespanden
Slet automatisk genererede trin ændret type (Ændret type) и Forhøjede overskrifter (Promoverede overskrifter) og slippe af med tomme linjer og linjer med totaler ved hjælp af et filter null и I ALT ved den første kolonne. Som et resultat får vi følgende billede:
Trin 3. Tilføjelse af ledere
For senere at forstå, hvor hvis salg er, er det nødvendigt at tilføje en kolonne til vores tabel, hvor der i hver række vil være et tilsvarende efternavn. For det:
1. Lad os tilføje en hjælpekolonne med linjenumre ved hjælp af kommandoen Tilføj kolonne – Indekskolonne – Fra 0 (Tilføj kolonne — Indekskolonne — Fra 0).
2. Tilføj en kolonne med en formel med kommandoen Tilføjelse af en kolonne – Brugerdefineret kolonne (Tilføj kolonne – tilpasset kolonne) og introducer følgende konstruktion der:
Logikken i denne formel er enkel - hvis værdien af den næste celle i den første kolonne er "Produkt", betyder det, at vi er stødt på begyndelsen af en ny tabel, så vi viser værdien af den forrige celle med lederens navn. Ellers viser vi ikke noget, dvs. null.
For at få den overordnede celle med efternavnet henviser vi først til tabellen fra det forrige trin #"Indeks tilføjet", og angiv derefter navnet på den kolonne, vi skal bruge [Kolonne1] i firkantede parenteser og cellenummeret i den kolonne i krøllede parenteser. Cellenummeret vil være en mindre end det nuværende, som vi tager fra kolonnen Indeks, henholdsvis.
3. Det er tilbage at udfylde de tomme celler med null navne fra højere celler med kommandoen Transformer – Fyld – Ned (Transformer — Fyld — Ned) og slet den ikke længere nødvendige kolonne med indekser og rækker med efternavne i den første kolonne. Som et resultat får vi:
Trin 4. Gruppering i separate tabeller efter ledere
Det næste trin er at gruppere rækkerne for hver leder i separate tabeller. For at gøre dette skal du på fanen Transformation bruge kommandoen Grupper efter (Transformer – Grupper efter) og i vinduet, der åbnes, skal du vælge kolonnen Manager og operationen Alle rækker (Alle rækker) for blot at indsamle data uden at anvende nogen aggregeringsfunktion på dem (sum, gennemsnit osv.). P.):
Som et resultat får vi separate tabeller for hver leder:
Trin 5: Transformer indlejrede tabeller
Nu giver vi tabellerne, der ligger i hver celle i den resulterende kolonne Alle data i anstændig form.
Først skal du slette en kolonne, der ikke længere er nødvendig i hver tabel Manager. Vi bruger igen Brugerdefineret kolonne fanen Transformation (Transformer – Brugerdefineret kolonne) og følgende formel:
Derefter hæver vi med en anden beregnet kolonne den første række i hver tabel til overskrifterne:
Og til sidst udfører vi hovedtransformationen – udfoldning af hvert bord ved hjælp af M-funktionen Tabel.UnpivotAndrekolonner:
Navnene på regionerne fra overskriften vil gå ind i en ny kolonne, og vi får en smallere, men samtidig en længere normaliseret tabel. Tomme celler med null ignoreres.
For at slippe af med unødvendige mellemsøjler har vi:
Trin 6 Udvid indlejrede tabeller
Det er tilbage at udvide alle normaliserede indlejrede tabeller til en enkelt liste ved hjælp af knappen med dobbeltpile i kolonneoverskriften:
… og vi får endelig, hvad vi ønskede:
Du kan eksportere den resulterende tabel tilbage til Excel ved hjælp af kommandoen Hjem — Luk og indlæs — Luk og indlæs... (Hjem — Luk&indlæs — Luk&indlæs til...).
- Byg tabeller med forskellige overskrifter fra flere bøger
- Indsamling af data fra alle filer i en given mappe
- Indsamling af data fra alle ark i bogen i én tabel