Функция обратная сцепить в excel

Обновлено: 04.07.2024

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

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

Важно! 255 – это максимально возможное число аргументов функции. Применение большего количества невозможно. Попытка реализовать больше аргументов приведет к ошибке.

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

Опытные пользователи табличного процессора знают, что, объединяя несколько ячеек в одну, данные всех компонентов стираются, кроме самого левого верхнего. Функция СЦЕПИТЬ это предотвращает. Пошаговое руководство выглядит следующим образом:

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

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

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

Способ 1: объединение данных в ячейках

Пошаговое руководство объединения данных:

Производим выделение ячейки, в которой желаем отобразить объединённые значения. Щёлкаем элемент «Вставить функцию», находящийся рядом со строчкой для ввода формул.

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

На экране отобразилось знакомое нам окошко аргументов. Производим установку указателя в первую строчку окошка. Далее, на рабочем листе выделяем ссылку, содержащую данные, необходимые для объединения. Аналогичные действия производим со 2-й строчкой, выделяя другой сектор. Проделываем этот маневр до тех пор, пока адреса всех секторов не будут введены в окошко аргументов. После проведения всех действий щелкаем «ОК».

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

Способ 2: применение функции с пробелом

Этот недостаток легко исправляется добавлением пробелов между аргументами функции. Пошаговое руководство выглядит следующим образом:

Способ 3: добавление пробела через окно аргументов

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

Находим любой пустой сектор на рабочем листе и два раза жмем по нему ЛКМ, вводим внутри него пробел. Лучше, чтобы сектор располагался дальше от главной таблички. Выбранная ячейка никогда не должна заполняться какой-либо информацией.

Реализовываем алгоритм действий из прошлых способов, чтобы добраться до окошка аргументов функции. Как и в предыдущих методах, вписываем значение первого сектора с данными в первое поле. Далее наводим указатель на вторую строчку и указываем адрес того сектора, в который мы только что вводили пробел. Для значительного ускорения процедуры можно произвести копирование значения сектора при помощи комбинации «Ctrl+C».

Далее вводим адрес следующего сектора. В следующее поле снова добавляем адрес пустого сектора. Повторяем аналогичные действия до того момента, пока не закончатся данные в таблице. После проведения всех манипуляций щелкаем кнопку «ОК».

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

Способ 4: объединение колонок

Оператор СЦЕПИТЬ позволяет объединить значения нескольких столбиков в один. Пошаговое руководство выглядит следующим образом:

С секторами первой строчки объединяемых столбиков реализуем те же самые манипуляции, которые приведены во 2-м и 3-м примере. Стоит упомянуть, что если вы хотите применить метод с пустым сектором, то для него необходимо сделать ссылку абсолютного типа. Для этого следует перед всеми символами координат поставить знак «$». Другие поля остаются относительными. После проведения всех манипуляций кликаем на элемент «ОК».

Наводим курсор на правый нижний уголок сектора с формулой. После того, как указатель примет форму плюсика, при помощи зажатия ЛКМ растягиваем маркер до самого низа таблицы.

После реализации этого процесса информация, указанная в столбиках, станет объединённой в одну колонку.

Способ 5: добавление дополнительных символов

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

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

Существует несколько операторов, позволяющих произвести разъединение значений одной ячейки. Примеры функций:

Редактирование функции

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

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

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

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

При работе с большим количеством ячеек в качестве ссылки указывается массив данных. Пошаговое руководство выглядит так:

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

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

Для корректного объединения первоначально необходимо дату вписать в оператор ТЕКСТ. Оператор позволяет придать формат числу.
Значение ДД.ММ.ГГ. определяет, каким образом будет выглядеть дата. К примеру, если ГГ заменить на ГГГГ, то год будет отображаться в виде четырех цифр, а не двух.

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

Видео работы функции

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

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

Заключение

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

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

Применение оператора СЦЕПИТЬ

Синтаксис этого оператора выглядит следующим образом:

В качестве аргументов могут выступать, как текст, так и ссылки на ячейки, которые его содержат. Количество аргументов может варьироваться от 1 до 255 включительно.

Способ 1: объединение данных в ячейках

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

Выделяем ячейку, в которой планируем разместить объединенные данные. Кликаем на кнопку «Вставить функцию». Она имеет вид пиктограммы и размещена слева от строки формул.

Способ 2: применение функции с пробелом

Выполняем задачу по тому же алгоритму, который описан выше.
Двойным щелчком левой кнопки мыши по ячейке с формулой активируем её для редактирования.

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

Способ 3: добавление пробела через окно аргументов

Конечно, если преобразуемых значений не много, то вышеприведенный вариант разрыва склейки прекрасно подойдет. Но его будет затруднительно быстро воплотить, если существуют множество ячеек, которые нужно объединить. Тем более, если эти ячейки не находятся в едином массиве. Значительно упростить расстановку пробела можно, воспользовавшись вариантом его вставки через окно аргументов.

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

Внимание! Как видим, вышеуказанный способ значительно ускоряет процедуру корректного объединения данных в ячейках. Но следует учесть, что данный вариант таит в себе и «подводные камни». Очень важно, чтобы в элементе, который содержит пробел, со временем не появились какие-то данные или он не был сдвинут.

Способ 4: объединение колонок

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

($)

