Склеивание текста по условию

Про то, как можно стремительно склеивать текст из нескольких ячеек в одну и, напротив, разбирать длинноватую текстовую строчку на составляющие я уже писал. Сейчас же давайте разглядим близкую, но чуток наиболее сложную задачку — как склеивать текст из нескольких ячеек при выполнении определенного данного условия.

Допустим, что у нас имеется база данных по клиентам, где одному наименованию компании может соответствовать несколько различных email’ов ее служащих. Наша задачка состоит в том, чтоб собрать все адреса по заглавиям компаний и сцепить их (через запятую либо точку с запятой), чтоб создать позже, к примеру, почтовую рассылку по клиентам, т.е. получить на выходе что-то схожее на:

Иными словами, нам нужен инструмент, который будет склеивать (сцеплять) текст по условию — аналог функции СУММЕСЛИ (SUMIF) , но для текста.

Метод 0. Формулой

Не весьма роскошный, зато самый обычный метод. Можно написать легкую формулу, которая будет инспектировать различается ли компания в очередной строке от предшествующей. Если не различается, то приклеиваем через запятую очередной адресок. Если различается, то «сбрасываем» накопленное, начиная поновой:

Минусы такового подхода явны: из всех ячеек приобретенного доп столбца нам необходимы лишь крайние по каждой компании (желтоватые). Если перечень большенный, то чтоб их стремительно отобрать придется добавить очередной столбец, использующий функцию ДЛСТР (LEN) , проверяющий длину скопленных строк:

Сейчас можно отфильтровать единички и скопировать нужные клейки адресов для предстоящего использования.

Метод 1. Макрофункция клейки по одному условию

Если начальный перечень не отсортирован по компаниям, то приведенная выше обычная формула не работает, но можно просто вывернуться при помощи маленький пользовательской функции на VBA. Откройте редактор Visual Basic нажатием на сочетание кнопок Alt+F11 либо при помощи клавиши Visual Basic на вкладке Разраб (Developer) . В открывшемся окне вставьте новейший пустой модуль через меню Insert — Module и скопируйте туда текст нашей функции:

Если сейчас возвратиться в Microsoft Excel, то в перечне функций (клавиша fx в строке формул либо вкладка Формулы — Вставить функцию) можно будет отыскать нашу функцию MergeIf в группы Определенные юзером (User Defined) . Аргументы у функции последующие:

Метод 2. Сцепить текст по неточному условию

Если поменять в 13-й строке нашего макроса 1-ый символ = на оператор ориентировочного совпадения Like, то можно будет производить клейку по неточному совпадению начальных данных с аспектом отбора. К примеру, если заглавие компании быть может записано в различных вариантах, то мы можем одной функцией проверить и собрать их все:

Поддерживаются обычные спецсимволы подстановки:

• звездочка (*) — обозначает хоть какое количество всех знаков (в т.ч. и их отсутствие)
• вопросительный символ (?) — обозначает один хоть какой знак
• сетка (#) — обозначает одну всякую цифру (0-9)

По дефлоту оператор Like регистрочувствительный, т.е. осознает, к примеру, «Орион» и «оРиОн» как различные компании. Чтоб не учесть регистр можно добавить в самое начало модуля в редакторе Visual Basic строку Option Compare Text, которая переключит Like в режим, когда он невосприимчив к регистру.

Таковым образом можно составлять очень сложные маски для проверки критерий, к примеру:

• ?1##??777RUS — подборка по всем авто номерам 777 региона, начинающимся с 1
• ООО* — все компании, заглавие которых начинается на ООО
• ##7## — все продукты с пятизначным цифровым кодом, где 3-я цифра 7
• . — все наименования из 5 букв и т.д.

Метод 3. Макрофункция клейки текста по двум условиям

В работе может повстречаться задачка, когда сцеплять текст необходимо больше, чем по одному условию. К примеру представим, что в нашей предшествующей таблице добавился очередной столбец с городом и склеивание необходимо проводить не только лишь для данной компании, но еще и для данного городка. В этом случае нашу функцию придется незначительно модернизировать, добавив к ней проверку еще 1-го спектра:

Применяться она будет совсем аналогично — лишь аргументов сейчас необходимо указывать больше:

Метод 4. Группировка и {склейка} в Power Query

Решить делему можно и без программирования на VBA, если применять бесплатную надстройку Power Query. Для Excel 2010-2013 ее можно скачать тут, а в Excel 2016 она уже встроена по дефлоту. Последовательность действий будет последующей:

Power Query не умеет работать с обыкновенными таблицами, потому первым шагом превратим нашу таблицу в «умную». Для этого ее необходимо выделить и надавить сочетание Ctrl + T либо избрать на вкладке Основная — Форматировать как таблицу (Home — Format as Table) . На показавшейся потом вкладке Конструктор (Design) можно задать имя таблицы (я оставил обычное Таблица1):

Сейчас загрузим нашу таблицу в надстройку Power Query. Для этого на вкладке Данные (если у вас Excel 2016) либо на вкладке Power Query (если у вас Excel 2010-2013) нажимаем Из таблицы (Data — From Table) :

В открывшемся окне редактора запросов выделяем щелчком по заголовку столбец Компания и сверху нажимаем клавишу Группировать (Group By) . Вводим имя новейшего столбца и тип операции в группировке — Все строчки (All Rows) :

Нажимаем ОК и получаем для каждой компании мини-таблицу сгруппированных значений. Содержимое таблиц отлично видно, если щелкать левой клавишей мыши в белоснежный фон ячеек (не в текст!) в получившемся столбце:

Сейчас добавим очередной столбец, где при помощи функции склеим через запятую содержимое столбцов Адресок в каждой из мини-таблиц. Для этого на вкладке Добавить столбец нажимаем Пользовательский столбец (Add column — Custom column) и в показавшемся окне вводим имя новейшего столбца и формулу сцепки на интегрированном в Power Query языке М:

Направьте внимание, что все М-функции регистрочувствительные (в отличие от Excel). Опосля нажатия на ОК получаем новейший столбец со склееными адресами:

Осталось удалить ненадобный уже столбец ТаблАдресов (правой клавишей мыши по заголовку — Удалить столбец) и выгрузить результаты на лист, нажав на вкладке Основная — Закрыть и загрузить (Home — Close and load) :

Принципиальный аспект : в отличие от прошлых методов (функций), таблицы из Power Query не обновляются автоматом. Если в будущем произойдут какие-либо конфигурации в начальных данных, то необходимо будет щелкнуть правой клавишей в хоть какое пространство таблицы результатов и избрать команду Обновить (Refresh) .