Садржај
Већ сам писао о томе како можете брзо залепити текст из неколико ћелија у једну и, обрнуто, рашчланити дуги текстуални низ на компоненте. Погледајмо сада блиски, али мало сложенији задатак – како залепити текст из неколико ћелија када је испуњен одређени услов.
Рецимо да имамо базу података купаца, где једно име компаније може да одговара неколико различитих мејлова њених запослених. Наш задатак је да прикупимо све адресе по називима компанија и повежемо их (одвојене зарезима или тачком и зарезом) како бисмо направили, на пример, маилинг листу за купце, односно добили излаз нешто попут:
Другим речима, потребан нам је алат који ће залепити (повезати) текст према услову – аналог функције СУММЕСЛИ (СУМИФ), али за текст.
Метод 0. Формула
Није баш елегантан, али најлакши начин. Можете написати једноставну формулу која ће проверити да ли се предузеће у следећем реду разликује од претходног. Ако се не разликује, онда залепите следећу адресу одвојену зарезом. Ако се разликује, онда „ресетујемо“ акумулирано, почевши поново:
Недостаци овог приступа су очигледни: од свих ћелија добијене додатне колоне потребне су нам само последње за свако предузеће (жуто). Ако је листа велика, да бисте их брзо изабрали, мораћете да додате још једну колону помоћу функције ДЛСТР (ЛЕН), провера дужине акумулираних стрингова:
Сада можете филтрирати оне и копирати потребно лепљење адресе за даљу употребу.
Метод 1. Макрофункција лепљења једним условом
Ако оригинална листа није сортирана по компанији, онда горња једноставна формула не функционише, али можете лако да се заобиђете са малом прилагођеном функцијом у ВБА. Отворите Висуал Басиц Едитор притиском на пречицу на тастатури Алт + ФКСНУМКС или помоћу дугмета Висуал Басиц табулатор развијач (Програмер). У прозору који се отвори убаците нови празан модул кроз мени Инсерт – Модул и копирајте текст наше функције тамо:
Функција МергеИф(ТектРанге Ас Ранге, СеарцхРанге Ас Ранге, Цондитион Ас Стринг) Дим Делиметер Ас Стринг, и Ас Лонг Делиметер = ", " лепљења нису једнака једно другом - излазимо са грешком Иф СеарцхРанге.Цоунт <> ТектРанге.Цоунт Затим МергеИф = ЦВЕРр(клЕррРеф) Изађите из функције Крај Ако 'прођите кроз све ћелије, проверите услов и прикупите текст у променљивој ОутТект Фор и = 1 То СеарцхРанге. Целлс.Цоунт Иф СеарцхРанге.Целлс(и) Лике Цондитион Тхен ОутТект = ОутТект & ТектРанге.Целлс(и) & Делиметер Нект и 'приказује резултате без последњег граничника МергеИф = Лефт(ОутТект, Лен(ОутТект) - Лен(Делиметар)) Енд функција
Ако се сада вратите у Мицрософт Екцел, онда на листи функција (дугме fx на траци формуле или на картици Формуле – Уметање функције) биће могуће пронаћи нашу функцију МергеИф у категорији Кориснички дефинисано (кориснички дефинисано). Аргументи функције су следећи:
Метод 2. Спојите текст нетачним условом
Ако заменимо први знак у 13. реду нашег макроа = оператору приближног подударања као, тада ће бити могуће извршити лепљење нетачним подударањем почетних података са критеријумом избора. На пример, ако назив компаније може бити написан у различитим варијантама, онда можемо све да проверимо и прикупимо помоћу једне функције:
Подржани су стандардни џокер знакови:
- звездица (*) – означава било који број знакова (укључујући њихово одсуство)
- упитник (?) – означава било који појединачни знак
- знак фунте (#) – означава било коју цифру (0-9)
Оператор Лике подразумевано је осетљив на велика и мала слова, односно разуме, на пример, „Орион“ и „орион“ као различите компаније. Да бисте занемарили велика и мала слова, можете додати ред на самом почетку модула у уређивачу Висуал Басиц-а Опција Упореди текст, који ће пребацити Лике на велика и мала слова.
На овај начин можете саставити веома сложене маске за проверу услова, на пример:
- ?1##??777РУС – избор свих регистарских таблица региона 777, почевши од 1
- ДОО* – све компаније чије име почиње са ДОО
- ##7## – сви производи са петоцифреним дигиталним кодом, где је трећа цифра 7
- ????? – сви називи од пет слова итд.
Метод 3. Макро функција за лепљење текста под два услова
У раду може доћи до проблема када треба да повежете текст са више услова. На пример, замислимо да је у нашој претходној табели додата још једна колона са градом, а лепљење треба да се изврши не само за дату компанију, већ и за дати град. У овом случају, наша функција ће морати да се мало модернизује додавањем још једне провере опсега:
Функција МергеИфс(ТектРанге Ас Ранге, СеарцхРанге1 Ас Ранге, Цондитион1 Ас Стринг, СеарцхРанге2 Ас Ранге, Цондитион2 Ас Стринг) Дим Делиметер Ас Стринг, и Ас Лонг Делиметер = ", " 'знакови за раздвајање (могу се заменити размаком или ; итд.) е.) 'ако опсези валидације и лепљења нису једнаки један другом, изађите са грешком Ако СеарцхРанге1.Цоунт <> ТектРанге.Цоунт Или СеарцхРанге2.Цоунт <> ТектРанге.Цоунт Онда МергеИфс = ЦВЕРр(клЕррРеф) Изађите из функције Енд Иф 'прођите кроз све ћелије, проверите све услове и сакупите текст у променљиву ОутТект Фор и = 1 То СеарцхРанге1.Целлс.Цоунт Ако СеарцхРанге1.Целлс(и) = Цондитион1 и СеарцхРанге2.Целлс(и) = Цондитион2 Онда ОутТект = ОутТект & ТектРанге.Целлс(и) & Делиметер Енд Иф Нект и 'приказује резултате без последњег граничника МергеИфс = Лефт(ОутТект, Лен(ОутТект) - Лен(Делиметер)) Енд Функција
Биће примењен на потпуно исти начин – само аргументе сада треба више специфицирати:
Метод 4. Груписање и лепљење у Повер Куерију
Проблем можете решити без програмирања у ВБА ако користите бесплатни додатак Повер Куери. За Екцел 2010-2013 може се преузети овде, а у Екцел 2016 је већ подразумевано уграђен. Редослед радњи ће бити следећи:
Повер Куери не зна да ради са редовним табелама, па је први корак да нашу табелу претворимо у „паметну“. Да бисте то урадили, изаберите га и притисните комбинацију Цтрл+T или изаберите са картице Почетна – Формат као табела (Почетна — Формат као табела). На картици која се тада појављује Конструктор (Дизајн) можете подесити име табеле (напустио сам стандардни Табела КСНУМКС):
Сада учитајмо нашу табелу у Повер Куери додатак. Да бисте то урадили, на картици Датум (ако имате Екцел 2016) или на картици Повер Куери (ако имате Екцел 2010-2013) кликните Са стола (Подаци — из табеле):
У прозору уређивача упита који се отвори изаберите колону кликом на заглавље Koмпaниja и притисните дугме изнад Група (Група од). Унесите назив нове колоне и тип операције у груписању – Све линије (Сви редови):
Кликните ОК и добићемо мини табелу груписаних вредности за сваку компанију. Садржај табела је јасно видљив ако кликнете левим тастером миша на белу позадину ћелија (не на текст!) у резултујућој колони:
Сада додајмо још једну колону, где, користећи функцију, лепимо садржај колона Аддресс у сваку од мини табела, одвојених зарезима. Да бисте то урадили, на картици Додај колону притиснемо Прилагођена колона (Додај колону — Прилагођена колона) и у прозору који се појави унесите назив нове колоне и формулу за спајање на М језику уграђеном у Повер Куери:
Имајте на уму да су све М-функције осетљиве на велика и мала слова (за разлику од Екцел-а). Након што кликнете на OK добијамо нову колону са залепљеним адресама:
Остаје да уклоните већ непотребну колону ТаблеАддрессес (десни клик на наслов) Избриши колону) и отпремите резултате на листу кликом на картицу Почетна — Затворите и преузмите (Почетна — Затвори и учитај):
Важна нијанса: За разлику од претходних метода (функција), табеле из Повер Куерија се не ажурирају аутоматски. Ако у будућности буде било каквих промена у изворним подацима, мораћете да кликнете десним тастером миша било где у табели резултата и изаберете команду Ажурирајте и сачувајте (Освјежи).
- Како поделити дугачки текстуални низ на делове
- Неколико начина за лепљење текста из различитих ћелија у једну
- Коришћење оператора Лике за тестирање текста у односу на маску