Сцепить в excel диапазон ячеек

Функция СЦЕПИТЬ (CONCATENATE)

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

Здесь между ячейками вставлены пробелы (» «) и в конце дорисовывается восклицательный знак.

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

Так дело идет немного быстрее. Хотя и не очень эффективно – потребуется занять отдельную ячейку. А если нечаянно эту ячейку удалить, то получится вообще нехорошо. В целом при небольшом количестве данных, работают оба варианта.

Кстати, не все знают, что в Excel для склеивания ячеек также предусмотрен специальный оператор – & (амперсанд). Его нужно просто поставить между связываемыми ячейками или символами.

Вставлять амперсанд не удобно – нужно вначале переключиться на английскую раскладку, а затем нажать Shift + 7. Поэтому рекомендуется воспользоваться специальной комбинацией – удерживая клавишу Alt нажать 3 и 8 на цифровой клавиатуре. Здорово помогает сохранить душевное равновесие.

Формула СЦЕПИТЬ и амперсанд исправно работают до тех пор, пока не приходится соединять большое количество ячеек. Замучаешься вставлять «;» либо «&». Более того, между ячейками, как привило, добавляется пробел. К сожалению, указать в качестве аргумента целый диапазон нельзя (уже можно). Выход из этой ситуации следующий.

Функция СЦЕПИТЬ для большого количества ячеек

Один из вариантов заключатся в том, чтобы в качестве ссылки на ячейки указать массив данных. Следует сразу отметить, что данные могут располагаться по горизонтали или вертикали. Если данные расположены в одной строке, то делаем следующее. Для примера, отдельные по ячейкам слова находится в 5-й строке. Теперь в пустой ячейке указываем весь диапазон для соединения и через амперсанд (&) добавляем пробел (» «).

Затем нажимаем F9, для того, чтобы формула выдала результат вычисления, в нашем случае это будет массив.

Как видно к каждому слову добавился пробел, а между словами стоит точка с запятой – как раз то, что нужно для вставки в формулу СЦЕПИТЬ. Теперь убираем лишние скобки и вставляем этот массив в формулу. Нажимаем Enter.

Данные могут быть расположены не по горизонтали, (как в примере выше), а по вертикали. В этом случае в получаемом массиве значения будут разделены двоеточием. Придется сделать один лишний шаг, чтобы заменить их на точку с запятой (через Найти и заменить, горячая клавиша Ctrl + H, либо еще быстрее — использовать функцию ТРАНСП, чтобы из горизонтального массива сделать вертикальный).

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

Поэтому переходим к следующему способу. Он, признаться, выглядит не очень эстетично. Зато дешево, надежно, и практично! (с) Во всяком случае прием помогает решить проблему. А это самое главное. Нам потребуется один дополнительный столбец (или строка, смотря как расположены исходные данные для соединения).

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

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

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

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

Как видно, ничего не получилось.

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

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

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

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

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

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



Источник: statanaliz.info

Функция СЦЕПИТЬ в Excel: синтаксис и примеры использования

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

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

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

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

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



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

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

Данные для объединения:

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

Результат объединения значений в ячейках:

Такой же результат получим с помощью амперсанда:

Один из аргументов – текст. Формула: =СЦЕПИТЬ(“слесарь”; ” “;A2;” “;B2;” “;C2). Результат:

Читайте также:  Excel как объединить текст в ячейках в excel

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

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

Преобразуем вертикальный диапазон в горизонтальный с помощью функции ТРАНСП. А затем воспользуемся формулой массива для быстрого соединения значений. В ячейку В1 ставим знак «равно». Пишем ТРАНСП. Указываем диапазон А1:А6. Вводим & и пробел (« »). Жмем F9.

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

