Neman haruffa a jeren maƙunsar rubutu na Excel

Masu amfani da Excel, duk da cewa gano halin da ake so yana kama da aiki mai sauƙi, sau da yawa ba sa fahimtar yadda ake yin shi. Wasu daga cikinsu sun fi sauƙi, wasu sun fi wuya. Har ila yau, a wasu lokuta ana samun matsaloli tare da gano haruffa kamar alamar tambaya ko alamar alama saboda ana amfani da su a cikin tacewa. A yau za mu bayyana hanyoyin samun alamomin nau'ikan iri daban-daban.

Yadda ake nemo haruffan rubutu (haruffa da lambobi) a cikin tantanin halitta

Da farko, bari mu yi ƙoƙarin yin aiki mafi sauƙi: ƙayyade kasancewar haruffan rubutu a cikin sel kuma nemo wanda kuke buƙata. Don yin wannan, kuna buƙatar amfani da !SEMTools add-on, wanda da shi zaku iya nemo haruffa iri-iri. Jerin ayyuka kamar haka:

1. Zaɓi kewayon wanda shine ainihin kuma kwafa shi zuwa shafi na gaba.
2. Sannan zaɓi zango na biyu.
3. Bude shafin "!SEMTools" A can, a gefen hagu na kayan aiki, za a sami shafin "Gano".
4. Bayan haka, buɗe menu na "Symbols".
5. Sa'an nan ƙarin menu zai bayyana, a cikin abin da kuke buƙatar nemo abu "Haruffa-lambobi" kuma danna kan shi.

A cikin wannan motsin rai, zaku iya ganin ainihin yadda ake ci gaba daidai don nemo haruffan rubutu a cikin tantanin halitta. Tare da wannan aikin ƙarawa, mai amfani zai iya tantance ko akwai haruffa marasa bugawa a wasu sel.

Yadda ake nemo lambobi a cikin tantanin halitta

Wani lokaci kuna buƙatar gano sel waɗanda ke da lambobi, amma suna tare da rubutu. Lokacin da irin waɗannan sel suna da yawa, yana iya zama da wahala a gano su. Kafin aiwatar da wannan aikin, kuna buƙatar ayyana wasu ƙa'idodi na asali. Babban manufar mu shine "gano". Wannan yana nufin bincika ko wani nau'in hali yana cikin kirtani. Idan eh, zai dawo GASKIYA, idan ba haka ba, KARYA. Idan, ban da neman lambobi a cikin tantanin halitta, mai amfani yana son yin wasu ayyuka, to zaku iya amfani da ƙarin sassan wannan umarni.

Tunani na biyu da ke buƙatar tarwatsa shi ne lambobi. Wannan kalma ce mai mahimmanci wanda ke nufin adadin haruffa 10 waɗanda suka dace da lambobi daga 0 zuwa 9. Saboda haka, don bincika kasancewar lambobi, mai amfani yana buƙatar bincika kewayon sau 10. Ana iya yin wannan ta amfani da aikin IFamma wannan hanyar tana ɗaukar lokaci sosai.

