Mukati
Imwe yemavhidhiyo anonyanya kutariswa pane yangu YouTube chiteshi vhidhiyo nezve Flash Zadza muMicrosoft Excel. Izvo zvakakosha zvechishandiso ichi ndechekuti kana iwe uchida kushandura yako sosi data, saka iwe unongoda kutanga kutaipa mhedzisiro yaunoda kuwana mukoramu iri padyo. Mushure memasero akati wandei akataipa nemaoko (kazhinji 2-3 anokwana), Excel icha "kunzwisisa" pfungwa yeshanduko yaunoda uye otoenderera mberi nezvawanyora, ichipedzisa basa rese risingaite kwauri:
Iyo quintessence yekubudirira. Bhatani remashiripiti "ita zvakanaka" iro isu tese tinoda zvakanyanya, handiti?
Muchokwadi, kune analogue yechishandiso chakadaro muSimba Query - ipapo inonzi Koramu kubva mumienzaniso (Koramu kubva kuMienzaniso). Muchokwadi, iyi idiki hungwaru hwekugadzira yakavakirwa muSimba Query iyo inogona kukurumidza kudzidza kubva kune yako data uye wozoishandura. Ngatitarisei zvakanyanya kugona kwayo mune akati wandei anoshanda mamiriro kuitira kuti tinzwisise kwaanogona kutibatsira mumabasa chaiwo.
Muenzaniso 1. Gluing / kucheka zvinyorwa
Ngatitii tine tafura "yakangwara" muExcel ine data revashandi:
Irodze muPower Query nenzira yakajairwa - nebhatani Kubva Tafura/Range Tab Data (Data - Kubva Tafura/Range).
Ngatitii tinoda kuwedzera koramu ine mazita ekupedzisira uye ekutanga kune mumwe nemumwe mushandi (Ivanov SV yemushandi wekutanga, nezvimwewo). Kugadzirisa dambudziko iri, unogona kushandisa imwe yenzira mbiri:
- tinya-kurudyi pane iyo kolamu yemusoro ine source data uye sarudza rairo Wedzera chikamu kubva mumienzaniso (Wedzera chikamu kubva mumienzaniso);
- sarudza imwe kana kupfuura makoramu ane data uye pane tab Kuwedzera chikamu sarudza timu Koramu kubva mumienzaniso. Pano, mune yekudonhedza pasi, unogona kutsanangura kana makoramu ese kana akasarudzwa chete anofanirwa kuongororwa.
Zvadaro zvinhu zvose zviri nyore - mumutsara unowanikwa kurudyi, tinotanga kuisa mienzaniso yezvaunoda, uye hungwaru hwekugadzira hwakavakwa muPower Query hunoedza kunzwisisa shanduko yedu yekushandura uye kuenderera mberi mberi kwayo:
Nenzira, iwe unogona kuisa sarudzo dzakakodzera mune chero masero eiyi koramu, kureva kwete kumusoro-pasi uye mumutsara. Zvakare, iwe unogona nyore kuwedzera kana kubvisa makoramu kubva pakuongorora gare gare uchishandisa mabhokisi ekutarisa ari mubhari yemusoro.
Teerera kune formula iri pamusoro pehwindo - izvi ndizvo zvinogadzirwa neSmart Power Query kuti tiwane mhinduro dzatinoda. Izvi, nenzira, ndiwo musiyano wakakosha pakati pechishandiso ichi uye Instant fill muExcel. Kuzadza nekukasira kunoshanda se "bhokisi dema" - havatiratidze pfungwa yeshanduko, asi ingopa mhinduro dzakagadzirirwa uye tinozvitora sezvinei. Pano zvese zviri pachena uye iwe unogona kugara uchinyatsonzwisisa zviri kuitika nedata.
Kana iwe ukaona iyo Power Query "yakabata zano", saka unogona kudzvanya bhatani zvakachengeteka OK kana nzira yekudimbudzira keyboard Ctrl+pinda -Koramu yetsika ine fomula yakagadzirwa nePower Query ichagadzirwa. Nenzira, inogona kugadziridzwa nyore nyore seyakajairwa yakagadzirwa nemaoko makoramu (nemurairo Kuwedzera Column - Custom Column) nekudzvanya giya icon kurudyi rwezita renhanho:
Muenzaniso 2: Nyaya semitsetse
Kana iwe ukadzvanya-kurudyi pane iyo column musoro nemavara uye wosarudza murairo Kuchinja (Shandura), ipapo iwe unogona kuona mirairo mitatu ine basa rekuchinja rejista:
Yakanaka uye inotonhorera, asi mune iyi runyorwa, semuenzaniso, ini pachangu ndagara ndisina imwe sarudzo - nyaya senge mumitsara, kana capitalization (capital) isingave iyo yekutanga vara muizwi rimwe nerimwe, asi chete yekutanga tsamba muchitokisi, uye zvimwe zvinyorwa kana Izvi zvichiratidzwa mumavara madiki (madiki).
Ichi chisipo chiri nyore kushandisa nehungwaru hwekugadzira Makoramu kubva mumienzaniso - ingo pinda akati wandei ekuti Power Query ienderere mberi mumweya mumwe chete:
Seformula pano, Power Query inoshandisa boka remabasa Text.Upper и Text.Lower, kushandura mavara kuva epamusoro neakaderera, zvichiteerana, uye mabasa Text.Start и Text.Mid -analogues yeExcel inoshanda LEFT uye PSTR, inokwanisa kuburitsa substring kubva pane zvinyorwa kubva kuruboshwe uye kubva pakati.
Muenzaniso 3. Kutenderwa kwemashoko
Dzimwe nguva, kana uchigadzira iyo data yakagamuchirwa, zvinova zvakafanira kurongedzazve mazwi mumaseru mune yakapihwa kutevedzana. Ehe, iwe unogona kupatsanura iyo koramu muakaparadzana mazwi makoramu uye wozoinamatira kumashure mune yakatarwa (usakanganwa kuwedzera nzvimbo), asi nerubatsiro rwechishandiso. Koramu kubva mumienzaniso zvese zvichange zviri nyore:
Muenzaniso 4: Nhamba chete
Rimwe basa rakakosha nderekuburitsa nhamba chete (nhamba) kubva mukati mesero. Sepakutanga, mushure mekurodha data muPower Query, enda kune iyo tab Kuwedzera koramu - Column kubva mumienzaniso uye uzadze masero akati wandei nemaoko kuti chirongwa chinzwisise kuti chii chaizvo chatinoda kuwana:
Bingo!
Zvekare, zvakakodzera kutarisa kumusoro kwehwindo kuti uone kuti Query yakagadzira fomula nemazvo - mune iyi kesi ine basa. Text. Sarudza, iyo, sezvaungafungidzira, inobvisa mavara akapihwa kubva muzvinyorwa zvekwakabva maererano nerondedzero. Zvadaro, iyi rondedzero, hongu, inogona kugadziridzwa zviri nyore mubhawa reformula kana zvichidikanwa.
Muenzaniso 5: Mameseji chete
Zvakafanana nemuenzaniso wekare, unogona kudhonza kunze uye zvinopesana - chete zvinyorwa, kubvisa nhamba dzose, zviratidzo zvepumburu, nezvimwe.
Muchiitiko ichi, basa rakatopesana mune zvinoreva rinoshandiswa - Text.Bvisa, iyo inobvisa mavara kubva patambo yepakutanga maererano nerunyoro rwakapiwa.
Muenzaniso 6: Kubvisa data kubva pane alphanumeric porridge
Power Query inogonawo kubatsira mune dzimwe nyaya dzakaoma, kana iwe uchida kubvisa ruzivo runobatsira kubva kune alphanumeric porridge muchitokisi, semuenzaniso, tora nhamba yeakaunti kubva pakutsanangurwa kwechinangwa chekubhadhara pane chirevo chebhangi:
Ziva kuti iyo Power Query inogadzirwa shanduko formula inogona kunge yakaoma:
Kuti zvive nyore kuverenga uye kunzwisisa, inogona kushandurwa kuita fomu ine hunyoro uchishandisa yemahara online sevhisi. Power Query Formatter:
Chinhu chinobatsira zvikuru - kuremekedza kune vasiki!
Muenzaniso 7: Kushandura misi
Tool Koramu kubva mumienzaniso inogona kuiswa kune date kana datetime columns zvakare. Paunopinda madhijitari ekutanga ezuva, Power Query ichabatsira kuratidza rondedzero yezvose zvinogoneka shanduko sarudzo:
Saka iwe unogona kushandura nyore zuva rekutanga kuita chero chimiro chekunze, senge "gore-mwedzi-zuva":
Muenzaniso 8: Kuisa mapoka
Kana tikashandisa mudziyo Koramu kubva mumienzaniso kune koramu ine nhamba data, inoshanda zvakasiyana. Ngatitii tine mhinduro dzebvunzo dzevashandi dzakaiswa muPower Query (conditional zvibodzwa muchikamu 0-100) uye isu tinoshandisa anotevera ane giredhisheni:
- Masters - avo vakawana zvinopfuura makumi mapfumbamwe
- Nyanzvi - dzakawana kubva ku70 kusvika ku90
- Vashandisi - kubva pamakumi matatu kusvika makumi manomwe
- Vanotanga - avo vakawana zvisingasviki makumi matatu
Kana isu tikawedzera koramu kubva kumienzaniso kune iyo rondedzero uye totanga kuronga aya madhizaini nemaoko, saka munguva pfupi Power Query inotora zano redu uye nekuwedzera koramu ine fomula, apo vanoshanda mukati memumwe nemumwe. if logic ichaitwa, yakafanana chaizvo nezvatinoda:
Zvekare, haugone kudzvanya mamiriro acho kusvika kumagumo, asi tinya OK wobva wagadzirisa zvikumbaridzo zvatove mufomula - inokurumidza neiyi nzira:
mhedziso
Chokwadi chishandiso Koramu kubva mumienzaniso haisi "mapiritsi emashiripiti" uye, nokukurumidza kana kuti gare gare, pachava nemamiriro ezvinhu asina kukwana kana kunyanya kusaregererwa kwe "purazi rekubatana" mu data, apo Power Query ichakundikana uye haizokwanisi kushanda zvatinoda. zvakarurama kwatiri. Nekudaro, sechishandiso chekubatsira, yakanaka kwazvo. Uyezve, nekudzidza mafomula aakagadzira, unogona kuwedzera ruzivo rwako rwemabasa emutauro weM, uyo unogara uchishanda mune ramangwana.
- Kupatsanura Chinyorwa chine Matauriro Akajairwa (RegExp) mune Simba Query
- Kutsvaga mavara asina kujeka muKubvunza Kwesimba
- Flash Zadza muMicrosoft Excel