Kaundan
Pagporma sa problema
Atong tan-awon ang usa ka matahum nga solusyon alang sa usa sa labing kasagaran nga mga kahimtang nga giatubang sa kadaghanan sa mga tiggamit sa Excel sa madugay o sa madali: kinahanglan nimo nga dali ug awtomatiko nga mangolekta mga datos gikan sa daghang mga file sa usa ka katapusan nga lamesa.
Ibutang ta nga kita adunay mosunod nga folder, nga adunay daghang mga file nga adunay datos gikan sa mga lungsod sa sanga:
Ang gidaghanon sa mga file dili igsapayan ug mahimong mausab sa umaabot. Ang matag file adunay ngalan nga sheet salesdiin nahimutang ang data table:
Ang gidaghanon sa mga laray (mga han-ay) sa mga lamesa, siyempre, lahi, apan ang hugpong sa mga kolum mao ang sumbanan bisan asa.
Buluhaton: aron makolekta ang datos gikan sa tanan nga mga file ngadto sa usa ka libro nga adunay sunod nga awtomatikong pag-update kung magdugang o magtangtang sa mga file sa lungsod o mga linya sa mga lamesa. Sumala sa katapusan nga consolidated lamesa, nan kini mahimong posible nga sa pagtukod sa bisan unsa nga mga taho, pivot lamesa, filter-sort data, ug uban pa Ang nag-unang butang mao ang makahimo sa pagkolekta.
Gipili namo ang mga hinagiban
Alang sa solusyon, kinahanglan namon ang pinakabag-o nga bersyon sa Excel 2016 (ang kinahanglan nga pag-andar natukod na niini pinaagi sa default) o mga naunang bersyon sa Excel 2010-2013 nga adunay libre nga add-in nga na-install. Kusog nga Pagpangutana gikan sa Microsoft (i-download kini dinhi). Ang Power Query kay usa ka super flexible ug super power nga himan para sa loading data ngadto sa Excel gikan sa gawas nga kalibutan, unya paghubo ug pagproseso niini. Gisuportahan sa Power Query ang hapit tanan nga mga gigikanan sa datos - gikan sa mga file sa teksto hangtod sa SQL ug bisan ang Facebook 🙂
Kung wala kay Excel 2013 o 2016, dili ka na makabasa og dugang (nagkadlaw lang). Sa karaan nga mga bersyon sa Excel, ang ingon nga buluhaton mahimo lamang pinaagi sa pagprograma sa usa ka macro sa Visual Basic (nga lisud kaayo alang sa mga nagsugod) o pinaagi sa monotonous nga manual nga pagkopya (nga nagkinahanglag taas nga oras ug nagpatunghag mga sayup).
Lakang 1. Pag-import og usa ka file isip sample
Una, mag-import kita og data gikan sa usa ka workbook isip usa ka pananglitan, aron ang Excel "mokuha sa ideya". Aron mahimo kini, paghimo usa ka bag-ong blangko nga workbook ug…
- kung ikaw adunay Excel 2016, dayon ablihi ang tab Data unya Paghimo Pangutana - Gikan sa File - Gikan sa Libro (Data — Bag-ong Pangutana- Gikan sa file — Gikan sa Excel)
- kung ikaw adunay Excel 2010-2013 nga adunay gi-install nga Power Query add-in, unya ablihi ang tab Kusog nga Pagpangutana ug pilia kini Gikan sa file - Gikan sa libro (Gikan sa file - Gikan sa Excel)
Pagkahuman, sa bintana nga nagbukas, adto sa among folder nga adunay mga taho ug pilia ang bisan unsang mga file sa lungsod (dili igsapayan kung hain, tungod kay silang tanan kasagaran). Pagkahuman sa pila ka segundo, kinahanglan nga makita ang bintana sa Navigator, diin kinahanglan nimo nga pilion ang sheet nga kinahanglan namon (Sales) sa wala nga bahin, ug ang mga sulud niini ipakita sa tuo nga bahin:
Kung imong i-klik ang buton sa ubos nga tuo nga suok sa kini nga bintana Download (Pagkarga), unya ang lamesa i-import dayon sa sheet sa orihinal nga porma niini. Alang sa usa ka file, maayo kini, apan kinahanglan namon nga i-load ang daghang ingon nga mga file, aron magkalainlain kami ug i-klik ang buton Pagtul-id (I-edit). Pagkahuman niana, ang editor sa pangutana sa Power Query kinahanglan nga ipakita sa usa ka bulag nga bintana sa among data gikan sa libro:
Kini usa ka gamhanan kaayo nga himan nga nagtugot kanimo sa "paghuman" sa lamesa sa panglantaw nga atong gikinahanglan. Bisan ang usa ka taphaw nga paghulagway sa tanan nga mga gimbuhaton niini mokabat sa usa ka gatos nga mga panid, apan, kung sa kadali, gamit kini nga bintana mahimo nimo:
- pagsala sa wala kinahanglana nga datos, walay sulod nga mga linya, mga linya nga adunay mga sayup
- paghan-ay sa datos pinaagi sa usa o daghang mga kolum
- kuhaa ang pagbalik-balik
- bahina ang sticky text pinaagi sa mga kolum (pinaagi sa mga delimiter, gidaghanon sa mga karakter, ug uban pa)
- ibutang ang teksto sa han-ay (kuhaa ang dugang nga mga espasyo, husto nga kaso, ug uban pa)
- i-convert ang mga tipo sa datos sa tanang posible nga paagi (ibalhin ang mga numero sama sa teksto ngadto sa normal nga mga numero ug vice versa)
- transpose (i-rotate) ang mga lamesa ug palapdan ang duha ka dimensyon nga cross-table ngadto sa patag
- idugang ang dugang nga mga kolum sa lamesa ug gamita ang mga pormula ug mga gimbuhaton niini gamit ang M nga lengguwahe nga gitukod sa Power Query.
- ...
Pananglitan, magdugang ta ug kolum nga adunay ngalan sa teksto sa bulan sa among lamesa, aron sa ulahi mas dali ang paghimo sa mga taho sa pivot table. Aron mahimo kini, pag-right-click sa ulohan sa kolum petsaug pilia ang sugo Duplicate nga kolum (Duplicate nga Kolum), ug dayon i-right-click ang ulohan sa duplicate nga column nga makita ug pilia ang Commands Pagbag-o - Bulan - Ngalan sa Bulan:
Usa ka bag-ong kolum kinahanglan nga maporma nga adunay mga ngalan sa teksto sa bulan alang sa matag laray. Pinaagi sa pag-double click sa usa ka ulohan sa kolum, mahimo nimo kini usbon gikan sa Petsa sa Kopya sa mas komportable Month, pananglitan
Kung sa pipila ka mga kolum ang programa wala kaayo makaila sa tipo sa datos, nan makatabang ka niini pinaagi sa pag-klik sa icon sa format sa wala nga bahin sa matag kolum:
Mahimo nimong dili iapil ang mga linya nga adunay mga sayup o walay sulod nga mga linya, ingon man ang dili kinahanglan nga mga managers o kustomer, gamit ang usa ka yano nga filter:
Dugang pa, ang tanan nga gihimo nga mga pagbag-o gitakda sa tuo nga panel, diin kini kanunay nga i-roll back (cross) o usbon ang ilang mga parameter (gear):
Gaan ug elegante, dili ba?
Lakang 2. Atong usbon ang atong hangyo ngadto sa usa ka function
Aron sa sunod nga pag-usab sa tanan nga mga pagbag-o sa datos nga gihimo alang sa matag imported nga libro, kinahanglan namon nga i-convert ang among gibuhat nga hangyo ngadto sa usa ka function, nga unya magamit, sa baylo, sa tanan namon nga mga file. Sa pagbuhat niini mao ang tinuod nga yano kaayo.
Sa Editor sa Pangutana, adto sa tab nga View ug i-klik ang buton Abanteng Editor (Tan-awa — Advanced Editor). Kinahanglang magbukas ang usa ka bintana diin ang tanan namong nangaging mga aksyon isulat sa porma sa code sa M nga pinulongan. Palihug timan-i nga ang agianan sa file nga among gi-import alang sa pananglitan gi-hardcode sa code:
Karon maghimo kita og pipila ka mga kausaban:
Yano ang ilang kahulogan: ang unang linya (filepath) => gihimo ang among pamaagi nga usa ka function nga adunay argumento filepath, ug sa ubos atong usbon ang fixed nga dalan ngadto sa bili niini nga variable.
Tanan. Pag-klik sa Humana ug kinahanglan makita kini:
Ayaw kahadlok nga nawala ang datos - sa tinuud, OK ra ang tanan, kinahanglan nga ingon niini ang tanan 🙂 Malampuson namon nga nahimo ang among naandan nga function, diin ang tibuuk nga algorithm alang sa pag-import ug pagproseso sa datos mahinumduman nga wala gihigot sa usa ka piho nga file . Nagpabilin kini sa paghatag niini og mas masabtan nga ngalan (pananglitan getData) sa panel sa tuo sa field unang ngalan ug makaani ka Panimalay — Isira ug i-download (Balay — Isira ug I-load). Palihug timan-i nga ang agianan sa file nga among gi-import alang sa pananglitan gi-hardcode sa code. Mobalik ka sa panguna nga bintana sa Microsoft Excel, apan ang usa ka panel nga adunay gihimo nga koneksyon sa among function kinahanglan nga makita sa tuo:
Lakang 3. Pagkolekta sa tanan nga mga file
Ang tanan nga pinakalisud nga bahin anaa sa luyo, ang makapahimuot ug sayon nga bahin nagpabilin. Adto sa tab Data - Paghimo Pangutana - Gikan sa File - Gikan sa Folder (Data — Bag-ong Pangutana — Gikan sa file — Gikan sa folder) o, kung ikaw adunay Excel 2010-2013, parehas sa tab Kusog nga Pagpangutana. Sa bintana nga makita, ipiho ang folder kung diin nahimutang ang tanan namon nga gigikanan nga mga file sa lungsod ug i-klik OK. Ang sunod nga lakang kinahanglan magbukas sa usa ka bintana diin ang tanan nga mga file sa Excel nga makit-an sa kini nga folder (ug ang mga subfolder niini) ug mga detalye alang sa matag usa niini ilista:
I-klik Kausaban (I-edit) ug pag-usab kita moadto sa pamilyar nga query editor window.
Karon kinahanglan namong idugang ang laing kolum sa among lamesa gamit ang among gibuhat nga function, nga "mobira" sa datos gikan sa matag file. Aron mahimo kini, adto sa tab Idugang ang Column – Custom Column (Add Column — Add Custom Column) ug sa bintana nga makita, isulod ang among function getData, nga nagpiho alang niini ingon usa ka argumento ang tibuuk nga agianan sa matag file:
Human sa pag-klik OK ang gibuhat nga kolum kinahanglan idugang sa among lamesa sa tuo.
Karon atong tangtangon ang tanan nga wala kinahanglana nga mga kolum (sama sa Excel, gamit ang tuo nga buton sa mouse - Kuhaa), ibilin lamang ang gidugang nga kolum ug ang kolum nga adunay ngalan sa file, tungod kay kini nga ngalan (mas tukma, ang siyudad) mapuslanon nga mabatonan sa kinatibuk-ang datos alang sa matag laray.
Ug karon ang "wow moment" - pag-klik sa icon nga adunay kaugalingon nga mga udyong sa taas nga tuo nga suok sa dugang nga kolum sa among function:
… kuhaa ang tsek Gamita ang orihinal nga ngalan sa kolum isip prefix (Gamita ang orihinal nga ngalan sa kolum isip prefix)ug i-klik OK. Ug ang among function magkarga ug magproseso sa datos gikan sa matag file, nga nagsunod sa natala nga algorithm ug pagkolekta sa tanan sa usa ka sagad nga lamesa:
Para sa kompleto nga katahom, mahimo usab nimong tangtangon ang .xlsx nga mga extension gikan sa unang kolum nga adunay mga ngalan sa file - pinaagi sa standard nga pagpuli sa "wala" (pag-right-click sa ulohan sa kolum - Kapuli) ug ilisan ang ngalan niini nga column ngadto sa City. Ug itul-id usab ang format sa datos sa kolum nga adunay petsa.
Tanan! Pag-klik sa Panimalay - Pagsira ug Pag-load (Balay — Close & Load). Ang tanang datos nga nakolekta pinaagi sa pangutana alang sa tanang siyudad i-upload sa kasamtangan nga Excel sheet sa “smart table” nga pormat:
Ang gibuhat nga koneksyon ug ang among function sa asembliya dili kinahanglan nga i-save nga gilain sa bisan unsang paagi - kini gitipigan kauban ang karon nga file sa naandan nga paagi.
Sa umaabot, sa bisan unsang mga pagbag-o sa folder (pagdugang o pagtangtang sa mga lungsod) o sa mga file (pag-usab sa gidaghanon sa mga linya), igo na ang pag-right-click direkta sa lamesa o sa pangutana sa tuo nga panel ug pilia ang sugo I-update ug I-save (I-refresh) - Ang Power Query "magtukod pag-usab" sa tanan nga datos pag-usab sa pipila ka segundo.
PS
Amendment. Pagkahuman sa mga update sa Enero 2017, nahibal-an sa Power Query kung giunsa pagkolekta ang mga workbook sa Excel nga mag-inusara, ie dili na kinahanglan nga maghimo usa ka lahi nga function - awtomatiko kini nga mahitabo. Busa, ang ikaduhang lakang gikan niini nga artikulo dili na gikinahanglan ug ang tibuok proseso nahimong mas simple:
- Pagpili Paghimo Hangyo - Gikan sa File - Gikan sa Folder - Pagpili Folder - OK
- Human makita ang lista sa mga file, pindota Kausaban
- Sa bintana sa Query Editor, palapdan ang kolum sa Binary nga adunay doble nga arrow ug pilia ang ngalan sa sheet nga kuhaon gikan sa matag file.
Ug kana lang! Kanta!
- Pagdesinyo pag-usab sa crosstab ngadto sa usa ka patag nga angay alang sa paghimo sa mga pivot table
- Paghimo ug animated bubble chart sa Power View
- Macro aron ma-assemble ang mga sheet gikan sa lainlaing mga file sa Excel ngadto sa usa