Объединение всех комбинаций данных из трех столбцов в Excel

Question

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

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

Так как же получить Excel, чтобы перечислить каждую возможную комбинацию столбцов a, b и c? (Я не хочу объединять параметры в каждом столбце.

Так, например, если я начну с "ado", я бы хотел ado-ar-en, ado-r-en, ado-an-en, ado-n-en ... ну, вы поняли, что это длинный список.

Answer 1

Оригинальный источник от Excellll на SO здесь. Его код.

Самый простой способ - взять списки независимо и сохранить их в массиве. Затем используйте массивы для вывода всех возможных комбинаций. Этот код будет выводиться в 3 разных столбцах, но вы можете объединить их, если они нужны в одной ячейке.

Sub combinations()

Dim c1() As Variant
Dim c2() As Variant
Dim c3() As Variant
Dim out() As Variant
Dim j, k, l, m As Long


Dim col1 As Range
Dim col2 As Range
Dim col3 As Range
Dim out1 As Range


Set col1 = Range("A1", Range("A1").End(xlDown))
Set col2 = Range("B1", Range("B1").End(xlDown))
Set col3 = Range("C1", Range("C1").End(xlDown))

c1 = col1
c2 = col2
c3 = col3

Set out1 = Range("E2", Range("G2").Offset(UBound(c1) * UBound(c2) * UBound(c3)))
out = out1

j = 1
k = 1
l = 1
m = 1


Do While j <= UBound(c1)
    Do While k <= UBound(c2)
        Do While l <= UBound(c3)
            out(m, 1) = c1(j, 1)
            out(m, 2) = c2(k, 1)
            out(m, 3) = c3(l, 1)
            m = m + 1
            l = l + 1
        Loop
        l = 1
        k = k + 1
    Loop
    k = 1
    j = j + 1
Loop


out1.Value = out
End Sub

Answer 2

Не проверено, у меня нет Excel, доступного (используя LibreOffice дома, это не будет работать в LibreOffice).

ЕСЛИ ЭТО РАБОТАЕТ ;-) это должно / делает: Предполагается, что "объявление" находится в ячейке А1, а остальные просто рядом с ним. Заполните D1 и вниз комбинированными результатами, пока "текущая" ячейка не является пустой ячейкой в каждом из столбцов A, B и C.

sub list_gen

  a=1
  d=1
  while Worksheets(1).Cells(a,1).Text<>""
    astr=Worksheets(1).Cells(a,1).text
    b=1
    while Worksheets(1).Cells(b,2).text<>""
      bstr=Worksheets(1).Cells(b,2).text
      c=1
      while Worksheets(1).Cells(c,3).text<>""
        cstr=Worksheets(1).Cells(c,3).text
        c=c+1
        Worksheets(1).Cells(d,4).text=astr+"-"+bstr+"-"+cstr
        d=d+1
       wend 
       b=b+1
    wend  
    a=a+1
  wend 

end sub

Answer 3

Один из подходов заключается в создании "трехмерной" таблицы для захвата всех комбинаций. Ваши списки имеют 11, 11 и 5 записей, поэтому создайте пять таблиц 11x11. Каждая из пяти будет содержать комбинации, включающие одну из записей в вашем третьем столбце. Ячейки в каждой таблице будут отражать комбинацию одного элемента из вашего первого столбца и одного элемента из вашего второго.

Предположим, данные, которые вы показываете, находятся в A1:C11 . Создайте первую таблицу, начиная с A15 . Мы помечаем строки и столбцы таблицы индексными номерами, которые будут использоваться для поиска ячеек для каждой записи в таблице. Таким образом, таблица 1 будет настроена так:

         [A]  [B]  [C]  [D]  [E]  [F]  [G]  [H]  [I]  [J]  [K]  [L]
    [15]       0    1    2    3    4    5    6    7    8    9    10
    [16]  0
    [17]  1
    [18]  2
    [19]  3
    [20]  4
    [21]  5
    [22]  6
    [23]  7
    [24]  8
    [25]  9
    [26] 10

Формула в B16 будет иметь вид:

    =OFFSET($A$1,$A16,0,)&OFFSET($B$1,B$15,0,)&$C$1

Скопируйте это, чтобы заполнить все ячейки в таблице. Первое выражение привязывает список в столбце A и выбирает запись, соответствующую метке строки для таблицы (первая строка имеет нулевое смещение, поэтому она ссылается на первую запись в списке). Второе выражение привязывает список в столбце B и выбирает запись, соответствующую метке столбца. Третье выражение использует первое значение в списке столбца C для всех записей в первой таблице. Знаки доллара в выражениях привязывают формулы к меткам строк и столбцов таблицы, поэтому вы можете скопировать формулу для заполнения таблицы, и каждая ячейка будет ссылаться на правильные метки. Амперсанды объединяют три значения. Если вы хотите иметь разделитель между значениями, например, показанный вами дефис, используйте его вместо амперсандов в формуле:

    &"-"&

Так что B16 покажет adaren или ad-ar-en . C16 покажет adren или ad-r-en . B17 покажет adoaren или ado-ar-en .

Создайте другие таблицы точно так же, рядом с или под первой таблицей, где это удобно. Когда вы вводите формулу для первой ячейки, укажите на метки строк и столбцов в этой таблице. Например, если вторая таблица начинается ниже первой таблицы, скажем, в A30 , первая ячейка таблицы будет B31 . Первое выражение в этой формуле будет использовать $A31 вместо $A16 и B$30 вместо B$15 . Последнее выражение во второй таблице будет $C$2 вместо $C$1 . В третьей таблице это будет $C$3 и так далее.

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

Answer 4

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

List_A  List_B  List_C  
A1      B1      C1      
A2      B2      C2      
A3  

    Sub RandomCombo()

    ListA = Application.WorksheetFunction.Transpose(Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value) 'data starts in 2nd row
    ListB = Application.WorksheetFunction.Transpose(Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).Value) 'data starts in 2nd row
    ListC = Application.WorksheetFunction.Transpose(Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row).Value) 'data starts in 2nd row

    counter = 2

    For i = 1 To UBound(ListA)
        For j = 1 To UBound(ListB)
            For k = 1 To UBound(ListC)
                Cells(counter, 4) = ListA(i) & "-" & ListB(j) & "-" & ListC(k)
                counter = counter + 1
            Next k
        Next j
    Next i

    End Sub