содржина
Веќе пишував за тоа како можете брзо да залепите текст од неколку ќелии во една и, обратно, да анализирате долга текстуална низа во компоненти. Сега да погледнеме една блиска, но малку посложена задача – како да се залепи текст од неколку ќелии кога е исполнет одреден одреден услов.
Да речеме дека имаме база на податоци за клиенти, каде што едно име на компанија може да одговара на неколку различни е-пошта на нејзините вработени. Наша задача е да ги собереме сите адреси по имиња на компании и да ги споиме (одделени со запирки или точка-запирка) за да направиме, на пример, мејлинг листа за клиентите, односно да добиеме излез нешто како:
Со други зборови, потребна ни е алатка која ќе го залепи (поврзе) текстот според условот – аналог на функцијата СУММЕСЛИ (SUMIF), но за текст.
Метод 0. Формула
Не многу елегантен, но најлесниот начин. Можете да напишете едноставна формула која ќе провери дали компанијата во следниот ред се разликува од претходната. Ако не се разликува, тогаш залепете ја следната адреса одделена со запирка. Ако се разликува, тогаш го „ресетираме“ акумулираното, почнувајќи одново:
Недостатоците на овој пристап се очигледни: од сите ќелии од добиената дополнителна колона, потребни ни се само последните за секоја компанија (жолта). Ако списокот е голем, тогаш за брзо да ги изберете, ќе треба да додадете друга колона користејќи ја функцијата DLSTR (ЛЕН), проверка на должината на акумулираните жици:
Сега можете да ги филтрирате оние и да го копирате потребното лепење за адреса за понатамошна употреба.
Метод 1. Макрофункција на лепење по еден услов
Ако оригиналниот список не е подреден по компанија, тогаш горната едноставна формула не функционира, но лесно можете да се заобиколите со мала приспособена функција во VBA. Отворете го уредувачот на Visual Basic со притискање на кратенка на тастатурата Alt + F11 или користејќи го копчето Visual Basic табот инвеститорот (програмер). Во прозорецот што се отвора, вметнете нов празен модул низ менито Вметни – Модул и копирајте го текстот на нашата функција таму:
Функција MergeIf(TextRange as Range, Search Range as Range, Condition as String) Dim Delimeter as String, i As Long Delimeter = ", " лепењето не се еднакви едни на други - излегуваме со грешка ако SearchRange.Count <> TextRange.Count Потоа MergeIf = CVERr(xlErrRef) Излезете од функцијата Крај Ако 'поминете низ сите ќелии, проверете ја состојбата и соберете го текстот во променливата OutText For i = 1 To Search Range. Cells.Count If SearchRange.Cells(i) Like Condition then OutText = OutText & TextRange.Cells(i) & Delimeter Следно ги прикажувам резултатите без последен разграничувач MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) Крај функција
Ако сега се вратите на Microsoft Excel, тогаш во списокот со функции (копче fx во лентата со формула или табот Формули – Вметнете функција) ќе биде можно да се најде нашата функција MergeIf во категоријата Дефинирано од корисник (Кориснички дефинирано). Аргументите на функцијата се како што следува:
Метод 2. Поврзете текст по неточна состојба
Ако го замениме првиот знак во 13-тата линија од нашето макро = до операторот за приближно совпаѓање Допаѓа, тогаш ќе биде можно да се изврши лепење со неточно совпаѓање на првичните податоци со критериумот за избор. На пример, ако името на компанијата може да се напише во различни варијанти, тогаш можеме да ги провериме и собереме сите со една функција:
Поддржани се стандардни џокери:
- ѕвездичка (*) - означува кој било број на знаци (вклучувајќи го и нивното отсуство)
- прашалник (?) - означува кој било знак
- Знак за фунта (#) - значи која било една цифра (0-9)
Стандардно, операторот Like е осетлив на букви, т.е. ги разбира, на пример, „Orion“ и „orion“ како различни компании. За да игнорирате букви, можете да ја додадете линијата на самиот почеток на модулот во уредникот на Visual Basic Опција Споредете текст, што ќе се префрли Like да биде без чувствителност на букви.
На овој начин, можете да составите многу сложени маски за проверка на условите, на пример:
- ?1##??777RUS – избор на сите регистарски таблички од регионот 777, почнувајќи од 1
- LLC* – сите компании чие име започнува со ДОО
- ##7## – сите производи со петцифрена дигитална шифра, каде третата цифра е 7
- ????? – сите имиња на пет букви итн.
Метод 3. Макро функција за лепење текст под два услови
Во делото може да има проблем кога треба да го поврзете текстот повеќе од еден услов. На пример, да замислиме дека во нашата претходна табела е додадена уште една колона со градот и лепењето треба да се врши не само за дадена компанија, туку и за даден град. Во овој случај, нашата функција ќе треба малку да се модернизира со додавање на друга проверка на опсегот:
Функција MergeIfs (TextRange како опсег, Search Range1 како опсег, Condition1 as String, Search Range2 како опсег, Condition2 as String) Затемнето разграничување како стринг, i As Long Delimeter = ", " 'разграничувачки знаци (може да се заменат со празно место или ; итн.) д.) 'ако опсезите за валидација и лепење не се еднакви еден со друг, излезете со грешка Ако SearchRange1.Count <> TextRange.Count Или SearchRange2.Count <> TextRange.Count Потоа MergeIfs = CVERr(xlErrRef) Излезете од функцијата Крај ако 'поминете низ сите ќелии, проверете ги сите услови и соберете го текстот во променливата OutText For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 Потоа OutText = OutText & TextRange.Cells(i) & Delimeter End If Next ги прикажувам резултатите без последен разграничувач 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) кликнете Од масата (Податоци - од табела):
Во прозорецот за уредувач на прашања што се отвора, изберете ја колоната со кликнување на заглавието компанијата и притиснете го копчето погоре група (Група по). Внесете го името на новата колона и типот на операција во групирањето – Сите линии (Сите редови):
Кликнете ОК и добиваме мини-табела со групирани вредности за секоја компанија. Содржината на табелите е јасно видлива ако кликнете со левото копче на белата позадина на ќелиите (не на текстот!) во колоната што произлегува:
Сега да додадеме уште една колона, каде што, користејќи ја функцијата, ја лепиме содржината на колоните Адреса во секоја од мини-табелите, одделени со запирки. За да го направите ова, на јазичето Додај колона притискаме Прилагодена колона (Додај колона - Прилагодена колона) и во прозорецот што се појавува, внесете го името на новата колона и формулата за спојување на јазикот М вграден во Power Query:
Забележете дека сите M-функции се осетливи на букви (за разлика од Excel). Откако ќе кликнете на OK добиваме нова колона со залепени адреси:
Останува да се отстрани веќе непотребната колона Адреси на табели (десен клик на насловот) Избриши колона) и прикачете ги резултатите на листот со кликнување на јазичето Дома — Затворете и преземете (Дома - Затвори и вчитај):
Важна нијанса: За разлика од претходните методи (функции), табелите од Power Query не се ажурираат автоматски. Ако во иднина има какви било промени во изворните податоци, тогаш ќе треба да кликнете со десното копче на кое било место во табелата со резултати и да ја изберете командата Ажурирајте и зачувајте (Освежи).
- Како да се подели долга текстуална низа на делови
- Неколку начини за лепење текст од различни ќелии во една
- Користење на операторот Like за тестирање текст во однос на маска