txheem
Nag hmo hauv marathon 30 Excel ua haujlwm hauv 30 hnub peb siv lub luag haujlwm INDEX (INDEX) xa rov qab tus nqi ntawm lub xovtooj ntawm qhov kev sib tshuam ntawm kab thiab kab.
Hnub 25 ntawm lub marathon, peb yuav mob siab rau txoj kev kawm ntawm txoj haujlwm hloov (REPLACE), uas belongs rau qeb ntawv. Nws hloov qee tus lej ntawm cov cim hauv kab ntawv nrog rau lwm cov ntawv.
Yog li, cia peb ua tib zoo saib cov ntaub ntawv thiab cov piv txwv ntawm kev ua haujlwm hloov (REPLACE), thiab yog tias koj muaj lus qhia ntxiv lossis piv txwv, thov qhia rau lawv hauv cov lus.
Txoj Haujlwm 25: REPLACE
muaj nuj nqi hloov (REPLACE) hloov cov cim nyob rau hauv cov ntawv nyeem raws li tus naj npawb ntawm cov cim thiab pib txoj haujlwm.
Yuav siv REPLACE function li cas?
muaj nuj nqi hloov (REPLACE) tuaj yeem hloov cov cim hauv kab ntawv, piv txwv li:
- Hloov chaw code hauv tus xov tooj.
- Hloov chaw nrog txoj hnyuv nrog qhov chaw.
- Siv nested function hloov (REPLACE) txhawm rau ntxig ntau qhov hyphens.
REPLACE Syntax
muaj nuj nqi hloov (REPLACE) muaj cov syntax hauv qab no:
REPLACE(old_text,start_num,num_chars,new_text)
ЗАМЕНИТЬ(старый_текст;нач_поз;число_знаков;новый_текст)
- old_text (old_text) – cov kab ntawv uas cov cim yuav raug hloov.
- pib_num (start_pos) – pib txoj hauj lwm ntawm cov cim qub.
- num_chars (num_chars) – tus lej ntawm cov cim qub.
- new_text (new_text) – cov ntawv uas yuav muab tso rau hauv qhov chaw ntawm cov cim qub.
Traps REPLACE (REPLACE)
muaj nuj nqi hloov (REPLACE) hloov ib tus lej ntawm cov cim pib ntawm txoj haujlwm tshwj xeeb. Txhawm rau hloov cov kab ntawv tshwj xeeb nyob qhov twg hauv cov ntawv qub, koj tuaj yeem siv txoj haujlwm RAWS LI (SUBSTITUTE), uas peb yuav saib tom qab hauv peb marathon.
Piv txwv 1: Hloov chaw code hauv ib tus xov tooj
Siv cov haujlwm hloov (REPLACE) Koj tuaj yeem hloov pauv thawj peb tus lej ntawm tus lej xov tooj, piv txwv li, thaum lub koos pij tawj tshiab tau teeb tsa. Hauv peb qhov xwm txheej, tus lej tshiab hauv cheeb tsam tau nkag rau hauv kab C, thiab cov lej kho tau raug tso tawm hauv kab D.
=REPLACE(B3,1,3,C3)
=ЗАМЕНИТЬ(B3;1;3;C3)
Piv txwv 2: Hloov ib qho chaw nrog lub plab zom mov nrog qhov chaw
Txhawm rau txheeb xyuas qhov chaw pib rau qhov ua haujlwm hloov (REPLACE), Koj tuaj yeem siv txoj haujlwm NRHIAV (TSI) txhawm rau nrhiav cov kab ntawv tshwj xeeb lossis tus cwj pwm. Hauv qhov piv txwv no, peb xav hloov thawj qhov chaw uas tshwm sim hauv cov ntawv nyeem nrog cov nyuv ua raws li qhov chaw.
=REPLACE(B3,FIND(" ",B3,1),1,": ")
=ЗАМЕНИТЬ(B3;НАЙТИ(" ";B3;1);1;": ")
Piv txwv 3: Nested REPLACE ua haujlwm los ntxig ntau qhov hyphens
muaj nuj nqi hloov (REPLACE) tau tso cai rau zes ib qho hauv lwm qhov, yog li ntau qhov kev hloov pauv tuaj yeem ua rau hauv cov ntawv nyeem. Hauv qhov piv txwv no, tus lej xov tooj yuav tsum muaj hyphens tom qab thawj peb lub cim thiab tom qab thib ob. Siv Xoom, raws li tus naj npawb ntawm cov cim los hloov, peb yuav tau txais cov txiaj ntsig uas tsis muaj tus cim ntawm tus lej xov tooj yuav raug tshem tawm, tsuas yog 2 hyphens yuav raug ntxiv.
=REPLACE(REPLACE(B3,4,0,"-"),8,0,"-")
=ЗАМЕНИТЬ(ЗАМЕНИТЬ(B3;4;0;"-");8;0;"-")
