Сцепить текст массово

Как объединить текст при помощи пользовательской функции в MS Excel. А так же сделать умную сцепку с условиями. Функции join и filter.

Задача

Пользователи Экселя издавна страдали от недостатка пользовательских функций с условиями. С новыми версиями Экселя начали появляться новые полезные функции: СУММЕСЛИМН, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИМН. Но так и не появилась функция, которая соберет текст из нескольких ячеек в одну, причем сделает это элегантно.

Сейчас на все случаи жизни существует функция СЦЕПИТЬ, а для более искушенных пользователей, знак амперсанд:

&

Таким образом, приходится как то справляться с задачами, идя в обход. Например, нам нужно получить из этого это ↓:

 

Итак, вот обрисовалась такая задача: собрать из множества клеток Эксель данные в одну, используя любой заданный разделитель. В примере выше используется разделитель "-". При этом функция проста в использовании. Вот она:

=join(A2:A15;"-")

Если зайти в саму клетку, то увидишь такое:

 

Решение поставленной задачи

Решение данной задачи -- использовать свою пользовательскую функцию. Открой редактор Visual Basic в Экселе, нажав Alt+F11, вставь новый модуль и в новый модуль вставь следующий код:

Function join(rng As Range, Optional Delim As String = " ") As String
    Dim c As Range
    Set rng = Intersect(rng, ActiveSheet.UsedRange)
      For Each c In rng
            join = join & c.Value & Delim
      Next c
    If Len(join) > 0 Then
    join = Left(join, Len(join) - Len(Delim))
    End If
End Function

Теперь еще раз по порядку, как это работает:

1. Ты вносишь код в книгу и код с функцией работает внутри этой книги. Чтобы код работал во всех книгах Эксель, необходимо вставить код в надстройку Экселя, сохранить книгу с кодом как надстройку-книгу с расширением XLAM и добавить себе в надстройки.
2. Сам код очень прост, но он делает большое дело. После вставки кода, ты можешь запустить функцию, введя ее, как любую другую функцию в Экселе. Ты можешь поменять название функции, для этого в ее тексте тебе необходимо заменить 6 раз слово join на свое слово, например ОБЪЕДИНИТЬ.
3.  Функция принимает 2 аргумента. Первый аргумент -- диапазон. Он может быть довольно большим. Если делать то же через стандартную фукнцию Экселя СЦЕПИТЬ, то при наличии ста исходных клеток можно рехнуться, вводя каждую клетку вручную. Тут же берется диапазон ячеек целиком. второй аргумент -- это разделитель. Его мы заключаем в кавычки. Второй диапазон можно упустить. По умолчанию разделителем будет пробел. Ты можешь поменять значение по умолчанию, заменив в первой строке Optional Delim As String = " ") на Optional Delim As String = "Ы"), где Ы -- будет твоим новым разделителем по умолчанию.

Решение сверхзадачи -- сцепить с условиями

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

Функция Фильтра интересна сама по себе. О ней подробнее расскажу в следующий раз.