Функция СЦЕПИТЬ (CONCATENATE). Excel сцепить функция

Функция Excel СЦЕПИТЬ | Статанализ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вот, как это выглядит.

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

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

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

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

На этом пока все.

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

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

А вот еще один трюк с объединением ячеек.

statanaliz.info

Функция excel СЦЕПИТЬ Видео

3 г. назад

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

2 г. назад

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

3 г. назад

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

3 г. назад

Как сцепить ячейки в эксель таблице.

3 г. назад

Этот видеоурок расскажет как правильно и быстро сцепить ячейки в Excel. Формула сцепить в Excel описана в статье...

4 г. назад

Многим знакома ситуация: в одной ячейке Excel хранится имя сотрудника, во второй - отчество, а в третьей - его...

3 г. назад

Текстовая версия: http://4upc.ru/materials/show/kak-obedinit-tekst-v-yachejkah-v-excel-funkciya-scepit В этом уроке расскажу как объединить текст...

1 г. назад

Операция объединения текстовых строк в единую строка в Excel обычно называется конкатенацией. Для ее осущест...

1 г. назад

Сегодня мы покажем как объединить данные в Excel. Итак у нас есть 3 ячейки, разного формата , числовой, текстовы...

3 г. назад

как сцепить в эксель столбцы.

5 г. назад

Как собрать данные из нескольких таблиц (одинаковых или не очень) в один итоговый отчет в Excel. Подробно и...

2 г. назад

Как в одной ячейке собрать строку со значениями из других ячеек? Функция "Сцепить" - описание, пример использ...

7 г. назад

Урок 8. Функциия СЦЕПИТЬ Смотрите больше на ...

2 г. назад

Как сцепить/склеить/соединить столбцы с данными в таблицах в Excel Эксель.

2 г. назад

Серия коротких уроков для быстрого и полноценного освоения Excel для новичков и профессионалов.

4 мес. назад

Из этого урока вы узнаете, как в программе эксель объединить текст нескольких ячеек в одну. Для решения...

5 г. назад

На примере создания рекламной кампании Яндекс.Директ в Excel, показываю способы объединения данных в Excel....

2 г. назад

Функция СЦЕПИТЬ в excel https://maxfad.ru/ofis/ms-excel/157-funktsiya-stsepit-v-excel.html Поддержите канал лайком и репостом, ставьте клас...

2 г. назад

Как реализовать в Excel функцию СЦЕПИТЬ. Для тех , кто хочет получить более глубокие знания по Excel:: http://royaltylex.ru...

www.meizer.kz

функция сцепить в excel Видео

3 г. назад

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

2 г. назад

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

3 г. назад

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

3 г. назад

Этот видеоурок расскажет как правильно и быстро сцепить ячейки в Excel. Формула сцепить в Excel описана в статье...

4 г. назад

Многим знакома ситуация: в одной ячейке Excel хранится имя сотрудника, во второй - отчество, а в третьей - его...

3 г. назад

как сцепить в эксель столбцы.

2 г. назад

Как реализовать в Excel функцию СЦЕПИТЬ. Для тех , кто хочет получить более глубокие знания по Excel:: http://royaltylex.ru...

5 г. назад

Как собрать данные из нескольких таблиц (одинаковых или не очень) в один итоговый отчет в Excel. Подробно и...

4 мес. назад

Из этого урока вы узнаете, как в программе эксель объединить текст нескольких ячеек в одну. Для решения...

2 г. назад

Как сцепить/склеить/соединить столбцы с данными в таблицах в Excel Эксель.

3 г. назад

Как сцепить ячейки в эксель таблице.

2 г. назад

Текстовые функции Excel ПОДСТАВИТЬ И ЗАМЕНИТЬ используют для поиска и заменты части текстовой строки на друг...

2 г. назад

Функция СЦЕПИТЬ в excel https://maxfad.ru/ofis/ms-excel/157-funktsiya-stsepit-v-excel.html Поддержите канал лайком и репостом, ставьте клас...

2 г. назад

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

1 г. назад

Подстановка данных по ключевому полю (критерию) и объединение результата в одну ячейку - множественный...

3 г. назад

Текстовая версия: http://4upc.ru/materials/show/kak-obedinit-tekst-v-yachejkah-v-excel-funkciya-scepit В этом уроке расскажу как объединить текст...

5 г. назад

На примере создания рекламной кампании Яндекс.Директ в Excel, показываю способы объединения данных в Excel....

1 г. назад

Операция объединения текстовых строк в единую строка в Excel обычно называется конкатенацией. Для ее осущест...

9 мес. назад

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

4 г. назад

Несколько примеров использования функций ЕСЛИ, И, ИЛИ и ВПР для проверки условий в Excel. Заходите в гости...

www.meizer.kz

Как сцепить несколько значений в одну ячейку по критерию? СцепитьЕсли

Хитрости » 1 Май 2011 Дмитрий 96995 просмотров

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

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

