В эксель сцепить: Функция CONCAT — Служба поддержки Майкрософт

Содержание

3 способа склеить текст из нескольких ячеек

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


Надпись на заборе: «Катя + Миша + Семён + Юра + Дмитрий Васильевич +

товарищ Никитин + рыжий сантехник + Витенька + телемастер Жора +

сволочь Редулов + не вспомнить имени, длинноволосый такой +

ещё 19 мужиков + муж = любовь!»

Способ 1. Функции СЦЕПИТЬ, СЦЕП и ОБЪЕДИНИТЬ


В категории Текстовые есть функция СЦЕПИТЬ (CONCATENATE), которая соединяет содержимое нескольких ячеек (до 255) в одно целое, позволяя комбинировать их с произвольным текстом. Например, вот так:



Нюанс: не забудьте о пробелах между словами — их надо прописывать как отдельные аргументы и заключать в скобки, ибо текст. 


Очевидно, что если нужно собрать много фрагментов, то использовать эту функцию уже не очень удобно, т. к. придется прописывать ссылки на каждую ячейку-фрагмент по отдельности. Поэтому, начиная с 2016 версии Excel, на замену функции СЦЕПИТЬ пришла ее более совершенная версия с похожим названием и тем же синтаксисом — функция СЦЕП (CONCAT). Ее принципиальное отличие в том, что теперь в качестве аргументов можно задавать не одиночные ячейки, а целые диапазоны — текст из всех ячеек всех диапазонов будет объединен в одно целое:



Для массового объединения также удобно использовать новую функцию ОБЪЕДИНИТЬ (TEXTJOIN), появившуюся начиная с Excel 2016. У нее следующий синтаксис:


=ОБЪЕДИНИТЬ(Разделитель; Пропускать_ли_пустые_ячейки; Диапазон1; Диапазон2 … )


где

  • Разделитель — символ, который будет вставлен между фрагментами
  • Второй аргумент отвечает за то, нужно ли игнорировать пустые ячейки (ИСТИНА или ЛОЖЬ)
  • Диапазон 1, 2, 3 .. . — диапазоны ячеек, содержимое которых хотим склеить


Например:


Способ 2. Символ для склеивания текста (&)


Это универсальный и компактный способ сцепки, работающий абсолютно во всех версиях Excel. 


Для суммирования содержимого нескольких ячеек используют знак плюс «+«, а для склеивания содержимого ячеек используют знак «&» (расположен на большинстве клавиатур на цифре «7»). При его использовании необходимо помнить, что:

  • Этот символ надо ставить в каждой точке соединения, т.е. на всех «стыках» текстовых строк также, как вы ставите несколько плюсов при сложении нескольких чисел (2+8+6+4+8)
  • Если нужно приклеить произвольный текст (даже если это всего лишь точка или пробел, не говоря уж о целом слове), то этот текст надо заключать в кавычки. В предыдущем примере с функцией СЦЕПИТЬ о кавычках заботится сам Excel — в этом же случае их надо ставить вручную.


Вот, например, как можно собрать ФИО в одну ячейку из трех с добавлением пробелов:



Если сочетать это с функцией извлечения из текста первых букв — ЛЕВСИМВ (LEFT), то можно получить фамилию с инициалами одной формулой:


Способ 3. Макрос для объединения ячеек без потери текста.


Имеем текст в нескольких ячейках и желание — объединить эти ячейки в одну, слив туда же их текст. Проблема в одном — кнопка Объединить и поместить в центре (Merge and Center) в Excel объединять-то ячейки умеет, а вот с текстом сложность — в живых остается только текст из верхней левой ячейки. 


Чтобы объединение ячеек происходило с объединением текста (как в таблицах Word) придется использовать макрос. Для этого откройте редактор Visual Basic  на вкладке Разработчик — Visual Basic (Developer — Visual Basic) или сочетанием клавиш Alt+F11, вставим в нашу книгу новый программный модуль (меню Insert — Module) и скопируем туда текст такого простого макроса:

Sub MergeToOneCell()
    Const sDELIM As String = " "     'символ-разделитель
    Dim rCell As Range
    Dim sMergeStr As String
    If TypeName(Selection) <> "Range" Then Exit Sub   'если выделены не ячейки - выходим
    With Selection
        For Each rCell In . Cells
            sMergeStr = sMergeStr & sDELIM & rCell.Text  'собираем текст из ячеек
        Next rCell
        Application.DisplayAlerts = False   'отключаем стандартное предупреждение о потере текста
        .Merge Across:=False                'объединяем ячейки
        Application.DisplayAlerts = True
        .Item(1).Value = Mid(sMergeStr, 1 + Len(sDELIM))    'добавляем к объед.ячейке суммарный текст
    End With
End Sub


Теперь, если выделить несколько ячеек и запустить этот макрос с помощью сочетания клавиш Alt+F8 или кнопкой Макросы на вкладке Разработчик (Developer — Macros), то Excel объединит выделенные ячейки в одну, слив туда же и текст через пробелы.

Ссылки по теме

  • Делим текст на куски
  • Объединение нескольких ячеек в одну с сохранением текста с помощью надстройки PLEX
  • Что такое макросы, как их использовать, куда вставлять код макроса на VBA


 

Функция «Сцепить» в Excel — RUUD

20-08-2018 21:26

The content of the article:

  • Способ 1: простое объединение
  • Способ 2: формула «Сцепить» с пробелом в Excel
  • Способ 3: расстановка пробелов через дополнительный аргумент
  • Способ 4: объединение колонок

При работе в табличном редакторе Excel иногда появляется потребность объединить в одной ячейке данные из нескольких. К сожалению, стандартная для программы опция «Объединить» в этом не поможет, так как при ее выполнении часть данных обязательно пропадет. Однако можно обратиться к формуле «Сцепить» в Excel. В этой статье мы расскажем, как ею пользоваться.

Способ 1: простое объединение

Логично будет начать статью с информации о том, как в Excel сцепить ячейки обычным способом, рассказать о самой функции «Сцепить» и привести пример ее использования. Предварительно рассмотрим синтаксис: =СЦЕПИТЬ (текст1;текст2;…).

You will be interested:How dangerous is the new coronavirus?

Из него становится понятным, что функция довольна простая. Из аргументов один лишь текст, который нужно объединить в единой ячейке. Ну а теперь давайте рассмотрим все на примере. Допустим, у вас есть таблица с именами людей. При этом имя, отчество и фамилия вписаны в отдельные колонки. Наша задача — объединить их в одной:

  • Выделите пустую ячейку. В нашем случае в колонке «ФИО».
  • Нажмите по кнопке «Вставить функцию». Находится она слева от строки формул.
  • В появившемся окне выберите категорию «Текстовые».
  • В самом списке выделите функцию «Сцепить».
  • Нажмите ОК.
  • Откроется окно с аргументами выбранной функции. Так как мы будем объединять три ячейки, то в соответствующее число полей нам нужно вписать адреса нужных ячеек:

  • В поле «Текст1» укажите ячейку с фамилией человека. Сделать это можно как вручную, прописав «B4», так и автоматически, нажав по кнопке справа от поля и указав ячейку мышкой.
  • В «Текст2» укажите ячейку с именем.
  • В «Текст3» – с отчеством.
  • Нажмите ОК.
  • Это первый пример использования функции «Сцепить» в Excel.Теперь вы можете наблюдать, что в колонке «ФИО» появился результат – фамилия, имя и отчество записаны последовательно.

    Способ 2: формула «Сцепить» с пробелом в Excel

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

  • Кликните дважды левой кнопкой мыши по ячейке с формулой.
  • Впишите между аргументами пробелы. Однако делается это нестандартным способом. Пробел должен выступать также в роли аргумента. Это означает, что его необходимо взять в кавычки, а после них поставить точку с запятой.
  • Нажмите Enter для применения изменений.
  • Теперь, как вы можете заметить, между всеми переменными есть пробелы. Однако это только первый способ вставки, так что перейдем ко второму.

    Способ 3: расстановка пробелов через дополнительный аргумент

    Может случиться и так, что предыдущий способ расстановки пробелов в функции «Сцепить» в Excel не подойдет вам. Тогда воспользуйтесь другим. Он подразумевает включение пробелов непосредственно в аргументы в «Мастере функций». Ну а теперь давайте перейдем к подробной инструкции:

  • Далеко от таблицы выделите ячейку, которую никогда не будете использовать.
  • Введите в нее знак пробела.
  • Создайте функцию «Сцепить» так, как было это описано в первом способе, только остановитесь на этапе ввода аргументов.
  • Первым аргументом введите фамилию.
  • Вторым выберите ячейку, которую недавно создали, то есть пробел.
  • Далее выберите имя, снова пробел и в последней – отчество.
  • Нажмите ОК.
  • Как можно заметить, второй способ постановки пробелов между аргументами формулы «Сцепить» в Excel также отлично справляется со своими задачами. Некоторым пользователям он придется по душе, так как все действия выполняются непосредственно в графическом интерфейсе.

    Способ 4: объединение колонок

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

  • Откройте окно функций первой ячейки в колонке «ФИО».
  • Перейдите на этап заполнения аргументов.
  • Во всех ссылках на ячейку поставьте знак «$» перед буквой и перед числом.
  • Нажмите ОК.
  • После этого можно приступать к копированию функции «Сцепить» на все оставшиеся в колонке ячейки. Для этого:

  • Выделите первую ячейку с введенной функцией.
  • Наведите курсор на ее нижний правый угол.
  • После появления крестика зажмите левую кнопку мыши.
  • Удерживая ее, протяните курсор до конца колонки, выделив все сопутствующие ячейки.
  • Отпустите кнопку мыши.
  • После этого колонка автоматически заполнится. Как можно заметить, это очень эффективный способ, если вам нужно справиться с огромным объемом данных.

    Источник

    Автор: Карина Ленская

    Похожие статьи

    Как объединить в Excel (СЦЕПИТЬ, СЦЕПИТЬ, СОЕДИНИТЬ ТЕКСТ)


    Функция СЦЕПИТЬ Excel используется для объединения нескольких текстовых строк в одну строку. Часто это решение, когда текст, который мы хотели бы видеть вместе, расположен в разных местах или в нескольких ячейках.

    Начиная с Excel 2016, функция СЦЕПИТЬ постепенно упраздняется в пользу функций СЦЕПИТЬ Excel и ТЕКСТОВОЕ СОЕДИНЕНИЕ Excel. В настоящее время CONCATENATE по-прежнему доступен для обратной совместимости.

    Мы рассмотрим, как работают все три функции, чтобы вы могли решить, когда уместно использовать каждую из них.


    Загрузите бесплатный файл практики СЦЕПИТЬ Excel!

    Используйте этот бесплатный файл Excel СЦЕПИТЬ, чтобы практиковаться вместе с учебным пособием.

    Введите адрес электронной почты

    Как объединить в Excel

    • Функция СЦЕПИТЬ
    • Функция СЦЕП
    • Функция ТЕКСТСОЕДИНЕНИЕ
    • Оператор конкатенации (&)

    СЦЕПИТЬ

    Синтаксис функции СЦЕПИТЬ:

      =СЦЕПИТЬ(текст1, [текст2]. ..)  

    Каждый аргумент может быть ссылкой на ячейку или текстовой строкой, введенной непосредственно в формулу. Требуется только один аргумент, но если вы используете эту функцию, скорее всего, у вас есть как минимум два. CONCATENATE может принимать до 255 аргументов с максимальной длиной 8192 символа.

    Как использовать СЦЕПИТЬ

    Чтобы узнать, как использовать эту функцию, давайте рассмотрим пример. На листе ниже все элементы адреса разбиты на пять столбцов.

    Мы можем использовать CONCATENATE, чтобы соединить все пять элементов в одну строку. Важно помнить, что если между элементами требуется пробел или какой-либо другой символ (например, запятая), его необходимо ввести вручную между каждой ссылкой на ячейку в двойных кавычках.

      =СЦЕПИТЬ(A2," ",B2,", ",C2,", ",D2," ",E2)  

    Обратите внимание, что, поскольку после B2 и C2 требуются запятая и пробел, текстовая строка «, » была введена в качестве аргумента после обеих ссылок на ячейки.

    Объединение с числовыми значениями

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

    В следующем примере номер улицы будет введен непосредственно в формулу СЦЕПИТЬ и будет присоединен к ссылкам на ячейки для создания полного адреса.

      =СЦЕПИТЬ(123," ",A2,", ",B2,", ",C2," ",D2)  

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

    В приведенном выше примере мы ввели пробел между двумя двойными кавычками в качестве второго аргумента, и это отразилось в выходной ячейке.

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

    Объединение строк с разрывами строк

    Мы можем захотеть отобразить некоторые из этих элементов в отдельных строках. Для этого мы можем использовать символ Юникода CHAR(10) для вставки разрыва строки.

      =СЦЕПИТЬ(A2," ",B2,CHAR(10),C2,", ",D2,CHAR(10),E2)  

    Обратите внимание, что в приведенном выше примере мы удалили пробел и запятую после B2, поскольку он больше не нужен после вставки символа разрыва строки — CHAR(10).

    Столбец F также был отформатирован для переноса текста для отображения результатов.

    Ограничения СЦЕПИТЬ

    1. СЦЕПИТЬ может стать немного громоздким, особенно при добавлении разделителей. Довольно частая неожиданность заключается в том, что в строке результата появляется кавычка из-за пропуска запятой между аргументами. Например: 
      =СЦЕПИТЬ("Доброе утро")  

    вернет результат Доброе утро , потому что запятая не была введена для разделения двух строк.

    1. Какой бы полезной ни была функция СЦЕПИТЬ Excel, если соединяемые строки находятся в непрерывном диапазоне и не требуют разделителей, таких как запятые или пробелы, она не может принять диапазон в качестве аргумента.

    В следующем примере элементы группы телефонных номеров разбиты на четыре столбца.

    Если мы попытаемся применить функцию СЦЕПИТЬ Excel, используя диапазон A2:D2 в качестве аргумента, Excel вернет ошибку #ЗНАЧ! ошибка.

    Это очень плохо, но здесь в дело вступает функция CONCAT.

    СЦЕПИТЬ

    Начиная с версии Excel 2016, функция СЦЕПИТЬ Excel присоединилась к семейству текстовых функций и, как и СЦЕПИТЬ, предназначена для объединения нескольких текстовых строк в одну строку.

    Синтаксис:

      =CONCAT(текст1, [текст2]...)  

    Каждый аргумент может быть ссылкой на ячейку, диапазоном ссылок на ячейки или текстовой строкой, введенной непосредственно в формулу. Благодаря такой гибкости мы можем быстро объединить приведенные выше телефонные номера в одну строку, используя диапазон от A2 до D2 в качестве аргумента функции CONCAT, чего не может сделать CONCATENATE.

      =CONCAT(A2:D2)  

    CONCAT поддерживает все остальные функции CONCATENATE, включая необходимость вручную вставлять между строками отдельные разделители, такие как пробелы, амперсанд, запятые и т. д., если это необходимо.

    Например, чтобы поместить код города в тире в приведенном выше примере, мы должны ввести:

      =CONCAT(A2,"(",B2,")",C2:D2)  

    Ограничения функций CONCATENATE и CONCATENATE

    Основным недостатком функций CONCAT и CONCATENATE является то, что разделители необходимо вводить по отдельности. , даже если разделитель между всеми аргументами один и тот же. Это может занять очень много времени, трудно читать и может привести к ошибкам.

    TEXTJOIN

    Моя любимая из трех функций соединения строк — TEXTJOIN. TEXTJOIN был развернут вместе с Excel 2019 и реально экономит время. Он имеет три обязательных аргумента.

    Синтаксис:

      =TEXTJOIN(разделитель, ignore_empty, текст1, [текст2], …) 
     

    Определения аргументов

    • разделитель (обязательно) — символ или символы, разделяющие каждый аргумент, введенный в двойных кавычках.
    • ignore_empty (обязательно) — это параметр, который сообщает Excel, что делать с пустыми ячейками. Если TRUE, пустые ячейки игнорируются. Если FALSE, отображается пробел, обозначающий пустую ячейку в результате.
    • text1 (обязательно) — текстовая строка или массив строк.
    • [текст2] — необязательная текстовая строка или массив строк. Всего разрешено 252 текстовых аргумента с максимальной длиной 32 767 символов.

    Наиболее очевидным преимуществом TEXTJOIN является то, что если разделители между строками идентичны, это избавляет от необходимости вводить один и тот же разделитель снова и снова.

    Поэтому вполне возможно, что потребуется всего три аргумента, даже если желаемый результат растянут на несколько строк. Это связано с тем, что разделитель был введен один раз в начале формулы, а третий аргумент ( text1 ) может быть диапазоном.

    В следующем примере следует соединить титул каждого клиента (если есть), имя, фамилию и элементы их адреса, разделенные пробелом. Мы также хотим вставить разрыв строки после имени, поэтому символ Юникода CHAR(10) вводится после ссылки на ячейку C2.

      =TEXTJOIN(" ",TRUE,A2:C2,CHAR(10),D2:h3)  

    Обратите внимание, что для второго аргумента установлено значение TRUE, так что если Excel встретит какие-либо пустые ячейки, он не создаст дополнительных пробел в выходной ячейке. Столбец I был настроен для переноса текста, чтобы отображался результат разрыва строки.

    Чтобы получить аналогичный результат от функции СЦЕПИТЬ, запись должна быть:

      =СЦЕПИТЬ(A2," ",B2," ",C2,CHAR(10),D2," ",E2," ",F2," ",G2," ",h3)  

    С функцией CONCAT Excel запись будет выглядеть так:

      =CONCAT(A2," ",B2," ",C2,CHAR(10),D2," ",E2," ",F2," ",G2," ",h3)  

    Хотя допустимы диапазоны с функцией CONCAT здесь нет никаких преимуществ, поскольку между каждой строкой требуются разделители. Это означало бы в общей сложности 15 аргументов, использующих старые функции, по сравнению с пятью аргументами в TEXTJOIN.

    Кроме того, операторы СЦЕПИТЬ и СЦЕПИТЬ не могут игнорировать пустые ячейки, поэтому для каждого появления пустых ячеек создается дополнительный разделитель, например, с пропуском заголовка или некоторых частей адреса в приведенных выше примерах.

    Аргумент ignore_empty в TEXTJOIN отлично справляется с этим, удаляя из результатов ненужные разделители.

    Оператор конкатенации (&)

    В Excel возможно объединение без использования функций. Это делается с использованием символа амперсанда (&) в качестве оператора конкатенации.

    Давайте сравним использование оператора конкатенации с использованием функции CONCAT. Мы выбрали CONCAT, потому что это более новая версия CONCATENATE, но тот же принцип применим и к CONCATENATE.

    Формат здесь таков, что оператор & вводится после каждой ссылки на ячейку и значения, включая символ пробела, указанный в двойных кавычках. Результаты в столбце D идентичны результатам использования CONCAT в столбце E, как показано ниже.

    Конечно, оператор конкатенации не принимает диапазоны ячеек, а недостаток пустых ячеек решается только с помощью функции Excel JOIN.

    TEXTJOIN позволяет придать выходным ячейкам более однородный вид, даже если некоторые элементы отсутствуют.

    Подробнее

    Пробовали ли вы использовать все четыре метода объединения? Какой из них вы предпочитаете? Расскажите нам в комментариях и будьте в курсе наших курсов Excel и других ресурсов.

    Начните свое путешествие с бесплатного курса Excel за час  сегодня!

    Бесплатный ускоренный курс Excel

    Быстро изучите основы Excel с помощью этого БЕСПЛАТНОГО курса. Получите сертификат сегодня!

    Начать бесплатный курс

    Как использовать конкатенацию в Excel

    Функция конкатенации может быть не так хорошо известна, как другие распространенные функции Excel, такие как ЕСЛИ и ВПР, но она по-прежнему эффективна, хотя по своей природе выполняет более простую функцию. Объединение объединяет данные, которые вы вставили в столбцы, в отдельную ячейку или столбец. Например, вы можете объединить столбцы с подробным описанием выручки и прибыли за данный месяц в одну ячейку.

    Давайте посмотрим, как использовать конкатенацию в Excel.

    Содержание

    • Использование функции объединения двух столбцов в Excel
    • Как объединить 3 столбца в Excel?
    • Как объединить в Excel без формулы?

    Сложность

    Легкость

    Продолжительность

    5 минут

    Что вам нужно

    • ПК

    • Microsoft Excel

    Использование функции конкатенации для двух столбцов в Excel

    Шаг 1: Выберите столбец и ячейку, в которых вы будете объединять данные из других ячеек. В демонстрационных целях я буду использовать конкатенацию в столбце, примыкающем к первым двум столбцам, которые я буду объединять, но вы можете использовать любое место в электронной таблице.

    Шаг 2: Я буду объединять ячейки A2 и B2, поэтому я ввел =CONCATENATE(A2,B2) . Это самая простая форма использования функции конкатенации, но есть способы ее настройки, которые мы рассмотрим на шаге 4.

    Шаг 3: Чтобы подтвердить формулу конкатенации, просто нажмите клавишу ввода, после чего ячейки, которые вы вставили в формулу, будут объединены.

    Шаг 4: Как вы можете видеть выше, стандартная формула конкатенации, которую я использовал, объединила мои данные, но она выглядит не совсем правильно.

    Вы можете вставить пробел между соединяемыми данными: введите =CONCATENATE(A2, » «, B2) .

    Если вы хотите добавить другое значение, просто введите его между кавычками в этой формуле. Например, вы можете разделить объединенные данные знаком равенства.

    Я добавил знак равенства и два дополнительных пробела между ним в кавычках, чтобы разделить объединенные данные.

    Шаг 5: Вы можете дополнительно настроить ячейку, в которой используется функция конкатенации, с помощью стандартных инструментов Excel. Я сделал его жирным, расположил текст по центру и увеличил размер шрифта.

    Как объединить 3 столбца в Excel?

    Вы также можете использовать функцию конкатенации для объединения более двух столбцов.

    Шаг 1: Я ввел = СЦЕПИТЬ (A2, B2, C2) в отдельную ячейку. Измените буквы столбца на те, которые вы выбрали, а затем нажмите Enter.

    Шаг 2: Если вы хотите использовать конкатенацию для всего столбца, Excel предлагает простое решение. Просто перетащите правый нижний угол первой ячейки, который мы заполнили на шаге 1, с помощью мыши туда, где вы хотите, чтобы отображались объединенные данные.