Kutsvaga hunhu muExcel spreadsheet row

Vashandisi veExcel, zvisinei nekuti kuwana hunhu hwaunoda kunoratidzika senge riri nyore basa, kazhinji havanzwisise maitirwo azvo. Zvimwe zvacho zviri nyore, zvimwe zvakaoma. Zvakare, dzimwe nguva kune matambudziko ekutsvaga mavara akadai semubvunzo kana asterisk nekuda kwekuti anoshandiswa mumasefa. Nhasi tichatsanangura nzira dzekuwana zviratidzo zvemarudzi akasiyana-siyana.

Mawaniro emavara mavara (mavara nenhamba) muchitokisi

Kutanga, ngatiedzei kuita basa rakareruka: tarisa kuvepo kwemavara mavara mumaseru uye tsvaga iyo yaunoda. Kuti uite izvi, unofanirwa kushandisa !SEMTools add-on, iyo yaunogona kutsvaga nayo mavara emhando dzakasiyana. Kutevedzana kwezviito kunotevera:

1. Sarudza huwandu hwekutanga uye ukope kune iyo inotevera column.
2. Zvadaro sarudza chikamu chechipiri.
3. Vhura “!SEMTools” tab. Ikoko, kurutivi rwekuruboshwe rwechishandiso, pachava ne "Detect" tab.
4. Mushure meizvozvo, vhura iyo "Symbols" menyu.
5. Zvadaro imwe menyu ichaonekwa, iyo yaunoda kuwana chinhu "Tsamba-nhamba" uye tinya pairi.

Mune ino animation, unogona kuona chaizvo mafambiro nemazvo kuti uwane mavara emavara muchitokisi. Nebasa rekuwedzera iri, mushandisi anogona kuona kana paine mavara asina kudhindwa mune mamwe maseru.

Mawaniro enhamba musero retafura

Dzimwe nguva unofanirwa kuona maseru ane manhamba, asi ane mavara. Kana masero akadaro akawanda, zvinogona kuoma kuaziva. Usati waita basa iri, unofanirwa kutsanangura mamwe mazwi ekutanga. Pfungwa yedu huru ndeye "discover". Izvi zvinoreva kutarisa kana imwe mhando yehunhu iri mutambo. Kana hongu, inodzorera TRUE, kana zvisiri, FALSE. Kana, kunze kwekutsvaga nhamba musero, mushandisi anoda kuita zvimwe zviito, saka unogona kushandisa zvimwe zvikamu zvekuraira uku.

Pfungwa yechipiri inoda kudimburwa inhamba. Iri ishoko rakakosha rinoreva zvakawanda semabhii gumi anoenderana nenhamba kubva pa10 kusvika 0. Saizvozvo, kuti utarise kuvepo kwenhamba, mushandisi anofanira kutarisa huwandu hwegumi. Izvi zvinogona kuitwa uchishandisa basa IFasi nzira iyi inotora nguva yakawanda.

Kugadzirisa dambudziko iri, unogona kushandisa fomula yakakosha ichaita cheki dzese nekufamba kumwe chete: =COUNT(TSVAGA({1:2:3:4:5:6:7:8:9:0};A1) )>0 . Iri basa rine mutsara wakafanana neunotsvaga mavara echiCyrillic muzvinyorwa.

Iwe unogona zvakare kushandisa yekuwedzera-mukati iyo yatova neyakavakirwa-mukati macro kuitira kuti uite basa iri. Muchiitiko ichi, zvakakwana kungoshandisa yakakosha !SEMTools tab, iyo inofanira kuiswa pane imwe mbiru, inova kopi yakakwana yeiyo yepakutanga.

Naizvozvo, seti yematanho anotorwa anoenderana zvizere nendima yapfuura. Iwe unofanirwa kutanga wasarudza iyo yekutanga renji, woikopa, uye wozosarudza iyo inooneka koramu uye isa macro kwairi zvichienderana nekutevedzana kwematanho akapihwa mune ino animation.

Ngatitii tinoda kuwana nhamba dzakati chete kubva kune dzese dzakapihwa. Izvi zvingaitwa sei? Chekutanga, ngatikuratidzei maitirwo acho ne!SEMTools. Kushandisa chishandiso chiri nyore. Zvakakwana kunyora pasi nhamba dzese dzinodiwa mumabhuraketi, uye wobva wadzvanya bhatani raOK kuti usimbise. Uchishandisa nzira imwe cheteyo, unogona kuwana arufabheti yechiLatin kana kuwana mavara makuru mumutsetse wezvinyorwa.

Iwe unogona zvakare kushandisa fomula kuti uwane nhamba dzinodiwa muhuwandu hwemasero. Kuti uite izvi, unofanirwa kushandisa musanganiswa wemabasa Check и SEARCH. Nerubatsiro rwayo, haugone kuona kwete chete nhamba dzemunhu, asiwo nhamba dzese dzakateerana: =СЧЁТ(ПОИСК({01:02:03:911:112};A1))>0.

