Xlfn textjoin что за формула
Функции Excel 2016 СЦЕП (CONCAT) и ОБЪЕДИНИТЬ (TEXTJOIN)
В начале 2016 года Microsoft Excel выпустил очередной релиз своей программы. В этой заметке рассмотрены две новые текстовые функции Excel. На момент написания этой статьи они доступны пока только в режиме Excel online, поэтому скриншоты и последующий ролик записаны именно там.
Чтобы объединить содержимое нескольких ячеек в одну, традиционно используют формулу Excel СЦЕПИТЬ либо специальный оператор амперсанд &. В то же время у этой функции есть ряд недостатков. Основной из них — это необходимость каждую склеиваемую ячейку указывать в виде отдельного аргумента. При большом количестве склеиваемых ячеек приходится изрядно помучиться. С появлением новых функций СЦЕП и ОБЪЕДИНИТЬ этому неудобству пришел конец.
На смену СЦЕПИТЬ пришла функция СЦЕП, которая может сцепить целый диапазон! Теперь вместо отдельных ячеек A1;A2;A3;A4;A5;A6;A7;A8;A9;A10 достаточно указать диапазон A1:A10. Многие давно этого ждали. Дождались!
Однако радость кажется неполной, т.к. между словами обычно вставляют пробелы, запятые или какие-либо другие разделители, а СЦЕП этого не умеет. В примере выше нужно их либо сразу указать в конце каждого слова, либо опять же прописывать вручную. Вот бы здорово еще и разделители сами вставлялись.
Вы не поверите, но это как раз то, что умеет делать вторая текстовая функция эксель, о которой я хотел рассказать! Встречайте — ОБЪЕДИНИТЬ.
Ее синтаксис следующий.
ОБЪЕДИНИТЬ(разделитель; игнорировать_пустые; текст1; [текст2]; …)
разделитель – то, что должно вставляться между ячейками.
игнорировать_пустые – здесь ставится либо 0 (ЛОЖЬ), либо 1 (ИСТИНА). Если поставить 1, то пустые ячейки будут игнорироваться и разделители не будут дублироваться.
текст1; [текст2]; … – это либо отдельные ячейки, либо целый соединяемый диапазон.
В нашем примере функция ОБЪЕДИНИТЬ позволяет получить следующий результат.
Примечание. Некоторое время функция имела названия TEXTJOIN.
Я специально удалил бананы, чтобы показать отсутствие лишнего пробела после яблок.
Теперь объединить ячейки Excel, добавив к ним разделитель, можно всего за несколько секунд. Отличная функция ОБЪЕДИНИТЬ. Выглядит настолько круто, что хочется разработчикам пожать руку.
Xlfn textjoin что за формула
Функция Excel TEXTJOIN
Excel ТЕКСТ ПРИСОЕДИНИТЬСЯ функция объединяет несколько значений из строки, столбца или диапазона ячеек с определенным разделителем.
Синтаксис
аргументы
Разделитель (Обязательно): разделитель, который вставляется между каждым объединенным текстом;
Ignore_empty (Обязательно): определите, следует ли игнорировать пустые ячейки;
Текст 1 (Обязательно): первый текст, который нужно присоединить;
[Text2],… (Необязательно): дополнительные тексты, которые нужно объединить.
Возвращаемое значение
Составное текстовое значение
Примечания к функциям
TEXTJOIN доступен только в Office 2019 и 365.
Примеры
Объединяйте тексты с определенным разделителем, игнорируя или включая пустые ячейки
Как показано на скриншоте ниже, вы хотите объединить тексты в разных столбцах с помощью запятой и игнорировать или включать все пустые ячейки. Пожалуйста, сделайте следующее.
Пример 1: объединение текстов через запятую без учета пустых ячеек
1. Выберите пустую ячейку (F3), скопируйте в нее приведенную ниже формулу, чтобы получить результат.
=TEXTJOIN(«, «,TRUE,B3:E3)
2. Не снимая выделения с ячейки результата, перетащите Ручка заполнения чтобы применить формулу к другим ячейкам.
Пример 2: объединение текстов через запятую, включая пустые ячейки
Выберите пустую ячейку, скопируйте в нее приведенную ниже формулу и затем перетащите маркер заполнения, чтобы применить формулу к другим ячейкам, чтобы получить все сцепленные тексты, включая пустые ячейки. См. Снимок экрана ниже:
=TEXTJOIN(«, «,FALSE,B3:E3)
Связанные функции
Функция ПОДСТАВИТЬ в Excel
Функция ЗАМЕНА в Excel заменяет текст или символы в текстовой строке другим текстом или символами.
Функция Excel TEXT
Функция ТЕКСТ преобразует значение в текст с заданным форматом в Excel.
Функция Excel TRIM
Функция Excel TRIM удаляет все лишние пробелы из текстовой строки и сохраняет только отдельные пробелы между словами.
Функция ВЕРХНИЙ в Excel
Функция Excel ВЕРХНИЙ преобразует все буквы заданного текста в верхний регистр.
Лучшие инструменты для работы в офисе
Как сцепить ячейки в Excel при помощи различных функций и операторов
В этом руководстве вы узнаете о различных способах соединения текстовых строк, ячеек, диапазонов, столбцов и строк в Excel с использованием функции СЦЕПИТЬ, СЦЕП, ОБЪЕДИНИТЬ и оператора «&».
В этом руководстве мы собираемся изучить различные методы «склеивания» данных, чтобы вы смогли выбрать наиболее подходящий для себя.
Что значит и как «сцепить» в Excel?
По сути, существует два способа объединения данных в таблицах:
Когда вы объединяете ячейки, вы «физически» объединяете две или более клеточки в одну. В результате у вас есть одно большое поле, которое отображается в нескольких строчках и / или столбиках на вашем рабочем листе.
Команда СЦЕПИТЬ предназначена для объединения различных фрагментов текста или объединения данных из нескольких ячеек в одну.
Синтаксис её выглядит следующим образом:
Замечание. В Excel 2016, 2019, Excel Online и Excel Mobile СЦЕПИТЬ заменяется функцией СЦЕП (CONCAT в английской версии), которая имеет точно такой же синтаксис. Хотя функция СЦЕПИТЬ сохраняется для обратной совместимости, рекомендуется использовать вместо нее СЦЕП, поскольку Microsoft не дает никаких обещаний, что старая функция будет доступна в будущих версиях программы.
Как сцепить несколько ячеек.
Простейшее выражение для конкатенации содержимого A1 и B1 выглядит следующим образом:
Обратите внимание, что они будут соединены друг с другом без разделителя, как в строке 3 на рисунке ниже. Думаю, такой вариант нас не устроит.
Чтобы разделить их пробелом, введите “ “ во втором аргументе, как в строке 4.
Чтобы разделить объединенные значения с другими разделителями, такими как запятая, пробел или косая черта, смотрите здесь.
Объединение текстовой строки и ссылки.
Нет необходимости ограничиваться только объединением значений ячеек. Вы также можете добавить к ним какой-либо текст, чтобы сделать результат более значимым и понятным. Например:
Приведенный выше пример информирует пользователя о завершении определенного задания. Обратите внимание, что мы добавляем пробел перед словом «выполнено», чтобы отделить соединенные текстовые элементы.
Естественно, вы можете добавить текст в начале или в середине формулы СЦЕПИТЬ:
Между объединенными элементами добавляется пробел (» «), поэтому результат отображается как «задание 2», а не «задание2».
Объединение текстовой строки и вычисляемой формулы
Чтобы сделать результат, возвращаемый каким-то расчётом, более понятным для ваших пользователей, вы можете связать его с текстовым пояснением. Оно объяснит, как следует оценивать получившееся.
Например, вы можете использовать следующую формулу для возврата текущей даты:
Чтобы вычисления с этой функцией всегда давали правильные результаты, запомните следующие простые правила:
Оператор «&» для объединения текста
Этот метод очень удобен во многих случаях, потому что ввод знака амперсанда (&) гораздо быстрее, чем ввод целого выражения 🙂
Подобно функции СЦЕПИТЬ, вы можете использовать «&» для объединения различных текстовых строк, значений ячеек и результатов, возвращаемых другими вычислениями.
Чтобы увидеть оператор конкатенации в действии, давайте перепишем примеры, уже рассмотренные выше:
Слить вместе A1 и B1:
Соедините A1 и B1 через пробел:
Соединить A1, B1 и текст:
Собрать воедино текст и результат вычисления текущей даты:
Как показано на скриншоте ниже, функция СЦЕПИТЬ и оператор «&» возвращают идентичные результаты:
Оператор «&» против функции СЦЕПИТЬ
Многие пользователи задаются вопросом, какой же более эффективный способ объединения строк – использовать СЦЕПИТЬ или оператор «&».
Кроме этого, нет никакой разницы между этими двумя методами конкатенации, и нет никакой разницы в скорости работы между СЦЕПИТЬ и «&».
А поскольку число 255 действительно большое, и в реальных задачах кому-то вряд ли когда-нибудь понадобится объединить столько элементов, разница сводится к удобству и простоте использования. Некоторым пользователям формулы легче читать, я лично предпочитаю использовать метод «&». Так что, просто придерживайтесь метода конкатенации, который вам удобнее.
Объединить с пробелом, запятой и другими символами
В ваших рабочих листах часто может потребоваться собрать воедино элементы таким образом, чтобы они содержали запятые, пробелы, различные знаки пунктуации или другие символы, такие как дефис или косая черта.
Для этого просто включите нужный символ в формулу объединения. Не забудьте заключить этот символ в кавычки, как показано в следующих примерах.
Так можно сцепить с пробелом:
Объединение через запятую:
Соединяем с дефисом:
На рисунке показано, как могут выглядеть результаты:
Объединение текста с переносами строк.
Чаще всего вы бы разделяли соединенные текстовые элементы знаками препинания и пробелами, как показано в предыдущем примере. Однако в некоторых случаях может потребоваться разделить их с помощью переноса строки или возврата каретки. Типичным примером является объединение почтовых адресов из информации в отдельных столбцах.
Проблема заключается в том, что вы не можете просто ввести разрыв строки, как обычный символ, и поэтому необходима специальная функция CHAR для предоставления соответствующего кода ASCII в формулу конкатенации:
В этом примере у нас есть части адреса в столбцах от A до F, и мы соединяем их в столбце G с помощью оператора конкатенации «&». Объединенные элементы разделяются запятой («,»), пробелом (» «) и переводом строки СИМВОЛ(10):
Таким же образом вы можете разделить связанные части и другими символами, такими как:
Хотя проще включить печатные символы в формулу конкатенации, просто набрав их в двойных кавычках, как мы это делали в предыдущем примере.
В любом случае, все четыре из приведенных ниже выражений дают одинаковые результаты:
=СЦЕПИТЬ(A1; СИМВОЛ(47); B1)
Как объединить столбцы
Чтобы собрать воедино два или более столбца, вы просто вводите обычную формулу объединения в первой клетке, а затем копируете ее в другие, перетаскивая маркер заполнения (маленький квадрат, который появляется в нижнем правом углу выделенной клетки).
Например, чтобы интегрировать два столбца (столбцы A и B), разделяя их содержимое пробелом, введите следующую формулу в C2, а затем скопируйте ее ниже. При перетаскивании маркера заливки для копирования указатель мыши изменится на крест, как показано на снимке экрана ниже:
Обратите внимание, что программа сама определяет, как далеко копировать после двойного щелчка дескриптор заливки, основываясь на ячейках, указанных вами в качестве аргументов. Если в вашей таблице окажутся пустые места, скажем, A6 и B6 в этом примере были пустыми, будет предложено скопировать только до строки 5. В этом случае вам потребуется вручную перетащить маркер заполнения ещё дальше, чтобы объединить все столбцы ниже.
Как объединить диапазон.
Объединение значений из нескольких ячеек может потребовать некоторых усилий, поскольку функция СЦЕПИТЬ в Excel не воспринимает массивы и требует использования одной ссылки в каждом аргументе.
Чтобы интегрировать несколько ячеек, скажем, от А1 до А4, вам понадобится что-то подобное:
При присоединении к довольно небольшому диапазону нет ничего сложного, чтобы ввести все ссылки в строке формул. Но было бы весьма утомительно добавлять большой диапазон, указывая каждую ссылку вручную. Ниже вы найдете 3 метода быстрой конкатенации.
Метод 1. Используем клавишу CTRL.
Чтобы быстро выбрать несколько ячеек, вы можете нажать клавишу CTRL и затем щелкнуть по каждой из тех, которые вы хотите использовать в качестве аргумента функции. Вот подробные шаги:
Замечание. При использовании этого метода вы должны нажать на каждую отдельную ячейку. Выбор диапазона с помощью мыши добавит в формулу массив, который функция СЦЕПИТЬ не понимает.
Способ 2. Используйте ТРАНСП
Когда вам нужно консолидировать огромный диапазон, состоящий из десятков или сотен ячеек, предыдущий метод недостаточно удобен и быстр. Ведь он требует нажатия на каждую из них.
В этом случае лучше использовать функцию ТРАНСП (TRANSPOSE в английской версии) для возврата массива, а затем заменить его ссылками одним махом.
Замечание. Какой бы метод вы ни использовали, объединенное значение в С1 является текстом (обратите внимание на выравнивание по левому краю), даже если каждое из исходных значений является числом. Это связано с тем, что СЦЕПИТЬ всегда возвращает текстовую фразу независимо от типа данных источника.
Объединение чисел и дат в различных форматах
Когда вы объединяете какие-то слова с числом или датой, вы можете форматировать результат по-разному в зависимости от набора данных. Чтобы сделать это, вставьте функцию ТЕКСТ в свою формулу.
ТЕКСТ(значение; формат) имеет два аргумента:
Ниже приведены еще несколько примеров, объединяющих текст, дату и число:
Используем новые функции СЦЕП и ОБЪЕДИНИТЬ.
Долгое время СЦЕПИТЬ была первой функцией, о которой мы думали, когда нам нужно было соединить текст в Excel. Но несколько новых игроков недавно вошли в игру. Я говорю о ОБЪЕДИНИТЬ (или TEXTJOIN) и СЦЕП ( или CONCAT), разработанных, чтобы помочь вам интегрировать несколько значений. Используйте эти простые функции всякий раз, когда вам нужно сложить вместе части имен, адресов или фраз, соединить цифры и слова. В настоящее время они доступны в Excel 2016, Excel Online и Mobile, Excel для планшетов и телефонов Android.
Вот как она выглядит:
ОБЪЕДИНИТЬ(разделитель, игнорировать_пустые, ТЕКСТ1, [ТЕКСТ2],…)
Он имеет 3 обязательных и 1 необязательный аргументы:
Для текстовых элементов может быть не более 252 аргументов. Если результат превышает предел 32767 символов, она возвращает ошибку #ЗНАЧ!.
Представленный как часть обновления в феврале 2016 года, СЦЕП заменяет функцию СЦЕПИТЬ в Excel. Работает почти так же, но существенное отличие состоит в том, что теперь можно работать с диапазонами данных, а не перечислять каждую позицию по очереди. Согласитесь, это здорово упрощает работу.
Кроме того, СЦЕП является стандартной функцией, используемой в Google Sheets.
СЦЕПИТЬ продолжает поддерживаться для обеспечения совместимости с предыдущими версиями Excel.
Выглядит это следующим образом:
Есть только два аргумента, один из которых является необязательным:
Для текстовых данных может быть не более 253 аргументов.
Ранее, если вам нужно было объединить текст из нескольких ячеек, необходимо было указать каждую из них по отдельности. С новыми функциями ОБЪЕДИНИТЬ и СЦЕП вы можете просто ссылаться на диапазон и комбинировать текстовые фразы из диапазонов с разделителем или без него.
Скажем, вы хотите сгруппировать вместе части телефонных номеров. Применяя СЦЕПИТЬ, вам нужно будет вводить адреса один за другим, как в этом примере.
В этом случае СЦЕП выглядит гораздо компактнее. Все, что вам нужно сделать, это просто выбрать диапазон для объединения.
В то же время, СЦЕП и СЦЕПИТЬ выглядят одинаково, когда вам нужно соединить части текста в одну фразу с помощью разделителей.
Как легко увидеть, они не обрабатывают пустые ячейки, что приводит к появлению лишних пробелов (например, в G3 и G8).
Поэтому функции ОБЪЕДИНИТЬ суждено стать действительно популярным выбором для пользователей, работающих с большими объемами текстовых данных.
Вы просто указываете разделитель (если нужно), выбираете «игнорировать пустые» и определяете диапазон. Это гораздо более разумное и быстрое решение.
Если у вас есть Excel 2016, изучите применение ОБЪЕДИНИТЬ и СЦЕП, чтобы увидеть, насколько они полезны, и попрощайтесь с традиционными способами при объединении.
TEXTJOIN функция
Функцията TEXTJOIN комбинира текста от множество диапазони и/или низове и включва разделител, който сте задали, между всяка текстова стойност, която ще се комбинира. Ако разделителят е празен текстов низ, тази функция ефективно ще комбинира диапазоните.
Забележка: Тази функция е налична Windows или Mac, ако имате Office 2019 или ако имате абонамент за Microsoft 365. Ако сте абонат наMicrosoft 365, уверете се, че имате най-новата версия на Office.
Синтаксис
TEXTJOIN(разделител; игнорирай_празни; текст1; [текст2];. )
Текстов низ – или празен, или един или повече знаци, оградени с двойни кавички – или препратка към валиден текстов низ. Ако е предоставено число, то ще бъде третирано като текст.
Ако е TRUE, игнорира празните клетки.
Текстовият елемент, който ще се обединява. Текстов низ или масив от низове, като например диапазон от клетки.
Допълнителните текстови елементи за присъединяване. Може да има максимум 252 аргумента «текст» за текстови елементи, включително текст1. Всеки от тях може да бъде текстов низ или масив от низове, като например диапазон от клетки.
Напр. =TEXTJOIN(» «,TRUE,»Слънцето»;»ще»;»изгрее»;»утре»;»сутринта.») ще върне Слънцето ще изгрее утре сутринта.
Забележки
Ако полученият низ надвишава 32767 знака (ограничение за клетка), TEXTJOIN връща #VALUE! грешка.
Примери
Копирайте примерните данни от всяка от следващите таблици и ги поставете в клетка A1 на нов работен лист на Excel. За да покажат резултати формулите, изберете ги, натиснете клавиша F2 и след това натиснете клавиша Enter. Ако е необходимо, коригирайте ширините на колоните, за да видите всичките данни.
TEXTJOIN function
The TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
Note: This feature is available on Windows or Mac if you have Office 2019, or if you have a Microsoft 365 subscription. If you are a Microsoft 365 subscriber, make sure you have the latest version of Office.
Syntax
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
A text string, either empty, or one or more characters enclosed by double quotes, or a reference to a valid text string. If a number is supplied, it will be treated as text.
If TRUE, ignores empty cells.
Text item to be joined. A text string, or array of strings, such as a range of cells.
Additional text items to be joined. There can be a maximum of 252 text arguments for the text items, including text1. Each can be a text string, or array of strings, such as a range of cells.
For example, =TEXTJOIN(» «,TRUE, «The», «sun», «will», «come», «up», «tomorrow.») will return The sun will come up tomorrow.
Remarks
If the resulting string exceeds 32767 characters (cell limit), TEXTJOIN returns the #VALUE! error.
Examples
Copy the example data in each of the following tables, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.