Функция Сцепить в Excel

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

Синтаксис: СЦЕПИТЬ(текст1;[текст2];…)

Ниже приведены примеры.

• =СЦЕПИТЬ("Популяция рек для ";A2;" ";A3;" составляет ";A4;" на километр.")

• =СЦЕПИТЬ(В2;" ";C2)

Примеры

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

Распространенные неполадки

Рекомендации

Дополнительные сведения

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

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

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

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

или

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

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

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

1. Для начала определяемся с ячейкой, в которой планируем объединить данные из других. Переходим в нее (выделяем) и щелкаем по значку “Вставить функцию” (fx).
2. В открывшемся окне вставки функции выбираем категорию “Текстовые” (или “Полный алфавитный перечень”), отмечаем строку “СЦЕП” (или “СЦЕПИТЬ”) и кликаем OK.
3. На экране появится окно, в котором нужно заполнить аргументы функции, в качестве которых могут быть указаны как конкретные значения, так и ссылки на ячейки. Причем последние можно указать как вручную, так и просто кликнув по нужным ячейкам в самой таблице (при это курсор должен быть установлен в поле для ввода значения напротив соответствующего аргумента). В нашем случае делаем следующее:
   • находясь в поле “Текст1” щелкаем по ячейке (A2), значение которой будет стоять на первом месте в объединенной ячейке;
   • кликаем по полю “Текст2”, где ставим запятую и пробел (“, “), которые будут служит разделителем между содержимыми ячеек, указанных в аргументах “Текст1” и “Текст3” (появится сразу же после того, как мы приступим к заполнению аргумента “Текст2”). Можно на свое усмотрение указывать любые символы: пробел, знаки препинания, текстовые или числовые значения и т.д.
   • переходим в поле “Текст3” и кликаем по следующей ячейке, содержимое которой нужно добавить в общую ячейку (в нашем случае – это B2).
   • аналогичным образом заполняем все оставшиеся аргументы, после чего жмем кнопку OK. При этом увидеть предварительный результат можно в нижней левой части окна аргументов.
4. Все готово, нам удалось объединить содержимое всех выбранных ячеек в одну общую.
5. Выполнять действия выше для остальных ячеек столбца не нужно. Просто наводим указатель мыши на правый нижний угол ячейки с результатом, и, после того как он сменит вид на небольшой черный плюсик, зажав левую кнопку мыши тянем его вниз до нижней строки столбца (или до строки, для которой требуется выполнить аналогичные действия).
6. Таким образом, получаем заполненный столбец с новыми наименованиями, включающими данные по размеру и полу.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

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

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

Функция входит в группу «текстовые». Синтаксис: (текст 1; текст 2;…). Можно включить до 255 текстовых строк. Первый аргумент – «текст 1» – является обязательным. Любые аргументы можно указать в виде текста, цифр или ссылок на ячейки.

Общие правила использования функции СЦЕПИТЬ:

1. Для разделения аргументов применяется точка с запятой. Пропуск или использование других знаков приведет к отображению в ячейке с результатом кавычек.
2. Текстовые аргументы обязательно заключать в кавычки. В противном случае появляется ошибка – #ИМЯ? Числа и ссылки на ячейки – не нужно.
3. Чтобы соединяемые элементы в строке результата отобразились с пробелом, пробелы нужно добавлять в формулу с функцией. С помощью двойных кавычек с пробелом внутри (“ “) или с помощью пробела после текстового аргумента (“Иванов ”;…).

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

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

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

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

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

Такой же результат получим с помощью амперсанда:

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

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

1. Соединим текст с датой. Данные находятся в отдельных ячейках. Для столбца «Дата» установлен формат «Дата». Если использовать только функцию СЦЕПИТЬ, дата отобразится в числовом формате. Поэтому добавим функцию ТЕКСТ для форматирования чисел. Формула: Второй аргумент функции ТЕКСТ – формат представления даты. Результат:

2. Соединим сразу много ячеек в строке. Если писать формулу с функцией СЦЕПИТЬ, получится долго и малоэффективно. Используем маленькую хитрость. Вот наша строка со значениями в отдельных ячейках. В ячейку Н1 ставим знак «равно». Выделяем диапазон со значениями А1:F1. Вводим амперсанд & и пробел “ “. Жмем F9. В строке формул появится формула массива. Ставим курсор в строку формул после знака «равно». Пишем название функции. Фигурные скобки заменяем на круглые и жмем ВВОД. Получаем нужный результат.

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

