Üldine valem
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Kokkuvõte
Mitme väärtuse valemiga leidmiseks ja asendamiseks võite mitu SUBSTITUTE funktsiooni omavahel pesitseda ja funktsiooni INDEX abil sisestada teise tabeli leidmise / asendamise paarid. Näidatud näites teostame 4 eraldi otsimise ja asendamise toimingut. G5 valem on:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2)),INDEX(find,3),INDEX(replace,3)),INDEX(find,4),INDEX(replace,4))
kus "leidma" on nimega vahemik E5: E8 ja "asendama" on nimega vahemik F5: F8. Allpool leiate teavet selle kohta, kuidas seda valemit hõlpsamini lugeda.
Eessõna
Excelis leidmise ja asendamise toimingute seeria käivitamiseks pole sisseehitatud valemit, nii et see on "lähenemisviis" valem ühe lähenemise kuvamiseks. Otsitav ja asendatav tekst salvestatakse otse tabelis töölehele ja leitakse funktsiooniga INDEX. See muudab lahenduse "dünaamiliseks" - mõnda neist väärtustest muudetakse, tulemusi värskendatakse kohe. Loomulikult ei nõuta INDEXi kasutamist; soovi korral saate valemisse väärtused kodeerida.
Selgitus
Põhimõtteliselt kasutab valem iga asenduse teostamiseks funktsiooni SUBSTITUT selle põhilise mustriga:
=SUBSTITUTE(text,find,replace)
"Tekst" on sissetulev väärtus, "otsi" on otsitav tekst ja "asenda" on asendatav tekst. Otsitav ja asendatav tekst on tabelis paremal, vahemikus E5: F8, üks paar reas. Vasakpoolsed väärtused asuvad nimega vahemikus "leid" ja paremal olevad väärtused nimega vahemikus "asenda". Funktsiooni INDEX kasutatakse nii otsingu kui ka teksti asendamiseks järgmiselt:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Nii et esimese asenduse käivitamiseks (otsige "punane", asendage "roosa") kasutame:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
Kokku käivitame neli eraldi asendust ja iga järgmine asendus algab eelmise asendaja tulemusega:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2)),INDEX(find,3),INDEX(replace,3)),INDEX(find,4),INDEX(replace,4))
Reavahetused loetavuse tagamiseks
Märkate, et sellist pesastatud valemit on üsna raske lugeda. Reavahetuste lisamise abil saame valemi lugeda ja hooldada palju lihtsamaks:
= SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( B5, INDEX(find,1),INDEX(replace,1)), INDEX(find,2),INDEX(replace,2)), INDEX(find,3),INDEX(replace,3)), INDEX(find,4),INDEX(replace,4))
Exceli valemiriba eirab täiendavat tühimikku ja reavahetusi, nii et ülaltoodud valemi saab kleepida otse:
Muide, valemiriba laiendamiseks ja ahendamiseks on olemas klaviatuuri otsetee.
Rohkem asendusi
Rohkemate ridade leidmiseks / asendamiseks saab tabelisse lisada rohkem ridu. Iga paari lisamisel tuleb valemit uuendada, et see sisaldaks uut paari. Samuti on oluline veenduda, et nimetatud vahemikke (kui te neid kasutate) värskendatakse, et need hõlmaksid uusi väärtusi vastavalt vajadusele. Alternatiivina võite dünaamiliste vahemike jaoks kasutada nimega vahemike asemel korralikku Exceli tabelit.
Muud kasutusalad
Sama lähenemist saab kasutada teksti puhastamiseks, eemaldades tekstist kirjavahemärgid ja muud sümbolid koos asenduste jadaga. Näiteks näitab selle lehe valem, kuidas telefoninumbreid puhastada ja ümber vormindada.
