Соединить значения ячеек в excel

Как объединить текст ячеек в Excel

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

Как объединить текст (значения) ячеек при помощи формул?

Стандартное средство Excel для объединения значений нескольких ячеек в одной ячейке — это функция «СЦЕПИТЬ», позволяющая объединять до 255 текстовых значений в одно. Объединяемые элементы могут быть как текстовыми, так и числовыми, а также ссылками на ячейки или сочетанием этих элементов. Функция «СЦЕПИТЬ» находится в категории «Текстовые», вызвать ее можно как через мастер функций, так и через вкладку «Формулы», которая находится на ленте Excel 2007 и выше.

Если ячейка A1 листа содержит имя человека, в ячейке B1 содержится его отчество, а в ячейке C1 — фамилия, объединить эти три значения в отдельной ячейке можно с помощью следующей формулы:

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

возвращает то же значение, что и вышеописанная формула.

Объединение текста (значений) разных ячеек Excel макросом

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

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

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

Надстройка для объединения текста из разных ячеек

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

1) объединять значения по строкам;

2) объединять значения по столбцам;

3) объединять значения по произвольным диапазонам;

4) создавать интервалы между ячейками;

5) устанавливать знак-разделитель;

6) изменять направление объединения значений ячеек:

а) сначала слева направо, затем сверху вниз;

б) сначала сверху вниз, затем слева направо.

Видео по объединению значений в ячейках Excel

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

Как объединить ячейки в Excel

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

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

Вы можете объединять ячейки как по горизонтали, так и по вертикали. Также можно объединить области, содержащие боле одной строки и столбца.

Способы объединения ячеек

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

• Объединить по строкам — позволяет выбрать диапазон ячеек, который содержит несколько строк. В этом случае Excel будет создавать объединенные ячейки по одной на каждой строке
• Объединить ячейки — объединяет выделенные ячейки в одну, но при этом не выравнивает текст в ячейке.
• Отменить объединение ячеек — позволяет разбить объединенную ячейку на несколько.

Какие проблемы возникают при использовании объединенных ячеек

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

• Если диапазон содержит объединенные ячейки, то пользоваться сортировкой и фильтрацией в этом диапазоне будет невозможно.
• Также невозможно будет преобразовать такой диапазон в таблицу (форматировать как таблицу).
• Также можно забыть об автоматическом выравнивании ширины или высоты ячейки. Например если имеется объединенная ячейка A1:B1, то выравнять ширину столбца A уже не получится.
• Если Вы пользуетесь горячими клавишами для навигации, например переходите в начало и конец таблицы путем сочетания клавиш Ctrl + стрелка вверх и вниз, то переход не удастся, и курсор будет «упираться» в объединенные ячейки.
• Если вы выделяете столбцы (или строки) с помощью горячих клавиш Ctrl (Shift) +Пробел, то при наличии объединенных ячеек, вы не сможете выделить 1 столбец (или строку).

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

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

1. Выделить все ячейки на листе. Это можно сделать путем нажатия сочетания клавиш Ctrl + A или кликнуть на черный треугольник между заголовками строк и столбцов на листе.
2. Нажать на команду Главная -> Выравнивание -> Объединить и поместить в центре если она выделена. Если же она не выделена, значит выбранный лист не содержит объединенных ячеек.

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

1. Откройте окно Найти и заменить. Сделать это можно сочетанием клавиш Ctrl + F.
2. Поле Найти оставьте пустым и нажмите на кнопку Параметры.
3. Должна отобразиться кнопка Формат. Нажмите левой кнопкой мыши по ней.
   
4. В диалоговом окне Найти формат на вкладке Выравнивание выберите опцию Объединение ячеек. Далее нажмите Ok.
   
5. В диалоговом окне Найти и заменить нажмите Найти далее или Найти все в зависимости от вашей дальнейшей задачи.

Альтернативы использования объединенных ячеек

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

1. Выделите диапазон ячеек, который хотите отцентрировать. При этом сам текст должен содержаться в левой верхней ячейке.
2. Выберите команду формат ячеек или нажмите сочетание клавиш Ctrl + 1.
3. В диалоговом окне Формат ячеек перейдите на вкладку Выравнивание.
4. В выпадающем списке выравнивания по горизонтали выберите значение по центру выделения и нажмите OK.
   

Еще один способ избежать объединения ячеек — это использовать надписи. В них также можно вставлять текст и выбирать направление текста по вертикали. Способ конечно также накладывает много ограничений, но в каких-то случаях имеет место быть.

Все способы можно скачать нажав кнопку вначале статьи.

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

Объединение текста нескольких ячеек в одну – базовая функция при работе с табличными данными, с ее п.

Объединение текста нескольких ячеек в одну – базовая функция при работе с табличными данными, с ее помощью содержание произвольного количества полей можно сцеплять нужным образом и размещать в свободных ячейках. Она используется при составлении пофамильных списков сотрудников, отчетов различных направлений и других операциях с большими массивами информации. Для объединения ячеек есть два способа: с потерей данных и без потери. В первом случае в объединенной ячейке остается только одно значение, во втором – сохраняется весь текст. Второй способ предполагает как минимум четыре варианта объединения: с использованием двух функций («Сцеп-сцепить» и «Объединить»), при помощи символа амперсанд и с применением макроса. В статье подробно разберемся, как в Excel объединить текст нескольких ячеек в одну.

