О том, как соединить текст из разных ячеек на основании условий.
Очень часто при работе с выгрузками данных из баз приходится упорядочивать и систематизировать информацию, соединять несколько текстов в один.
Например, существует перечень поставщиков сока для крупной компании. У каждого поставщика имеется несколько наименований продукции.
Поставщик | Наименование сока |
ООО «Крошка-Морошка» | «Сок Ванили» |
ООО «Икар-Макар» | «Ореховый настой» |
ООО «Крошка-Морошка» | «Апельсинка» |
ООО «Крошка-Морошка» | Морс «Старый Ельник» |
ООО «Крошка-Морошка» | «Тыквовый» |
ООО «Крошка-Морошка» | «Огуречный с сиропом» |
ООО «Икар-Макар» | «Яблочный с мякотью» |
ООО «Икар-Макар» | «Груша-дичка» |
ПАО «Старгарден» | «Морс из клюквы» |
ООО «Крошка-Морошка» | Сок «Натуральный ананас» |
ПАО «Старгарден» | Напиток газированный «Лаванда» |
ООО «Крошка-Морошка» | Настой морошки |
ПАО «Старгарден» | Напиток газированный «Абрикос» |
Нам необходимо выбрать и записать в одну строку все названия продукции, относящиеся к определенному поставщику, соединить их.
Для реализации такого действия необходимо воспользоваться сцепкой текста по заданным условиям, такой функции нет, поэтому создадим специальный макрос добавляющий функцию условной сцепки значений.
Чтобы добавить макрос, следует выполнить следующие действия:
- Войти в панель разработчика;
- Нажать «Просмотр кода»;
- В окне project кликнуть правой кнопкой мыши и вставить модуль;
- Открыть просмотр кода нового модуля;
- Скопировать приведенный ниже код макроса VBA и вставить его в модуль;
- Сохранить файл.
Код макроса VBA :
Function Scepka(DiapazonScepki As Range, DiapazonPoiska As Range, Uslovie As String)
Dim Delitel As String, i As Long , OutText As String
‘назначаем переменные для работы макроса DiapazonScepki — это диапазон с текстом, который следует сцепить; DiapazonPoiska – это диапазон условий для сравнения; Uslovie – это то условие, которое мы ищем; Delitel – символ делителя.
Delitel = «, » ‘указываем разделитель (символ, который будет разделять сцепленный текст, можно поставить пробел или пустоту «» – тогда текст сольется в одно слово)
If DiapazonPoiska.Count <> DiapazonScepki.Count Then
Scepka = CVErr(xlErrRef)
Exit Function
End If ‘если диапазоны с данными для проверки и для сцепки отличаются по длине – функция выдает ошибку и закрывается
For i = 1 To DiapazonPoiska.Cells.Count
If DiapazonPoiska.Cells(i) Like Uslovie And Len(DiapazonScepki.Cells(i)) > 0 Then OutText = OutText & DiapazonScepki.Cells(i) & Delitel
Next i ‘сверяем ячейки между собой и присваиваем переменной OutText подходящие по условиям текстовые значения. Для точного совпадения оператор Like следует заменить на знак «=».
Scepka = Left(OutText, Len(OutText) — Len(Delitel))
End Function
Теперь когда функция условной сцепки текста добавлена в книгу Excel при помощи макроса , ее можно использовать для обработки текста следующим образом:
- Поставить курсор в ячейку, предназначенную для внесения соединенного (сцепленного) текста.
- Активировать мастер функций.
- Среди функций «Определенных пользователем» выбрать Scepka.
- В поле DiapazonScepki внести адреса ячеек, которые будут соединяться
- В поле DiapazonPoiska внести адреса ячеек, по которым будет произведен поиск (сравнение).
- В поле Uslovie внести условие поиска.
- Нажать «Ок»