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

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

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

Нажмите, чтобы загрузить образец файла

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

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

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

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

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

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

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

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

=A2&" "&B2

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

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

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

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

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

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

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

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

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

= СЦЕПИТЬ (F2,",",G2)

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

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

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

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

Если вы используете Excel 2019 или Excel 365, функция СЦЕП и функция ТЕКСТОВОЕ СОЕДИНЕНИЕ могут быть лучшим выбором.

Дополнительные сведения о функции CONCAT см. на странице: CONCAT.

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

=СЦЕП(A21," ",B21)

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

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

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

Дополнительные сведения о функции TEXTJOIN см. на странице: ТЕКСТ ПРИСОЕДИНИТЬСЯ.

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

=ТЕКСТСОЕДИНЕНИЕ(",",ИСТИНА,E21:G21))

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

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

Игнорировать пробелы

Включить пробелы

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

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

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

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

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

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

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

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

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

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

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

Символ амперсанда:

=A49&CHAR(10)&B49

СЦЕПИТЬ функцию:

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

Функция CONCAT

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

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

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

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

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

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

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

1.21 Использование функции ТЕКСТОВОЕ СОЕДИНЕНИЕ (Excel 2019 или Excel 365)

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

Дополнительные сведения о функции TEXTJOIN см. на странице: ТЕКСТ ПРИСОЕДИНИТЬСЯ.

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

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

=ТЕКСТСОЕДИНЕНИЕ("",ИСТИНА,A2:C2)

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

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

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

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

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

=A10&C10

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

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

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

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

1. Нажмите другой + 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 for Excel в Экселе, Комбинируйте строки, столбцы или ячейки без потери данных функция очень помогает в видах совмещения работ.

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

2. Нажмите Кутулс > Слияние и разделение > Комбинируйте строки, столбцы или ячейки без потери данных.

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

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

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

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

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

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

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

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

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

4) Укажите операцию над объединенными результатами.

4. Нажмите Ok or Применить чтобы закончить комбинацию.

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

Удалить содержимое объединенных ячеек

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

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

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

Для 30-дневной бесплатной пробной версии Kutools for Excel, пожалуйста скачать это сейчас.

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

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

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

Дополнительные сведения о функции ЕСЛИ см. на веб-сайте: IF

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

=ЕСЛИ(А2="",В2,А2)

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

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

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

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

1. Нажмите другой + 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 ключ или щелкните Run   кнопку, то столбец A будет заполнен значением в столбце B, если значение в столбце A пусто.

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

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

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

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

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

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

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

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

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

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

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

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

1. Отсортируйте идентификатор от А до Я. Выберите столбец идентификатора, нажмите Данные > Сортировка от А до Я.

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

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

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

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

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

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

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

=ЕСЛИ(A18<>A19;"Объединено","")

 В формуле A18 и A19 являются двумя соседними ячейками в столбце идентификатора, если соседние ячейки не равны, он вернется к «Объединенному», в противном случае он вернется пустым.

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

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

8. Нажмите ФИЛЬТР кнопка и галочка Объединенные флажок в раскрывающемся меню, нажмите OK.

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

Подробнее о функции ЕСЛИ. пожалуйста, посетите: IF.

Если вы установили Kutools for Excel в Экселе, Расширенные ряды комбинирования может легко выполнить эту работу.

1. Выберите таблицу и нажмите Кутулс > Слияние и разделение > Расширенные ряды комбинирования.

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

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

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

3) нажмите Ok.

Теперь строки были объединены одним и тем же идентификатором.

Функция Advanced Combine Rows нарушит исходные данные. Перед использованием сохраните данные как копию.

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

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

Для 30-дневной бесплатной пробной версии Kutools for Excel, пожалуйста скачать это сейчас.

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

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

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

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

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

1) Выберите функции как вам нужно;

2) Щелкните стрелку, чтобы выбрать таблицу;

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

4) Отметьте галочками Верхний ряд и Левая колонка;

5) Нажмите OK.

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

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

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

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

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

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

2.23 Использование удобного инструмента — Advanced Combine Rows

Если в таблице есть более двух столбцов, которые вы хотите объединить и произвести расчеты, например, есть три столбца, первый содержит повторяющиеся названия продуктов, которые необходимо объединить вместе, второй содержит названия магазинов, которые необходимо быть объединены и разделены запятой, последний столбец содержит числа, которые необходимо суммировать на основе повторяющихся строк в первом столбце, как показано ниже, Kutools for Excel's Advanced Combine Rows может вам помочь.

1. Выберите таблицу и нажмите Кутулс > Слияние и разделение > Расширенные ряды комбинирования.

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

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

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

