Функция объединения ячеек excel функция: Объединение текста из двух или нескольких ячеек в одну

Содержание

Объединение текста из двух или нескольких ячеек в одну


Использование функций


Справка и обучение по Excel


Использование функций




Использование функций



Объединение текста из двух или нескольких ячеек в одну



  • Автоматическая нумерация строк




    Статья



  • Вычисление разности двух дат




    Статья



  • Определение и использование имен в формулах




    Статья



  • Объединение текста из двух или нескольких ячеек в одну




    Статья


Далее:


Таблицы

Данные из нескольких ячеек можно объединить в одну с помощью символа «амперсанд» (&) или функции СЦЕП.


Объединение данных с помощью символа «амперсанд» (&)


  1. Выделите ячейку, в которую вы хотите вставить объединенные данные.

  2. Введите = (знак равенства) и выберите первую ячейку, которую нужно объединить.

  3. Введите символ & и пробел, заключенный в кавычки.

  4. Выберите следующую ячейку, которую нужно объединить, и нажмите клавишу ВВОД. Пример формулы: =A2&» «&B2.

Объединение данных с помощью функции СЦЕП


  1. Выделите ячейку, в которую вы хотите вставить объединенные данные.

  2. Введите выражение = СЦЕП(.

  3. Выберите первую ячейку, которую нужно объединить.

    Для разделения объединяемых ячеек используйте запятые, а для добавления пробелов, запятых и другого текста — кавычки.

  4. Введите закрывающую скобку в конце формулы и нажмите клавишу ВВОД. Пример формулы: =CONCAT(A2, » Семья»).

Дополнительные сведения




См. также



Функция ОБЪЕДИНИТЬ


Функция CONCAT


Объединение и отмена объединения ячеек


СЦЕПИТЬ


Рекомендации, позволяющие избежать появления неработающих формул


Автоматическая нумерация строк


3 способа склеить текст из нескольких ячеек

21250
28.10.2012
Скачать пример


Надпись на заборе: «Катя + Миша + Семён + Юра + Дмитрий Васильевич +

товарищ Никитин + рыжий сантехник + Витенька + телемастер Жора +

сволочь Редулов + не вспомнить имени, длинноволосый такой +

ещё 19 мужиков + муж = любовь!»

Способ 1.

Функции СЦЕПИТЬ, СЦЕП и ОБЪЕДИНИТЬ


В категории Текстовые есть функция СЦЕПИТЬ (CONCATENATE), которая соединяет содержимое нескольких ячеек (до 255) в одно целое, позволяя комбинировать их с произвольным текстом. Например, вот так:



Нюанс: не забудьте о пробелах между словами — их надо прописывать как отдельные аргументы и заключать в скобки, ибо текст. 


Очевидно, что если нужно собрать много фрагментов, то использовать эту функцию уже не очень удобно, т.к. придется прописывать ссылки на каждую ячейку-фрагмент по отдельности. Поэтому, начиная с 2016 версии Excel, на замену функции СЦЕПИТЬ пришла ее более совершенная версия с похожим названием и тем же синтаксисом — функция СЦЕП (CONCAT). Ее принципиальное отличие в том, что теперь в качестве аргументов можно задавать не одиночные ячейки, а целые диапазоны — текст из всех ячеек всех диапазонов будет объединен в одно целое:



Для массового объединения также удобно использовать новую функцию ОБЪЕДИНИТЬ (TEXTJOIN), появившуюся начиная с Excel 2016. У нее следующий синтаксис:


=ОБЪЕДИНИТЬ(Разделитель; Пропускать_ли_пустые_ячейки; Диапазон1; Диапазон2 … )


где

  • Разделитель — символ, который будет вставлен между фрагментами

  • Второй аргумент отвечает за то, нужно ли игнорировать пустые ячейки (ИСТИНА или ЛОЖЬ)

  • Диапазон 1, 2, 3 … — диапазоны ячеек, содержимое которых хотим склеить


Например:


Способ 2. Символ для склеивания текста (&)


Это универсальный и компактный способ сцепки, работающий абсолютно во всех версиях Excel. 


Для суммирования содержимого нескольких ячеек используют знак плюс «+«, а для склеивания содержимого ячеек используют знак «&» (расположен на большинстве клавиатур на цифре «7»). При его использовании необходимо помнить, что:

  • Этот символ надо ставить в каждой точке соединения, т. е. на всех «стыках» текстовых строк также, как вы ставите несколько плюсов при сложении нескольких чисел (2+8+6+4+8)

  • Если нужно приклеить произвольный текст (даже если это всего лишь точка или пробел, не говоря уж о целом слове), то этот текст надо заключать в кавычки. В предыдущем примере с функцией СЦЕПИТЬ о кавычках заботится сам Excel — в этом же случае их надо ставить вручную.


Вот, например, как можно собрать ФИО в одну ячейку из трех с добавлением пробелов:



Если сочетать это с функцией извлечения из текста первых букв — ЛЕВСИМВ (LEFT), то можно получить фамилию с инициалами одной формулой:


Способ 3. Макрос для объединения ячеек без потери текста.


Имеем текст в нескольких ячейках и желание — объединить эти ячейки в одну, слив туда же их текст. Проблема в одном — кнопка Объединить и поместить в центре (Merge and Center) в Excel объединять-то ячейки умеет, а вот с текстом сложность — в живых остается только текст из верхней левой ячейки.  


Чтобы объединение ячеек происходило с объединением текста (как в таблицах Word) придется использовать макрос. Для этого откройте редактор Visual Basic  на вкладке Разработчик — Visual Basic (Developer — Visual Basic) или сочетанием клавиш Alt+F11, вставим в нашу книгу новый программный модуль (меню Insert — Module) и скопируем туда текст такого простого макроса: 

Sub MergeToOneCell()
    Const sDELIM As String = " "     'символ-разделитель
    Dim rCell As Range
    Dim sMergeStr As String
    If TypeName(Selection) <> "Range" Then Exit Sub   'если выделены не ячейки - выходим
    With Selection
        For Each rCell In .Cells
            sMergeStr = sMergeStr & sDELIM & rCell.Text  'собираем текст из ячеек
        Next rCell
        Application.DisplayAlerts = False   'отключаем стандартное предупреждение о потере текста
        .Merge Across:=False                'объединяем ячейки
        Application. DisplayAlerts = True
        .Item(1).Value = Mid(sMergeStr, 1 + Len(sDELIM))    'добавляем к объед.ячейке суммарный текст
    End With
End Sub


Теперь, если выделить несколько ячеек и запустить этот макрос с помощью сочетания клавиш Alt+F8 или кнопкой Макросы на вкладке Разработчик (Developer — Macros), то Excel объединит выделенные ячейки в одну, слив туда же и текст через пробелы.

Ссылки по теме

  • Делим текст на куски

  • Объединение нескольких ячеек в одну с сохранением текста с помощью надстройки PLEX

  • Что такое макросы, как их использовать, куда вставлять код макроса на VBA


 

Объединить текст из двух и более ячеек в одну ячейку

Обычное использование функций

  • Автоматически нумеровать строки

    Статья

  • Вычислить разницу между двумя датами

    Статья

  • Определение и использование имен в формулах

    Статья

  • Объединить текст из двух или более ячеек в одну ячейку

    Статья

Следующий:

Столы

Обычное использование функций

Справка по Excel и обучение

Обычное использование функций

Обычное использование функций

Объединить текст из двух или более ячеек в одну ячейку

  • Автоматически нумеровать строки

    Статья

  • Вычислить разницу между двумя датами

    Статья

  • Определение и использование имен в формулах

    Статья

  • Объединить текст из двух или более ячеек в одну ячейку

    Статья

Следующий:

Столы

Вы можете объединить данные из нескольких ячеек в одну ячейку, используя символ амперсанда (&) или функцию СЦЕП.

Объединить данные с символом амперсанда (&)

  1. Выберите ячейку, в которую вы хотите поместить объединенные данные.

  2. Введите = и выберите первую ячейку, которую нужно объединить.

  3. Введите и и используйте кавычки с пробелом.

  4. Выберите следующую ячейку, которую хотите объединить, и нажмите клавишу ввода. Примером формулы может быть  =A2&» «&B2 .

Объединение данных с помощью функции CONCAT

    org/ItemList»>

  1. Выберите ячейку, в которую вы хотите поместить объединенные данные.

  2. Тип =CONCAT( .

  3. Сначала выберите ячейку, которую хотите объединить.

    Используйте запятые для разделения объединяемых ячеек и используйте кавычки для добавления пробелов, запятых или другого текста.

  4. Закройте формулу скобками и нажмите Enter. Примером формулы может быть =CONCAT(A2, «Family») .

Нужна дополнительная помощь?

См.

также

функция ТЕКСТОВОЕ СОЕДИНЕНИЕ

СЦЕПНАЯ функция

Объединение и разделение ячеек

СЦЕПИТЬ функцию

Как избежать неработающих формул

Автоматически нумеровать строки

Объединение и объединение ячеек в Excel без потери данных

В этом руководстве демонстрируются различные методы быстрого объединения двух ячеек в Excel и объединения нескольких ячеек построчно или столбец за столбцом без потери данных в Excel 365, Excel 2021, 2019, 2016, 2013, 2010 и ниже.

В листах Excel вам часто может понадобиться объединить две или более ячеек в одну большую ячейку. Например, вы можете объединить несколько ячеек для лучшего представления или структуры данных. В других случаях может быть слишком много содержимого для отображения в одной ячейке, и вы решаете объединить его с соседними пустыми ячейками.

Какова бы ни была причина, объединение ячеек в Excel не так просто, как может показаться. Если по крайней мере две ячейки, которые вы пытаетесь соединить, содержат данные, стандартная функция объединения ячеек Excel сохранит только значение верхней левой ячейки и отбросит значения в других ячейках.

Но есть ли способ объединить ячейки в Excel без потери данных? Есть конечно. Далее в этом руководстве вы найдете несколько решений, которые работают во всех версиях Excel 2016, Excel 2013, Excel 2010 и более ранних версиях.

Объединение ячеек с помощью функции слияния и центрирования Excel

Самый быстрый и простой способ объединить две или более ячеек в Excel — использовать встроенный параметр Объединить и центрировать . Весь процесс занимает всего 2 быстрых шага:

  1. Выберите смежные ячейки, которые вы хотите объединить.
  2. На вкладке Главная > в группе Выравнивание щелкните Объединить и центрировать

В этом примере у нас есть список фруктов в ячейке A1, и мы хотим объединить его с парой пустых ячеек справа (B2 и C2), чтобы создать большую ячейку, вмещающую весь список.

После того, как вы нажмете Объединить и центрировать , выбранные ячейки будут объединены в одну ячейку, а текст будет центрирован, как показано на следующем снимке экрана:

Другие параметры слияния в Excel

Чтобы получить доступ к еще нескольким параметрам слияния, предоставляемым Excel, щелкните маленькую стрелку раскрывающегося списка рядом с кнопкой Объединить и центрировать и выберите нужный параметр в раскрывающемся меню:

Объединить через — объединить выделенные ячейки в каждой строке по отдельности:

Объединить ячейки — объединить выделенные ячейки в одну ячейку без центрирования текста:

Совет. Чтобы изменить выравнивание текста после слияния, просто выберите объединенную ячейку и щелкните нужное выравнивание в группе Alignment на вкладке Home .

Возможности слияния Excel — ограничения и особенности

При использовании встроенных функций Excel для объединения ячеек следует помнить о нескольких вещах:

  1. Убедитесь, что все данные, которые вы хотите включить в объединенную ячейку, введены в крайнюю левую ячейку выбранного диапазона, поскольку после объединения останется только содержимое верхней левой ячейки, данные во всех остальных ячейках будет удален. Если вы хотите объединить две или более ячеек с данными в них, ознакомьтесь с разделом Как объединить ячейки без потери данных.
  2. Если кнопка Объединить и центрировать неактивна, скорее всего, выбранные ячейки находятся в Режим редактирования . Нажмите клавишу Enter , чтобы отменить режим Edit , а затем попробуйте объединить ячейки.
  3. Ни один из стандартных параметров объединения Excel не работает для ячеек внутри таблицы Excel. Сначала вы должны преобразовать таблицу в обычный диапазон (щелкните правой кнопкой мыши таблицу и выберите Таблица > Преобразовать в диапазон из контекстного меню), а затем объедините ячейки.
  4. Невозможно отсортировать диапазон, содержащий как объединенные, так и не объединенные ячейки.

Как объединить ячейки в Excel без потери данных

Как уже упоминалось, стандартные функции слияния Excel сохраняют содержимое только верхней левой ячейки. И хотя Microsoft сделала довольно много улучшений в последних версиях Excel, функционал Merge Cells, похоже, ускользнул от их внимания и это критическое ограничение сохраняется даже в Excel 2013 и Excel 2016. Ну, а там, где нет очевидного способа , есть обходной путь 🙂

Метод 1. Объединение ячеек в одном столбце (функция «По ширине»)

Это быстрый и простой способ объединения ячеек с сохранением всего их содержимого. Однако для этого требуется, чтобы все объединяемые ячейки находились в одной области в одном столбце.

  1. Выберите все ячейки, которые вы хотите объединить.
  2. Сделайте столбец достаточно широким, чтобы вместить содержимое всех ячеек.
  3. На вкладке Главная в группе Редактирование щелкните Заполнить > Выровнять по ширине . Это переместит содержимое выбранных ячеек в самую верхнюю ячейку.
  4. Нажмите Объединить и центрировать или Объединить ячейки , в зависимости от того, хотите ли вы, чтобы объединенный текст был центрирован или нет.

Если объединенные значения распределены по двум или более строкам, сделайте столбец немного шире и повторите процесс.

Этот метод слияния прост в использовании, однако имеет ряд ограничений:

  • Используя Justify , вы можете соединять ячейки только в одном столбце.
  • Работает только для текста, числовые значения или формулы не могут быть объединены таким образом.
  • Не работает, если между объединяемыми ячейками есть пустые ячейки.
Способ 2. Объединить несколько ячеек с данными в любом диапазоне (надстройка «Объединить ячейки»)

Чтобы иметь возможность объединить две и более ячеек в Excel без потери данных и без лишних «хитростей», мы создали специальный инструмент — Объединить ячейки для Excel.

С помощью этой надстройки вы можете быстро объединить несколько ячеек, содержащих любые типы данных, включая текст, числа, даты и специальные символы. Кроме того, вы можете разделить значения любым разделителем по вашему выбору, таким как запятая, пробел, косая черта или разрыв строки.

Чтобы объединить ячейки именно так, как вы хотите, настройте следующие параметры:

  • Выберите Ячейки в одну в разделе « Что объединить «.
  • Выберите разделитель в разделе « Разделяйте значения с помощью «.
  • Укажите ячейку, в которую вы хотите поместить результат : вверху слева, вверху справа, внизу слева или внизу справа.
  • Убедитесь, что выбран параметр Объединить все области в выборе . Если этот флажок не установлен, надстройка будет работать как функция Excel СЦЕПИТЬ, т.е. объединять значения без объединения ячеек.

Помимо объединения всех ячеек в выбранном диапазоне, этот инструмент также может объединять строки и объединять столбцы , вам просто нужно выбрать соответствующий параметр в раскрывающемся списке « Что объединить ».

Чтобы попробовать надстройку «Объединить ячейки», загрузите ознакомительную версию для Excel 2016–365.

Способ 3. Используйте функцию СЦЕПИТЬ или СЦЕПИТЬ для объединения двух или нескольких ячеек

Пользователям, которым удобнее работать с формулами Excel, может понравиться этот способ объединения ячеек в Excel. Вы можете использовать функцию СЦЕПИТЬ или оператор &, чтобы сначала соединить значения ячеек, а затем при необходимости объединить ячейки. В Excel 2016 — Excel 365 вы также можете использовать функцию CONCAT для той же цели. Подробные шаги следуют ниже.

Предположим, вы хотите объединить две ячейки на листе Excel, A2 и B2, и в обеих ячейках есть данные. Чтобы не потерять значение во второй ячейке при объединении, соедините две ячейки, используя одну из следующих формул:

= СЦЕПИТЬ (A2,", ",B2)

=A2&", "&B2

Однако формула вставляет объединенные значения в другую ячейку. Если вам нужно объединить две ячейки с исходными данными, A2 и B2 в этом примере, то потребуется несколько дополнительных шагов:

  • Скопируйте ячейку с формулой СЦЕПИТЬ (D2).
  • Вставьте скопированное значение в верхнюю левую ячейку диапазона, который вы хотите объединить (A2). Для этого щелкните правой кнопкой мыши ячейку и выберите Специальная вставка > Значения из контекстного меню.
  • Выберите ячейки, которые вы хотите соединить (A2 и B2), и нажмите Объединить и центрировать .

Аналогичным образом вы можете объединить несколько ячеек в Excel, формула СЦЕПИТЬ в этом случае будет немного длиннее. Преимущество такого подхода в том, что в одной формуле можно разделить значения разными разделителями, например:

.

=СЦЕПИТЬ(A2, ": ", B2, ", ", C2)

Дополнительные примеры формул можно найти в следующих руководствах:

  • СЦЕПИТЬ в Excel: объединить текстовые строки, ячейки и столбцы
  • Как использовать функцию CONCAT для соединения строк

Ярлык для объединения ячеек в Excel

Если вы регулярно объединяете ячейки на листах Excel, вам может пригодиться следующий ярлык «Объединить ячейки» .

  1. Выберите ячейки, которые вы хотите объединить.
  2. Нажмите клавишу Alt, которая обеспечивает доступ к командам на ленте Excel, и удерживайте ее, пока не появится наложение.
  3. Нажмите H, чтобы выбрать вкладку Home .
  4. Нажмите M, чтобы переключиться на Объединить и центрировать .
  5. Нажмите одну из следующих клавиш:
    • C для объединения и центрирования выбранных ячеек
    • A для объединения ячеек в каждой отдельной строке
    • M для объединения ячеек без центрирования

На первый взгляд, ярлык слияния кажется немного многословным, но немного потренировавшись, вы сможете найти этот способ объединения ячеек быстрее, чем нажатие кнопки 9.0119 Объединить и центрировать кнопку мыши.

Как быстро найти объединенные ячейки

Чтобы найти объединенные ячейки на листе Excel, выполните следующие действия:

  1. Нажмите Ctrl + F, чтобы открыть диалоговое окно Найти и заменить , или щелкните Найти и выбрать > Найти .
  2. На вкладке Найти щелкните Параметры > Формат .
  3. На вкладке Выравнивание выберите Объедините ячейки в поле Текстовый элемент управления и нажмите OK .
  4. Наконец, нажмите либо Найти далее , чтобы выбрать следующую объединенную ячейку, либо Найти все , чтобы найти все объединенные ячейки на листе. Если вы выберете последнее, Microsoft Excel отобразит список всех найденных объединенных ячеек и позволит вам перемещаться между ними, выбрав одну из объединенных ячеек в этом списке:

Как разъединить ячейки в Excel

Если вы передумали сразу после объединения ячеек, вы можете быстро разъединить их, нажав сочетание клавиш Ctrl + Z или нажав кнопку Кнопка отмены на панели быстрого доступа.

Чтобы разделить ранее объединенную ячейку, выберите эту ячейку и щелкните Объединить и центрировать или щелкните маленькую стрелку рядом с Объединить и центрировать и выберите Разъединить ячейки :

После разделения ячеек все содержимое появится в верхней левой ячейке.

Дополнительные сведения о том, как быстро разъединить ячейки в Excel, см. в этой статье.

Альтернативы объединению ячеек в Excel

Само собой разумеется, что объединенные ячейки могут помочь представить информацию на ваших листах Excel в лучшем и более значимом виде… но они порождают множество побочных эффектов, о которых вы можете даже не подозревать. Вот лишь несколько примеров:

  • Невозможно отсортировать столбец с объединенными ячейками.
  • Ни автозаполнение, ни Flash-заполнение не работают, если диапазон ячеек, которые необходимо заполнить, содержит объединенные ячейки.
  • Вы не можете превратить диапазон, содержащий хотя бы одну объединенную ячейку, в полноценную таблицу Excel, не говоря уже о сводной таблице.

Итак, я бы посоветовал дважды подумать, прежде чем объединять ячейки в Excel, и делать это только тогда, когда это действительно необходимо для презентации или аналогичных целей, например. чтобы центрировать заголовок таблицы по таблице.

Читайте также: