Okuqukethwe
Izolo kumarathon 30 Excel isebenza ngezinsuku ezingama-30 siqaphele izinhlobo zamaphutha sisebenzisa umsebenzi ERROR.TYPE (IPHUTHA UHLOBO) futhi yenze isiqiniseko sokuthi ingaba usizo kakhulu ekulungiseni amaphutha ku-Excel.
Ngosuku lwe-18 lwemarathon, sizonikela ngesifundo somsebenzi SEARCH (SESHA). Ibheka uhlamvu (noma izinhlamvu) ngaphakathi kweyunithi yezinhlamvu yombhalo bese ibika lapho itholwe khona. Sizophinde sibheke ukuthi singabhekana kanjani nezimo lapho lo msebenzi uphonsa iphutha.
Ngakho-ke, ake sibhekisise ithiyori nezibonelo ezingokoqobo zomsebenzi SEARCH (SESHA). Uma unamaqhinga noma izibonelo zokusebenza nalo msebenzi, sicela wabelane ngazo kumazwana.
Umsebenzi 18: SESHA
Umsebenzi SEARCH (SESHA) icinga iyunithi yezinhlamvu yombhalo phakathi kolunye uchungechunge lombhalo, futhi uma itholakele, ibika indawo yayo.
Ngingawusebenzisa kanjani umsebenzi othi SEARCH?
Umsebenzi SEARCH (SESHA) icinga iyunithi yezinhlamvu yombhalo phakathi kolunye uchungechunge lombhalo. Angakwazi:
- Thola iyunithi yezinhlamvu yombhalo ngaphakathi kwenye iyunithi yezinhlamvu yombhalo (ikesi alizweli).
- Sebenzisa izinhlamvu ze-wildcard kusesho lwakho.
- Nquma indawo yokuqala embhalweni obukiwe.
SEARCH Syntax
Umsebenzi SEARCH (SEARCH) inala mazwi alandelayo:
SEARCH(find_text,within_text,[start_num])
ПОИСК(искомый_текст;текст_для_поиска;[нач_позиция])
- thola_umbhalo (search_text) umbhalo owufunayo.
- ngaphakathi_kombhalo (text_for_search) – iyunithi yezinhlamvu yombhalo lapho ukusesha kwenziwa khona.
- inombolo_yokuqala (start_position) - uma kungashiwongo, ukusesha kuzoqala kuhlamvu lokuqala.
Izicupho SESHA (SESHA)
Umsebenzi SEARCH (SESHA) izobuyisela indawo yeyunithi yezinhlamvu yokuqala efanayo, icala alizwani. Uma udinga ukusesha okubucayi, ungasebenzisa umsebenzi THOLA (THOLA), esizohlangana nayo ngokuhamba kwesikhathi emjahweni 30 Excel isebenza ngezinsuku ezingama-30.
Isibonelo 1: Ukuthola umbhalo kuyunithi yezinhlamvu
Sebenzisa umsebenzi SEARCH (SESHA) ukuze uthole umbhalo othile ngaphakathi kweyunithi yezinhlamvu yombhalo. Kulesi sibonelo, sizobe sibheka uhlamvu olulodwa (oluthayiphe kuseli B5) ngaphakathi kweyunithi yezinhlamvu yombhalo etholakala kuseli B2.
=SEARCH(B5,B2)
=ПОИСК(B5;B2)
Uma umbhalo utholakala, umsebenzi SEARCH (SEARCH) izobuyisela inombolo yendawo yohlamvu lwayo lokuqala kuyunithi yezinhlamvu yombhalo. Uma ingatholakali, umphumela uzoba umlayezo wephutha #INANI! (#SO).
Uma umphumela uyiphutha, ungasebenzisa umsebenzi IFERROR (IFERROR) ukuze esikhundleni sokwenza umsebenzi SEARCH (SESHA) bonisa umlayezo ohambisanayo. Umsebenzi IFERROR (IFERROR) yethulwe ku-Excel kusukela kunguqulo 2007. Ezinguqulweni zangaphambili, umphumela ofanayo wawungatholakala kusetshenziswa IF (IF) kanye ISERROR (EOSHIBKA).
=IFERROR(SEARCH(B5,B2),"Not Found")
=ЕСЛИОШИБКА(ПОИСК(B5;B2);"Not Found")
Isibonelo sesi-2: Ukusebenzisa amakhadi asendle nge-SEA
Enye indlela yokuhlola umphumela ubuyile SEARCH (SESHA), ngephutha – sebenzisa umsebenzi ISNUMBER (ISNUMBER). Uma intambo itholakala, umphumela SEARCH (SESHA) kuzoba inombolo, okusho umsebenzi ISNUMBER (ISNUMBER) izobuya TRUE. Uma umbhalo ungatholakali, ke SEARCH (SESHA) izobika iphutha, futhi ISNUMBER (ISNUMBER) izobuyisela okuthi FALSE.
Inani lempikiswano thola_umbhalo (search_text) ungasebenzisa izinhlamvu ze-wildcard. Uphawu * (inkanyezi) ingena esikhundleni sanoma iyiphi inombolo yezinhlamvu noma lutho, futhi ? (uphawu lombuzo) luthatha indawo yanoma yiluphi uhlamvu olulodwa.
Esibonelweni sethu, uhlamvu lwe-wildcard lusetshenziswa *, ngakho-ke imishwana CENTRAL, CENTER, kanye ne-CENTER izotholakala emagameni emigwaqo.
=ISNUMBER(SEARCH($E$2,B3))
=ЕЧИСЛО(ПОИСК($E$2;B3))
Isibonelo sesi-3: Ukucacisa indawo yokuqala yokuthi SESHA (SESHA)
Uma sibhala izimpawu ezimbili zokususa (ukuphika kabili) phambi komsebenzi ISNUMBER (ISNUMBER), izobuyisela amanani 1/0 esikhundleni sethi IQINISO/AMANGA (IQINISO/AMANGA). Okulandelayo, umsebenzi ISUM (SUM) kuseli E2 izobala inani eliphelele lamarekhodi lapho umbhalo wosesho utholwe khona.
Esibonelweni esilandelayo, ikholamu B ibonisa:
Igama ledolobha | Umsebenzi
Umsebenzi wethu ukuthola amaphrofeshinali aqukethe iyunithi yezinhlamvu yombhalo efakwe kuseli E1. Ifomula kuseli C2 ingaba:
=--ISNUMBER(SEARCH($E$1,B2))
=--ЕЧИСЛО(ПОИСК($E$1;B2))
Le fomula ithole imigqa equkethe igama elithi "ibhange", kodwa kwelinye lawo leli gama alitholakali egameni lomsebenzi, kodwa egameni lomuzi. Lokhu akusifanele!
Igama ledolobha ngalinye lilandelwa uphawu | (ibha eqondile), ngakho-ke, sisebenzisa umsebenzi SEARCH (SESHA), singathola indawo yalo mlingiswa. Indawo yayo ingacaciswa njengevelu ye-agumenti inombolo_yokuqala (start_position) kumsebenzi othi “main” SEARCH (SESHA). Ngenxa yalokho, amagama amadolobha azoshaywa indiva usesho.
Manje ifomula ehloliwe futhi elungisiwe izobala kuphela leyo migqa equkethe igama elithi "ibhange" egameni lomsebenzi:
=--ISNUMBER(SEARCH($E$1,B2,SEARCH("|",B2)))
=--ЕЧИСЛО(ПОИСК($E$1;B2;ПОИСК("|";B2)))