Contents
Jiya a gudun marathon 30 Excel yana aiki a cikin kwanaki 30 mun yi amfani da aikin INDEX (INDEX) don dawo da ƙimar tantanin halitta da ke tsakanin layin da aka bayar da shafi.
A ranar 25th na marathon, za mu ƙaddamar da nazarin aikin Sauya (MUSA), wanda ke cikin nau'in rubutu. Yana maye gurbin takamaiman adadin haruffa a cikin sigar rubutu da wani rubutu.
Don haka, bari mu kalli bayanai da misalai akan aikin Sauya (MUSA), kuma idan kuna da ƙarin bayani ko misalai, da fatan za a raba su a cikin sharhi.
Aiki 25: SAUYA
aiki Sauya (SAUKI) yana maye gurbin haruffa a cikin rubutu bisa ƙayyadadden adadin haruffa da matsayi na farawa.
Ta yaya za a iya amfani da aikin REPLACE?
aiki Sauya (MUSA) na iya maye gurbin haruffa a cikin rubutun rubutu, misali:
- Canja lambar yanki a lambar wayar.
- Maye gurbin sarari da hanji da sarari.
- Yi amfani da aikin gida Sauya (MUSA) don saka sarƙaƙƙiya da yawa.
MATSAYI Syntax
aiki Sauya (MUSA) yana da ma'ana mai zuwa:
REPLACE(old_text,start_num,num_chars,new_text)
ЗАМЕНИТЬ(старый_текст;нач_поз;число_знаков;новый_текст)
- tsohon_rubutu (old_text) - igiyar rubutu wacce za a maye gurbin haruffa.
- farawa_num (start_pos) - farkon matsayi na tsoffin haruffa.
- lamba_chars (num_chars) - adadin tsoffin haruffa.
- sabon_rubutu (new_text) - rubutu da za a saka a madadin tsoffin haruffa.
Tarko MUSA (MUSA)
aiki Sauya (SAUKI) yana maye gurbin ƙayyadadden adadin haruffa waɗanda suka fara a ƙayyadadden matsayi. Don maye gurbin takamaiman layin rubutu a ko'ina cikin ainihin rubutun, zaku iya amfani da aikin MUSA (MAGAJIN), wanda za mu duba daga baya a tseren gudunmu.
Misali 1: Canja lambar yanki a lambar waya
Amfani da ayyuka Sauya (MUSA) Zaka iya canza lambobi uku na farko na lambar waya, misali, lokacin da aka saita sabon lambar yanki. A cikin yanayinmu, ana shigar da sabuwar lambar yanki a shafi na C, kuma ana nuna lambobin wayar da aka gyara a shafi na D.
=REPLACE(B3,1,3,C3)
=ЗАМЕНИТЬ(B3;1;3;C3)
Misali 2: Sauya sarari da hanji da sarari
Don ayyana wurin farawa don aiki Sauya (MUSA), Kuna iya amfani da aikin KA (NEMO) don nemo takamaiman layin rubutu ko hali. A cikin wannan misali, muna so mu maye gurbin wuri na farko da ke faruwa a cikin zaren rubutu tare da hanji wanda sarari ke biye da shi.
=REPLACE(B3,FIND(" ",B3,1),1,": ")
=ЗАМЕНИТЬ(B3;НАЙТИ(" ";B3;1);1;": ")
Misali na 3: Ayyukan GABATARWA don saka sarƙaƙƙiya da yawa
aiki Sauya (SAUKI) an yarda ya yi gida ɗaya a cikin wani, don haka ana iya yin maye da yawa a cikin rubutun tushen. A cikin wannan misali, lambobin waya dole ne su ƙunshi saƙa bayan haruffa uku na farko da kuma bayan na biyu. Amfani Zero, a matsayin adadin haruffan da za mu maye gurbin, za mu sami sakamakon cewa ba za a cire ko ɗaya daga cikin haruffan lambar wayar ba, kawai za a ƙara 2 hyphens.
=REPLACE(REPLACE(B3,4,0,"-"),8,0,"-")
=ЗАМЕНИТЬ(ЗАМЕНИТЬ(B3;4;0;"-");8;0;"-")
