Kupeza munthu mumzere wa spreadsheet wa Excel

Ogwiritsa ntchito a Excel, ngakhale kuti kupeza munthu yemwe akufuna kumawoneka ngati ntchito yosavuta, nthawi zambiri samamvetsetsa momwe angachitire. Zina mwa izo ndizosavuta, zina zimakhala zovuta. Komanso, nthawi zina pamakhala zovuta kupeza zilembo monga funso kapena asterisk chifukwa amagwiritsidwa ntchito muzosefera. Lero tifotokoza njira zopezera zizindikiro zamitundu yosiyanasiyana.

Momwe mungapezere zilembo (zilembo ndi manambala) mu cell

Poyamba, tiyeni tiyese kuchita ntchito yosavuta: kudziwa kupezeka kwa zilembo m'maselo ndi kupeza amene mukufuna. Kuti muchite izi, muyenera kugwiritsa ntchito chowonjezera cha !SEMTools, chomwe mungathe kufufuza zilembo zamitundu yosiyanasiyana. Kutsatira kwa zochita ndi motere:

1. Sankhani mtundu womwe uli woyambirira ndikukopera kugawo lotsatira.
2. Kenako sankhani mtundu wachiwiri.
3. Tsegulani "!SEMTools" tabu. Pamenepo, kumanzere kwenikweni kwa chida chazida, padzakhala tabu "Penyani".
4. Pambuyo pake, tsegulani menyu "Zizindikiro".
5. Kenako menyu yowonjezera idzawonekera, yomwe muyenera kupeza chinthucho "Letters-nambala" ndikudina.

Mu makanema ojambulawa, mutha kuwona momwe mungayendere bwino kuti mupeze zilembo mu cell. Ndi ntchito yowonjezerayi, wogwiritsa ntchito amatha kudziwa ngati pali zilembo zosasindikizidwa m'maselo ena.

Momwe mungapezere manambala mu cell cell

Nthawi zina muyenera kuzindikira maselo omwe ali ndi manambala, koma ali ndi zolemba. Maselo akakhala ochuluka, zimakhala zovuta kuwazindikira. Musanagwiritse ntchito ntchitoyi, muyenera kufotokozera mawu ofunikira. Lingaliro lathu lalikulu ndi "kupeza". Izi zikutanthawuza kufufuza ngati mtundu wina wa khalidwe uli mu chingwe. Ngati inde, ikubwezera TRUE, ngati sichoncho, FALSE. Ngati, kuwonjezera pa kufufuza manambala mu selo, wogwiritsa ntchito akufuna kuchita zina, ndiye kuti mungagwiritse ntchito zigawo zina za malangizowa.

Lingaliro lachiwiri lomwe liyenera kuthetsedwa ndi manambala. Ili ndi liwu lofunikira lomwe limatanthawuza kuchuluka kwa zilembo 10 zomwe zimagwirizana ndi manambala kuyambira 0 mpaka 9. Chifukwa chake, kuti muwone kupezeka kwa manambala, wogwiritsa ntchito amayenera kuyang'ana maulendo 10. Izi zitha kuchitika pogwiritsa ntchito ntchitoyi IFkoma njira iyi ndi nthawi yambiri.

Kuti muthane ndi vutoli, mutha kugwiritsa ntchito fomula yapadera yomwe idzayang'anire zonse nthawi imodzi: =COUNT(SEARCH({1:2:3:4:5:6:7:8:9:0};A1) )>0. Ntchitoyi ili ndi mawu ofanana ndi omwe amafufuza zilembo za Cyrillic m'mawuwo.

Mutha kugwiritsanso ntchito chowonjezera chomwe chili kale ndi macro omangidwa kuti mugwire ntchitoyi. Pankhaniyi, ndikwanira kungogwiritsa ntchito tabu yapadera ya !SEMTools, yomwe iyenera kuyikidwa pagawo lowonjezera, lomwe ndi buku lathunthu lazomwe ndi zoyambirira.

Choncho, ndondomeko zomwe ziyenera kuchitidwa zimagwirizana kwathunthu ndi ndime yapitayi. Choyamba muyenera kusankha mtundu woyambirira, kukopera, kenako sankhani gawo lomwe likuwonekera ndikuyika macro molingana ndi masitepe omwe aperekedwa mu makanema ojambula.

Tiyerekeze kuti tikufunika kupeza manambala ena okha kuchokera ku zonse zomwe zaperekedwa. Kodi zimenezi zingatheke bwanji? Choyamba, tiyeni tikuwonetseni momwe mungachitire ndi !SEMTools. Kugwiritsa ntchito chida ndikosavuta. Ndikokwanira kulemba manambala onse ofunikira m'mabulaketi, ndiyeno dinani OK batani kutsimikizira. Pogwiritsa ntchito njira yomweyo, mungapeze zilembo zachilatini kapena kupeza zilembo zazikulu pamzere wa mawu.

Mutha kugwiritsanso ntchito chilinganizochi kuti mupeze manambala ofunikira m'maselo osiyanasiyana. Kuti muchite izi, muyenera kugwiritsa ntchito zosakaniza ZAKE и FUFUZANI. Ndi chithandizo chake, mutha kuzindikira osati manambala okha, komanso mawerengero athunthu: =СЧЁТ(ПОИСК({01:02:03:911:112};A1))>0.

Nthawi zina muyenera kupeza manambala olekanitsidwa ndi mipata. Pankhaniyi, amatchedwa mawu-manambala. Kuti mupeze, muyenera kugwiritsanso ntchito zida zoyenera !SEMTools. Makanema awa akuwonetsa bwino zomwe muyenera kuchita kuti muchite izi.

Momwe mungadziwire ngati cell ya Excel ili ndi zilembo zachilatini

Nthawi zambiri, ogwiritsa ntchito a Excel amasokoneza malingaliro a "Pezani" ndi "Extract", ngakhale pali kusiyana kwakukulu pakati pawo. Mawu oyamba amatanthauza kuyang'ana ngati pali munthu wina mu chingwe chalemba kapena mndandanda wa data. Kenako, lingaliro la "Extract" limatanthauza kutulutsa chilembo chomwe mukufuna kuchokera palemba ndikuchipereka ku ntchito ina kapena kulemba ku selo.

Kodi muyenera kuchita chiyani kuti mupeze zilembo zachilatini? Mwachitsanzo, mutha kugwiritsa ntchito zilembo zapadera zomwe zimathandizira kuzindikira zilembo zachingerezi ndi maso. Mwachitsanzo, izi zimapangitsa font Dubai Medium, zomwe zimapangitsa zilembo za Chingerezi kukhala zolimba mtima.

Koma choti muchite ngati pali zambiri? Pankhaniyi, kudziwa ndi diso kutsatizana anafuna makhalidwe kuti kusanthula deta sikokwanira. Pankhaniyi, muyenera kuyang'ana njira zosinthira izi. Pali njira zingapo zomwe mungachitire izi.

Kugwiritsa ntchito yapadera

