Съдържание
Вече писах за това как можете бързо да залепите текст от няколко клетки в една и, обратно, да анализирате дълъг текстов низ на компоненти. Сега нека разгледаме една близка, но малко по-сложна задача - как да залепим текст от няколко клетки, когато е изпълнено определено условие.
Да кажем, че имаме база данни от клиенти, където едно име на фирма може да съответства на няколко различни имейла на нейните служители. Нашата задача е да съберем всички адреси по имена на фирми и да ги свържем (разделени със запетаи или точка и запетая), за да направим например пощенски списък за клиенти, т.е. да получим нещо като изход:
С други думи, имаме нужда от инструмент, който ще слепи (свърже) текста според условието – аналог на функцията СУМЕСЛИ (SUMIF), но за текст.
Метод 0. Формула
Не много елегантен, но най-лесният начин. Можете да напишете проста формула, която ще провери дали компанията в следващия ред се различава от предишната. Ако не се различава, залепете следващия адрес, разделен със запетая. Ако се различава, тогава „нулираме“ натрупаното, започвайки отново:
Недостатъците на този подход са очевидни: от всички клетки на получената допълнителна колона са ни необходими само последните за всяка компания (жълто). Ако списъкът е голям, тогава за да ги изберете бързо, ще трябва да добавите друга колона с помощта на функцията DLSTR (LEN), проверявайки дължината на натрупаните низове:
Сега можете да филтрирате тези и да копирате необходимото залепване на адреси за по-нататъшна употреба.
Метод 1. Макрофункция на залепване по едно условие
Ако оригиналният списък не е сортиран по компания, тогава горната проста формула не работи, но можете лесно да се придвижвате с малка персонализирана функция във VBA. Отворете редактора на Visual Basic, като натиснете клавишна комбинация Alt + F11 или с помощта на бутона Visual Basic етикет предприемач (Разработчик). В прозореца, който се отваря, вмъкнете нов празен модул през менюто Вмъкване – Модул и копирайте текста на нашата функция там:
Функция MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " залепванията не са равни едно на друго - излизаме с грешка If SearchRange.Count <> TextRange.Count След това MergeIf = CVErr(xlErrRef) Изход от функцията End If 'преминете през всички клетки, проверете условието и съберете текста в променливата OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'показвам резултати без последен разделител MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End функция
Ако сега се върнете към Microsoft Excel, тогава в списъка с функции (бутон fx в лентата с формули или в раздела Формули – Вмъкване на функция) ще бъде възможно да се намери нашата функция MergeIf в категория Потребителски дефиниран (дефиниран от потребителя). Аргументите на функцията са както следва:
Метод 2. Свързване на текст по неточно условие
Ако заменим първия знак в 13-ия ред на нашия макрос = към оператора за приблизително съответствие като, тогава ще бъде възможно да се извърши залепване чрез неточно съвпадение на първоначалните данни с критерия за избор. Например, ако името на компанията може да бъде написано в различни варианти, тогава можем да ги проверим и съберем всички с една функция:
Поддържат се стандартни заместващи знаци:
- звездичка (*) – обозначава произволен брой знаци (включително липсата им)
- въпросителен знак (?) – означава произволен отделен знак
- знак за паунд (#) – означава всяка една цифра (0-9)
По подразбиране операторът Like е чувствителен към главни и малки букви, т.е. разбира например „Orion“ и „orion“ като различни компании. За да игнорирате регистъра на буквите, можете да добавите реда в самото начало на модула в редактора на Visual Basic Опция Сравнете текст, което ще превключи Like на нечувствителен към главни и малки букви.
По този начин можете да съставите много сложни маски за проверка на условията, например:
- ?1##??777RUS – избор на всички регистрационни табели от региона 777, започващи с 1
- LLC* – всички компании, чието име започва с LLC
- ##7## – всички продукти с петцифрен цифров код, като третата цифра е 7
- ????? – всички имена от пет букви и т.н.
Метод 3. Макро функция за залепване на текст при две условия
В работата може да има проблем, когато трябва да свържете текста с повече от едно условие. Например, нека си представим, че в нашата предишна таблица е добавена още една колона с града и залепването трябва да се извърши не само за дадена компания, но и за даден град. В този случай нашата функция ще трябва да бъде леко модернизирана, като добавим още една проверка на диапазона към нея:
Функция MergeIfs(TextRange като диапазон, SearchRange1 като диапазон, Condition1 като низ, SearchRange2 като диапазон, Condition2 като низ) Дим разделител като низ, i като дълъг разделител = ", " 'знаци за разделител (могат да бъдат заменени с интервал или ; и т.н.) д.) 'ако диапазоните на валидиране и залепване не са равни един на друг, излезте с грешка If SearchRange1.Count <> TextRange.Count Or SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Изход от функцията End If 'преминете през всички клетки, проверете всички условия и съберете текста в променливата OutText For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 Then OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'показва резултати без последен разделител MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) Крайна функция
Ще се прилага по абсолютно същия начин – само аргументите трябва да бъдат посочени повече:
Метод 4. Групиране и залепване в Power Query
Можете да разрешите проблема без програмиране във VBA, ако използвате безплатната добавка Power Query. За Excel 2010-2013 може да бъде изтеглен от тук, а в Excel 2016 вече е вграден по подразбиране. Последователността на действията ще бъде както следва:
Power Query не знае как да работи с обикновени таблици, така че първата стъпка е да превърнем нашата таблица в „умна“. За да направите това, изберете го и натиснете комбинацията Ctrl+T или изберете от раздела Начало – Форматиране като таблица (Начало — Форматиране като таблица). В раздела, който след това се появява конструктор (Дизайн) можете да зададете името на таблицата (оставих стандарта Таблица 1):
Сега нека заредим нашата таблица в добавката Power Query. За да направите това, в раздела Дата (ако имате Excel 2016) или в раздела Power Query (ако имате Excel 2010-2013) щракнете върху От масата (Данни — от таблицата):
В прозореца на редактора на заявки, който се отваря, изберете колоната, като щракнете върху заглавката За Компанията и натиснете бутона по-горе група (Групирай по). Въведете името на новата колона и вида на операцията в групирането – Всички линии (Всички редове):
Щракнете върху OK и ще получим мини-таблица с групирани стойности за всяка компания. Съдържанието на таблиците се вижда ясно, ако щракнете с левия бутон върху белия фон на клетките (не върху текста!) в получената колона:
Сега нека добавим още една колона, където с помощта на функцията залепваме съдържанието на колоните Адрес във всяка от мини-таблиците, разделени със запетаи. За да направите това, в раздела Добавете колона натискаме Персонализирана колона (Добавяне на колона — Персонализирана колона) и в прозореца, който се показва, въведете името на новата колона и формулата за свързване на езика M, вграден в Power Query:
Обърнете внимание, че всички M-функции са чувствителни към главни и малки букви (за разлика от Excel). След като щракнете върху OK получаваме нова колона със залепени адреси:
Остава да премахнете вече ненужната колона Адреси на таблици (десен клик върху заглавието) Изтриване на колона) и качете резултатите в листа, като щракнете върху раздела Начало — Затваряне и изтегляне (Начало — Затваряне и зареждане):
Важен нюанс: За разлика от предишните методи (функции), таблиците от Power Query не се актуализират автоматично. Ако в бъдеще ще има промени в изходните данни, тогава ще трябва да щракнете с десния бутон навсякъде в таблицата с резултати и да изберете командата Актуализиране и запазване (Опресняване).
- Как да разделим дълъг текстов низ на части
- Няколко начина за слепване на текст от различни клетки в една
- Използване на оператора Like за тестване на текст спрямо маска