Nilalaman
Pagbubuo ng problema
Tingnan natin ang isang magandang solusyon para sa isa sa mga pinakakaraniwang sitwasyon na kinakaharap ng karamihan sa mga user ng Excel nang maaga o huli: kailangan mong mabilis at awtomatikong mangolekta ng data mula sa isang malaking bilang ng mga file sa isang huling talahanayan.
Ipagpalagay na mayroon kaming sumusunod na folder, na naglalaman ng ilang mga file na may data mula sa mga lungsod ng sangay:
Ang bilang ng mga file ay hindi mahalaga at maaaring magbago sa hinaharap. Ang bawat file ay may pinangalanang sheet Bintahankung saan matatagpuan ang talahanayan ng data:
Ang bilang ng mga hilera (mga order) sa mga talahanayan, siyempre, ay iba, ngunit ang hanay ng mga hanay ay pamantayan sa lahat ng dako.
Gawain: upang mangolekta ng data mula sa lahat ng mga file sa isang aklat na may kasunod na awtomatikong pag-update kapag nagdadagdag o nagtatanggal ng mga file ng lungsod o mga hilera sa mga talahanayan. Ayon sa panghuling pinagsama-samang talahanayan, posible na bumuo ng anumang mga ulat, pivot table, filter-sort data, atbp. Ang pangunahing bagay ay ang mangolekta.
Pumili kami ng mga armas
Para sa solusyon, kailangan namin ang pinakabagong bersyon ng Excel 2016 (ang kinakailangang pag-andar ay nakapaloob na dito bilang default) o mga nakaraang bersyon ng Excel 2010-2013 na may naka-install na libreng add-in. kapangyarihan query mula sa Microsoft (i-download ito dito). Ang Power Query ay isang napaka-flexible at napakalakas na tool para sa pag-load ng data sa Excel mula sa labas ng mundo, pagkatapos ay pag-alis at pagproseso nito. Sinusuportahan ng Power Query ang halos lahat ng umiiral na data source – mula sa mga text file hanggang sa SQL at maging sa Facebook 🙂
Kung wala kang Excel 2013 o 2016, hindi ka na makakapagbasa pa (biro lang). Sa mga mas lumang bersyon ng Excel, ang ganitong gawain ay magagawa lamang sa pamamagitan ng pagprograma ng macro sa Visual Basic (na napakahirap para sa mga nagsisimula) o sa pamamagitan ng monotonous na manu-manong pagkopya (na tumatagal ng mahabang panahon at bumubuo ng mga error).
Hakbang 1. Mag-import ng isang file bilang sample
Una, mag-import tayo ng data mula sa isang workbook bilang isang halimbawa, upang "kunin ng Excel ang ideya". Upang gawin ito, gumawa ng bagong blangko na workbook at…
- kung mayroon kang Excel 2016, pagkatapos ay buksan ang tab data at pagkatapos ay Lumikha ng Query – Mula sa File – Mula sa Aklat (Data — Bagong Query- Mula sa file — Mula sa Excel)
- kung mayroon kang Excel 2010-2013 na may naka-install na Power Query add-in, pagkatapos ay buksan ang tab kapangyarihan query at piliin ito Mula sa file – Mula sa aklat (Mula sa file — Mula sa Excel)
Pagkatapos, sa window na bubukas, pumunta sa aming folder na may mga ulat at piliin ang alinman sa mga file ng lungsod (hindi mahalaga kung alin, dahil lahat sila ay tipikal). Pagkatapos ng ilang segundo, dapat lumitaw ang window ng Navigator, kung saan kailangan mong piliin ang sheet na kailangan namin (Sales) sa kaliwang bahagi, at ang mga nilalaman nito ay ipapakita sa kanang bahagi:
Kung nag-click ka sa pindutan sa kanang sulok sa ibaba ng window na ito Download (Load), pagkatapos ay mai-import kaagad ang talahanayan sa sheet sa orihinal nitong anyo. Para sa isang solong file, ito ay mabuti, ngunit kailangan nating mag-load ng maraming ganoong mga file, kaya medyo naiiba tayo at i-click ang pindutan Pagwawasto (I-edit). Pagkatapos nito, dapat ipakita ang editor ng query ng Power Query sa isang hiwalay na window kasama ang aming data mula sa aklat:
Ito ay isang napakalakas na tool na nagbibigay-daan sa iyong "tapusin" ang talahanayan sa view na kailangan namin. Kahit na ang isang mababaw na paglalarawan ng lahat ng mga pag-andar nito ay aabutin ng humigit-kumulang isang daang pahina, ngunit, kung napakaikli, gamit ang window na ito maaari mong:
- i-filter ang hindi kinakailangang data, mga walang laman na linya, mga linya na may mga error
- pagbukud-bukurin ang data ayon sa isa o higit pang mga column
- alisin ang pag-uulit
- hatiin ang malagkit na teksto sa pamamagitan ng mga column (sa pamamagitan ng mga delimiter, bilang ng mga character, atbp.)
- ayusin ang teksto (alisin ang mga karagdagang puwang, tamang case, atbp.)
- i-convert ang mga uri ng data sa lahat ng posibleng paraan (gawing normal na mga numero ang mga numero tulad ng text at vice versa)
- i-transpose (iikot) ang mga talahanayan at palawakin ang dalawang-dimensional na cross-table sa mga flat
- magdagdag ng mga karagdagang column sa talahanayan at gumamit ng mga formula at function sa mga ito gamit ang M language na nakapaloob sa Power Query.
- ...
Halimbawa, magdagdag tayo ng column na may pangalan ng text ng buwan sa aming talahanayan, upang sa ibang pagkakataon ay mas madaling bumuo ng mga ulat ng pivot table. Upang gawin ito, mag-right-click sa heading ng column petsaat piliin ang utos Dobleng column (Dobleng Column), at pagkatapos ay i-right-click ang header ng duplicate na column na lalabas at piliin ang Mga Command Transform – Buwan – Pangalan ng Buwan:
Dapat bumuo ng bagong column na may mga pangalan ng text ng buwan para sa bawat row. Sa pamamagitan ng pag-double click sa heading ng column, maaari mong palitan ang pangalan nito mula sa Petsa ng Kopya sa mas komportable buwan, hal.
Kung sa ilang mga column ay hindi masyadong nakilala ng program ang uri ng data, maaari mo itong tulungan sa pamamagitan ng pag-click sa icon ng format sa kaliwang bahagi ng bawat column:
Maaari mong ibukod ang mga linyang may mga error o walang laman na linya, pati na rin ang mga hindi kinakailangang manager o customer, gamit ang isang simpleng filter:
Bukod dito, ang lahat ng ginawang pagbabago ay naayos sa kanang panel, kung saan maaari silang palaging i-roll pabalik (krus) o baguhin ang kanilang mga parameter (gear):
Magaan at matikas, hindi ba?
Hakbang 2. Ibahin natin ang ating kahilingan sa isang function
Upang pagkatapos ay ulitin ang lahat ng mga pagbabagong data na ginawa para sa bawat na-import na aklat, kailangan naming i-convert ang aming nilikhang kahilingan sa isang function, na pagkatapos ay ilalapat, sa turn, sa lahat ng aming mga file. Upang gawin ito ay talagang napaka-simple.
Sa Query Editor, pumunta sa tab na View at i-click ang button Advanced na Editor (Tingnan — Advanced na Editor). Dapat bumukas ang isang window kung saan isusulat ang lahat ng ating nakaraang aksyon sa anyo ng code sa wikang M. Pakitandaan na ang path sa file na na-import namin para sa halimbawa ay naka-hardcode sa code:
Ngayon gumawa tayo ng ilang pagsasaayos:
Ang kanilang kahulugan ay simple: ang unang linya (filepath) => ginagawang function na may argumento ang aming procedure filepath, at sa ibaba ay binabago namin ang nakapirming landas sa halaga ng variable na ito.
Lahat. Mag-click sa Tapusin at dapat itong makita:
Huwag matakot na ang data ay nawala – sa katunayan, ang lahat ay OK, ang lahat ay dapat magmukhang ganito 🙂 Matagumpay naming nalikha ang aming pasadyang pag-andar, kung saan ang buong algorithm para sa pag-import at pagproseso ng data ay naaalala nang hindi nakatali sa isang partikular na file . Ito ay nananatiling bigyan ito ng isang mas maliwanag na pangalan (halimbawa getData) sa panel sa kanan sa field Pangalan at maaari kang umani Home — Isara at i-download (Home — Isara at I-load). Pakitandaan na ang path sa file na na-import namin para sa halimbawa ay naka-hardcode sa code. Babalik ka sa pangunahing window ng Microsoft Excel, ngunit dapat lumitaw sa kanan ang isang panel na may nilikhang koneksyon sa aming function:
Hakbang 3. Pagkolekta ng lahat ng mga file
Ang lahat ng pinakamahirap na bahagi ay nasa likod, ang kaaya-aya at madaling bahagi ay nananatili. Pumunta sa tab Data – Lumikha ng Query – Mula sa File – Mula sa Folder (Data — Bagong Query — Mula sa file — Mula sa folder) o, kung mayroon kang Excel 2010-2013, katulad ng tab kapangyarihan query. Sa lalabas na window, tukuyin ang folder kung saan matatagpuan ang lahat ng aming source city file at i-click OK. Ang susunod na hakbang ay dapat magbukas ng isang window kung saan ang lahat ng mga Excel file na makikita sa folder na ito (at ang mga subfolder nito) at mga detalye para sa bawat isa sa kanila ay nakalista:
I-click ang Baguhin (I-edit) at muli nakapasok kami sa pamilyar na window ng editor ng query.
Ngayon ay kailangan naming magdagdag ng isa pang haligi sa aming talahanayan gamit ang aming nilikha na function, na "pull" ang data mula sa bawat file. Upang gawin ito, pumunta sa tab Magdagdag ng Column – Custom Column (Magdagdag ng Column — Magdagdag ng Custom Column) at sa lalabas na window, ipasok ang aming function getData, na tumutukoy para dito bilang isang argumento ang buong landas sa bawat file:
Pagkatapos ng pag-click sa OK ang ginawang column ay dapat idagdag sa aming table sa kanan.
Ngayon, tanggalin natin ang lahat ng hindi kinakailangang column (tulad ng sa Excel, gamit ang kanang pindutan ng mouse – Alisin), iiwan lamang ang idinagdag na column at ang column na may pangalan ng file, dahil ang pangalang ito (mas tiyak, ang lungsod) ay magiging kapaki-pakinabang na magkaroon sa kabuuang data para sa bawat row.
At ngayon ang "wow sandali" - mag-click sa icon na may sarili nitong mga arrow sa kanang itaas na sulok ng idinagdag na hanay kasama ang aming function:
… alisan ng check Gamitin ang orihinal na pangalan ng column bilang prefix (Gamitin ang orihinal na pangalan ng column bilang prefix)at i-click ang OK. At ang aming function ay maglo-load at magpoproseso ng data mula sa bawat file, kasunod ng naitala na algorithm at pagkolekta ng lahat sa isang karaniwang talahanayan:
Para sa kumpletong kagandahan, maaari mo ring alisin ang mga .xlsx na extension mula sa unang column na may mga pangalan ng file – sa pamamagitan ng karaniwang pagpapalit ng “wala” (i-right click sa header ng column – Kapalit) at palitan ang pangalan ng column na ito sa lungsod. At itama din ang format ng data sa column na may petsa.
Lahat! Mag-click sa Tahanan – Isara at I-load (Tahan — Isara at I-load). Ang lahat ng data na nakolekta ng query para sa lahat ng mga lungsod ay ia-upload sa kasalukuyang Excel sheet sa "smart table" na format:
Ang nilikha na koneksyon at ang aming function ng pagpupulong ay hindi kailangang i-save nang hiwalay sa anumang paraan - nai-save ang mga ito kasama ng kasalukuyang file sa karaniwang paraan.
Sa hinaharap, sa anumang mga pagbabago sa folder (pagdaragdag o pag-alis ng mga lungsod) o sa mga file (pagbabago ng bilang ng mga linya), sapat na ang direktang pag-right click sa talahanayan o sa query sa kanang panel at piliin ang utos I-update at I-save (Refresh) – Ang Power Query ay "muling bubuuin" ang lahat ng data sa loob ng ilang segundo.
PS
Susog. Pagkatapos ng mga update sa Enero 2017, natutunan ng Power Query kung paano kolektahin ang mga workbook ng Excel nang mag-isa, ibig sabihin, hindi na kailangang gumawa ng hiwalay na function - awtomatiko itong nangyayari. Kaya, ang pangalawang hakbang mula sa artikulong ito ay hindi na kailangan at ang buong proseso ay nagiging mas simple:
- Piliin Lumikha ng Kahilingan - Mula sa File - Mula sa Folder - Piliin ang Folder - OK
- Pagkatapos lumitaw ang listahan ng mga file, pindutin ang Baguhin
- Sa window ng Query Editor, palawakin ang Binary column na may double arrow at piliin ang pangalan ng sheet na kukunin mula sa bawat file
At yun lang! Kanta!
- Muling idisenyo ang crosstab sa isang patag na angkop para sa pagbuo ng mga pivot table
- Bumuo ng animated na bubble chart sa Power View
- Macro para mag-assemble ng mga sheet mula sa iba't ibang Excel file sa isa