Учебник по Excel: Объединение столбцов, строк, ячеек

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

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

Нажмите, чтобы скачать пример файла

1 Объединение столбцов/строк в одну ячейку

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

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

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

1.11 Использование амперсанда (&)

В Excel символ амперсанда (&) обычно используется для объединения текстов.

Пример: Объединить имя (столбец A) и фамилию (столбец B) в полное имя

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

=A2&" "&B2

В формуле & используется для объединения текстов, A2 и B2 — это две объединяемые ячейки, " " — разделитель (пробел) между ними. Если вы хотите использовать запятую в качестве разделителя, просто укажите запятую в двойных кавычках ",".

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

Пример: Объединить номер (строка15) и имя (строка16) в одну ячейку

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

1.12 Использование функции CONCATENATE (Excel2016 или более ранние версии)

Если вы используете Excel2016 или более ранние версии, функция CONCATENATE поможет вам.

Для получения подробной информации о функции CONCATENATE посетите: CONCATENATE.

Пример: Объединить имена (столбец F) и адрес (столбец G) в один столбец

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

=CONCATENATE(F2,",",G2)

В формуле F2 и G2 — это две объединяемые ячейки, "," — разделитель (запятая) между ними. Если вы хотите использовать пробел в качестве разделителя, просто укажите пробел в двойных кавычках " ".

Нажмите Enter для получения объединённого результата, затем перетащите маркер автозаполнения вниз для остальных результатов.

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

1.13 Использование функции CONCAT или TEXTJOIN (Excel2019 или Excel365)

Если вы используете Excel2019 или Excel365, функции CONCAT и TEXTJOIN могут быть более удобным выбором.

Для получения подробной информации о функции CONCAT посетите: CONCAT.

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

=CONCAT(A21," ",B21)

В формуле A21 и B21 — это две объединяемые ячейки, " " — разделитель (пробел) между ними. Если вы хотите использовать другой разделитель, просто укажите его в двойных кавычках.

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

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

Для получения подробной информации о функции TEXTJOIN посетите: TEXTJOIN.

Чтобы объединить столбцы или строки с помощью TEXTJOIN:

=TEXTJOIN(",",TRUE,E21:G21))

В формуле E21:G21 — это непрерывный диапазон, который нужно объединить. " " — разделитель (пробел) между значениями. Если вы хотите использовать другой разделитель, просто укажите его в двойных кавычках. Логическое значение TRUE указывает игнорировать пустые ячейки при объединении. Если нужно объединять с пустыми, замените TRUE на FALSE.

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

Игнорировать пустые значения

Включать пустые значения

1.14 Использование Блокнота (только для объединения столбцов)

1. Использование Блокнота также позволяет объединять столбцы (не подходит для строк).

Выделите диапазон, который хотите объединить в один столбец, и нажмите Ctrl + C для копирования диапазона.

2. Откройте Блокнот, и нажмите Ctrl + V для вставки скопированного диапазона.

3. Выделите табуляцию между двумя текстами в Блокноте и нажмите Ctrl + H чтобы открыть Заменить функцию, символ табуляции уже будет в поле Найти текстовое поле.

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

5. Теперь выделите тексты в Блокноте и нажмите Ctrl + C чтобы скопировать их, затем перейдите в Excel, выберите ячейку и нажмите Ctrl + V для вставки объединённых результатов.

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

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

Прежде всего, учтите, что в Excel функция CHAR() позволяет вставлять символы, которые сложно ввести вручную, например, CHAR(10) возвращает разрыв строки.

Затем используйте CHAR(10) в формулах, приведённых выше, чтобы объединить столбцы или строки с разрывом строки в качестве разделителя:

Амперсанд:

=A49&CHAR(10)&B49

Функция CONCATENATE:

=CONCATENATE(A49,CHAR(10),B49)

Функция CONCAT

=CONCAT(A49,CHAR(10),B49)

Функция TEXTJOIN

