Как использовать функцию excel concatenate для объединения ячеек

Используем новые функции СЦЕП и ОБЪЕДИНИТЬ.

Долгое время СЦЕПИТЬ была первой функцией, о которой мы думали, когда нам нужно было соединить текст в Excel. Но несколько новых игроков недавно вошли в игру. Я говорю о ОБЪЕДИНИТЬ (или TEXTJOIN) и СЦЕП ( или CONCAT), разработанных, чтобы помочь вам интегрировать несколько значений. Используйте эти простые функции всякий раз, когда вам нужно сложить вместе части имен, адресов или фраз, соединить цифры и слова. В настоящее время они доступны в Excel 2016, Excel Online и Mobile, Excel для планшетов и телефонов Android.

Функция ОБЪЕДИНИТЬ складывает текст из нескольких диапазонов или строк. Вы можете указать разделитель между каждым текстовым значением и дать указание игнорировать пустые ячейки. Даже если разделитель — пустая строка, ОБЪЕДИНИТЬ успешно сработает.

Вот как она выглядит:

Он имеет 3 обязательных и 1 необязательный аргументы:

• Разделитель позволяет указать любой символ для разделения текста. Это может быть запятая, пробел, амперсанд или что угодно. Символы должны быть заключены в двойные кавычки или это может быть ссылка на допустимую текстовую ячейку. Любые числа будут рассматриваться как текст.
• Второй параметр является обязательным аргументом. Если это ИСТИНА, пустые ячейки будут игнорироваться. Но вы можете приказать учитывать пустые, установив для него — ЛОЖЬ.
• ТЕКСТ1 позволяет вам указать диапазон для объединения, а не вводить каждое из них.
• — это необязательный аргумент, который позволяет указывать дополнительные диапазоны для включения, если ваш диапазон не является непрерывным.

Для текстовых элементов может быть не более 252 аргументов. Если результат превышает предел 32767 символов, она возвращает ошибку #ЗНАЧ!.

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

Кроме того, СЦЕП является стандартной функцией, используемой в Google Sheets. 

СЦЕПИТЬ продолжает поддерживаться для обеспечения совместимости с предыдущими версиями Excel.

Выглядит это следующим образом:

Есть только два аргумента, один из которых является необязательным:

• ТЕКСТ1 — текстовая запись, которую нужно присоединить. Это может быть отдельный элемент или область таблицы.
• обозначает дополнительные текстовые элементы для объединения.

Для текстовых данных может быть не более 253 аргументов.

Ранее, если вам нужно было объединить текст из нескольких ячеек, необходимо было указать каждую из них по отдельности. С новыми функциями ОБЪЕДИНИТЬ и СЦЕП вы можете просто ссылаться на диапазон и комбинировать текстовые фразы из диапазонов с разделителем или без него.

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

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

В то же время, СЦЕП и СЦЕПИТЬ выглядят одинаково, когда вам нужно соединить части текста в одну фразу с помощью разделителей.

и

Как легко увидеть, они не обрабатывают пустые ячейки, что приводит к появлению лишних пробелов (например, в G3 и G8).

Поэтому функции ОБЪЕДИНИТЬ суждено стать действительно популярным выбором для пользователей, работающих с большими объемами текстовых данных. 

Вы просто указываете разделитель (если нужно), выбираете «игнорировать пустые» и определяете диапазон. Это гораздо более разумное и быстрое решение.

Если у вас есть Excel 2016, изучите применение ОБЪЕДИНИТЬ и СЦЕП, чтобы увидеть, насколько они полезны, и попрощайтесь с традиционными способами при объединении.

 Спасибо за чтение!

Применение формулы CONCATENATE в другом месте

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

Щелкните ячейку один раз, чтобы она была выделена. Вы должны увидеть небольшой прямоугольник в правом нижнем углу ячейки, например:

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

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

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

​ щелчок по последней​

​ все теряются, кроме​Ячейку, которая не была​ Мы стараемся как можно​o-lenyska​Borovik1980​

​ — галочка переносить​

​: Подскажие, пожалуйста,есть ли​ ​ переключатель в соответствующую​ В нашем случае​ последнего, после адреса​

​«Текст1»​

​ элементы листа, в​ в таблице любым​ рассмотрим, как это​

​ вкладку​

​. Из раскрывшегося списка​ ​ строчке, но при​

​ тех, которые были​

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

​ которых он расположен.​

​ из тех двух​ сделать.​«Главная»​ действий следует выбрать​ этом одновременно зажать​ расположены в самом​ невозможно разделить. Если​ актуальными справочными материалами​Попробовала скопировать, не​ Уважаемые.​и радуетесь!​ чтобы объединенные значения,​ на кнопку​=A4&» «&A5&» «&A6&» «&A7&»​ выражение:​ левой кнопки мыши,​ Именно последнее свойство​ вариантов, которые были​

​Выделяем все ячейки строк​

​и щелкаем по​ пункт​

​ клавишу​ ​ верхнем элементе. Во​ ​ вы ищете сведения​ на вашем языке.​ получилось!​ ​Подскажите, пожалуйста, как​

​Guest​

​ начинались с новой​«OK»​ «&A8&» «&A9​&» «​ клацаем по первому​ и будет использовано​ описаны выше. Затем​ таблицы, которые мы​ кнопке на ленте​ ​«Формат ячеек…»​Shift​ втором случае, физически​ о том, как​ Эта страница переведена​пертаскивала Module (в​ с помощью формулы​: кстати если галочку​

​ строки в ячейке?​

​.​Для вывода результата на​

​Данное выражение – это​

​ элементу, содержащему наименование​ нами для выполнения​

​ во вкладке​

​ хотим объединить. Это​

​«Объединить и поместить в​.​на клавиатуре. Таким​ строки остаются в​ распределить данные из​ автоматически, поэтому ее​ меню View -​ решить задачу по​ на переносить по​Prog​После выполнения последнего действия​ экран жмем на​ своеобразный знак пробела​ техники в столбце​ поставленной задачи. Всего​«Главная»​ также можно сделать​ центре»​Также, в той же​ образом будет выделен​ прежнем виде, просто​ ячейки по нескольким​ текст может содержать​ Project Explorer)​

planetaexcel.ru>

Как разъединить ячейки в Excel? (используя 3 метода)

Есть 3 метода разделения ячеек в Excel:

1. Разъединение ячеек с помощью ленты Excel
2. Разъединение ячеек с помощью ярлыка Excel
3. Разъединить ячейки и скопировать исходное значение в каждую несоединенную ячейку

Обсудим подробно каждый из них —

Вы можете скачать этот шаблон Excel Unmerge Cells здесь — Unmerge Cells Excel Template

Метод №1 — разделение ячеек с помощью ленты Excel

Ниже мы приводим детали заказа по продукту. Обсудим это на примерах.

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

Выделите ячейки, которые вы хотите отключить. См. Снимок экрана ниже.

Перейдите на вкладку ГЛАВНАЯ, затем нажмите Слияние и центр вариант под Выравнивание, затем щелкните раскрывающееся меню. Он отобразит список элементов, как показано на скриншоте ниже. Нажмите на опцию Unmerge Cells.

Эта опция отключит все объединенные ячейки в выделенной области.

Окончательный результат показан ниже:

Объяснение

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

Метод № 2 — Разъединение ячеек с помощью горячих клавиш

Выделите ячейки, которые хотите объединить, и нажмите клавишу ALT + H + M + U, и это разделит все несоединенные ячейки.

Также обратите внимание на эти популярные сочетания клавиш в Excel

Метод № 3 — Разъединить ячейки и скопировать исходное значение в каждую не объединенную ячейку

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

Мы узнаем это на примере ниже.

Давайте снова возьмем данные о заказе с точки зрения продукта.

Чтобы разъединить ячейки и заполнить ячейки исходными значениями. Выполните следующие шаги:

Выберите ячейки, которые были объединены, затем перейдите на вкладку HOME и щелкните параметр «Объединить и центрировать» в разделе «Выравнивание», затем щелкните раскрывающееся меню, в нем отобразится список элементов, в котором щелкните параметр «Разъединить ячейки».

Это разъединит все ячейки, как показано на скриншоте ниже.

Снова выберите всю таблицу и перейдите на вкладку ГЛАВНАЯ, затем нажмите кнопку «Найти и выбрать» в разделе «Редакция». Откроется раскрывающийся список, как показано ниже. Нажмите на опцию «Перейти к специальному».

Откроется диалоговое окно «Перейти к специальному», как показано на снимке экрана ниже.

Щелкните переключатель «Пробелы», как показано ниже, затем нажмите «ОК».

Он выберет все пустые ячейки. Смотрите скриншот ниже.

Введите = (знак равенства) и нажмите клавишу со стрелкой вверх на клавиатуре. См. Снимок экрана ниже.

Это создаст простую формулу, которая заполняет пустую ячейку значением из указанной выше ячейки. Поскольку мы хотим заполнить все не объединенные ячейки, которые в настоящее время пусты, нажмите кнопку CTRL + ВВОД клавишу, чтобы ввести формулу во все выделенные ячейки.

Окончательный результат показан ниже:

То, что нужно запомнить

• Перед отключением ячеек необходимо проверить, находятся ли объединенные ячейки в выбранной области или нет.
• Для этого выделите ячейки на листе.
• На вкладке ГЛАВНАЯ в разделе выравнивания проверьте:
• Если параметр «Объединить и центрировать» не выделен, это означает, что в выбранной области нет объединенных ячеек.

УЗНАТЬ БОЛЬШЕ >>

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

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

1. Выделите все ячейки на листе. Это можно сделать, нажав сочетание клавиш Ctrl + A или щелкнув черный треугольник между заголовками строк и столбцов на листе.
2. Щелкните команду Home -> Align -> Merge and Center, если она выбрана. Если не выбран, выбранный лист не содержит объединенных ячеек.

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

1. Откройте окно «Найти и заменить». Это можно сделать с помощью сочетания клавиш Ctrl + F.
2. Оставьте поле «Найти» пустым и нажмите кнопку «Параметры.
3. Должна появиться кнопка «Форматировать». Щелкните левой кнопкой мыши по нему.
4. В диалоговом окне «Найти формат» на вкладке «Выравнивание» выберите параметр «Объединить ячейки». Затем нажмите ОК.
5. В диалоговом окне «Найти и заменить» нажмите «Найти далее» или «Найти все» в зависимости от следующей задачи.

Горячие клавиши объединения ячеек в Excel

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

Затем буквы будут отображаться под каждой командой или раскрывающимся меню команды. Таким образом, вы можете определить последовательность клавиш для вызова нужной команды. Чтобы объединить и центрировать ячейки в моей версии Excel, вам нужно последовательно нажимать Alt — Z — Sh — B. Это был первый метод.

Второй способ. Слить ячейки любым способом и снова вызвать команду клавишей F4 (повторить последнее действие).

Третий способ. Сначала поместите кнопку «Объединить ячейки» на панель быстрого доступа. Для этого щелкните треугольник справа от всех команд и выберите там «Дополнительные команды.

Вы будете перенаправлены к настройкам панели быстрого доступа. Найдите команду «Объединить и отцентрировать» и добавьте ее в правое окно.

Кнопка появится на панели быстрого доступа.

После нажатия Alt под этой кнопкой появится число, зависящее от порядкового номера в строке.

Теперь есть еще одна комбинация клавиш для объединения ячеек в Excel — Alt + 5.

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

​ ячейку с формулой​​Существует выход и в​ на ленте «Объединить​ центре​ ; будет выглядеть​ объединить их можно​ удобства также приводим​Субъект​ #VALUE! Ошибка.​ вставляя между текстовыми​ одну с помощью​ все ячейки на​ объединение нескольких ячеек​ Excel» тут.​ с исходными данными​-​Выравнивание по вертикали​ сцепления.​ этой ситуации. Мы​ и поместить в​недоступна, убедитесь, что​

​ так:​ с помощью функции​ ссылку на оригинал​Индекс​Скопируйте данные примеров из​ значениями указанный разделитель.​ символа «амперсанд» (&)​ листе, при этом​ в одну. С​

​Можно​​ и столбец В​настраиваем ориентацию.​ – регулирует расположение​Таким образом, мы получаем​ будем использовать функцию​ центре».​ вы не редактируете​=ПОДСТАВИТЬ(G8;»»»:»»»;»;»)​ СЦЕПИТЬ() записав формулу​

Синтаксис

​ (на английском языке).​Страна​

​ приведенных ниже таблиц​

​ Если в качестве​

​ или функции СЦЕП.​​ информация во всех​

​ помощью данных функций​объединить слова,​ с формулой удаляем.​Внимание!​ данных по вертикали​ новую ячейку, содержащую​ «СЦЕПИТЬ». Прежде всего,​В этом случае, ячейки​ ячейку и что​

​В итоге, вставив результат​​ =СЦЕПИТЬ(A6;A7;A8;A9)​

​Не удается разбить отдельные​Тула​

​ и вставьте их​​ разделителя используется пустая​

​Выделите ячейку, в которую​ ячейках, кроме верхней​ Вы сможете переносить​другие данные не​

​ Получилась такая же​​Когда объединяем заполненные​

​ — вверху ячейки,​ данные, которые следовало​ нужно добавить между​ объединятся, а все​ ячейки, которые вы​​ формулы как значение,​​Или с помощью Амперсанда​ ячейки, но можно​Тульская обл.​ в ячейку A1​. ​ текстовая строка, функция​ вы хотите вставить​ левой, будет удалена.​ текст на несколько​​ только в предложение,​ таблица, только ячейки​​ ячейки, то сохраняется​

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

Примеры

​ хотите объединить, не​ получим строку Вася;Петя;Даша;Маша.​ & (добавим еще​ отобразить его как​74133​ нового листа Excel.​ эффективно объединит диапазоны.​ объединенные данные.​В примере ниже мы​ строк, создавать заголовки​ но и через​ А и В​ содержимое только из​

​ ячейки.​

​ промежуточные ячейки удалены.​
​ соединить, ещё одну​
​ вписываться в объединенную​
​ находятся в таблице.​
​Примечание​
​ для красоты символ​
​ если бы ячейки​
​Россия​
​ Чтобы отобразить результаты​	​Примечание:​Введите = (знак равенства)​
​ объединим диапазон A1:E1,​	​ для таблиц, вписывать​ знаки, например –​ объединены и данные​ первой ячейки, в​Можно совмещать эти функции​

​Как видим, если обычное​

​ ячейку. Для этого,​	​ ячейку, станут помещаться​
​Совет:​	​: В формуле для​
​ пробела): =A6&» «&A7&»​	​ была разделена, объединение​
​Красноярск​	​ формул, выделите их​
​ Эта функция недоступна в​	​ и выберите первую​
​ чтобы создать заголовок​	​ длинный текст в​
​ косую черточку, другие​	​ в них объединены.​
​ других ячейках –​	​ в одной ячейке.​ объединение ячеек в​
​ кликаем правой кнопкой​	​ в центре.​ Чтобы объединить ячейки, не​ замены двойных кавычек​ «&A8&» «&A9​ ​ ячеек над ней.​Красноярский край​​ и нажмите клавишу​ Excel 2016, если​ ячейку, которую нужно​ для нашего листа.​

​ одну строку, не​

​ знаки. Пример: сметана/молоко.​	​Если ячейки с исходными​	​ теряется. Появится такое​	​ Например: по центру​
​ программе Microsoft Excel​	​ мыши на крайнюю​	​Если вы хотите, чтобы​	​ выравнивая данные по​
​ » использовано 2​	​Если значений много, то​	​Выделите ячейки для объединения.​	​98109​
​ F2, а затем —​	​ у вас нет​	​ объединить.​	​Выделите ячейки, которые требуется​
​ увеличивая при этом​	​Можно​	​ данными удалять не​	​ предупреждающее окно.​
​ по вертикали и​	​ довольно простое, то​	​ правую из объединяемых​	​ данные размещались, согласно​
​ центру, щелкните объединенную​
​ двойные кавычки «».​	​ придется вводить вручную​	​Выберите​	​Россия​
​ клавишу ВВОД. При​	​ подписки на Office​
​Введите символ​	​ объединить.​ ширину столбцов, и​

См. также

​составить предложение не только​

​ нужно, то не​

​Но, Excel дает нам​ по центру по​

​ с объединением ячеек​ ячеек. В появившемся​

​ форматированию ячейки, то​

​ ячейку и затем​Примечание:​

​ много амперсандов и​

​Объединить и поместить в​

​Москва​

support.office.com>

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

Если ячейки пустые

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

Способ 1

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

• выделить мышкой нужные из них (в своем примере я выделил A1, B1, C1 и D1);
• в разделе «Главная» нажать по инструменту «Объединить ячейки» , см. скриншот ниже.

Выделение и объединение ячеек (Excel 2019)

В эту же секунду ячейки станут объединенными ( примечание : кстати, если у вас были какие-то данные в них — то всё это удалится, кроме данных из ячейки A1) .

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

Способ 2

Если на панели инструментов Excel вы не находите нужной кнопки (опции) — эту задачу можно выполнить иначе:

• сначала также выделите необходимые ячейки;
• затем кликните по выделенной области правой кнопкой мышки и в появившемся меню откройте пункт «Формат ячеек» .

Далее перейдите во вкладку «Выравнивание» и поставьте галочку рядом с функцией «Объединение ячеек» . Нажмите OK.

Как видите на скрине ниже — задача успешно решена (и этот способ вполне рабочий, правда, вынуждает сделать 1-2 «лишних» клика мышкой) .

Задача выполнена — ячейки слиты

Если в ячейках есть данные (которые также нужно объединить)

Важная ремарка : при объединении ячеек мы будем не суммировать их содержимое, а «склеивать» (сцеплять) между собой. Т.е. если у вас в одной ячейке приводится имя, а в другой фамилия — после объединения будет одна строка с именем и фамилией; если в одной ячейке цифра «5», а в другой «3» — после объединения будет «53» (а не как при сумме: «8») .

Способ 1

В качестве примера взял список ФИО нескольких вымышленных персонажей. Задача, кстати, вполне приближена к жизни (приходилось с этим сталкиваться).

Сначала необходимо выбрать одну из ячеек напротив первой строки (я взял E1) и написать в строке формул следующее выражение:

=СЦЕПИТЬ( A1 ; B1 ; C1 )

Пишем спец. формулу (СЦЕПИТЬ)

После того, как нажмете Enter, формула сцепит все перечисленные ячейки в ней. В моем случае получилось ФИО, но без пробелов (что ни есть удобно).

Для того, чтобы исправить это, просто измените частично формулу (добавьте кавычки с пробелом, как в моем примере) :

=СЦЕПИТЬ( A1 ;» «; B1 ;» «; C1 )

Если вам нужны пробелы (или другие символы) между объединенными данными — корректируем формулу

Разумеется, вместо пробела в кавычках можно поставить любой другой символ (дефис, например).

После, останется только растянуть формулу на другие строки, и задача будет решенной.

Растягиваем формулу на другие строки

Как перенести склеенные данные в другой документ (чтобы они не зависели от формул)

Наши сцепленные строки в этом документе зависят от формул, и если слегка их изменить — то все «разъедется». В ряде случае гораздо удобнее работать не с ними, а с текстовыми строками. Попробуем перенести наши результаты в другой документ (или новый лист).

И так, сначала необходимо выделить наши сцепленные ячейки (в моем примере это столбец «E»), нажать правой кнопкой мышки по ним и выбрать функцию «Копировать» .

Далее открыть новый документ (или лист) кликнуть правой кнопкой мышки по нужной ячейке и нажать по меню «Специальная вставка» . См. пример ниже.

В появившемся окне настроек укажите, что вам требуется вставить только «Значения» (а не сами формулы) и нажмите OK.

Как видите на скрине ниже, в документ были вставлены текстовые значения из ранее сцепленных ячеек (а не сами формулы).

Формул нет (только текстовые строки)

Способ 2

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

Формула в этом случае будет выглядеть так:

Еще один вариант объединения ячеек с содержимым

Разумеется, чтобы добавить пробелы между объединенными значениями, нужно слегка изменить формулу:

Вставляем пробелы между данными

Если вместо пробелов вам нужны дефисы, формула несколько преобразится:

Как будет выглядеть формула, если вместо пробелов вставим дефисы

Когда «настройка» формулы будет завершена — просто потяните мышкой за уголок и растяните ее на другие строки (столбцы). После, Excel применит ее к другим ячейкам (пример ниже).

Формула распространена на др. ячейки

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

На сим закруглюсь! Дополнения по теме приветствуются.

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

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

Когда требуется выполнить слияние строк в Excel, Вы можете достичь желаемого результата вот таким способом:

Как объединить несколько строк в Excel при помощи формул

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

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

=CONCATENATE(A1,», «,A2,», «,A3) =СЦЕПИТЬ(A1;», «;A2;», «;A3) Объединить строки, оставив пробелы между значениями:

=CONCATENATE(A1,» «,A2,» «,A3) =СЦЕПИТЬ(A1;» «;A2;» «;A3) Объединить строки без пробелов между значениями:

Уверен, что Вы уже поняли главное правило построения подобной формулы – необходимо записать все ячейки, которые нужно объединить, через запятую (или через точку с запятой, если у Вас русифицированная версия Excel), и затем вписать между ними в кавычках нужный разделитель; например, “, “ – это запятая с пробелом; ” “ – это просто пробел.

Итак, давайте посмотрим, как функция CONCATENATE (СЦЕПИТЬ) будет работать с реальными данными.

1. Выделите пустую ячейку на листе и введите в неё формулу. У нас есть 9 строк с данными, поэтому формула получится довольно большая:

=CONCATENATE(A1,», «,A2,», «,A3,», «,A4,», «,A5,», «,A6,», «,A7,», «,A8) =СЦЕПИТЬ(A1;», «;A2;», «;A3;», «;A4;», «;A5;», «;A6;», «;A7;», «;A8)

Скопируйте эту формулу во все ячейки строки, у Вас должно получиться что-то вроде этого:

Теперь все данные объединены в одну строку. На самом деле, объединённые строки – это формулы, но Вы всегда можете преобразовать их в значения. Более подробную информацию об этом читайте в статье Как в Excel заменить формулы на значения.

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 объединит выделенные ячейки в одну, слив туда же и текст через пробелы.

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

​ необходимости измените ширину​​ 365. Если вы​&​Нажмите команду​ многое другое.​ из ячеек, расположенных​ нужно копировать значение​ возможность объединить и​ горизонтали. Текст будет​ без потерь придется​ контекстном меню выбираем​ нужно из выпадающего​ выберите выравнивание по​Мы стараемся как​ аргументов функции СЦЕПИТЬ().​ центре.​Московская обл.​ столбцов, чтобы видеть​ являетесь подписчиком Office​и пробел, заключенный​Объединить и поместить в​

​Очень часто содержимое не​ в строке, но​ столбца с формулой​ ячейки и данные​ расположен в средине​ повозиться. Тем не​

Объединение ячеек

​ пункт «Вставить…».​

​ списка выбрать пункт​​ левому или правому​ можно оперативнее обеспечивать​
​ Это неудобно.​​Важно:​08830​ все данные.​ 365, Убедитесь, что​ в кавычки.​ центре​ может полностью отобразиться​ и в столбце​ (пункт 4 не​ в ячейках.​ ячейки.​ менее, это тоже​

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

1. ​Открывается окошко, в котором​ «Объединить ячейки».​​ краю рядом со​ вас актуальными справочными​​Избежать этого поможет дополнительный​

2. ​  В объединенной ячейке​​Россия​

​Пример 1​​ у вас есть​

• ​Выберите следующую ячейку, которую​на вкладке​ в ячейке, т.к.​. Для этого нужно​ нужно делать). А​Как объединить ячейки в​В разделе «Ориентация»​ выполнимая задача для​ нужно переставить переключатель​В этом случае, запись​

  

  

  

  

  

  

• ​ значком​ материалами на вашем​ столбец с простейшей​ отображается содержимое только​Хабаровск​Валюта​ последнюю версию Office.​​ нужно объединить, и​​Главная​

support.office.com>

Объедините содержимое двух или более ячеек в Excel

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

СОГЛАСОВАТЬ Синтаксис и Аргументы

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

Синтаксис для функции CONCATENATE :

 = CONCATENATE (Text1, Text2, ... Text255) 

Text1 (обязательно) может быть фактическим текстом, таким как слова или цифры, пробелы, заключенные в кавычки, или ссылками на ячейки для расположения данных на листе.

Text2, Text3, … Text255 (необязательно до 255 текстовых записей) можно добавить в функцию CONCATENATE длиной до 8 192 символов, включая пробелы. Каждая запись должна быть разделена запятой.

Ни один из методов конкатенации не оставляет автоматически пробел между словами, что хорошо при соединении двух частей составного слова, такого как Бейсбол, в одну или объединении двух серий чисел, таких как 123456.

Однако для соединения имени и фамилии или адреса требуется пробел, поэтому он должен быть включен в формулу объединения, как показано в строках 4, 5 и 6 сопровождающего изображения.

Объединение числовых данных

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

Полученные данные в ячейке C7 нельзя использовать в качестве аргументов для определенных математических функций, таких как SUM и AVERAGE. Если такая запись включена в аргументы функции, она обрабатывается как другие текстовые данные и игнорируется.

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

Как использовать функцию Excel CONCATENATE

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

Приведенные ниже шаги охватывают ввод функции с помощью диалогового окна в ячейку C2 .

1. Нажмите на ячейку C2 , чтобы сделать ее активной.
2. Нажмите на вкладку Формулы .
3. Выберите Текстовые функции на ленте, чтобы открыть раскрывающийся список функций.
4. Нажмите на CONCATENATE в списке, чтобы открыть диалоговое окно функции.
5. Нажмите на строку Текст 1 в диалоговом окне.
6. Нажмите на ячейку A4 на рабочем листе, чтобы ввести ссылку на эту ячейку в диалоговое окно.
7. Нажмите на строку Текст 2 в диалоговом окне.
8. Нажмите Пробел на клавиатуре, чтобы добавить пробел в строку Текст 2 . Excel добавляет двойные кавычки вокруг пробела.
9. Нажмите на строку Текст 3 в диалоговом окне.
10. Нажмите на ячейку B4 на листе, чтобы ввести ссылку на эту ячейку в диалоговое окно.
11. Нажмите ОК , чтобы закрыть диалоговое окно и вернуться на лист.

Объединенное имя Мэри Джонс отображается в ячейке C4 .

Если щелкнуть ячейку C4, полная функция появится на панели формул над рабочим листом.

 = CONCATENATE (A4, "", B4) 

Отображение амперсанда в объединенном тексте

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

Чтобы отображать амперсанд в виде текстового символа, а не использовать его в качестве оператора конкатенации, заключите его в двойные кавычки, как и другие текстовые символы, как показано в формуле в ячейке D6 .

Следует отметить, что в этом примере пробелы присутствуют по обе стороны от амперсанда, чтобы отделить этот символ от слов по обе стороны.Чтобы достичь этого результата, вы вводите пробелы с обеих сторон амперсанда внутри двойных кавычек следующим образом: “&” .

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

 = A6 & "&" & B6 

Например, формула в ячейке D6 может быть заменена приведенной выше формулой для достижения тех же результатов.

Объединение чисел и дат в различных форматах

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

ТЕКСТ(значение; формат) имеет два аргумента:

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

Например:

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

=A2 & ” ” & ТЕКСТ(B2; “$# ##0,0”) – отобразить число с двумя десятичными знаками и знак $.

=A2 & ” ” & ТЕКСТ(B2; “0,#”) – не отображает лишние нули и знак валюты.

=A2&” “&ТЕКСТ(B2;”#_??/??”)  – отобразить десятичную часть в виде дроби.

=”Сегодня  ” & ТЕКСТ(СЕГОДНЯ();”dd.mm.yyyy”) & ”  ” & A2 & ” ” & ТЕКСТ(B2;”$# ##0,0″) – показать текст, дату и число.