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

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

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

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

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

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


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

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

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

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

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

СЦЕПИТЬ.

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

Результат.

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

Результат 1.

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

Результат 2.

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

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

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

ТРАНСП.

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

Формула массива.

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

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

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

Примеры:

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

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

Пример практического применения функции СЦЕПИТЬ

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

Использование СЦЕПИТЬ для ИНДЕКС и ПОИСКПОЗ с несколькими условиями

Например, у нас имеется таблица с исходными ежемесячными значениями за 2 года. Первый столбец – «Года», второй столбец – «Месяцы» и Третий – «Значения сумм»:

Таблица для примера

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

Чтобы легко понять принцип работы формулы извлечения данных из таблиц разделим процесс на два этапа: найти значение и извлечь. Извлекать будем с помощью функции ИНДЕКС. В ее первом аргументе мы укажем диапазон ячеек со значениями третьего столбца исходной таблицы C2:C25. Для второго аргумента функции ИНДЕКС нам нужно указать номер строки таблицы. Искать будем с помощью функции ПОИСКПОЗ по двум условиям одновременно по двум первым столбцам таблицы.

Если посмотреть на значения в первых двух столбцах таблицы, то легко можно определить каждое значения из двух столбцов в одной строке, то образуется уникальный ID код для каждой строки. Например, для первой строки код равен 2024JANUARY, для второй 2024FEBRUARY и так далее…

Генерация ID кодов строк

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

=IF(E$3,INDEX(Data!$C$2:$E$73,MATCH(CONCATENATE($A$25,$D7),CONCATENATE(Data!$A$2:$A$73,Data!$B$2:$B$73),0),E$4),NA())
Выборка и поиск по нескольким условиям

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

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

Пример использования СЦЕПИТЬ для презентаций в Excel

Скачать пример использования функции СЦЕПИТЬ для дашборда в Excel download file

Смотрите примеры формул на разных листах шаблона.


en ru