Ukuthola uhlamvu kumugqa wespredishithi se-Excel

Abasebenzisi be-Excel, naphezu kweqiniso lokuthi ukuthola umlingiswa oyifunayo kubonakala kuwumsebenzi olula, ngokuvamile abaqondi ukuthi kwenziwa kanjani. Ezinye zazo zilula, ezinye zinzima kakhulu. Futhi, ngezinye izikhathi kuba nezinkinga ekutholeni izinhlamvu ezinjengophawu lombuzo noma inkanyezi ngenxa yokuthi zisetshenziswa ezihlungini. Namuhla sizochaza izindlela zokuthola izimpawu zezinhlobo ezahlukene.

Uzithola kanjani izinhlamvu zombhalo (izinhlamvu nezinombolo) kuseli

Okokuqala, ake sizame ukwenza umsebenzi olula: thola ubukhona bezinhlamvu zombhalo kumaseli bese uthola lowo omdingayo. Ukwenza lokhu, udinga ukusebenzisa isengezo se-!SEMTools, ongacinga ngaso izinhlamvu zezinhlobo ezahlukene. Ukulandelana kwezenzo kanje:

1. Khetha ububanzi obungobokuqala bese ubukopishela kukholamu elandelayo.
2. Bese ukhetha ububanzi besibili.
3. Vula ithebhu ethi “!SEMTools”. Lapho, ohlangothini lwesobunxele lwebha yamathuluzi, kuzoba nethebhu ethi “Thola”.
4. Ngemuva kwalokho, vula imenyu ethi "Izimpawu".
5. Khona-ke imenyu eyengeziwe izovela, lapho udinga ukuthola khona into ethi "Izinhlamvu-izinombolo" bese uchofoza kuyo.

Kulesi sithombe sopopayi, ungabona kahle ukuthi ungaqhubeka kanjani ngendlela efanele ukuze uthole izinhlamvu zombhalo kuseli. Ngalo msebenzi wokwengeza, umsebenzisi angakwazi ukunquma ukuthi zikhona yini izinhlamvu ezingaphrinteki kwamanye amaseli.

Uzithola kanjani izinombolo kuseli yetafula

Kwesinye isikhathi udinga ukukhomba amaseli anezinombolo, kodwa anombhalo. Uma emaningi amangqamuzana anjalo, kungaba nzima ukuwabona. Ngaphambi kokuthi wenze lo msebenzi, udinga ukuchaza amagama ayisisekelo. Umqondo wethu oyinhloko uthi “thola”. Lokhu kusho ukuhlola ukuthi uhlobo oluthile lomlingiswa lukuyunithi yezinhlamvu. Uma uthi yebo, ibuyisela IQINISO, uma kungenjalo, AMANGA. Uma, ngaphezu kokusesha izinombolo kuseli, umsebenzisi ufuna ukwenza ezinye izenzo, ungasebenzisa ezinye izigaba zalo myalelo.

Umqondo wesibili odinga ukuhlakazwa izinombolo. Leli igama elibalulekile elisho izinhlamvu ezingafika kweziyi-10 ezihambisana nezinombolo ezisuka ku-0 kuye ku-9. Ngakho, ukuze uhlole ukuba khona kwezinombolo, umsebenzisi udinga ukuhlola ububanzi izikhathi eziyi-10. Lokhu kungenziwa ngokusebenzisa umsebenzi IFkodwa le ndlela idla isikhathi esiningi.

Ukuze uxazulule le nkinga, ungasebenzisa ifomula ekhethekile ezokwenza konke ukuhlola ngesikhathi esisodwa: =COUNT(SESHA({1:2:3:4:5:6:7:8:9:0};A1) )>0. Lo msebenzi une-syntax efanayo naleyo esesha izinhlamvu zesiCyrillic embhalweni.

Ungasebenzisa futhi isengezo esivele sine-macro eyakhelwe ngaphakathi ukuze wenze lo msebenzi. Kulokhu, kwanele ukusebenzisa ithebhu ekhethekile !SEMTools, okumele isetshenziswe kukholamu eyengeziwe, okuyikhophi ephelele yaleyo okungeyokuqala.

Ngakho-ke, isethi yezinyathelo okufanele zithathwe ihambisana ngokugcwele nesigaba sangaphambilini. Kufanele uqale ukhethe ibanga lokuqala, ulikopishe, bese ukhetha ikholomu evelayo bese usebenzisa i-macro kulo ngokuvumelana nokulandelana kwezinyathelo ezinikezwe kulesi sithombe sopopayi.