Dzimwe nguva unoda kuwana nhamba dzakaparadzaniswa nenzvimbo. Muchiitiko ichi, vanonzi mazwi-nhamba. Kuti uzviwane, unofanira kushandisawo maturusi akakodzera !SEMTools. Iyi animation inoratidza zvakajeka zviito zvaunoda kuita kuti uite izvi.

Nzira yekuziva kana sero reExcel rine mavara echiLatin

Kazhinji, vashandisi veExcel vanovhiringidza pfungwa dze "Tsvaga" uye "Kubvisa", kunyangwe paine musiyano mukuru pakati pavo. Chirevo chekutanga chinoreva kutarisa kana paine humwe hunhu murunyoro tambo kana data renji. Izvi zvinorevawo kuburitsa mavara anodiwa kubva muchinyorwa wochipfuudza kune rimwe basa kana kunyorera sero.

Chii chinoda kuitwa kuti uwane mavara echiLatin? Semuenzaniso, unogona kushandisa akakosha mafonti anozoita kuti zvikwanise kuziva mavara echiRungu neziso. Semuenzaniso, izvi zvinogadzira font Dubai Medium, izvo zvinoita kuti mavara echiRungu ashinge.

Asi chii chaunofanira kuita kana pane data yakawanda? Muchiitiko ichi, kusarudza neziso kutevedzana kunodiwa kwehunhu kuitira kuongorora iyo data hakuna kukwana. Muchiitiko ichi, iwe unofanirwa kutsvaga nzira dzekuita otomatiki iyi maitiro. Pane nzira dzakawanda dzaungaita izvi.

Kushandisa basa rinokosha

