Vispārēja formula
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Kopsavilkums
Lai atrastu un aizstātu vairākas vērtības ar formulu, varat kopā ligzdot vairākas SUBSTITUTE funkcijas un, izmantojot funkciju INDEX, ievadīt citas tabulas meklēšanas / aizstāšanas pārus. Parādītajā piemērā mēs veicam 4 atsevišķas meklēšanas un aizstāšanas darbības. G5 formula ir:
=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 "atrast" ir nosauktais 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
Programmā Excel nav iebūvētas formulas, lai palaistu virkni meklēšanas un aizstāšanas darbību, tāpēc šī ir “koncepta” formula, kas parāda vienu pieeju. Teksts, kuru meklēt un aizstāt, tiek glabāts tieši darblapā tabulā un izgūts ar 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 lietot INDEX; ja vēlaties, formulā varat kodēt vērtības.
Paskaidrojums
Pamatā formula izmanto funkciju SUBSTITUTE, lai veiktu katru aizstāšanu ar šo pamata modeli:
=SUBSTITUTE(text,find,replace)
"Teksts" ir ienākošā vērtība, "atrast" ir meklējamais teksts un "aizstāt" ir teksts, ar kuru aizstāt. Teksts, kuru meklēt un aizstāt, tiek saglabāts tabulā pa labi, diapazonā E5: F8, viens pāris rindā. Kreisajā pusē esošās vērtības atrodas nosauktajā diapazonā "atrast", un labajā pusē esošās vērtības ir nosauktajā diapazonā "aizstāt". Funkcija INDEX tiek izmantota, lai izgūtu gan tekstu “atrast”, gan tekstu “aizstāt” šādi:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Tātad, lai veiktu pirmo aizstāšanu (meklējiet "sarkans", aizstājiet ar "rozā"), mēs izmantojam:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
Kopumā mēs veicam četras atsevišķas aizstāšanas, un katra nākamā SUBSTITUTE sākas ar rezultātu no iepriekšējā 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))
Rindas 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 padarīt formulu daudz vieglāk lasāmu un uzturamu:
= 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 atstarpi un līniju pārtraukumus, tāpēc iepriekš minēto formulu var ielīmēt tieši:
Starp citu, ir īsinājumtaustiņš, lai paplašinātu un sakļautu formulas joslu.
Vairāk aizstājēju
Tabulai var pievienot vairāk rindu, lai apstrādātu vairāk meklēšanas / aizvietošanas pāru. Katru reizi, kad tiek pievienots pāris, formula jāatjaunina, lai iekļautu jauno pāri. Ir svarīgi arī pārliecināties, vai nosauktie diapazoni (ja tos izmantojat) tiek atjaunināti, lai pēc vajadzības iekļautu jaunas vērtības. Varat arī izmantot dinamisko diapazonu vietā pareizu Excel tabulu, nevis nosaukto diapazonu.
Citi izmantošanas veidi
To pašu pieeju var izmantot, lai attīrītu tekstu, "noņemot" pieturzīmes un citus simbolus no teksta ar virkni aizstājēju. Piemēram, šīs lapas formula parāda, kā tīrīt un pārformatēt tālruņu numurus.