Ake sithi sidinga ukuthola izinombolo ezithile kuphela kuzo zonke ezinikeziwe. Kungenziwa kanjani lokhu? Okokuqala, ake sikubonise ukuthi ungakwenza kanjani nge-!SEMTools. Ukusebenzisa ithuluzi kulula. Kwanele ukubhala phansi zonke izinombolo ezidingekayo kubakaki, bese ucindezela inkinobho ethi KULUNGILE ukuze uqinisekise. Usebenzisa indlela efanayo, ungathola izinhlamvu zesiLatini noma uthole osonhlamvukazi emgqeni wombhalo.

Ungasebenzisa futhi ifomula ukuze uthole izinombolo ezidingekayo kuhlu lwamaseli. Ukuze wenze lokhu, udinga ukusebenzisa inhlanganisela yemisebenzi HLOLA и SEARCH. Ngosizo lwayo, awukwazi ukubona izinombolo ngazinye kuphela, kodwa futhi nokulandelana kwezinombolo okuphelele: =СЧЁТ(ПОИСК({01:02:03:911:112};A1))>0.

Kwesinye isikhathi udinga ukuthola izinombolo ezihlukaniswe yizikhala. Kulokhu, abizwa ngokuthi amagama-izinombolo. Ukuze uwathole, kufanele futhi usebenzise amathuluzi afanelekile !SEMTools. Lokhu kugqwayiza kubonisa ngokucacile ukuthi yiziphi izenzo okudingeka uzenze ukuze wenze lokhu.

Ungathola kanjani ukuthi i-excel cell iqukethe izinhlamvu zesiLatini

Ezikhathini eziningi, abasebenzisi be-Excel badida imiqondo ethi “Thola” nethi “Khipha”, nakuba kunomehluko omkhulu phakathi kwabo. Isisho sokuqala sisho ukuhlola ukuthi kukhona yini uhlamvu oluthile kuyunithi yezinhlamvu yombhalo noma ububanzi bedatha. Ngokulandelayo, umqondo othi “Khipha” usho ukukhipha uhlamvu olufunayo embhalweni bese uludlulisela komunye umsebenzi noma ulubhale kuseli.

Yini okudingeka yenziwe ukuze kutholakale izinhlamvu zesiLatini? Isibonelo, ungasebenzisa amafonti akhethekile azokwenza kube nokwenzeka ukuhlonza izinhlamvu zesiNgisi ngeso. Isibonelo, lokhu kwenza ifonti I-Dubai Medium, okwenza izinhlamvu zesiNgisi zibe nesibindi.

Kodwa yini okufanele uyenze uma kunedatha eningi? Kulokhu, ukunquma ngeso ukulandelana okufunayo kwamanani ukuze uhlaziye idatha akwanele. Kulokhu, udinga ukubheka izindlela zokwenza le nqubo ngokuzenzakalelayo. Kunezindlela eziningana ongakwenza ngayo lokhu.

Ukusebenzisa umsebenzi okhethekile

Inkinga enkulu yokufuna izinhlamvu zesiLatini ukuthi kunezikhathi ezimbili nengxenye ezingaphezu kwezinombolo. Ngakho-ke, udinga ukunikeza uhlelo iluphu ehlanganisa ukuphindaphinda okungu-26, okungase kucindezele kakhulu. Kodwa uma usebenzisa ifomula yamalungu afanayo ehlanganisa imisebenzi engenhla HLOLA и SEARCH, khona-ke lo mbono awubonakali uyinkimbinkimbi kakhulu: =COUNT(SESHA({“a”:”b”:”c”:”d”:”e”:”f”:”g”:”h”:”i”:”j”:”k”: »l»:»m»:»n»:»o»:»p»:»q»:»r»:»s»:»t»:»u»:»v»:»w»:»x »:»y»:»z»};A1))>0. Le fomula isebenza kahle ezimweni eziningi. Isibonelo, uma kungenakwenzeka ukufaka ama-macros afanelekile angakwazi ukwenza lokhu kalula futhi ngokushesha.

