Excel

Poiščite in zamenjajte več vrednosti

Find Replace Multiple Values

Formula Excel: Poiščite in zamenjajte več vrednostiSplošna formula | _+_ | Povzetek

Če želite poiskati in zamenjati več vrednosti s formulo, lahko skupaj ugnezdite več funkcij SUBSTITUTE in vnesete pare iskanja/zamenjave iz druge tabele s funkcijo INDEX. V prikazanem primeru izvajamo 4 ločene operacije iskanja in zamenjave. Formula v G5 je:



= SUBSTITUTE ( SUBSTITUTE (B5, INDEX (find,1), INDEX (replace,1)), INDEX (find,2), INDEX (replace,2))

kjer je 'najti' imenovani obseg E5: E8 in „nadomesti“ je poimenovano območje 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' - vsaka od teh vrednosti se spremeni, rezultati se takoj posodobijo. Seveda ni potrebe po uporabi INDEX-a, če želite, lahko vnesete vrednosti v formulo.





Pojasnilo

V bistvu formula uporablja funkcijo SUBSTITUTE za izvajanje vsake zamenjave s tem osnovnim vzorcem:

 
= 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))

'Besedilo' je dohodna vrednost, 'najdi' je besedilo, ki ga je treba iskati, in 'nadomesti' je besedilo, s katerim ga je treba zamenjati. Besedilo, ki ga želite poiskati in zamenjati, je shranjeno v tabeli na desni, v območju E5: F8, en par na vrstico. Vrednosti na levi so v imenovani obseg 'find' in vrednosti na desni so v imenovanem obsegu 'replace'. Funkcija INDEX se uporablja za iskanje besedila 'find' in 'replace' na naslednji način:



število delovnih dni med dvema datumoma presega
 
= SUBSTITUTE (text,find,replace)

Torej, za izvedbo prve zamenjave (poiščite 'rdečo', zamenjajte z 'rožnato') uporabljamo:

 
 INDEX (find,1) // first 'find' value  INDEX (replace,1) // first 'replace' value

Skupaj izvedemo štiri ločene zamenjave in vsaka naslednja ZAMENINA se začne z rezultatom iz prejšnje ZAMENE:

kako najti način na
 
= SUBSTITUTE (B5, INDEX (find,1), INDEX (replace,1))

Prelomi vrstic za berljivost

Opazili boste, da je to vrsto gnezdene formule precej težko prebrati. Z dodajanjem prelomov vrstic lahko formulo olajšamo branje in vzdrževanje:

 
= 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 formule v Excelu ne upošteva dodatnih presledkov in prelomov vrstic, zato lahko zgornjo formulo prilepite neposredno:

Prelomi vrstic so dodani v vrstico formule za berljivost in vzdrževanje

Mimogrede, obstaja a bližnjica na tipkovnici za razširitev in strnitev vrstice formule.

Več zamenjav

V tabelo lahko dodate več vrstic za več parov iskanja/zamenjave. Ob vsakem dodajanju para je treba formulo posodobiti tako, da bo vključevala nov par. Pomembno je tudi, da se imenovani obsegi (če jih uporabljate) posodobijo in po potrebi vključijo nove vrednosti. Druga možnost je, da uporabite a pravilno Excelovo tabelo za dinamična območja, namesto imenovanih.

Druge uporabe

Isti pristop se lahko uporabi 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čistite in preoblikujte telefonske številke .

Avtor Dave Bruns


^