Обратная функция СЦЕПИТЬ в Excel

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

  • ЛЕВСИМВ (отображает заданную часть символов с начала строки);
  • ПРАВСИМВ (отображает заданную часть символов с конца строки);
  • ПСТР (отображает указанную часть символов, начиная с заданной позиции) и другие.

  1. Извлечем первое слово из строки, применив функцию ЛЕВСИМВ. Аргументы – «текст» (ссылка на строку с искомым значением), «количество знаков» (число символов, которые нужно извлечь).
  2. Извлечем последнее слово из строки с помощью функции ПРАВСИМВ. Синтаксис похожий (как в предыдущем примере). Только количество знаков считается с конца.
  3. Извлечем из записи в строке фамилию (второе слово) с помощью функции ПСТР. Аргументы – «текст» (ссылка на строку), «начальная позиция» (номер символа, с которого программа начнет считать число знаков); «количество знаков». В примере: Е2 – «текст», с 9 знака функция вернет 8 символов.

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

Источник: exceltable.com

Сцепить много ячеек с указанным разделителем

Часто бывает ситуация, когда необходимо из трех разных столбцов сцепить данные в одну строку с разделителем. Допустим в А1 Фамилия, в В1 – Имя, в С1 – Отчество, а надо получить все вместе Фамилия Имя Отчество. Как обычно в Excel объединяют значения нескольких ячеек в одну? Правильно, при помощи функции СЦЕПИТЬ или при помощи амперсанда:
=СЦЕПИТЬ( A1 ;” “; B1 ;” “; C1 ;” “)
= A1 &” “& B1 &” “& C1 &” ”
Это достаточно эффективно, если необходимо сцепить значения из трех-пяти ячеек. А если ячеек 50? Или того больше? Не очень удобно объединять их все описанными выше способами. А других встроенных функций в Excel для подобных операций не существует.
Поэтому я написал функцию пользователя, которая сцепляет данные из указанных ячеек в одну строку. Чем отличается от стандартной функции СЦЕПИТЬ()? Тем, что в качестве ячеек для сцепки указывается не каждая из ячеек по очереди, а сразу весь диапазон с возможностью указания разделителя между значениями каждой ячейки.

Option Explicit ‘————————————————————————————— ‘ Procedure : СцепитьМного ‘ http://www.excel-vba.ru ‘ Purpose : Функция сцепляет все указанные ячейки в одну с указанным разделителем. ‘ Аргументы функции: ‘ Диапазон — диапазон ячеек, значения которых необходимо объединить в строку. ‘ Разделитель — необязательный аргумент. ‘ Один или несколько символов, которые будут вставлены между каждым словом. ‘ По умолчанию пробел. ‘ БезПовторов — необязательный аргумент. ‘ Если указан как ИСТИНА или 1 — в результирующей строке будут значения без дубликатов. ‘ Для английской локализации данный параметр указывается как TRUE и FALSE соответственно. ‘————————————————————————————— Function СцепитьМного(Диапазон As Range, Optional Разделитель As String = ” “, Optional БезПовторов As Boolean = False) Dim avData, lr As Long, lc As Long, sRes As String avData = Диапазон.Value If Not IsArray(avData) Then СцепитьМного = avData Exit Function End If For lc = 1 To UBound(avData, 2) For lr = 1 To UBound(avData, 1) If Len(avData(lr, lc)) Then sRes = sRes & Разделитель & avData(lr, lc) End If Next lr Next lc If Len(sRes) Then sRes = Mid(sRes, Len(Разделитель) + 1) End If If БезПовторов Then Dim oDict As Object, sTmpStr Set oDict = CreateObject(“Scripting.Dictionary”) sTmpStr = Split(sRes, Разделитель) On Error Resume Next For lr = LBound(sTmpStr) To UBound(sTmpStr) oDict.Add sTmpStr(lr), sTmpStr(lr) Next lr sRes = “” sTmpStr = oDict.keys For lr = LBound(sTmpStr) To UBound(sTmpStr) sRes = sRes & IIf(sRes <> “”, Разделитель, “”) & sTmpStr(lr) Next lr End If СцепитьМного = sRes End Function

Чтобы правильно использовать приведенный код, необходимо сначала ознакомиться со статьей Что такое функция пользователя(UDF)?. Вкратце: скопировать текст кода выше, перейти в редактор VBA( Alt + F11 ) -создать стандартный модуль(Insert –Module) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций( Shift + F3 ), отыскав её в категории Определенные пользователем (User Defined Functions) .

Синтаксис функции:
=СцепитьМного(A2:A100;”, “;ИСТИНА)

Диапазон – диапазон ячеек, значения которых необходимо объединить в строку.
Разделитель – необязательный аргумент. Один или несколько символов, которые будут вставлены между каждым словом. По умолчанию пробел.
БезПовторов – необязательный аргумент. Если указан как ИСТИНА или 1 – в результирующей строке будут значения без дубликатов. Например, из значений Сидоров, Петров, Сидоров, Иванов в результат попадут только Сидоров, Петров, Иванов. Если ЛОЖЬ или 0 – будут выведены все значения. Для английской локализации данный параметр указывается как TRUE и FALSE соответственно.

Tips_Macro_CoupleCells.xls (54,0 KiB, 9 244 скачиваний)

Если необходимо объединять значения ячеек из “рваных”(несмежных) диапазонов(выделенных через Ctrl), то код нужно немного изменить:

Option Explicit ‘————————————————————————————— ‘ Procedure : СцепитьМного ‘ http://www.excel-vba.ru ‘ Purpose : Функция сцепляет все указанные ячейки в одну с указанным разделителем. Допускается указание несмежных диапазонов ‘ Аргументы функции: ‘ Диапазон — диапазон ячеек, значения которых необходимо объединить в строку. ‘ Разделитель — необязательный аргумент. ‘ Один или несколько символов, которые будут вставлены между каждым словом. ‘ По умолчанию пробел. ‘ БезПовторов — необязательный аргумент. ‘ Если указан как ИСТИНА или 1 — в результирующей строке будут значения без дубликатов. ‘ Для английской локализации данный параметр указывается как TRUE и FALSE соответственно. ‘————————————————————————————— Function СцепитьМного(диапазон As Range, Optional разделитель As String = ” “, Optional БезПовторов As Boolean = False) Dim avData, lr As Long, lc As Long, sRes As String Dim ra As Range For Each ra In диапазон.Areas avData = ra.Value If Not IsArray(avData) Then ReDim avData(1 To 1, 1 To 1) avData(1, 1) = ra.Value End If For lc = 1 To UBound(avData, 2) For lr = 1 To UBound(avData, 1) If Len(avData(lr, lc)) Then sRes = sRes & разделитель & avData(lr, lc) End If Next lr Next lc Next If Len(sRes) Then sRes = Mid(sRes, Len(разделитель) + 1) End If If БезПовторов Then Dim oDict As Object, sTmpStr Set oDict = CreateObject(“Scripting.Dictionary”) sTmpStr = Split(sRes, разделитель) On Error Resume Next For lr = LBound(sTmpStr) To UBound(sTmpStr) oDict.Add sTmpStr(lr), sTmpStr(lr) Next lr sRes = “” sTmpStr = oDict.Keys For lr = LBound(sTmpStr) To UBound(sTmpStr) sRes = sRes & IIf(sRes <> “”, разделитель, “”) & sTmpStr(lr) Next lr End If СцепитьМного = sRes End Function

Читайте также:  Сравнение значений ячеек в excel больше меньше

Однако в таком случае слегка изменится и синтаксис – такие диапазоны обязательно надо будет записывать в скобках:
Синтаксис функции:
=СцепитьМного(( A2:A100 ; F4:F60 ; Y2:Z43 );”, “;ИСТИНА)
Иначе функция просто не сработает и выдаст ошибку #ЗНАЧ! (#VALUE!)

И еще одна реализация – в ней допускается указывать не только отдельные диапазоны, но и вообще все что угодно(ячейки, отдельный текст, числа и т.п.). Единственная проблема – в этой функции иначе организован порядок аргументов: сначала указывается разделитель, а уже потом значения для сцепления. Более подробно эта функция рассмотрена в статье Что такое функция пользователя(UDF)?. Так же эта функция не убирает дубли, что впрочем, не так сложно добавить, ориентируясь на функции выше.

Function ОбъединитьВсеСРазделителем(Разделитель As String, ParamArray Значения()) As String Dim result As String, arg, x, rc As Range For Each arg In Значения Select Case TypeName(arg) Case “Range” ‘это диапазон ‘цикл по всем ячейкам For Each rc In arg.Cells If result = “” Then result = rc.Value Else result = result & Разделитель & rc.Value End If Next Case “Variant()” ‘это произвольный массив(<"а";"б";"в">) ‘цикл по всем ячейкам For Each x In arg If result = “” Then result = x Else result = result & Разделитель & x End If Next Case Else ‘это любой другой тип ‘суммируем If result = “” Then result = arg Else result = result & Разделитель & arg End If End Select Next ОбъединитьВсеСРазделителем = result End Function

Статья помогла? Поделись ссылкой с друзьями!

Источник: www.excel-vba.ru

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

Здравствуй дорогой, читатель!

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

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

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

На данный момент существует 3 способа сцепить ячейки в Excel:

Но рассмотрим мы только первые 2 варианта, ну а о третем поговорим уж в другой раз. Итак:

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

С помощью функции СЦЕПИТЬ, как я выше уже писал, можно объединить до 255 ячеек и до 8 192 символов. Что позволяет производить громадное количество комбинаций для наших нужд. Есть также небольшой нюанс в использовании, не стоить забывать о пробелах, программа, увы, сама их не ставит и что бы не получить слитый текст в одно слово, между значениями нужно указать пробел как текстовое значение (пример: “ “). В таком случае функция СЦЕПИТЬ в Excel сработает на 100% и вы получите нужный вам результат.

2. Сцепить диапазон ячеек в Excel при помощи оператора & (амперсанд) вместо функции СЦЕПИТЬ

Амперсанд — это своеобразный знак “+” для текстовых значений, которые нам нужно соединить. Найти амперсанд можно на клавиатуре, возле циферки “7”, ну по крайней мере, на большинстве клавиатур там он и находится. А если его нет, значит, внимательно посмотрите куда его перенесли. А так данный вариант похож, как и функция СЦЕПИТЬ, за исключением специфики орфографии и об этом не стоит забывать, так, к примеру, функция СЦЕПИТЬ сама ставит кавычки, а вот при использовании амперсанда вы прописываете их вручную. Но вот в возможности склеить значения в ячейках Excel по скорости, использование 2 варианта самое оптимальное.

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

Пример №1:

Нам надо сцепить текстовые значения, а именно ФИО сотрудников выгруженное с другой программы, но разбросанное в разных ячейках. Задача на первый взгляд легка, так оно, конечно, и есть, за исключением того, что нам надо фамилия и инициалы сотрудников, то есть сократить имя и по отчеству. И это можно сделать если использовать сочетание функций, а именно функция, которая, позволяет извлекать из текста первые буквы — функция ЛЕВСИМВ, в таком случае мы получим фамилию с инициалами в одной формуле.

Пример №2:

Нам надо сцепить разрозненную информацию о договорах его номер и дату заключения. К примеру, у нас есть “Договор на транспортные перевозки” “№23” “02.09.2015” и нам надо получить все данные одним предложением “Договор на транспортные перевозки №23 от 02.09.2015 года”. Но при использовании возможности сцепить диапазон ячеек с помощью функции СЦЕПИТЬ, мы не сможем получить нужный нам результат так будет произведена склейка текстовых значений, а нас есть дата. Соответственно, данные будут исковерканы. Для получения результата необходимо использовать дополнительно функцию ТЕКСТ. Она позволит, назначить для даты соответствующий формат «ДД.ММ.ГГГГ», соответственно формату мы получим данные двузначные для дней “ДД” и месяца “ММ” и четырёхзначное для года “ГГГГ”. Таким образом, мы сможем получить правильный конечный вариант.

Файл с примерами можно забрать сдесь.

А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!

Экономия — способ тратить деньги без удовольствия.
Арман Салакру

Источник: topexcel.ru

Excel works!

Excel работает за вас

Excel works!

Thanks for Visiting

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

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

Читайте также:  Как разделить ячейку в экселе

Вопрос очень популярен, может быть поэтому в Excel есть 2 равнозначных метода сцепления/скрепления данных в одну строку.

