Excel

Etsi ja korvaa useita arvoja

Find Replace Multiple Values

Excel-kaava: Etsi ja korvaa useita arvojaYleinen 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ä on 'konseptikaava', joka 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-koodia. Voit koodata arvot kaavaan halutessasi.



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 nimetty alue 'etsi' ja oikealla olevat arvot ovat nimellisalueella 'korvaa'. HAKEMISTOIMINTOA käytetään sekä haku- että korvaustekstin hakemiseen näin:



muuntaa numero Exceliksi
 
 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:

miten pivot-taulukkoa käytetään Excelissä
 
= 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:

Kaavapalkkiin lisätyt rivinvaihdot luettavuuden ja ylläpidon varmistamiseksi

Muuten, on 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ää a oikea Excel-taulukko dynaamisille alueille 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ää miten puhdista ja alusta puhelinnumerot .

Kirjailija Dave Bruns


^