4. Соединим несколько значений в столбце. Функция подходит для значений в строках. Описанный выше способ с формулой массива тоже не сработает.

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

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

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

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

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

Примеры:

1. Извлечем первое слово из строки, применив функцию ЛЕВСИМВ. Аргументы – «текст» (ссылка на строку с искомым значением), «количество знаков» (число символов, которые нужно извлечь).

2. Извлечем последнее слово из строки с помощью функции ПРАВСИМВ. Синтаксис похожий (как в предыдущем примере). Только количество знаков считается с конца.

3. Извлечем из записи в строке фамилию (второе слово) с помощью функции ПСТР. Аргументы – «текст» (ссылка на строку), «начальная позиция» (номер символа, с которого программа начнет считать число знаков); «количество знаков». В примере: Е2 – «текст», с 9 знака функция вернет 8 символов.

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

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

На замену этой простой функции приходит более функциональная ее версия – СЦЕПИТЬ. На самом деле, в современных версиях Microsoft Office такой функции уже нет, ее полностью заменяет функция СЦЕП. Пока-что еще ее можно использовать, ее поддержка включена для обратной совместимости, но через некоторое время ее может и не быть. Поэтому рекомендуется в Excel 2016, Online и более новых версиях использовать функцию СЦЕП.

Функция СЦЕПИТЬ – подробное описание

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

В целом, правила использования этой функции следующие:

1. Для разделения аргументов используется знак точки с запятой. Если пользователь решит применить другие знаки, то отображение будет результату в кавычках.
2. Если в качестве аргумента функции используется значение в текстовом формате, которое вводится непосредственно в формулу, его необходимо указывать в кавычках. Если же на такое значение ведет ссылка, то в таком случае кавычки не требуются. То же касается и числовых значений. Если необходимо добавить цифру к строке, то кавычка не требуется. Если нарушить эти правила, будет отображена такая ошибка – #ИМЯ?
3. Если необходимо добавить пробел между соединяемыми элементами, его необходимо добавить, как отдельную текстовую строку, то есть, в кавычках. Вот так: “ “ .

А теперь давайте рассмотрим более подробно синтаксис этой функции. Он очень прост. 

Синтаксис

Итак, по сути аргумент всего один – это текстовая строка, которая должна вставляться. Каждый аргумент, как мы уже знаем, разделяется точкой с запятой. Можно указывать до 255 аргументов. Сами они по своему типу дублируются. Первый аргумент указывать обязательно. И как мы уже знаем, можно указывать аргументы в трех форматах: текст, число и ссылка. 

Сферы применения функции СЦЕПИТЬ

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

1. Бухгалтерия. Например, перед бухгалтером поставлена задача выделить серию и номер документа, а потом вставить эти данные в виде одной строки в одной ячейке. Или же нужно к серии и номеру документа добавить, кем он был выдан. Или же перечислить в одной ячейке сразу несколько квитанций. Вариантов, на самом деле, целая куча, можно перечислять до бесконечности. 
2. Отчеты в офисе. Особенно если необходимо предоставить сводные данные. Или же соединять имя и фамилию.
3. Геймификация. Это очень популярный тренд, который активно используется в образовании, воспитании детей, а также в программах лояльности разных компаний. Следовательно, в сфере образования и бизнесе также эта функция может быть полезной. 

Эта функция входит в стандартный набор, который должен знать каждый пользователь Excel. 

Обратная функция СЦЕПИТЬ в Эксель

По сути, такой функции, которая была бы полностью противоположной функции «СЦЕПИТЬ», нет. Чтобы выполнить разъединение ячеек, используются другие функции, такие как ЛЕВСИМВ и ПРАВСИМВ, а также ПСТР. Первая извлекает из строки определенное количество символов слева. Вторая – справа. А ПСТР умеет делать это с произвольного места и заканчивать в произвольном месте. 

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

Часто встречающиеся неполадки с функцией СЦЕПИТЬ

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

1. В строке результата отображаются кавычки. Чтобы этой проблемы не было, в качестве разделителя нужно использовать точку с запятой. Но, как писали выше, чисел это правило не касается.
2. Слова расположен совсем рядом. Эта проблема возникает, потому что человек не знает всех нюансов использования функции СЦЕПИТЬ. Чтобы слова отображались раздельно, необходимо добавить к ним знак пробела. Или же можно его вставить непосредственно после текстового аргумента (как внутри ячейки, так и если вводить текст отдельно в формулу). Например, так: =СЦЕПИТЬ(“Здравствуйте, “, “уважаемый”). Видим, что здесь к слову «Здравствуйте» в конец был добавлен пробел. 
3. Отображается ошибка #ИМЯ? Это говорит о том, что не были указаны кавычки для текстового аргумента. 