=TEXTJOIN(CHAR(10),TRUE,A49:B49)

После ввода одной из формул выше выделите результаты и нажмите Главная > Перенос текста.

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

Если вы хотите объединить строки в одном столбце в одну ячейку, функция Выровнять по ширине в Excel поможет вам. Выделите столбец с несколькими строками и выберите Главная > Заполнить > Выровнять по ширине — строки будут объединены в верхнюю ячейку диапазона с пробелом в качестве разделителя.

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

1.21 Использование функции TEXTJOIN (Excel2019 или Excel365)

Если вы используете Excel2019 или Excel365, новая функция TEXTJOIN легко справится с этой задачей.

Для получения подробной информации о функции TEXTJOIN посетите: TEXTJOIN.

Пример: Объединить имя (столбец A), номер (столбец B) и домен (столбец C) в одну ячейку для создания e-mail, при этом некоторые номера могут быть пустыми.

Выберите ячейку для размещения объединённого результата и введите формулу:

=TEXTJOIN("",TRUE,A2:C2)

В формуле A2:C2 — диапазон объединяемых ячеек, "" — разделитель (отсутствует), если нужен другой разделитель, укажите его в двойных кавычках, например, ",". Логическое значение TRUE означает игнорировать пустые ячейки. Если нужно объединять с пустыми, замените TRUE на FALSE.

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

1.22 Использование амперсанда (&)

Если вы используете Excel2016 или более ранние версии, используйте амперсанд (&) для поочерёдного объединения текстов, вручную пропуская пустые значения.

Выберите ячейку для размещения объединённого результата и введите формулу:

=A10&C10

В формуле & используется для объединения текстов, A10 и C10 — объединяемые ячейки. Если хотите использовать запятую как разделитель, укажите её в двойных кавычках "," и соедините с помощью & между двумя текстами.

Нажмите Enter для получения объединённого результата. Затем измените ссылки в формуле для получения следующего результата.

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

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

1. Нажмите Alt + F11 для открытия окна Microsoft Visual Basic for Applications.

2. Нажмите Вставка > Модуль для добавления нового модуля.

3. Скопируйте и вставьте приведённый ниже код в новый модуль и сохраните его.

VBA: Объединить ячейки с пропуском пустых

Function Concatenatecells(ConcatArea As Range) As String
'updateby Extendoffice
  For Each n In ConcatArea: nn = IIf(n = "", nn & "", nn & n & "_"): Next
  Concatenatecells = Left(nn, Len(nn) - 1)
End Function

4. Вернитесь на лист, выберите ячейку для размещения результата и введите формулу

=Concatenatecells(A15:C15)

В формуле A15:C15 — диапазон объединяемых ячеек. В коде VBA "_" — разделитель, который используется для разделения текстов в результате, вы можете изменить его по необходимости.

Если у вас установлен Kutools для Excel, функция Объединить строки/столбцы/ячейки и сохранить значения значительно упростит различные задачи по объединению.

1. Выберите ячейки, которые хотите объединить, и ячейки для размещения результата.

2. Нажмите Kutools > Объединить и разделить > Объединить строки/столбцы/ячейки и сохранить значения.

3. В появившемся диалоге укажите параметры следующим образом:

1) Укажите, объединять ли столбцы, строки или все ячейки в одну.

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

2) Укажите разделитель для текстов в объединённом результате.

3) Укажите расположение объединённого результата (опция недоступна при выборе варианта "Объединить в одну ячейку")

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

Левая ячейка Например.	Правая ячейка Например.

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

Верхняя ячейка Например.	Нижняя ячейка Например.

4) Укажите действие с объединёнными результатами.

4. Нажмите Ok или Применить для завершения объединения.

Сохранить содержимое этих ячеек

Удалить содержимое этих ячеек

Объединить эти ячейки

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

Для получения подробной информации о Kutools для Excel посетите Kutools for Excel.

Для бесплатного пробного периода Kutools for Excel на 30 дней скачайте его сейчас.

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

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

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

Для получения подробной информации о функции IF посетите: IF

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

=IF(A2="",B2,A2)

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

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

1.42 Использование VBA

Вот код VBA, который также справится с этой задачей.

1. Нажмите Alt + F11 для открытия окна Microsoft Visual Basic for Applications, затем выберите Вставка > Модуль для создания нового пустого модуля.

2. Скопируйте и вставьте приведённый ниже код в новый модуль.

VBA: Объединить два столбца, если есть пустые значения

Sub MergebyBlank() 'UpdatebyExtendoffice20220506 Dim xRg1, xRg2, xRgUser As Range Dim xRg As Range Dim xWsh As Worksheet Dim xCount, xFNum As Integer Set xRg1 = Range("A:A") Set xRg2 = Range("B:B") Set xWsh = xRg1.Worksheet Set xRgUser = xWsh.UsedRange Set xRg1 = Intersect(xRgUser, xRg1) Set xWsh = xRg2.Worksheet Set xRgUser = xWsh.UsedRange Set xRg2 = Intersect(xRgUser, xRg2) xCount = xRg1.Count If (xCount > xRg2.Count) Then xCount = xRg2.Count End If For xFNum = 1 To xCount If (xRg1.Item(xFNum).Value = "") Then If (xRg2.Item(xFNum).Value <> "") Then xRg1.Item(xFNum).Value = xRg2.Item(xFNum).Value End If ElseIf (xRg2.Item(xFNum).Value = "") Then If (xRg1.Item(xFNum).Value <> "") Then xRg2.Item(xFNum).Value = xRg1.Item(xFNum).Value End If End If Next End Sub 

В коде A:A и B:B — это два объединяемых столбца, просто измените их по необходимости.

3. Затем нажмите F5 или нажмите Выполнить кнопку, после чего столбец A будет заполнен значениями из столбца B, если в A значение пустое.

2 Объединение строк с одинаковым ID

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

В этой части приведены методы объединения строк с одинаковым ID и разделением запятой, как показано на скриншоте ниже:

2.11 Использование VBA

Здесь приведён код VBA, который справится с этой задачей

1. Нажмите Alt + F11 для открытия окна Microsoft Visual Basic for Applications, затем выберите Вставка > Модуль для создания нового пустого модуля.

2. Скопируйте и вставьте приведённый ниже код в новый модуль.

VBA: Объединить строки с одинаковым ID

Sub Combine_Rows() 'UpdatebyExtendoffice20220506 Dim xRg As Range Dim xRows As Long Dim I As Long, J As Long, K As Long On Error Resume Next Set xRg = Application.InputBox("Select Range:", "Kutools For Excel", Selection.Address, , , , , 8) Set xRg = Range(Intersect(xRg, ActiveSheet.UsedRange).Address) If xRg Is Nothing Then Exit Sub xRows = xRg.Rows.Count For I = xRows To 2 Step -1 For J = 1 To I - 1 If xRg(I, 1).Value = xRg(J, 1).Value And J <> I Then For K = 2 To xRg.Columns.Count If xRg(J, K).Value <> "" Then If xRg(I, K).Value = "" Then xRg(I, K) = xRg(J, K).Text Else xRg(I, K) = xRg(I, K).Text & "," & xRg(J, K).Text 'here uses comma as separator, you can change it to others as you need End If End If Next xRg(J, 1).EntireRow.Delete I = I - 1 J = J - 1 End If Next Next ActiveSheet.UsedRange.Columns.AutoFit End Sub

3. Затем нажмите F5 или нажмите Выполнить кнопку, после чего появится диалоговое окно Kutools for Excel для выбора таблицы, в которой нужно объединить строки с одинаковым ID.

4. Нажмите OK. Теперь выбранная таблица объединена по одинаковым ID.

2.12 Использование функции IF для добавления вспомогательных столбцов

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

1. Отсортируйте ID по возрастанию. Выделите столбец ID, нажмите Данные > Сортировка по возрастанию.

2. Затем в Предупреждение о сортировке диалоге выберите Развернуть выделение, нажмите Сортировать.

Теперь одинаковые ID отсортированы вместе.

3. В соседнем столбце используйте функцию IF так:

=IF(A17=A18,C17&", "&B18,B18)

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

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

5. В следующем столбце введите формулу:

=IF(A18<>A19,"Merged","")

В формуле A18 и A19 — две соседние ячейки в столбце ID. Если значения не равны, возвращается "Merged", иначе — пусто.

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

7. Выделите последний столбец формул с заголовком и нажмите Данные > Фильтр.

8. Нажмите Фильтр кнопку и отметьте Merged флажок в выпадающем меню, нажмите OK.

Теперь отфильтрованы только объединённые строки, скопируйте их и вставьте в другое место.

Для получения подробной информации о функции IF посетите: IF.

Если у вас установлен Kutools for Excel, функция Расширенное объединение строк легко справится с этой задачей.

1. Выделите таблицу и нажмите Kutools > Объединить и разделить > Расширенное объединение строк.

2. В диалоге Расширенное объединение строк выполните следующие действия:

1) Выберите столбец ID и установите его как Основной ключ;

2) Выберите столбец, значения которого нужно объединить с разделителем, нажмите Объединить и выберите разделитель.

3) Нажмите Ok.

Теперь строки объединены по одинаковому ID.

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

Для получения подробной информации об этой функции посетите Расширенное объединение строк.

Для получения подробной информации о Kutools для Excel посетите Kutools for Excel.

Для бесплатного пробного периода Kutools for Excel на 30 дней скачайте его сейчас.

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

2.21 Использование функции Консолидация

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

1. Выберите ячейку для размещения объединённого результата и нажмите Данные > Консолидация.

2. В диалоге Консолидация выполните следующие действия:

1) Выберите Умные функции по необходимости;

2) Нажмите стрелку для выбора таблицы;

3) Нажмите Добавить, чтобы добавить выбранный диапазон в список Все ссылки;

4) Отметьте флажки Верхняя строка и Самый левый столбец;

5) Нажмите OK.

Теперь таблица просуммирована по одинаковым ID.

2.22 Использование VBA

Вот VBA, который также объединяет строки с одинаковым ID и суммирует значения.

1. Нажмите Alt + F11 для открытия Microsoft Visual Basic for Applications окна, затем нажмите Вставка > Модуль для создания нового пустого модуля.

2. В новом модуле скопируйте и вставьте приведённый ниже код VBA.

VBA: Объединить дублирующиеся строки и просуммировать

Sub CombineRows()
'Updateby Extendoffice
Dim WorkRng As Range
Dim Dic As Variant
Dim arr As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set Dic = CreateObject("Scripting.Dictionary")
arr = WorkRng.Value
For i = 1 To UBound(arr, 1)
    Dic(arr(i, 1)) = Dic(arr(i, 1)) + arr(i, 2)
Next
Application.ScreenUpdating = False
WorkRng.ClearContents
WorkRng.Range("A1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.keys)
WorkRng.Range("B1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.items)
Application.ScreenUpdating = True
End Sub

3. Затем нажмите F5 или нажмите Выполнить кнопку, затем появится диалоговое окно для выбора используемой таблицы, после чего нажмите OK.

Теперь выбранный диапазон объединён по одинаковым значениям и просуммирован.

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

2.23 Использование удобного инструмента – Расширенное объединение строк

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

1. Выделите таблицу и нажмите Kutools > Объединить и разделить > Расширенное объединение строк.

2. В диалоге Расширенное объединение строк выполните следующие действия:

1) Выберите столбец ID и установите его как Основной ключ;

2) Выберите столбец, значения которого нужно объединить с разделителем, нажмите Объединить и выберите разделитель.

3) Выберите столбец для вычисления, нажмите Вычислить и выберите нужное вычисление.

