Atrodiet un nomainiet vairākas vērtības

Lai atrastu un aizstātu vairākas vērtības ar formulu, varat ligzdot vairākas SUBSTITUTE funkcijas kopā un ievadīt atrašanas/aizstāšanas pārus no citas tabulas, izmantojot funkciju INDEX. Parādītajā piemērā mēs veicam 4 atsevišķas meklēšanas un nomaiņas darbības. G5 formula ir šāda:

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

kur 'atrast' ir nosaukts diapazons E5: E8, un “aizstāt” ir nosauktais diapazons F5: F8. Tālāk skatiet informāciju par to, kā padarīt šo formulu vieglāk lasāmu.

Priekšvārds

Nav iebūvētas formulas, lai programmā Excel veiktu virkni meklēšanas un nomaiņas darbību, tāpēc šī ir “koncepcijas” formula, lai parādītu vienu pieeju. Teksts, kas jāmeklē un jāaizstāj, tiek saglabāts tieši tabulas darblapā un iegūts, izmantojot funkciju INDEX. Tas padara risinājumu “dinamisku” - jebkura no šīm vērtībām tiek mainīta, rezultāti tiek nekavējoties atjaunināti. Protams, nav prasības izmantot INDEX, ja vēlaties, formulā var kodēt vērtības.

Pamatā formula izmanto funkciju SUBSTITUTE, lai veiktu katru aizstāšanu, izmantojot šo pamata modeli:

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

“Teksts” ir ienākošā vērtība, “atrast” ir meklējamais teksts, un “aizstāt” ir teksts, ar kuru aizstāt. Teksts, kas jāmeklē un jāaizstāj, tiek saglabāts tabulā pa labi, diapazonā E5: F8, viens pāris katrā rindā. Kreisajā pusē esošās vērtības ir nosaukts diapazons “atrast” un vērtības labajā pusē ir nosauktajā diapazonā “aizstāt”. Funkciju INDEX izmanto, lai izgūtu gan tekstu “atrast”, gan “aizstāt”:

kā aprēķināt kavētās dienas

 
= SUBSTITUTE (text,find,replace)

Tātad, lai veiktu pirmo aizstāšanu (meklējiet “sarkans”, aizstājiet ar “rozā”), mēs izmantojam:

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

Kopumā mēs veicam četras atsevišķas maiņas, un katra nākamā AIZSTĀDE sākas ar iepriekšējās AIZSTĀJĪBAS rezultātu:

kā izmantot summu, ja formula

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

Līniju pārtraukumi lasāmībai

Jūs pamanīsit, ka šāda veida ligzdota formula ir diezgan grūti lasāma. Pievienojot rindiņu pārtraukumus, mēs varam ievērojami vienkāršot formulas lasīšanu un uzturēšanu:

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

Formulu josla programmā Excel ignorē papildu atstarpes un rindiņu pārtraukumus, tāpēc iepriekš minēto formulu var ielīmēt tieši:

Starp citu, ir a īsinājumtaustiņš lai paplašinātu un sakļautu formulas joslu.

Vairāk nomaiņu

Tabulai var pievienot vairāk rindu, lai apstrādātu vairāk pāru atrašanas/nomaiņas. Katru reizi, kad tiek pievienots pāris, formula ir jāatjaunina, lai iekļautu jauno pāri. Ir arī svarīgi pārliecināties, vai nosauktie diapazoni (ja tos izmantojat) tiek atjaunināti, lai vajadzības gadījumā iekļautu jaunas vērtības. Varat arī izmantot a Pareiza Excel tabula dinamiskiem diapazoniem, nevis nosauktajiem diapazoniem.

Citi lietojumi

Tādu pašu pieeju var izmantot teksta tīrīšanai, “noņemot” pieturzīmes un citus simbolus no teksta ar virkni aizstājumu. Piemēram, šīs lapas formula parāda, kā to izdarīt tīrīt un pārformatēt tālruņa numurus .