Kufomula echazwe ngenhla, i-A1 iyiseli lapho ukuhlola okwenziwa khona. Ngokufanelekile, udinga ukubeka leyo evumelana nesimo sakho. Lo msebenzi ubuyisela inani le-boolean njengomphumela wokuhlola. Uma okufanayo kutholwa, u-opharetha uyabuya TRUEuma zingekho - AMANGA.

Umsebenzi SEARCH ayikuvumeli ukusesha okubucayi kwezinhlamvu. Ukuze wenze lokhu, udinga ukusebenzisa opharetha UKUTHOLA, eyenza imisebenzi efanayo, inezimpikiswano ezifanayo, kuphela izwela amacala. Enye indlela iwukwenza ifomula engenhla ibe ifomula yamalungu afanayo. Kulokhu, izobukeka kanje:{=COUNT(SESHA(CHAR(STRING(65:90))),A1))>0}.

Njengoba lena kuyifomula yamalungu afanayo, kufanele icaciswe ngaphandle kwabakaki. Kulokhu, ngemva kokungena, kufanele ucindezele inhlanganisela yokhiye Ctrl + Shift + Enter (esikhundleni sokumane ucindezele ukhiye wokungena, njengoba kunjalo ngomsebenzi ovamile), ngemva kwalokho ama-curly braces azovela ngokwawo.

