Почему не работает функция сцепить в excel

СЦЕПИТЬ диапазон Excel (с разделителем и без него)

В Excel есть два способа объединить содержимое нескольких ячеек:

• Функция СЦЕПИТЬ в Excel (или оператор амперсанда (&))
• Функция Excel TEXTJOIN (новая функция в Excel, если у вас есть Office 365)

В своей базовой форме функция СЦЕПИТЬ может объединять 2 или более символов строк.

• = CONCATENATE («Хорошо», «Утро») даст вам результат GoodMorning.
• = СЦЕПИТЬ («Хорошо», «,« Доброе утро ») даст вам результат« Доброе утро ».
• = CONCATENATE (A1 & A2) даст вам результат GoodMorning (где A1 содержит текст «Good», а A2 — текст «Morning».

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

Например, в приведенном выше примере, хотя используется формула = СЦЕПИТЬ (A1: A5), результат показывает только «Сегодня» и не объединяет все ячейки.

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

Примечание. Если вы используете Excel 2016, вы можете использовать функцию TEXTJOIN, которая создана для объединения нескольких ячеек с помощью разделителя.

СЦЕПИТЬ диапазон Excel (без разделителя)

Вот шаги для объединения диапазона Excel без разделителя (как показано на рисунке):

1. Выделите ячейку, в которой вам нужен результат.
2. Перейдите в строку формул и введите = TRANSPOSE (A1: A5).
   • В зависимости от ваших региональных настроек вы также можете попробовать = A1: A5 (вместо = TRANSPOSE (A1: A5))
3. Выделите всю формулу и нажмите F9 (это преобразует формулу в значения).
4. Снимите фигурные скобки с обоих концов.
5. Добавьте = СЦЕПИТЬ (в начало текста и закройте его круглой скобкой).
6. Нажмите Ввод.

Это приведет к объединению диапазона ячеек в одну ячейку (как показано на изображении выше). Обратите внимание: поскольку мы используем любой разделитель (например, запятую или пробел), все слова объединяются без разделителя.

СЦЕПИТЬ диапазоны Excel (с разделителем)

Вот шаги, чтобы объединить диапазон Excel с пробелом в качестве разделителя (как показано на рисунке):

1. Выделите ячейку, в которой вам нужен результат.
2. Перейдите в строку формул и введите = TRANSPOSE (A1: A5) & ”“
   • В зависимости от ваших региональных настроек вы также можете попробовать = A1: A5 (вместо = TRANSPOSE (A1: A5)).
3. Выделите всю формулу и нажмите F9 (это преобразует формулу в значения).
4. Снимите фигурные скобки с обоих концов.
5. Добавьте = СЦЕПИТЬ (в начало текста и закройте его круглой скобкой).
6. нажмите Ввод

Обратите внимание, что в этом случае я использовал пробел в качестве разделителя (разделителя). При желании можно использовать другие разделители, например запятую или дефис.

СЦЕПИТЬ диапазоны Excel (с помощью VBA)

Ниже приведен пример настраиваемой функции, которую я создал с помощью VBA (я назвал ее CONCATENATEMULTIPLE), которая позволит вам объединить несколько ячеек, а также указать разделитель / разделитель.

Вот код VBA, который создаст эту настраиваемую функцию для объединения нескольких ячеек:

Вот шаги, чтобы скопировать этот код в Excel:

• Перейдите на вкладку разработчика и щелкните значок Visual Basic (или воспользуйтесь сочетанием клавиш Alt + F11).
• В редакторе VB щелкните правой кнопкой мыши любой из объектов, выберите «Вставить» и выберите «Модуль».
• Скопируйте и вставьте приведенный выше код в окно кода модуля.
• Закройте редактор VB.

Щелкните здесь, чтобы загрузить файл примера.

Теперь вы можете использовать эту функцию как любую обычную функцию рабочего листа в Excel.

СВЯЗАТЬ диапазоны Excel с помощью функции TEXTJOIN (доступно в Excel с подпиской на Office 365)

В Excel, который поставляется с Office 365, была представлена ​​новая функция — TEXTJOIN.

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

Вот синтаксис функции:

TEXTJOIN (разделитель, ignore_empty, text1, [text2],…)

• delimiter — здесь можно указать разделитель (разделитель текста). Вы можете ввести это вручную или использовать ссылку на ячейку с разделителем.
• ignore_empty — если это ИСТИНА, пустые ячейки будут игнорироваться.
• text1 — это текст, который нужно соединить. Это может быть текстовая строка или массив строк, например диапазон ячеек.
• [text2] — это необязательный аргумент, в котором вы можете указать до 252 аргументов, которые могут быть текстовыми строками или диапазонами ячеек.

Вот пример того, как работает функция TEXTJOIN:

В приведенном выше примере в качестве разделителя указан пробел, который объединяет текстовые строки в формате A1: A5.

Вы можете узнать больше о функции TEXTJOIN здесь.

Сталкивались ли вы с ситуациями, когда это может быть полезно? Я хотел бы поучиться у вас. Оставляйте свои следы в комментариях!

Excel works!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Исправление ошибки #ЗНАЧ! в функции СЦЕПИТЬ

Почему не работает объединение ячеек в Excel и кнопка совсем не активна? Что делать, если ячейки в таблице в Экселе не объединяются и не дают закончить работу?

Исправление ошибки #VALUE! в функции СЦЕПИТЬ в Excel 2003 и более ранних версиях

Если ошибка #ЗНАЧ! возникает в Office Excel 2003 или более ранних версиях, необходимо отключить параметр “вычисления по правилам 1-2-3” в Excel. Выполните действия, описанные в #VALUE! при скаймании ячеек .

Как сцепить несколько ячеек.

Простейшее выражение для конкатенации содержимого A1 и B1 выглядит следующим образом:

Обратите внимание, что они будут соединены друг с другом без разделителя, как в строке 3 на рисунке ниже. Думаю, такой вариант нас не устроит.

Чтобы разделить их пробелом, введите “ “ во втором аргументе, как в строке 4.

Чтобы разделить объединенные значения с другими разделителями, такими как запятая, пробел или косая черта, смотрите здесь.

Причины

Выделяется несколько причин, почему не объединяются ячейки в Excel, и возникают трудности. К основным объяснениям стоит отнести:

1. Ошибки в объединении и незнание, как выполнить эту работу.
2. Отсутствие активации программы.
3. Неровная граница между «Показатели понижающие КТУ», и «Размер %».
4. Включен общий доступ к файлу.
5. Защита листа и т. д.

Это основные причины, почему не работает объединение ячеек в Excel, нет кнопки, или появляются иные сложности в работе.

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

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

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

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

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

Объединение текстовой строки и ссылки.

Нет необходимости ограничиваться только объединением значений ячеек. Вы также можете добавить к ним какой-либо текст, чтобы сделать результат более значимым и понятным. Например:

Приведенный выше пример информирует пользователя о завершении определенного задания. Обратите внимание, что мы добавляем пробел перед словом «выполнено», чтобы отделить соединенные текстовые элементы.

Естественно, вы можете добавить текст в начале или в середине формулы СЦЕПИТЬ:

Между объединенными элементами добавляется пробел (” “), поэтому результат отображается как “задание 2”, а не “задание2”.

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

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

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

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

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

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

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

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

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

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Оператор “&” против функции СЦЕПИТЬ

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

Единственное существенное отличие между ними – это максимальное ограничение в 255 аргументов функции СЦЕПИТЬ и отсутствие таких ограничений при использовании амперсанда.

Кроме этого, нет никакой разницы между этими двумя методами конкатенации, и нет никакой разницы в скорости работы между СЦЕПИТЬ и “&”.

А поскольку число 255 действительно большое, и в реальных задачах кому-то вряд ли когда-нибудь понадобится объединить столько элементов, разница сводится к удобству и простоте использования. Некоторым пользователям формулы легче читать, я лично предпочитаю использовать метод “&”. Так что, просто придерживайтесь метода конкатенации, который вам удобнее.

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

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

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

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

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

Аргументы функции

• text (текст1) – первое значение, которое вы хотите объединить с другим значением. Значение может быть текстом, буквой, цифрой, ссылкой на ячейку;
• [text2], … ([текст2]) – значение, с которым вы хотите объединить аргумент text (текст1). Всего в одной функции можно использовать до 255 значений, суммарное число букв которых не может превышать более “8192”.

Как объединить столбцы

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

Например, чтобы интегрировать два столбца (столбцы A и B), разделяя их содержимое пробелом, введите следующую формулу в C2, а затем скопируйте ее ниже. При перетаскивании маркера заливки для копирования указатель мыши изменится на крест, как показано на снимке экрана ниже:

Совет. Быстрый способ скопировать формулу – это выбрать ячейку с формулой и дважды щелкнуть маркер заполнения .

Обратите внимание, что программа сама определяет, как далеко копировать после двойного щелчка дескриптор заливки, основываясь на ячейках, указанных вами в качестве аргументов. Если в вашей таблице окажутся пустые места, скажем, A6 и B6 в этом примере были пустыми, будет предложено скопировать только до строки 5. В этом случае вам потребуется вручную перетащить маркер заполнения ещё дальше, чтобы объединить все столбцы ниже.

Объединение чисел и дат в различных форматах

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

ТЕКСТ(значение; формат) имеет два аргумента:

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

Ниже приведены еще несколько примеров, объединяющих текст, дату и число:

=A2 & ” ” & ТЕКСТ(B2; “$# ##0,0”) – отобразить число с двумя десятичными знаками и знак $.

=A2 & ” ” & ТЕКСТ(B2; “0,#”) – не отображает лишние нули и знак валюты.