Да претпоставиме дека имате листа во која, со различни степени на „директност“, се напишани првичните податоци - на пример, адреси или имиња на компании:
 |  |
Јасно се гледа дека истиот град или компанија овде е присутен во шарени варијанти, што, очигледно, ќе создаде многу проблеми при работа со овие табели во иднина. И ако размислите малку, можете да најдете многу примери за слични задачи од други области.
Сега замислете дека таквите криви податоци ви доаѓаат редовно, т.е. ова не е еднократна приказна „рачно поправете, заборави“, туку проблем на редовна основа и во голем број ќелии.
Што да се прави? Немојте рачно да го заменувате искривениот текст 100500 пати со точниот преку полето „Најди и замени“ или со кликнување Ctrl+H?
Првото нешто што ми паѓа на ум во таква ситуација е да се направи масовна замена според претходно составена референтна книга за совпаѓање на неточни и точни опции - вака:
За жал, со очигледната распространетост на таквата задача, Microsoft Excel нема едноставни вградени методи за нејзино решавање. За почеток, ајде да откриеме како да го направиме тоа со формули, без да вклучиме „тешка артилерија“ во форма на макроа во VBA или Power Query.
Случај 1. Рефус целосна замена
Да почнеме со релативно едноставен случај – ситуација кога треба да го замените стариот искривен текст со нов. целосно.
Да речеме дека имаме две табели:
Во првата – оригиналните разновидни имиња на компании. Во вториот – референтна книга за кореспонденција. Ако во името на компанијата во првата табела најдеме некој збор од колоната Да најде, тогаш треба целосно да го замените ова криво име со точното – од колоната Заменик втора табела за пребарување.
За погодност:
- Двете табели се претвораат во динамични („паметни“) со помош на кратенка на тастатурата Ctrl+T или тим Вметнете – Табела (Вметни - Табела).
- На јазичето што се појавува Изведувач (Дизајн) првата табела именувана податоции втората референтна табела – Замени.
За да ја објасниме логиката на формулата, да одиме малку од далеку.
Земајќи ја првата компанија од ќелијата А2 како пример и привремено заборавајќи на останатите компании, да се обидеме да одредиме која опција од колоната Да најде се среќава таму. За да го направите ова, изберете која било празна ќелија во слободниот дел од листот и внесете ја функцијата таму ДА НАЈДЕ (НАЈДЕТЕ):
Оваа функција одредува дали е вклучена дадената подниза (првиот аргумент се сите вредности од колоната Да најде) во изворниот текст (првата компанија од табелата со податоци) и треба да го изнесе или редниот број на знакот од кој е пронајден текстот или грешка ако поднизата не е пронајдена.
Трикот овде е што бидејќи наведовме не една, туку неколку вредности како прв аргумент, оваа функција исто така ќе врати како резултат не една вредност, туку низа од 3 елементи. Ако ја немате најновата верзија на Office 365 која поддржува динамички низи, тогаш откако ќе ја внесете оваа формула и ќе кликнете на Внесете ќе ја видите оваа низа директно на листот:
Ако имате претходни верзии на Excel, тогаш откако ќе кликнете на Внесете ќе ја видиме само првата вредност од низата со резултати, односно грешка #VALUE! (#ВРЕДНОСТ!).
Не треба да се плашите 🙂 Всушност, нашата формула работи и сè уште можете да ја видите целата низа на резултати ако ја изберете внесената функција во лентата со формули и притиснете го копчето F9(само не заборавајте да притиснете Escда се вратиме на формулата):
Резултирачката низа на резултати значи дека во оригиналното искривено име на компанијата (ГК Морозко ОАО) од сите вредности во колона Да најде го најде само вториот (Морозко), и почнувајќи од 4-тиот знак по ред.
Сега да додадеме функција на нашата формула VIEW(ПРЕБАРУВАЊЕ):
Оваа функција има три аргументи:
- Посакувана вредност - можете да користите кој било доволно голем број (главната работа е што ја надминува должината на кој било текст во изворните податоци)
- Виден_вектор – опсегот или низата каде што ја бараме саканата вредност. Еве ја претходно воведената функција ДА НАЈДЕ, што враќа низа {#VALUE!:4:#VALUE!}
- Вектор_резултати – опсегот од кој сакаме да ја вратиме вредноста доколку саканата вредност се најде во соодветната ќелија. Еве ги точните имиња од колоната Заменик нашата референтна табела.
Главната и неочигледна карактеристика овде е дека функцијата VIEW ако не постои точно совпаѓање, секогаш ја бара најблиската најмала (претходна) вредност. Затоа, со наведување на кој било голем број (на пример, 9999) како сакана вредност, ќе присилиме VIEW најдете ја ќелијата со најблискиот најмал број (4) во низата {#VALUE!:4:#VALUE!} и вратете ја соодветната вредност од векторот на резултатот, односно точното име на компанијата од колоната Заменик.
Втората нијанса е дека, технички, нашата формула е формула за низа, бидејќи функцијата ДА НАЈДЕ враќа како резултати не една, туку низа од три вредности. Но, бидејќи функцијата VIEW поддржува низи надвор од кутијата, тогаш не мора да ја внесуваме оваа формула како класична формула за низа - користејќи кратенка на тастатурата Ctrl+Префрлат+Внесете. Едноставно ќе биде доволно Внесете.
Тоа е се. Се надевам дека ја разбираш логиката.
Останува да се пренесе готовата формула во првата ќелија B2 од колоната Фиксна – и нашата задача е решена!
Се разбира, со обични (не паметни) маси, оваа формула исто така функционира одлично (само не заборавајте за клучот F4 и поправање на соодветните врски):
Случај 2. Масовно делумна замена
Овој случај е малку посложен. Повторно имаме две „паметни“ табели:
Првата табела со криво напишани адреси што треба да се коригира (ја викам Податоци2). Втората табела е референтна книга, според која треба да направите делумна замена на подниза во адресата (јас ја нареков оваа табела Замени2).
Основната разлика овде е што треба да замените само фрагмент од оригиналните податоци - на пример, првата адреса има неточна „Св. Петербург“ на десната „Св. Петербург“, оставајќи го остатокот од адресата (поштенски број, улица, куќа) како што е.
Завршената формула ќе изгледа вака (за олеснување на перцепцијата, ја поделив на колку линии користам Alt+Внесете):
Главната работа овде ја врши стандардната текстуална функција на Excel СУПСТИТУТ (ЗАМЕНА), кој има 3 аргументи:
- Изворниот текст – првата крива адреса од колоната Адреса
- Она што го бараме – овде го користиме трикот со функцијата VIEW (ПРЕБАРУВАЊЕ)од претходниот начин да се повлече вредноста од колоната Да најде, кој е вклучен како фрагмент во крива адреса.
- Со што да се замени - на ист начин ја наоѓаме точната вредност што одговара на неа од колоната Заменик.
Внесете ја оваа формула со Ctrl+Префрлат+Внесете не е потребно ниту овде, иако тоа е, всушност, формула за низа.
И јасно се гледа (види #N/A грешки на претходната слика) дека таквата формула, и покрај сета своја елеганција, има неколку недостатоци:
- функција SUBSTITUTE е осетлив на букви, така што „Spb“ во претпоследната линија не се најде во табелата за замена. За да го решите овој проблем, можете или да ја користите функцијата ЗАМЕНИТ (ЗАМЕНИ), или прелиминарно донесете ги двете табели во истиот регистар.
- Ако текстот е првично точен или во него нема фрагмент за замена (последна линија), тогаш нашата формула фрла грешка. Овој момент може да се неутрализира со пресретнување и замена на грешки користејќи ја функцијата ИФЕРОР (ИФЕРОР):
- Доколку оригиналниот текст содржи неколку фрагменти од директориумот одеднаш, тогаш нашата формула ја заменува само последната (во 8-миот ред, Лиговски «Авенија« сменето во „пр-т“, Но „S-Pb“ on „Св. Петербург“ повеќе не, затоа што „S-Pb” е повисоко во директориумот). Овој проблем може да се реши со повторно извршување на нашата сопствена формула, но веќе по должината на колоната Фиксна:
Не е совршено и незгодно на места, но многу подобро од истата рачна замена, нели? 🙂
PS
Во следната статија, ќе откриеме како да имплементираме таква голема замена користејќи макроа и Power Query.
- Како функционира функцијата SUBSTITUTE за да го замени текстот
- Наоѓање точни совпаѓања со текст користејќи ја функцијата EXACT
- Пребарување и замена со осетливи на големи букви (VLOOKUP со чувствителни на големи букви)