Функция СЦЕПИТЬ в Excel: объединение содержимого ячеек
Функция СЦЕПИТЬ в Эксель позволяет объединить содержимое двух и более ячеек в одну общую, что делает ее одним из самых полезных и необходимых инструментов программы, которым непременно нужно овладеть для эффективной работы. Давайте посмотрим, как работать с данной функцией.
Описание и синтаксис функции СЦЕПИТЬ
Данная функция начиная с Excel 2016 сменила название на “СЦЕП”, однако, для тех, кто привык к старому наименованию (“СЦЕПИТЬ”), оператор под этим названием также доступен в программе. СЦЕП и СЦЕПИТЬ абсолютно взаимозаменяемы и выполняют одну и ту же операцию по объединению содержимого выбранных ячеек в одну общую.
Формула функций выглядит так:
=СЦЕП(текст1;текст2;. ) или =СЦЕПИТЬ(текст1;текст2;. )
Максимальное количество аргументов – 255.
Вставка и настройка функции
Как мы знаем, при объединении нескольких ячеек в одну, содержимое всех элементов за исключением самой верхней левой стирается. Чтобы этого не происходило, нужно использовать функцию СЦЕПИТЬ (СЦЕП).
- Для начала определяемся с ячейкой, в которой планируем объединить данные из других. Переходим в нее (выделяем) и щелкаем по значку “Вставить функцию” (fx).
- В открывшемся окне вставки функции выбираем категорию “Текстовые” (или “Полный алфавитный перечень”), отмечаем строку “СЦЕП” (или “СЦЕПИТЬ”) и кликаем OK.
- На экране появится окно, в котором нужно заполнить аргументы функции, в качестве которых могут быть указаны как конкретные значения, так и ссылки на ячейки. Причем последние можно указать как вручную, так и просто кликнув по нужным ячейкам в самой таблице (при это курсор должен быть установлен в поле для ввода значения напротив соответствующего аргумента). В нашем случае делаем следующее:
- находясь в поле “Текст1” щелкаем по ячейке (A2), значение которой будет стоять на первом месте в объединенной ячейке;
- кликаем по полю “Текст2”, где ставим запятую и пробел (“, “), которые будут служит разделителем между содержимыми ячеек, указанных в аргументах “Текст1” и “Текст3” (появится сразу же после того, как мы приступим к заполнению аргумента “Текст2”). Можно на свое усмотрение указывать любые символы: пробел, знаки препинания, текстовые или числовые значения и т.д.
- переходим в поле “Текст3” и кликаем по следующей ячейке, содержимое которой нужно добавить в общую ячейку (в нашем случае – это B2).
- аналогичным образом заполняем все оставшиеся аргументы, после чего жмем кнопку OK. При этом увидеть предварительный результат можно в нижней левой части окна аргументов.
- находясь в поле “Текст1” щелкаем по ячейке (A2), значение которой будет стоять на первом месте в объединенной ячейке;
- Все готово, нам удалось объединить содержимое всех выбранных ячеек в одну общую.
- Выполнять действия выше для остальных ячеек столбца не нужно. Просто наводим указатель мыши на правый нижний угол ячейки с результатом, и, после того как он сменит вид на небольшой черный плюсик, зажав левую кнопку мыши тянем его вниз до нижней строки столбца (или до строки, для которой требуется выполнить аналогичные действия).
- Таким образом, получаем заполненный столбец с новыми наименованиями, включающими данные по размеру и полу.
Аргументы функции без разделителей
Если разделители между содержимыми ячеек не нужны, в этом случае в значении каждого аргумента сразу указываем адреса требуемых элементов.
Правда, таким способом пользуются редко, так как сцепленные значения сразу будут идти друг за другом, что усложнит дальнейшую работу с ними.
Указание разделителя в отдельной ячейке
Вместо того, чтобы вручную указывать разделитель (пробел, запятая, любой другой символ, текст, число) в аргументах функции, его можно добавить в отдельную ячейку, и затем в аргументах просто ссылаться на нее.
Например, мы добавляем запятую и пробел (“, “) в ячейку B16.
В этом случае, аргументы функции нужно заполнить следующим образом.
Но здесь есть один нюанс. Чтобы при копировании формулы функции на другие ячейки не произошло нежелательного сдвига адреса ячейки с разделителем, ссылку на нее нужно сделать абсолютной. Для этого выделив адрес в поле соответствующего аргумента нажимаем кнопку F4. Напротив обозначений столбца и строки появятся символы “$”. После этого можно нажимать кнопку OK.
Визуально в ячейке результат никак не будет отличаться от полученного ранее.
Однако формула будет выглядет иначе. И если мы решим изменить разделитель (например, на точку), нам не нужно будет корректировать аргументы функции, достаточно будет просто изменить содержимое ячейки с разделителем.
Как ранее было отмечено, добавить в качестве разделителя можно любую текстовую, числовую и иную информацию, которой изначально не было в таблице.
Таким образом, функция СЦЕП (СЦЕПИТЬ) предлагает большую вариативность действий, что позволяет наилучшим образом представить объединенные данные.
Редактирование функции
Если функция уже добавлена в ячейку, но в ее аргументы требуется внести определенные правки, сделать это можно двумя способами.
Способ 1: изменения в окне аргументов
Перейти к редактированию функции можно, просто нажав на кнопку “Вставить функцию” (fx), которая находится слева от строки формул. При этом предварительно нужно выбрать ячейку, содержащую функцию.
Таким образом мы попадем в окно с аргументами функции, где можем внести требуемые изменения, после чего нажать кнопку OK.
Мы получим результат с учетом скорректированных данных.
Способ 2: ручная корректировка формулы
Альтернативный метод изменения аргументов функции – ручная корректировка формулы. Перейти в режим редактирования можно двумя по-разному:
- Выделяем ячейку с функцией, затем щелкаем по строке формул и вносим в ней изменения.
- Двойным щелчком мыши по ячейке с формулой активируем режим редактирования и корректируем формулу, непосредственно, в самой ячейке.
Независимо от выбранного метода, при ручной корректировке формул нужно быть предельно внимательным, соблюдая следующие правила:
- адреса ячеек пишутся без кавычек;
- перечисление аргументов – через знак “;”
- если в качестве аргумента используется символ или текст, наличие кавычек с двух сторон указанного значения обязательно.
Заключение
Благодаря умению пользоваться функцией СЦЕП (СЦЕПИТЬ) можно автоматизировать процесс объединения содержимого разных ячеек в одну общую, что в некоторых случаях, является единственным условием успешного выполнения поставленной задачи. Поэтому очень важно научиться правильно работать с данной функцией в программе Эксель.
Функция СЦЕПИТЬ в Excel: синтаксис и примеры использования
Чтобы объединить значения (текстовые, цифровые) из разных ячеек в одну, используется функция СЦЕПИТЬ. Ее аналог – & (амперсанд). Знак справляется с простейшими задачами. Но для объединения множества строк не подходит.
Синтаксис функции СЦЕПИТЬ
Функция входит в группу «текстовые». Синтаксис: (текст 1; текст 2;…). Можно включить до 255 текстовых строк. Первый аргумент – «текст 1» – является обязательным. Любые аргументы можно указать в виде текста, цифр или ссылок на ячейки.
Общие правила использования функции СЦЕПИТЬ:
- Для разделения аргументов применяется точка с запятой. Пропуск или использование других знаков приведет к отображению в ячейке с результатом кавычек.
- Текстовые аргументы обязательно заключать в кавычки. В противном случае появляется ошибка – #ИМЯ? Числа и ссылки на ячейки – не нужно.
- Чтобы соединяемые элементы в строке результата отобразились с пробелом, пробелы нужно добавлять в формулу с функцией. С помощью двойных кавычек с пробелом внутри (“ “) или с помощью пробела после текстового аргумента (“Иванов ”;…).
Как пользоваться функцией СЦЕПИТЬ в Excel
Начнем с простейших примеров.
Данные для объединения:
Ставим курсор в ячейку Е2 и вызываем мастер функций (кнопка fx). В категории «Текстовые» находим функцию СЦЕПИТЬ. Аргументы – ссылки на ячейки А2, В2, С2 и пробелы:
Результат объединения значений в ячейках:
Такой же результат получим с помощью амперсанда:
Один из аргументов – текст. Формула: =СЦЕПИТЬ(«слесарь»; » «;A2;» «;B2;» «;C2). Результат:
И с более сложными задачами справится функция СЦЕПИТЬ в Excel. Примеры:
- Соединим текст с датой. Данные находятся в отдельных ячейках. Для столбца «Дата» установлен формат «Дата». Если использовать только функцию СЦЕПИТЬ, дата отобразится в числовом формате. Поэтому добавим функцию ТЕКСТ для форматирования чисел. Формула: Второй аргумент функции ТЕКСТ – формат представления даты. Результат:
- Соединим сразу много ячеек в строке. Если писать формулу с функцией СЦЕПИТЬ, получится долго и малоэффективно. Используем маленькую хитрость. Вот наша строка со значениями в отдельных ячейках. В ячейку Н1 ставим знак «равно». Выделяем диапазон со значениями А1:F1. Вводим амперсанд & и пробел “ “. Жмем F9. В строке формул появится формула массива. Ставим курсор в строку формул после знака «равно». Пишем название функции. Фигурные скобки заменяем на круглые и жмем ВВОД. Получаем нужный результат.
- Соединим значения через косую черточку («-», «&» и т.п.). Формула будет выглядеть так: = СЦЕПИТЬ (ссылка на ячейку 1; «/»; ссылка на ячейку 2).
- Соединим несколько значений в столбце. Функция подходит для значений в строках. Описанный выше способ с формулой массива тоже не сработает.
Преобразуем вертикальный диапазон в горизонтальный с помощью функции ТРАНСП. А затем воспользуемся формулой массива для быстрого соединения значений. В ячейку В1 ставим знак «равно». Пишем ТРАНСП. Указываем диапазон А1:А6. Вводим & и пробел (« »). Жмем F9.
Теперь преобразуем формулу массива, добавив функцию и заменив скобки.
Обратная функция СЦЕПИТЬ в Excel
Стандартной функции, которая разделяла бы значения одной ячейки, нет. Для выполнения ряда подобных задач можно использовать другие функции:
- ЛЕВСИМВ (отображает заданную часть символов с начала строки);
- ПРАВСИМВ (отображает заданную часть символов с конца строки);
- ПСТР (отображает указанную часть символов, начиная с заданной позиции) и другие.
- Извлечем первое слово из строки, применив функцию ЛЕВСИМВ. Аргументы – «текст» (ссылка на строку с искомым значением), «количество знаков» (число символов, которые нужно извлечь).
- Извлечем последнее слово из строки с помощью функции ПРАВСИМВ. Синтаксис похожий (как в предыдущем примере). Только количество знаков считается с конца.
- Извлечем из записи в строке фамилию (второе слово) с помощью функции ПСТР. Аргументы – «текст» (ссылка на строку), «начальная позиция» (номер символа, с которого программа начнет считать число знаков); «количество знаков». В примере: Е2 – «текст», с 9 знака функция вернет 8 символов.
Для выполнения более сложных задач используются более сложные формулы, составляются пользовательские функции.
3 способа склеить текст из нескольких ячеек
Надпись на заборе: «Катя + Миша + Семён + Юра + Дмитрий Васильевич +
товарищ Никитин + рыжий сантехник + Витенька + телемастер Жора +
сволочь Редулов + не вспомнить имени, длинноволосый такой +
ещё 19 мужиков + муж = любовь!»
Способ 1. Функции СЦЕПИТЬ, СЦЕП и ОБЪЕДИНИТЬ
В категории Текстовые есть функция СЦЕПИТЬ (CONCATENATE) , которая соединяет содержимое нескольких ячеек (до 255) в одно целое, позволяя комбинировать их с произвольным текстом. Например, вот так:
Нюанс: не забудьте о пробелах между словами — их надо прописывать как отдельные аргументы и заключать в скобки, ибо текст.
Очевидно, что если нужно собрать много фрагментов, то использовать эту функцию уже не очень удобно, т.к. придется прописывать ссылки на каждую ячейку-фрагмент по отдельности. Поэтому, начиная с 2016 версии Excel, на замену функции СЦЕПИТЬ пришла ее более совершенная версия с похожим названием и тем же синтаксисом — функция СЦЕП (CONCAT) . Ее принципиальное отличие в том, что теперь в качестве аргументов можно задавать не одиночные ячейки, а целые диапазоны — текст из всех ячеек всех диапазонов будет объединен в одно целое:
Для массового объединения также удобно использовать новую функцию ОБЪЕДИНИТЬ (TEXTJOIN) , появившуюся начиная с Excel 2016. У нее следующий синтаксис:
=ОБЪЕДИНИТЬ( Разделитель ; Пропускать_ли_пустые_ячейки ; Диапазон1 ; Диапазон2 . )
- Разделитель — символ, который будет вставлен между фрагментами
- Второй аргумент отвечает за то, нужно ли игнорировать пустые ячейки (ИСТИНА или ЛОЖЬ)
- Диапазон 1, 2, 3 . — диапазоны ячеек, содержимое которых хотим склеить
Способ 2. Символ для склеивания текста (&)
Это универсальный и компактный способ сцепки, работающий абсолютно во всех версиях Excel.
Для суммирования содержимого нескольких ячеек используют знак плюс «+«, а для склеивания содержимого ячеек используют знак «&» (расположен на большинстве клавиатур на цифре «7»). При его использовании необходимо помнить, что:
- Этот символ надо ставить в каждой точке соединения, т.е. на всех «стыках» текстовых строк также, как вы ставите несколько плюсов при сложении нескольких чисел (2+8+6+4+8)
- Если нужно приклеить произвольный текст (даже если это всего лишь точка или пробел, не говоря уж о целом слове), то этот текст надо заключать в кавычки. В предыдущем примере с функцией СЦЕПИТЬ о кавычках заботится сам Excel — в этом же случае их надо ставить вручную.
Вот, например, как можно собрать ФИО в одну ячейку из трех с добавлением пробелов:
Если сочетать это с функцией извлечения из текста первых букв — ЛЕВСИМВ (LEFT) , то можно получить фамилию с инициалами одной формулой:
Способ 3. Макрос для объединения ячеек без потери текста.
Имеем текст в нескольких ячейках и желание — объединить эти ячейки в одну, слив туда же их текст. Проблема в одном — кнопка Объединить и поместить в центре (Merge and Center) в Excel объединять-то ячейки умеет, а вот с текстом сложность — в живых остается только текст из верхней левой ячейки.
Чтобы объединение ячеек происходило с объединением текста (как в таблицах Word) придется использовать макрос. Для этого откройте редактор Visual Basic на вкладке Разработчик — Visual Basic (Developer — Visual Basic) или сочетанием клавиш Alt + F11 , вставим в нашу книгу новый программный модуль (меню Insert — Module) и скопируем туда текст такого простого макроса:
Теперь, если выделить несколько ячеек и запустить этот макрос с помощью сочетания клавиш Alt + F8 или кнопкой Макросы на вкладке Разработчик (Developer — Macros) , то Excel объединит выделенные ячейки в одну, слив туда же и текст через пробелы.
Объединить текст в Excel
Необходимость объединить текст в Excel появляется очень при работе с многими документами, но многие не знают, как можно очень просто и быстро справится с этой задачей с помощью табличного редактора MS Excel.
В этой статье мы на примерах рассмотрим, как в Excel объединить ячейки с текстом. Объединить текст в нескольких ячейках можно несколькими способами. Рассмотрим каждый из них.
Все рассмотренные далее способы объединения текста применяются для всех версий: Excel 2003, Excel 2007, Excel 2010, Excel 2013, Excel 2016. Рассмотрим каждый из них.
Объединить ячейки с текстом с помощью знака амперсанда
Итак, первый способ. Самый простой способ объединить текст в нескольких ячейках в Excel – это использовать знак амперсанда (&).
Например, у нас есть таблица с тремя столбцами в которых содержится фамилия, имя и отчество сотрудников компании или учащихся, студентов.
Объединить текст в Excel – Таблица с данными
Нам необходимо создать столбец, который будет содержать в одной ячейке фамилию, имя и отчество. То есть объединить текст в нескольких ячейках Excel.
1. Переходим ячейку D2 . В данной ячейке мы будем объединять текст из нескольких ячеек.
2. Вводим знак =, и кликаем по ячейке с фамилией ( A2 ), затем вводим знак амперсанда & при помощи сочетания клавиш SHIFT+7 .
3. Далее нам необходимо поставить разделитель, чтобы наша фамилия «не слиплась» с именем и отчеством. В данном случае мы будем использовать пробел. Для этого после амперсанда ставим пробел, заключенный в кавычки ( » » ).
Разделитель необязательно должным пробелом, вы можете использовать любой символ, знак или комбинацию. Для этого необходимо заключить их в кавычки. Например, если вы хотите разделить объединяемый текст с помощью запятых, то используйте запятую и пробел после нее ( «, » ).
4. Далее снова вводим знак амперсанда & , и кликаем по ячейке с именем ( B2 ).
5. Опять таки не забываем по разделитель – пробел в кавычках ( » » ).
6. Осталось только присоединить отчество. Вводим знак & и кликаем на ячейку С2 .
7. Нажимаем клавишу Enter и любуемся полученным результатом.
Получившаяся формула выглядит так:
И в нашей ячейке D2 появился объединенный текст из трех ячеек.
Объединить текст в Excel – Объединить текст с помощью амперсанда
Чтобы заполнить все оставшиеся ячейки достаточно выделить ячейку, и перетащить маркер заполнения вниз. Теперь все ячейки заполнены объединенным текстом, и столбец « ФИО » выглядит следующим образом:
Объединить текст в Excel – Объединенный текст для всех сотрудников
Если необходимо объединить текст в двух ячейках, например, объединить фамилию и имя, то формула для первого человека будет выглядеть так:
Объединить текст в Excel – Объединить 2 ячейки с текстом
Давайте рассмотрим еще один случай, когда необходимо объединить текст в нескольких ячейках, но не полностью. Например, получить в ячейке фамилию и инициалы человека.
Для того чтобы объединить ячейки с текстом, и в тоже время ограничить количество символов в имени и отчестве, нам понадобится функция ЛЕВСИМВ . Данная функция возвращает первый символ, или указанное число первых символов тестовой строки.
Для получения желаемого результата введите следующую формулу:
Таким образом мы получим следующую таблицу:
Объединить текст в Excel – Объединенный текст фамилии и инициалов с помощью амперсанда
Обратите внимание, что в данном случае мы в качестве разделителя используем точку и пробел ( «. » ). Перейдем с следующему способу, как объединить текст в ячейках в Excel.
Объединить текст в ячейках с помощью функции СЦЕПИТЬ
В этом примере для объединения текста в ячейках мы будем использовать функцию «СЦЕПИТЬ», которая объединяет две или несколько текстовые строки в одну, позволяя комбинировать их с произвольным текстом. Рассмотрим пример, как в Excel объединить столбцы с текстом.
Добавим к таблице столбец с названием города.
Объединить текст в Excel – Дополнение исходной таблицы
Пусть мы хотим не только объединить ячейки с текстом, но и составить такого вида текст: « Иванов Иван Иванович, проживающий в городе Москва ».
Тогда в ячейке мы вводим в следующую формулу:
=СЦЕПИТЬ(A2;» «;B2;» «; C2;», проживающий в городе «;D2)
Заполняем оставшиеся ячейки с помощью маркера заполнения и в результате получаем такой результат:
Объединить текст в Excel – Объединение текста с помощью формулы СЦЕПИТЬ
Рассмотрим случай, когда нам необходимо объединить текст, дату и числа из разных ячеек в одну.
Объединить текст с датой или числами
В данном случае для того чтобы объединить текст, мы будем использовать функции СЦЕПИТЬ и ТЕКСТ . Функцию СЦЕПИТЬ мы уже изучили, давайте теперь ознакомимся функцией ТЕКСТ .
Данная функция ТЕКСТ позволяет изменить представление числа или времени в удобочитаемом виде. В нашем случае мы будем использовать ее для корректного отображения числа и даты при объединении числовых значений и значений даты с текстовой строкой.
Предположим у нас есть следующая таблица:
Объединить текст в Excel – Исходная таблица с данными
Предположим, что мы хотим не просто объединить текст, а составить предложение, в котором будет говорится, что такой-то сотрудник, такого-то числа реализовал столько-то товаров, и сколько это составило в процентах от общего объема продаж.
Для этого мы должны ввести в ячейку (D2) следующую формулу для первого сотрудника:
=СЦЕПИТЬ(A2; » «; ТЕКСТ($B$1;»ДД.ММ.ГГ»);» реализовал «; B2;» единиц товаров, что составило «;ТЕКСТ(C2;»0%»);» от общего объема продаж»)
Не стоит пугаться. Сейчас мы разберем эту формулу по порядку.
1. А2 – ссылка на ячейку фамилии нашего сотрудника.
2. » « – далее мы вставляем разделитель, в нашем случае это пробел.
3. ТЕКСТ($B$1;»ДД.ММ.ГГ») – при помощи функции ТЕКСТ мы задаем вид даты.
$B$1 – это абсолютная ссылка на дату, ДД.ММ.ГГ – это формат отображаемой даты. В нашем случае исходная дата 13 ноября 2017 г., а мы ее преобразовываем в 13.11.17 (ДД.ММ.ГГ).
4. » реализовал » – продолжаем формировать необходимое нам предложение.
5. B2 – ссылка на ячейку, где хранится информация о количестве реализованной продукции.
6. » единиц товаров, что составило » – добавляем текст.
7. ТЕКСТ(C2;»0%») – ссылка на ячейку C2 указывает на количество процентов, а «0%» – процентный формат.
8. » от общего объема продаж» – завершаем наше предложение.
И заполняем для остальных сотрудников с помощью маркера заполнения.
В результате мы получаем следующее:
Объединить текст в Excel – Объединить текст в ячейке с датой и числами
Ну вот, в итоге мы объединили текст из нескольких ячеек и получили полноценные предложения, которые можно будет вставить, к примеру в отчет.
Объединить текст в ячейках с помощью функции ОБЪЕДИНИТЬ
В версии MS Excel 2016 появилась еще одна функция для объединения текста – функция ОБЪЕДИНИТЬ. Если вы пользователь более ранних версий, то для того чтобы объединить текст, используйте вышеописанные способы.
Обратите внимание , что функция Объединить доступна только при наличии подписки на Office 365.
С помощью функции ОБЪЕДИНИТЬ можно объединить текст из нескольких ячеек, строк или диапазонов.
Сперва разберем синтаксис функции ОБЪЕДИНИТЬ:
=ОБЪЕДИНИТЬ(разделитель; игнорировать_пустые; текст1; [текст2]; . )
- разделитель – текстовая строка, которой будет разделятся объединенный текст;
- игнорировать_пустые – принимает значение ИСТИНА (1) или ЛОЖЬ (0). Если необходимо пропустить пустые ячейки, то устанавливаем значение ИСТИНА;
- текст1 – текстовый элемент, который необходимо объединить;
- [текст2]; . – дополнительные текстовые элементы для объединения.
Рассмотрим пример, как объединить текст в Excel используя функцию ОБЪЕДИНИТЬ.
Объединить текст в Excel – Объединить текст с помощью функции ОБЪЕДИНИТЬ
На этом мы закончим статью, как в Excel объединить текст. Мы рассмотрели несколько вариантов, как объединить текст в ячейках в Excel с помощью амперсанда ( & ), функции СЦЕПИТЬ , функции ОБЪЕДИНИТЬ, а также как объединить текст с датой или числами с соответствующими форматами.