txheem
Nag hmo hauv marathon 30 Excel ua haujlwm hauv 30 hnub peb tau lees paub cov hom kev ua yuam kev uas siv cov haujlwm ERROR.TYPE (YOG YIM) thiab ua kom paub tseeb tias nws tuaj yeem muaj txiaj ntsig zoo rau kev kho qhov yuam kev hauv Excel.
Hnub 18 ntawm lub marathon, peb yuav mob siab rau txoj kev kawm ntawm txoj haujlwm NRHIAV (XAV). Nws saib rau tus cwj pwm (los yog cov cim) nyob rau hauv ib txoj hlua ntawv thiab qhia qhov twg nws pom. Peb tseem yuav saib yuav ua li cas nrog cov xwm txheej uas qhov haujlwm no cuam tshuam qhov yuam kev.
Yog li, cia peb saib ze dua ntawm txoj kev xav thiab cov piv txwv ntawm kev ua haujlwm NRHIAV (XAV). Yog tias koj muaj qee qhov kev ua yuam kev lossis piv txwv ntawm kev ua haujlwm nrog cov haujlwm no, thov qhia rau lawv hauv cov lus.
Txoj Haujlwm 18: SEARCH
muaj nuj nqi NRHIAV (SEARCH) tshawb nrhiav cov kab ntawv hauv lwm txoj kab ntawv, thiab yog tias pom, qhia nws txoj haujlwm.
Kuv tuaj yeem siv SEARCH muaj nuj nqi li cas?
muaj nuj nqi NRHIAV (SEARCH) tshawb nrhiav cov kab ntawv hauv lwm kab ntawv. Nws tuaj yeem:
- Nrhiav ib txoj hlua ntawm cov ntawv hauv lwm kab ntawv (cov ntaub ntawv tsis zoo).
- Siv cov cim wildcard hauv koj qhov kev tshawb nrhiav.
- Txiav txim siab qhov chaw pib hauv cov ntawv nyeem.
SEARCH Syntax
muaj nuj nqi NRHIAV (SEARCH) muaj cov syntax hauv qab no:
SEARCH(find_text,within_text,[start_num])
ПОИСК(искомый_текст;текст_для_поиска;[нач_позиция])
- nrhiav_text (search_text) yog cov ntawv koj tab tom nrhiav.
- hauv_text (text_for_search) – ib kab ntawv nyob rau hauv uas kev tshawb fawb tau ua.
- pib_num (start_position) - yog tias tsis tau teev tseg, kev tshawb nrhiav yuav pib los ntawm thawj tus cim.
Traps SEARCH (Tshawb nrhiav)
muaj nuj nqi NRHIAV (SEARCH) yuav rov qab txoj hauj lwm ntawm thawj txoj hlua sib tw, rooj plaub tsis zoo. Yog tias koj xav tau kev tshawb nrhiav cov ntaub ntawv, koj tuaj yeem siv lub luag haujlwm NRHIAV (NRAUG), uas peb yuav ntsib tom qab hauv marathon 30 Excel ua haujlwm hauv 30 hnub.
Piv txwv 1: Nrhiav cov ntawv hauv ib txoj hlua
Siv txoj haujlwm NRHIAV (SEARCH) txhawm rau nrhiav qee cov ntawv hauv ib kab ntawv. Hauv qhov piv txwv no, peb yuav tab tom nrhiav ib tus cim (sau hauv cell B5) nyob rau hauv cov kab ntawv hauv xov tooj B2.
=SEARCH(B5,B2)
=ПОИСК(B5;B2)
Yog tias pom cov ntawv nyeem, ua haujlwm NRHIAV (SEARCH) yuav xa rov qab tus lej xov tooj ntawm nws thawj tus cim hauv kab ntawv. Yog tsis pom, qhov tshwm sim yuav yog lus yuam kev #VALUE! (#SO).
Yog tias qhov tshwm sim yog qhov yuam kev, koj tuaj yeem siv qhov ua haujlwm IFERROR (IFERROR) yog li ntawd es tsis txhob ua haujlwm NRHIAV (SEARCH) tso saib cov lus sib raug. Muaj nuj nqi IFERROR (IFERROR) tau qhia hauv Excel pib hauv version 2007. Hauv cov ntawv dhau los, cov txiaj ntsig zoo ib yam tuaj yeem tau txais siv IF (IF) ua ke nrog ISERROR (EOSHIBKA).
=IFERROR(SEARCH(B5,B2),"Not Found")
=ЕСЛИОШИБКА(ПОИСК(B5;B2);"Not Found")
Piv txwv 2: Siv wildcards nrog SEARCH
Lwm txoj hauv kev los xyuas cov txiaj ntsig rov qab NRHIAV (Tshawb nrhiav), rau qhov yuam kev - siv cov haujlwm ISNUMBER (ISNUMBER). Yog tias pom txoj hlua, qhov tshwm sim NRHIAV (SEARCH) yuav yog tus lej, uas txhais tau tias muaj nuj nqi ISNUMBER (ISNUMBER) yuav rov qab TRUE. Yog tsis pom cov ntawv nyeem, ces NRHIAV (SEARCH) yuav qhia qhov yuam kev, thiab ISNUMBER (ISNUMBER) yuav rov FALSE.
Nyob rau hauv tus nqi ntawm qhov kev sib cav nrhiav_text (search_text) koj tuaj yeem siv cov cim tshwj xeeb. Cim * (asterisk) hloov cov lej cim lossis tsis muaj, thiab ? (lus nug) hloov ib tus cwj pwm.
Hauv peb qhov piv txwv, tus cim wildcard yog siv *, yog li cov kab lus CENTRAL, CENTER, thiab CENTER yuav pom nyob rau hauv cov npe ntawm txoj kev.
=ISNUMBER(SEARCH($E$2,B3))
=ЕЧИСЛО(ПОИСК($E$2;B3))
Piv txwv 3: Kev txiav txim siab pib txoj haujlwm rau Kev Tshawb Fawb (SEARCH)
Yog tias peb sau ob lub cim rho tawm (ob chav tsis pom zoo) ua ntej ntawm txoj haujlwm ISNUMBER (ISNUMBER), nws yuav rov qab cov nqi 1/0 es tsis yog TRUE/FALSE (TRUE/FALSE). Tom ntej no, ua haujlwm sum (SUM) hauv xov tooj ntawm tes E2 yuav suav tag nrho cov ntaub ntawv uas cov ntawv tshawb nrhiav pom.
Hauv qhov piv txwv hauv qab no, kab B qhia:
Lub nroog | Kev tshaj lij
Peb txoj haujlwm yog txhawm rau nrhiav cov haujlwm uas muaj cov kab ntawv nkag rau hauv cell E1. Cov mis hauv cell C2 yuav yog:
=--ISNUMBER(SEARCH($E$1,B2))
=--ЕЧИСЛО(ПОИСК($E$1;B2))
Cov qauv no pom cov kab uas muaj lo lus "bank", tab sis nyob rau hauv ib tug ntawm lawv lo lus no yog pom tsis nyob rau hauv lub npe ntawm lub profession, tab sis nyob rau hauv lub npe ntawm lub nroog. Qhov no tsis haum peb!
Txhua lub nroog lub npe yog ua raws li lub cim | ( ntsug bar), yog li peb, siv cov haujlwm NRHIAV (SEARCH), peb tuaj yeem nrhiav txoj haujlwm ntawm tus cwj pwm no. Nws txoj hauj lwm tuaj yeem teev tau raws li tus nqi ntawm qhov kev sib cav pib_num (start_position) hauv "lub ntsiab" ua haujlwm NRHIAV (XAV). Yog li ntawd, lub nroog cov npe yuav raug tsis quav ntsej los ntawm kev tshawb nrhiav.
Tam sim no cov qauv kuaj thiab kho yuav suav tsuas yog cov kab uas muaj lo lus "bank" hauv lub npe ntawm txoj haujlwm:
=--ISNUMBER(SEARCH($E$1,B2,SEARCH("|",B2)))
=--ЕЧИСЛО(ПОИСК($E$1;B2;ПОИСК("|";B2)))