4) Нажмите Ok.

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

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

Для получения подробной информации об этой функции посетите Расширенное объединение строк.

Для получения подробной информации о Kutools для Excel посетите Kutools for Excel.

Для бесплатного пробного периода Kutools for Excel на 30 дней скачайте его сейчас.

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

2.31 Использование VBA

В Excel нет встроенной функции для этой задачи, но здесь приведён VBA, который справится с этим.

1. Нажмите Alt + F11 для открытия окна Microsoft Visual Basic for Applications, затем выберите Вставка > Модуль для создания нового пустого модуля.

2. В новом модуле скопируйте и вставьте приведённый ниже код VBA.

VBA: Объединить соседние строки с одинаковым значением

Sub MergeSameCell()
'Updateby Extendoffice
Dim Rng As Range, xCell As Range
Dim xRows As Integer
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
xRows = WorkRng.Rows.Count
For Each Rng In WorkRng.Columns
    For i = 1 To xRows - 1
        For j = i + 1 To xRows
            If Rng.Cells(i, 1).Value <> Rng.Cells(j, 1).Value Then
                Exit For
            End If
        Next
        WorkRng.Parent.Range(Rng.Cells(i, 1), Rng.Cells(j - 1, 1)).Merge
        i = j - 1
    Next
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

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

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

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

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

Если у вас установлен Kutools for Excel, функция Объединить одинаковые ячейки в Kutools for Excel выполнит задачу в один шаг.

Выделите столбец, в котором нужно объединить одинаковые значения, затем нажмите Kutools > Объединить и разделить > Объединить одинаковые ячейки.

Теперь соседние ячейки с одинаковыми значениями объединены.

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

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

Для получения подробной информации об этой функции посетите Разъединить ячейку.

Для получения подробной информации о Kutools для Excel посетите Kutools for Excel.

Для бесплатного пробного периода Kutools for Excel на 30 дней скачайте сейчас.

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

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

Пример: Объединить диапазон A1:C3

3.11 Использование амперсанда (&)

В Excel символ амперсанда (&) обычно используется для объединения текстов.

Выберите ячейку для размещения объединённого результата и введите формулу:

=A1&", "&B1&", "&C1&", "&A2&", " &B2&", "&C2&", "&A3&", "&B3&", "&C3

В формуле & используется для объединения текстов, ", " — разделитель (запятая + пробел) между значениями. Если нужен другой разделитель, укажите его в двойных кавычках.

Нажмите клавишу Enter для получения объединённого результата.

3.12 Использование функции CONCATENATE (Excel2016 или более ранние версии)

Если вы используете Excel2016 или более ранние версии, функция CONCATENATE поможет вам.

Для получения подробной информации о функции CONCATENATE посетите: CONCATENATE

Выберите ячейку для размещения объединённого результата и введите формулу:

=CONCATENATE(A1,", ",B1,", ",C1,", ",A2,", ",B2,", ",C2,", ",A3,", ",B3,", ",C3)

В формуле A1, B1…,C3 — объединяемые значения. ", " — разделитель (запятая + пробел) между значениями. Если нужен другой разделитель, укажите его в двойных кавычках.

Затем нажмите клавишу Enter для получения объединённого результата.

3.13 Использование функции CONCAT или TEXTJOIN (Excel2019 или Excel365)

Если вы используете Excel2019 или Excel365, функции CONCAT и TEXTJOIN могут быть более удобным выбором.

Для получения подробной информации о функции CONCAT посетите: CONCAT.

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

=CONCAT(A1,", ",B1,", ",C1,", ",A2,", ",B2,", ",C2,", ",A3,", ",B3,", ",C3)

В формуле A1, B1…,C3 — объединяемые значения. ", " — разделитель (запятая + пробел) между значениями. Если нужен другой разделитель, укажите его в двойных кавычках.

Затем нажмите клавишу Enter для получения объединённого результата.

Для получения подробной информации о функции TEXTJOIN посетите: TEXTJOIN.

