Saturs
Problēmas formulēšana
Kā ievaddati mums ir Excel fails, kurā vienā no lapām ir vairākas tabulas ar šādas formas pārdošanas datiem:
Pieraksti to:
- Tabulas dažādu izmēru un ar dažādiem produktu komplektiem un reģioniem rindās un kolonnās bez šķirošanas.
- Starp tabulām var ievietot tukšas līnijas.
- Tabulu skaits var būt jebkurš.
Divi svarīgi pieņēmumi. Tiek pieņemts, ka:
- Virs katras tabulas pirmajā kolonnā ir tā menedžera vārds, kura pārdošanas apjomi tabulā parādīti (Ivanovs, Petrovs, Sidorovs utt.)
- Preču un reģionu nosaukumi visās tabulās ir rakstīti vienādi – ar gadījuma precizitāti.
Galīgais mērķis ir apkopot datus no visām tabulām vienā plakanā normalizētā tabulā, kas ir ērta turpmākai analīzei un kopsavilkuma veidošanai, ti, šajā tabulā:
1. darbība. Izveidojiet savienojumu ar failu
Izveidosim jaunu tukšu Excel failu un atlasiet to cilnē Datums Komanda Iegūt datus – no faila – no grāmatas (Dati — no faila — no darbgrāmatas). Norādiet avota faila atrašanās vietu ar pārdošanas datiem un pēc tam navigatora logā atlasiet mums nepieciešamo lapu un noklikšķiniet uz pogas Konvertēt datus (Pārveidot datus):
Rezultātā visi dati no tā jāielādē Power Query redaktorā:
2. darbība. Iztīriet atkritumus
Dzēst automātiski ģenerētās darbības modificēts tips (Mainīts veids) и Paaugstinātas galvenes (Reklamētās galvenes) un atbrīvojieties no tukšām rindām un rindām ar summām, izmantojot filtru nulle и KOPĀ ar pirmo kolonnu. Rezultātā mēs iegūstam šādu attēlu:
3. darbība. Pārvaldnieku pievienošana
Lai vēlāk saprastu, kur ir noiets, mūsu tabulai ir jāpievieno kolonna, kur katrā rindā būs atbilstošs uzvārds. Priekš šī:
1. Izmantojot komandu, pievienosim papildu kolonnu ar rindu numuriem Pievienot kolonnu — rādītāja kolonna — no 0 (Pievienot kolonnu — rādītāja kolonnu — no 0).
2. Pievienojiet kolonnu ar formulu ar komandu Kolonnas pievienošana — pielāgota kolonna (Pievienot kolonnu — pielāgota kolonna) un ieviest tur šādu konstrukciju:
Šīs formulas loģika ir vienkārša – ja nākamās šūnas vērtība pirmajā kolonnā ir “Produkts”, tad tas nozīmē, ka esam nejauši atraduši jaunas tabulas sākumu, tāpēc iepriekšējās šūnas vērtību attēlojam ar vadītāja vārds. Pretējā gadījumā mēs neko neparādīsim, ti, nulli.
Lai iegūtu vecākšūnu ar uzvārdu, vispirms atsaucamies uz tabulu no iepriekšējās darbības #"Indekss pievienots"un pēc tam norādiet mums vajadzīgās kolonnas nosaukumu [Sleja 1] kvadrātiekavās un šūnas numurs šajā kolonnā cirtainajās iekavās. Šūnas numurs būs par vienu mazāks nekā pašreizējais, ko mēs ņemam no kolonnas Indekss, Attiecīgi.
3. Atliek aizpildīt tukšās šūnas ar nulle nosaukumus no augstākām šūnām ar komandu Pārveidot – aizpildīt – uz leju (Pārveidot — Aizpildīt — uz leju) un izdzēsiet vairs nevajadzīgo kolonnu ar indeksiem un rindām ar uzvārdiem pirmajā kolonnā. Rezultātā mēs iegūstam:
4. solis. Grupēšana atsevišķās tabulās pēc vadītājiem
Nākamais solis ir grupēt katra pārvaldnieka rindas atsevišķās tabulās. Lai to izdarītu, cilnē Transformācija izmantojiet komandu Grupēt pēc (Transform – Group By) un atvērtajā logā atlasiet kolonnu Pārvaldnieks un operāciju Visas rindas (Visas rindas), lai vienkārši apkopotu datus, neizmantojot apkopošanas funkciju. tos (summa, vidējais utt.). P.):
Rezultātā mēs iegūstam atsevišķas tabulas katram pārvaldniekam:
5. darbība. Pārveidojiet ligzdotās tabulas
Tagad mēs sniedzam tabulas, kas atrodas katrā iegūtās kolonnas šūnā Visi dati pieklājīgā formā.
Vispirms katrā tabulā izdzēsiet kolonnu, kas vairs nav vajadzīga Vadītājs. Mēs izmantojam vēlreiz Pielāgota kolonna tab transformācija (Transformēt — pielāgota kolonna) un šādu formulu:
Pēc tam ar citu aprēķināto kolonnu mēs paceļam katras tabulas pirmo rindu uz virsrakstiem:
Un visbeidzot veicam galveno transformāciju – katra galda atlocīšanu, izmantojot M-funkciju Table.UnpivotOtherColumns:
Reģionu nosaukumi no galvenes nonāks jaunā kolonnā, un mēs iegūsim šaurāku, bet tajā pašā laikā garāku normalizētu tabulu. Tukšas šūnas ar nulle tiek ignorēti.
Atbrīvojoties no nevajadzīgām starpkolonnām, mums ir:
6. darbība. Izvērsiet ligzdotās tabulas
Atliek izvērst visas normalizētās ligzdotās tabulas vienā sarakstā, izmantojot pogu ar dubultajām bultiņām kolonnas galvenē:
... un beidzot mēs iegūstam to, ko vēlējāmies:
Varat eksportēt iegūto tabulu atpakaļ programmā Excel, izmantojot komandu Sākums — Aizvērt un ielādēt — Aizvērt un ielādēt… (Sākums — Aizvērt&Ielādēt — Aizvērt&Ielādēt...).
- Veidojiet tabulas ar dažādām galvenēm no vairākām grāmatām
- Datu vākšana no visiem failiem dotajā mapē
- Datu apkopošana no visām grāmatas lapām vienā tabulā