Kuunganidza matafura kubva akasiyana eExcel mafaera ane Power Query

Kugadziriswa kwedambudziko

Ngatitarisei mhinduro yakanaka kune imwe yeaya akajairika mamiriro anotarisana nevazhinji vashandisi veExcel nekukurumidza kana gare gare: iwe unofanirwa kukurumidza uye otomatiki kuunganidza data kubva kunhamba huru yemafaira mune imwe yekupedzisira tafura. 

Ngatitii tine folda inotevera, ine akati wandei mafaera ane data kubva kumaguta emapazi:

Nhamba yemafaira haina basa uye inogona kuchinja mune ramangwana. Imwe neimwe faira ine bepa rine zita Salespane iyo data table iripo:

Nhamba yemitsara (mirairo) mumatafura, hongu, yakasiyana, asi seti yemakoramu ndiyo yakajairika kwese kwese.

Basa: kuunganidza data kubva kune ese mafaera mubhuku rimwechete neinozotevera otomatiki kugadzirisa paunenge uchiwedzera kana kudzima mafaera eguta kana mitsara mumatafura. Maererano netafura yekupedzisira yakasimbiswa, zvino zvichave zvichibvira kuvaka chero mishumo, pivot tables, filter-sort data, etc. Chinhu chikuru ndechokukwanisa kuunganidza.

Tinosarudza zvombo

Kune mhinduro, isu tinoda yazvino vhezheni yeExcel 2016 (iyo inodiwa mashandiro akatovakwa mairi nekusarudzika) kana shanduro dzakapfuura dzeExcel 2010-2013 ine yemahara yekuwedzera yakaiswa. Simba Query kubva kuMicrosoft (dhawunirodha pano). Power Query chishandiso chakanyanya kuchinjika uye chine simba kwazvo chekurodha data muExcel kubva kunze kwenyika, wobva wakurura nekuchigadzirisa. Power Query inotsigira angangoita ese aripo data masosi - kubva mameseji mafaera kuenda kuSQL uye kunyange Facebook 🙂

Kana iwe usina Excel 2013 kana 2016, saka haugone kuverenga mberi (kungotamba). Mushanduro dzekare dzeExcel, basa rakadaro rinogona kuitwa chete nekugadzira macro muVisual Basic (iyo yakaoma kwazvo kune vanotanga) kana ne monotonous manual kukopa (izvo zvinotora nguva yakareba uye zvinogadzira zvikanganiso).

Danho 1. Ngenisa faira rimwe chete semuenzaniso

Kutanga, ngatitorei data kubva kune rimwe bhuku semuenzaniso, kuitira kuti Excel "itore pfungwa". Kuti uite izvi, gadzira bhuku idzva rekushanda uye…

• kana iwe uine Excel 2016, wobva wavhura iyo tab Data ndokuzo Gadzira Mubvunzo - Kubva Pafaira - Kubva kuBhuku (Data - Mutsva Mutsva- Kubva faira - Kubva kuExcel)
• kana uine Excel 2010-2013 ine Power Query add-in yakaiswa, wobva wavhura tab. Simba Query uye sarudza pairi Kubva pafaira - Kubva mubhuku (Kubva faira - Kubva kuExcel)

Zvadaro, muhwindo rinovhura, enda kune yedu folda nemishumo uye sarudza chero yemafaira eguta (hazvina basa kuti ndeipi, nekuti ese akajairika). Mushure memasekondi mashoma, hwindo reNavigator rinofanira kuoneka, paunoda kusarudza pepa ratinoda (Kutengesa) kuruboshwe, uye zvirimo zvicharatidzwa kurudyi:

Kana ukadzvanya bhatani riri pakona yekurudyi yehwindo iri Download (Mutoro), ipapo tafura ichakurumidza kuendeswa kune pepa muchimiro chayo chepakutanga. Kune rimwe faira, izvi zvakanaka, asi isu tinoda kurodha mafaera akawanda akadaro, saka tichaenda zvishoma zvakasiyana uye tinya bhatani. Kururamisa (Hodha). Mushure meizvozvo, iyo Power Query query edhita inofanirwa kuratidzwa muhwindo rakasiyana nedata redu kubva mubhuku:

Ichi chishandiso chine simba kwazvo chinokubvumira "kupedzisa" tafura kune maonero atinoda. Kunyangwe tsananguro yezvese mabasa ayo angangotora zana mapeji, asi, kana muchidimbu, uchishandisa hwindo iri unogona:

• sefa data isina kufanira, mitsetse isina chinhu, mitsetse ine zvikanganiso
• ronga data nechikamu chimwe kana akawanda
• bvisa kudzokorora
• patsanura mavara anonamira nemakoramu (nemadelimiters, nhamba yemavara, nezvimwewo)
• isa zvinyorwa muhurongwa (bvisa dzimwe nzvimbo, kesi chaiyo, nezvimwewo)
• shandura mhando dze data munzira dzese dzinogoneka (shandura manhamba semavara kuita manhamba akajairwa uye zvichipesana)
• transpose (tenderera) matafura uye kuwedzera maviri-dimensional muchinjiko-matafura kuita iwo akati sandara
• wedzera mamwe makoramu patafura uye shandisa mafomula nemabasa mazviri uchishandisa M mutauro wakavakirwa muSimba Query.
• ...

Semuenzaniso, ngatiwedzerei koramu ine zita remavara emwedzi patafura yedu, kuitira kuti gare gare zvive nyore kugadzira mishumo yetafura yepivot. Kuti uite izvi, tinya-kurudyi pamusoro wekoramu zuvauye sarudza murairo Duplicate column (Duplicate Column), wobva wadzvanya-kurudyi pamusoro weiyo duplicate column inooneka wosarudza Mirairo Shandura – Mwedzi – Mwedzi Zita:

Chikamu chitsva chinofanira kuumbwa chine mazita emwedzi pamutsara wega wega. Nekudzvanya kaviri pamusoro wekoramu, unogona kupa zita kubva Copy Date kune imwe yakasununguka Mwedzi, semuenzaniso.

Kana mune mamwe makoramu chirongwa chisina kunyatso ziva mhando yedata, saka unogona kuibatsira nekudzvanya pane iyo fomati icon kuruboshwe rwekoramu yega yega:

Iwe unogona kusabvisa mitsetse ine zvikanganiso kana mitsara isina chinhu, pamwe nemamaneja asina kufanira kana vatengi, uchishandisa iri nyore sefa:

Uyezve, shanduko dzese dzakaitwa dzakamisikidzwa mupaneru yekurudyi, kwadzinogona kugara dzichitenderedzwa kumashure (muchinjiko) kana kushandura maparameter (giya):

Chiedza uye chinoyevedza, handizvo here?

Danho 2. Ngatishandure chikumbiro chedu kuita basa

Kuti tizodzokororazve shanduko dzese dzakaitwa kubhuku rega rega rinotengwa kunze kwenyika, isu tinofanirwa kushandura chikumbiro chedu chakasikwa kuita basa, iro rinozoshandiswa, zvakare, kune ese mafaera edu. Kuita izvi zviri nyore chaizvo.

Mune Query Editor, enda kuTarisa tab uye tinya bhatani Advanced Editor (Ona - Yepamberi Mupepeti). Hwindo rinofanirwa kuvhurika apo zvese zvatakaita zvekare zvichanyorwa nenzira yekodhi mumutauro weM. Ndokumbira utarise kuti nzira inoenda kufaira yatakaunza kune muenzaniso yakaomeswa mukodhi:

Iye zvino ngatigadzirisei zvishoma:

Zvazvinoreva zviri nyore: mutsara wekutanga (filepath)=> inoshandura maitiro edu kuita chiito nenharo filepath, uye pazasi isu tinoshandura nzira yakagadziriswa kune kukosha kweiyo shanduko. 

Zvose. Dzvanya pa pedza uye unofanira kuona izvi:

Usatya kuti iyo data yanyangarika - chokwadi, zvese zvakanaka, zvese zvinofanirwa kutaridzika seizvi 🙂 Isu takabudirira kugadzira yedu tsika basa, uko iyo algorithm yese yekupinza uye kugadzirisa data inoyeukwa pasina kusungirirwa kune chaiyo faira. . Inoramba ichipa iro zita rinonzwisisika (semuenzaniso getData) mupaneru kurudyi mumunda zita First uye unogona kukohwa Musha - Vhara uye dhawunirodha (Kumba - Vhara uye Rodha). Ndokumbira utarise kuti nzira inoenda kufaira yatakaunza kune muenzaniso yakadhindwa mukodhi. Iwe uchadzokera kune huru Microsoft Excel hwindo, asi pani ine yakasikwa yekubatanidza kune yedu basa inofanira kuoneka kurudyi:

Danho 3. Kuunganidza mafaira ose

