Как объединить строки в столбце в одну ячейку в excel?

например 

A1:I
A2:am
A3:a
A4:boy

Я хочу объединить их все в одну ячейку "Iamaboy"
Этот пример показывает, что 4 ячейки сливаются в 1 ячейку, однако у меня есть много ячеек (более 100), я не могу ввести их по одному с помощью A1 & A2 & A3 & A4 что я могу сделать?

8 74
excel vba excel-vba

8 ответов:

Я представляю вам свою функцию ConcatenateRange VBA (спасибо Жану за совет по именованию!) . Он будет принимать диапазон ячеек (любое измерение, любое направление и т. д.) и объединить их вместе в одну строку. В качестве необязательного третьего параметра можно добавить разделитель(например, пробел или запятую).

в этом случае вы бы написали это, чтобы использовать его:

=ConcatenateRange(A1:A4)


Function ConcatenateRange(ByVal cell_range As range, _
                    Optional ByVal separator As String) As String

Dim newString As String
Dim cell As Variant

For Each cell in cell_range
    If Len(cell) <> 0 Then
        newString = newString & (separator & cell))
    End if
Next

If Len(newString) <> 0 Then
    newString = Right$(newString, (Len(newString) - Len(separator)))
End If

ConcatenateRange = newString

End Function
82

Если вы предпочитаете делать это без VBA, вы можете попробовать следующее:

  1. ваши данные в ячейках A1:A999 (или такой)
  2. установите ячейку B1 в "=A1"
  3. установите ячейку B2 в "=B1&A2"
  4. копировать ячейку B2 вплоть до B999 (например, путем копирования B2, выбора ячеек B3:B99 и вставки)

ячейки B999 теперь будет содержать объединенную текстовую строку, которую вы ищете.

141

внутри CONCATENATE можно использовать TRANSPOSE если вы расширить его (F9), затем снимите окружающих {}скобках, например этой рекомендует

=CONCATENATE(TRANSPOSE(B2:B19))

становится

=CONCATENATE("Oh ","combining ", "a " ...)

CONCATENATE(TRANSPOSE(B2:B19))

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

=B1&" "
=B2&" "
=B3&" "
35

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

  1. в любой ячейке напишите следующий код 

    =Transpose(A1:A9)

где A1: A9-это ячейки, которые вы хотите объединить.

  1. не выходя из ячейки нажмите F9

после этого ячейка будет содержать строку: 

={A1,A2,A3,A4,A5,A6,A7,A8,A9}

источник: http://www.get-digital-help.com/2011/02/09/concatenate-a-cell-range-without-vba-in-excel/

обновление: одна часть может быть неоднозначным. Не выходя из ячейки означает, что ваша ячейка находится в режиме редактора. Альтернативно вы можете нажать F9, находясь в панели редактора ячеек (обычно это можно найти над электронной таблицей)

23

использовать уже существующий VBA . Функции VBA не отображаются в Excel, поэтому я обертываю Join в пользовательской функции, которая предоставляет свою функциональность. Самая простая форма:

Function JoinXL(arr As Variant, Optional delimiter As String = " ")
    'arr must be a one-dimensional array.
    JoinXL = Join(arr, delimiter)
End Function

пример использования:

=JoinXL(TRANSPOSE(A1:A4)," ")

вводится в виде формулы массива (с помощью Ctrl -Shift -введите).

enter image description here

теперь JoinXL принимает только одномерные массивы в качестве входных данных. В Excel, диапазоны возвращают двумерные массивы. В приведенном выше примере TRANSPOSE преобразует 4×1 двумерный массив в 4-элементный одномерный массив (это документированное поведение TRANSPOSE когда он подается с одностолбцовым двумерным массивом).

для горизонтального диапазона, вы должны были бы сделать двойной TRANSPOSE:

=JoinXL(TRANSPOSE(TRANSPOSE(A1:D1)))

внутренний TRANSPOSE преобразует 1×4 двумерный массив в 4×1 двумерный массив, который внешний TRANSPOSE затем преобразуется в ожидаемый 4-х элементный одномерный массив.

enter image description here

это использование TRANSPOSE это хорошо известный способ преобразования 2D массивов в 1D массивы в Excel, но это выглядит ужасно. Более элегантным решением было бы скрыть это в JoinXL функции VBA.

12

для тех у кого Excel 2016 (и я полагаю, что следующие версии), теперь есть непосредственно функция concat функция, которая заменит объединения

6

для Excel 2011 на Mac все по-другому. Я сделал это как трехэтапный процесс.

  1. создайте столбец значений в столбце A.
  2. в столбце B, справа от первой ячейки, создайте правило, которое использует функцию concatenate для значения столбца и",". Например, предполагая, что A1-это первая строка, формула для B1 -=B1. Для следующей строки в строке N формула =Concatenate(",",A2). Вы в конечном итоге с: 
QA
,Sekuli
,Testing
,Applitools
,Visual Testing
,Test Automation
,Selenium
  1. в столбце C создать формулу, которая объединяет все предыдущие значения. Потому что это добавка вы получите все в конце. Формула для ячейки C1 -=B1. Для всех остальных строк до n, формула =Concatenate(C1,B2). И вы получаете:
QA,Sekuli
QA,Sekuli,Testing
QA,Sekuli,Testing,Applitools
QA,Sekuli,Testing,Applitools,Visual Testing
QA,Sekuli,Testing,Applitools,Visual Testing,Test Automation
QA,Sekuli,Testing,Applitools,Visual Testing,Test Automation,Selenium

последняя ячейка списка будет то, что вы хотите. Это совместимо с Excel на Windows или Mac.

2

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

Я всегда просто типа =CONCATENATE("'",B2,"'",","), а затем выберите и перетащите ее вниз, что создает =CONCATENATE("'",B3,"'",","),=CONCATENATE("'",B4,"'",",") и т. д. затем выделите весь столбец, скопируйте вставку в обычный текстовый редактор и вставьте обратно, если это необходимо, тем самым удаляя разделение строк. Это работает, но опять же, как разовая сделка, это не хорошее решение для тех, кто нуждается в этом все время.

1