Хитрости »
Основные понятия (23)
Сводные таблицы и анализ данных (9)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (14)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (38)
Баги и глюки Excel (2)

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

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

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

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
    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)
            End If
        Next lr
    Next lc
    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

Чтобы применить код необходимо ознакомиться со статьей: Что такое функция пользователя(UDF)?

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

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

Скачать пример

  Tips_Macro_CoupleCells.xls (54,0 KiB, 7 366 скачиваний)


Если необходимо объединять значения ячеек из "рваных"(несмежных) диапазонов(выделенных через 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)?. Так же эта функция не убирает дубли, что впрочем, не так сложно добавить, ориентируясь на функции выше.

Function ОбъединитьВсеСРазделителем(Разделитель As String, ParamArray Значения()) As String
    Dim result As String, arg, x, rc As Range
    For Each arg In Значения
        Select Case TypeName(arg)
        Case "Range"                     'это диапазон
            'цикл по всем ячейкам
            For Each rc In arg.Cells
                If result = "" Then
                    result = rc.Value
                Else
                    result = result & Разделитель & rc.Value
                End If
            Next
        Case "Variant()"                 'это произвольный массив({"а";"б";"в"})
            'цикл по всем ячейкам
            For Each x In arg
                If result = "" Then
                    result = x
                Else
                    result = result & Разделитель & x
                End If
            Next
        Case Else 'это любой другой тип
            'суммируем
            If result = "" Then
                result = arg
            Else
                result = result & Разделитель & arg
            End If
        End Select
    Next
    ОбъединитьВсеСРазделителем = result
End Function

Также см.:
Сцепить_МН
Как сцепить несколько значений в одну ячейку по критерию? СцепитьЕсли
Что такое функция пользователя(UDF)?


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Надстройки Настройка Печать Поиск данных Политика Конфиденциальности Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных баги и глюки в Excel ссылки
Обсуждение: 39 комментариев
  1. Маржан:

    Огромное Вам спасибо!!!!

  2. Сергей:

    Здравствуйте. Этот вопрос уже звучал. Возможно ли с помощью этой функции выбирать неск. отдельных диапазонов или ячеек для сцепки? Спасибо

    • Сергей, если внимательно прочитать статью, то можно увидеть, что реализаций функции 2. И одна из них как раз для нескольких диапазонов - последняя.

      P.S. теперь в статье три различных функции и последние две работают с "рваными" диапазонами. Выбирайте.

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2018 Excel для всех   Войти