Чтобы объединить столбцы или строки с помощью TEXTJOIN:

=TEXTJOIN(", ",TRUE,A1:C3)

В формуле A1:C3 — непрерывный диапазон для объединения. ", " — разделитель (запятая + пробел) между значениями. Если нужен другой разделитель, укажите его в двойных кавычках. Логическое значение TRUE означает игнорировать пустые ячейки. Если нужно объединять с пустыми, замените TRUE на FALSE.

Затем нажмите клавишу Enter для получения объединённого результата.

Если вы хотите использовать разрыв строки как разделитель, используйте CHAR(10) в формуле, например =TEXTJOIN(CHAR(10),TRUE,A1:C3), затем отформатируйте ячейку как Перенос текста.

3.14 Использование функции Объединить строки/столбцы/ячейки и сохранить значения

Если у вас установлен Kutools for Excel, функция Объединить строки/столбцы/ячейки и сохранить значения быстро справится с этой задачей.

1. Выделите диапазон ячеек, который хотите объединить в одну ячейку, затем нажмите Kutools > Объединить и разделить > Объединить строки/столбцы/ячейки и сохранить значения.

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

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

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

Для получения подробной информации о Kutools для Excel посетите Kutools for Excel.

Для бесплатного пробного периода Kutools for Excel на 30 дней скачайте его сейчас.

Транспонировать ячейки в один столбец

Транспонировать ячейки в одну строку

3.21 Присвоение имени диапазону и использование функции INDEX (только для одного столбца)

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

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

2. В появившемся Новое имя диалоге введите имя в поле Имя и нажмите OK.

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

=INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1)

Для получения подробной информации о функции INDEX посетите здесь.

4. Нажмите Enter и перетащите маркер автозаполнения вниз, пока не появится ошибка #REF!.

5. Удалите ошибку, и диапазон ячеек будет объединён в один столбец.

3.22 VBA (только для одного столбца)

Для объединения ячеек в один столбец приведён VBA, который также справится с этим.

1. Нажмите Alt + F11 для открытия окна Microsoft Visual Basic for Applications, затем выберите Вставка > Модуль для создания нового пустого модуля.

2. Скопируйте и вставьте приведённый ниже код VBA в пустой модуль.

VBA: Преобразовать диапазон в столбец

