Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Power BI и Power Query (5) Здесь собраны статьи, раскрывающие различные возможности мощнейшего инструмента для визуализаций бизнесс-процессов Power BI и надстройки для Excel Power Query
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (59) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (34) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

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

Часто бывает ситуация, когда необходимо из трех разных столбцов сцепить данные в одну строку с разделителем. Допустим в А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, 3 634 скачиваний)

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


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

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

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

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

  2. Валерий:

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

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

  3. Ал:

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

  4. Виталий:

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

  5. Евгений:

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

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

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

  7. Александр:

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

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

      • Александр:

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

  8. Виталий:

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

  9. Михаил:

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

  10. Николай:

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

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

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


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

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти