Splošna formula
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Povzetek
Če želite poiskati in nadomestiti več vrednosti s formulo, lahko skupaj ugnezdite več funkcij SUBSTITUTE in s funkcijo INDEX vnesete pare find / replace iz druge tabele. V prikazanem primeru izvajamo 4 ločene operacije iskanja in zamenjave. Formula v G5 je:
=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))
kjer je "find" imenovani obseg E5: E8, "replace" pa imenovani obseg F5: F8. Spodaj si oglejte informacije o tem, kako olajšati branje te formule.
Predgovor
V Excelu ni vgrajene formule za izvajanje vrste operacij iskanja in zamenjave, zato je to "konceptna" formula, ki prikazuje en pristop. Besedilo, ki ga želite poiskati in zamenjati, je shranjeno neposredno na delovnem listu v tabeli in pridobljeno s funkcijo INDEX. Zaradi tega je rešitev "dinamična" - katera koli od teh vrednosti se spremeni in rezultati se takoj posodobijo. Seveda ni potrebe po uporabi indeksa; vrednosti lahko trdo kodirate v formulo, če želite.
Pojasnilo
V osnovi formula uporablja funkcijo SUBSTITUTE za izvedbo vsake zamenjave s tem osnovnim vzorcem:
=SUBSTITUTE(text,find,replace)
"Text" je dohodna vrednost, "find" je besedilo, ki ga je treba iskati, in "replace" je besedilo, s katerim ga je treba zamenjati. Besedilo, ki ga želimo poiskati in zamenjati, je shranjeno v tabeli na desni strani v obsegu E5: F8, po en par na vrstico. Vrednosti na levi so v imenovanem obsegu "najdi", vrednosti na desni pa v imenovanem obsegu "zamenjaj". Funkcija INDEX se uporablja za pridobivanje besedila "najdi" in besedila "zamenjaj", kot je ta:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Torej, za izvedbo prve zamenjave (poiščite "rdečo", zamenjajte z "roza") uporabimo:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
Skupaj izvedemo štiri ločene zamenjave in vsaka naslednja NADOMESTKA se začne z rezultatom prejšnje NADOMESTE:
=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))
Prelomi vrstic za berljivost
Opazili boste, da je tovrstno vgnezdeno formulo precej težko brati. Z dodajanjem prelomov vrstic lahko olajšamo branje in vzdrževanje formule:
= 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))
Vrstica s formulami v Excelu prezre dodaten presledek in prelome vrstic, zato je zgornjo formulo mogoče prilepiti neposredno:
Mimogrede, obstaja bližnjica na tipkovnici za razširitev in strnitev vrstice s formulami.
Več zamenjav
V tabelo lahko dodate več vrstic za obdelavo več parov za iskanje / zamenjavo. Vsakič, ko dodate par, je treba formulo posodobiti, da bo vključevala nov par. Pomembno je tudi zagotoviti, da so imenovani obsegi (če jih uporabljate) posodobljeni tako, da po potrebi vključujejo nove vrednosti. Namesto imenovanih obsegov lahko za dinamične obsege uporabite tudi ustrezno tabelo Excel.
Druge uporabe
Enak pristop lahko uporabimo za čiščenje besedila z "odstranjevanjem" ločil in drugih simbolov iz besedila z vrsto zamenjav. Formula na tej strani na primer prikazuje, kako očistiti in preoblikovati telefonske številke.