'--------------------------------------------------------------------------------------- ' Author : The_Prist(Щербаков Дмитрий) ' Профессиональная разработка приложений для MS Office любой сложности ' Проведение тренингов по MS Excel ' http://www.excel-vba.ru ' Purpose: '--------------------------------------------------------------------------------------- Function СцепитьЕсли(ByRef Диапазон As Range, ByVal Критерий As String, ByRef Диапазон_сцепления As Range, Optional Разделитель As String = " ", Optional БезПовторов As Boolean = False) As String Dim li As Long, sStr As String, avItem, avDateArr(), avRezArr(), lUBnd As Long If Диапазон.Count > 1 Then avDateArr = Intersect(Диапазон, Диапазон.Parent.UsedRange).Value avRezArr = Intersect(Диапазон_сцепления, Диапазон_сцепления.Parent.UsedRange).Value If Диапазон.Rows.Count = 1 Then avDateArr = Application.Transpose(avDateArr) avRezArr = Application.Transpose(avRezArr) End If Else ReDim avDateArr(1, 1): ReDim avRezArr(1, 1) avDateArr(1, 1) = Диапазон.Value avRezArr(1, 1) = Диапазон_сцепления.Value End If lUBnd = UBound(avDateArr, 1) 'Определяем вхождение операторов сравнения в Критерий Dim objRegExp As Object, objMatches As Object Set objRegExp = CreateObject("VBScript.RegExp") objRegExp.Global = False: objRegExp.Pattern = "=|<>|=>|>=|<=|=<|>|<" Set objMatches = objRegExp.Execute(Критерий) 'Если есть вхождения If objMatches.Count > 0 Then Dim sStrMatch As String sStrMatch = objMatches.Item(0) Критерий = Replace(Replace(Критерий, sStrMatch, "", 1, 1), Chr(34), "", 1, 2) If IsNumeric(Критерий) And Критерий <> "" Then Критерий = CDbl(Критерий) End If Select Case sStrMatch Case "=" For li = 1 To lUBnd If avDateArr(li, 1) = Критерий Then If Trim(avRezArr(li, 1)) <> "" Then _ sStr = sStr & IIf(sStr <> "", Разделитель, "") & avRezArr(li, 1) End If Next li Case "<>" For li = 1 To lUBnd If avDateArr(li, 1) <> Критерий Then If Trim(avRezArr(li, 1)) <> "" Then _ sStr = sStr & IIf(sStr <> "", Разделитель, "") & avRezArr(li, 1) End If Next li Case ">=", "=>" For li = 1 To lUBnd If avDateArr(li, 1) >= Критерий Then If Trim(avRezArr(li, 1)) <> "" Then _ sStr = sStr & IIf(sStr <> "", Разделитель, "") & avRezArr(li, 1) End If Next li Case "<=", "=<" For li = 1 To lUBnd If avDateArr(li, 1) <= Критерий Then If Trim(avRezArr(li, 1)) <> "" Then _ sStr = sStr & IIf(sStr <> "", Разделитель, "") & avRezArr(li, 1) End If Next li Case ">" For li = 1 To lUBnd If avDateArr(li, 1) > Критерий Then If Trim(avRezArr(li, 1)) <> "" Then _ sStr = sStr & IIf(sStr <> "", Разделитель, "") & avRezArr(li, 1) End If Next li Case "<" For li = 1 To lUBnd If avDateArr(li, 1) < Критерий Then If Trim(avRezArr(li, 1)) <> "" Then _ sStr = sStr & IIf(sStr <> "", Разделитель, "") & avRezArr(li, 1) End If Next li End Select Else 'Если нет вхождения For li = 1 To lUBnd If avDateArr(li, 1) Like Критерий Then If Trim(avRezArr(li, 1)) <> "" Then _ sStr = sStr & IIf(sStr <> "", Разделитель, "") & avRezArr(li, 1) End If Next li End If If БезПовторов Then Dim oDict As Object, sTmpStr Set oDict = CreateObject("Scripting.Dictionary") sTmpStr = Split(sStr, Разделитель) On Error Resume Next For li = LBound(sTmpStr) To UBound(sTmpStr) oDict.Add sTmpStr(li), sTmpStr(li) Next li sStr = "" sTmpStr = oDict.keys For li = LBound(sTmpStr) To UBound(sTmpStr) sStr = sStr & IIf(sStr <> "", Разделитель, "") & sTmpStr(li) Next li End If СцепитьЕсли = sStr End Function

Для применения функции в своем файле достаточно создать стандартный модуль (о том как создать стандартный модуль: Что такое модуль? Какие бывают модули?) и просто вставить приведенный код. После этого в диспетчере функций появиться новая категория (если до этого её не было) - Определенные пользователем (User Defined). В ней эта функция - СцепитьЕсли.Синтаксис:=СцепитьЕсли(B5:B18;B5;C5:C18;"-";0)

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

Диапазон(B5:B18) - диапазон, в котором искать критерий(указывается один столбец)

Критерий(B5) - критерий. Значение, на основании которого необходимо сцеплять значения. Может содержать символы подстановки - * и ? и символы сравнения ("", 0, ""&A1 и т.п.). Просматривается Диапазон. При совпадении значения ячейки в Диапазоне значение из Диапазона_Сцепления добавляется к результату с выбранным разделителем.

Диапазон_сцепления(C5:C18) - из этого диапазона берется значение для сцепления, если значение в аргументе Диапазон совпадает с аргументом Критерий(указывается один столбец). Если в Диапазоне значение 5-ой строки совпадает с критерием, то из Диапазона_Сцепления будет взято так же значение из 5-ой строк этого диапазона и сцеплено с результатом.

Разделитель("-") - По умолчанию пробел, но можно задать любой другой символ или группу символов.

БезПовторов - если указать 1 или ИСТИНА, то в результате получится строка, в которой нет одинаковых значений. Если указать 0 или ЛОЖЬ, то будут выведены все значения. По умолчанию значение ЛОЖЬ.

Примечание: для работы функции должны быть разрешены макросы

Скачать пример

Пример СцепитьЕсли.xls (68,0 KiB, 8 393 скачиваний)

Также см.:ВПР_МНСцепить_МНСцепитьЕслиЧто такое функция пользователя(UDF)?