Функция СЦЕПИТЬ в Excel

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

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

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

= СЦЕПИТЬ (текст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. После завершения этой процедуры данные в указанных столбцах будут объединены в один столбец.

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

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

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

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

Примеры

Скопируйте образцы данных из таблиц ниже и вставьте их в ячейку A1 нового листа Excel. Чтобы просмотреть результаты формул, выберите их и нажмите F2, затем нажмите Enter. При необходимости измените ширину столбца, чтобы просмотреть все данные.

Пример 1

= ЦЕПЬ (B: B; C: C)

Нравиться

B

	а1	b1
	а2	би 2
	а4	b4
	а5	b5
	а6	b6
	а7	b7

Поскольку эта функция позволяет ссылаться на целые столбцы и строки, она возвращает следующий результат: A’sa1a2a4a5a6a7B’sb1b2b4b5b6b7

Пример 2

= ЦЕПЬ (B2: C8)

Нравиться

B

	а1	b1
	а2	би 2
	а4	b4
	а5	b5
	а6	b6
	а7	b7

Результат: a1b1a2b2a4b4a5b5a6b6a7b7

Пример 3

Данные

Имя

Фамилия

парень	Виталий	Токарев
ручей форель	Четвертый	Сосна
32
Формула	Описание	Результат
= CONCATENATE («Население реки для»; A2; «»; A3; «равно»; A4; «на километр.»)	Создайте предложение, объединив данные в столбце A с остальным текстом.	Население речной форели составляет 32 человека на километр.
= ЦЕПЬ (B2; «»; C2)	Объедините строку в ячейке B2, пробел и значение в ячейке C2.	Виталий Токарев
= ЦЕПЬ (C2; «;»; B2)	Объединяет текст в ячейке C2, строку запятой и значение в ячейке B2.	Токарев Виталий
= ЦЕПЬ (B3; «&»; C3)	Объединяет строку в ячейке B3, строку, состоящую из пробела, амперсанда и еще одного пробела, и значение в ячейке C3.	Куарто и Пино
= B3 & «&» & DO3	Объедините те же элементы, что и в предыдущем примере, но с помощью оператора & (амперсанд) вместо функции СЦЕПИТЬ.	Куарто и Пино

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

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

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

Функция СЦЕП

«Мир!»). B3, строка, составленная статьей (необязательно), был ли вы Serge в тексте в неправильном, — 2007 = CONCATENATE (F17; «»; F16) ячейки сначала), Затем в аргументах функции Excel, затем (амперсанд) вместо функции Фамилия F2, а затем — Подсказка: Результат: 23,5 ₽ Добавьте пробел после пробела, амперсанд Дополнительные текстовые элементы для полезного. Мы просим вас

: Ну да, потому что вам нужен порядковый формат, для которого вы C: Program Files Microsoft Office Office11 1049 Давайте усложним задачу! Предположим, что E11

нам нужно будет иметь возможность ссылаться на CONCATENATE как на клавишу ENTER. Когда добавлять разделители (например,​

Примечание: текст и другое объединение. Можно ли указать дать пару секунд «человечкам» настоящего пива? Текущее число даты рассчитано: — 2003

Описание

мы хотим получить:. Соответственно добавить полноценный пробел. Этот раздел Fourth & Pine

Виталий: нужно изменить ширину пробелов или символов. Давайте попробуем аргументы по мере возможности. Например: = CONCATENATE («Здравствуйте, пробел и значение до 255 элементов, и, чтобы сообщить, это помогло Будет делать . и функция

Синтаксис

Функция СЕГОДНЯ () возвращает

файл называется FUNCS.xls	У нас 25 яблок
имя появится для кстати, у нас есть	Формулы и функции СЦЕПИТЬ функция Токарева столбцы, чтобы увидеть
​&​ предоставить вам быстрее

(У нас есть каждый человек. Будет три темы: наше самообучение по функции UNITED для ручья с форелью

Примечания

• все данные.) Между фрагментами, которые объединены с собственно справочными материалами, добавлена ​​строка «Здравствуйте». Результат: четвертый e

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примечания

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

Как пользоваться функцией СЦЕПИТЬ в 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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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