Генеричка формула
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Резиме
Да бисте пронашли и заменили више вредности формулом, можете угнездити више функција ЗАМЕНЕ заједно и увести парове за проналазак / замену из друге табеле помоћу функције ИНДЕКС. У приказаном примеру изводимо 4 одвојене операције проналажења и замене. Формула у Г5 је:
=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))
где је „пронађи“ именовани опсег Е5: Е8, а „замени“ именовани опсег Ф5: Ф8. Погледајте испод информације о томе како олакшати читање ове формуле.
Предговор
Не постоји уграђена формула за покретање низа операција проналажења и замене у програму Екцел, тако да је ово „концепт“ формула која показује један приступ. Текст који треба тражити и заменити чува се директно на радном листу у табели и преузима се помоћу функције ИНДЕКС. То решење чини „динамичним“ - било која од ових вредности се мења, резултати се одмах ажурирају. Наравно, не постоји захтев да се користи ИНДЕКС; можете да уврстите вредности у формулу ако желите.
Објашњење
У основи, формула користи функцију СУБСТИТУТЕ да би извршила сваку замену, са овим основним обрасцем:
=SUBSTITUTE(text,find,replace)
„Текст“ је долазна вредност, „пронађи“ је текст који треба потражити, а „замени“ је текст којим се замењује. Текст који треба тражити и заменити чува се у табели десно, у опсегу Е5: Ф8, по један пар у реду. Вредности лево су у именованом опсегу „пронађи“, а вредности десно налазе се у именованом опсегу „замени“. Функција ИНДЕКС се користи за дохваћање и текста „пронађи“ и „замени“ овако:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Дакле, за покретање прве замене (потражите „црвену“, замените „ружичастом“) користимо:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
Укупно изводимо четири одвојене замене и свака следећа ЗАМЕНА започиње резултатом из претходне ЗАМЕНЕ:
=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))
Прекиди редова ради читљивости
Приметићете да је овакву угнежђену формулу прилично тешко прочитати. Додавањем прелома линија можемо формулу учинити много лакшом за читање и одржавање:
= 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))
Трака формуле у програму Екцел игнорише додатни размак и преломе линија, па се горња формула може директно налепити:
Иначе, постоји тастерска пречица за проширивање и сажимање траке са формулама.
Још замена
У табелу се може додати више редова ради обраде више парова проналажења / замене. Сваки пут када се дода пар, формулу је потребно ажурирати како би обухватила нови пар. Такође је важно осигурати да се именовани опсези (ако их користите) ажурирају тако да укључују нове вредности по потреби. За динамичке опсеге уместо именованих опсега могли бисте користити одговарајућу Екцел табелу.
Друга употреба
Исти приступ се може користити за чишћење текста "уклањањем" интерпункције и других симбола из текста са низом замена. На пример, формула на овој страници показује како да очистите и поново форматирате телефонске бројеве.