Sub ConvertRangeToColumn()
'UpdatebyExtendoffice
Dim Range1 As Range, Range2 As Range, Rng As Range
Dim rowIndex As Integer
xTitleId = "KutoolsforExcel"
Set Range1 = Application.Selection
Set Range1 = Application.InputBox("Source Ranges:", xTitleId, Range1.Address, Type:=8)
Set Range2 = Application.InputBox("Convert to (single cell):", xTitleId, Type:=8)
rowIndex = 0
Application.ScreenUpdating = False
For Each Rng In Range1.Rows
    Rng.Copy
    Range2.Offset(rowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
    rowIndex = rowIndex + Rng.Columns.Count
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

3. Затем нажмите F5 или нажмите Выполнить кнопку, появится диалоговое окно для выбора диапазона ячеек, нажмите OK.

4. Появится ещё одно диалоговое окно для выбора ячейки для результата, нажмите OK.

3.23 Использование удобного инструмента – Преобразовать диапазон

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

1. Выделите диапазон ячеек и нажмите Kutools > Диапазон > Преобразовать диапазон.

2. В Преобразовать диапазон диалоге отметьте Диапазон в одну колонку или Диапазон в одну строку по необходимости. Нажмите OK.

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

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

Для получения подробной информации об этой функции посетите Преобразовать диапазон.

Для получения подробной информации о Kutools для Excel посетите Kutools for Excel.

Для бесплатного пробного периода Kutools for Excel на 30 дней скачайте его сейчас.

Если в таблице с несколькими столбцами есть дубликаты, как объединить столбцы в один без дубликатов, как показано на скриншоте ниже?

В этой части приведены три различных метода решения задачи.

3.31 Копировать, вставить и удалить дубликаты

В Excel общий способ решения — копировать и вставлять столбцы по одному, затем удалить дубликаты.

1. Выделите первый столбец и нажмите Ctrl + C для копирования, затем выберите ячейку для вставки и нажмите Ctrl + V для вставки.

2. Повторите шаг1 для остальных столбцов, вставляя их под первым столбцом.

3. Затем выделите объединённый столбец и нажмите Данные > Удалить дубликаты, затем в Удалить дубликаты диалоге отметьте имя столбца и нажмите OK.

4. Появится диалоговое окно с уведомлением об удалении дубликатов. Нажмите OK для закрытия, и в объединённом столбце останутся только уникальные значения.

Если столбцов сотни, копировать и вставлять по одному долго. Если у вас установлен Kutools for Excel, функция Преобразовать диапазон быстро преобразует диапазон в столбец, затем используйте функцию Удалить дубликаты в Excel.

Выделите диапазон столбцов и нажмите Kutools > Диапазон > Преобразовать диапазон.

Затем отметьте Диапазон в одну колонку и нажмите OK для выбора ячейки для объединённого столбца.

Затем примените Удалить дубликаты для удаления повторяющихся значений.

Для получения подробной информации об этой функции посетите Преобразовать диапазон.

Для получения подробной информации о Kutools для Excel посетите Kutools for Excel.

Для бесплатного пробного периода Kutools for Excel на 30 дней скачайте его сейчас.

3.32 Использование VBA

Также приведён код VBA, который решает эту задачу.

1. Нажмите Alt + F11 для открытия окна Microsoft Visual Basic for Applications, затем выберите Вставка > Модуль для создания нового пустого модуля.

2. Скопируйте и вставьте приведённый ниже код в новый модуль.

VBA: Объединить столбцы в один без дубликатов

Sub FindUniques()
'UpdatebyExtendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set dic = CreateObject("Scripting.Dictionary")
For j = 1 To InputRng.Columns.Count
    For i = 1 To InputRng.Rows.Count
        xValue = InputRng.Cells(i, j).Value
        If xValue <> "" And Not dic.Exists(xValue) Then
            OutRng.Value = xValue
            dic(xValue) = ""
            Set OutRng = OutRng.Offset(1, 0)
        End If
    Next
Next
End Sub

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

4. Во втором появившемся диалоговом окне выберите ячейку для объединённого столбца и нажмите OK.

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

Если есть два столбца, один из которых отформатирован особым образом (например, пользовательская дата mm/dd/yyyy), при объединении обычными методами форматирование будет потеряно, как показано на скриншоте ниже:

В этой части приведены способы объединения ячеек с сохранением форматирования.

3.41 Встраивание функции TEXT в формулу

Функция TEXT используется для преобразования числа в текст в определённом формате. Здесь мы сначала преобразуем ячейку с числом в нужный формат, а затем объединяем её с другими ячейками с помощью &, функции CONCATENATE, CONCAT или TEXTJOIN.

Для подробностей по общему объединению ячеек в одну вернитесь к пункту1.1.

Для получения подробной информации о функции TEXT посетите TEXT function.

В качестве примера рассмотрим использование функции TEXT внутри функции CONCAT.

Скопируйте и вставьте формулу ниже в ячейку для размещения результата:

=CONCAT(TEXT(A2,"mm/dd/yyyy hh:mm")," ",B2)

В формуле A2 — ячейка, формат которой нужно сохранить, “mm/dd/yyyy hh:mm” — используемый формат, B2 — другая объединяемая ячейка. " " — разделитель (пробел). Вы можете изменить ссылки, формат и разделитель по необходимости.

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

3.42 Использование Microsoft Word

1. Выделите таблицу для объединения ячеек и нажмите Ctrl + C для копирования.

2. Откройте пустой Word документ, нажмите Ctrl + V для вставки, затем щёлкните по таблице в документе — появится крестик в правом верхнем углу таблицы.

3. Нажмите вкладку Макет и затем выберите Преобразовать в текст в группе Данные в появившемся Преобразовать таблицу в текст диалоге укажите разделитель для столбцов. Нажмите OK.

Теперь содержимое таблицы в Word преобразовано в текст.

4. Выделите преобразованный текст и нажмите Ctrl + C для копирования, затем вернитесь в Excel, выберите пустую ячейку и нажмите Ctrl + V для вставки объединённого результата.

3.43 Использование удобного инструмента – Объединить и сохранить значения

Самый эффективный способ — использовать функцию Kutools for Excel Объединить строки/столбцы/ячейки и сохранить значения, где достаточно отметить опцию Использовать форматированные значения, чтобы объединённый результат сохранил форматирование данных.

1. Выделите таблицу, включая ячейки для результата, и нажмите Kutools > Объединить и разделить > Объединить строки/столбцы/ячейки и сохранить значения.

2. В появившемся диалоге укажите нужную операцию объединения и снимите флажок Использовать форматированные значения (по умолчанию опция включена). Нажмите Ok.

Теперь данные объединены с сохранением форматирования.

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

Для получения подробной информации о Kutools для Excel посетите Kutools for Excel.

Для бесплатного пробного периода Kutools for Excel на {module745} дней скачайте его сейчас.

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

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

Функция DATE используется для создания даты по году, месяцу и дню.

Для получения подробной информации о функции DATE посетите DATE function.

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

=DATE(A2,B2,C2)

В формуле A2, B2 и C2 — ячейки с годом, месяцем и днём соответственно.

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

Другие руководства по Excel:

---

Лучшие инструменты для повышения производительности Office

Kutools для Excel решает большинство ваших проблем и увеличивает вашу продуктивность на 80%

• Супер строка формул (легкое редактирование нескольких строк текста и формул); Режим чтения (удобное чтение и редактирование большого количества ячеек); Вставка в отфильтрованный диапазон...
• Объединение ячеек/строк/столбцов с сохранением данных; Разделение содержимого ячеек; Объединение дублирующихся строк с подсчетом суммы/среднего значения... Предотвращение дублирования ячеек; Сравнение диапазонов...
• Выбор дублирующихся или уникальных строк; Выбор пустых строк (все ячейки пустые); Супер поиск и нечеткий поиск во многих книгах; Случайный выбор...
• Точное копирование нескольких ячеек без изменения ссылок на формулы; Автоматическое создание ссылок на несколько листов; Вставка маркеров, флажков и многое другое...
• Избранные и быстрая вставка формул, диапазонов, диаграмм и изображений; Шифрование ячеек с паролем; Создание списка рассылки и отправка электронных писем...
• Извлечение текста, добавление текста, удаление по позиции, удаление пробелов; Создание и печать статистики страниц; Преобразование между содержимым ячеек и комментариями...
• Супер фильтр (сохранение и применение схем фильтрации к другим листам); Расширенная сортировка по месяцу/неделе/дню, частоте и другим параметрам; Специальный фильтр по жирному шрифту, курсиву...
• Объединение книг и листов; Объединение таблиц на основе ключевых столбцов; Разделение данных на несколько листов; Пакетное преобразование xls, xlsx и PDF...
• Группировка сводной таблицы по номеру недели, дню недели и другим параметрам... Отображение разблокированных, заблокированных ячеек разными цветами; Выделение ячеек, содержащих формулы/имена...

Читать далее... Полные функции 30-дневная бесплатная пробная версия... Приобрести... 

---

Office Tab - добавляет интерфейс с вкладками в Office и значительно упрощает вашу работу

• Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
• Открывайте и создавайте несколько документов в новых вкладках одного окна, а не в новых окнах.
• Увеличивает вашу продуктивность на 50% и экономит сотни кликов мышью каждый день!

Читать далее... Полные функции 30-дневная бесплатная пробная версия... Приобрести...