Да предположим, че имате списък, в който с различна степен на „праволинейност“ са написани първоначални данни – например адреси или имена на компании:
 |  |
Ясно се вижда, че един и същ град или фирма присъства тук в пъстри варианти, което очевидно ще създаде много проблеми при работата с тези таблици в бъдеще. И ако помислите малко, можете да намерите много примери за подобни задачи от други области.
Сега си представете, че такива изкривени данни идват при вас редовно, т.е. това не е еднократна история „поправи го ръчно, забрави“, а проблем на регулярна основа и в голям брой клетки.
Какво да правя? Не заменяйте ръчно кривия текст 100500 пъти с правилния чрез полето „Намиране и замяна“ или чрез щракване Ctrl+H?
Първото нещо, което идва на ум в такава ситуация, е да се направи масова замяна според предварително компилиран справочник от съвпадение на неправилни и правилни опции - така:
За съжаление, с очевидното разпространение на такава задача, Microsoft Excel няма прости вградени методи за нейното решаване. Като начало, нека разберем как да направим това с формули, без да включваме „тежка артилерия“ под формата на макроси във VBA или Power Query.
Случай 1. Масова пълна подмяна
Нека започнем с относително прост случай – ситуация, в която трябва да замените стария крив текст с нов. напълно.
Да кажем, че имаме две таблици:
В първия – оригиналните шарени имена на фирми. Във втория – справочник за кореспонденция. Ако намерим в името на фирмата в първата таблица някоя дума от колоната Да намеря, то трябва напълно да смените това криво име с правилното – от графата заместител втора справочна таблица.
За удобство:
- И двете таблици се преобразуват в динамични („умни“) с помощта на клавишна комбинация Ctrl+T или екип Вмъкване – Таблица (Вмъкване — Таблица).
- В раздела, който се показва конструктор (Дизайн) първата таблица с име Дата, а втората справочна таблица – Смените.
За да обясним логиката на формулата, нека отидем малко отдалеч.
Като вземем за пример първата компания от клетка A2 и временно забравим за останалите компании, нека се опитаме да определим коя опция от колоната Да намеря среща там. За да направите това, изберете всяка празна клетка в свободната част на листа и въведете функцията там ДА НАМЕРЯ (НАМИРАМ):
Тази функция определя дали дадения подниз е включен (първият аргумент са всички стойности от колоната Да намеря) в изходния текст (първата компания от таблицата с данни) и трябва да изведе или поредния номер на знака, от който е намерен текстът, или грешка, ако поднизът не е намерен.
Номерът тук е, че тъй като сме посочили не една, а няколко стойности като първи аргумент, тази функция също ще върне като резултат не една стойност, а масив от 3 елемента. Ако нямате най-новата версия на Office 365, която поддържа динамични масиви, след като въведете тази формула и щракнете върху Въведете ще видите този масив точно на листа:
Ако имате предишни версии на Excel, след като щракнете върху Въведете ще видим само първата стойност от масива с резултати, т.е. грешка #VALUE! (#VALUE!).
Не трябва да се страхувате 🙂 Всъщност нашата формула работи и все пак можете да видите целия масив от резултати, ако изберете въведената функция в лентата с формули и натиснете клавиша F9(само не забравяйте да натиснете Escза да се върна към формулата):
Полученият масив от резултати означава, че в оригиналното криво име на фирмата (GK Morozko OAO) от всички стойности в колона Да намеря намери само второто (Морозко), и започвайки от 4-тия пореден знак.
Сега нека добавим функция към нашата формула ВИЖТЕ(ПОГЛЕДНИ НАГОРЕ):
Тази функция има три аргумента:
- Желана стойност – можете да използвате всяко достатъчно голямо число (основното е, че то надвишава дължината на всеки текст в изходните данни)
- Разгледан_вектор – диапазонът или масивът, където търсим желаната стойност. Ето въведената по-рано функция ДА НАМЕРЯ, който връща масив {#VALUE!:4:#VALUE!}
- вектор_резултати – диапазонът, от който искаме да върнем стойността, ако желаната стойност се намери в съответната клетка. Ето правилните имена от колоната заместител нашата справочна таблица.
Основната и неочевидна характеристика тук е, че функцията ВИЖТЕ ако няма точно съвпадение, винаги търси най-близката най-малка (предишна) стойност. Следователно, като посочим произволно голямо число (например 9999) като желана стойност, ще принудим ВИЖТЕ намерете клетката с най-близкото най-малко число (4) в масива {#VALUE!:4:#VALUE!} и върнете съответната стойност от резултатния вектор, т.е. правилното име на фирмата от колоната заместител.
Вторият нюанс е, че технически нашата формула е формула за масив, защото функция ДА НАМЕРЯ връща като резултат не една, а масив от три стойности. Но тъй като функцията ВИЖТЕ поддържа масиви от кутията, тогава не е нужно да въвеждаме тази формула като класическа формула за масив – използвайки клавишна комбинация Ctrl+Превключване+Въведете. Една проста ще бъде достатъчна Въведете.
Това е всичко. Дано схванете логиката.
Остава да прехвърлите готовата формула в първата клетка B2 на колоната определен – и нашата задача е решена!
Разбира се, с обикновени (не умни) таблици тази формула също работи чудесно (само не забравяйте за ключа F4 и коригиране на съответните връзки):
Случай 2. Масова частична подмяна
Този случай е малко по-сложен. Отново имаме две „умни“ таблици:
Първата таблица с криво написани адреси, която трябва да се коригира (нарекох я Данни2). Втората таблица е справочник, според който трябва да направите частична замяна на подниз вътре в адреса (аз нарекох тази таблица Замени2).
Основната разлика тук е, че трябва да замените само фрагмент от оригиналните данни – например първият адрес има неправилен “Св. Петербург” отдясно “Св. Петербург”, оставяйки останалата част от адреса (пощенски код, улица, къща) както е.
Готовата формула ще изглежда така (за по-лесно възприемане я разделих на колко реда използвам Друг+Въведете):
Основната работа тук се извършва от стандартната текстова функция на Excel ЗАМЕСТИТЕЛ (ЗАМЕСТВАНЕ), който има 3 аргумента:
- Изходен текст – първият крив адрес от колоната Адрес
- Какво търсим – тук използваме трика с функцията ВИЖТЕ (ПОГЛЕДНИ НАГОРЕ)от предишния начин за изтегляне на стойността от колоната Да намеря, който е включен като фрагмент в извит адрес.
- С какво да заменим – по същия начин намираме правилната стойност, съответстваща на него от колоната заместител.
Въведете тази формула с Ctrl+Превключване+Въведете не е необходимо и тук, въпреки че всъщност е формула за масив.
И ясно се вижда (виж #N/A грешки в предишната снимка), че такава формула, въпреки цялата си елегантност, има няколко недостатъка:
- функция SUBSTITUTE е чувствителен към главни и малки букви, така че „Spb“ в предпоследния ред не беше намерено в таблицата за заместване. За да разрешите този проблем, можете да използвате функцията ЗАМЕНИТ (ЗАМЕНЯТ), или предварително занесете и двете таблици в един регистър.
- Ако текстът е първоначално правилен или в него няма фрагмент за замяна (последен ред), тогава нашата формула извежда грешка. Този момент може да бъде неутрализиран чрез прихващане и заместване на грешки с помощта на функцията АКО ГРЕШКА (ГРЕШКА):
- Ако оригиналният текст съдържа няколко фрагмента от директорията наведнъж, тогава нашата формула замества само последната (в 8-ия ред, Ligovsky «Булевард« променено на „пр-т“, Но „S-Pb“ on “Св. Петербург” вече не, защото „S-Pb” е по-високо в директорията). Този проблем може да бъде решен чрез повторно пускане на нашата собствена формула, но вече по колоната определен:
Не е перфектен и тромав на места, но много по-добър от същата ръчна подмяна, нали? 🙂
PS
В следващата статия ще разберем как да приложим такова масово заместване с помощта на макроси и Power Query.
- Как работи функцията SUBSTITUTE за заместване на текст
- Намиране на точни текстови съвпадения с помощта на функцията EXACT
- Чувствително към малки и главни букви търсене и заместване (чувствително към малки и главни букви VLOOKUP)