Vuto lalikulu lofufuza zilembo zachilatini ndikuti pali nthawi ziwiri ndi theka kuposa manambala. Chifukwa chake, muyenera kupatsa pulogalamuyo kuzungulira kophatikiza 26, zomwe zitha kukhala zovutitsa. Koma ngati mukugwiritsa ntchito fomula yophatikizika yomwe ili pamwambayi ZAKE и FUFUZANI, ndiye lingaliro ili silikuwoneka lovuta kwambiri: =COUNT(SEARCH({“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. Njirayi imagwira ntchito nthawi zambiri. Mwachitsanzo, ngati sizingatheke kukhazikitsa ma macros oyenera omwe angachite izi mosavuta komanso mwachangu.

Munjira yomwe tafotokozazi, A1 ndiye selo lomwe chekecho chimachitikira. Chifukwa chake, muyenera kuyika zomwe zikugwirizana ndi vuto lanu. Ntchitoyi imabweza mtengo wa boolean chifukwa cha cheke. Ngati machesi apezeka, ndiye woyendetsa amabwerera WOONAngati kulibe - KUnama.

ntchito FUFUZANI sichilola kusaka kwa zilembo zamtundu uliwonse. Kuti muchite izi, muyenera kugwiritsa ntchito opareshoni KUPEZA, yomwe imagwira ntchito zomwezo, imakhala ndi mfundo zofanana, koma imakhala yovuta kwambiri. Njira ina ndikupanga fomula ili pamwambayi kukhala yotsatizana. Pankhaniyi, zikuwoneka motere:{=NKHANI(KUPEZA(CHAR(STRING(65:90)),A1))>0}.

Popeza iyi ndi njira yotsatsira, iyenera kufotokozedwa popanda mabatani. Pankhaniyi, mukalowa, muyenera kukanikiza makiyi ophatikizira Ctrl + Shift + Lowani (m'malo mongokanikiza fungulo lolowera, monga momwe zimakhalira ndi ntchito yokhazikika), pambuyo pake ma curly braces adzawonekera okha.

Ngati mukufuna kupeza zilembo za Cyrillic, kutsatizana kwa zochita kumakhala kofanana, muyenera kungoyika mndandanda wonse wa zilembo za Cyrillic ngati kusaka. =COUNT(SEARCH({“a”:”b”:”c”:”g”:”e”:”e”:”e”:”g”:”h”:”i”:”d”: "k":"l":"m":"n":"o":"p":"r":"s":"t":"y":"f":"x":" »:"h":"w":"u":"b":"s":"b":"e":"yu":"i"};A1))>0. Mukhozanso kugwiritsa ntchito SYMBOL, kuchita izi. {=COUNT(SEARCH(CHAR(STRING(192:223))),A1))>0}

Fomula iyi iyenera kulembedwa ngati mndandanda wotsatira. Chifukwa chake, muyenera kukanikiza makiyi ophatikizira Ctrl + Shift + Enter m'malo mongokanikiza fungulo lolowera. Koma pali zina zomwe sizingagwire ntchito. Choyamba muyenera kuwonetsetsa kuti chilankhulo chosasinthika pamapulogalamu omwe si a Unicode ndi . Pankhaniyi, pasakhale mavuto. Mafomuwa ali ndi zosiyana zina ndi zina. M'malo mwa zilembo 33, ndondomeko yomaliza imagwiritsa ntchito 32 yokha. Izi ndizo, sizimaganizira chilembo ё monga Cyrillic.

Pankhaniyi, mofanana ndi m'mbuyomu, kuti mufufuze zilembo zomwe mukufuna m'njira yovuta, muyenera kugwiritsa ntchito ntchitoyi. KUPEZA. Choncho, mukhoza kufufuza, mwachitsanzo, theka la zilembo zolembedwa m'malembo ang'onoang'ono ndi theka lolembedwa ndi zilembo zazikulu. Zotsutsana ndizofanana.

Momwe mungapezere mawu mu cell yomwe ili ndi Cyrillic ndi Chilatini

We can logically conclude that to search for those words that contain both Cyrillic and Latin, we need to use as what we are looking for, all the characters of the and English alphabets.

Momwe mungapezere zilembo zazikulu mu cell

Kuti mupeze zilembo zazikulu, muyenera kugwiritsa ntchito ntchitoyi KUPEZA, ndipo monga mikangano imatchula zilembo zazikulu za Cyrillic (kapena zigawo za zilembo za Chilatini, ngati mukufunikira kuzipeza) kapena zizindikiro zawo.

When searching for Cyrillic letters through codes, you need to remember that the ASCII table must first be set to . In simple words, to have localization.

Ngati mukufuna kupeza zilembo zazikulu, mosasamala kanthu za zilembo zomwe ziyenera kufufuzidwa, muyenera kugwiritsa ntchito ntchitoyi. Ocheperako и CHITSANZO… Mndandanda wa zochita uli motere:

1. Timapanga zing'onozing'ono mu cell ina.
2. Timayerekezera zotsatira ndi zoyambirira.
3. Pambuyo pake, timagwiritsa ntchito ndondomekoyi: =OSATI(YENKHANI(WAM'munsi(A1),A1))

Ngati maselowa sakufanana, ndiye kuti zilembo zina za mu selo loyambirira zinali zazikulu.

Kupeza zilembo mu Excel pogwiritsa ntchito mawu okhazikika

Mukhozanso kugwiritsa ntchito mawu okhazikika kuti mupeze zilembo. Njira yabwino yochitira izi ndi chida cha !SEMTools, chifukwa chimagwiritsa ntchito makina ambiri. Kuchuluka kwa kugwiritsa ntchito mawu okhazikika mu Excel ndikokulirapo. Tidzayang'ana poyamba pazinthu zonse FUFUZANI, M'MALO, ZOCHITIKA.

Nkhani yabwino ndiyakuti izi zitha kugwiritsidwa ntchito mu Google Sheets ndi Excel ndikusintha uku.

Yoyamba yokhazikika ntchito ndi REGEXMATCH, yomwe ingayang'ane ngati ndondomekoyi ikufanana ndi yomwe ili mu selo lina. Syntax: =REGEXMATCH(“text”;”RegEx pattern to search”). Ntchitoyi imabweretsa chimodzi mwazinthu ziwiri: zoona kapena zabodza. Zomwe zimatengera ngati machesi amawonedwa kapena ayi. Ntchito yachiwiri ndi =REGEXEXTRACT("text";"RegEx search pattern") Zimakupatsani mwayi wochotsa zilembo zomwe mukufuna ku chingwe.

Komabe, ntchitoyi ili ndi kusiyana pang'ono ndi Google Sheets. Zili ndi mfundo yakuti chotsiriziracho, ngati malembawo sanapezeke, abweretse cholakwika, pamene chowonjezerachi chimasonyeza mtengo wopanda kanthu.

Ndipo pomaliza, muyenera kugwiritsa ntchito fomula kuti musinthe mawuwo: =REGEXREPLACE("text";"RegEx search pattern";"mawu oti alowe m'malo mwa zomwe zapezeka").

Zoyenera kuchita ndi zizindikiro zomwe zapezeka

Good. Suppose we have found symbols. What can be done with them next? There are several options here on how to proceed. For example, you can delete them. For example, if we found the Latin alphabet among the Cyrillic values. You can also replace it with a similar character, only in Cyrillic (for example, large English M to M) or extract this character for use in another formula.

Kuchotsa Malembo Owonjezera mu Excel

Pali njira zambiri zochotsera zilembo zosafunikira mu Excel. Chimodzi mwazosankha ndikugwiritsa ntchito Pezani ndi Kusintha, komwe mungasinthe mawonekedwe omwe mukufuna kuchotsa ndi chingwe chopanda kanthu "". Mutha kugwiritsa ntchito mawu omwewo omwe amagwiritsidwa ntchito m'malo mwa zilembo zomwe zapezeka.

Chotsani zilembo zapadera mu Excel

Mungagwiritse ntchito ntchito ya "Pezani" pa izi, koma mungagwiritsenso ntchito mawu oyenerera nthawi zonse, pamene mkangano woyamba ndi malemba omwe ayenera kuchotsedwa, ndipo chachiwiri ndi selo kapena mzere woti mufufuze.

Sinthani zilembo mu Excel

Ndondomekoyi ndi yofanana ndi kufufutidwa, khalidwe lofunidwa ndiloyenera kusinthidwa ndi khalidwe lina (kuphatikizapo osasindikizidwa), ndipo osati chingwe chopanda kanthu pamtsutso wofanana.