
Программа электронных таблиц Microsoft позволяет выполнять сложные вычисления и интересные анализы. Но бывают случаи, когда необходимо объединить несколько элементов в один результат. Функция Excel CONCATENATE позволяет сделать именно это. Мы объясним, как она работает.
Для чего используется функция CONCATENATE в Excel?
В Excel результаты вычислений обычно отображаются в соответствующей ячейке. Вы можете объединить несколько функций для выполнения сложных вычислений и получения единого результата. Но иногда вам может понадобиться объединить несколько элементов в одной ячейке. Функция CONCATENATE позволяет сделать именно это. Например, предположим, вам дана электронная таблица Excel с месяцами года в одном столбце и средней температурой в другом (столбец 1: январь, февраль, март; столбец 2: 10, 12, 20). Вместо того чтобы отображать два отдельных столбца, можно объединить эти результаты в одну ячейку в строке (например, «10 января», «12 февраля», «20 марта»). Excel CONCATENATE позволяет объединять текст, числа и ссылки на ячейки друг с другом. Вы даже можете интегрировать в формулу другие функции, что в некоторых случаях устраняет необходимость во вспомогательных ячейках.
Синтаксис функции CONCATENATE в Excel
В принципе, CONCATENATE можно использовать в Excel двумя разными способами: как функцию или в виде оператора. Функция имеет как минимум один аргумент, но может связывать друг с другом до 255 записей.
=CONCATENATE(Text1[Text2],…)
Внутри функции Excel называет параметры «текстом», поскольку формула предназначена для объединения нескольких элементов в строку текста. Однако, как указано выше, можно комбинировать различные формы:
- Текст: В Excel вы вставляете текст между кавычками. Это указывает программе, как она должна обрабатывать информацию.
- Числа: Числа можно вставлять как с кавычками, так и без них.
- Ссылки на ячейки: Если вы хотите передать содержимое ячейки в функцию, введите в формулу ссылку на ячейку. Ссылку можно вставить без маркировки или со знаком доллара в качестве маркера.
- Функции: В функцию CONCATENATE можно также вставить другие функции. Затем вывод этих функций конкатенируется со всеми элементами.
Каждый элемент в скобках функции выводится рядом друг с другом. Если вы хотите отделить различные входные данные друг от друга с помощью пробелов (например, для написания полных предложений), вы должны передать эти пробелы в функцию в качестве аргументов. Пробел должен быть введен в функцию внутри кавычек (например, » «).
Поскольку КОНКАТЕНАЦИЯ регулярно используется в Excel, Microsoft добавила оператор, выполняющий ту же задачу. Вы, вероятно, знакомы с понятием операторов по другим действиям. Например, вы можете использовать знак плюс вместо функции «SUM». Вместо CONCATENTATE можно использовать амперсанд («&»).
=A1&B1
С помощью этого оператора можно соединять вместе самые разные элементы. При использовании оператора вычисления не забывайте вставлять пробелы, если они нужны для правильного отображения результата.
Функция Excel CONCATENATE на практике
В повседневной работе с Excel вы, вероятно, будете использовать CONCATENATE в основном для добавления имени, состоящего из одного или нескольких слов, к определенному значению. Во многих случаях значение хранится в отдельной ячейке.
=CONCATENATE(A1," km/h")

Значение из ячейки A1 (в данном случае ссылка абсолютная, поэтому она не изменится, даже если формула будет сдвинута) объединяется с текстом. Чтобы слово не переходило непосредственно в значение, модуль текста начинается с пробела.
Таким образом, Excel позволяет писать целые предложения. Если объединить одну или несколько ячеек с текстом, можно выводить сложное содержимое понятным образом. Предположим, у нас есть список имен и несколько баллов для каждого участника. Мы хотим просуммировать баллы по именам. Для этого мы можем использовать функцию SUMIF.
=SUMIF(A1:A10,$D$2,B1:B10)
Теперь Excel суммирует все баллы, соответствующие имени, которое мы ввели в ячейку D2. Вы также можете выбрать имя удобным образом через выпадающее меню. Теперь мы можем объединить ячейки в конкатенацию имен и результатов.
=A12&" scored "&B12&" points"

Поскольку функция CONCATENATE принимает в качестве аргументов дополнительные функции, нам не нужны вспомогательные ячейки, и мы можем интегрировать функцию SUMIF непосредственно в формулу.
=A12&" scored "&SUMIF(A1:A10,A12,B1:B10)&" points"

В Excel CONCATENATE часто сочетается с функцией TEXT. Эта функция преобразует числовые значения в текст в заданном формате и добавляет соответствующие символы (например, для валют). Это делает функцию ценной в сочетании с конкатенацией.
=CONCATENATE("On ",TEXT(TODAY(),"DDDD"), " the value is ",TEXT(A1,"$0"),"!")

Мы можем сгенерировать полную фразу с помощью этой формулы. Первая функция TEXT вставляет текущий день недели, вторая извлекает число из ячейки и форматирует значение как денежную сумму.
Новые функции: CONCAT и TEXTJOIN
Начиная с Excel 2019, Microsoft представила две новые функции, связанные с функцией CONCATENATE. Они расширяют возможности, предоставляемые функцией Excel CONCATENATE, и поэтому доступны отдельно. Таким образом, версия Excel 2019 года остается совместимой со старыми рабочими листами Excel.
Вы также можете использовать новые функции в Microsoft 365.
Функция CONCAT работает так же, как CONCATENATE. Так же, как и старая версия, она позволяет объединять различные элементы. Одно из преимуществ по сравнению со старой версией заключается в том, что можно вводить целые диапазоны и не нужно заполнять отдельный аргумент для каждой ячейки.
=CONCAT(A1:D2)
Содержимое различных ячеек последовательно вставляется в текст результата. Функция выполняет строку за строкой. Это означает, что за A1 следует B1, а не A2. CONCAT не вставляет пробелы автоматически. Если вы хотите отделить отдельное содержимое друг от друга, необходимо использовать ту же процедуру, что и в CONCATENATE.
Новая функция TEXTJOIN отличается: Хотя цель та же, синтаксис отличается. Вы можете указать разделитель, который хотите использовать, непосредственно в этой функции.
=TEXTJOIN(Delimiter,Ignore_empty,Text1,[Text2],…)
В TEXTJOIN вы сначала указываете, хотите ли вы использовать разделитель (т.е. разделитель), и если да, то какой. Это может быть, например, пробел или дефис. Вы должны заключить эту запись в кавычки. Затем укажите, должна ли функция включать пустые ячейки или нет. Для этого введите TRUE или FALSE. Как и в других функциях, далее следуют собственно объединяемые элементы.
Подобно CONCAT, для функции TEXTJOIN можно ввести весь диапазон ячеек. Благодаря измененному синтаксису теперь можно вставлять разделители между отдельными элементами.