Contents [show]
Formulering van die probleem
As invoerdata het ons 'n Excel-lêer, waar een van die velle verskeie tabelle bevat met verkoopsdata van die volgende vorm:
Let daarop dat:
- Tabelle van verskillende groottes en met verskillende stelle produkte en streke in rye en kolomme sonder enige sortering.
- Leë lyne kan tussen tabelle ingevoeg word.
- Die aantal tafels kan enige wees.
Twee belangrike aannames. Daar word aanvaar dat:
- Bo elke tabel, in die eerste kolom, is daar die naam van die bestuurder wie se verkope die tabel illustreer (Ivanov, Petrov, Sidorov, ens.)
- Die name van goedere en streke in alle tabelle word op dieselfde manier geskryf – met 'n kassie akkuraatheid.
Die uiteindelike doel is om data van alle tabelle in een plat genormaliseerde tabel in te samel, gerieflik vir daaropvolgende ontleding en die bou van 'n opsomming, dws in hierdie een:
Stap 1. Koppel aan die lêer
Kom ons skep 'n nuwe leë Excel-lêer en kies dit op die oortjie data Command Kry data – van lêer – uit boek (Data — Van lêer — Uit werkboek). Spesifiseer die ligging van die bronlêer met verkoopsdata en kies dan in die navigatorvenster die blad wat ons benodig en klik op die knoppie Skakel data om (Transformeer data):
Gevolglik moet alle data daarvan in die Power Query-redigeerder gelaai word:
Stap 2. Maak die asblik skoon
Vee outomaties gegenereerde stappe uit gewysigde tipe (Veranderde tipe) и Verhoogde kopskrifte (Bevorderde opskrifte) en ontslae te raak van leë lyne en lyne met totale met behulp van 'n filter nul и TOTAAL deur die eerste kolom. As gevolg hiervan kry ons die volgende prentjie:
Stap 3. Voeg bestuurders by
Om later te verstaan waar wie se verkope is, is dit nodig om 'n kolom by ons tabel te voeg, waar daar in elke ry 'n ooreenstemmende van sal wees. Vir dit:
1. Kom ons voeg 'n hulpkolom met reëlnommers by deur die opdrag te gebruik Voeg kolom by – Indekskolom – Vanaf 0 (Voeg kolom by — Indekskolom — Vanaf 0).
2. Voeg 'n kolom met 'n formule by met die opdrag Voeg 'n kolom by - Pasgemaakte kolom (Voeg kolom by – Pasgemaakte kolom) en stel die volgende konstruksie daar bekend:
Die logika van hierdie formule is eenvoudig – as die waarde van die volgende sel in die eerste kolom “Produk” is, beteken dit dat ons op die begin van 'n nuwe tabel afgekom het, dus vertoon ons die waarde van die vorige sel met die naam van die bestuurder. Andersins wys ons niks nie, dws nul.
Om die ouersel met die van te kry, verwys ons eers na die tabel van die vorige stap #"Indeks bygevoeg", en spesifiseer dan die naam van die kolom wat ons benodig [Kolom1] tussen vierkantige hakies en die selnommer in daardie kolom tussen krullerige hakies. Die selnommer sal een minder wees as die huidige een, wat ons uit die kolom neem indeks, Onderskeidelik.
3. Dit bly om die leë selle mee in te vul nul name van hoër selle met die opdrag Transformeer – Vul – Af (Transformeer — Vul — Af) en verwyder die nie meer benodigde kolom met indekse en rye met vanne in die eerste kolom. As gevolg hiervan kry ons:
Stap 4. Groepering in aparte tabelle deur bestuurders
Die volgende stap is om die rye vir elke bestuurder in aparte tabelle te groepeer. Om dit te doen, op die Transformasie-oortjie, gebruik die Groepeer volgens opdrag (Transformeer – Groepeer volgens) en in die venster wat oopmaak, kies die Bestuurderkolom en die bewerking Alle rye (Alle rye) om bloot data te versamel sonder om enige samevoegingsfunksie toe te pas op hulle (som, gemiddeld, ens.). P.):
As gevolg hiervan kry ons aparte tabelle vir elke bestuurder:
Stap 5: Transformeer geneste tabelle
Nou gee ons die tabelle wat in elke sel van die resulterende kolom lê Alle data in ordentlike vorm.
Vee eers 'n kolom uit wat nie meer in elke tabel benodig word nie Bestuurder. Ons gebruik weer Gepasmaakte kolom tab Transformasie (Transformeer – Pasgemaakte kolom) en die volgende formule:
Dan, met 'n ander berekende kolom, verhoog ons die eerste ry in elke tabel na die opskrifte:
En laastens voer ons die hooftransformasie uit – ontvou elke tafel met behulp van die M-funksie Tabel. OntpivotAnderkolomme:
Die name van die streke vanaf die kopskrif sal in 'n nuwe kolom gaan en ons sal 'n nouer, maar terselfdertyd 'n langer genormaliseerde tabel kry. Leë selle met nul word geïgnoreer.
Om van onnodige tussenkolomme ontslae te raak, het ons:
Stap 6 Brei geneste tabelle uit
Dit bly om alle genormaliseerde geneste tabelle in 'n enkele lys uit te brei deur die knoppie met dubbele pyle in die kolomkop te gebruik:
… en ons kry uiteindelik wat ons wou hê:
U kan die resulterende tabel terug uitvoer na Excel met die opdrag Tuis — Maak toe en laai — Maak toe en laai in... (Tuis — Maak toe en laai — Maak toe en laai na...).
- Bou tabelle met verskillende opskrifte uit verskeie boeke
- Versamel data van alle lêers in 'n gegewe vouer
- Versamel data van alle velle van die boek in een tabel