В этом руководстве вы узнаете о различных способах как сцепить текстовые значения, ячейки, диапазоны, столбцы и строки в Excel с помощью функций СЦЕПИТЬ, СЦЕП и оператора «&» . Особое внимание мы уделим тому, как сцепить ячейки в Excel с пробелами или другими символами.
В ваших файлах Excel данные не всегда структурированы в соответствии с вашими потребностями. Часто вы можете захотеть разбить содержимое одной ячейки на отдельные ячейки или сделать наоборот — сцепить данные из двух или более столбцов в один столбец. Типичными примерами, требующими таких действий в Excel, являются соединение частей имен и адресов, сцепка текста с числом, рассчитанным при помощи формулы, отображение даты и времени в нужном формате. И это лишь некоторые из них.
В этой статье мы собираемся изучить различные методы как сцепить ячейки в Excel. Вы сможете выбрать тот из них, который лучше всего подходит для ваших рабочих таблиц.
Что такое «сцепить» в Excel?
По сути, существует два способа слияния данных в ячейках электронных таблиц Excel:
- Соединить сами ячейки
- Объединить их значения
Когда вы соединяете ячейки при помощи стандартного инструмента, вы «физически» получаете из двух или более ячеек одну. В результате у вас есть одна большая ячейка, которая отображается в нескольких строках и/или столбцах на вашем листе.
Когда вы хотите сцепить данные из ячеек в Excel, вы соединяете только их содержимое. Другими словами, «сцепить» в Excel — это сличние двух или более значений вместе.
Этот метод часто используется для соединения нескольких фрагментов текста, находящихся в разных ячейках (технически они называются текстовыми строками или просто строками), или для вставки вычисляемого по формуле значения в середину некоторого текста.
На следующем скриншоте показана разница между этими двумя методами:
В этом руководстве мы рассмотрим два основных способа сцепить ячейки в Excel — с помощью функции СЦЕПИТЬ и оператора Excel &. Другие методы с примерами вы можете посмотреть в этой статье: Как объединить данные в ячейках Excel.
Функция СЦЕПИТЬ
Функция СЦЕПИТЬ (CONCATENATE на английском) в Excel предназначена для соединения разных фрагментов текста или значений из нескольких ячеек в одной.
Синтаксис её следующий:
СЦЕПИТЬ(текст1; [текст2]; …)
Где текст — это текстовая строка, ссылка на ячейку или значение, определяемое формулой.
Функция СЦЕПИТЬ поддерживается во всех версиях Excel для Microsoft 365, Excel 2019 — Excel 2007.
Примечание. В Excel 365 — Excel 2019 также доступна функция СЦЕП (CONCAT на английском). Она является современным преемником СЦЕПИТЬ с точно таким же синтаксисом.
Функция СЦЕП
Функция СЦЕП (CONCAT в английской версии) в Excel используется для слияния данных из нескольких ячеек или диапазонов в одну.
Она относится к категории текстовых функций и доступна в Excel для Microsoft 365, Excel 2021, Excel 2019 и Excel для Интернета.
Синтаксис её простой:
СЦЕП(текст1; [текст2]; …)
Где текст1 (обязательный аргумент) и текст2 (необязательный) — данные, которые нужно соединить.
Аргументы могут быть предоставлены в виде текстовых значений, ссылок на ячейки или диапазоны. Принимается не более 255 текстовых аргументов. Данные соединяются в порядке их появления в формуле.
Обратите внимание, что текст, передаваемый непосредственно в формулу, всегда должен быть заключен в двойные кавычки. Например:
=СЦЕП("alex"; "@gmail.com")
6 вещей, о которых нужно помнить
Прежде чем мы углубимся в практические примеры, позвольте мне кратко указать несколько моментов:
- Для работы функций СЦЕПИТЬ и СЦЕП требуется хотя бы один текстовый аргумент. Если какой-либо из аргументов недействителен (например, представлен другой функцией, которая выдает ошибку), то произойдет ошибка.
- Результатом этих функций всегда является текстовая строка, даже если вы сцепляете числа.
- Если результирующая строка превышает 32 767 символов, возникает ошибка #ЗНАЧ! .
- В СЦЕПИТЬ и СЦЕП нет предопределенного параметра для разделителя между соединяемыми значениями. Чтобы разделить сцепленные фрагменты текста, поместите разделители между ссылками на ячейки, например:
=СЦЕП(A1;” “;A2;”-“;A3)
- Пустые ячейки игнорируются.
- Функция СЦЕПИТЬ не распознает массивы. Каждая ссылка на ячейку должна быть указана отдельно. Например, вы должны написать
=СЦЕПИТЬ(A1; A2; A3) вместо =СЦЕПИТЬ(A1:A3).
Ниже вы найдете несколько примеров использования функций слияния данных в Excel.
Соединить значения нескольких ячеек
Простейшая формула чтобы сцепить значения ячеек A1 и B1, выглядит следующим образом:
=СЦЕПИТЬ(A1; B1)
=СЦЕП(A1; B1)
Обратите внимание, что значения будут соединяться вместе без какого-либо разделителя. Вы это можете видеть в строке 2 на скриншоте ниже.
Чтобы разделить значения пробелом, введите " " во второй аргумент, как в строке 4 на скриншоте ниже.
=СЦЕПИТЬ(A3;B3)
Чтобы разделить составные значения с помощью других разделителей, таких как запятая, пробел или косая черта, см. раздел Как СЦЕПИТЬ формулы со специальными символами.
Как соединить текст и значение ячейки
Нет никаких причин, по которым можно ограничиться только соединением значений отдельных ячеек. Вы также можете прикреплять к ним различные текстовые строки, чтобы сделать результат более значимым. Например:
=СЦЕПИТЬ(A9;B9;" завершено")
Приведенная выше формула информирует пользователя о том, что определенное задание завершено. Например как в строке 9 на скриншоте выше. Обратите внимание, что мы добавляем пробел перед словом «завершено», чтобы разделить сцепленные текстовые строки.
Естественно, вы также можете добавить текстовую строку в начале или в середине формулы:
=СЦЕПИТЬ("Смотри ";A10; " "; B10)
Целесообразно сцепить ячейки Excel с пробелами, чтобы корректно отображать итоговый текст. Пробел ("") добавляется между значениями ячеек, чтобы результат отображался как "Задание 1", а не "Задание1".
Сцепить текст и значение, рассчитанное по формуле
Нужно сделать результат, возвращаемый какой-либо формулой, более понятным для ваших пользователей. Вы можете соединить его с текстом, поясняющим, что на самом деле представляет собой это значение.
Например, вы можете сцепить дату и текст в Excel, чтобы возвратить в удобной форме текущую дату:
=СЦЕПИТЬ("Сегодня "; ТЕКСТ(СЕГОДНЯ(); "dd-mmm-yy"))
Совет. Может понадобится зафиксировать полученный результат, не затрагивая исходные данные. Используйте «Специальная вставка — только значения», чтобы преобразовать формулы в их значения.
Простой способ сцепить ячейки при помощи оператора «&»
В Microsoft Excel оператор & — это еще один способ сцепить текст из ячеек. Этот метод очень удобен, потому что ввод знака амперсанда (&) намного быстрее, чем ввод слова «СЦЕПИТЬ» :)
Вы можете использовать «&» в Excel для соединения различных текстовых строк, значений ячеек и результатов, возвращаемых другими функциями.
Примеры формул Excel "&"
Чтобы увидеть оператор & в действии, давайте перепишем формулы, рассмотренные чуть выше:
Соедините значения в A1 и B1:
=A1&B1
Сцепите значения в A1 и B1 с пробелом между ними:
=A1&" "&B1
Сцепите значения A1, B1 и поясняющий текст:
=A1 & B1 & " завершено"
Соедините текстовую строку и результат функции ТЕКСТ / СЕГОДНЯ:
="Сегодня " & ТЕКСТ(СЕГОДНЯ(), "dd-mmm-yy")
Функция СЦЕПИТЬ и оператор «&» возвращают совершенно идентичные результаты.
Оператор Excel «&» против функции СЦЕПИТЬ
Многие пользователи задаются вопросом, какой способ сцепить текст в Excel более эффективен — функция СЦЕПИТЬ или оператор «&».
Единственным существенным различием между оператором СЦЕПИТЬ и оператором «&» является ограничение в 255 строк функции СЦЕПИТЬ в Excel и отсутствие таких ограничений при использовании амперсанда &. Кроме этого, больше нет никакой разницы между этими двумя методами сцепки ячеек. Также нет отличий в скорости между формулами СЦЕПИТЬ и "&".
255 — это действительно большое число, и в реальных задачах вряд ли кому-то когда-нибудь понадобится соединять столько значений. Поэтому разница между ними сводится к удобству и простоте использования. Некоторым пользователям формулу СЦЕПИТЬ легче читать, но лично я предпочитаю использовать метод "&".
Итак, просто придерживайтесь той техники, с которой вы чувствуете себя более комфортно.
Как сцепить ячейки с пробелами, запятой или другими символами
В ваших рабочих листах Excel вам часто может понадобиться сцепить значения таким образом, чтобы они включали запятые, пробелы, различные знаки препинания или другие символы, такие как дефис или наклонная черта.
Чтобы сцепить ячейки в Excel с пробелами или с другим символом, добавьте нужный символ в свою формулу. Не забудьте также заключить этот символ в кавычки, как показано в следующих примерах.
Как сцепить две ячейки Excel с пробелом:
=СЦЕПИТЬ(A1; " "; B1)
или
=A1 & " " & B1
Соединить значения двух ячеек с запятой между ними:
=СЦЕПИТЬ(A1; ", "; B1)
или
=A1 & ", " & B1
Соединить два значения, разделив их дефисом:
=СЦЕПИТЬ(A1; "-"; B1)
или
=A1 & "-" & B1
На этом скриншоте показано, как могут выглядеть результаты:
Чаще всего вы будете разделять сцепленные текст, числа и даты знаками препинания и пробелами. Однако в некоторых случаях может потребоваться разделить значения переносом строки. Типичным примером является получение полных почтовых адресов из данных, находящихся в отдельных ячейках.
Проблема в том, что вы не можете записать в формулу перенос строки, как обычный символ. Поэтому необходима специальная функция СИМВОЛ для подачи соответствующего ASCII-кода в формулу:
- В Windows используйте СИМВОЛ(10), где 10 — это код ASCII для перевода строки.
- В системе Mac используйте СИМВОЛ(13), где 13 — код ASCII для возврата каретки.
В этом примере у нас есть фрагменты адреса в столбцах от A до F. Мы сцепляем их в столбце G с помощью оператора «&». Сцепленные значения разделяются запятой (","), пробелом (" ") и разрывом строки СИМВОЛ(10):
=A2&" "&B2&СИМВОЛ(10)&C2&СИМВОЛ(10)&D2&", "&E2&" "&F2
Примечание. При использовании разрывов строк для разделения значений необходимо включить параметр форматирования «Перенос текста», чтобы результат отображался правильно. Для этого нажмите Ctrl + 1, чтобы открыть диалоговое окно «Формат ячейки», перейдите на вкладку «Выравнивание» и установите флажок «Переносить текст».
Таким же образом вы можете разделить соединяемые текстовые фрагменты другими символами, такими как:
- Двойные кавычки ("") - СИМВОЛ(34)
- Косая черта (/) - СИМВОЛ(47)
- Звездочка (*) - СИМВОЛ(42)
Тем не менее, более простой способ включить печатные символы в формулу. Можно просто вставить их в двойных кавычках, как мы сделали в предыдущем примере.
В любом случае, все приведенные ниже формулы дают одинаковые результаты:
=A1 & СИМВОЛ(47) & B1
=A1 & "/" & B1
=СЦЕПИТЬ(A1; СИМВОЛ(47); B1)
=СЦЕПИТЬ(A1; "/"; B1)
Как построчно сцепить столбцы в Excel
Чтобы сцепить два или более столбца в Excel, вы просто вводите формулу чтобы сцепить текст в первую ячейку. Затем копируете ее в другие ячейки, перетаскивая маркер заполнения (маленький квадрат, который появляется в правом нижнем углу поля).
Например, чтобы соединить текст из двух столбцов (столбец A и B), разделяя эти значения пробелом, введите следующую формулу в ячейку C2, а затем скопируйте ее вниз в другие ячейки. Когда вы перетаскиваете маркер заполнения, чтобы скопировать формулу, указатель мыши принимает форму креста, как показано ниже:
Совет. Есть быстрый способ скопировать формулу в другие ячейки столбца. Выделите ячейку с формулой и дважды щелкнуть маркер заполнения.
Обратите внимание, что Microsoft Excel определяет, насколько далеко копировать ячейки после двойного щелчка дескриптора заполнения, на основе ячеек, на которые ссылается ваша формула. Если в вашей таблице окажутся пустые ячейки, например ячейки A6 и B6 были пустыми, формула будет скопирована только до строки 5. В этом случае вам нужно будет вручную перетащить маркер заполнения вниз.
Как сцепить диапазон ячеек в Excel
Соединение значений из нескольких ячеек может потребовать некоторых усилий. Ведь функция Excel СЦЕПИТЬ не работает с массивами и требует ссылки на одну ячейку в каждом аргументе.
Чтобы сцепить все значения из диапазона ячеек, скажем, от A1 до A4, вам понадобится одна из следующих формул:
=СЦЕПИТЬ(A1; A2; A3; A4)
или
=A1 & A2 & A3 & A4
При работе с небольшой областью нет ничего сложного в том, чтобы ввести все ссылки в строку формул. Было бы утомительно добавлять большой диапазон, вводя ссылку на каждую его ячейку вручную. Ниже вы найдете 3 метода как быстро сцепить ячейки из заданного диапазона в Excel.
1. Нажмите CTRL, чтобы выбрать несколько ячеек
Чтобы быстро выбрать несколько ячеек, вы можете нажать клавишу CTRL и кликнуть мышкой каждую ячейку, которую хотите включить в формулу СЦЕПИТЬ. Вот подробные шаги:
- Выберите ячейку, в которую вы хотите ввести формулу.
- Введите
=СЦЕПИТЬ(в этой ячейке или в строке формул. - Нажмите и удерживайте
Ctrlи кликните последовательно на каждую ячейку, которую вы хотите задействовать. - Отпустите
Ctrl, введите закрывающую скобку в строке формул и нажмитеENTER.
Примечание. При использовании этого метода вы должны щелкнуть каждую отдельную ячейку. Выбор диапазона с помощью мыши добавит в формулу массив, который функция СЦЕПИТЬ не принимает.
2. Используйте функцию ТРАНСП, чтобы получить диапазон
Когда вам нужно сцепить большой диапазон, состоящий из десятков или сотен ячеек, предыдущий метод недостаточно быстр и удобен, поскольку требует клика по каждой ячейке. В этом случае лучше использовать функцию ТРАНСП, чтобы взять диапазон ячеек, а затем заменить его значениями отдельных ячеек одним махом.
- В ячейку, куда вы хотите записать результат, введите формулу ТРАНСП, например:
=ТРАНСП(A1:A10)
- Установите курсор в строку формул и нажмите
F9, чтобы заменить это выражение вычисленными значениями. В результате у вас будет массив значений для дальнейшего использования его в формуле. - Удалите фигурные скобки, окружающие значения массива.
- Введите
=СЦЕПИТЬ(перед первым значением, затем введите закрывающую скобку после последнего значения и нажмитеEnter.
Примечание. Какой бы метод вы ни использовали, итоговое значение в C1 представляет собой текстовую строку (обратите внимание на ее выравнивание по левому краю в ячейке), даже если каждое из исходных значений является числом. Это связано с тем, что функция СЦЕПИТЬ всегда возвращает текстовую строку независимо от типа исходных данных.
3. Сцепить два или более столбца
А сейчас рассмотрим функцию СЦЕП, которая позволяет гораздо проще и быстрее сцепить несколько ячеек, строк или столбцов.
Чтобы сцепить значения из нескольких столбцов построчно, вам нужно сделать следующее:
- Напишите формулу СЦЕП, чтобы сцепить ячейки в первой строке.
- Скопируйте формулу на столько строк, сколько необходимо.
Например, чтобы соединить текст в столбцах A, B, C и D в каждой строке, введите эту формулу в E2:
=СЦЕП(A2:D2)
Затем перетащите маркер заполнения вниз, чтобы скопировать формулу в строки ниже. Если в исходных данных нет пропусков, можно просто дважды щелкнуть маркер заполнения.
Из-за использования относительных ссылок формула корректируется соответствующим образом для каждой строки. То есть в E3 ссылка изменится на A3:В3, и так далее.
Чтобы сцепить в одной ячейке все значения из определенного столбца, укажите ссылку на этот столбец в формуле СЦЕП. Например:
=СЦЕП(A:A)
Поскольку функция пропускает пустые ячейки, в результирующей строке не будет пробелов:
Чтобы задействовать в этом методе все ячейки в столбце, кроме заголовка, используйте ссылку на диапазон, например A2:A1048576, где A2 — первая ячейка с данными, а A1048576 — последняя ячейка в столбце:
=СЦЕП(A2:A1048576)
Примечание. Если вы решите сцепить текст сразу из всех ячеек в столбце, имейте в виду, что Excel имеет ограничение на общее количество символов, которое может содержать ячейка — до 32 767. Если результирующая строка превышает этот предел, ваша формула выдаст ошибку #ЗНАЧ!.
Также вы не можете с данном случае сцепить ячейки Excel с пробелами. Поэтому итоговый текст может быть нечитаемым. Гораздо больше возможностей в этом плане дает функция ОБЪЕДИНИТЬ. О ней вы можете получить подробную информацию здесь: Как объединить текст в ячейках Excel в одну.
4. Сцепить диапазон ячеек
Соединить текст из диапазона ячеек в Excel раньше было проблемой. Теперь вы можете просто поместить ссылку на диапазон в функцию СЦЕП:
=СЦЕП(A2:C3)
В результате все значения из указанного диапазона соединяются слева направо и затем вниз до следующей строки:
Аналогичным образом вы можете сцепить ячейки в двух или более несмежных диапазонах Excel:
=СЦЕП(A2:C3; A5:C6)
Как видно из этих примеров, использование функции СЦЕП намного предпочтительнее и проще.
Совет. После того, как вы сцепили несколько ячеек, у вас может получиться достаточно длинный текст. Чтобы удобно разместить его в ячейке, попробуйте воспользоваться этими инструкциями: Как перенести текст на другую строку в ячейке Excel.
Как сцепить текст, число и дату в различных форматах
НужноКогда вы хотите сцепить дату и текст в Excel, вы встретитесь с одной проблемой. Нужно отформатировать результат по-разному в зависимости от вашего набора данных и решаемых задач. Для этого используйте функцию ТЕКСТ.
Функция ТЕКСТ([значение], [формат_текста]) имеет два аргумента:
В первом аргументе ( [значение] ) вы указываете число или дату, которые нужно преобразовать в текст, или вставляете ссылку на ячейку, содержащую числовое значение.
Во втором аргументе ( [формат_текста] ) вы вводите желаемый формат, используя коды, которые может понять функция ТЕКСТ.
Когда вы хотите сцепить текст и дату, то важно помнить, что дата хранится в Excel в виде числа. Поэтому если вы просто соедините содержимое ячеек, результат вас вряд ли порадует. Необходимо преобразовать дату в текст в нужном вам формате, и только после этого сцепить ее с каким- то другим текстом.
Мы уже рассматривали ранее, что если нужно сцепить дату и текст, то приходится использовать функцию ТЕКСТ для отображения даты в нужном формате. Например:
=СЦЕПИТЬ("Сегодня "; ТЕКСТ(СЕГОДНЯ(); "ДД-МММ-ГГ"))
или
="Сегодня "& ТЕКСТ(СЕГОДНЯ(); "ДД-МММ-ГГ"")
Вы это можете видеть на скриншоте ниже.
Ниже приведены еще несколько примеров формул, чтобы сцепить текст и число:
=A2 & " " & ТЕКСТ(B2; "$# ##0,0") – отображать число с одним знаком после запятой и знаком $.
=A2 & " " & ТЕКСТ(B2; "₽# ##0,0") – отображает число и знак рубля ₽.
=A2&" "&ТЕКСТ(B2;"#__?/10") – отображать число в виде дроби.
СЦЕП против СЦЕПИТЬ в Excel
Фактически, функция СЦЕП является заменой СЦЕПИТЬ в новых версиях Excel. Хотя старая функция по-прежнему поддерживается из соображений совместимости, вместо нее рекомендуется использовать более новую функцию.
С технической точки зрения существует два основных различия между СЦЕП и СЦЕПИТЬ в Excel:
- Прежде всего, функция СЦЕП преодолевает самое большое ограничение своего предшественника — невозможность обработки диапазона ячеек. В отличие от функции СЦЕПИТЬ, она может соединить в одной ячейке все значения в диапазоне, избавляя вас от необходимости указывать ссылку на каждую ячейку по отдельности.
- Функция СЦЕП доступна только в Excel 365, Excel 2021, Excel 2019 (Windows и Mac) и Excel Online. Функция СЦЕПИТЬ поддерживается во всех версиях Excel с 2007 по 365.
Во всем остальном эти две функции по существу одинаковы.
Также рекомендую еще несколько статей по этой же теме:
Как объединить текст в ячейках Excel в одну
Способы соединить ячейки в Excel в одну без потери данных
Как правильно соединить столбцы в Excel без потери данных
Быстро объединить несколько файлов Excel
Как соединить две или несколько таблиц в Excel.
Надстройка «Merge Cells» — способ сцепить ячейки в Excel без использования формул
С надстройкой «Сцепить ячейки» (Merge Cells), включенной в Ultimate Suite for Excel , вы можете эффективно выполнять следующие задачи:
- Соединить несколько ячеек в одну без потери данных.
- Слить значения нескольких ячеек в одну ячейку и разделить их любым разделителем по вашему выбору.
Инструмент «Merge Cells» работает со всеми версиями Excel и может сцепить любые типы данных, включая текст, числа, даты и специальные символы. Два его ключевых преимущества — простота и скорость — все делается в пару кликов. А теперь позвольте мне показать вам это в действии.
Чтобы сцепить ячейки в Excel с пробелами или с любыми другими символами, вы выбираете диапазон и настраиваете следующие параметры:
- Ячейки в одну под " Как сцепить ";
- Выберите нужный разделитель в разделе « Разделить значения с помощью », в данном примере это точка с запятой и пробел;
- Выберите, где вы хотите разместить результат;
- И самое главное – снимите флажок «Объединить все области в выделенном». Именно этот параметр определяет, соединяются ли сами ячейки или только сцепляются их значения.
Процесс вы видите на скриншоте ниже.
Вот сам результат:
Чтобы соединить два или более столбца, вы настраиваете параметры «Ячейки слияния» аналогичным образом, но выбираете «Столбцы» в разделе « Что соединять»:
Чтобы проверить, как надстройка Merge Cells будет работать с вашими наборами данных, вы можете использовать эту ссылку для загрузки полнофункциональной пробной версии Ultimate Suite для Excel.
Надеюсь, что эта информация поможет вам сцепить ячейки в Excel быстро, безошибочно и без потери данных.
