Сцепить много ячеек с указанным разделителем

Часто бывает ситуация, когда необходимо из трех разных столбцов сцепить данные в одну строку с разделителем. Допустим в А1 Фамилия, в В1 - Имя, в С1 - Отчество, а надо получить все вместе Фамилия Имя Отчество. Как обычно в Excel объединяют значения нескольких ячеек в одну? Правильно, при помощи функции СЦЕПИТЬ или при помощи амперсанда: =СЦЕПИТЬ( A1 ;" "; B1 ;" "; C1 ;" ") = A1 &" "& B1 &" "& C1 &" " Это достаточно эффективно, если необходимо сцепить значения из трех-пяти ячеек. А если ячеек 50? Или того больше? Не очень удобно объединять их все описанными выше способами. А других встроенных функций в Excel для подобных операций не существует. С момента написания статьи Microsoft порадовал нас новыми функциями и теперь в составе функций есть функция ОБЪЕДИНИТЬ (TEXTJOIN) , которая способна решить задачу без лишних телодвижений. =ОБЪЕДИНИТЬ(", ";ИСТИНА; A2:A100 ) =TEXTJOIN(", ",TRUE,A2:A100)

Разделитель(", ") - разделитель, с которым объединять текст из указанных ячеек Пропускать пустые(ИСТИНА) - указывает пропускать ли пустые ячейки. Т.е. если указано ИСТИНА или 1(а так же если аргумент вовсе не указан) - пустые ячейки будут пропускаться и не попадут в общую строку сцепки. Если указано ЛОЖЬ - сцепляться будут все ячейки, независимо от их содержимого. Например, если указать три ячейки A1:A3 в которых А2 пустая, то при указании ИСТИНА результат будет таким: "один, два" . Если указать ЛОЖЬ, то пустая ячейка тоже попадет в сцепку: "один, , два" . Так же этот аргумент удобен, если неизвестен заранее размер диапазона сцепления. Можно указать ячейки чуть с запасом( A1:A300 ) и тогда сцепляться будут только ячейки заполненного диапазона. Текст( A2:A100 ) - указывается непосредственно диапазон либо текст для сцепления. Этот аргумент расширяемый - т.е. можно указать не один диапазон, а несколько или просто текст: =ОБЪЕДИНИТЬ(", ";ИСТИНА; A2:A100 ; B2:B70 ;"текст")

Правда и здесь не все так радужно: эта функция доступна только пользователям версий 2019 и выше, а так же офиса 365 .

Поэтому я написал функцию пользователя, которая сцепляет данные из указанных ячеек в одну строку и использовать её можно в любой версии офиса. Чем отличается от стандартной функции СЦЕПИТЬ()? Тем, что в качестве ячеек для сцепки указывается не каждая из ячеек по очереди, а сразу весь диапазон с возможностью указания разделителя между значениями каждой ячейки. Так же, в функции сразу заложен алгоритм пропуска пустых ячеек и возможность сцеплять исключительно уникальные значения - т.е. в результате будут сцепляться только те ячейки, значения которых ранее еще не были добавлены в сцепку.

Option Explicit '--------------------------------------------------------------------------------------- ' Procedure : СцепитьМного ' http://www.excel-vba.ru ' Purpose : Функция сцепляет все указанные ячейки в одну с указанным разделителем. ' Аргументы функции: ' Диапазон — диапазон ячеек, значения которых необходимо объединить в строку. ' Разделитель — необязательный аргумент. ' Один или несколько символов, которые будут вставлены между каждым словом. ' По умолчанию пробел. ' БезПовторов — необязательный аргумент. ' Если указан как ИСТИНА или 1 — в результирующей строке будут значения без дубликатов. ' Для английской локализации данный параметр указывается как TRUE и FALSE соответственно. '--------------------------------------------------------------------------------------- Function СцепитьМного(Диапазон As Range, Optional Разделитель As String = " ", Optional БезПовторов As Boolean = False) Dim avData, lr As Long, lc As Long, sRes As String Dim oDict As Object, sTmpStr Set oDict = CreateObject("Scripting.Dictionary") oDict.comparemode = 1 avData = Диапазон.Value If Not IsArray(avData) Then СцепитьМного = avData Exit Function End If For lc = 1 To UBound(avData, 2) For lr = 1 To UBound(avData, 1) If Len(avData(lr, lc)) Then sRes = sRes & Разделитель & avData(lr, lc) If БезПовторов Then If Not oDict.exists(avData(lr, lc)) Then oDict.Add avData(lr, lc), 0& End If End If End If Next lr Next lc If Len(sRes) Then sRes = Mid(sRes, Len(Разделитель) + 1) End If If БезПовторов Then sRes = "" sTmpStr = oDict.keys For lr = LBound(sTmpStr) To UBound(sTmpStr) sRes = sRes & IIf(sRes <> "", Разделитель, "") & sTmpStr(lr) Next lr End If СцепитьМного = sRes End Function

Чтобы правильно использовать приведенный код, необходимо сначала ознакомиться со статьей Что такое функция пользователя(UDF)?. Вкратце: скопировать текст кода выше, перейти в редактор VBA( Alt + F11 ) -создать стандартный модуль(Insert -Module) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций( Shift + F3 ), отыскав её в категории Определенные пользователем (User Defined Functions) .

Синтаксис функции: =СцепитьМного(A2:A100;", ";ИСТИНА)

Диапазон - диапазон ячеек, значения которых необходимо объединить в строку. Разделитель - необязательный аргумент. Один или несколько символов, которые будут вставлены между каждым словом. По умолчанию пробел. БезПовторов - необязательный аргумент. Если указан как ИСТИНА или 1 - в результирующей строке будут значения без дубликатов. Например, из значений Сидоров, Петров, Сидоров, Иванов в результат попадут только Сидоров, Петров, Иванов. Если ЛОЖЬ или 0 - будут выведены все значения. Для английской локализации данный параметр указывается как TRUE и FALSE соответственно.

СцепитьМного.xls (52,5 KiB, 11 147 скачиваний)

Если необходимо объединять значения ячеек из "рваных"(несмежных) диапазонов(выделенных через Ctrl), то код нужно немного изменить:

Option Explicit '--------------------------------------------------------------------------------------- ' Procedure : СцепитьМного ' http://www.excel-vba.ru ' Purpose : Функция сцепляет все указанные ячейки в одну с указанным разделителем. Допускается указание несмежных диапазонов ' Аргументы функции: ' Диапазон — диапазон ячеек, значения которых необходимо объединить в строку. ' Разделитель — необязательный аргумент. ' Один или несколько символов, которые будут вставлены между каждым словом. ' По умолчанию пробел. ' БезПовторов — необязательный аргумент. ' Если указан как ИСТИНА или 1 — в результирующей строке будут значения без дубликатов. ' Для английской локализации данный параметр указывается как TRUE и FALSE соответственно. '--------------------------------------------------------------------------------------- Function СцепитьМного(диапазон As Range, Optional разделитель As String = " ", Optional БезПовторов As Boolean = False) Dim avData, lr As Long, lc As Long, sRes As String Dim ra As Range For Each ra In диапазон.Areas avData = ra.Value If Not IsArray(avData) Then ReDim avData(1 To 1, 1 To 1) avData(1, 1) = ra.Value End If For lc = 1 To UBound(avData, 2) For lr = 1 To UBound(avData, 1) If Len(avData(lr, lc)) Then sRes = sRes & разделитель & avData(lr, lc) End If Next lr Next lc Next If Len(sRes) Then sRes = Mid(sRes, Len(разделитель) + 1) End If If БезПовторов Then Dim oDict As Object, sTmpStr Set oDict = CreateObject("Scripting.Dictionary") sTmpStr = Split(sRes, разделитель) On Error Resume Next For lr = LBound(sTmpStr) To UBound(sTmpStr) oDict.Add sTmpStr(lr), sTmpStr(lr) Next lr sRes = "" sTmpStr = oDict.Keys For lr = LBound(sTmpStr) To UBound(sTmpStr) sRes = sRes & IIf(sRes <> "", разделитель, "") & sTmpStr(lr) Next lr End If СцепитьМного = sRes End Function

Однако в таком случае слегка изменится и синтаксис - такие диапазоны обязательно надо будет записывать в скобках: Синтаксис функции: =СцепитьМного(( A2:A100 ; F4:F60 ; Y2:Z43 );", ";ИСТИНА) Иначе функция просто не сработает и выдаст ошибку #ЗНАЧ! (#VALUE!)

И еще одна реализация - в ней допускается указывать не только отдельные диапазоны, но и вообще все что угодно(ячейки, отдельный текст, числа и т.п.). Единственная проблема - в этой функции иначе организован порядок аргументов: сначала указывается разделитель, а уже потом значения для сцепления. Более подробно эта функция рассмотрена в статье Что такое функция пользователя(UDF)?. Так же эта функция не убирает дубли, что впрочем, не так сложно добавить, ориентируясь на функции выше.