Функция TEXTJOIN в Excel для объединения текста из нескольких ячеек
В учебном пособии показано, как использовать функцию СОЕДИНЕНИЕ ТЕКСТОВ для объединения текста в Excel с практическими примерами.
До недавнего времени существовало два распространенных метода объединения содержимого ячеек в Excel: оператор конкатенации и функция СЦЕПИТЬ. С введением TEXTJOIN кажется, что появилась более мощная альтернатива, которая позволяет вам соединять текст более гибким способом, включая любой разделитель между ними. Но на самом деле, это гораздо больше!
Функция Excel ТЕКСТОВОЕ СОЕДИНЕНИЕ
TEXTJOIN в Excel объединяет текстовые строки из нескольких ячеек или диапазонов и разделяет объединенные значения любым указанным разделителем. Он может либо игнорировать, либо включать в результат пустые ячейки.
Эта функция доступна в Excel для Office 365, Excel 2021 и Excel 2019.
Синтаксис функции TEXTJOIN следующий:
TEXTJOIN(разделитель, ignore_empty, text1, [text2]…)
Где:
- Разделитель (обязательно) — это разделитель между каждым текстовым значением, которое вы объединяете. Обычно он предоставляется в виде текстовой строки, заключенной в двойные кавычки, или ссылки на ячейку, содержащую текстовую строку. Число, указанное в качестве разделителя, обрабатывается как текст.
- Ignore_empty (обязательно) — определяет, игнорировать ли пустые ячейки или нет:
- TRUE — игнорировать любые пустые ячейки.
- FALSE – включить пустые ячейки в результирующую строку.
- Text1 (обязательно) — первое значение для объединения. Может быть предоставлен в виде текстовой строки, ссылки на ячейку, содержащую строку, или массива строк, например диапазона ячеек.
- Text2, … (необязательно) – дополнительные текстовые значения, которые необходимо объединить. Допускается не более 252 текстовых аргументов, включая текст 1.
В качестве примера объединим части адреса из ячеек B2, C2 и D2 вместе в одну ячейку, разделив значения запятой и пробелом:
С помощью функции CONCATENATE вам нужно указать каждую ячейку отдельно и поставить разделитель (“,”) после каждой ссылки, что может быть утомительно при объединении содержимого многих ячеек:
= СЦЕПИТЬ (A2, “, “, B2, “, “, C2)
В Excel TEXTJOIN вы указываете разделитель только один раз в первом аргументе и указываете диапазон ячеек для третьего аргумента:
=TEXTJOIN(“, “, ИСТИНА, A2:C2)
TEXTJOIN в Excel — 6 вещей, которые нужно помнить
Чтобы эффективно использовать TEXTJOIN на ваших листах, обратите внимание на несколько важных моментов:
- СОЕДИНЕНИЕ ТЕКСТОВ — это новая функция, которая доступна только в Excel 2019 — Excel 365. В более ранних версиях Excel вместо этого используйте функцию СЦЕПИТЬ или оператор «&».
- В новых версиях Excel вы также можете использовать функцию CONCAT для объединения значений из отдельных ячеек и диапазонов, но без параметров разделителей или пустых ячеек.
- Любой номер, указанный в TEXTJOIN для разделитель или же текст аргументы преобразуются в текст.
- Если разделитель не указан или представляет собой пустую строку (“”), текстовые значения объединяются без разделителя.
- Функция может обрабатывать до 252 текстовых аргументов.
- Результирующая строка может содержать не более 32 767 символов, что является пределом ячеек в Excel. Если этот предел превышен, формула ОБЪЕДИНЕНИЕ ТЕКСТОВ возвращает ошибку #ЗНАЧ! ошибка.
Как соединить текст в Excel — примеры формул
Чтобы лучше понять все преимущества TEXTJOIN, давайте посмотрим, как использовать эту функцию в реальных сценариях.
Преобразовать столбец в список, разделенный запятыми
Когда вы хотите объединить вертикальный список, разделяя значения запятой, точкой с запятой или любым другим разделителем, TEXTJOIN — это правильная функция для использования.
В этом примере мы будем объединять победы и поражения каждой команды из таблицы ниже. Это можно сделать с помощью следующих формул, которые отличаются только диапазоном соединяемых ячеек.
Для команды 1:
=ТЕКСТОВОЕ СОЕДИНЕНИЕ(“,”, ЛОЖЬ, B2:B6)
Для команды 2:
=ТЕКСТСОЕДИНЕНИЕ(“,”, ЛОЖЬ, C2:C6)
И так далее.
Во всех формулах используются следующие аргументы:
- Разделитель – запятая (“,”).
- Игнорировать_пусто установлено значение FALSE, чтобы включить пустые ячейки, потому что нам нужно показать, в какие игры не играли.
В результате вы получите четыре списка через запятую, которые представляют победы и поражения каждой команды в компактной форме:
Объединить ячейки с разными разделителями
В ситуации, когда вам нужно разделить комбинированные значения с разными разделителями, вы можете указать несколько разделителей как константу массива или ввести каждый разделитель в отдельную ячейку и использовать ссылку на диапазон для разделитель аргумент.
Предположим, вы хотите соединить ячейки, содержащие разные части имени, и получить результат в следующем формате: Фамилия, Имя второе имя.
Как видите, Фамилия и Имя разделены запятой и пробелом (“,”), а Имя и Отчество только пробелом (“”). Итак, включаем эти два разделителя в константу массива {“, “,” “} и получаем следующую формулу:
=TEXTJOIN({“, “,” “}, ИСТИНА, A2:C2)
Где A2:C2 — объединяемые части имени.
Кроме того, вы можете ввести разделители без кавычек в некоторые пустые ячейки (например, запятую и пробел в F3 и пробел в G3) и использовать диапазон $F$3:$G$3 (обратите внимание на абсолютные ссылки на ячейки) для в разделитель аргумент:
=TEXTJOIN($F$3:$G$3, ИСТИНА, A2:C2)
Используя этот общий подход, вы можете объединять содержимое ячеек в различных формах.
Например, если вы хотите получить результат в Имя Второй инициал Фамилия формат, затем используйте функцию ВЛЕВО, чтобы извлечь первый символ (начальный) из ячейки C2. Что касается разделителей, мы ставим пробел (“”) между Именем и инициалом Отчества; точка и пробел (“”.”) между Инициалом и Фамилией:
=TEXTJOIN({” “,”. “}, ИСТИНА, B2, ВЛЕВО(C2,1), A2)
Соединить текст и даты в Excel
В конкретном случае, когда вы объединяете текст и даты, указание дат непосредственно в формуле TEXTJOIN не будет работать. Как вы, возможно, помните, Excel хранит даты в виде порядковых номеров, поэтому ваша формула вернет число, представляющее дату, как показано на снимке экрана ниже:
=TEXTJOIN(” “, ИСТИНА, A2:B2)
Чтобы исправить это, вам нужно преобразовать дату в текстовую строку, прежде чем присоединиться к ней. И здесь на помощь приходит функция ТЕКСТ с нужным кодом формата (в нашем случае «мм/дд/гггг»):
=TEXTJOIN(” “, TRUE, A2, TEXT(B2, “мм/дд/гггг”))
Объединить текст с разрывами строк
Если вы хотите объединить текст в Excel, чтобы каждое значение начиналось с новой строки, используйте CHAR(10) в качестве разделителя (где 10 — символ перевода строки).
Например, чтобы объединить текст из ячеек A2 и B2, разделив значения разрывом строки, используйте следующую формулу:
=ТЕКСТСОЕДИНЕНИЕ(СИМВОЛ(10), ИСТИНА, A2:B2)
Кончик. Чтобы результат отображался в несколько строк, как показано на снимке экрана выше, убедитесь, что функция переноса текста включена.
TEXTJOIN IF для объединения текста с условиями
Благодаря способности Excel TEXTJOIN обрабатывать массивы строк, его также можно использовать для условного слияния содержимого двух или более ячеек. Чтобы это сделать, используйте функцию ЕСЛИ, чтобы оценить диапазон ячеек и вернуть массив значений, которые удовлетворяют условию. текст 1 аргумент TEXTJOIN.
Из таблицы, показанной на снимке экрана ниже, предположим, что вы хотите получить список Команда 1 члены. Для этого вложите следующий оператор IF в текст 1 аргумент:
ЕСЛИ($B$2:$B$9=1, $A$2:$A$9, “”)
На простом английском языке приведенная выше формула гласит: если столбец B равен 1, вернуть значение из столбца A в той же строке; в противном случае вернуть пустую строку.
Полная формула для Команда 1 принимает такую форму:
=TEXTJOIN(“, “, ИСТИНА, ЕСЛИ($B$2:$B$9=1, $A$2:$A$9, “”))
Аналогичным образом вы можете получить разделенный запятыми список членов Команда 2:
=TEXTJOIN(“, “, ИСТИНА, ЕСЛИ($B$2:$B$9=2, $A$2:$A$9, “”))
Примечание. Благодаря функции динамических массивов, доступной в Excel 365 и 2021, это работает как обычная формула, показанная на снимке экрана выше. В Excel 2019 вы должны ввести его как традиционную формулу массива, нажав сочетание клавиш Ctrl + Shift + Enter.
Поиск и возврат нескольких совпадений в списке, разделенном запятыми
Как вы, наверное, знаете, функция ВПР Excel может возвращать только первое найденное совпадение. Но что, если вам нужно получить все совпадения для определенного идентификатора, SKU или чего-то еще?
Чтобы вывести результаты в отдельные ячейки, используйте одну из формул, описанных в статье Как выполнять ВПР нескольких значений в Excel.
Чтобы найти и вернуть все совпадающие значения в одной ячейке в виде списка, разделенного запятыми, используйте формулу СОЕДИНЕНИЕ ЕСЛИ.
Чтобы увидеть, как это работает на практике, давайте получим список продуктов, купленных данным продавцом, из приведенной ниже таблицы-образца. Это легко сделать с помощью следующей формулы:
=TEXTJOIN(“, “, ИСТИНА, ЕСЛИ($A$2:$A$12=D2, $B$2:$B$12, “”))
Где A2:A12 — названия продавцов, B2:B12 — продукты, а D2 — интересующий продавец.
Приведенная выше формула переходит в E2 и выводит все совпадения для целевого продавца в D2 (Адам). Благодаря грамотному использованию относительных (для целевого продавца) и абсолютных (для названий продавцов и продуктов) ссылок на ячейки формула корректно копируется в ячейки ниже и прекрасно работает и для двух других продавцов:
Примечание. Как и в предыдущем примере, это работает как обычная формула в Excel 365 и 2021 и как формула CSE (Ctrl + Shift + Enter) в Excel 2019.
Логика формулы точно такая же, как и в предыдущем примере:
Оператор IF сравнивает каждое имя в A2:A12 с целевым именем в D2 (Adam в нашем случае):
ЕСЛИ($A$2:$A$12=D2, $B$2:$B$12, “”)
Если логическая проверка дает ИСТИНА (т. е. имя в D2 совпадает с именем в столбце A), формула возвращает произведение из столбца B; в противном случае возвращается пустая строка (“”). Результатом ЕСЛИ является следующий массив:
{“”;””;”Бананы”;”Яблоки”;””;””;””;”Апельсины”;””;”Лимоны”;””}
Массив переходит в функцию TEXTJOIN как текст 1 аргумент. И поскольку TEXTJOIN настроен на разделение значений запятой и пробелом (“,”), мы получаем эту строку в качестве окончательного результата:
Бананы, яблоки, апельсины, лимоны
Excel TEXTJOIN не работает
Когда ваша формула TEXTJOIN приводит к ошибке, это, скорее всего, одно из следующих:
- #ИМЯ? ошибка возникает, когда TEXTJOIN используется в более старой версии Excel, где эта функция не поддерживается (до 2019 г.), или когда в имени функции указана ошибка.
- #ЦЕННОСТЬ! ошибка возникает, если результирующая строка превышает 32 767 символов.
- #ЦЕННОСТЬ! ошибка также может возникнуть, если Excel не распознает разделитель как текст, например, если вы указываете какой-либо непечатаемый символ, такой как CHAR (0).
Вот как использовать функцию TEXTJOIN в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
