Функция Excel CONCAT — примеры конкатенации строк
В учебнике описываются некоторые распространенные варианты использования функции СЦЕП и объясняется, чем она отличается от своей предшественницы — функции СЦЕПИТЬ. Вы узнаете, как объединять строки с пробелом, запятой, точкой с запятой или любым другим разделителем по вашему выбору.
Традиционно конкатенация строк в Excel выполняется с помощью функции СЦЕПИТЬ или символа амперсанда. В современном Excel эти методы старой школы уступают место CONCAT — более новой и улучшенной функции для объединения текста из нескольких ячеек в одну ячейку.
Excel функция СЦЕП
Функция CONCAT в Excel используется для объединения строк из нескольких ячеек или диапазонов в одну ячейку.
Он относится к категории текстовых функций и доступен в Excel для Microsoft 365, Excel 2021, Excel 2019 и Excel для Интернета.
Синтаксис такой же простой:
СЦЕП(текст1, [text2],…)
Где текст 1 (обязательно) и текст2 (необязательно) строки, которые нужно соединить.
Аргументы могут быть предоставлены в виде текстовых значений, ссылок на ячейки или диапазоны. Принимается не более 255 текстовых аргументов. Строки объединяются в порядке их появления в формуле.
В качестве примера напишем формулу CONCAT в простейшей форме для объединения двух текстовых строк — имени пользователя и доменного имени. Обратите внимание, что строки, передаваемые непосредственно в формулу, всегда должны быть заключены в двойные кавычки, например:
=CONCAT(“Джон”, “@gmail.com”)
На приведенном ниже снимке экрана показан результат конкатенации:
Функция CONCAT — 5 вещей, о которых нужно помнить
Прежде чем мы углубимся в практические примеры, позвольте мне кратко описать несколько моментов, на которые следует обратить внимание:
- Для функции CONCAT требуется хотя бы один текст аргумент для работы. Если какой-либо из аргументов недействителен (например, представлен другой функцией, которая выдает ошибку), произойдет ошибка.
- Результатом функции СЦЕПИТЬ всегда является текстовая строка, даже если вы объединяете числа.
- Если результирующая строка превышает 32 767 символов, #VALUE! возникает ошибка.
- В Excel CONCAT нет предопределенного параметра разделителя. Чтобы разделить объединенные фрагменты текста, поместите разделители между объединенными строками, как показано на этот пример.
- Пустые ячейки игнорируются.
Как использовать функцию СЦЕП в Excel — примеры формул
Теперь самое интересное — использование функции CONCAT в различных сценариях для решения реальных задач.
СЦЕПИТЬ две или более ячеек
Для начала давайте обсудим основной вариант использования — объединение ячеек без разделителя. Очевидно, вам просто нужно использовать соответствующие ссылки на ячейки для текст1, текст2, …, текстн аргументы.
Например, чтобы объединить две ячейки (A2 и B2), формула в C2 будет следующей:
= СЦЕП (A2, B2)
Чтобы объединить строки из нескольких смежных ячеек, вы можете предоставить либо ссылки на отдельные ячейки, либо ссылку на один диапазон.
Например, для объединения строк в ячейках с A2 по C2 будут работать обе приведенные ниже формулы:
= СЦЕП (A2, B2, C2)
= СЦЕП (A2: C2)
Если некоторые из объединенных ячеек пусты, не беспокойтесь — функция СЦЕП автоматически игнорирует пустые ячейки.
СЦЕПИТЬ два или более столбца
Чтобы объединить столбцы построчно, вам нужно сделать следующее:
- Напишите формулу CONCAT для объединения ячеек в первой строке.
- Скопируйте формулу на столько строк, сколько необходимо.
Например, чтобы объединить строки в столбцах A, B, C в каждой строке, введите эту формулу в D2:
= СЦЕП (A2: C2)
Затем перетащите маркер заполнения вниз, чтобы скопировать формулу в строки ниже. Если в исходных данных нет пропусков, можно просто дважды щелкнуть маркер заполнения. Подробные инструкции см. в разделе Как скопировать формулу в Excel.
Из-за использования относительных ссылок формула корректируется соответствующим образом для каждой строки. То есть в D3 ссылка изменится на A3:C3, в D4 на A4:C4 и так далее.
Объединить диапазон ячеек
Объединение диапазона ячеек в Excel раньше было проблемой. Но не больше! Теперь вы можете просто поместить ссылку на диапазон в текст аргумент функции CONCAT:
= СЦЕП (A2: C3)
В результате все значения из указанного диапазона объединяются слева направо и далее вниз до следующей строки:
Аналогичным образом вы можете объединить два или более несмежных диапазона:
= СЦЕП (A2: C3, A5: C6)
Объединить все ячейки в столбце
Чтобы объединить значения из всех ячеек в определенном столбце, укажите ссылку на столбец в CONCAT. Например:
=СЦЕП(А:А)
Поскольку функция пропускает пустые ячейки, в результирующей строке не будет пробелов:
Чтобы объединить все ячейки в столбце, кроме заголовка, используйте ссылку на диапазон, например A2:A1048576, где A2 — первая ячейка с данными, а A1048576 — последняя ячейка в столбце:
= СЦЕП (A2: A1048576)
Примечание. При объединении всех ячеек в столбце имейте в виду, что Excel имеет ограничение на общее количество символов, которое может содержать ячейка — до 32 767 символов. Если результирующая строка превышает этот предел, ваша формула выдаст ошибку #ЗНАЧ! ошибка.
CONCAT с разделителем (запятая, пробел, разрыв строки и т. д.)
Как уже упоминалось, в синтаксисе функции CONCAT нет опции разделителя. Однако это не означает, что вы не можете включить его непосредственно в формулу.
Чтобы объединить строки с запятой, поместите один из следующих разделителей между текст аргументы:
- Запятая без пробела: “,”
- Запятая и пробел: “,”
Например, чтобы объединить строки из ячеек A2, B2 и C2, разделяя значения запятой и пробелом, используйте следующую формулу:
=СЦЕП(A2, “, “, B2, “, “, C2)
Чтобы соединиться с пробелом, поставьте ” ” между текст аргументы:
=СЦЕП(A2, ” “, B2, ” “, C2)
Чтобы объединить строки с разрывом строки, используйте CHAR(10), код ASCII для перевода строки:
=CONCAT(A2, СИМВОЛ(10), B2, СИМВОЛ(10), C2)
Чтобы результаты отображались правильно, обязательно включите функцию «Деформация текста».
На первый взгляд синтаксис CONCAT, не предусматривающий разделителя, может показаться недостатком. Однако это позволяет вам использовать разные разделители в одной формуле, чтобы комбинировать несколько строк именно так, как вы хотите!
Например, вы можете вставить разрыв строки после текст 1 при разделении текст2 а также текст3 с запятой:
=CONCAT(A2, СИМВОЛ(10), B2, “, “, C2)
Кончик. Чтобы объединить несколько значений с общим разделителем, используйте функцию TEXTJOIN.
Объединить даты
В ситуации, когда некоторые из комбинированных значений являются датами, вы можете получить запутанные результаты, как показано ниже:
= СЦЕП (A2, СИМВОЛ (10), B2)
Что не так с этой формулой? Ничего такого. Функция CONCAT делает именно то, для чего она предназначена — объединяет текстовые строки. И любые числовые значения преобразуются в текстовые значения во время процесса. Поскольку во внутренней системе Excel даты хранятся в виде порядковых номеров, функция СЦЕП использует эти базовые числа.
Чтобы правильно объединить дату, сначала преобразуйте ее в текстовые строки с помощью функции ТЕКСТ, а затем передайте строку в СЦЕП.
Например, чтобы объединить текстовое значение в формате A2 и дату в формате B2, используйте следующую формулу:
=CONCAT(A2, СИМВОЛ(10), ТЕКСТ(B2, “д-ммм-гггг”))
Вместо «д-ммм-гггг» вы можете использовать любой другой формат даты, который вам нравится.
Объединить текст и другую функцию
Чтобы объединить строки, созданные некоторыми другими формулами, вложите эти формулы в функцию CONCAT.
Предположим, вы хотите отобразить текущее время на листе. Функция NOW может сделать это с легкостью. Чтобы четко указать, что это текущее время, вы можете добавить поясняющий текст в ту же ячейку.
Для выполнения этой задачи мы объединяем текст “Текущее время: ” и функцию СЕЙЧАС. Кроме того, мы заключаем СЕЙЧАС() в ТЕКСТ, чтобы отображать время в нужном формате. Без этого уточнения время будет отображаться как десятичное число, представляющее значение даты/времени внутри Excel.
=CONCAT(“Текущее время: “, ТЕКСТ(СЕЙЧАС(), “чч:мм AM/PM”))
Поскольку мы добавили пробел в конец текстовой строки, нет необходимости включать разделитель в формулу — результат конкатенации выглядит идеально:
CONCAT против CONCATENATE в Excel
Фактически, функция СЦЕП является заменой СЦЕПИТЬ в новых версиях Excel. Хотя старая функция по-прежнему поддерживается из соображений совместимости, вместо нее рекомендуется использовать более новую функцию.
С технической точки зрения существует два основных различия между CONCAT и CONCATENATE в Excel:
- Прежде всего, функция CONCAT преодолевает самое большое ограничение своего предшественника — невозможность обработки диапазона ячеек. В отличие от функции СЦЕПИТЬ, СЦЕПИТЬ может объединять все строки в диапазоне, избавляя вас от необходимости указывать ссылку на каждую ячейку по отдельности.
- Функция СЦЕП доступна только в Excel 365, Excel 2021, Excel 2019 (Windows и Mac) и Excel Online. Функция СЦЕПИТЬ поддерживается во всех версиях Excel с 2007 по 365.
Во всем остальном эти две функции по существу одинаковы.
Вот как можно использовать функцию CONCAT в Excel для соединения текстовых строк. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания
Примеры формул Excel CONCAT (файл .xlsx)
