Okulula, ekuqaleni, inkinga ngesisombululo esingabonakali: khipha igama lokugcina emgqeni wombhalo. Nokho, noma, esimweni esivamile, ucezu lokugcina, oluhlukaniswe uhlamvu oluhlukanisiwe olunikeziwe (isikhala, ukhefana, njll.) Ngamanye amazwi, kuyadingeka ukusebenzisa ukusesha okuhlanekezelwe (kusuka ekugcineni kuye ekuqaleni) kuchungechunge lwe uhlamvu olunikeziwe bese ukhipha zonke izinhlamvu kwesokudla salo .
Ake sibheke izindlela ezimbalwa ongakhetha kuzo ngokwesiko: amafomula, ama-macros, kanye Nombuzo Wamandla.
Indlela 1. Amafomula
Ukwenza kube lula ukuqonda ingqikithi kanye nobunjiniyela befomula, ake siqale kancane sisuka kude. Okokuqala, masikhulise inani lezikhala phakathi kwamagama kumbhalo wethu womthombo, ngokwesibonelo, izingcezu ezingama-20. Ungakwenza lokhu ngomsebenzi wokubuyisela. I-SUBSTITUTE (SUBSTITUTE) kanye nomsebenzi wokuphinda uhlamvu olunikeziwe izikhathi ezingu-N - REPEAT (REPT):
Manje sesinqamula izinhlamvu ezingama-20 ekugcineni kombhalo owumphumela sisebenzisa umsebenzi KUPHELA (KWESOKUDLA):
Sekuqala ukufudumala, akunjalo? Kusele ukususa izikhala ezengeziwe usebenzisa umsebenzi I-TRIM (TRIM) futhi inkinga izoxazululwa:
Enguqulweni yesiNgisi, ifomula yethu izobukeka kanje:
=INKOMBE(RIGHT(RIGHT(A1;»);REPT(» «;20));20))
Ngithemba ukuthi kucacile ukuthi ngokomthetho akudingekile ukufaka izikhala ze-20 ngqo - noma iyiphi inombolo ezokwenza, inqobo nje uma ingaphezu kobude begama elide kakhulu embhalweni womthombo.
Futhi uma umbhalo womthombo udinga ukuhlukaniswa hhayi ngendawo, kodwa ngomunye uhlamvu lwesihlukanisi (ngokwesibonelo, ngokhefana), ifomula yethu izodinga ukulungiswa kancane:
Indlela 2. Umsebenzi weMacro
Umsebenzi wokukhipha igama lokugcina noma ucezu embhalweni ungaxazululwa kusetshenziswa ama-macros, okungukuthi, ukubhala umsebenzi wokusesha ohlanekezelwe ku-Visual Basic ozokwenza esikudingayo - sesha umucu ongaphansi onikeziwe ochungechungeni olubheke kolunye uhlangothi - ukusuka isiphetho ekuqaleni.
Cindezela isinqamuleli sekhibhodi Alt+F11 noma inkinobho Visual Basic ithebhu Unjiniyela (Unjiniyela)ukuvula i-macro editor. Bese wengeza imojuli entsha ngemenyu Faka – Imojula bese ukopisha ikhodi elandelayo lapho:
Umsebenzi 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
Manje usungakwazi ukugcina incwadi yokusebenzela (ngefomethi enikwe amandla amakhulu!) futhi usebenzise umsebenzi odaliwe ku-syntax elandelayo:
=LastWord(txt ; delim ; n)
lapho
- txt – iseli enombhalo owumthombo
- delim - isihlukanisi uhlamvu (okuzenzakalelayo - isikhala)
- n - yiliphi igama okufanele likhishwe ekugcineni (ngokuzenzakalelayo - elokuqala ukusuka ekugcineni)
Nganoma yiziphi izinguquko embhalweni womthombo esikhathini esizayo, umsebenzi wethu omkhulu uzobalwa kabusha ngokuphazima kweso, njenganoma yimuphi umsebenzi ojwayelekile weshidi le-Excel.
Indlela 3. Umbuzo Wamandla
Umbuzo Wamandla iyisengezo samahhala esivela ku-Microsoft sokungenisa idatha ku-Excel kusuka cishe kunoma yimuphi umthombo bese siguqula idatha elandiwe ibe yinoma yiluphi uhlobo. Amandla nokuphola kwalesi sengezo kukhulu kangangokuthi iMicrosoft yakhe zonke izici zayo ku-Excel 2016 ngokuzenzakalelayo. Nge-Excel 2010-2013 Power Query ingalandwa mahhala kusuka lapha.
Umsebenzi wethu wokuhlukanisa igama lokugcina noma isiqeshana ngokusebenzisa isihlukanisi esinikeziwe sisebenzisa Umbuzo Wamandla uxazululwa kalula kakhulu.
Okokuqala, ake siguqule ithebula lethu ledatha libe ithebula elihlakaniphile sisebenzisa izinqamuleli zekhibhodi. Ctrl+T noma imiyalo Ikhaya – Fometha njengetafula (Ekhaya — Fometha Njengethebula):
Bese silayisha "ithebula le-smart" elidaliwe ku-Power Query sisebenzisa umyalo Kusukela kuthebula/ububanzi (Kusuka kuthebula/ububanzi) ithebhu Idatha (uma une-Excel 2016) noma kuthebhu Umbuzo Wamandla (uma une-Excel 2010-2013):
Kuwindi lomhleli wombuzo elivulekayo, kuthebhu Uguquko (Guqula) khetha iqembu Hlukanisa Ikholomu – Nge-Delimiter (Hlukanisa Ikholomu — Nge-delimiter) bese kusala ukusetha uhlamvu lwesihlukanisi bese ukhetha inketho I-delimiter engakwesokudlaukusika hhayi wonke amagama, kodwa kuphela elokugcina:
Ngemuva kokuchofoza OK igama lokugcina lizohlukaniswa libe yikholomu entsha. Ikholomu yokuqala engadingekile ingasuswa ngokuchofoza kwesokudla inhlokweni yayo nokukhetha Susa (Susa). Ungaphinda uqambe kabusha ikholomu esele kunhlokweni yethebula.
Imiphumela ingalayishwa ibuyiselwe eshidini kusetshenziswa umyalo Ikhaya - Vala futhi Ulayishe - Vala futhi Ulayishe ku ... (Ekhaya — Vala & Layisha — Vala & Layisha ku…):
Futhi ngenxa yalokho sithola:
Njengalokhu - kushibhile futhi kujabule, ngaphandle kwamafomula namamakhro, cishe ngaphandle kokuthinta ikhibhodi 🙂
Uma uhlu lwangempela lushintsha ngokuzayo, kuzokwanela ukuthi uchofoze kwesokudla noma usebenzise isinqamuleli sekhibhodi Ctrl+Alt+F5 buyekeza isicelo sethu.
- Ukuhlukanisa umbhalo onamathelayo ube amakholomu
- Ukuhlaziya nokudlulisa umbhalo ngezinkulumo ezivamile
- Ikhipha amagama okuqala embhalweni ngomsebenzi we-SUBSTITUTE