Функция СЦЕПИТЬ в Excel. Как использовать? – ЭКСЕЛЬ ХАК

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Объединение текстовой строки и вычисляемой формулы

Чтобы сделать результат, возвращаемый каким-то расчётом, более понятным для ваших пользователей, вы можете связать его с текстовым пояснением. Оно объяснит, как следует оценивать получившееся.

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

=СЦЕПИТЬ(“Сегодня “;ТЕКСТ(СЕГОДНЯ(); “dd-mmm-yyyy”))

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

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

=СЦЕПИТЬ(A1, A2, A3) 

вместо   

=СЦЕПИТЬ(A1:A3)

  • Если хотя бы один из аргументов функции СЦЕПИТЬ недопустим, выражение возвращает ошибку #ЗНАЧ!

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

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

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

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

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

Аргументы функции СЦЕП (СЦЕПИТЬ) в Эксель

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

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

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

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

Ячейка с разделителем для функции СЦЕПИТЬ в Эксель

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

Заполнение аргументов функции СЦЕП (СЦЕПИТЬ) с разделителем в Эксель

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

Заполнение аргументов функции СЦЕП (СЦЕПИТЬ) с зафиксированным разделителем в Эксель

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

Результат по функции СЦЕП (СЦЕПИТЬ) с разделителем в Excel

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

Корректировка разделителя в функции СЦЕП (СЦЕПИТЬ) в Excel

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

Аргументы функции СЦЕП (СЦЕПИТЬ) с разными разделителями в Эксель

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

Результат по функции СЦЕП (СЦЕПИТЬ) с разными разделителями в Эксель

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

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

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

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

Необходимые действия

Описание

Вместо функции СЦЕПИТЬ используйте знак & (амперсанд).

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

Например, =A1 & B1 возвращает то же значение, что и =С CONCATENATE(A1;B1). Во многих случаях воспользоваться оператором амперсанда проще и быстрее, чем создавать строки с помощью функции СЦЕПИТЬ.

См. дополнительные сведения об использовании операторов вычислений.

Для объединения и форматирования строк используйте функцию ТЕКСТ.

Функция ТЕКСТ преобразует числовое значение в текст и объединяет числа с текстом или символами.

Предположим, что ячейка A1 содержит число 23,5. Чтобы отформатировать число как сумму в рублях, можно использовать следующую формулу:

=ТЕКСТ(A1;”0,00 ₽”)

Результат: 23,5 ₽

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

  • Для объединения и форматирования строк используйте функцию ТЕКСТ.

  • Ознакомьтесь с дополнительными сведениями об использовании операторов вычислений.

Нужна дополнительная помощь?

Макрос для объединения ячеек без потери текста.

Имеем текст в нескольких ячейках и желание – объединить эти ячейки в одну, слив туда же их текст. Проблема в одном – кнопка Объединить и поместить в центре (Merge and Center) в Excel объединять-то ячейки умеет, а вот с текстом сложность – в живых остается только текст из верхней левой ячейки.

Чтобы объединение ячеек происходило с объединением текста (как в таблицах Word) придется использовать макрос. Для этого откройте редактор Visual Basic на вкладке Разработчик – Visual Basic (Developer – Visual Basic) или сочетанием клавиш Alt+F11, вставим в нашу книгу новый программный модуль (меню Insert – Module) и скопируем туда текст такого простого макроса:

Sub MergeToOneCell() Const sDELIM As String = ” ” ‘символ-разделитель Dim rCell As Range Dim sMergeStr As String If TypeName(Selection) <> “Range” Then Exit Sub ‘если выделены не ячейки – выходим With Selection For Each rCell In .Cells sMergeStr = sMergeStr & sDELIM & rCell.Text ‘собираем текст из ячеек Next rCell Application.DisplayAlerts = False ‘отключаем стандартное предупреждение о потере текста .Merge Across:=False ‘объединяем ячейки Application.DisplayAlerts = True .Item(1).Value = Mid(sMergeStr, 1 + Len(sDELIM)) ‘добавляем к объед.ячейке суммарный текст End With End Sub

Теперь, если выделить несколько ячеек и запустить этот макрос с помощью сочетания клавиш Alt+F8 или кнопкой Макросы на вкладке Разработчик (Developer – Macros), то Excel объединит выделенные ячейки в одну, слив туда же и текст через пробелы.

Процесс объединения

Смотрите также: “Поиск решения в Excel: пример использования функции”

В целом, существующие инструменты Эксель для объединения столбцов условно делятся на две основные группы:

  • инструменты форматирования
  • специальные функции

Форматирование – достаточно простой и наглядный способ объединения столбцов, однако, использование функций значительно упрощает и ускоряет процесс в случае со сложными или большими таблицами. Рассмотрим на практических примерах инструменты из обеих групп.

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...