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

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

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

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

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

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

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

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

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

Применение

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

Для добавления атрибутов используйте форму ввода функции. Для этого в нужной ячейке введите знак равенства и нажмите кнопку со значком fx. В открывшемся окне выберите нужный пункт и нажмите «ОК».

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

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

= ЦЕПЬ (D2; «.»; E2)

В результате мы получим в выделенной ячейке текст 21.09, состоящий из данных ячейки D2 — числа 21, точки и данных ячейки E2 — строки «09». Информация в поле E2 — это число, но оно отформатировано как текст, так как важно, чтобы на первом месте стояло число 0. Если такое число преобразовать в числовой формат, этот ноль исчезнет, ​​и только число 9 останется.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примеры

Рассмотрим примеры использования CONCATENATE. Мы перейдем от простых задач к более сложным. Напоследок разберем примеры с разными условиями.

Пример 1. Объедините фамилию, имя и отчество в одной ячейке. В строке формул напишите выражение со ссылками на ячейки.

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

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

Строка формул с амперсандом выглядит так:

Пример 2. Объединяем ячейки разных форматов. Мы будем использовать текстовые вставки и ссылки на ячейки с датами и числовым форматом. Исходные данные:

Формула будет выглядеть так:

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

Результат:

Пример 3. Объединение ячеек с помощью знака препинания или символа.

ЦЕПЬ функция:

И коммерческие:

Здесь кавычки помогают правильно разграничить символы.

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

Соединение текста и даты

И еще одна хитрость с формулой CONCATENA. Чаще всего приходится сочетать текст с датой. Дата, как известно, представляет собой простое порядковое число (отсчет времени начинается с 1 января 1900 года), которому присваивается желаемый формат даты. Если вы напрямую свяжете текст и дату, на выходе будет указанный текст и простое число. Возьмем, например, слово «Сегодня» (не забудьте добавить пробел в конце) и функцию СЕГОДНЯ (которая всегда возвращает текущую дату).

Как видите, ничего не произошло.

Для правильной склейки необходимо «обернуть» дату в формулу ТЕКСТ, которая дает заданный формат числа.

Формат ДД.ММ.АЙ указывает, в какой форме должна быть указана дата. Например, если указать формат ДД.ММ.ГГГГ, дата будет такой: 03.09.2015 (год состоит из 4 цифр).

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

Поэтому я всегда прекрасно знал текущую дату и день недели. При этом содержимое ячейки состояло всего из одной функции СЕГОДНЯ.

Эту идею довольно просто реализовать. Но это уже чистое форматирование. Я при случае расскажу вам поподробнее.

PS Все вышеперечисленные проблемы исправлены в новых функциях Excel СЦЕПИТЬ и ОБЪЕДИНЕНИЕ .

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

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

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

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

Примечания

• Если объединенная строка содержит более 32767 символов (предел ячейки), функция СЦЕПИТЬ вернет ошибку # ЗНАЧ!.

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

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

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

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

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

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

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

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

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

= ПОДКЛЮЧИТЬ (D2; «.»; E2)

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

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

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

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

Основываясь на этой таблице, мы понимаем, что ссылки на имена должны быть указаны в столбце B, а фамилии — в A. Сама формула будет записана в первой ячейке под заголовком «Полное имя».

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

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

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

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

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

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

Точно так же можно связать штат и город.

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

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

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

6

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

= CONNECT («У нас есть»; F17; «»; F16)

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

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

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

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

Один из аргументов — это текст. Формула: = ЦЕПЬ («кузнец»; «»; A2; «»; B2; «»; C2).

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

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

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

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

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

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

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

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

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

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

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