Uma udinga ukuthola izinhlamvu zesiCyrillic, ukulandelana kwezenzo kuyafana, udinga kuphela ukusetha ukulandelana kwezinhlamvu zesiCyrillic njengobubanzi bokusesha. =COUNT(SESHA({“a”:”b”:”c”:”g”:”e”:”e”:”e”:”g”:”h”:”i”:”d”: "k":"l":"m":"n":"o":"p":"r":"s":"t":"y":"f":"x":"c »:"h":"w":"u":"b":"s":"b":"e":"yu":"i"};A1))>0. Ungasebenzisa futhi umsebenzi SYMBOL, ukwenza lokhu. {=COUNT(SESHA(CHAR(STRING(192:223))),A1))>0}

Le fomula kufanele ibhalwe njengefomula yamalungu afanayo. Ngakho-ke, udinga ukucindezela inhlanganisela yokhiye Ctrl + Shift + Enter esikhundleni sokumane ucindezele ukhiye wokungena. Kodwa kukhona okuhlukile lapho lesi sici singeke sisebenze. Kufanele uqale uqinisekise ukuthi ulimi olumisiwe lwezinhlelo ezingezona i-Unicode yi-. Kulokhu, akufanele kube nezinkinga. Lawa mafomula anomehluko othile komunye nomunye. Esikhundleni sezinhlamvu ezingu-33, ifomula yokugcina isebenzisa kuphela 32. Okusho ukuthi, ayifaki uhlamvu ё njengeCyrillic.

Kulesi simo, ngokufanayo nakwedlule, ukuze useshe izinhlamvu ozifunayo ngendlela ezwelayo, kufanele usebenzise umsebenzi. UKUTHOLA. Ngakho-ke, ungasesha, isibonelo, ingxenye yezinhlamvu ezibhalwe ngezinhlamvu ezincane nengxenye ebhalwe ngosonhlamvukazi. Izimpikiswano ziyafana.

Ungawathola kanjani amagama esitokisini esiqukethe isiCyrillic nesiLatini

Ngokunengqondo singaphetha ngokuthi ukuze sifune lawo magama aqukethe kokubili isiCyrillic nesiLatini, kudingeka sisebenzise njengalokho esikufunayo, zonke izinhlamvu zezinhlamvu zesiNgisi nezesiNgisi.

Indlela yokuthola osonhlamvukazi kuseli

Ukuze uthole osonhlamvukazi, udinga ukusebenzisa umsebenzi UKUTHOLA, futhi njengoba izimpikiswano zicacisa izinhlamvu zesiCyrillic (noma izingxenye zezinhlamvu zesiLatini, uma udinga ukuzithola) noma amakhodi azo.

Uma usesha izinhlamvu zesiCyrillic ngamakhodi, udinga ukukhumbula ukuthi ithebula le-ASCII kufanele lisethelwe kuqala ukuze . Ngamagama alula, ukuze ube nokwasendaweni.

Uma udinga ukuthola noma yimaphi osonhlamvukazi, kungakhathaleki ukuthi badinga ukuseshwa ngaluphi uhlobo, udinga ukusebenzisa imisebenzi. AMANDLA и ISEXOLO… Ukulandelana kwezenzo kungokulandelayo:

1. Senza amanani osonhlamvukazi abancane ngeseli ehlukile.
2. Siqhathanisa imiphumela neyokuqala.
3. Ngemva kwalokho, sisebenzisa ifomula elandelayo: =HAYI(NGQINISO(IPHANSI(A1),A1))

Uma la maseli engafani, lokhu kubonisa ukuthi ezinye zezinhlamvu kuseli lokuqala bezingosonhlamvukazi.

Ukuthola izinhlamvu ku-Excel usebenzisa izinkulumo ezijwayelekile

Ungasebenzisa futhi izinkulumo ezivamile ukuze uthole izinhlamvu. Indlela engcono kakhulu yokwenza lokhu ukusebenzisa ithuluzi le-!SEMTools, njengoba lenza inqubo eminingi yokuzisebenzisa. I-spectrum yokusebenzisa izinkulumo ezivamile ku-Excel ibanzi kakhulu. Sizogxila kuqala emisebenzini SESHA, buyisela esikhundleni, I-EXTRACT.

Izindaba ezinhle ukuthi le misebenzi ingasetshenziswa ku-Google AmaSpredishithi naku-Excel ngalesi silungiselelo.

Umsebenzi wokuqala ojwayelekile ngu REGEXMATCH, engahlola ukuthi le phethini iyafana yini naleyo ekwelinye iseli. I-syntax: =REGEXMATCH(“umbhalo”;”iphethini ye-RegEx ozoyicinga”). Lo msebenzi ubuyisela inani elilodwa kwamabili: iqiniso noma amanga. Yini ngempela incike ekutheni umdlalo uyabonwa yini noma cha. Umsebenzi wesibili uthi =REGEXEXTRACT(“umbhalo”;”iphethini yokusesha ye-RegEx”) Ikuvumela ukuthi ukhiphe izinhlamvu ozifunayo kuyunithi yezinhlamvu.

Nokho, lo msebenzi unomehluko omncane kusukela ku-Google AmaSpredishithi. Kuqukethe ukuthi lokhu kokugcina, uma umbhalo oshiwo ungatholakali, ubuyisela iphutha, kuyilapho lesi sengezo sibonisa inani elingenalutho kuphela.

Futhi ekugcineni, udinga ukusebenzisa le fomula ukuze umiselele umbhalo: =REGEXREPLACE(“umbhalo”;”iphethini yokusesha ye-RegEx”;”umbhalo ozothatha indawo yokutholiwe”).

Okufanele ukwenze ngezimpawu ezitholakele

Kuhle. Ake sithi sithole izimpawu. Yini engenziwa ngabo ngokulandelayo? Kunezinketho ezimbalwa lapha zokuthi ungaqhubeka kanjani. Isibonelo, ungawasusa. Isibonelo, uma sithole izinhlamvu zesiLatini phakathi kwamanani wesiCyrillic. Ungakwazi futhi ukufaka uhlamvu olufanayo esikhundleni salo, ngesiCyrillic kuphela (isibonelo, isiNgisi esikhulu M ukuya ku-M) noma ukhiphe lolu hlamvu ukuze lusetshenziswe kwenye ifomula.

Ukukhipha Izinhlamvu Ezengeziwe ku-Excel

Kunezindlela eziningi zokususa izinhlamvu ezingafuneki ku-Excel. Enye yezinketho ukusebenzisa umsebenzi we-Find and Replace, lapho ungashintsha khona uhlamvu ofuna ukumsusa ngochungechunge olungenalutho "". Ungasebenzisa izinkulumo ezifanayo ezivamile ezisetshenziswa esikhundleni sohlamvu olutholiwe.

Khipha izinhlamvu ezithile ku-Excel

Ungasebenzisa umsebenzi othi “Thola” kulokhu, kodwa ungasebenzisa isisho esivamile esifanele, lapho impikiswano yokuqala ingumbhalo okufanele ukhishwe, kanti eyesibili iseli noma ububanzi okufanele buseshwe.

Shintsha izimpawu ku-Excel

Inqubo iyafana nokususa, uhlamvu olufunayo kuphela okufanele kushintshwe olunye uhlamvu (kuhlanganise nalezo ezingaphrinteki), futhi hhayi iyunithi yezinhlamvu engenalutho ku-agumenti ehambisanayo.