С помощью функции

Excel предусматривает несколько функций для объединения ячеек. Для начала, расскажем о самой простой и «теряющей» информацию. Если интересует, как в Экселе соединить текст из двух ячеек в одну, оставив только одно значение – то вам понадобится функция «объединить» в разделе «Формат ячейки».

1. Выделите мышкой ячейки, которые нужно объединить.
2. По образовавшемуся полю щелкните правой кнопкой мыши.
3. Выберите пункт «Формат ячеек».
4. В появившемся окне зайдите в раздел «Выравнивание».
5. Отметьте галочкой пункт «Объединить ячейки» и нажмите «Ок». Выделенные поля объединяться в одну большую с сохранением содержания первой ячейки.

Также эта функция есть в графе «Главная» в разделе «Выравнивание» в верхней части экрана.

С помощью функции «Сцеп-сцепить»

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

1. Щелкните по пустой ячейке в Excel.
2. Поставьте знак «=» и большими буквами введите «Сцепить» либо «Сцеп».
3. В скобках после формулы кликните по ячейке, которая должна быть первой в объединенной ячейке, либо введите ее адрес вручную (например, A1). Затем поставьте точку с запятой, после щелкните или напишите вторую ячейку. Затем поставьте точку с запятой и отметьте третье поле. После перечисления адресов нажмите «Enter». В поле, где вбивали формулу, появится объединенный текст. Он появится без пробелов между текстовыми блоками. Чтобы этого избежать, отбейте пробел в каждой отдельно взятой ячейке после находящегося текста, либо после адреса ячейки в формуле добавьте пробел вручную, написав « » (пробел в кавычках). Пробел в формуле также отделяется от остальных элементов точкой с запятой.

Как в Экселе соединить текст из двух ячеек в одну с помощью функции «Объединить»

«Объединить» — усовершенствованная вариация функции «Сцеп». Ее отличие в том, что параметры вводятся единожды на этапе написания функции. В частности, разделитель придется указать один раз для содержимого всех ячеек. Для того чтобы воспользоваться функцией «Объединить» нужно выполнить действия:

1. В пустой ячейке поставьте знак «=» и напишите большими буквами «Объединить».
2. В скобках после формулы укажите разделитель в кавычках (« » ,«,»,« и »), затем поставьте точку с запятой. Второй параметр формулы – это игнорирование пустых полей. Если они не должны учитываться в формуле, напишите «1», в ряде версий этот параметр обозначается словом «Истина» или «Ложь». Затем перечислите через запятую ячейки, которые нужно объединить, и нажмите «Enter».

В итоге формула должна иметь вид: «=ОБЪЕДИНИТЬ(« »;ИСТИНА; «A1»; «A2»; «A3»).

Если в A1 – «Иванов», в А2 – «Иван», в А3 – «Иванович», то после нажатия «Enter» в пустой ячейке появится «Иванов Иван Иванович».

С помощью символа «&»

Четвертый способ объединить ячейки – использовать амперсанд ( «&»). Для этого:

1. В пустой ячейке наберите «=».
2. Напишите нужную ячейку, например, А1, затем поставьте знак «&», потом в кавычках введите разделитель. Это может быть пробел или запятая, он также помещается в кавычки, потом введите вторую ячейку, которую нужно объединить. Если их больше двух, пробел выставите после каждой нового поля. Формула должна выглядеть так: «=А1&« »&А2& « »&А3». После нажатия «Enter» содержимое ячеек объединится.

С помощью макроса

Макрос в Excel – это сервис для автоматизации повторяющихся действий, он представляет собой записанную последовательность примененных функций и щелчков мышью для последующего воспроизводства. Используется, если нужно регулярно выполнять типовые задачи, в которых меняются данные, но не структура. Например, макросы используются при составлении ежемесячных отчетов о заработной плате, в которых меняются только цифры, но не метод расчета. Тогда макрос избавит пользователя от необходимости регулярно вручную настраивать и использовать формулы.

Чтобы записать макрос:

1. В разделе «Разработчик» в графе «Код» нажмите на клавишу «Записать макрос».
2. В появившемся окне выберите имя макроса, чтобы потом было удобнее его искать, присвойте ему сочетание клавиш и место хранения, добавьте описание.
3. Нажмите «Ок», затем точно и без ошибок (потому что макрос записывает и ошибки тоже) проделайте алгоритм.
4. Вернитесь во вкладку «Разработчик» и «Код» и нажмите «Остановить запись».

Макрос записывается в виде кода Visual Basic для приложений на одноименном языке программирования, который доступен для редактирования при нажатии комбинации ALT + F11. При ручном редактировании становится доступным правка записанного макроса, однако это требует дополнительных знаний и недоступно для рядового пользователя. Также макросы способны работать в нескольких продуктах Microsoft Office одновременно. Выполняется макрос нажатием присвоенной комбинации либо через окно «Макрос», которое вызывается комбинацией ALT + F8.

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

Надпись на заборе: «Катя + Миша + Семён + Юра + Дмитрий Васильевич +
товарищ Никитин + рыжий сантехник + Витенька + телемастер Жора +
сволочь Редулов + не вспомнить имени, длинноволосый такой +
ещё 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) и скопируем туда текст такого простого макроса:

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