Руководство по Excel: Объединить столбцы, строки, ячейки

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

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

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

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

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

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

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

1,11 С использованием символа амперсанда (&)

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

Пример: объединение Имя (столбец A) и Фамилия (столбец B) в Полное имя

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

=A2&" "&B2

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

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

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

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

1,12 С использованием функции СЦЕПИТЬ (CONCATENATE) (Excel 2016 или более ранние версии)

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

Более подробную информацию о функции СЦЕПИТЬ (CONCATENATE) можно найти здесь: СЦЕПИТЬ.

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

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

=CONCATENATE(F2,",",G2)

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

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

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

1,13 С использованием функций СЦЕП (CONCAT) или ОБЪЕДИНИТЬ (TEXTJOIN) (Excel 2019 или Excel 365)

Если вы работаете в Excel 2019 или Excel 365, оптимальным решением станут функции СЦЕП (CONCAT) и ОБЪЕДИНИТЬ (TEXTJOIN).

Более подробную информацию о функции СЦЕП (CONCAT) можно найти здесь: СЦЕП.

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

=CONCAT(A21,« »,B21)

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

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

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

Более подробную информацию о функции ОБЪЕДИНИТЬ (TEXTJOIN) можно найти здесь: ОБЪЕДИНИТЬ.

Чтобы объединить Объединить столбцы или строки с помощью функции ОБЪЕДИНИТЬ (TEXTJOIN):

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

В формуле E21:G21 — это непрерывный диапазон, который нужно объединить. «  » (пробел) выступает в роли разделителя между текстами в итоговой ячейке. Хотите использовать другой разделитель? Просто укажите его внутри двойных кавычек «""». Логическое значение «ИСТИНА» означает, что пустые ячейки будут игнорироваться при объединении; если вы хотите включать их, замените «ИСТИНА» на «ЛОЖЬ».

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

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

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

1,14 С использованием Блокнота (только для объединения столбцов)

1. Объединить столбцы можно и с помощью Блокнота (этот способ не подходит для объединения строк).

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

2. Откройте Блокнот и нажмите клавиши Ctrl + V, чтобы вставить скопированный диапазон.

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

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

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

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

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

Прежде всего стоит помнить: в Excel функция СИМВОЛ() (CHAR) позволяет использовать символы, которые сложно ввести в формулу напрямую — например, СИМВОЛ(10) возвращает разрыв строки.

Затем используйте СИМВОЛ(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 (Excel 2019 или Excel 365)

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

Подробнее о функции TEXTJOIN — смотрите по ссылке: TEXTJOIN.

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

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

=TEXTJOIN(«»,TRUE,A2:C2)

В формуле A2:C2 — это диапазон с текстами, которые нужно объединить; «» — разделитель (в данном случае отсутствует) между текстами в результирующей ячейке. Чтобы добавить разделитель, просто укажите его в двойных кавычках, например ",". Логическое значение «ИСТИНА» означает, что пустые ячейки будут игнорироваться при объединении; если вы хотите включать и пустые ячейки, замените ИСТИНА на ЛОЖЬ.

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

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

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

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

=A10&C10

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

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

1,23 Использование пользовательской функции

Пользователям Excel 2016 и более ранних версий, которым нужно объединить несколько столбцов или строк, пропуская пустые ячейки, рекомендуется воспользоваться VBA для создания пользовательской функции — это позволит быстро и эффективно решить задачу.

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

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 в Excel, функция Объединить строки/столбцы/ячейки и сохранить значения окажет существенную помощь при выполнении самых разных задач объединения.

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

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

3. В появившемся диалоговом окне задайте параметры, как описано ниже:

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

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

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

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

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

Ячейка слева — пример.	Ячейка справа — пример.

При выборе опции Объединить строки можно указать размещение результата: «Ячейка сверху — Выберите диапазон» или «Ячейка снизу — Выберите диапазон».

Ячейка сверху — пример.	Ячейка ниже — пример.

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

4. Нажмите кнопку ОК или Применить, чтобы завершить объединение.

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

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

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

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

Дополнительные сведения о Kutools для Excel см. в статье Kutools для Excel.

Чтобы получить 30-дневную бесплатную пробную версию Kutools для Excel, загрузите её сейчас.

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

1,41 Использование функции ЕСЛИ

Функция ЕСЛИ позволяет проверить определённое условие: с её помощью вы можете определить, является ли ячейка пустой, и автоматически заполнить такие ячейки данными из другого столбца.

Подробную информацию о функции ЕСЛИ см. в статье: ЕСЛИ

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

=IF(A2=«»,B2,A2)

Затем нажмите клавишу Enter, чтобы получить первый результат, и перетащите маркер автозаполнения вниз — так вы получите все результаты.

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

1,42 Использование VBA

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

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

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 для Excel, предлагающее выбрать таблицу, строки которой нужно объединить по одинаковому ID.

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

2,12 Использование функции ЕСЛИ для добавления вспомогательных столбцов

Если у вас всего два столбца и вы хотите объединить строки с одинаковыми ID, разделив значения второго столбца запятыми, как показано на снимке экрана ниже, функция ЕСЛИ придёт вам на помощь.

1. Отсортируйте ID от А до Я. Выделите столбец с ID и нажмите Данные > По возрастанию.

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

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

3. В соседнем столбце примените функцию ЕСЛИ следующим образом:

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

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

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

5. Перейдите в соседний столбец и введите формулу следующим образом:

=IF(A18<,>,A19,«Merged»,«»)

В формуле A18 и A19 — это две соседние ячейки в столбце ID. Если их значения не совпадают, формула вернёт «Объединено», в противном случае — пустое значение.

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

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

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

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

Более подробную информацию о функции ЕСЛИ см. по адресу: ЕСЛИ.

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

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

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

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

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

3）нажмите ОК.

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

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

Подробную информацию об этой функции см. по адресу: Расширенное объединение строк.

Дополнительные сведения о Kutools для Excel см. по адресу: Kutools для Excel.

Чтобы получить 30-дневную бесплатную пробную версию Kutools для Excel, скачайте её сейчас.

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

2,21 Использование функции «Объединить»

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

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

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

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

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

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

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

5) Нажмите ОК.

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

2,22 Использование VBA

Приведённый ниже макрос VBA позволяет объединять строки с одинаковыми ID и суммировать соответствующие значения.

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

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 или кнопку Выполнить, после чего появится диалоговое окно для выбора используемой таблицы. Нажмите ОК.

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

Макрос VBA изменяет исходные данные — обязательно сохраните их копию перед использованием.

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

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

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

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

1) Выберите столбец с идентификаторами и задайте его как Первичный ключ;

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

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

4）Нажмите ОК.

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

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

Подробнее об этой функции см. на странице Расширенное объединение строк.

Дополнительную информацию о Kutools для Excel см. на странице Kutools для Excel.

Чтобы получить 30-дневную бесплатную пробную версию Kutools для Excel, пожалуйста, загрузите её сейчас.

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

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. После этого откроется диалоговое окно для выбора используемой таблицы — нажмите ОК.

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

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

2,32 С помощью удобного инструмента — Объединить одинаковые ячейки

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

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

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

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

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

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

Дополнительную информацию о Kutools для Excel см. на странице Kutools для Excel.

Чтобы получить 30-дневную бесплатную пробную версию Kutools для Excel, пожалуйста, загрузите её прямо сейчас!

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

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

Пример: Диапазон объединения A1:C3

3,11 С использованием символа амперсанда (&)

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

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

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

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

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

3,12 С использованием функции СЦЕПИТЬ (Excel 2016 и более ранние версии)

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

Подробнее о функции СЦЕПИТЬ см.:СЦЕПИТЬ

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

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

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

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

3. 13 С использованием функций ОБЪЕДИНИТЬ или ОБЪЕДИНИТЬ.ТЕКСТ (доступны в Excel 2019 и Excel 365)

Если вы используете Excel 2019 или Excel 365, лучше выбрать функции CONCAT или TEXTJOIN.

Подробнее о функции ОБЪЕДИНИТЬ см.: ОБЪЕДИНИТЬ.

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

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

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

Затем нажмите Enter, чтобы получить объединённый результат.

Подробнее о функции TEXTJOIN см. по ссылке:TEXTJOIN.

Чтобы объединить Объединить столбцы или строки с помощью функции ОБЪЕДИНИТЬ.ТЕКСТ:

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

В формуле A1:C3 — это непрерывный диапазон, который необходимо объединить. «, » (запятая с пробелом) служит разделителем между фрагментами текста в результирующей ячейке. Если нужен другой разделитель, просто укажите требуемый символ или строку символов в двойных кавычках («»). Логическое значение «ИСТИНА» означает, что при объединении пустые ячейки будут игнорироваться. Чтобы включать и пустые ячейки, замените «ИСТИНА» на «ЛОЖЬ».

Затем нажмите Enter, чтобы получить объединённый результат.

Если в качестве разделителя нужно использовать перевод строки, воспользуйтесь в формуле функцией CHAR(10), например: =TEXTJOIN(CHAR(10),ИСТИНА,A1:C3), а затем примените к ячейке с результатом форматирование «Перенос по словам».

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

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

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

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

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

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

Дополнительную информацию о Kutools для Excel см. на странице Kutools для Excel.

Чтобы получить 30-дневную бесплатную пробную версию Kutools для Excel, пожалуйста,загрузитееё сейчас.

Преобразование ячеек в один столбец

Преобразование ячеек в одну строку

3,21 Присвоение имени диапазону и использование функции ИНДЕКС (только для одного столбца)

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

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

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

3. После присвоения имени диапазону выделите ячейку, в которую следует поместить преобразованные данные, и используйте функцию ИНДЕКС следующим образом:

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

Подробнее о функции ИНДЕКС см. здесь.

4. Нажмите клавишу Enter и перетащите маркер автозаполнения вниз, пока не появится ошибка #ССЫЛКА!

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 для Excel её функция Преобразовать диапазон позволяет легко преобразовывать диапазон в один столбец или строку, а также наоборот — строку или столбец в диапазон.

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

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

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

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

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

Подробнее о Kutools для Excel — на странице Kutools для Excel.

Чтобы получить 30-дневную бесплатную пробную версию Kutools для Excel, пожалуйста, загрузите её сейчас.

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

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

3,31 Копирование, вставка и Удалить дубликаты

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

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

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

3. Затем выделите объединённый столбец, перейдите в меню Данные → Удалить дубликаты. В открывшемся диалоговом окне Удаление дубликатов установите флажок напротив имени столбца и нажмите OK.

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

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

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

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

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

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

Подробнее о Kutools для Excel — на соответствующей странице.Kutools для Excel.

Чтобы получить 30-дневную бесплатную пробную версию Kutools для Excel, пожалуйста,загрузитеЕё сейчас.

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 Встраивание функции ТЕКСТ в формулу

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

Подробнее об общих методах объединения ячеек в одну см. раздел 1,1.

Подробнее о функции ТЕКСТ см. описание функции ТЕКСТ.

Ниже приведён пример использования функции ТЕКСТ внутри функции ОБЪЕДИНИТЬ.

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

=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 для Excel: Объединить строки/столбцы/ячейки и сохранить значения. Достаточно установить флажок Использовать отформатированные значения при применении функции — и объединённый результат сохранит исходное форматирование данных.

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

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

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

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

Чтобы узнать больше о Kutools для Excel, перейдите по ссылке.

Чтобы получить {module745}-дневную бесплатную пробную версию Kutools для Excel, загрузите её сейчас.

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

3,51 С использованием функции ДАТА

Функция ДАТА позволяет создать дату на основе заданных значений года, месяца и дня.

Подробнее о функции ДАТА см. в статье «Функция ДАТА».

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

=DATE(A2,B2,C2)

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

Нажмите клавишу Enter, чтобы получить первую дату, а затем протяните маркер автозаполнения вниз — и все даты появятся автоматически.

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

---

Лучшие инструменты для повышения продуктивности в офисе

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

• Супер строка формул (удобное редактирование многострочного текста и формул); Режим чтения (удобный просмотр и редактирование большого количества ячеек); Вставка в диапазон фильтрации…
• Объединение ячеек, строк или столбцов с сохранением данных; разделение содержимого ячеек;Объединение дублирующихся строк с суммированием или усреднением… предотвращение дублирования записей в ячейках;Сравнение диапазонов…
• Выбор дубликатов или уникальных строк; выбор пустых строк (все ячейки пустые); улучшенный и нечёткий поиск по множеству книг; случайный выбор…
• Точная копия нескольких ячеек без изменения ссылок в формулах; автоматическое создание ссылок на несколько листов; вставка маркеров, флажков и многое другое…
• Избранные и быстрая вставка формул, диапазонов, диаграмм и изображений; Защита ячеек паролем; Создание списка рассылкии отправка электронных писем…
• Извлечение текста, добавление текста, удаление символов в определённой позиции, удаление пробелов; создание и печать статистики по странице данных; преобразование между содержимым ячеек и примечаниями…
• Суперфильтр (сохранение и применение схем фильтрации к другим листам); расширенная сортировка по месяцам, неделям, дням, частоте и другим параметрам; специальный фильтр по полужирному, курсиву…
• Объединение книг и листов; объединение таблиц на основе ключевого столбца; разделение данных на несколько листов; пакетное преобразование XLS, XLSX и PDF…
• Сводная таблица: группировка по номеру недели, дню недели и многому другому… Показ разблокированных и заблокированных ячеек разными цветами; Выделение ячеек, содержащих формулы или имена…

Подробнее…Полный функционал, бесплатная пробная версия на 30 дней…Купить… 

---

Office Tab — добавляет вкладки в Office и значительно упрощает вашу работу

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

Подробнее…Полный функционал, бесплатная пробная версия на 30 дней…Купить…