3) Выберите столбец, который вы хотите сделать расчет, нажмите Рассчитать и выберите один расчет.

4) нажмите Ok.

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

 Функция Advanced Combine Rows нарушит исходные данные. Перед использованием сохраните данные как копию.

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

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

Для 30-дневной бесплатной пробной версии Kutools for Excel, пожалуйста скачать это сейчас.

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

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

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

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

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 ключ или щелкните Run   кнопку, чтобы включить этот VBA, затем появится диалоговое окно для выбора таблицы, которую вы используете, а затем нажмите OK.

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

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

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

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

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

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

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

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

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

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

Для получения 30-дневной бесплатной пробной версии Kutools for Excel, пожалуйста, скачать сейчас.

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

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

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

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

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

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

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

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

Press Enter ключ, чтобы получить комбинированный результат.

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

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

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

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

=СЦЕПИТЬ(A1,", ",B1,", ",C1,", ",A2,", ",B2,", ",C2,", ",A3,", ",B3,", " ,С3)

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

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

3. 13 Использование функции CONCAT или функции TEXTJOIN (Excel 2019 или Excel 365)

Если вы используете Excel 2019 или Excel 365, функция СЦЕП и функция ТЕКСТОВОЕ СОЕДИНЕНИЕ могут быть лучшим выбором.

Дополнительные сведения о функции CONCAT см. на странице: CONCAT.

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

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

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

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

Дополнительные сведения о функции TEXTJOIN см. на странице: ТЕКСТ ПРИСОЕДИНИТЬСЯ.

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

=ТЕКСТСОЕДИНЕНИЕ(", ",ИСТИНА,A1:C3)

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

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

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

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

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

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

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

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

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

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

Для 30-дневной бесплатной пробной версии Kutools for Excel, пожалуйста скачать это сейчас.

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

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

3.21 Назовите диапазон и используйте функцию ИНДЕКС (только для одного столбца)

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

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

2. Во всплывающем Новое имя введите имя в поле ФИО текстовое поле, щелкните OK.

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. Нажмите другой + F11 ключи для включения Microsoft Visual Basic для приложений окно, затем щелкните Вставить > Модули для создания нового пустого модуля.

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 ключ или щелкните Run   появится диалоговое окно для выбора диапазона ячеек, нажмите OK.

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

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

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

1. выберите диапазон ячеек и нажмите Кутулс > Диапазон > Диапазон преобразования.

2. в Диапазон преобразования диалог, проверьте Диапазон до одного столбца or Диапазон до одной строки вариант как вам нужно. Нажмите OK.

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

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

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

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

Для 30-дневной бесплатной пробной версии Kutools for Excel, пожалуйста скачать это сейчас.

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

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

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

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

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

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

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

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

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

Выберите диапазон столбцов и нажмите Кутулс > Диапазон > Диапазон преобразования.

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

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

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

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

Для 30-дневной бесплатной пробной версии Kutools for Excel, пожалуйста скачать это сейчас.

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

Более того, вот код VBA, который может решить эту задачу.

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

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

4. Затем во втором всплывающем диалоговом окне выберите ячейку назначения для размещения столбца с накоплением и нажмите OK.

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

Если есть два столбца, один отформатирован как специальный формат, например пользовательский DateTimemm/dd/yyyy, чтобы объединить эти два столбца в один с помощью обычных методов, пользовательское форматирование будет удалено, как показано на снимке экрана ниже:

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

3.41 Встроенная функция ТЕКСТ в формулу

Функция ТЕКСТ используется для преобразования числа в текст в определенном формате. Здесь мы можем использовать его, чтобы сначала преобразовать ячейку (содержащую число) в форматирование, а затем объединить ее с другими ячейками с помощью «&», функции CONCATENATE, функции CONCAT или функции TEXTJOIN.

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

Дополнительные сведения о функции ТЕКСТ см. ТЕКСТ функции.

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

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

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

 В формуле A2 — это ячейка, форматирование которой вы хотите сохранить, «мм/дд/гггг чч: мм» — это форматирование, которое вы используете, B2 — это другая ячейка, которая используется для объединения. " " указывает на разделение значений пробелом. вы можете изменить ссылки, форматирование и разделитель по мере необходимости.

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

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

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

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

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

Для получения {module745}-дневной бесплатной пробной версии Kutools for Excel, пожалуйста скачать это сейчас.

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

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

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

Дополнительные сведения о функции ДАТА см. в разделе Функция ДАТА.

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

=DATE(A2,B2,C2)

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

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

Дополнительные учебники по Excel:

---

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

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

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

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

---

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

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

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