Mukati
Nezuro mumarathon 30 Excel inoshanda mumazuva makumi matatu takashandisa basa INDEX (INDEX) kudzosa kukosha kwesero riri pamharadzano yemutsara wakapihwa nekoramu.
Pazuva remakumi maviri nenhatu remarathon, isu tichapa chidzidzo chebasa racho Tsiva (REPLACE), inova yechikamu chemavara. Inotsiva imwe nhamba yemavara mumutsara wetambo nemamwe mavara.
Saka, ngatitarisei zvakanyanya ruzivo uye mienzaniso pane basa Tsiva (REPLACE), uye kana uine rumwe ruzivo kana mienzaniso, ndokumbira ugovane mumhinduro.
Basa rechi25: REPLACE
basa Tsiva (REPLACE) inotsiva mavara mukati memavara zvichienderana nenhamba yakatsanangurwa yemavara uye nzvimbo yekutanga.
Iyo REPLACE basa rinogona kushandiswa sei?
basa Tsiva (REPLACE) inogona kutsiva mavara mune tambo yemavara, semuenzaniso:
- Chinja kodhi yenzvimbo mune nhamba yefoni.
- Tsiva nzvimbo nekoloni nenzvimbo.
- Shandisa nested function Tsiva (REPLACE) kuisa mahyphens akawanda.
REPLACE Syntax
basa Tsiva (REPLACE) ine chirevo chinotevera:
REPLACE(old_text,start_num,num_chars,new_text)
ЗАМЕНИТЬ(старый_текст;нач_поз;число_знаков;новый_текст)
- old_text (old_text) - tambo yemavara umo mavara achatsiviwa.
- start_num (start_pos) - tanga chinzvimbo chemavara ekare.
- num_chars (num_chars) - nhamba yevatambi vekare.
- new_text (new_text) - mavara achaiswa panzvimbo yemavara ekare.
Misungo REPLACE (REPLACE)
basa Tsiva (REPLACE) inotsiva nhamba yakatarwa yemavara kutanga panzvimbo yakatarwa. Kuti utsive mutsara wemavara chero kupi zvako muzvinyorwa zvepakutanga, unogona kushandisa basa racho SUBSTITUTE (SUBSTITUTE), iyo yatichazotarisa gare gare mumutambo wedu wemarathon.
Muenzaniso 1: Kuchinja kodhi yenzvimbo mune nhamba yefoni
Kushandisa mabasa Tsiva (REPLACE) Unogona kushandura manhamba matatu ekutanga enhamba yefoni, semuenzaniso, kana kodhi yenzvimbo itsva yaiswa. Kwatiri, iyo kodhi yenzvimbo nyowani inoiswa mukoramu C, uye nhamba dzenhare dzakagadziriswa dzinoratidzwa mukoramu D.
=REPLACE(B3,1,3,C3)
=ЗАМЕНИТЬ(B3;1;3;C3)
Muenzaniso 2: Tsiva nzvimbo nekoloni ine nzvimbo
Kutsanangura nzvimbo yekutangira yechinhu Tsiva (REPLACE), Unogona kushandisa basa racho ZIVA (TSVAGA) kutsvaga mutsara chaiwo wechinyorwa kana chimiro. Mumuenzaniso uyu, tinoda kutsiva nzvimbo yekutanga inoitika mumutsara tambo ine colon inoteverwa nenzvimbo.
=REPLACE(B3,FIND(" ",B3,1),1,": ")
=ЗАМЕНИТЬ(B3;НАЙТИ(" ";B3;1);1;": ")
Muenzaniso 3: Nested REPLACE mabasa ekuisa akawanda hyphens
basa Tsiva (REPLACE) inotenderwa kumisa imwe mukati meimwe, saka matsivedzo akawanda anogona kuitwa muzvinyorwa. Mumuenzaniso uyu, nhamba dzenhare dzinofanira kunge dziine hyphens mushure memavara matatu ekutanga uye mushure mechipiri. Kushandisa zero, sehuwandu hwemavara ekutsiva, isu tinowana mhedzisiro yekuti hapana mavara enhamba yefoni achabviswa, 2 chete hyphens ichawedzerwa.
=REPLACE(REPLACE(B3,4,0,"-"),8,0,"-")
=ЗАМЕНИТЬ(ЗАМЕНИТЬ(B3;4;0;"-");8;0;"-")
