Excel объединяет текст из нескольких ячеек в одну
Обновлено: 21.11.2024
До недавнего времени существовало два распространенных метода объединения содержимого ячеек в Excel: оператор конкатенации и функция СЦЕПИТЬ. С введением TEXTJOIN кажется, что появилась более мощная альтернатива, которая позволяет вам соединять текст более гибким способом, включая любой разделитель между ними. Но на самом деле это гораздо больше!
Функция ТЕКСТОВОЕ СОЕДИНЕНИЕ в Excel
TEXTJOIN в Excel объединяет текстовые строки из нескольких ячеек или диапазонов и разделяет объединенные значения любым указанным разделителем. Он может либо игнорировать пустые ячейки, либо включать их в результат.
Эта функция доступна в Excel для Office 365, Excel 2021 и Excel 2019.
Синтаксис функции TEXTJOIN следующий:
- Разделитель (обязательно) — это разделитель между каждым текстовым значением, которое вы объединяете. Обычно он предоставляется в виде текстовой строки, заключенной в двойные кавычки, или ссылки на ячейку, содержащую текстовую строку. Число, указанное в качестве разделителя, рассматривается как текст.
- Ignore_empty (обязательно) — определяет, следует ли игнорировать пустые ячейки:
- TRUE – игнорировать любые пустые ячейки.
- FALSE – включить в результирующую строку пустые ячейки.
В качестве примера давайте объединим части адреса из ячеек B2, C2 и D2 вместе в одну ячейку, разделяя значения запятой и пробелом:
При использовании функции СЦЕПИТЬ вам нужно указать каждую ячейку отдельно и поставить разделитель (",") после каждой ссылки, что может быть неудобно при объединении содержимого многих ячеек:
=СЦЕПИТЬ(A2, ", ", B2, ", ", C2)
В Excel TEXTJOIN вы указываете разделитель только один раз в первом аргументе и указываете диапазон ячеек для третьего аргумента:
=TEXTJOIN(", ", ИСТИНА, A2:C2)
TEXTJOIN в Excel — 6 вещей, которые нужно помнить
Чтобы эффективно использовать TEXTJOIN на ваших листах, обратите внимание на несколько важных моментов:
Как соединить текст в Excel — примеры формул
Чтобы лучше понять все преимущества TEXTJOIN, давайте посмотрим, как использовать эту функцию в реальных сценариях.
Преобразовать столбец в список, разделенный запятыми
Если вы хотите объединить вертикальный список, разделив значения запятой, точкой с запятой или любым другим разделителем, вам подойдет функция TEXTJOIN.
Для этого примера мы объединим победы и поражения каждой команды из таблицы ниже. Это можно сделать с помощью следующих формул, которые отличаются только диапазоном соединяемых ячеек.
=TEXTJOIN(",", ЛОЖЬ, B2:B6)
=TEXTJOIN(",", ЛОЖЬ, C2:C6)
Во всех формулах используются следующие аргументы:
- Разделитель – запятая (","). Для параметра
- Ignore_empty установлено значение FALSE, чтобы включить пустые ячейки, потому что нам нужно показать, в какие игры не играли.
В результате вы получите четыре списка, разделенных запятыми, которые представляют победы и поражения каждой команды в компактной форме:
Объединить ячейки с разными разделителями
В ситуации, когда вам нужно разделить комбинированные значения с помощью разных разделителей, вы можете указать несколько разделителей как константу массива или ввести каждый разделитель в отдельную ячейку и использовать ссылку на диапазон для разделителя аргумент.
Предположим, вы хотите соединить ячейки, содержащие разные части имени, и получить результат в следующем формате: Фамилия, Имя Отчество.
Как видите, Фамилия и Имя разделены запятой и пробелом (","), а Имя и Отчество - только пробелом (""). Итак, мы включаем эти два разделителя в константу массива и получаем следующую формулу:
Где A2:C2 — части имени, которые нужно объединить.
В качестве альтернативы вы можете ввести разделители без кавычек в некоторые пустые ячейки (например, запятую и пробел в F3 и пробел в G3) и использовать диапазон $F$3:$G$3 (обратите внимание на абсолютную ячейку ссылки) для аргумента разделитель:
=TEXTJOIN($F$3:$G$3, ИСТИНА, A2:C2)
Используя этот общий подход, вы можете объединять содержимое ячеек в различных формах.
Например, если вы хотите получить результат в формате Имя Отчество инициалов Фамилия, используйте функцию LEFT для извлечения первый символ (начальный) из ячейки C2. Что касается разделителей, мы ставим пробел ("") между Именем и инициалом Отчества; точка и пробел ("".") между Инициалом и Фамилией:
=TEXTJOIN(, ИСТИНА, B2, ВЛЕВО(C2,1), A2)
Объединить текст и даты в Excel
В определенных случаях, когда вы объединяете текст и даты, указание дат непосредственно в формуле TEXTJOIN не работает. Как вы помните, Excel хранит даты в виде порядковых номеров, поэтому ваша формула будет возвращать число, представляющее дату, как показано на снимке экрана ниже:
=TEXTJOIN(" ", ИСТИНА, A2:B2)
Чтобы исправить это, вам нужно преобразовать дату в текстовую строку, прежде чем присоединять ее. И здесь на помощь приходит функция ТЕКСТ с нужным кодом формата (в нашем случае «мм/дд/гггг»):
=TEXTJOIN(" ", TRUE, A2, TEXT(B2, "мм/дд/гггг"))
Объединить текст с разрывами строк
Если вы хотите объединить текст в Excel, чтобы каждое значение начиналось с новой строки, используйте CHAR(10) в качестве разделителя (где 10 – символ перевода строки).
Например, чтобы объединить текст из ячеек A2 и B2, разделив значения разрывом строки, используйте следующую формулу:
=TEXTJOIN(СИМВОЛ(10), ИСТИНА, A2:B2)
Совет. Чтобы результат отображался в несколько строк, как показано на снимке экрана выше, убедитесь, что функция переноса текста включена.
TEXTJOIN IF для объединения текста с условиями
Благодаря способности Excel TEXTJOIN обрабатывать массивы строк, его также можно использовать для условного объединения содержимого двух или более ячеек. Для этого используйте функцию ЕСЛИ, чтобы оценить диапазон ячеек и вернуть массив значений, соответствующих условию, в аргумент text1 функции TEXTJOIN.
Предположим, что из таблицы, показанной на снимке экрана ниже, вы хотите получить список участников Команды 1. Для этого вложите следующий оператор IF в аргумент text1:
Проще говоря, приведенная выше формула гласит: если столбец B равен 1, вернуть значение из столбца A в той же строке; в противном случае вернуть пустую строку.
Полная формула для Команды 1 выглядит следующим образом:
Аналогичным образом вы можете получить список членов Команды 2, разделенных запятыми:
Примечание. Благодаря функции динамических массивов, доступной в Excel 365 и 2021, это работает как обычная формула, показанная на снимке экрана выше. В Excel 2019 вы должны ввести его как традиционную формулу массива, нажав сочетание клавиш Ctrl + Shift + Enter.
Поиск и возврат нескольких совпадений в списке, разделенном запятыми
Как вы, наверное, знаете, функция ВПР Excel может возвращать только первое найденное совпадение. Но что, если вам нужно получить все совпадения для определенного идентификатора, артикула или чего-то еще?
Чтобы вывести результаты в отдельные ячейки, используйте одну из формул, описанных в статье Как выполнять ВПР нескольких значений в 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 (Адам в нашем случае):
Если логическая проверка дает ИСТИНА (т. е. имя в D2 совпадает с именем в столбце A), формула возвращает произведение из столбца B; в противном случае возвращается пустая строка (""). Результатом ЕСЛИ является следующий массив:
Массив передается функции TEXTJOIN в качестве аргумента text1. И поскольку TEXTJOIN настроен на разделение значений запятой и пробелом (","), мы получаем эту строку в качестве окончательного результата:
Бананы, яблоки, апельсины, лимоны
Excel TEXTJOIN не работает
Когда ваша формула TEXTJOIN приводит к ошибке, скорее всего, это одно из следующих:
Вот как использовать функцию ОБЪЕДИНЕНИЕ ТЕКСТ в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Итог: узнайте, как использовать формулы, функции и Power Query для объединения нескольких ячеек или столбцов в один столбец и добавления символов-разделителей.
Уровень квалификации: средний
Посмотреть обучающее видео
Загрузить файлы Excel
Я включил файлы "До" и "После", чтобы вы могли их загрузить и попрактиковаться, если хотите.
Объединить текст — BEGIN.xlsx
Объединить текст — FINAL.xlsx
Объединение текста из нескольких столбцов
Недавно мы искали разные способы брать информацию из одного столбца и разбивать ее на два или более столбцов. Мы использовали пример столбца полного имени, который мы хотели разделить на имя и фамилию. Мы узнали, как разделить столбцы с помощью Power Query, текста в столбцы, формул и быстрого заполнения.
Сегодняшнее руководство основано на комментариях профессора YC и Мохамада на нашем канале YouTube, которые спрашивают, можем ли мы сделать наоборот.
Это отличные вопросы, и я рад показать вам три разных способа их решения.
1. Формула с использованием амперсанда (&)
Совместимость: все версии Excel во всех операционных системах.
Первый способ объединить текст – использовать простую формулу. Чтобы соединить ячейки вместе, мы используем символ амперсанда (&). Объединение содержимого ячеек A2 и B2 будет выглядеть так: =A2&B2.
Но чтобы отделить имя от фамилии в выводе, мы используем символ пробела, заключенный в кавычки, и добавляем еще один амперсанд. Формула будет выглядеть так: =A2&», «&B2
».Возможно, вы бы предпочли, чтобы в окончательном выводе сначала была фамилия, затем запятая, а затем имя. Если это так, вы можете изменить свою формулу, чтобы изменить порядок ячеек и добавить запятую перед пробелом: =B2&», «&A2
2. Формула с использованием функции TEXTJOIN
Совместимость: Excel 2019 или более поздней версии, включая Microsoft 365, во всех операционных системах
Что делать, если у вас есть три столбца, но не во всех ячейках есть данные? Конечно, вы можете добавить еще одну ячейку в свою формулу с другим амперсандом, но каждый раз, когда у вас была пустая ячейка, в вашем выводе также будет дополнительный символ пробела. Чтобы избежать этого, вы можете использовать формулу с функцией ОБЪЕДИНЕНИЕ ТЕКСТ (доступно в версиях Excel 2019 и более поздних).
Функция TEXTJOIN имеет три аргумента.
- Первый аргумент — разделитель. Это символ (или строка символов), который должен отображаться между текстом в ваших ячейках. В нашем примере с именами мы хотим, чтобы они были разделены пробелом, поэтому мы набираем ” “.
- Второй аргумент предназначен для игнорирования пустых ячеек.Вы выбираете либо true, либо false, в зависимости от того, хотите ли вы, чтобы Excel игнорировал пустые ячейки. Так и есть, поэтому мы введем TRUE.
- Третий аргумент — текст. Это ячейки, которые вы хотите объединить. Вы можете выбрать их по отдельности или весь диапазон.
Все вместе наша формула записывается так: =TEXTJOIN(" ",TRUE,A2:C2)
3. Power Query
Совместимость: Excel 2010 или более поздней версии для Windows
Функция объединения столбцов Power Query — еще один отличный способ быстро объединить несколько столбцов и добавить символ-разделитель.
- Чтобы объединить содержимое ячеек с помощью Power Query, перейдите на вкладку "Данные" (вкладка "Power Query" для более ранних версий Excel).
- Выберите вариант «Из листа», «Из таблицы» или «Диапазон» (в зависимости от вашей версии). Это откроет предварительный просмотр ваших данных в редакторе Power Query.
- Выберите столбцы, которые вы хотите объединить.
- Затем выберите «Объединить столбцы» на вкладке «Добавить столбец». Откроется окно "Объединить столбцы".
- Выберите способ разделения текста из каждого столбца. В нашем случае нам нужен пробел между именами.
- В этом окне вы также можете назвать столбец.
- Нажмите "ОК".
- Если вы хотите обновить данные в одной из исходных ячеек, любые изменения, внесенные в отдельные ячейки, будут автоматически отражены в объединенной ячейке.
- Эти методы не ограничиваются только тремя ячейками. Формула работает с большими объемами данных, столбцов и строк, которые вы, возможно, захотите объединить.
- Используйте параметр "Вставить как текст", если вы хотите скопировать и вставить данные в другую электронную таблицу или рабочий лист. Это предотвратит изменение данных.
- Как объединить ячейки без пробела/разделителя между ними.
- Как объединить ячейки с пробелом/разделителем между ними.
- Как объединить ячейки с разрывами строк между ними.
- Как объединить ячейки с текстом и числами.
- Введите следующую формулу в ячейку, где вы хотите получить объединенную строку:
- Скопируйте и вставьте это во все ячейки.
- Введите в ячейку следующую формулу:
- Скопируйте и вставьте это во все ячейки.
В область предварительного просмотра будет добавлен новый столбец с объединенным текстом.
Power Query автоматически обнаруживает и пропускает любые пустые ячейки при объединении столбцов.
Если вместо этого вы хотите, чтобы в объединенном столбце формат был «Последний, первый», процесс аналогичен. Порядок, в котором вы выбираете столбцы перед выбором «Объединить столбцы», совпадает с порядком, в котором они будут отображаться в выходных данных. Вместо выбора «Пробел» в качестве разделителя выберите «Пользовательский», а затем введите запятую и пробел.
Когда вы нажмете OK, появится новый объединенный столбец с нужным форматом.
Если ваш предварительный просмотр выглядит так, как вы хотите, вы можете нажать верхнюю половину кнопки «Закрыть и загрузить», чтобы экспортировать его на лист.
Примечание. Этот процесс, который я описал, добавляет ваш новый столбец к существующим столбцам на выходном листе. Если вы хотите заменить их, просто выберите параметр «Объединить столбцы» на вкладке «Преобразование» вместо параметра на вкладке «Добавить столбец».
Если вы не знакомы с Power Query, вот руководство по установке Power Query, если вы используете более старую версию Excel. Посетите мой бесплатный веб-семинар ниже, чтобы узнать, как начать работу с Power Query и другими современными инструментами и функциями Excel.
Бесплатный веб-семинар по Power Query и современному Excel
Если вы хотите увидеть другое применение функции TEXTJOIN, ознакомьтесь с этим руководством: 3 способа отображения (несколько элементов) критериев фильтрации в сводной таблице
Надеюсь, вы многому научились и сможете применить эти знания! Я хочу помочь вам стать героем Excel на вашем рабочем месте. Если у вас есть комментарии или вопросы, оставьте их ниже.
Иногда мы работаем с большими листами в книге Excel. Это становится ужасной проблемой, когда вы хотите объединить все свои данные, поскольку большинство людей автоматически думают, что это будет означать повторный ввод всего этого текста. Теперь вы можете вздохнуть с облегчением, потому что при работе с данными в Excel стало проще объединять записи данных в разных ячейках, строках и даже столбцах.
Существуют разные способы добиться этого с помощью формулы Excel. Давайте рассмотрим некоторые методы.
Метод A: используйте функцию Concatenate для объединения ячеек
Оглавление
Функция Concatenate в Excel означает объединение или соединение вместе. Это та же функция, что и в таблицах Google. Функция проста и удобна, когда нужно объединить несколько ячеек в одну.
Это мощный инструмент Excel и удобный метод, поскольку он помогает сократить время, затрачиваемое на ввод повторяющихся данных. Вот руководство о том, что вам нужно сделать.
<р>1. Откройте таблицу. <р>2. Выберите предпочтительную ячейку на листе, которую вы хотите объединить.<р>3. Введите СЦЕПИТЬ. Помните, что, как и во всех формулах Excel, начинайте со знака = перед тем, как написать Concatenate. Затем используйте открывающую и закрывающую круглую скобку, введите расположение ячеек, которое указывает функциональным ячейкам объединиться. Например, = СЦЕПИТЬ (A2, B2, C2) или = СЦЕПИТЬ (AA, BB, CC)
<р>4. Настройте формулу, чтобы включить в нее все необходимые знаки препинания, такие как разделители, кавычки, чтобы добавить пробелы, запятые или другой текст. Например, =СЦЕПИТЬ(A2, ", ", B2, " ", C2)
<р>5. Когда закончите, закройте формулу и нажмите Enter.Способ Б. Объедините данные с символом амперсанда (&)
<р>1. Выберите предпочитаемую ячейку
<р>2. Введите знак = и выберите первую ячейку, которую хотите объединить. Например, B2
<р>3. Введите кавычки с заключенным пробелом. Например, B2&."
<р>4. Выберите вторую ячейку, которую вы хотите объединить, и нажмите клавишу ВВОД. Ваша формула будет выглядеть так =A2&" "&B2&" &C2
Способ C. Используйте функцию соединения текста для объединения ячеек
Этот метод в основном используется в Excel 365 и более поздних версиях, поскольку это новая функция.
<р>1. Откройте таблицу. <р>2. Выберите ячейку, чтобы поместить объединенные данные.<р>3. Введите =TEXTJOIN
<р>4. Выберите ячейки, которые вы хотите объединить в одну ячейку.
<р>5. Не забудьте включить разделители. Ваша функция будет выглядеть так =TEXTJOIN(", ", TRUE,A2:C2)
Вывод
Из статьи выше мы познакомились с различными способами объединения нескольких ячеек, в нашем случае 3, в одну ячейку. Выбор любого из них приведет к одинаковым результатам и поможет уменьшить дублирование.
Часто нам приходится работать с текстовыми данными в Excel. Это могут быть имена, адреса, идентификаторы электронной почты или другие текстовые строки. Часто возникает необходимость объединить ячейки в Excel, содержащие текстовые данные.
Ваши данные могут находиться в соседних ячейках (строках/столбцах) или далеко друг от друга на том же листе или даже на другом листе.
Как объединить ячейки в Excel
В этом руководстве вы узнаете, как объединять ячейки в Excel в различных сценариях:
Как объединить ячейки без пробела/разделителя
Это самый простой и, вероятно, наиболее часто используемый способ объединения ячеек в Excel. Например, предположим, что у вас есть набор данных, как показано ниже:
Вы можете легко объединить ячейки в столбцах A и B, чтобы получить строку, такую как A11, A12 и т. д.
Вот как это можно сделать:
Это даст вам то, что показано ниже:
То же самое можно сделать с помощью функции СЦЕПИТЬ вместо амперсанда (&). Приведенная ниже формула даст тот же результат:
Как объединить ячейки с пробелом/разделителем между ними
Вы также можете комбинировать ячейки и использовать между ними указанный разделитель. Это может быть пробел, запятая или любой другой разделитель.
Предположим, у нас есть набор данных, как показано ниже:
Вот шаги, чтобы объединить имя и фамилию с пробелом между ними:
Это объединит имя и фамилию с пробелом между ними.
Если вам нужен любой другой разделитель (например, запятая или точка), вы можете использовать его в формуле.
Как объединить ячейки с разрывами строк между ними
Помимо разделителей, вы также можете добавлять разрывы строк при объединении ячеек в Excel.
Предположим, у вас есть набор данных, как показано ниже:
Это неправильный формат адреса. Вы можете попробовать использовать обтекание текстом, но это тоже не сработает.
Здесь необходимо, чтобы каждый элемент адреса располагался на отдельной строке в одной и той же ячейке. Этого можно добиться, используя функцию CHAR(10) вместе со знаком &.
CHAR(10) – это перевод строки в Windows. Это означает, что все, что следует после символа, переходит на новую строку.
Используйте приведенную ниже формулу, чтобы получить содержимое каждой ячейки на отдельной строке в той же ячейке:
Эта формула использует функцию CHAR(10) между каждой ссылкой на ячейку и вставляет разрыв строки после каждой ячейки. Получив результат, примените обтекание текстом к ячейкам, в которых есть результаты, и вы получите что-то, как показано ниже:
Как объединить ячейки с текстом и числами
Вы также можете комбинировать ячейки, содержащие разные типы данных. Например, вы можете комбинировать ячейки, содержащие текст и числа.
Сначала рассмотрим простой пример.
Предположим, у вас есть набор данных, как показано ниже:
Приведенный выше набор данных содержит текстовые данные в одной ячейке, а число — в другой ячейке. Вы можете легко комбинировать эти два параметра, используя приведенную ниже формулу:
Здесь я использовал тире в качестве разделителя.
Вы также можете добавить к нему текст. Таким образом, вы можете использовать следующую формулу для создания предложения:
Здесь мы использовали комбинацию ссылки на ячейку и текста для построения предложений.
Теперь давайте рассмотрим этот пример и посмотрим, что произойдет, если вы попытаетесь использовать числа с некоторым форматированием.
Предположим, у вас есть набор данных, как показано ниже, в котором у нас есть значения продаж.
Теперь давайте объединим ячейки в столбцах A и B, чтобы составить предложение.
Вот формула, которую я буду использовать:
Вот как выглядят результаты:
Вы видите здесь проблему? Внимательно посмотрите на формат значения продаж в результате.
Вы можете видеть, что форматирование значения продаж исчезло, и результат имеет простое числовое значение. Это происходит, когда мы объединяем ячейки с числами, к которым применено форматирование.
Вот как это исправить. Используйте приведенную ниже формулу:
Аналогичным образом вы можете использовать функцию "Текст" для отображения в любом формате, допустимом в Excel.
Вот еще один пример. Есть имена и дата рождения, и если вы попытаетесь объединить эти ячейки, вы получите что-то вроде того, что показано ниже:
Вы можете видеть, что Excel полностью искажает формат даты. Причина в том, что дата и время хранятся в Excel в виде чисел, и когда вы объединяете ячейки с числами, как показано выше, отображается числовое значение, но не используется исходный формат.
Вот формула, которая это исправит:
Опять же, здесь мы использовали функцию ТЕКСТ и указали формат, в котором мы хотим, чтобы дата рождения отображалась в результате.
Читайте также: