Групна замена текста формулама

Претпоставимо да имате листу у којој су, са различитим степеном „једноставности“, уписани почетни подаци – на пример, адресе или називи компанија:

Јасно се види да је исти град или компанија овде присутан у шароликим варијантама, што ће, очигледно, стварати много проблема при раду са овим столовима у будућности. А ако мало размислите, можете наћи доста примера сличних задатака из других области.

Замислите сад да вам овако искривљени подаци редовно долазе, односно ово није једнократна прича „ручно поправи, заборави“, већ проблем на редовној основи и у великом броју ћелија.

Шта да радим? Не замењујте ручно искривљени текст 100500 пута исправним путем оквира „Пронађи и замени“ или кликом на Цтрл+H?

Прво што вам падне на памет у таквој ситуацији је да направите масовну замену према унапред састављеној референтној књизи подударања нетачних и тачних опција – овако:

Нажалост, са очигледном распрострањеношћу таквог задатка, Мицрософт Екцел нема једноставне уграђене методе за његово решавање. За почетак, хајде да схватимо како да то урадимо помоћу формула, без укључивања „тешке артиљерије“ у облику макроа у ВБА или Повер Куери-ју.

Случај 1. Масовна пуна замена

Почнимо од релативно једноставног случаја – ситуације у којој је потребно да замените стари искривљени текст новим. потпуно.

Рецимо да имамо две табеле:

У првом – оригинална шаролика имена компанија. У другом – референтна књига преписке. Ако у називу предузећа у првој табели нађемо било коју реч из колоне Да пронађу, онда треба ово искривљено име потпуно заменити исправним – из колоне Субституте друга табела за претрагу.

Ради лакшег:

• Обе табеле се конвертују у динамичку („паметну“) помоћу пречице на тастатури Цтрл+T или тим Уметак – Табела (Инсерт — Табела).
  
• На картици која се појави Конструктор (Дизајн) прва табела именована Датум, а друга референтна табела – Замене.

Да бисмо објаснили логику формуле, идемо мало издалека.

Узимајући за пример прво предузеће из ћелије А2 и привремено заборављајући на остале компаније, хајде да покушамо да одредимо која опција из колоне Да пронађу састаје се тамо. Да бисте то урадили, изаберите било коју празну ћелију у слободном делу листа и тамо унесите функцију ТО ФИНД (ПРОНАЂИ):

Ова функција одређује да ли је дати подстринг укључен (први аргумент су све вредности из колоне Да пронађу) у изворни текст (прва компанија из табеле података) и треба да испише или редни број знака из којег је текст пронађен, или грешку ако подниз није пронађен.

Трик је у томе што, пошто смо навели не једну, већ неколико вредности као први аргумент, ова функција ће такође вратити као резултат не једну вредност, већ низ од 3 елемента. Ако немате најновију верзију Оффице 365 која подржава динамичке низове, онда након што унесете ову формулу и кликнете на унети видећете овај низ право на листу:

Ако имате претходне верзије Екцел-а, онда након што кликнете на унети видећемо само прву вредност из низа резултата, односно грешку #ВРЕДНОСТ! (#ВРЕДНОСТ!).

Не треба да се плашите 🙂 У ствари, наша формула функционише и још увек можете да видите цео низ резултата ако изаберете унету функцију у траци формула и притиснете тастер F9(само не заборавите да притиснете Есцда се вратим на формулу):

Добијени низ резултата значи да у оригиналном кривом називу компаније (ГК Морозко ОАО) свих вредности у колони Да пронађу нашао само другу (Морозко), а почев од 4. знака по реду.

Сада додајмо функцију нашој формули ПОГЛЕД(ПОТРАЖИТИ):

Ова функција има три аргумента:

1. Жељена вредност – можете користити било који довољно велики број (главно је да премашује дужину било ког текста у изворним подацима)
   
2. Виевед_вецтор – опсег или низ у коме тражимо жељену вредност. Ево претходно уведене функције ТО ФИНД, који враћа низ {#ВАЛУЕ!:4:#ВАЛУЕ!}
   
3. вектор_Резултати – опсег из којег желимо да вратимо вредност ако се жељена вредност нађе у одговарајућој ћелији. Ево тачних имена из колоне Субституте наша референтна табела.
   

Главна и неочигледна карактеристика овде је да функција ПОГЛЕД ако нема тачног подударања, увек тражи најближу најмању (претходну) вредност. Стога, навођењем било ког великог броја (на пример, 9999) као жељене вредности, ми ћемо приморати ПОГЛЕД пронађите ћелију са најближим најмањим бројем (4) у низу {#ВАЛУЕ!:4:#ВАЛУЕ!} и вратите одговарајућу вредност из вектора резултата, односно тачан назив компаније из колоне Субституте.

Друга нијанса је да је, технички, наша формула формула низа, јер функција ТО ФИНД враћа као резултате не једну, већ низ од три вредности. Али пошто функција ПОГЛЕД подржава низове из кутије, онда не морамо да уносимо ову формулу као класичну формулу низа – користећи пречицу на тастатури Цтрл+смена+унети. Једноставна ће бити довољна унети.

То је све. Надам се да сте схватили логику.

Остаје да се готова формула пренесе у прву ћелију Б2 колоне Фиксно – и наш задатак је решен!

Наравно, са обичним (не паметним) табелама, ова формула такође функционише одлично (само не заборавите на кључ F4 и поправљање релевантних веза):

Случај 2. Масовна делимична замена

Овај случај је мало тежи. Опет имамо два „паметна“ стола:

Прва табела са криво исписаним адресама које треба исправити (назвао сам је Дата2). Друга табела је референтна књига, према којој треба да извршите делимичну замену подниза унутар адресе (ја сам ову табелу назвао Замене2).

Основна разлика је у томе што морате да замените само део оригиналних података – на пример, прва адреса има нетачну „Св. Петерсбург” на десно „Св. Петерсбург”, остављајући остатак адресе (поштански број, улица, кућа) какав јесте.

Готова формула ће изгледати овако (ради лакше перцепције, поделио сам је на колико редова користим други+унети):

Главни посао овде обавља стандардна Екцел текстуална функција ЗАМЕНА (ЗАМЕНА), који има 3 аргумента:

1. Изворни текст – прва крива адреса из колоне Адреса
2. Оно што тражимо – овде користимо трик са функцијом ПОГЛЕД (ПОТРАЖИТИ)са претходног начина за повлачење вредности из колоне Да пронађу, који је укључен као фрагмент у закривљену адресу.
3. Чиме заменити – на исти начин из колоне налазимо одговарајућу вредност која јој одговара Субституте.

Унесите ову формулу са Цтрл+смена+унети ни овде није потребна, иако је, у ствари, формула низа.

И јасно се види (погледајте #Н/А грешке на претходној слици) да таква формула, уз сву своју елеганцију, има неколико недостатака:

• функција СУБСТИТУТЕ разликује велика и мала слова, тако да „Спб“ у претпоследњем реду није пронађен у табели за замену. Да бисте решили овај проблем, можете користити или функцију ЗАМЕНИТ (ЗАМЕНИТИ), или прелиминарно довести обе табеле у исти регистар.
• Ако је текст у почетку тачан или у њему нема фрагмента за замену (последњи ред), онда наша формула даје грешку. Овај тренутак се може неутралисати пресретањем и заменом грешака помоћу функције ИФЕРРОР (ИФЕРРОР):

  

• Ако оригинални текст садржи неколико фрагмената из именика одједном, онда наша формула замењује само последњу (у 8. реду Лиговски «авенија« промењено у "пр-т", Али „С-Пб” on „Св. Петерсбург” не више, јер “С-Пб” је више у именику). Овај проблем се може решити поновним покретањем сопствене формуле, али већ дуж колоне Фиксно:

  
  

Није савршено и гломазно на местима, али много боље од исте ручне замене, зар не? 🙂

PS

У следећем чланку ћемо схватити како да имплементирамо такву масовну замену користећи макрое и Повер Куери.

• Како функција СУБСТИТУТЕ функционише за замену текста
  
• Проналажење тачних подударања текста помоћу функције ЕКСАЦТ
• Претрага и замена осетљива на велика и мала слова (ВЛООКУП осетљив на велика и мала слова)