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

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

Эта простая функция заменена более функциональной версией — СЦЭПИТ. Фактически в современных версиях Microsoft Office этой функции уже нет, она полностью заменена функцией ШАГ. На данный момент его еще можно использовать, он включен для обратной совместимости, но через некоторое время его может не быть. Поэтому рекомендуется в Excel 2016 Online и более поздних версиях использовать функцию ШАГ.

Функция СЦЕПИТЬ – подробное описание

Функция СЦЭПИТ относится к тексту. Это означает, что он используется для выполнения операций над текстовыми значениями. При этом вы можете указывать аргументы в разных форматах: текстовом, числовом или в виде ссылок на ячейки. 

В целом правила использования этой функции следующие:

1. Точка с запятой используется для разделения аргументов. Если пользователь решит использовать другие символы, то на дисплее будет результат в кавычках.
2. Если значение в текстовом формате используется в качестве аргумента функции и вводится непосредственно в формулу, его необходимо заключить в кавычки. Если есть ссылка на такое значение, то кавычки не требуются. То же самое касается числовых значений. Если вам нужно добавить к строке цифру, то кавычка не требуется. Если вы нарушите эти правила, будет отображена следующая ошибка — #ИМЯ?
3. Если вам необходимо добавить пробел между соединяемыми элементами, его необходимо добавлять отдельной текстовой строкой, то есть в кавычках. Так: " " .

Теперь давайте более подробно рассмотрим синтаксис этой функции. Он очень простой. 

Синтаксис

Итак, аргумент по сути только один — это текстовая строка, которую следует вставить. Каждый аргумент, как мы уже знаем, отделяется точкой с запятой. Вы можете указать до 255 аргументов. Сами они дублируются по-своему. Обязателен первый аргумент. А как мы уже знаем, аргументы можно указывать в трёх форматах: текст, число и ссылка. 

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

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

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

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

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

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

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

Распространенные проблемы с функцией СЦЕПИТЬ

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

1. Котировки отображаются в строке результата. Чтобы избежать этой проблемы, вам нужно использовать точку с запятой в качестве разделителя. Но, как уже говорилось выше, это правило не распространяется на числа.
2. Слова очень близки. Данная проблема возникает потому, что человек не знает всех нюансов использования функции. СЦЭПИТ. Чтобы отображать слова отдельно, к ним необходимо добавить пробел. Либо можно вставить его сразу после текстового аргумента (как внутри ячейки, так и при вводе текста в формуле отдельно). Например так: =CONCATENATE("Привет", "дорогой"). Мы видим, что здесь в конце слова «Привет» добавлен пробел. 
3. #ИМЯ? Это указывает на то, что для текстового аргумента не были указаны кавычки. 

Рекомендации по использованию функции

Чтобы работа с этой функцией была более эффективной, необходимо учесть несколько важных рекомендаций:

1. Используйте & как можно больше. Если вам нужно соединить всего две текстовые строки, то нет необходимости использовать для этого отдельную функцию. Так таблица будет работать быстрее, особенно на слабых компьютерах с небольшим объемом оперативной памяти. Примером может служить следующая формула: = А1 и В1. Это похоже на формулу =РАЗДЕЛ(A1,B1). Особенно первый вариант проще при вводе формулы вручную.
2. Если необходимо объединить валюту или дату с текстовой строкой, а также информацию в любом другом формате, кроме перечисленных выше, то необходимо предварительно обработать ее функцией ТЕКСТ. Он предназначен для преобразования чисел, дат, символов в текст.

Как видите, разобраться в этих нюансах совсем не сложно. И они следуют из информации выше. 

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

Итак, общая формула: СЦЕПИТЬ([текст2];[текст2];…). Вставьте текст в соответствующие места. Важно отметить, что требование к получаемому тексту следующее: он должен быть меньше длины поля, в которое вводится значение. В качестве атрибутов можно использовать не только предопределённые значения, но и информацию в ячейках, а также результаты вычислений по другим формулам.

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

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

Кстати, рекомендация по использованию Excel в целом: всегда учите горячие клавиши. Они помогут вам сэкономить много времени.

Но пока вы новичок, вам придется использовать специально созданное окно.

Так как это назвать? Если посмотреть на строку ввода формулы, то слева от нее есть вот такая маленькая кнопочка «fx». Если нажать ее, появится следующее меню. Нам необходимо выбрать нужную функцию из списка.

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

Если вводить данные вручную, то ввод осуществляется, начиная со знака «равно». То есть вот так:

=СЦЕПИТЬ(D2;»»,;E2)

После всех проделанных нами операций мы увидим в полученной ячейке текст «21.09», который состоит из нескольких частей: числа 21, которое можно найти в ячейке с индексом D2 и строки 09, которая находится в ячейке E2. . Чтобы их разделяла точка, мы использовали ее в качестве второго аргумента. 

Привязка к имени

Для ясности давайте рассмотрим пример, описывающий, как привязывать имена. 

Допустим, у нас есть такая таблица. Содержит информацию об имени, фамилии, городе, штате клиентов. Наша задача — соединить имя и фамилию и получить полное имя. 

Исходя из этой таблицы, мы понимаем, что ссылки на имена следует давать в столбце Б, а на фамилии – А. Сама формула будет записана в первой ячейке под заголовком «ФИО».

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

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

После того, как мы определили аргументы, запишем их в формулу в соответствующей последовательности. 

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

Иногда между аргументами можно поставить обычную запятую. Если используется английская версия Excel, то ставится запятая. Если версия -language, то точка с запятой. После того, как мы нажмем Enter, появится объединенная версия.

Теперь все, что осталось, — это просто использовать маркер автозаполнения, чтобы вставить эту формулу во все остальные ячейки этого столбца. В результате мы имеем полное имя каждого клиента. Миссия выполнена.

Точно таким же образом можно соединить штат и город.

Связывание чисел и текста

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

Нам нужен следующий конечный результат.

В этом случае нам также понадобятся три аргумента, а синтаксис останется прежним. Но давайте попробуем выполнить задание несколько повышенной сложности. Предположим, нам нужно написать сложную строку «У нас есть 25 яблок». Поэтому нам нужно к трем существующим аргументам добавить еще одну строчку «У нас есть». Конечный результат выглядит следующим образом.

=CONCATENATE("У нас есть ";F17;" ";F16)

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

Соединение VLOOKUP и CONCATENATE

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

Итак, допустим, у нас есть такая таблица. В нем описывается, какие товары в данный момент находятся на первом и втором складах. 

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

После этого мы объединяем данные. 

Это можно сделать либо с помощью этой формулы:

=B2&»/»&C2

Или такой.

=СЦЕПИТЬ(B2;”/”;C2)

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

Далее берем следующую таблицу и заполняем ее информацией о выбранном посетителем товаре. Нам необходимо получить информацию о стоимости товара и номере склада из первой таблицы. Это делается с помощью функции ВПР.

Далее выберите ячейку K2, и напишите в нем следующую формулу. 

{=ВПР(G2&»/»&H2;A2:E6;5;0)}

Или это можно записать через функцию СЦЭПИТ.

{=ВПР(СЦЕПИТЬ(G2;»/»;H2);A2:E6;5;ЛОЖЬ)}

Синтаксис в данном случае аналогичен тому, как осуществлялось объединение информации о номере и складе. 

Вам нужно включить функцию ВПР через комбинацию клавиш «Ctrl» + «Shift» + «Enter».

Как видите, все просто.