Concatenate excel не видит
The CONCATENATE function in Excel enables a user to join two or more cells into one. We often need to join the first and last name into a full name or street, house number and city into a full address. One of the easiest ways to complete this is using the Concatenate function. In this tutorial, we will see how to use the function to concatenate texts, cells references, function results, etc.
Formula Syntax Explanation
The syntax of the CONCATENATE function in Excel looks like:
=CONCATENATE(text1, [text2], [text3], …)
text1, [text2], [text3]
These are texts which we want to concatenate, separated with a comma. Only one text is mandatory, while up to 255 texts can be passed to the function. The function accepts all kinds of data, including numbers, dates, other functions, cells references, etc. As a result, the function will return a text, regardless of the input data formats.
Please note that from Excel 2016, the function CONCAT is introduced instead of the CONCATENATE function. These functions have the same syntax and almost similar results. The only difference is that the CONCAT function works with ranges, which will be explained more later. However, we will explain all the examples on the CONCATENATE function, because of the compatibility with the older Excel versions.
Basic usage of the CONCATENATE function
We will first look at the most simple example of the function, which includes joining two texts. In the example, we want to concatenate “Mike“ and “Johnson” in the cell B3 to get the full name. The formula is:
=CONCATENATE(“Mike “, “Johnson”)
As you can see in Image 1, all the texts which we want to join, need to be put under quotations (“). As a result, we get “Mike Johnson” in cell B3.
Now we will see how to do the same concatenation, but with cell references instead of texts. In this example, the first name is in the cell B3, while the last name is in the cell C3. The result of the function is in the cell D3. The formula is:
=CONCATENATE(B3, C3)
As we can see in Image 1, values from B3 and C3 are concatenated into “MikeJohnson” in the cell D3. In this example, we need to add a space between the first and last name to make the output valid.
Concatenate cells with spaces and other symbols
In order to add a space between two cells, we need to put a space under the quotations (“ “) as a parameter of the function. The formula is:
Now the output of the function in D3 is the full name with a space between the first and last name (“Mike Johnson”).
Similarly to this example, we can join two cells with a comma between. In this case, we need to put a comma instead of space. The formula looks like:
Image 4. Concatenation of two cells with a comma
The result of the function in the cell D3 is “Mike, Johnson”. Similarly to this example, we can add any separator between the two cells.
Concatenate a text and cell values
As we saw in the previous example, the CONCATENATE function can work with both cells and texts. Now we will see how to combine cells and text in the same formula. In the cell D3 we want to concatenate the text “Fullname:“ and cells B3 and C3 with a space. The formula is:
The result of the function in D3 is “Fullname: Mike Johnson”. As you can see in Image 5, the function can combine texts and cells in concatenation.
Concatenate cells with a line break
Another possible option is to concatenate cells with a line break. This is possible by using the CHAR function which takes an ASCII code and returns a special character. In our case, the ASCII code for a line break is 10. Before applying the formula, we need to enable wrap text option. To do this, click on the cell with the formula (D3) and then click on the Wrap text icon in the “Alignment group” of the Hometab. The formula is:
=CONCATENATE(B3, CHAR(10), C3)
As a result, the first name is in the first row of the cell D3, while the last name is in the second row. This is also the example of using the function as a parameter of the CONCATENATE function.
Concatenate a range of cells
As we mentioned in the formula explanation, the CONCATENATE function can’t take a range of cells as a parameter. Instead, we have to select every cell from a range as a separate parameter. In the next example, we want to concatenate all the names from the range B3:B8. To do this we need to type “=CONCATENATE(“ in the formula bar, hold CTRL and click on every cell in the range. The formula looks like:
=CONCATENATE(B3,B4,B5,B6,B7,B8)
As a result, we get all the names from the range B3:B8 concatenated in the cell D3.
If you have Excel 2016 or newer, another way to do the same is to use the CONCAT function. This function takes a range as a parameter. If we want to concatenate all the values from the range B3:B8, we need to type “=CONCAT(“ in the formula bar and select the range. The formula looks like:
=CONCAT(B3:B8)
The result in the cell D3 is the same as in the previous example.
Common error when using the CONCATENATE function
In order to prevent the error when using the function as the parameter of the CONCATENATE function, we can use the IFERROR function. This function checks if a selected cell contains an error and returns a given value instead of an error. The formula is:
=CONCATENATE(B3, “ “, IFERROR(C3, “”), “ “, D3)
As you can see from the formula, we check the value in the cell C3 and if it’s an error, we return an empty string (“”). As a result of the formula, we get “Mike Johnson”, because the error value from the cell C3 is replaced by an empty string.
Final thoughts
Мы часто сталкиваемся с ситуацией, когда у нас есть данные, хранящиеся в нескольких столбцах, и все, что нам нужно, это хранить их в одном столбце строка за строкой.
Например, у вас есть таблица полного просмотра, которая содержит различные столбцы, называемые Emp ID, Имя, Фамилия, Адрес, Город, Почтовый индекс, Зарплата. Вы хотели объединить имя и фамилию с пробелом в качестве разделителя между ними, или вы хотели объединить адрес вместе с деталями города и почтового индекса, чтобы вы могли иметь адрес в одной ячейке, которая может быть напечатана на конверте в случае, если Вы отправляете им письмо.
Что вы можете сделать в таких сценариях? Если вы не уверены, что делать в таких случаях, эта статья для вас.
В этой статье мы рассмотрим, как мы можем объединить данные из двух или более столбцов, используя функцию сцепления, а также оператор амперсанда (&)
Поскольку само слово означает в соответствии с английской литературой (Concatenate = link (вещи) вместе в цепочке или серии), конкатенация представляет собой процесс в Excel, который позволяет объединять два или более столбцов, содержащих строки, ячейка за ячейкой (строка за строкой больше точно) с помощью определенных разделителей / разделителей.
Как объединить столбцы в Excel?
У нас есть два способа объединить строки в Excel.
- Использование функции CONCATENATE ()
- Оператор амперсанда (&)
Мы увидим оба метода объединения столбцов в Excel с некоторыми примерами.
Вы можете скачать этот шаблон Excel "Конкатенированные столбцы" здесь - Шаблон Excel "Конкатенированные столбцы"
Конкатенация столбцов в Excel - Пример № 1
Объединение двух или более ячеек с помощью функции CONCATENATE () -
Синтаксис для CONCATENATE в Excel:
= CONCATENATE (текст1, (текст2), (текст3), …) . Где текст - чаще всего значение ссылки на ячейку.
Мы можем видеть, что кроме первого аргумента, остальные из них заключены в квадратные скобки. Это указывает на то, что эти аргументы являются необязательными.
Давайте посмотрим на некоторые примеры!
Предположим, у нас есть два столбца, содержащие имя и фамилию сотрудника в каждом соответствующем столбце (B и C).
Мы можем объединить два значения (B2 и C2) в столбце D (с именем Full Name) следующим образом:
= CONCATENATE (B2, C2)
Смотрите изображение ниже.
Тем не менее, вы можете видеть, что имя и фамилия объединяются без разделителя / разделителя. Пожалуйста, смотрите ниже изображение для реализации.
Вы можете видеть, что в столбце D (полное имя) нет разделителя между именем и фамилией.
Мы хотели бы иметь место между именем и фамилией.
К счастью, CONCATENATE в Excel позволяет нам добавлять разделители, а также разделители для настройки представления.
Смотрите ниже формулу:
= CONCATENATE (B2, “”, C2)
Вы можете легко распознать разницу между предыдущей формулой и этой. В кавычках есть пробел, чтобы добавить пробел между именем и фамилией.
Заметка -
- Мы можем использовать разные разделители в функции CONCATENATE (). Ex. Запятая («, »), дефис («-»), прямая или обратная косая черта, звездочка (*) и т. Д.
Теперь, когда мы нашли решение для объединения двух ячеек, давайте подумаем о более широкой картине и попробуем объединить два столбца. Это так же просто, как перетащить панель уведомлений вниз вашего смартфона.
Вам просто нужно перетащить маркер заполнения вниз, чтобы формула в D2 была скопирована и вставлена в последующие ячейки.
Легко выглядит верно?
Существует другой способ объединить два или более двух столбцов в Excel. Давайте посмотрим, это выглядит проще.
Объединение столбцов в Excel - пример № 2
Объединение двух или более ячеек с помощью оператора амперсанда (&) -
Оператор амперсанда (&) также можно использовать для объединения двух или более ячеек в Excel. Он работает так же, как функция CONCATENATE (). Этот оператор кажется более удобным, поскольку такие ленивые люди, как я, хотели бы ввести «&», а не CONCATENATE ().
Мы попытаемся объединить те же столбцы с помощью оператора амперсанда с синтаксисом ниже:
В ячейку D2 введите формулу: = B2 и C2
Это даст вам следующий вывод:
Идеально иметь пробел между именем и фамилией.
В аналогичном примечании о функции объединения можно добавить разделитель / разделитель, используя оператор амперсанда для объединения двух или более столбцов.
В ячейке D2 замените предыдущую формулу новой и посмотрите результаты: = B2 & ”” & C2
Мы можем перетащить маркер заполнения или использовать Ctrl + D (если между ними нет скрытых ячеек), чтобы применить формулу ко всему столбцу.
Проще набрать и чем CONCATENATE (), верно?
Объединение столбцов в Excel - пример № 3
Объединение двух или более строк с помощью разрыва строки -
Чаще всего вы используете разделитель / разделитель, такой как запятая (, ), пробел, прямая / обратная косая черта (при объединении дат), звездочка («*») и т. Д. Для объединения двух или более строк. Однако что делать, если у вас есть детали адреса, такие как улица, город и почтовый индекс в разных столбцах, и вы хотите объединить их с разрывами строк, чтобы выходные данные выглядели так:
Мы не можем добавить разрыв строки как простой разделитель (например, запятую, пробел и т. Д.) В формуле excel функции CONCATENATE (). Поэтому нам нужно использовать специальную функцию CHAR () для предоставления ASCII-кода разрыва строки (ASCII-код - это код, который представляет английские буквы, причем каждой букве присваивается уникальный номер от 0 до 127. Для разрыва строки ASCII-код равен 10). Обратите внимание, что эти коды совместимы с системой Windows.
Давайте посмотрим на пример:
У нас есть 3 разных столбца, содержащие адресную информацию. столбец A (улица), столбец B (город) и столбец C (почтовый индекс).
Давайте переименуем столбец D в Address и введите следующую формулу в ячейку D2:
= CONCATENATE (A2, CHAR (10), B2, CHAR (10), C2)
Это дает следующий результат:
Вы можете перетащить формулу, чтобы скопировать и применить ее ко всем ячейкам.
Результат будет выглядеть так:
То, что нужно запомнить
Рекомендуемые статьи
Это было руководство по объединению столбцов в Excel. Здесь мы обсудили объединение столбцов в Excel и Как использовать объединение столбцов в Excel, используя различные методы в Excel, а также практические примеры и загружаемый шаблон Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
CONCATENATE Функция является частью текстовой функции. Функция CONCATENATE полезна, когда вы хотите объединить несколько значений ячеек в одно. Возвращает объединенное значение нескольких ячеек.
Например, CONCATENATE («Sachin» & »« & »- это« & »« & »God of Cricket») в этой формуле функция CONCATENATE возвращает значение Sachin - «God of Cricket» . Таким образом, CONCATENATE добавление различных значений вместе.
Функция CONCATENATE также доступна в VBA. Мы обсудим это в конце этой статьи.
КОНКАТЕНАТ Формула в Excel
Ниже приведена формула КОНКАТЕНАТ:
Функция CONCATENATE использует нижеуказанный параметр
TEXT1: это обязательный аргумент, и это первый элемент, который нужно присоединить. Элемент может быть текстовым значением, ссылкой на ячейку или числом
TEXT2: Это также обязательный аргумент и дополнительные текстовые элементы, к которым мы хотим присоединиться. Мы можем объединить до 255 элементов длиной до 8192 символов.
Как использовать функцию CONCATENATE в Excel?
Эта функция CONCATENATE очень проста в использовании. Давайте теперь посмотрим, как использовать функцию CONCATENATE в Excel с помощью нескольких примеров.
Вы можете скачать эту функцию CONCATENATE для шаблона Excel здесь - функция CONCATENATE для шаблона Excel
Пример № 1
Предположим, у вас есть список сотрудников. Тем не менее, у вас есть имя в столбце A и фамилия в столбце B. Ваш менеджер хочет получить полное имя всех сотрудников в течение следующих 10 минут. Если вы впервые пользуетесь ею, значит, вы, черт возьми, почувствовали свое рабочее место.
Однако сегодня я продемонстрирую функцию CONCATENATE для объединения имени и фамилии вместе.
Теперь нам нужно объединить имя со вторым именем, чтобы получить полное имя. Если вы примените функцию CONCATENATE, вы можете объединить два значения двух разных ячеек.
Окончательный результат будет:
Теперь мы разобрались с проблемой, но, тем не менее, есть еще одна проблема для сделок, которая заключается в промежутке между именем и фамилией. Нам нужно объединить пробел после имени, чтобы отсортировать.
Окончательный результат будет:
Нам нужно поместить пробел с двойными кавычками по обе стороны от пробела после первого аргумента, чтобы получить точный результат.
Всякий раз, когда мы передаем аргумент, отличный от ссылки на ячейку, мы должны включить его в двойные кавычки. Если мы передадим без двойной кавычки, мы получим ошибку.
Пример Excel № 2
У нас есть три таблицы сотрудников по продажам. В первой таблице у нас есть их название, сколько единиц они продали, цена за единицу и общая стоимость продаж.
Во второй таблице их имена отсортированы от А до Я. В этой таблице значение продаж отсутствует. Нам нужно извлечь объем продаж из таблицы 1 с помощью Vlookup.
В третьей таблице их имена отсортированы от A до Z, и эта таблица содержит уровень их эффективности.
Теперь нам нужно объединить значение Vlookup с их уровнем эффективности. Например, продажи Эндрюса - 9724, а его эффективность - 78, 56%.
Нам нужно использовать CONCATENATE и Vlookup, чтобы получить точное предложение здесь.
Сначала функция Concatenate берет менеджера по продажам в качестве ссылки и добавляет слово Sales, затем извлекает данные о продажах менеджера по продажам с помощью Vlookup и добавляет значение, а затем снова выбирает значение столбца примечаний с помощью Vlookup.
= CONCATENATE (F3, », « Sales is «, VLOOKUP (F3, A3: D11, 4, 0), и его« VLOOKUP (F3, I3: J11, 2, 0))
Пример № 3
Вместо использования функции конкатенации мы можем использовать амперсанд (&) в качестве оператора конкатенации, и мы можем использовать его вместо функции конкатенации с той же функциональностью и гораздо более простым способом.
Ячейка формулы A1, добавляющая значение ячейки B1, чтобы получить результат, поскольку My Name - Ramya.
Пример № 4
Иногда наши данные должны быть разделены разрывом строки вместо пробелов или символов, как показано в предыдущих примерах. Рассмотрим только предыдущий пример, но покажем уровень эффективности менеджера по продажам в следующей строке.
Например, продажи Эндрюса 9724.
Эффективность Эндрюса составляет 78, 56%.
Так как нам нужно добавить разрыв строки, мы не можем просто ввести разрыв строки здесь, вместо этого нам нужно использовать функцию CHAR, чтобы вставить новую строку, чтобы разбить строку. CHAR (10) добавит новый прерыватель строки в Excel.
Чтобы получить результат, как показано выше, нам нужно включить опцию «Wrap text» для правильного отображения результатов. Для этого используйте Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек». Перейдите на вкладку «Выравнивание» и установите флажок «Обтекание» или перейдите на вкладку «Главная», а в разделе «Выравнивание» щелкните текст обтекания.
= CONCATENATE (F3, ”, ” Sales is “, VLOOKUP (F3, $ A $ 3: $ D $ 11, 4, 0), ” “, CHAR (10), F3, ” “, VLOOKUP (F3, $ I $ 3 : $ J $ 11, 2, 0))
Код VBA для использования функции CONCATENATE
Как и в Excel, мы можем использовать функцию CONCATENATE в коде VBA. Приведенный ниже код иллюстрирует использование функции CONCATENATE в макросах VBA.
Что нужно помнить о функции CONCATENATE
Рекомендуемые статьи
Это было руководство к функции CONCATENATE. Здесь мы обсуждаем формулу CONCATENATE и как использовать функцию CONCATENATE в Excel вместе с практическими примерами и загружаемыми шаблонами Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
В Microsoft Excel, конкатенация , когда содержимое двух или более ячеек в листе объединены в третью, отдельную ячейку. Этот процесс выполняется с помощью функции CONCATENATE или оператора конкатенации.
Инструкции в этой статье относятся к Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel для Office 365, Excel для Mac и Excel Online.
СОГЛАСОВАТЬ Синтаксис и Аргументы
Синтаксис функции относится к макету функции и включает имя функции, скобки, разделители запятых и аргументы.
Синтаксис для функции CONCATENATE :
Текст1 (обязательно): первый элемент, который будет объединен. Это может быть текст, такой как слова или цифры, пробелы, заключенные в кавычки, или ссылки на ячейки для расположения данных на листе.
Text2, Text3, … Text255 (необязательно до 255 текстовых записей): другие элементы для объединения. Может быть добавлено в функцию CONCATENATE до 8192 символов, включая пробелы. Каждая запись должна быть разделена запятой.
Ни один из методов конкатенации не добавляет пробел между словами, что хорошо при соединении двух частей составного слова (например, бейсбола) в одну или объединении двух серий чисел. При объединении имени и фамилии или адреса включите пробел в формулу объединения, как показано в строках 4, 5 и 6 примера изображения.
Конкатенация числовых данных
Даже если числа могут быть объединены, как видно в седьмой строке примера изображения, результат 123456 не считается числом программой, а рассматривается как текстовые данные.
Результат, показанный в ячейке C7, нельзя использовать в качестве аргументов для некоторых математических функций, таких как SUM и AVERAGE . Если этот тип записи включен с аргументами функции, он обрабатывается как другие текстовые данные и игнорируется.
Одним из признаков является то, что сцепленные данные в ячейке C7 выровнены по левому краю, что является выравниванием по умолчанию для текстовых данных. Тот же самый результат происходит, если вместо оператора объединения используется функция CONCATENATE.
Как использовать функцию Excel CONCATENATE
Хотя можно ввести полную функцию вручную , может быть проще использовать диалоговое окно для ввода аргументов функции. Диалоговое окно заботится о вводе скобок, запятых и, в этом примере, кавычек, окружающих пробел.
Читайте также: