Функция СЦЕП (СЦЕПИТЬ) в Excel: с пробелом, с запятой

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

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

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

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

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

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

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

где

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

Например:

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

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

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

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

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

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

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

У нас есть текст в нескольких ячейках и есть желание объединить эти ячейки в одну, и слить их текст в одно место. Есть одна проблема — кнопка «Объединить и центрировать» в Excel может объединять ячейки, а вот с текстом сложно — живым остается только текст из верхней левой ячейки.

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

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

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

Читайте также: Работа с примечаниями в Excel

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

Начиная с Excel 2016, эта функция сменила название на «СЦЕПИТЬ», но для тех, кто привык к старому названию («СЦЕПИТЬ»), оператор под этим названием также доступен в программе. CONCATENATE и CONCATENATE абсолютно взаимозаменяемы и выполняют одну и ту же операцию по объединению содержимого выделенных ячеек в общее.

Формула функции выглядит так:

=СЦЕПИТЬ(текст1,текст2,…) или =СЦЕПИТЬ(текст1,текст2;…)

Максимальное количество аргументов — 255.

Как пользоваться функцией СЦЕПИТЬ в Excel

Начнем с самых простых примеров.

Данные для объединения:

Ставим курсор в ячейку Е2 и вызываем мастер функции (кнопка fx). В категории «Текст» мы находим функцию СЦЕПИТЬ. Аргументы — ссылки на ячейки A2, B2, C2 и пробелы:

Результат объединения значений в ячейках:

Мы получаем тот же результат, используя амперсанд:

Одним из аргументов является текст. Формула: =СЦЕПИТЬ(«слесарь», » «;A2;» «;B2;» «;C2). Результат:

А с более сложными задачами справится функция СЦЕПИТЬ в Excel. Примеры:

1. Соединим текст с датой. Данные находятся в отдельных ячейках. В столбце Дата установлен формат даты. Если используется только функция СЦЕПИТЬ, дата будет отображаться в числовом формате. Поэтому мы добавим функцию ТЕКСТ для форматирования чисел. Формула: Второй аргумент функции ТЕКСТ — это формат представления даты. Результат:
2. Соединяйте много ячеек одновременно в ряд. Если написать формулу с помощью функции СЦЕПИТЬ, она получится длинной и неэффективной. Используем небольшую хитрость. Вот наша строка со значениями в отдельных ячейках. В ячейке H1 ставим знак «равно». Выберите диапазон со значениями A1:F1. Введите амперсанд, за которым следует пробел » «. Нажмите F9. В строке формул появится формула массива. Поместите курсор в строку формул после знака равенства. Пишем имя функции. Измените фигурные скобки на круглые и нажмите ENTER. Получаем желаемый результат.
3. Соединим значения через косую черту («-«, «&» и т.д.). Формула будет выглядеть так: =СЦЕПИТЬ(ссылка на ячейку 1; «/»; ссылка на ячейку 2).
4. Объединим несколько значений в один столбец. Функция подходит для значений в строках. Описанный выше способ с формулой массива тоже не подойдет.

Преобразуем вертикальную область в горизонтальную с помощью функции ТРАНСП. А затем мы используем формулу массива, чтобы быстро соединить значения. В ячейке B1 ставим знак «равно». Пишем ТРАНСПОРТ. Укажите диапазон A1:A6. Введите и поставьте пробел («»). Нажмите F9.

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

Вставка и настройка функции

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

1. Для начала определяем ячейку, где планируем объединять данные из других. Перейдите к нему (выделите его) и нажмите значок «Вставить функцию» (fx).
2. В открывшемся окне вставки функции выберите вкладку «Текст» (или «Полный алфавитный список»), отметьте строку «СВЯЗАН» (или «СВЯЗАН») и нажмите «ОК.
3. На экране появляется окно, в котором вы должны заполнить аргументы функции, которые могут быть либо конкретными значениями, либо ссылками на ячейки. Кроме того, последнее можно указать как вручную, так и просто кликнув по нужным ячейкам в самой таблице (в этом случае курсор необходимо поставить в поле для ввода значения, противоположного стороне от соответствующего аргумента). В нашем случае делаем следующее:
   • находясь в поле «Текст1», нажмите на ячейку (А2), значение которой будет находиться в первой строке в объединенной ячейке;
   • нажимаем на поле «Текст2», где ставим запятую и пробел («,»), который будет служить разделителем между содержимым ячеек, указанных в аргументах «Текст1» и «Текст3» (появляется сразу после начинаем заполнять «Текст2»). Вы можете указать любые символы на свое усмотрение: пробелы, разделители, текстовые или числовые значения и т д
   • переходим в поле «Текст3» и нажимаем на следующую ячейку, содержимое которой необходимо добавить в общую ячейку (в нашем случае это В2).
   • аналогичным образом заполните все остальные аргументы и нажмите кнопку ОК. В этом случае вы можете увидеть предварительный результат в левой нижней части окна аргументов.
4. Все готово, нам удалось объединить содержимое всех выделенных ячеек в общее.
5. Вам не нужно выполнять описанные выше шаги для остальных ячеек в столбце. Просто наведите указатель мыши на правый нижний угол ячейки с результатом, и после того, как он изменится на маленький черный плюсик, зажмите левую кнопку мыши и перетащите его вниз до нижней строки столбца (или до строки), для которой вы хотите выполнить аналогичные действия).
6. Таким образом мы получаем заполненную колонку с новыми именами, включая данные о размере и поле.

Аргументы функции без разделителей

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

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

Введите разделитель в отдельной ячейке

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

Например, мы добавляем запятую и пробел («,») в ячейку B16.

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

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

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

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

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

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

Редактирование функции

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

Способ 1: Изменения в окне аргументов

Вы можете продолжить редактирование функции, нажав кнопку «Вставить функцию» (fx), расположенную слева от строки формул. В этом случае необходимо сначала выделить ячейку, содержащую функцию.

Таким образом мы попадаем в окно с аргументами функции, где можем внести необходимые изменения, после чего нажимаем кнопку ОК.

Получим результат с учетом скорректированных данных.

Способ 2: ручная корректировка формулы

Альтернативным методом изменения аргументов функции является корректировка формулы вручную. Переключиться в режим редактирования можно двумя способами:

1. Выберите ячейку с функцией, затем щелкните строку формул и внесите в нее изменения.
2. Двойным кликом по ячейке с формулой мы активируем режим редактирования и корректируем формулу прямо в самой ячейке.

Независимо от выбранного метода, при ручной корректировке формул будьте предельно внимательны и соблюдайте следующие правила:

• адреса ячеек пишутся без кавычек;
• перечисление аргументов — через символ “;”
• если в качестве аргумента используется символ или текст, кавычки необходимы с обеих сторон указанного значения.

Обратная функция СЦЕПИТЬ в Excel

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

• ВЛЕВО (выводит указанную часть символов от начала строки);
• ВПРАВО (выводит указанную часть символов с конца строки);
• PSTR (отображает указанную часть символов, начиная с указанной позиции) и другие.

Примеры:

1. Извлеките первое слово из строки, используя функцию LEFT. Аргументы — «текст» (ссылка на строку с искомым значением), «количество символов» (количество символов для извлечения).
2. Извлеките последнее слово из строки с помощью функции ПРАВИЛЬНО. Синтаксис аналогичен (как и в предыдущем примере). С конца считается только количество символов.
3. Из записи в строке извлекаем фамилию (второе слово) с помощью функции MID. Аргументы — «текст» (ссылка на строку), «начальная позиция» (номер символа, с которого программа начнет подсчет количества символов); «количество символов». В примере: E2 — «текст», из 9 символов функция вернет 8 символов.

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

Применение оператора СЦЕПИТЬ

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

Синтаксис этого оператора следующий:

=СЦЕПИТЬ(текст1;текст2;…)

Аргументами может быть как текст, так и ссылки на ячейки, которые его содержат. Количество аргументов может варьироваться от 1 до 255 включительно.

Способ 1: объединение данных в ячейках

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

1. Выбираем ячейку, куда планируем поместить объединенные данные. Нажмите кнопку «Вставить функцию». Он выглядит как значок и расположен слева от строки формул.
2. Откроется мастер функций. В категории «Текст» или «Полный алфавитный список» ищем оператор «СЦЕПИТЬ». Выберите это имя и нажмите кнопку «ОК».
3. Откроется окно Аргументы функции. Аргументами могут быть ссылки на ячейки, содержащие данные или отдельный текст. Если стоит задача объединить содержимое ячеек, в этом случае мы будем работать только со ссылками.Подведите курсор к первому полю в окне. Затем мы выбираем ссылку в электронной таблице, содержащую данные, которые нам нужно объединить. После того, как координаты отобразятся в окне, проделываем то же самое с другим полем. Соответственно выберите другую ячейку. Проделываем аналогичную операцию до тех пор, пока в окне аргумента функции не будут введены координаты всех объединяемых ячеек. Затем нажмите на кнопку «ОК».

   

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

Способ 2: применение функции с пробелом

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

1. Выполняем задание по тому же алгоритму, что описан выше.
2. Дважды щелкните левой кнопкой мыши по ячейке с формулой, чтобы активировать ее для редактирования.
3. Между каждым аргументом мы пишем выражение в виде пробела, разделенного с обеих сторон кавычками. После ввода каждого такого значения ставьте точку с запятой. Общий вид добавляемых выражений должен быть следующим:» «;

   

4. Чтобы отобразить результат на экране, нажмите кнопку Enter.

Как видите, вместо пробелов с кавычками в ячейке есть разделители слов.

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

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

1. Выделите пустую ячейку на листе, дважды щелкнув левой кнопкой мыши. Используйте клавиатуру, чтобы ввести пробел внутри него. Желательно, чтобы он был размещен вдали от основной группы. Очень важно, чтобы после этого эта ячейка никогда не заполнялась данными.
2. Выполняем те же действия, что и в первом способе использования функции СЦЕПИТЬ, до открытия окна аргумента оператора. Добавляем в поле окна значение первой ячейки с данными, как описано ранее. Далее ставим курсор во второе поле, и выбираем пробелом пустую ячейку, о которой шла речь ранее. Ссылка отображается в поле окна аргумента. Чтобы ускорить процесс, вы можете скопировать его, выделив его и нажав Ctrl+C.
3. Затем мы добавляем ссылку на следующий элемент, который нужно добавить. В следующем поле оставьте ссылку на пустую ячейку. Так как мы скопировали адрес, то можно поставить курсор в поле и нажать комбинацию клавиш Ctrl+V. Координаты будут вставлены. Таким образом чередуем поля с адресами элементов и пустой ячейкой. После того, как все данные введены, нажмите кнопку «ОК».

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

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

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

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

1. С ячейками в первой строке столбцов, подлежащих объединению, выполняем действия, указанные во втором и третьем способах использования аргумента. Правда, если вы решите использовать метод с пустой ячейкой, то ссылка на него должна быть сделана обязательно. Для этого перед каждым символом горизонтальных и вертикальных координат этой ячейки ставится знак доллара ($). Естественно, лучше всего это сделать в самом начале, чтобы пользователь мог скопировать его в другие поля, содержащие этот адрес, содержащие постоянные абсолютные ссылки. Оставьте относительные ссылки в других полях. Как всегда, после завершения процедуры нажмите кнопку «ОК».
2. Поместите курсор в правый нижний угол элемента с формулой. Появится значок в виде креста, называемый маркером заполнения. Удерживая левую кнопку мыши, перетащите ее вниз параллельно положению объединяемых элементов.
3. После выполнения этой процедуры данные в указанных столбцах будут объединены в один столбец.

Способ 5: добавление дополнительных символов

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

1. Выполняем действия по добавлению значений в окно аргумента функции одним из приведенных выше способов. В одно из полей (при необходимости их может быть несколько) добавляем текстовый материал, который пользователь считает нужным добавить. Этот текст должен быть заключен в кавычки. Нажмите на кнопку «ОК».
2. Как видите, после этого действия к объединенным данным добавился текстовый материал.

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