Хитрости »
Основные понятия (22)
Сводные таблицы и анализ данных (6)
Графики и диаграммы (4)
Работа с VB проектом (10)
Power BI и Power Query (7)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (60)
Разное (36)

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

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

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

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

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

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

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

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

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


Статья помогла? Не держи в себе, поделись ссылкой с друзьями!

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

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

    БольшОе спасибо Дмитрий!
    Функция очень выручила!

    Рейтинг: 0
  2. Валерий:

    Дмитрий, спасибо!

    Скажите, пожалуйста, нет ли у Вас функции со множественными условиями?
    Или может быть существует функция, которая фильтрует массив по условиям и возвращает массив, чтобы его можно было передать Вашей функции в качестве первого параметра?

    Рейтинг: 0
  3. Ал:

    Привет. А как этой функцией сцепить например N ячеек по горизонтали и M строк по вертикали? (т.е. сцеплять много строк построчно)

    Рейтинг: 0
  4. Виталий:

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

    Рейтинг: 0
  5. Евгений:

    Спасибо большое, удачное решение.
    подскажите, если поместить в персональную книгу макросов, почему не работает? обязательное условие - нахождение в используемой книге?

    Рейтинг: 0
    • Не совсем так. Функции пользователя могут располагаться либо в той же книге, в которой используются, либо в надстройке. PERSONAL немного иначе устроена и не позволяет использовать UDF для любой книги. Только макросы. Про создание надстроек я писал в этой статье: Как создать свою надстройку?

      Рейтинг: 0
  6. Спасибо огромное!!!!
    Очень выручила данная функция!!!!
    отправил поддержку

    Рейтинг: 0
  7. Александр:

    Добрый день,
    Спасибо большое, очень нужная функция, если не затруднит, можете подсказать? как сделать чтобы после последнего значения стояла точка?

    Рейтинг: 0
    • На самом деле очень просто. Записываете функцию СцепитьЕсли, а в конце добавляете точку:
      =СцепитьЕсли(аргументы)&"."

      Рейтинг: 0
      • Александр:

        Добрый день,
        Просто но не для всех) точку поставил.
        Благодаря Вам и таким же сайтам, упрощается у многих работа с новыми функциями, спасибо.

        Рейтинг: 0
  8. Виталий:

    Всё работает!!! Автор - МАЛАДЦА!!! СПАСИБО!

    Рейтинг: 0
  9. Михаил:

    Огромное вам спасибо! Всё так продуманно!
    Благодарю вас и желаю счастья )

    Рейтинг: 0
  10. Николай:

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

    Рейтинг: 0
Поделитесь своим мнением

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


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

Тренинги

Заказать
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2017 Excel для всех  Войти
Авторизация
*
*
Регистрация
*
*
*
Пароль не введен
*
captcha
Перейти на страницу