Zvose zvakaomesesa zviri shure, chikamu chinofadza uye chiri nyore chinoramba chiripo. Enda kune tab Dhata - Gadzira Mubvunzo - Kubva Pafaira - Kubva Folder (Data - Mutsva Mutsva - Kubva faira - Kubva folda) kana, kana iwe uine Excel 2010-2013, zvakafanana kune iyo tab Simba Query. Muhwindo rinoonekwa, tsanangura iyo folda uko mafaera edu ese eguta anowanikwa uye tinya OK. Nhanho inotevera inofanira kuvhura hwindo apo mafaera ese eExcel anowanikwa mune iyi folda (uye maforodha ayo madiki) uye ruzivo rwechimwe nechimwe chazvo achanyorwa:

Click chinja (Hodha) uye zvakare tinopinda mune yakajairika query editor window.

Iye zvino tinoda kuwedzera imwe koramu patafura yedu nebasa redu rakagadzirwa, iro "richadhonza" data kubva kune imwe neimwe faira. Kuti uite izvi, enda kune tab Wedzera Column - Custom Column (Wedzera Koramu - Wedzera Tsika Column) uye pahwindo rinoonekwa, isa basa redu getData, ichitsanangura iyo senharo nzira yakazara kune imwe neimwe faira:

Mushure mekunge mavharidza OK iyo column yakagadzirwa inofanira kuwedzerwa patafura yedu kurudyi.

Zvino ngatibvisei makoramu ese asina basa (sepaExcel, tichishandisa bhatani rekurudyi rembeva - Bvisa), ichisiya chete koramu yakawedzerwa uye koramu ine zita refaira, nekuti zita iri (kunyanya, iro guta) richabatsira kuve mune yakazara data yemutsara wega wega.

Uye ikozvino "wow nguva" - tinya pane icon nemiseve yayo mukona yekurudyi yekoramu yakawedzerwa nebasa redu:

… uncheck Shandisa zita rekutanga rechikamu sechivakashure (Shandisa zita rekoramu rekutanga sechivakashure)uye baya OK. Uye basa redu rinotakura uye kugadzirisa iyo data kubva kune yega faira, ichitevera yakarekodhwa algorithm uye kuunganidza zvese mutafura yakafanana:

Kuti uwane runako rwakakwana, unogona zvakare kubvisa .xlsx ekuwedzera kubva mukoramu yekutanga ine mazita emafaira - neyakajairwa kutsiva ne "hapana" (tinya-kurudyi pamusoro wekoramu - Substitute) uye tumidza zita iri kuti koramu manzwiro. Uye zvakare gadzirisa iyo data fomati mukoramu ine zuva.

Zvose! Dzvanya pa Musha – Vhara uye Rodha (Kumba - Vhara & Rodha). Yese data yakaunganidzwa nemubvunzo wemaguta ese ichaiswa kune yazvino Excel sheet mu "smart table" fomati:

Iyo yakasikwa yekubatanidza uye basa regungano redu haridi kuponeswa zvakasiyana munzira ipi neipi - dzinochengetwa pamwe chete nefaira razvino nenzira yakajairika.

Mune ramangwana, nechero shanduko mufolda (kuwedzera kana kubvisa maguta) kana mumafaira (kuchinja huwandu hwemitsara), zvichave zvakakwana kudzvanya-kurudyi zvakananga patafura kana pamubvunzo uri mupaneru yekurudyi uye wosarudza command Gadziridza & Chengeta (Refresh) -Simba Mubvunzo "uchavakazve" data rese zvakare mumasekonzi mashoma.

PS

Kugadzirisa. Mushure mekuvandudzwa kwaNdira 2017, Power Query yakadzidza nzira yekuunganidza eExcel mabhuku ega ega, kureva kuti hapana chikonzero chekuita basa rakaparadzana zvakare - rinongoitika rega. Saka, nhanho yechipiri kubva kuchinyorwa ichi haichadiwi uye maitiro ese anove akajeka:

1. Sarudza Gadzira Chikumbiro - Kubva Faira - Kubva Folder - Sarudza Folder - Zvakanaka
2. Mushure mekunge rondedzero yemafaira yaoneka, tinya chinja
3. Muwindo reQuery Editor, wedzera iyo Binary column nekaviri museve uye sarudza zita reshizha rinotorwa kubva kune yega faira.

Uye ndizvo chete! Song!

• Kugadziridzwazve kweiyo crosstab kuita furati yakakodzera kuvaka matafura epivot
• Kuvaka animated bubble chart muPower View
• Macro yekuunganidza mapepa kubva akasiyana eExcel mafaera kuita rimwe