Dambudziko guru rekutsvaga mavara echiLatin nderekuti pane kaviri nehafu kakawanda kupfuura nhamba. Naizvozvo, iwe unofanirwa kupa chirongwa ichi loop ine 26 iterations, izvo zvinogona kunetsa zvakanyanya. Asi kana uchishandisa array formula inosanganisira mabasa ari pamusoro Check и SEARCH, saka iyi pfungwa haiite seyakaomarara: =COUNT(TSVAGA({“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. Iyi fomula inoshanda nemazvo kune mazhinji mamiriro. Semuenzaniso, kana zvisingaite kuisa macros akakodzera ayo anogona kuita izvi zviri nyore uye nekukurumidza.

Mune fomula yakatsanangurwa pamusoro, A1 ndiyo sero umo cheki inoitwa. Naizvozvo, iwe unofanirwa kuisa iyo inokodzera mamiriro ako. Iri basa rinodzosa kukosha kwe boolean semhedzisiro yecheki. Kana mutambo ukawanikwa, ipapo mushandisi anodzoka TRUEkana vasipo - KUNYEPA.

basa SEARCH haitenderi kutsvaga kusinganzwisisike kwemavara. Kuti aite izvi, unofanira kushandisa opareta KUWANA, iyo inoita maoparesheni mamwe chete, ine nharo dzakafanana, chete inobata nyaya. Imwe nzira ndeyekuita iyo fomula iri pamusoro ive yakarongwa fomula. Muchiitiko ichi, zvicharatidzika sezvizvi:{=COUNT(TSVAGA(CHAR(STRING(65:90))),A1))>0}.

Sezvo iyi iri fomula yakarongwa, inofanirwa kutsanangurwa pasina maparentheses. Muchiitiko ichi, mushure mekupinda, iwe unofanirwa kudzvanya kiyi musanganiswa Ctrl + Shift + Enter (panzvimbo yekungotsikirira kiyi yekupinda, sezvazvinoita nebasa rinogara richiitwa), mushure meizvozvo mabhureki akamonana achaonekwa ivo pachavo.

Kana iwe uchida kutsvaga iyo Cyrillic alphabet, kutevedzana kwezviito kwakafanana, iwe chete unofanirwa kuseta iyo yese kutevedzana kweCyrillic mavara seyekutsvaga. =COUNT(TSVAGA({“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. Unogonawo kushandisa basa SYMBOL, kuita izvi. {=COUNT(TSVAGA(CHAR(STRING(192:223))),A1))>0}

Iyi fomula inofanirwa kunyorwa seyakarongwa fomula. Naizvozvo, iwe unofanirwa kudzvanya kiyi musanganiswa Ctrl + Shift + Enter pane kungodzvanya kiyi yekupinda. Asi pane zvimwe zvakasarudzika apo chimiro ichi chisingashande. Iwe unofanirwa kutanga waona kuti mutauro wakasarudzika wezvisiri-Unicode zvirongwa . Muchiitiko ichi, hapafaniri kuva nematambudziko. Aya mafomula ane mimwe misiyano kubva kune imwe neimwe. Panzvimbo pemavara makumi matatu nematatu, fomu yekupedzisira inoshandisa 33 chete. Ndiko kuti, haifungi nezvetsamba ё seCyrillic.

Muchiitiko ichi, sezvakaita mune yakapfuura, kutsvaga mavara aunoda nenzira inobata nyaya, unofanira kushandisa basa racho. KUWANA. Naizvozvo, unogona kutsvaga, semuenzaniso, hafu yearufabheti yakanyorwa nemavara madiki nehafu yakanyorwa nemavara makuru. Nharo dzakafanana.

Maitiro ekutsvaga mazwi muchitokisi ane Cyrillic neLatin

Tinogona kugumisa zvine musoro kuti kutsvaga mazwi aya ane ese ari maviri echiCyrillic nechiLatin, isu tinofanirwa kushandisa sezvatiri kutsvaga, mavara ese emaarufabheti echiRungu.

Mawaniro emavara makuru muchitokisi

Kuti uwane mavara makuru, unofanirwa kushandisa basa racho KUWANA, uye senharo dzinotsanangura mavara makuru eCyrillic (kana zvinhu zvechiLatin alphabet, kana iwe uchida kuzviwana) kana macode avo.

Paunenge uchitsvaga mavara eCyrillic kuburikidza nemakodhi, iwe unofanirwa kuyeuka kuti iyo ASCII tafura inofanira kutanga yaiswa . Nemashoko akapfava, kuva nenzvimbo.

Kana iwe uchida kuwana chero mavara makuru, zvisinei nearufabheti yaanofanira kutsvaga, unofanirwa kushandisa mabasa. CHIDUKU и BASA… Kutevedzana kwezviito kunotevera:

1. Isu tinoita madiki mashoma mune imwe sero yakasiyana.
2. Isu tinoenzanisa mhedzisiro neyekutanga.
3. Mushure meizvozvo, isu tinoshandisa inotevera formula: =Kwete(CHAIKO(ZASI(A1),A1))

Kana masero aya asingaenderane, izvi zvinoratidza kuti mamwe mavara ari musero rekutanga anga ari makuru.

Kutsvaga mavara muExcel uchishandisa akajairika mataurirwo

Unogonawo kushandisa mataurirwo enguva dzose kutsvaga mavara. Nzira yakanakisa yekuita izvi ndeye !SEMTools chishandiso, sezvo ichigadzira yakawanda yemaitiro ekuishandisa. Huwandu hwekushandisa matauriro enguva dzose muExcel hwakakura. Tichatarisa kutanga pane zvose pamabasa TSVAGA, REPLACE, ZVEZVO.

Nhau dzakanaka ndedzekuti mabasa aya anogona kushandiswa mune ese maGoogle Sheets neExcel negadziriro iyi.

Basa rekutanga renguva dzose ndiro REGEXMATCH, iyo inogona kutarisa kana iyi pateni yakafanana neiri mune imwe sero. Syntax: =REGEXMATCH("chinyorwa";"RegEx pateni yekutsvaga"). Iri basa rinodzosa chimwe chezviviri zvakakosha: chokwadi kana nhema. Izvo chaizvo zvinoenderana nekuti mutambo unocherechedzwa here kana kuti kwete. Basa rechipiri ndiro =REGEXEXTRACT("chinyorwa";"RegEx yekutsvaga maitiro") Inokubvumira kubvisa mavara anodiwa kubva kune tambo.

Nekudaro, basa iri rine misiyano mishoma kubva kuGoogle Sheets. Inosanganisira mukuti iyo yekupedzisira, kana iyo yakatsanangurwa rugwaro ikasawanikwa, inodzosa kukanganisa, nepo iyi yekuwedzera inoratidza chete kukosha kusina chinhu.

Uye pakupedzisira, iwe unofanirwa kushandisa iyi fomula kutsiva iwo mavara: =REGEXREPLACE("chinyorwa";"RegEx yekutsvaga patani";"mavara ekutsiva awanikwa").

Zvekuita nezviratidzo zvakawanikwa

Kugona. Ngatitii tawana zviratidzo. Chii chingaitwa navo chinotevera? Pane zvakawanda zvingasarudzwa pano kuti ungaenderera sei. Semuenzaniso, unogona kudzidzima. Semuenzaniso, kana takawana mavara echiLatin pakati pemaitiro eCyrillic. Iwe unogona zvakare kuchitsiva nechimiro chakafanana, chete muCyrillic (semuenzaniso, hombe Chirungu M kusvika M) kana kubvisa uyu hunhu kuti ushandiswe mune imwe fomula.

Kubvisa Mamwe Mavara muExcel

Pane nzira dzakawanda dzekubvisa mavara asingadiwe muExcel. Imwe yesarudzo ndeye kushandisa Tsvaga uye Replace basa, kwaunogona kutsiva chimiro chaunoda kubvisa netambo isina chinhu "". Unogona kushandisa matauriro akafanana anoshandiswa kutsiva anowanikwa.

Bvisa mavara chaiwo muExcel

Iwe unogona kushandisa "Tsvaga" basa reizvi, asi iwe unogona zvakare kushandisa yakakodzera nguva yekutaura, apo nharo yekutanga ndiyo chinyorwa chinofanira kutorwa, uye chechipiri ndiyo sero kana rondedzero yekutsvaga.

Shandura zviratidzo muExcel

Maitirwo acho akafanana nekudzimwa, hunhu hunodiwa chete ndihwo hunofanira kutsiviwa neimwe hunhu (kusanganisira isingadhindike), uye kwete tambo isina chinhu munharo inoenderana.