Nilalaman
Pagbubuo ng problema
Bilang input data, mayroon kaming Excel file, kung saan ang isa sa mga sheet ay naglalaman ng ilang mga talahanayan na may data sa pagbebenta ng sumusunod na form:
Tandaan na:
- Mga talahanayan na may iba't ibang laki at may iba't ibang hanay ng mga produkto at rehiyon sa mga hilera at column nang walang anumang pag-uuri.
- Maaaring maglagay ng mga blangkong linya sa pagitan ng mga talahanayan.
- Ang bilang ng mga talahanayan ay maaaring alinman.
Dalawang mahalagang pagpapalagay. Ito ay ipinapalagay na:
- Sa itaas ng bawat talahanayan, sa unang hanay, mayroong pangalan ng manager na ang mga benta ay inilalarawan ng talahanayan (Ivanov, Petrov, Sidorov, atbp.)
- Ang mga pangalan ng mga kalakal at rehiyon sa lahat ng mga talahanayan ay nakasulat sa parehong paraan - na may katumpakan ng kaso.
Ang pangunahing layunin ay upang mangolekta ng data mula sa lahat ng mga talahanayan sa isang patag na normalized na talahanayan, na maginhawa para sa kasunod na pagsusuri at pagbuo ng isang buod, ibig sabihin, sa isang ito:
Hakbang 1. Kumonekta sa file
Gumawa tayo ng bagong walang laman na Excel file at piliin ito sa tab data Utos Kumuha ng Data – Mula sa File – Mula sa Aklat (Data — Mula sa file — Mula sa workbook). Tukuyin ang lokasyon ng source file na may data ng mga benta at pagkatapos ay sa window ng navigator piliin ang sheet na kailangan namin at mag-click sa pindutan I-convert ang Data (Transform Data):
Bilang resulta, ang lahat ng data mula dito ay dapat na mai-load sa editor ng Power Query:
Hakbang 2. Linisin ang basura
Tanggalin ang mga awtomatikong nabuong hakbang binagong uri (Binago ang Uri) и Mga nakataas na header (Mga Na-promote na Header) at alisin ang mga walang laman na linya at linya na may mga kabuuan gamit ang isang filter walang halaga и TOTAL sa pamamagitan ng unang hanay. Bilang resulta, nakuha namin ang sumusunod na larawan:
Hakbang 3. Pagdaragdag ng mga tagapamahala
Upang maunawaan sa ibang pagkakataon kung saan ang mga benta, kinakailangan upang magdagdag ng isang haligi sa aming talahanayan, kung saan sa bawat hilera ay magkakaroon ng kaukulang apelyido. Para dito:
1. Magdagdag tayo ng auxiliary column na may mga line number gamit ang command Magdagdag ng Column – Index Column – Mula sa 0 (Magdagdag ng column — Index column — Mula sa 0).
2. Magdagdag ng column na may formula na may command Pagdaragdag ng Column – Custom Column (Magdagdag ng column — Custom na column) at ipakilala ang sumusunod na konstruksiyon doon:
Ang lohika ng formula na ito ay simple - kung ang halaga ng susunod na cell sa unang column ay "Produkto", nangangahulugan ito na natisod tayo sa simula ng isang bagong talahanayan, kaya ipinapakita namin ang halaga ng nakaraang cell gamit ang pangalan ng manager. Kung hindi, hindi kami nagpapakita ng anuman, ibig sabihin, null.
Upang makuha ang parent cell na may apelyido, sumangguni muna kami sa talahanayan mula sa nakaraang hakbang #”Idinagdag ang index”, at pagkatapos ay tukuyin ang pangalan ng column na kailangan namin [Column1] sa mga square bracket at ang cell number sa column na iyon sa curly bracket. Ang cell number ay magiging mas mababa ng isa kaysa sa kasalukuyang isa, na kukunin namin mula sa column Index, Ayon sa pagkakabanggit.
3. Ito ay nananatiling punan ang mga walang laman na mga cell na may walang halaga mga pangalan mula sa mas mataas na mga cell na may command Ibahin ang anyo - Punan - Pababa (Transform — Punan — Pababa) at tanggalin ang hindi na kailangang column na may mga indeks at row na may mga apelyido sa unang column. Bilang resulta, nakukuha namin ang:
Hakbang 4. Pagpapangkat sa magkakahiwalay na mga talahanayan ng mga tagapamahala
Ang susunod na hakbang ay igrupo ang mga row para sa bawat manager sa magkakahiwalay na mga talahanayan. Upang gawin ito, sa tab na Transformation, gamitin ang Group by command (Transform – Group By) at sa window na bubukas, piliin ang Manager column at ang operation All row (All row) para mangolekta lang ng data nang hindi nag-aaplay ng anumang aggregating function sa kanila (sum, average, atbp.). P.):
Bilang resulta, nakakakuha kami ng hiwalay na mga talahanayan para sa bawat manager:
Hakbang 5: I-transform ang mga Nested Table
Ngayon ay binibigyan namin ang mga talahanayan na namamalagi sa bawat cell ng nagresultang haligi Lahat ng datos sa disenteng hugis.
Una, tanggalin ang isang column na hindi na kailangan sa bawat talahanayan Tagapamahala. Ginagamit namin ulit Custom na column tab Pagbabagong-anyo (Transform — Custom na column) at ang sumusunod na pormula:
Pagkatapos, sa isa pang kinakalkulang column, itinataas namin ang unang hilera sa bawat talahanayan sa mga heading:
At sa wakas, ginagawa namin ang pangunahing pagbabago - paglalahad ng bawat talahanayan gamit ang M-function Table.UnpivotOtherColumns:
Ang mga pangalan ng mga rehiyon mula sa header ay mapupunta sa isang bagong column at makakakuha tayo ng mas makitid, ngunit sa parehong oras, isang mas mahabang normalized na talahanayan. Walang laman ang mga cell na may walang halaga ay hindi pinansin.
Ang pag-alis ng mga hindi kinakailangang intermediate na column, mayroon kaming:
Hakbang 6 Palawakin ang Mga Nested Table
Nananatili itong palawakin ang lahat ng na-normalize na nested na talahanayan sa iisang listahan gamit ang button na may double arrow sa header ng column:
… at sa wakas nakuha namin ang gusto namin:
Maaari mong i-export ang resultang talahanayan pabalik sa Excel gamit ang command Tahanan — Isara at I-load — Isara at I-load sa… (Home — Isara&I-load — Isara&I-load sa…).
- Bumuo ng mga talahanayan na may iba't ibang mga header mula sa maraming aklat
- Pagkolekta ng data mula sa lahat ng mga file sa isang naibigay na folder
- Pagkolekta ng data mula sa lahat ng mga sheet ng libro sa isang talahanayan