Iri nyore, pakutanga pakuona, dambudziko nemhinduro isiri pachena: bvisa izwi rekupedzisira kubva mumutsara wechinyorwa. Zvakanaka, kana, mune zvakajairika, chidimbu chekupedzisira, chakaparadzaniswa neakapihwa delimiter hunhu (nzvimbo, koma, nezvimwewo) Nemamwe mazwi, zvinodikanwa kuita reverse kutsvaga (kubva kumagumo kusvika kumavambo) mutambo ye rakapihwa uye wobva waburitsa mavara ese kurudyi kwaro .
Ngatitarisei kune yakajairika nzira dzinoverengeka dzekusarudza kubva: mafomula, macros, uye kuburikidza neSimba Query.
Nzira 1. Zvirongwa
Kuita kuti zvive nyore kunzwisisa essence uye mechanics yeformula, ngatitangei zvishoma kubva kure. Chekutanga, ngatiwedzerei nhamba yenzvimbo pakati pemazwi ari mugwaro redu manyoro kusvika, semuenzaniso, zvidimbu makumi maviri. Iwe unogona kuita izvi nekutsiva basa. SUBSTITUTE (SUBSTITUTE) uye basa rekudzokorora hunhu hwakapihwa N-nguva - Dzokorora (REPT):
Iye zvino tinocheka mavara makumi maviri kubva kumagumo ezvinyorwa zvinobuda tichishandisa basa RUDYI (RUDYI):
Kwava kudziya, handiti? Inoramba ichibvisa nzvimbo dzekuwedzera uchishandisa basa TRIM (TRIM) uye dambudziko richagadziriswa:
Mushanduro yeChirungu, fomula yedu ichaita seizvi:
=KUTAMBURA(KURUDHI(RUDZO(A1;»);REPT(»«;20));20))
Ndinovimba zviri pachena kuti musimboti hazvikodzeri kuisa chaizvo 20 nzvimbo - chero nhamba ichaita, chero yakareba kupfuura kureba kwezwi rakareba muzvinyorwa zvinobva.
Uye kana iyo vhezheni yechinyorwa ichida kukamurwa kwete nenzvimbo, asi neimwe mutsara hunhu (semuenzaniso, necomma), saka fomula yedu inoda kugadziridzwa zvishoma:
Nzira 2. Macro basa
Basa rekubvisa izwi rekupedzisira kana chidimbu kubva muzvinyorwa zvinogona zvakare kugadziriswa uchishandisa macros, kureva, kunyora reverse rekutsvaga basa muVisual Basic iyo ichaita zvatinoda - tsvaga yakapihwa substring mutambo iri mhiri kwakatarisana - kubva. kuguma kwekutanga.
Dzvanya nzira yekudimbudzira kiibhodhi alt+F11 kana bhatani Visual Basic Tab yokuvaka (Mugadziri)kuvhura iyo macro editor. Wobva wawedzera module nyowani kuburikidza nemenyu Pinza – Module uye kopira kodhi inotevera ipapo:
Shanda LastWord(txt As String, Optional delim As String = " ", Optional n As Integer = 1) As String arFragments = Split(txt, delim) LastWord = arFragments(UBound(arFragments) - n + 1) End Function
Iye zvino unogona kuchengetedza bhuku rebasa (mune macro-enabled fomati!) uye shandisa iro rakagadzirwa basa mune inotevera syntax:
=LastWord(txt; delim; n)
apo
- txt – sero ine kwakabva mavara
- delim - kupatsanura hunhu (default - nzvimbo)
- n -izwi ripi rinofanira kubviswa kubva kumagumo (nekusagadzikana - yekutanga kubva kumagumo)
Nezve chero shanduko mune yezvinyorwa zvinyorwa mune ramangwana, yedu macro basa rinoverengerwa panhunzi, senge chero yakajairwa Excel sheet sheet.
Nzira 3. Power Query
Simba Query ndeye yemahara yekuwedzera kubva kuMicrosoft yekupinza data muExcel kubva kunenge chero kupi uye wozoshandura iyo data yakatorwa kuita chero fomu. Simba uye kutonhorera kweiyi yekuwedzera-mukati yakakura zvekuti Microsoft yakavaka zvese zvayo maficha muExcel 2016 nekukasira. YeExcel 2010-2013 Power Query inogona kutorwa mahara kubva pano.
Basa redu rekuparadzanisa izwi rekupedzisira kana chidimbu kuburikidza neakapihwa separator uchishandisa Power Query rinogadziriswa zviri nyore.
Chekutanga, ngatitenderei tafura yedu yedata kuita tafura yakangwara tichishandisa mapfupi ekhibhodi. Ctrl+T kana mirairo Kumba – Fomati setafura (Kumba - Fomati seTafura):
Zvadaro tinoisa "smart table" yakagadzirwa muPower Query tichishandisa murairo Kubva patafura/renji (Kubva patafura/chikamu) Tab Data (kana iwe uine Excel 2016) kana pane tab Simba Query (kana uine Excel 2010-2013):
Mumubvunzo wemupepeti hwindo rinovhurwa, pane tab Kuchinja (Shandura) sarudza timu Split Column – By Delimiter (Split Column - By delimiter) uye zvino inosara kuseta iyo yekuparadzanisa hunhu uye sarudza sarudzo Rightmost delimiterkucheka kwete mazwi ese, asi ekupedzisira chete.
Mushure mekunge mavharidza OK izwi rekupedzisira richapatsanurwa kuita mbiru itsva. Iyo isingakoshi yekutanga koramu inogona kubviswa nekudzvanya-kurudyi musoro wayo uye kusarudza Bvisa (Delete). Iwe unogona zvakare kutumidza zita rasara mumusoro wetafura.
Mhedzisiro yacho inogona kudzoserwa kushepa uchishandisa rairo Imba - Vhara uye Rodha - Vhara uye Rodha ku ... (Kumba - Vhara & Rodha - Vhara & Rodha ku…):
Uye semhedzisiro tinowana:
Sezvizvi - zvakachipa uye anofara, pasina mafomula uye macros, anenge asina kubata kiibhodhi 🙂
Kana rondedzero yepakutanga ikachinja mune ramangwana, zvichakwana kudzvanya-kurudyi kana kushandisa nzira yekudimbudzira kiibhodhi Ctrl+alt+F5 gadzirisa chikumbiro chedu.
- Kupatsanura mavara anonamira kuita makoramu
- Kupatsanura nekupatsanura zvinyorwa nezvirevo zvenguva dzose
- Kubvisa mazwi ekutanga kubva muzvinyorwa neiyo SUBSTITUTE basa