Werrej
Formulazzjoni tal-problema
Bħala dejta tal-input, għandna fajl Excel, fejn waħda mill-folji fiha diversi tabelli b'dejta tal-bejgħ tal-forma li ġejja:
Innota li:
- Tabelli ta 'daqsijiet differenti u b'settijiet differenti ta' prodotti u reġjuni f'ringieli u kolonni mingħajr ebda għażla.
- Linji vojta jistgħu jiddaħħlu bejn it-tabelli.
- In-numru ta 'tabelli jista' jkun kwalunkwe.
Żewġ suppożizzjonijiet importanti. Huwa preżunt li:
- Fuq kull tabella, fl-ewwel kolonna, hemm l-isem tal-maniġer li l-bejgħ tiegħu turi t-tabella (Ivanov, Petrov, Sidorov, eċċ.)
- L-ismijiet tal-merkanzija u r-reġjuni fit-tabelli kollha huma miktuba bl-istess mod - bi preċiżjoni tal-każ.
L-għan aħħari huwa li tinġabar dejta mit-tabelli kollha f'tabella waħda normalizzata ċatta, konvenjenti għall-analiżi sussegwenti u l-bini ta 'sommarju, jiġifieri f'dan wieħed:
Pass 1. Qabbad mal-fajl
Ejja noħolqu fajl Excel vojt ġdid u agħżel it-tab Data Kmand Ikseb id-Dejta – Mill-Fajl – Mill-Ktieb (Data — Mill-fajl — Mill-ktieb tax-xogħol). Speċifika l-post tal-fajl tas-sors bid-dejta tal-bejgħ u mbagħad fit-tieqa tan-navigatur agħżel il-folja li neħtieġu u kklikkja fuq il-buttuna Ikkonverti Data (Trasforma Data):
Bħala riżultat, id-dejta kollha minnha għandha titgħabba fl-editur tal-Power Query:
Pass 2. Naddaf it-Trash
Ħassar passi ġġenerati awtomatikament tip modifikat (Tip Mibdul) и Ras elevati (Intestaturi Promossi) u jeħles minn linji vojta u linji b'totali bl-użu ta 'filtru null и TOTAL mill-ewwel kolonna. Bħala riżultat, aħna jkollna l-istampa li ġejja:
Pass 3. Żieda ta 'maniġers
Sabiex nifhmu aktar tard fejn hu l-bejgħ ta 'min, huwa meħtieġ li tiżdied kolonna mat-tabella tagħna, fejn f'kull ringiela se jkun hemm kunjom korrispondenti. Għal din:
1. Ejja nżidu kolonna awżiljarja b'numri tal-linja bl-użu tal-kmand Żid Kolonna - Kolonna tal-Indiċi - Minn 0 (Żid il-kolonna — Kolonna tal-indiċi — Minn 0).
2. Żid kolonna b'formula bil-kmand Żieda ta 'Kolonna - Kolonna Custom (Żid kolonna — Kolonna personalizzata) u daħħal il-kostruzzjoni li ġejja hemmhekk:
Il-loġika ta 'din il-formula hija sempliċi - jekk il-valur taċ-ċellula li jmiss fl-ewwel kolonna huwa "Prodott", allura dan ifisser li tfixkilna mal-bidu ta' tabella ġdida, għalhekk aħna nuru l-valur taċ-ċellula ta 'qabel bil- isem il-maniġer. Inkella, ma nuru xejn, jiġifieri null.
Biex tikseb iċ-ċellula ġenitur bl-aħħar isem, l-ewwel nirreferu għat-tabella mill-pass preċedenti #"Indiċi miżjud", u mbagħad speċifika l-isem tal-kolonna li għandna bżonn [Kolonna 1] fil-parentesi kwadri u n-numru taċ-ċellula f’dik il-kolonna f’parentesi kaboċċi. In-numru taċ-ċellula se jkun wieħed inqas minn dak attwali, li nieħdu mill-kolonna indiċi, rispettivament.
3. Jibqa' biex timla ċ-ċelloli vojta bihom null ismijiet minn ċelloli ogħla bil-kmand Ittrasforma - Imla - 'l isfel (Trasforma — Imla — 'l isfel) u ħassar il-kolonna li m'għadhiex meħtieġa b'indiċi u ringieli bl-ismijiet tal-kunjom fl-ewwel kolonna. Bħala riżultat, irridu:
Pass 4. Raggruppament f'tabelli separati mill-maniġers
Il-pass li jmiss huwa li tiġbor ir-ringieli għal kull maniġer f'tabelli separati. Biex tagħmel dan, fuq it-tab tat-Trasformazzjoni, uża l-Grupp bil-kmand (Transform – Group By) u fit-tieqa li tiftaħ, agħżel il-kolonna tal-Maniġer u l-operazzjoni Ir-ringieli kollha (Ringieli kollha) biex sempliċement tiġbor id-dejta mingħajr ma tapplika l-ebda funzjoni ta’ aggregazzjoni għal minnhom (somma, medja, eċċ.). P.):
Bħala riżultat, aħna jkollna tabelli separati għal kull maniġer:
Pass 5: Ittrasforma Tabelli nested
Issa nagħtu t-tabelli li jinsabu f'kull ċellola tal-kolonna li tirriżulta Id-dejta kollha f'forma deċenti.
L-ewwel, ħassar kolonna li m'għadhiex meħtieġa f'kull tabella Manager. Aħna nużaw mill-ġdid Kolonna tad-dwana tab Trasformazzjoni (Trasforma — Kolonna personalizzata) u l-formula li ġejja:
Imbagħad, b'kolonna kkalkulata oħra, ngħollu l-ewwel ringiela f'kull tabella għall-intestaturi:
U fl-aħħarnett, aħna nwettqu t-trasformazzjoni prinċipali - li tiżvolġi kull tabella billi tuża l-funzjoni M Tabella.UnpivotOtherColumns:
L-ismijiet tar-reġjuni mill-header se jidħlu f'kolonna ġdida u se jkollna tabella normalizzata idjaq, iżda fl-istess ħin, itwal. Ċelloli vojta bil null huma injorati.
Neħles minn kolonni intermedji mhux meħtieġa, għandna:
Pass 6 Jespandu Tabelli nested
Jibqa' biex tespandi t-tabelli ibdiet normalizzati kollha f'lista waħda billi tuża l-buttuna bi vleġeġ doppji fl-intestatura tal-kolonna:
… u fl-aħħar niksbu dak li ridna:
Tista 'tesporta t-tabella li tirriżulta lura lejn Excel billi tuża l-kmand Id-dar — Agħlaq u Tagħbija — Agħlaq u Tagħbija fi... (Dar — Agħlaq&Tagħbija — Agħlaq&Tagħbija għal...).
- Ibni tabelli b'intestaturi differenti minn kotba multipli
- Il-ġbir tad-dejta mill-fajls kollha f'folder partikolari
- Il-ġbir tad-dejta mill-folji kollha tal-ktieb f'tabella waħda