«OK»

Способ 5: добавление дополнительных символов

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

«OK»

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

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

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

Допустим, что у нас имеется база данных по клиентам, где одному названию компании может соответствовать несколько разных email'ов ее сотрудников. Наша задача состоит в том, чтобы собрать все адреса по названиям компаний и сцепить их (через запятую или точку с запятой), чтобы сделать потом, например, почтовую рассылку по клиентам, т.е. получить на выходе что-то похожее на:

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

Способ 0. Формулой

Не очень изящный, зато самый простой способ. Можно написать несложную формулу, которая будет проверять отличается ли компания в очередной строке от предыдущей. Если не отличается, то приклеиваем через запятую очередной адрес. Если отличается, то "сбрасываем" накопленное, начиная заново:

Минусы такого подхода очевидны: из всех ячеек полученного дополнительного столбца нам нужны только последние по каждой компании (желтые). Если список большой, то чтобы их быстро отобрать придется добавить еще один столбец, использующий функцию ДЛСТР (LEN) , проверяющий длину накопленных строк:

Теперь можно отфильтровать единички и скопировать нужные склейки адресов для дальнейшего использования.

Способ 1. Макрофункция склейки по одному условию

Если исходный список не отсортирован по компаниям, то приведенная выше простая формула не работает, но можно легко выкрутиться с помощью небольшой пользовательской функции на VBA. Откройте редактор Visual Basic нажатием на сочетание клавиш Alt+F11 или с помощью кнопки Visual Basic на вкладке Разработчик (Developer) . В открывшемся окне вставьте новый пустой модуль через меню Insert - Module и скопируйте туда текст нашей функции:

Если теперь вернуться в Microsoft Excel, то в списке функций (кнопка fx в строке формул или вкладка Формулы - Вставить функцию) можно будет найти нашу функцию MergeIf в категории Определенные пользователем (User Defined) . Аргументы у функции следующие:

Способ 2. Сцепить текст по неточному условию

Если заменить в 13-й строчке нашего макроса первый знак = на оператор приблизительного совпадения Like, то можно будет осуществлять склейку по неточному совпадению исходных данных с критерием отбора. Например, если название компании может быть записано в разных вариантах, то мы можем одной функцией проверить и собрать их все:

Поддерживаются стандартные спецсимволы подстановки:

По умолчанию оператор Like регистрочувствительный, т.е. понимает, например, "Орион" и "оРиОн" как разные компании. Чтобы не учитывать регистр можно добавить в самое начало модуля в редакторе Visual Basic строчку Option Compare Text, которая переключит Like в режим, когда он невосприимчив к регистру.

Таким образом можно составлять весьма сложные маски для проверки условий, например:

Способ 3. Макрофункция склейки текста по двум условиям

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

Применяться она будет совершенно аналогично - только аргументов теперь нужно указывать больше:

Способ 4. Группировка и склейка в Power Query

Решить проблему можно и без программирования на VBA, если использовать бесплатную надстройку Power Query. Для Excel 2010-2013 ее можно скачать здесь, а в Excel 2016 она уже встроена по умолчанию. Последовательность действий будет следующей:

Power Query не умеет работать с обычными таблицами, поэтому первым шагом превратим нашу таблицу в "умную". Для этого ее нужно выделить и нажать сочетание Ctrl + T или выбрать на вкладке Главная - Форматировать как таблицу (Home - Format as Table) . На появившейся затем вкладке Конструктор (Design) можно задать имя таблицы (я оставил стандартное Таблица1):

Теперь загрузим нашу таблицу в надстройку Power Query. Для этого на вкладке Данные (если у вас Excel 2016) или на вкладке Power Query (если у вас Excel 2010-2013) жмем Из таблицы (Data - From Table) :

В открывшемся окне редактора запросов выделяем щелчком по заголовку столбец Компания и сверху жмем кнопку Группировать (Group By) . Вводим имя нового столбца и тип операции в группировке - Все строки (All Rows) :

Жмем ОК и получаем для каждой компании мини-таблицу сгруппированных значений. Содержимое таблиц хорошо видно, если щелкать левой кнопкой мыши в белый фон ячеек (не в текст!) в получившемся столбце:

Теперь добавим еще один столбец, где с помощью функции склеим через запятую содержимое столбцов Адрес в каждой из мини-таблиц. Для этого на вкладке Добавить столбец жмем Пользовательский столбец (Add column - Custom column) и в появившемся окне вводим имя нового столбца и формулу сцепки на встроенном в Power Query языке М:

Обратите внимание, что все М-функции регистрочувствительные (в отличие от Excel). После нажатия на ОК получаем новый столбец со склееными адресами:

Осталось удалить ненужный уже столбец ТаблАдресов (правой кнопкой мыши по заголовку - Удалить столбец) и выгрузить результаты на лист, нажав на вкладке Главная - Закрыть и загрузить (Home - Close and load) :

Важный нюанс : в отличие от предыдущих способов (функций), таблицы из Power Query не обновляются автоматически. Если в будущем произойдут какие-либо изменения в исходных данных, то нужно будет щелкнуть правой кнопкой в любое место таблицы результатов и выбрать команду Обновить (Refresh) .

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

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

Функция входит в группу «текстовые». Синтаксис: (текст 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 символов.

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

Читайте также: