1166 подписчиков

Как формулой объединить ячейки в Excel

16 ноября 202016 ноя 2020

1551

4 мин

Оглавление

Как объединить ячейки
Как объединить ячейки при помощи формул

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

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

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

Объединить несколько ячеек (строк/столбцов) в Excel очень просто. Способ применяется для объединения пустых ячеек или ячеек, среди которых только одна содержит данные, например шапка таблицы.

Для объединения выделите область ячеек, которые нужно объединить и нажмите на вкладке Главная (Выравнивание) подходящий вариант:

Варианты объединения:

Результат объединения выделенных ячеек:

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

Подобный эффект, но без последствий, даёт форматирование ячеек (Ctrl+1), вкладка Выравнивание ► по горизонтали ► по центру выделения:

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

Но вы настойчивы, и вот результат, мы потеряли часть данных:

Нет не решаемых задач. На помощь придет макрос, который поможет объединить данные из разных ячеек (строк/столбцов) в одну общую без потери данных:

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

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

Результат объединения при помощи макроса

Как объединить ячейки при помощи формул

1. Самый простой способ объединить данные в ячейках – использование знака конкатенации & (амперсанд):

Для добавления & в формулу нажмите Shift+7, на ENG раскладке клавиатуры.

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

Например:

пробел " ";
запятая и пробел ", " ;
точка с запятой ";" и т.д.

Объединение ячеек при помощи & с разделителем

2. Следующий вариант объединить строки – функция СЦЕПИТЬ (CONCATENATE), одна из текстовых функций, созданная для объединения двух или нескольких текстовых строк в одну.

Начиная с Excel 2016 является пока ещё рабочим атавизмом и используется в рамках совместимости версий.

Синтаксис функции:

СЦЕПИТЬ(текст1;[текст2];…), где текст1, ссылка на первую ячейку с данными или текстовую константу, соответственно [текст2] ссылка на следующую ячейку или текстовую константу и т.д. Объединять можно текстовое значение, число (дату) или ссылки на ячейки.

Формула СЦЕПИТЬ может содержать до 255 элементов и до 8 192 символов.

Объединение ячеек при помощи функции СЦЕПИТЬ

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

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

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

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

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

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

Решение появляется начиная с Excel 2016, в арсенал которого вошли две новых функции СЦЕП (CONCAT) и ОБЪЕДИНИТЬ (TEXTJOIN), речь о которых пойдет ниже.

3. Функция СЦЕП, полностью выполняющая функционал предшественника, но работает и с диапазонами данных.

Синтаксис функции:

СЦЕП(текст1; [текст2]; …), где текст – элемент, который нужно присоединить: строка или массив строк, например диапазон ячеек.

Формула СЦЕП может содержать до 253 аргументов.

Если объединенная строка содержит свыше 32767 символов (ограничение на ввод данных для ячейки), возникнет ошибка ЗНАЧ!.

Объединение диапазона ячеек при помощи функции СЦЕП

Работает без проблем, но если требуется соединить диапазон с разделителем, СЦЕП не поможет.

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

Синтаксис функции:

ОБЪЕДИНИТЬ(разделитель; игнорировать_пустые; текст1; [текст2]; ...), где

Разделитель – символ, который будет вставлен между фрагментами, указывается в кавычках;
игнорировать_пустые – если 0, то пустые ячейки включаются, если 1 – игнорируются;
Текст... - диапазон ячеек, содержимое которых нужно склеить.

Формула ОБЪЕДЕНИТЬ может содержать до 252 аргументов.