Содержание:
Я уже писал о том, как можно быстро склеить текст из нескольких ячеек в одну и, наоборот, разобрать длинную текстовую строку на составляющие. Теперь рассмотрим близкую, но чуть более сложную задачу — как склеить текст из нескольких ячеек при выполнении определенного заданного условия.
Допустим, у нас есть база данных клиентов, где одному названию компании может соответствовать несколько разных адресов электронной почты ее сотрудников. Наша задача — собрать все адреса по названиям компаний и объединить их (разделив запятыми или точками с запятой), чтобы составить, например, список рассылки для клиентов, т.е. получить на выходе что-то вроде:
Другими словами, нам нужен инструмент, который будет склеивать (связывать) текст по условию — аналог функции СУММЕСЛИ (СУММЕСЛИ), но для текста.
Способ 0. Формула
Не очень элегантный, но самый простой способ. Вы можете написать простую формулу, которая будет проверять, отличается ли компания в следующей строке от предыдущей. Если не отличается, то приклеиваем следующий адрес через запятую. Если оно отличается, то «сбрасываем» накопленное, начиная заново:
Недостатки такого подхода очевидны: из всех полученных ячеек дополнительного столбца нам нужны только последние для каждой компании (желтые). Если список большой, то для их быстрого выбора придется добавить еще один столбец с помощью функции ДЛСТР (LEN), проверяя длину накопленных строк:
Теперь вы можете отфильтровать нужные и скопировать нужную адресную склейку для дальнейшего использования.
Способ 1. Макрофункция склейки по одному условию.
Если исходный список не отсортирован по компаниям, то приведенная выше простая формула не работает, но вы можете легко обойтись небольшой специальной функцией в VBA. Откройте редактор Visual Basic, нажав сочетание клавиш. Alt + F11 или с помощью кнопки Визуальный Бейсик таб разработчик (Разработчик). В открывшемся окне вставьте новый пустой модуль через меню Вставка – Модуль и скопируем туда текст нашей функции:
Функция MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " склейки не равны друг другу - выходим с ошибкой Если 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 в строке формул или вкладке Формулы — функция вставки) можно будет найти нашу функцию СлияниеЕсли в категории User Defined (Определяемые пользователем). Аргументы функции следующие:
Способ 2. Объединить текст по неточному условию
Если мы заменим первый символ в 13-й строке нашего макроса = к оператору приблизительного соответствия Подобно, то можно будет осуществлять склейку по неточному совпадению исходных данных с критерием выбора. Например, если название компании можно написать в разных вариантах, то мы можем проверить и собрать их все одной функцией:
Поддерживаются стандартные подстановочные знаки:
- звездочка (*) – обозначает любое количество любых символов (в том числе их отсутствие)
- вопросительный знак (?) – обозначает любой одиночный символ
- знак фунта (#) – обозначает любую одну цифру (0–9).
По умолчанию оператор Like чувствителен к регистру, т.е. понимает, например, «Орион» и «orion» как разные компании. Чтобы игнорировать регистр, можно добавить строку в самом начале модуля в редакторе Visual Basic Опция Сравнить текст, который переключит Like на нечувствительность к регистру.
Таким способом можно составлять очень сложные маски проверки условий, например:
- ?1##??777RUS – выбор всех номеров 777 региона, начиная с 1
- ООО* – все компании, название которых начинается с ООО
- ##7## – все товары с пятизначным цифровым кодом, где третья цифра – 7.
- ????? – все имена из пяти букв и т. д.
Способ 3. Макрос-функция для склейки текста при двух условиях
В работе может возникнуть проблема, когда нужно связать текст более чем одним условием. Например, представим, что в нашу предыдущую таблицу добавлен еще один столбец с городом, и склейку нужно проводить не только по данной компании, но и по данному городу. В этом случае нашу функцию придется немного модернизировать, добавив в нее еще одну проверку диапазона:
Функция MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'символы-разделители (можно заменить пробелом или ; и т. д.) д.) 'если диапазоны проверки и склейки не равны друг другу, выходим с ошибкой If SearchRange1.Count <> TextRange.Count Или SearchRange2.Count <> TextRange.Count then MergeIfs = CVERr(xlErrRef) Exit Function 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)) End Function
Применится он будет точно так же — только аргументов теперь нужно указать больше:
Способ 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 для проверки текста по маске