Innhold
Formulering av problemet
Som inputdata har vi en Excel-fil, der ett av arkene inneholder flere tabeller med salgsdata i følgende form:
Noter det:
- Tabeller i forskjellige størrelser og med forskjellige sett med produkter og regioner i rader og kolonner uten sortering.
- Blanke linjer kan settes inn mellom tabeller.
- Antall bord kan være hvilket som helst.
To viktige forutsetninger. Det antas at:
- Over hver tabell, i den første kolonnen, er det navnet på lederen hvis salg tabellen illustrerer (Ivanov, Petrov, Sidorov, etc.)
- Navnene på varer og regioner i alle tabeller er skrevet på samme måte – med saksnøyaktighet.
Det endelige målet er å samle data fra alle tabeller til en flat normalisert tabell, praktisk for påfølgende analyse og å bygge et sammendrag, dvs. i denne:
Trinn 1. Koble til filen
La oss lage en ny tom Excel-fil og velge den på fanen Data Kommando Hent data – fra fil – fra bok (Data — Fra fil — Fra arbeidsbok). Angi plasseringen av kildefilen med salgsdata og velg deretter arket vi trenger i navigatorvinduet og klikk på knappen Konverter data (Transformer data):
Som et resultat bør alle data fra den lastes inn i Power Query-redigeringsprogrammet:
Trinn 2. Rydd opp i søppelet
Slett automatisk genererte trinn modifisert type (Endret type) и Forhøyede overskrifter (Forfremmede overskrifter) og bli kvitt tomme linjer og linjer med totaler ved hjelp av et filter null и TOTAL ved den første kolonnen. Som et resultat får vi følgende bilde:
Trinn 3. Legge til ledere
For senere å forstå hvor hvis salg er, er det nødvendig å legge til en kolonne i tabellen vår, hvor det i hver rad vil være et tilsvarende etternavn. For dette:
1. La oss legge til en hjelpekolonne med linjenummer ved å bruke kommandoen Legg til kolonne – Indekskolonne – Fra 0 (Legg til kolonne — Indekskolonne — Fra 0).
2. Legg til en kolonne med en formel med kommandoen Legge til en kolonne – egendefinert kolonne (Legg til kolonne – egendefinert kolonne) og introduser følgende konstruksjon der:
Logikken i denne formelen er enkel - hvis verdien til neste celle i den første kolonnen er "Produkt", betyr dette at vi har snublet over begynnelsen av en ny tabell, så vi viser verdien til forrige celle med navnet på lederen. Ellers viser vi ingenting, dvs. null.
For å få den overordnede cellen med etternavnet, viser vi først til tabellen fra forrige trinn #"Indeks lagt til", og spesifiser deretter navnet på kolonnen vi trenger [Kolonne1] i firkantede parenteser og cellenummeret i den kolonnen i krøllete parenteser. Cellenummeret vil være én mindre enn det nåværende, som vi tar fra kolonnen IndexHhv.
3. Det gjenstår å fylle ut de tomme cellene med null navn fra høyere celler med kommandoen Transform – Fyll – Ned (Transformer – Fyll – Ned) og slett kolonnen som ikke lenger er nødvendig med indekser og rader med etternavn i den første kolonnen. Som et resultat får vi:
Trinn 4. Gruppering i separate tabeller etter ledere
Det neste trinnet er å gruppere radene for hver leder i separate tabeller. For å gjøre dette, på Transformasjon-fanen, bruk Grupper etter-kommandoen (Transformer – Grupper etter) og i vinduet som åpnes, velg Manager-kolonnen og operasjonen Alle rader (Alle rader) for ganske enkelt å samle inn data uten å bruke noen aggregeringsfunksjon på dem (sum, gjennomsnitt osv.). P.):
Som et resultat får vi separate tabeller for hver leder:
Trinn 5: Transformer nestede tabeller
Nå gir vi tabellene som ligger i hver celle i den resulterende kolonnen Alle data i grei form.
Slett først en kolonne som ikke lenger er nødvendig i hver tabell Leder. Vi bruker igjen Egendefinert kolonne tab Transformation (Transform – egendefinert kolonne) og følgende formel:
Deretter, med en annen beregnet kolonne, hever vi den første raden i hver tabell til overskriftene:
Og til slutt utfører vi hovedtransformasjonen – vi folder ut hvert bord ved hjelp av M-funksjonen Tabell. Fjern pivotandre kolonner:
Navnene på regionene fra overskriften vil gå inn i en ny kolonne og vi vil få en smalere, men samtidig en lengre normalisert tabell. Tomme celler med null blir ignorert.
For å bli kvitt unødvendige mellomsøyler har vi:
Trinn 6 Utvid nestede tabeller
Det gjenstår å utvide alle normaliserte nestede tabeller til en enkelt liste ved å bruke knappen med doble piler i kolonneoverskriften:
… og vi får endelig det vi ønsket:
Du kan eksportere den resulterende tabellen tilbake til Excel ved å bruke kommandoen Hjem — Lukk og last — Lukk og last inn... (Hjem — Lukk&last — Lukk&last til...).
- Bygg tabeller med forskjellige overskrifter fra flere bøker
- Samle data fra alle filer i en gitt mappe
- Samle data fra alle arkene i boken i én tabell