Don magance wannan matsalar, kuna iya amfani da wata dabara ta musamman wacce za ta yi duk cak ɗin a tafi ɗaya: = COUNT(NENE({1:2:3:4:5:6:7:8:9:0};A1)* >>0 . Wannan aikin yana da ma'auni ɗaya da wanda ke neman haruffan Cyrillic a cikin rubutu.

Hakanan zaka iya amfani da ƙari wanda ya riga yana da ginannen macro don yin wannan aikin. A wannan yanayin, ya isa kawai don amfani da shafin SEMTools na musamman, wanda dole ne a yi amfani da shi akan ƙarin shafi, wanda shine cikakken kwafin wanda shine ainihin.

Don haka, saitin matakan da za a ɗauka sun yi daidai da sakin layi na baya. Dole ne ku fara zaɓar kewayon asali, kwafi shi, sannan zaɓi shafi da ya bayyana sannan ku yi amfani da macro akansa bisa jerin matakan da aka bayar a cikin wannan motsin rai.

A ce muna buƙatar nemo wasu takamaiman lambobi daga duk abin da aka bayar. Ta yaya za a yi haka? Da farko, bari mu nuna muku yadda ake yi da !SEMTools. Yin amfani da kayan aiki yana da sauƙi. Ya isa a rubuta duk lambobi masu mahimmanci a cikin maɓalli, sannan danna maɓallin Ok don tabbatarwa. Ta amfani da wannan hanyar, zaku iya nemo haruffan Latin ko nemo manyan haruffa a cikin layin rubutu.

Hakanan zaka iya amfani da dabarar don nemo lambobin da ake buƙata a cikin kewayon sel. Don yin wannan, kuna buƙatar amfani da haɗin ayyuka duba и SEARCH. Tare da taimakonsa, zaku iya gano ba kawai lambobi ɗaya ba, har ma da jerin lambobi duka: =СЧЁТ(ПОИСК({01:02:03:911:112};A1))>0.

Wani lokaci kuna buƙatar nemo lambobi waɗanda ke raba su ta sarari. A wannan yanayin, ana kiran su kalmomi-lambobi. Don nemo su, dole ne kuma ku yi amfani da kayan aikin da suka dace !SEMTools. Wannan raye-raye yana nuna a sarari abubuwan da kuke buƙatar aiwatarwa don yin wannan.

Yadda ake gano idan tantanin halitta na Excel ya ƙunshi haruffan latin

Sau da yawa, masu amfani da Excel suna rikitar da ra'ayoyin "Nemo" da "Extract", kodayake akwai babban bambanci a tsakanin su. Kalma ta farko tana nufin bincika idan akwai takamammen hali a cikin layin rubutu ko kewayon bayanai. Bi da bi, manufar “Extract” na nufin ciro abin da ake so daga cikin rubutun a wuce shi zuwa wani aiki ko rubuta shi zuwa tantanin halitta.

Menene ya kamata a yi don gano haruffan Latin? Misali, zaku iya amfani da haruffa na musamman waɗanda zasu ba da damar tantance haruffan Ingilishi da ido. Misali, wannan yana sanya font Dubai Medium, wanda ke sa haruffan Ingilishi ƙarfin hali.

Amma menene za a yi idan akwai bayanai da yawa? A wannan yanayin, tantancewa ta ido jerin abubuwan da ake so don tantance bayanan bai isa ba. A wannan yanayin, kuna buƙatar nemo hanyoyin sarrafa wannan tsari. Akwai hanyoyi da yawa yadda zaku iya yin wannan.

Amfani da aiki na musamman

Babban matsalar neman haruffan Latin shine cewa akwai sau biyu da rabi fiye da lambobi. Don haka, kuna buƙatar ba shirin madauki wanda ya ƙunshi juzu'i 26, wanda zai iya zama damuwa sosai. Amma idan ana amfani da dabarar tsararru mai kunshe da ayyukan da ke sama duba и SEARCH, to wannan ra'ayin bai yi kama da rikitarwa ba: = COUNT(NENE ({"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. Wannan dabarar tana aiki da kyau ga yawancin yanayi. Misali, idan ba zai yiwu a shigar da macro masu dacewa ba wanda zai iya yin hakan cikin sauƙi da sauri.

A cikin dabarar da aka bayyana a sama, A1 ita ce tantanin halitta wanda ake yin rajistan. Dangane da haka, kuna buƙatar sanya wanda ya dace da yanayin ku. Wannan aikin yana dawo da ƙimar boolean sakamakon cak ɗin. Idan an sami ashana, to mai aiki zai dawo GASKIYAidan babu su - KARYA.

aiki SEARCH baya bada izinin bincike-hannun hali don haruffa. Don yin wannan, kuna buƙatar amfani da afareta DOMIN SAMU, wanda ke yin ayyuka iri ɗaya, yana da muhawara iri ɗaya, kawai yana da hankali. Wata hanya kuma ita ce sanya dabarar da ke sama ta zama dabarar tsararru. A wannan yanayin, zai kasance kamar haka:{= COUNT(NENE (CHAR(STRING(65:90))),A1))>0}.

Tunda wannan tsarin tsararru ne, dole ne a fayyace shi ba tare da baka ba. A wannan yanayin, bayan shigar, dole ne ka danna haɗin maɓalli Ctrl + Shift + Shigar (maimakon kawai danna maɓallin shigar, kamar yadda yake tare da aiki na yau da kullun), bayan haka takalmin gyaran kafa zai bayyana kansu.

Idan kana buƙatar nemo haruffan Cyrillic, jerin ayyuka iri ɗaya ne, kawai kuna buƙatar saita jeri na haruffan Cyrillic azaman kewayon bincike. = COUNT(NENE ({"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. Hakanan zaka iya amfani da aikin SYMBOL, don yin wannan. {= COUNT(NENE (SARUWA (STRING(192:223))),A1))>0}

Dole ne a rubuta wannan dabarar azaman dabarar tsararru. Don haka, kuna buƙatar danna haɗin maɓalli Ctrl + Shift + Shiga maimakon kawai danna maɓallin shigar. Amma akwai wasu keɓancewa inda wannan fasalin ba zai yi aiki ba. Dole ne ka fara tabbatar da cewa tsohowar harshe don shirye-shiryen da ba Unicode ba shine . A wannan yanayin, bai kamata a sami matsala ba. Waɗannan ƙa'idodi suna da ɗan bambanci daga juna. Maimakon haruffa 33, tsari na ƙarshe yana amfani da kawai 32. Wato, baya la'akari da harafin ё a matsayin Cyrillic.

A wannan yanayin, kamar yadda yake a baya, don bincika haruffan da ake so ta hanyar da ta dace, dole ne ku yi amfani da aikin. DOMIN SAMU. Don haka, zaku iya bincika, alal misali, rabin haruffan da aka rubuta da ƙananan haruffa da rabi waɗanda aka rubuta da manyan haruffa. Hujja iri daya ce.

Yadda ake nemo kalmomi a cikin tantanin halitta masu ɗauke da Cyrillic da Latin

Za mu iya ƙarasa da hankali cewa don neman waɗannan kalmomin da suka ƙunshi duka Cyrillic da Latin, muna buƙatar amfani da su azaman abin da muke nema, duk haruffan haruffan Ingilishi da na Ingilishi.

Yadda ake nemo manyan haruffa a cikin tantanin halitta

Don nemo manyan haruffa, kuna buƙatar amfani da aikin DOMIN SAMU, kuma kamar yadda gardama ke ƙayyade manyan haruffa Cyrillic (ko abubuwan haruffan Latin, idan kuna buƙatar nemo su) ko lambobin su.

Lokacin neman haruffan Cyrillic ta lambobi, kuna buƙatar tuna cewa tebur ASCII dole ne a fara saita shi zuwa . A cikin kalmomi masu sauƙi, don samun wuri.

Idan kuna buƙatar nemo kowane babban haruffa, ba tare da la'akari da haruffan da ake buƙatar bincika su ba, kuna buƙatar amfani da ayyukan. KARANTA и SAURARA… Jerin ayyuka kamar haka:

1. Muna yin ƙananan ƙima a cikin tantanin halitta daban.
2. Muna kwatanta sakamakon da na asali.
3. Bayan haka, muna amfani da tsari mai zuwa: = BA (MAGANIN (KASANCEWA (A1),A1))

Idan waɗannan sel ba su yi daidai ba, wannan yana nuna cewa wasu haruffa a cikin tantanin halitta na asali sun kasance a cikin manya.

Nemo haruffa a cikin Excel ta amfani da maganganu na yau da kullun

Hakanan zaka iya amfani da maganganun yau da kullun don nemo haruffa. Hanya mafi kyau don yin wannan ita ce tare da kayan aikin SEMTools, kamar yadda yake sarrafa yawancin tsarin amfani da su. Bakan na yin amfani da maganganu na yau da kullun a cikin Excel yana da faɗi sosai. Da farko za mu mai da hankali kan ayyukan BINCIKE, MUSA, Ƙaddamarwa.

Labari mai dadi shine cewa ana iya amfani da waɗannan ayyuka a cikin Google Sheets da Excel tare da wannan saitin.

Aikin yau da kullun na farko shine REGEXMATCH, wanda zai iya bincika idan wannan tsarin yayi kama da wanda ke cikin wani tantanin halitta. Daidaitawa: = REGEXMATCH ("rubutu"; "Tsarin RegEx don bincika"). Wannan aikin yana dawo da ɗaya daga cikin ƙima biyu: gaskiya ko ƙarya. Menene ainihin ya dogara da ko an lura da wasa ko a'a. Aiki na biyu shine = REGEXEXTRACT ("rubutu"; "Tsarin bincike na RegEx") Yana ba ku damar cire haruffan da ake so daga kirtani.

Koyaya, wannan aikin yana da ɗan bambance-bambance daga Google Sheets. Ya ƙunshi gaskiyar cewa ƙarshen, idan ba a sami ƙayyadadden rubutu ba, dawo da kuskure, yayin da wannan ƙarawa yana nuna ƙimar fanko kawai.

Kuma a ƙarshe, kuna buƙatar amfani da wannan dabarar don maye gurbin rubutun: = REGEXREPLACE ("rubutu"; "Tsarin bincike na RegEx";"rubutu don maye gurbin abin da aka samo").

Abin da za a yi da alamun da aka samo

Yayi kyau. A ce mun sami alamomi. Me za a iya yi da su gaba? Akwai zaɓuɓɓuka da yawa anan kan yadda ake ci gaba. Misali, zaku iya share su. Misali, idan muka sami haruffan Latin a cikin ƙimar Cyrillic. Hakanan zaka iya maye gurbin shi da irin wannan hali, kawai a cikin Cyrillic (misali, babban Ingilishi M zuwa M) ko cire wannan harafin don amfani da shi a wata dabara.

Cire Karin Haruffa a cikin Excel

Akwai hanyoyi da yawa don cire haruffan da ba'a so a cikin Excel. Ɗaya daga cikin zaɓuɓɓukan shine a yi amfani da aikin Nemo da Sauya, inda za ku iya maye gurbin halin da kuke son cirewa tare da fanko "". Kuna iya amfani da maganganun yau da kullun waɗanda ake amfani da su don maye gurbin halin da aka samo.

Cire takamaiman haruffa a cikin Excel

Kuna iya amfani da aikin "Find" don wannan, amma kuma kuna iya amfani da kalmar da ta dace ta yau da kullun, inda hujja ta farko ita ce rubutun da za a ciro, na biyu kuma shine tantanin halitta ko kewayon da za a bincika.

Canza alamomi a cikin Excel

Hanyar iri ɗaya ce da gogewa, kawai halin da ake so dole ne a maye gurbinsa da wani hali (ciki har da waɗanda ba za a iya bugawa ba), kuma ba kirtani mara komai ba a cikin hujjar da ta dace.