Yleinen kaava
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Yhteenveto
Jos haluat etsiä ja korvata useita arvoja kaavalla, voit sijoittaa useita SUBSTITUTE-funktioita yhteen ja syöttää toisen taulukon haku- / korvauspareja INDEX-funktiolla. Esitetyssä esimerkissä suoritamme 4 erillistä etsintä- ja korvaustoimintoa. G5: n kaava 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))
missä "etsi" on nimetty alue E5: E8 ja "korvaa" on nimetty alue F5: F8. Katso alta lisätietoja siitä, miten kaava on helpompi lukea.
Esipuhe
Ei ole sisäänrakennettua kaavaa etsintä- ja korvaustoimintojen sarjan suorittamiseen Excelissä, joten tämä "käsite" -kaava näyttää yhden lähestymistavan. Etsitään ja korvataan teksti tallennetaan suoraan taulukon taulukkoon ja haetaan INDEX-toiminnolla. Tämä tekee ratkaisusta "dynaamisen" - mitä tahansa näistä arvoista muutetaan, tulokset päivittyvät välittömästi. Tietysti ei ole vaatimusta käyttää INDEX; voit koodata arvot kaavaan, jos haluat.
Selitys
Ytimessä kaava käyttää SUBSTITUTE-funktiota kunkin korvaamisen suorittamiseksi tällä perusmallilla:
=SUBSTITUTE(text,find,replace)
"Teksti" on saapuva arvo, "etsi" on etsittävä teksti ja "korvaa" on korvattava teksti. Etsittävä ja korvattava teksti tallennetaan taulukkoon oikealle, alueelle E5: F8, yksi pari riviä kohden. Vasemmanpuoleiset arvot ovat nimettyyn alueeseen "etsi" ja oikealla olevat arvot nimettyyn alueeseen "korvaa". INDEX-funktiota käytetään sekä hakutekstin että korvaavan tekstin hakemiseen näin:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Joten ensimmäisen vaihdon suorittamiseksi (etsi "punainen", korvaa "vaaleanpunainen") käytämme:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
Suoritamme yhteensä neljä erillistä korvaamista, ja jokainen seuraava SUBSTITUTE alkaa tuloksella edellisestä SUBSTITUTE:
=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))
Rivinvaihdot luettavuutta varten
Huomaat, että tällaista sisäkkäistä kaavaa on melko vaikea lukea. Lisäämällä rivinvaihtoja voimme tehdä kaavasta paljon helpommin luettavan ja ylläpidettävän:
= 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))
Excelin kaavapalkki jättää huomiotta ylimääräisen välilyönnin ja rivinvaihdot, joten yllä oleva kaava voidaan liittää suoraan:
Muuten, on olemassa pikanäppäin kaavapalkin laajentamiseksi ja tiivistämiseksi.
Lisää vaihtoja
Lisää rivejä voidaan lisätä taulukkoon käsittelemään enemmän etsintä- / korvauspareja. Joka kerta, kun pari lisätään, kaava on päivitettävä sisällyttämään uusi pari. On myös tärkeää varmistaa, että nimetyt alueet (jos käytät niitä) päivitetään sisältämään uudet arvot tarpeen mukaan. Vaihtoehtoisesti voit käyttää dynaamisille alueille oikeaa Excel-taulukkoa nimettyjen alueiden sijaan.
Muu käyttö
Samaa lähestymistapaa voidaan käyttää tekstin puhdistamiseen "poistamalla" välimerkit ja muut symbolit tekstistä sarjoilla korvauksia. Esimerkiksi tämän sivun kaava näyttää, kuinka puhelinnumerot puhdistetaan ja alustetaan uudelleen.
