Etsi ja korvaa useita arvoja

Jos haluat löytää ja korvata useita arvoja kaavalla, voit liittää useita SUBSTITUTE -funktioita yhteen ja syöttää Etsi/korvaa pareja toisesta taulukosta INDEX -funktion avulla. Esitetyssä esimerkissä suoritamme 4 erillistä etsintä- ja korvaustoimintoa. G5: n kaava on:

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

missä 'löytää' on nimetty alue E5: E8, ja 'korvata' on nimetty alue F5: F8. Katso alta ohjeet tämän kaavan helpompaan lukemiseen.

Esipuhe

Ei ole sisäänrakennettua kaavaa sarjan etsintä- ja korvaustoimintojen suorittamiseen Excelissä, joten tämä on `` käsite '' -kaava, joka näyttää yhden lähestymistavan. Etsittävä ja korvattava teksti tallennetaan suoraan taulukon laskentataulukkoon ja haetaan INDEX -toiminnolla. Tämä tekee ratkaisusta 'dynaamisen' - mikä tahansa näistä arvoista muuttuu, tulokset päivitetään välittömästi. Tietenkään ei ole pakollista käyttää INDEX voit koodata arvoja kaavaan, jos haluat.

Pohjimmiltaan kaava käyttää SUBSTITUTE -funktiota jokaisen korvauksen suorittamiseen tällä peruskuviolla:

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

'Teksti' on saapuva arvo, 'etsi' on etsittävä teksti ja 'korvata' on teksti, jolla korvataan. Etsittävä ja korvattava teksti tallennetaan oikealla olevaan taulukkoon alueella E5: F8, yksi pari per rivi. Vasemmanpuoleiset arvot ovat nimetty alue 'find' ja oikealla olevat arvot ovat nimetyllä alueella 'korvata'. INDEX -toimintoa käytetään sekä 'etsi' -tekstin että 'korvaavan' tekstin noutamiseen seuraavasti:

muuntaa numero Exceliksi

 
= SUBSTITUTE (text,find,replace)

Joten suoritamme ensimmäisen korvauksen (etsi 'punainen', korvaa 'vaaleanpunaisella'):

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

Yhteensä suoritamme neljä erillistä vaihtoa, ja jokainen seuraava KORVAUS alkaa edellisen KORVAUKSEN tuloksella:

miten pivot-taulukkoa käytetään Excelissä

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

Rivivälit luettavuuden vuoksi

Huomaat, että tällaista sisäkkäistä kaavaa on melko vaikea lukea. Lisäämällä rivinvaihtoja voimme tehdä kaavasta paljon helpompaa lukea ja ylläpitää:

 
= 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 ohittaa ylimääräiset välilyönnit ja rivinvaihdot, joten yllä oleva kaava voidaan liittää suoraan:

Muuten on olemassa a pikanäppäin kaavapalkin laajentamiseksi ja tiivistämiseksi.

Lisää vaihtoja

Taulukkoon voidaan lisätä lisää rivejä, jotta voidaan etsiä/korvata pareja. Aina kun pari lisätään, kaava on päivitettävä sisältä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 tarvittaessa uusia arvoja. Vaihtoehtoisesti voit käyttää a oikea Excel -taulukko dynaamisille alueille nimettyjen alueiden sijaan.

Muut käyttötarkoitukset

Samaa lähestymistapaa voidaan käyttää tekstin puhdistamiseen poistamalla välimerkit ja muut symbolit tekstistä, jossa on useita korvauksia. Esimerkiksi tämän sivun kaava näyttää kuinka puhdista ja muotoile puhelinnumerot uudelleen .