Как разделить и объединить ячейки в excel

Подсчет суммы в Excel с помощью таблицы

Встроенные инструменты Excel позволяют создать отформатированную таблицу из данных и взять результат оттуда. Для этого:

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

Как объединить диапазон.

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

Чтобы интегрировать несколько ячеек, скажем, от А1 до А4, вам понадобится что-то подобное:

или

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

Метод 1. Используем клавишу CTRL.

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

1. Выберите куда вы хотите ввести формулу.
2. Введите  =СЦЕПИТЬ(    в строке формул.
3. Нажмите и удерживайте Ctrl и одновременно щелкните по каждой клетке, которые вы хотите слить вместе.
4. Отпустите Ctrl и введите закрывающую скобку. Нажмите ENTER.

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

Способ 2. Используйте ТРАНСП

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

В этом случае лучше использовать функцию ТРАНСП (TRANSPOSE в английской версии) для возврата массива, а затем заменить его ссылками одним махом.

1. Там, где вы хотите вывести объединенный диапазон, запишите:

1. На панели формул нажмите клавишу F9, чтобы заменить выражение результатом вычисления. В результате у вас будет массив чисел для объединения.
2. Удалите фигурные скобки, окружающие значения массива.

1. Введите = СЦЕПИТЬ (перед первым значением), затем введите закрывающую скобку после последнего и нажмите Enter.

Замечание. Какой бы метод вы ни использовали, объединенное значение в С1 является текстом (обратите внимание на выравнивание по левому краю), даже если каждое из исходных значений является числом. Это связано с тем, что СЦЕПИТЬ всегда возвращает текстовую фразу независимо от типа данных источника

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

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

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

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

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

Консолидация (объединение) данных из нескольких таблиц в одну

Способ 1. С помощью формул

Имеем несколько однотипных таблиц на разных листах одной книги. Например, вот такие:

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

Самый простой способ решения задачи «в лоб» — ввести в ячейку чистого листа формулу вида

=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3

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

Если листов очень много, то проще будет разложить их все подряд и использовать немного другую формулу:

=СУММ(‘2001 год:2003 год’!B3)

Фактически — это суммирование всех ячеек B3 на листах с 2001 по 2003, т.е. количество листов, по сути, может быть любым. Также в будущем возможно поместить между стартовым и финальным листами дополнительные листы с данными, которые также станут автоматически учитываться при суммировании.

Способ 2. Если таблицы неодинаковые или в разных файлах

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

Рассмотрим следующий пример. Имеем три разных файла (Иван.xlsx, Рита.xlsx и Федор.xlsx) с тремя таблицами:

Хорошо заметно, что таблицы не одинаковы — у них различные размеры и смысловая начинка. Тем не менее их можно собрать в единый отчет меньше, чем за минуту. Единственным условием успешного объединения (консолидации) таблиц в подобном случае является совпадение заголовков столбцов и строк. Именно по первой строке и левому столбцу каждой таблицы Excel будет искать совпадения и суммировать наши данные.

Для того, чтобы выполнить такую консолидацию:

1. Заранее откройте исходные файлы
2. Создайте новую пустую книгу (Ctrl + N)
3. Установите в нее активную ячейку и выберите на вкладке (в меню) Данные — Консолидация(Data — Consolidate) . Откроется соответствующее окно:

Установите курсор в строку Ссылка(Reference) и, переключившись в файл Иван.xlsx, выделите таблицу с данными (вместе с шапкой). Затем нажмите кнопку Добавить(Add) в окне консолидации, чтобы добавить выделенный диапазон в список объединяемых диапазонов.
Повторите эти же действия для файлов Риты и Федора. В итоге в списке должны оказаться все три диапазона:

Обратите внимание, что в данном случае Excel запоминает, фактически, положение файла на диске, прописывая для каждого из них полный путь (диск-папка-файл-лист-адреса ячеек). Чтобы суммирование происходило с учетом заголовков столбцов и строк необходимо включить оба флажка Использовать в качестве имен (Use labels)

Флаг Создавать связи с исходными данными (Create links to source data) позволит в будущем (при изменении данных в исходных файлах) производить пересчет консолидированного отчета автоматически.

После нажатия на ОК видим результат нашей работы:

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

При работе с однотипными данными, размещенными в разных таблицах, листах или даже книгах, для удобства восприятия лучше собрать информацию воедино. В Microsoft Excel с этой задачей можно справиться с помощью специального инструмента, который называется «Консолидация». Он предоставляет возможность собрать разрозненные данные в одну таблицу. Давайте узнаем, как это делается.

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

• столбцы во всех таблицах должны иметь одинаковое название (допускается лишь перестановка столбцов местами);
• не должно быть столбцов или строк с пустыми значениями;
• шаблоны у таблиц должны быть одинаковыми.

Создание консолидированной таблицы

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

1. Открываем отдельный лист для консолидированной таблицы.

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

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

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

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

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

Например:

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

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

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

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

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

Excel 2010: как объединить ячейки

Для данной версии этой программы существуют следующие пять приёмов.

Приём №1

Первый приём работает при помощи контекстного меню. Опытные пользователи программы считают его самым быстрым и простым. Выполняется он так:

• зажмите клавишу Ctrl, а затем кликайте по ячейкам, которые необходимо объединить левой кнопкой мыши;
• выделив их, наведите курсор на них, а потом кликните правой кнопкой мыши;
• после этого появится диалоговое окно со списком возможных действий. Наведите курсор на строку «Формат ячеек», а затем нажмите левую кнопку мыши;
• в появившемся окне перейдите во вкладку «Выравнивание»;
• возле пункта «Объединение ячеек» поставьте галочку и нажмите «ОК».

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

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

Возможно, Вам пригодится статья о том, как сделать сводную таблицу в Excel.

Приём № 2

Для объединения можно использовать панель инструментов. Так, в Excel 2010, а также в версиях 2007 и 2013 есть иконка, которая называется «Объединить и поместить в центре». Она расположена в меню в разделе «Главная». Если нажать на эту иконку, то появится выпадающее меню, в котором есть несколько вариантов объединения. После того как будут выделены необходимые ячейки, нужно просто нажать на эту иконку и выбрать наиболее подходящий вариант их объединения. Кстати, нажав на эту иконку повторно, их можно опять разгруппировать.

Приём № 3

Чтобы во время слияния необходимая информация не исчезла, можно воспользоваться функцией «Сцепить». К примеру, необходимо будет соединить столбцы А и В. Для этого нам нужно выполнить следующие действия:

• выделяем В1. Во вкладке «Главная» наводим курсор на кнопку «Вставить», а затем кликаем на стрелочку, которая находится возле неё. Появится выпадающее меню, в котором нужно кликнуть на пункт «Вставить столбцы на лист»;
• между столбцами А и В появится новый столбец. Все данные из столбца В переместятся в С. В появившийся пустой столбец вписывается формула =СЦЕПИТЬ(А1;»,»;С1) и нажимается Enter. В этой формуле в кавычках заключён разделительный знак. Это необязательно должна быть запятая. Для разделения информации можно использовать такие знаки, как: «;», «:»;
• далее выделяется ячейка с формулой. После этого наводится курсор на правый нижний угол и зажимается левая кнопка мыши. Выделяется весь столбец. Таким образом, формула скопируется во все ячейки и столбцы объединятся;
• теперь выделите столбец В и нажмите Ctrl+C. После кликните один раз по столбцу правой кнопкой мыши и в появившемся меню сначала выберите пункт «Специальная вставка». В выпавшем меню выберите в пункте «Вставить значение» иконку с цифрами 123. Все эти действия необходимы чтобы выделенные ячейки столбца не были связаны с соседними ячейками формулой;
• далее удаляются столбцы А и С. Для этого курсор ставится в столбце А. Затем во вкладке «Главная» необходимо нажать на кнопку «Удалить». В выпавшем списке выбрать «Удалить столбцы с листа». Аналогичным образом удалить и столбец С. В итоге в таблице останется лишь один столбец со всеми объединёнными данными.

Приём № 4

Объединить их, не потеряв информацию, можно и другим способом. Алгоритм действий такой же, как и в третьем приёме, только для их склеивания используется формула =А2&С2.

Приём № 5

Объединять ячейки в Excel можно и при помощи горячих клавиш. Для этого нужно скопировать уже соединённую ячейку при помощи сочетания горячих клавиш Ctrl+C, а затем вставить её в нужном месте, используя горячие клавиши Ctrl+V. Стоит отметить, что применение горячих клавиш позволит значительно ускорить процесс создания таблицы с объединёнными ячейками.

Объединение определенных столбцов из нескольких листов.

А вот, как мне кажется, наиболее часто встречающаяся ситуация:

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

Запустите мастер объединения листов, как мы это делали в предыдущем примере, укажите нужные, а затем выберите соответствующие столбцы. Да, это так просто!

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

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

Полнофункциональная ознакомительная версия Ultimate Suite доступна для загрузки в по этой ссылке.

Как объединить столбцы ячеек и сохранить цвет текста в Excel?

Как все мы знаем, при конкатенации или объединении столбцов ячеек в один столбец форматирование ячеек (например, цвет шрифта текста, форматирование чисел и т. Д.) Будет потеряно. В этой статье я расскажу о некоторых хитростях, позволяющих объединить столбцы ячеек в один и максимально упростить сохранение цвета текста в Excel.

Объедините столбцы ячеек и сохраните цвет шрифта текста с кодом VBA

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

Чтобы завершить эту задачу в Excel, следующий код VBA может оказать вам услугу, сделайте следующее:

1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модульи вставьте следующий код в Окно модуля.

Код VBA: объединить столбцы ячеек и сохранить цвет текста:

Sub MergeFormatCell()
'Updateby Extendoffice
    Dim xSRg As Range
    Dim xDRg As Range
    Dim xRgEachRow As Range
    Dim xRgEach As Range
    Dim xRgVal As String
    Dim I As Integer
    Dim xRgLen As Integer
    Dim xSRgRows As Integer
    Dim xAddress As String
    On Error Resume Next
    xAddress = ActiveWindow.RangeSelection.Address
    Set xSRg = Application.InputBox("Please select cell columns to concatenate:", "KuTools For Excel", xAddress, , , , , 8)
    If xSRg Is Nothing Then Exit Sub
    xSRgRows = xSRg.Rows.Count
    Set xDRg = Application.InputBox("Please select cells to output the result:", "KuTools For Excel", , , , , , 8)
    If xDRg Is Nothing Then Exit Sub
    Set xDRg = xDRg(1)
    For I = 1 To xSRgRows
        xRgLen = 1
        With xDRg.Offset(I - 1)
            .Value = vbNullString
            .ClearFormats
            Set xRgEachRow = xSRg(1).Offset(I - 1).Resize(1, xSRg.Columns.Count)
            For Each xRgEach In xRgEachRow
                .Value = .Value & Trim(xRgEach.Value) & " "
            Next
            For Each xRgEach In xRgEachRow
                xRgVal = xRgEach.Value
                With .Characters(xRgLen, Len(Trim(xRgVal))).Font
                .Name = xRgEach.Font.Name
                .FontStyle = xRgEach.Font.FontStyle
                .Size = xRgEach.Font.Size
                .Strikethrough = xRgEach.Font.Strikethrough
                .Superscript = xRgEach.Font.Superscript
                .Subscript = xRgEach.Font.Subscript
                .OutlineFont = xRgEach.Font.OutlineFont
                .Shadow = xRgEach.Font.Shadow
                .Underline = xRgEach.Font.Underline
                .ColorIndex = xRgEach.Font.ColorIndex
                End With
                xRgLen = xRgLen + Len(Trim(xRgVal)) + 1
            Next
        End With
    Next I
End Sub

3, Затем нажмите F5 нажмите клавишу для запуска этого кода, и появится диалоговое окно с напоминанием о выборе диапазона данных, который вы хотите объединить с цветом текста, см. снимок экрана:

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

5. Затем нажмите OK Кнопка, столбцы были объединены без потери цвета текста, как показано на следующем снимке экрана:

Внимание: Если данные, которые необходимо объединить, содержат числа, код макроса не может работать правильно

Объединить текст в ячейках с помощью функции ОБЪЕДИНИТЬ

В версии MS Excel 2016 появилась еще одна функция для объединения текста – функция ОБЪЕДИНИТЬ. Если вы пользователь более ранних версий, то для того чтобы объединить текст, используйте вышеописанные способы.

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

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

Сперва разберем синтаксис функции ОБЪЕДИНИТЬ:

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

• разделитель – текстовая строка, которой будет разделятся объединенный текст;
• игнорировать_пустые – принимает значение ИСТИНА (1) или ЛОЖЬ (0). Если необходимо пропустить пустые ячейки, то устанавливаем значение ИСТИНА;
• текст1 – текстовый элемент, который необходимо объединить;
• ; . – дополнительные текстовые элементы для объединения.

Рассмотрим пример, как объединить текст в Excel используя функцию ОБЪЕДИНИТЬ.

Объединить текст в Excel – Объединить текст с помощью функции ОБЪЕДИНИТЬ

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

Как пользоваться функцией СЦЕПИТЬ в Excel

Начнем с простейших примеров.

Данные для объединения:

Ставим курсор в ячейку Е2 и вызываем мастер функций (кнопка fx). В категории «Текстовые» находим функцию СЦЕПИТЬ. Аргументы – ссылки на ячейки А2, В2, С2 и пробелы:

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

Такой же результат получим с помощью амперсанда:

Один из аргументов – текст. Формула: =СЦЕПИТЬ(«слесарь»; » «;A2;» «;B2;» «;C2). Результат:

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

1. Соединим текст с датой. Данные находятся в отдельных ячейках. Для столбца «Дата» установлен формат «Дата». Если использовать только функцию СЦЕПИТЬ, дата отобразится в числовом формате. Поэтому добавим функцию ТЕКСТ для форматирования чисел. Формула: Второй аргумент функции ТЕКСТ – формат представления даты. Результат:
2. Соединим сразу много ячеек в строке. Если писать формулу с функцией СЦЕПИТЬ, получится долго и малоэффективно. Используем маленькую хитрость. Вот наша строка со значениями в отдельных ячейках. В ячейку Н1 ставим знак «равно». Выделяем диапазон со значениями А1:F1. Вводим амперсанд & и пробел “ “. Жмем F9. В строке формул появится формула массива. Ставим курсор в строку формул после знака «равно». Пишем название функции. Фигурные скобки заменяем на круглые и жмем ВВОД. Получаем нужный результат.
3. Соединим значения через косую черточку («-», «&» и т.п.). Формула будет выглядеть так: = СЦЕПИТЬ (ссылка на ячейку 1; «/»; ссылка на ячейку 2).
4. Соединим несколько значений в столбце. Функция подходит для значений в строках. Описанный выше способ с формулой массива тоже не сработает.

Преобразуем вертикальный диапазон в горизонтальный с помощью функции ТРАНСП. А затем воспользуемся формулой массива для быстрого соединения значений. В ячейку В1 ставим знак «равно». Пишем ТРАНСП. Указываем диапазон А1:А6. Вводим & и пробел (« »). Жмем F9.

Теперь преобразуем формулу массива, добавив функцию и заменив скобки.

Вставка и настройка функции

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

1. Для начала определяемся с ячейкой, в которой планируем объединить данные из других. Переходим в нее (выделяем) и щелкаем по значку “Вставить функцию” (fx).
2. В открывшемся окне вставки функции выбираем категорию “Текстовые” (или “Полный алфавитный перечень”), отмечаем строку “СЦЕП” (или “СЦЕПИТЬ”) и кликаем OK.
3. На экране появится окно, в котором нужно заполнить аргументы функции, в качестве которых могут быть указаны как конкретные значения, так и ссылки на ячейки. Причем последние можно указать как вручную, так и просто кликнув по нужным ячейкам в самой таблице (при это курсор должен быть установлен в поле для ввода значения напротив соответствующего аргумента). В нашем случае делаем следующее:
   • находясь в поле “Текст1” щелкаем по ячейке (A2), значение которой будет стоять на первом месте в объединенной ячейке;
   • кликаем по полю “Текст2”, где ставим запятую и пробел (“, “), которые будут служит разделителем между содержимыми ячеек, указанных в аргументах “Текст1” и “Текст3” (появится сразу же после того, как мы приступим к заполнению аргумента “Текст2”). Можно на свое усмотрение указывать любые символы: пробел, знаки препинания, текстовые или числовые значения и т.д.
   • переходим в поле “Текст3” и кликаем по следующей ячейке, содержимое которой нужно добавить в общую ячейку (в нашем случае – это B2).
   • аналогичным образом заполняем все оставшиеся аргументы, после чего жмем кнопку OK. При этом увидеть предварительный результат можно в нижней левой части окна аргументов.
4. Все готово, нам удалось объединить содержимое всех выбранных ячеек в одну общую.
5. Выполнять действия выше для остальных ячеек столбца не нужно. Просто наводим указатель мыши на правый нижний угол ячейки с результатом, и, после того как он сменит вид на небольшой черный плюсик, зажав левую кнопку мыши тянем его вниз до нижней строки столбца (или до строки, для которой требуется выполнить аналогичные действия).
6. Таким образом, получаем заполненный столбец с новыми наименованиями, включающими данные по размеру и полу.

Аргументы функции без разделителей

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

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

Указание разделителя в отдельной ячейке

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

Например, мы добавляем запятую и пробел (“, “) в ячейку B16.

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

Но здесь есть один нюанс. Чтобы при копировании формулы функции на другие ячейки не произошло нежелательного сдвига адреса ячейки с разделителем, ссылку на нее нужно сделать абсолютной. Для этого выделив адрес в поле соответствующего аргумента нажимаем кнопку F4. Напротив обозначений столбца и строки появятся символы “$”. После этого можно нажимать кнопку OK.

Визуально в ячейке результат никак не будет отличаться от полученного ранее.

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

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

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

Обратная функция СЦЕПИТЬ в Excel

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

• ЛЕВСИМВ (отображает заданную часть символов с начала строки);
• ПРАВСИМВ (отображает заданную часть символов с конца строки);
• ПСТР (отображает указанную часть символов, начиная с заданной позиции) и другие.

1. Извлечем первое слово из строки, применив функцию ЛЕВСИМВ. Аргументы – «текст» (ссылка на строку с искомым значением), «количество знаков» (число символов, которые нужно извлечь).
2. Извлечем последнее слово из строки с помощью функции ПРАВСИМВ. Синтаксис похожий (как в предыдущем примере). Только количество знаков считается с конца.
3. Извлечем из записи в строке фамилию (второе слово) с помощью функции ПСТР. Аргументы – «текст» (ссылка на строку), «начальная позиция» (номер символа, с которого программа начнет считать число знаков); «количество знаков». В примере: Е2 – «текст», с 9 знака функция вернет 8 символов.

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

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

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

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

• В Windows используйте СИМВОЛ(10), где 10 — это код ASCII для перевода строки.
• В системе Mac используйте СИМВОЛ(13), где 13 — код ASCII для возврата каретки.

В этом примере у нас есть части адреса в столбцах от A до F, и мы соединяем их в столбце G с помощью оператора конкатенации «&». Объединенные элементы разделяются запятой («,»), пробелом (» «) и переводом строки СИМВОЛ(10):

Примечание. При использовании разрывов строк для разделения объединенных значений необходимо включить параметр «Перенос текста», чтобы результат отображался правильно. Для этого нажмите Ctrl + 1, чтобы открыть диалоговое окно «Формат», перейдите на вкладку «Выравнивание» и установите флажок « Переносить текст» .

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

• Двойные кавычки («) — СИМВОЛ(34)
• Косая черта (/) — СИМВОЛ(47)
• Звездочка (*) — СИМВОЛ(42)

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

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

Функция СЦЕПИТЬ для большого количества ячеек

Один из вариантов заключатся в том, чтобы в качестве ссылки на ячейки указать массив данных. Следует сразу отметить, что данные могут располагаться по горизонтали или вертикали. Если данные расположены в одной строке, то делаем следующее. Для примера, отдельные по ячейкам слова находится в 5-й строке. Теперь в пустой ячейке указываем весь диапазон для соединения и через амперсанд (&) добавляем пробел (» «).

Затем нажимаем F9, для того, чтобы формула выдала результат вычисления, в нашем случае это будет массив.

Как видно к каждому слову добавился пробел, а между словами стоит точка с запятой – как раз то, что нужно для вставки в формулу СЦЕПИТЬ. Теперь убираем лишние скобки и вставляем этот массив в формулу. Нажимаем Enter.

Данные могут быть расположены не по горизонтали, (как в примере выше), а по вертикали. В этом случае в получаемом массиве значения будут разделены двоеточием. Придется сделать один лишний шаг, чтобы заменить их на точку с запятой (через Найти и заменить, горячая клавиша Ctrl + H, либо еще быстрее — использовать функцию ТРАНСП, чтобы из горизонтального массива сделать вертикальный).

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

Поэтому переходим к следующему способу. Он, признаться, выглядит не очень эстетично. Зато дешево, надежно, и практично! (с) Во всяком случае прием помогает решить проблему. А это самое главное. Нам потребуется один дополнительный столбец (или строка, смотря как расположены исходные данные для соединения).

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

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