Функция СЦЕПИТЬ в Excel. Как объединить Фамилию, Имя и Отчество в одну строку

Итак, нам дан список сотрудников, нужно объединить каждое ФИО в отдельную ячейку.


В столбце D запишем формулу

Где каждый аргумент между точкой и запятой — это текст, который скрепляется. Поскольку нам нужно, чтобы ФИО выглядели красиво между словами мы вставляем пробелы, которые обозначаются как » «.

Кстати, чтобы вставить любые текстовые данные, заключите их в кавычки!

В итоге получается:

Альтернатива функции СЦЕПИТЬ. Объединять текст в Excel при помощи &

Того же результата можно добиться, если объединять текст в Excel при помощи специального символа — амперсанд, он же &.

Запишем в столбец D такие формулы:

По мне так этот способ даже удобнее.

Сцепить текст и число

Если к объединенному ФИО нужно добавить например число, то это можно сделать аналогично тексту, добавьте столбец с числом

Но здесь есть 1 нюанс: в Эксель вы зачастую округляете число форматом числа. Т.е. само число остается в неизменном виде, например, с четырьмя знаками после запятой.

Добавляем в формулу нужное округление

Теперь все красиво!

Сцепить текст и дату

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

Добавим еще запятые для красоты:

Разделить текст по столбцам

Если у вас даны уже скрепленные ФИО, и надо наоборот разделить текст по столбцам, то у нас есть отдельная статья специально для вас.

Пример как сцепить в Excel по всем формулам прикладываю

Как сцепить диапазон ячеек? Функция СЦЕП()

В Excel 2016 появилась возможность сцепить несколько ячеек, выделив диапазон, а не каждую ячейку по отдельности. Это функция СЦЕП — у нее только один аргумент, просто выберите диапазон ячеек, которые надо объединить.

Поиск повторений в больших таблицах данных при помощи объединения текста

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

В отдельном столбце запишем формулу

Как видите мы не используем никакие доп. символы, ни запятые, ни пробелы. А функции СЖПРОБЕЛЫ удаляют все пробелы в начале и конце значения каждой ячейки. Так мы исключим человеческий фактор при наборе данных — люди часто случайно ставят пробел в конце слова.

Теперь подсветим все дубликаты при помощи условного форматирования .

Красным выделились все повторяющиеся строки:

Источник: excelworks.ru

Функция СЦЕПИТЬ в Excel: синтаксис и примеры использования

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

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

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

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

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



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

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

Данные для объединения:

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

Результат объединения значений в ячейках:

Такой же результат получим с помощью амперсанда:

Один из аргументов – текст. Формула: =СЦЕПИТЬ(“слесарь”; ” “;A2;” “;B2;” “;C2). Результат:

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

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

Преобразуем вертикальный диапазон в горизонтальный с помощью функции ТРАНСП. А затем воспользуемся формулой массива для быстрого соединения значений. В ячейку В1 ставим знак «равно». Пишем ТРАНСП. Указываем диапазон А1:А6. Вводим & и пробел (« »). Жмем F9.

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

Обратная функция СЦЕПИТЬ в Excel

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

  • ЛЕВСИМВ (отображает заданную часть символов с начала строки);
  • ПРАВСИМВ (отображает заданную часть символов с конца строки);
  • ПСТР (отображает указанную часть символов, начиная с заданной позиции) и другие.

  1. Извлечем первое слово из строки, применив функцию ЛЕВСИМВ. Аргументы – «текст» (ссылка на строку с искомым значением), «количество знаков» (число символов, которые нужно извлечь).
  2. Извлечем последнее слово из строки с помощью функции ПРАВСИМВ. Синтаксис похожий (как в предыдущем примере). Только количество знаков считается с конца.
  3. Извлечем из записи в строке фамилию (второе слово) с помощью функции ПСТР. Аргументы – «текст» (ссылка на строку), «начальная позиция» (номер символа, с которого программа начнет считать число знаков); «количество знаков». В примере: Е2 – «текст», с 9 знака функция вернет 8 символов.

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

Источник: exceltable.com