Рекомендации по использованию функции

Чтобы сделать работу с этой функцией более эффективной, необходимо учитывать несколько важных рекомендаций:

1. Используйте & максимально, насколько это возможно. Если у вас есть необходимость соединить всего две текстовые строки, то нет необходимости использовать отдельную функцию для этого. Так электронная таблица будет работать быстрее, особенно на слабых компьютерах с небольшим объемом оперативной памяти. В качестве примера можно привести такую формулу: =A1 & B1. Оно аналогична формуле =СЦЕП(A1,B1). Особенно первый вариант проще при ручном вводе формулы.
2. Если необходимо с текстовой строкой объединить валюту или дату, а также информацию в любом другом формате, кроме вышеперечисленных, то нужно предварительно ее обработать функцией ТЕКСТ. Она предназначена для того, чтобы преобразовывать числа, даты, символы в текст.

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

Распространенные случаи применения функции СЦЕПИТЬ

Итак, общая формула такая: СЦЕПИТЬ([текст2];[текст2];…). В соответствующие места нужно вставить свой текст. Важно учесть, что требование к полученному тексту следующее: оно должно быть меньше, чем длина поля, в которое значение вводится. В качестве атрибутов можно использовать не только заранее определенные значения, но и информацию в ячейках, а также результаты вычислений по другим формулам.

В этом плане нет обязательной рекомендации использовать данные для ввода в текстовом формате. Но конечный результат будет выведен в формате «Текст».

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

Кстати, рекомендация по использованию Excel в целом: всегда учите горячие клавиши. Они помогут значительно сэкономить время.

Но пока вы новичок, придется пользоваться специально созданным окном.

Итак, как его вызвать? Если посмотреть на строку ввода формулы, то слева от нее есть такая маленькая кнопочка «fx». Если ее нажать, то появляется такое меню. Нам нужно выбрать нужную функцию из списка.

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

Если вводить данные вручную, то ввод осуществляется, начиная знаком «равно». То есть, так:

=СЦЕПИТЬ(D2;”.”;E2)

После всех выполненных нами операций увидим в результирующей ячейке текст «21.09», который состоит из нескольких частей: числа 21, который можно найти в ячейке, индексированной как D2 и строки 09, которая находится в ячейке E2. Для того, чтобы они были разделены точкой, мы ее использовали в качестве второго аргумента. 

Связывание имен

Давайте для наглядности рассмотрим такой пример, описывающий, как связывать имена. 

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

Исходя из этой таблицы, мы понимаем, что ссылки на имена надо давать на колонку B, а фамилии – А. Сама же формула будет записываться в первую ячейку под заголовком «Full Name».

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

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

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

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

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

Теперь осталось просто с помощью маркера автозаполнения вставить эту формулу во все остальные ячейки этой колонки. В результате мы имеем полное имя каждого клиента. Задача выполнена.

Точно таким же способом можно соединить штат и город.

Связывание чисел и текста

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

Нам нужен следующий конечный результат.

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

=СЦЕПИТЬ(“We have “;F17;” “;F16)

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

Соединение функции ВПР и СЦЕПИТЬ

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

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

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

После этого сцепляем данные. 

Это можно сделать или такой формулой:

=B2&”/”&C2

Или такой.

=СЦЕПИТЬ(B2;”/”;C2)

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

Далее мы берем следующую таблицу и заполняем ее информацией о товаре, выбранном посетителем. Нам нужно из первой таблицы достать информацию о стоимости товара и номере склада. Это делается с помощью функции ВПР.

Далее выделяем ячейку K2, и записываем в ней такую формулу. 

{=ВПР(G2&”/”&H2;A2:E6;5;0)}

Или же ее можно записать через функцию СЦЕПИТЬ.

{=ВПР(СЦЕПИТЬ(G2;”/”;H2);A2:E6;5;ЛОЖЬ)}

Синтаксис в этом случае аналогичный тому, как осуществлялось объединение информации о номере и складе. 

Нужно обязательно вставлять функцию ВПР через комбинацию клавиш «Ctrl» + «Shift» + «Enter».

Как видим, все просто.