Bendroji formulė
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Santrauka
Norėdami rasti ir pakeisti keletą reikšmių formule, galite susieti kelias SUBSTITUTE funkcijas kartu ir naudodamiesi funkcija INDEX, naudokite kitos lentelės paieškos / pakeitimo poras. Parodytame pavyzdyje atliekame 4 atskiras radimo ir pakeitimo operacijas. G5 formulė yra:
=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))
kur „rasti“ yra pavadintas diapazonas E5: E8, o „pakeisti“ - pavadintas diapazonas F5: F8. Žemiau pateikiama informacija apie tai, kaip lengviau perskaityti šią formulę.
Įvadas
Nėra integruotos formulės, leidžiančios vykdyti „Excel“ paieškos ir pakeitimo operacijų seriją, todėl tai yra „koncepcijos“ formulė, rodanti vieną požiūrį. Ieškomas ir pakeičiamas tekstas saugomas tiesiogiai darbalapyje lentelėje ir gaunamas naudojant funkciją INDEX. Tai daro sprendimą „dinamišką“ - bet kuri iš šių reikšmių yra pakeista, rezultatai nedelsiant atnaujinami. Žinoma, nėra reikalavimo naudoti INDEX; jei norite, galite tiksliai koduoti reikšmes į formulę.
Paaiškinimas
Pagrinde formulė naudoja funkciją SUBSTITUTE kiekvienam pakeitimui atlikti pagal šį pagrindinį modelį:
=SUBSTITUTE(text,find,replace)
„Tekstas“ yra gaunama vertė, „rasti“ yra ieškomas tekstas, o „pakeisti“ - tekstas, kuriuo reikia pakeisti. Ieškomas ir pakeičiamas tekstas saugomas lentelėje dešinėje, E5: F8 diapazone, po vieną porą eilutėje. Kairėje esančios reikšmės yra pavadintame diapazone „rasti“, o dešinėje - pavadintame diapazone „pakeisti“. INDEX funkcija naudojama norint rasti tekstą „rasti“ ir „pakeisti“ taip:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Taigi, norėdami atlikti pirmąjį pakeitimą (ieškokite „raudona“, pakeiskite „rožinė“), mes naudojame:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
Iš viso atliekame keturis atskirus pakeitimus, o kiekvienas paskesnis PAKEITIMAS prasideda ankstesnio PAKEITIMO rezultatu:
=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))
Eilučių pertraukos, skirtos įskaitomumui
Jūs pastebėsite, kad tokią įdėtą formulę sunku perskaityti. Pridėdami eilučių pertraukas, galime padaryti formulę daug lengviau skaitomą ir prižiūrimą:
= 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))
„Excel“ formulių juosta ignoruoja papildomą tarpą ir eilučių pertraukas, todėl pirmiau pateiktą formulę galima įklijuoti tiesiogiai:
Beje, yra klaviatūros klavišas, leidžiantis išplėsti ir sutraukti formulių juostą.
Daugiau pakeitimų
Prie lentelės galima pridėti daugiau eilučių, kad būtų galima tvarkyti daugiau radimo / pakeitimo porų. Kiekvieną kartą, kai pridedama pora, formulę reikia atnaujinti, kad būtų įtraukta nauja pora. Taip pat svarbu įsitikinti, kad įvardyti diapazonai (jei juos naudojate) yra atnaujinti, jei reikia, įtraukiant naujas reikšmes. Arba dinaminiams diapazonams galite naudoti tinkamą „Excel“ lentelę, o ne pavadintus diapazonus.
Kiti naudojimo būdai
Tą patį metodą galima naudoti norint išvalyti tekstą „išimant“ skyrybos ženklus ir kitus simbolius iš teksto su pakaitų serija. Pavyzdžiui, šiame puslapyje pateikta formulė rodo, kaip išvalyti ir suformatuoti telefono numerius.
