Általános képlet
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Összegzés
Ha több értéket szeretne megkeresni és képlettel helyettesíteni, több SUBSTITUTE függvényt egymásba ágyazhat, és az INDEX függvény segítségével betáplálhatja a keresési / cserepárokat egy másik táblából. A bemutatott példában 4 külön keresési és cserélési műveletet hajtunk végre. A G5 képlete a következő:
=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))
ahol a "find" az elnevezett tartomány E5: E8, a "csere" pedig az F5: F8 nevű tartomány. Az alábbiakban olvashat arról, hogyan lehet könnyebben elolvasni ezt a képletet.
Előszó
Nincs beépített képlet a keresési és helyettesítési műveletek sorozatának futtatására az Excelben, ezért ez egy "koncepció" képlet, amely egy megközelítést mutat. A keresendő és lecserélendő szöveget közvetlenül a munkalapon tárolja a táblázat, és az INDEX függvény segítségével tölti le. Ez "dinamikussá" teszi a megoldást - ezen értékek bármelyike megváltozik, az eredmények azonnal frissülnek. Természetesen nincs szükség az INDEX használatára; keményen beírhatja az értékeket a képletbe, ha úgy tetszik.
Magyarázat
Alapjában véve a képlet a SUBSTITUTE függvényt használja az egyes helyettesítések végrehajtására, az alábbi alapmintával:
=SUBSTITUTE(text,find,replace)
A "szöveg" a bejövő érték, a "keresés" a keresendő szöveg, a "csere" pedig a helyettesítendő szöveg. A keresendő és helyettesítendő szöveget a jobb oldali táblázat tárolja, az E5: F8 tartományban, soronként egy párral. A bal oldali értékek a "find" nevű tartományban vannak, a jobb oldalon lévő értékek pedig a "cserélje" nevű tartományban vannak. Az INDEX függvény mind a "keresés", mind a "csere" szöveg lekérdezésére szolgál, így:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Tehát az első helyettesítés futtatásához (keresse meg a "piros" szót, cserélje ki a "rózsaszínűre") használjuk:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
Összesen négy külön cserét futtatunk, és minden következő SUBSTITUTE az előző SUBSTITUTE eredménnyel kezdődik:
=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))
Sortörések az olvashatóság érdekében
Észre fogja venni, hogy ezt a fajta beágyazott képletet elég nehéz elolvasni. A sortörések hozzáadásával a képletet sokkal könnyebben olvashatjuk és fenntarthatjuk:
= 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))
Az Excel képletsávja figyelmen kívül hagyja az extra szóközt és a sortöréseket, így a fenti képlet közvetlenül beilleszthető:
Egyébként van egy billentyűparancs a képletsáv kibővítésére és összecsukására.
További cserék
Több sor hozzáadható a táblához, hogy több keresési / cserélési pár kezelhető legyen. Minden egyes pár hozzáadásakor a képletet frissíteni kell, hogy tartalmazza az új párt. Fontos azt is ellenőrizni, hogy a megnevezett tartományok (ha Ön használja őket) frissülnek, hogy szükség szerint új értékeket is tartalmazzanak. Alternatív megoldásként használhat egy megfelelő Excel táblázatot a dinamikus tartományokhoz a megnevezett tartományok helyett.
Egyéb felhasználások
Ugyanez a megközelítés alkalmazható a szöveg megtisztítására úgy, hogy az írásjeleket és más szimbólumokat egy sor helyettesítéssel "lecsupaszítja" a szövegből. Például az ezen az oldalon található képlet megmutatja, hogyan kell megtisztítani és újraformázni a telefonszámokat.
