Вопрос по excel, vba – Excel vba для создания всевозможных комбинаций диапазона

11

У меня есть проблема, которую мне нигде не удалось найти в Интернете (она может быть там, но я не могу ее найти, хе).

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

Все они отличаются, как

E:
101%
105%
110%
120%

J:
Верхний S
Вверх л
Даунсайд Б
Премиум V

Я видел несколько решений проблемы комбинации, которая использует вложенные циклы. Я хотел бы держаться подальше от 13 вложенных циклов (но это моя лучшая ставка на данный момент). Я не знаю, как создать каждую уникальную комбинацию в каждом столбце.

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

Спасибо за любую помощь, ребята, вы можете оказать мне.

Если вы хотите получить ответ «только vba», который масштабируется до любого количества «наборов» (или измерений или категорий) и любого количества членов в наборе, см. мой ответ ниже. spioter
Мне нравится это решение для кросс-соединения. Я должен дать ему шанс завтра. Я даже не думал об этом. Kelvin
Боюсь, тебе придется использовать петли. Лучше всего использовать 13 массивов, каждый из которых содержит определенный диапазон, а затем генерировать комбинации. Siddharth Rout
Ладно, "лучший", возможно, не был хорошим выбором слов. Другой способ - настроить рабочий лист, который повторяет все возможные индексы, а затем использоватьINDEX чтобы посмотреть значения. По сути, это массив из 13 измерений, но с использованием только функций лист andy holaday
Лучший способ, с которым я столкнулся, - это установить соединение данных ODBC, которое направляет файл Excel на себя, а затем создать перекрестный (декартовый) запрос к вашим данным. andy holaday

Ваш Ответ

5   ответов
22

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

Это способ создать Картезианский продукт из двух или более одномерных массивов данных с использованием Excel и Microsoft Query.

Эти инструкции были написаны для XL2007, но должны работать с незначительными (если есть) изменениями в любой версии.

Шаг

Организовать массивы в столбцах.

Важный Каждый столбец должен иметь два имени «заголовка», как показано ниже жирным шрифтом. Самое верхнее имя позже будет интерпретироваться как «имя таблицы». Второе имя будет интерпретировано как «имя столбца». Это станет очевидным через несколько шагов.

Выберите каждый диапазон данных по очереди, включая оба «заголовка», и нажмитеCtrl+Shift+F3. Только галочкаTop row в диалоговом окне «Создать имена» и нажмитеOK.

Как только все названные диапазоны будут установлены, сохраните файл.

Шаг

Data | Получить внешние данные | Из других источников | От Microsoft Query

Выберите<New Data Source>. ВChoose New Data Source диалог:

Дружественное имя для вашей связи

выберите соответствующий драйвер Microsoft Excel

... тогдаConnect

Шаг

Select Workbook... затем найдите свой файл.

Шаг 4

Добавить "столбцы" из ваших "таблиц". Теперь вы можете понять, почему макет «с двумя заголовками» на шаге 1 важен - он помогает водителю правильно понимать данные.

Далее нажмитеCancel (действительно!). В этот момент вам может быть предложено «продолжить редактирование в Microsoft Query?» (ответYes) или жалоба, которая присоединяется, не может быть представлена в графическом редакторе. Проигнорируйте это и подделайте ...

Шаг 5

Microsoft Query откроется, и по умолчанию добавленные вами таблицы будут перекрестными. Это создаст декартово произведение, чего мы и хотим.

Теперь закрой MSQuery.

Шаг 6

Вы вернулись на рабочий лист. Почти готово, обещаю! ТИКNew worksheet а такжеOK.

Шаг 7

Полученные результаты возвращаются.

Хорошо! В качестве альтернативы, и мой обычный способ сделать это, скопировать столбцы в таблицы в MS Access и генерировать эти результаты там же. Затем его можно легко скопировать обратно в Excel. mattboy
+ 1 Приятно подробно и объяснено Pradeep Kumar
+ 1 от меня тоже. Я хотел бы дать как +3 или что-то, для усилий и четкого объяснения Scott Holtzman
Это круто. Спасибо огромное, Энди! Kelvin
действительно очень круто! Я использовал Excel в течение 15 лет и не знал об этом l--''''''---------''''''''''''
9

почему ты не любишь зацикливаться. Смотрите этот пример. Это заняло меньше секунды.

Option Explicit

Sub Sample()
    Dim i As Long, j As Long, k As Long, l As Long
    Dim CountComb As Long, lastrow As Long

    Range("G2").Value = Now

    Application.ScreenUpdating = False

    CountComb = 0: lastrow = 6

    For i = 1 To 4: For j = 1 To 4
    For k = 1 To 8: For l = 1 To 12
        Range("G" & lastrow).Value = Range("A" & i).Value & "/" & _
                                     Range("B" & j).Value & "/" & _
                                     Range("C" & k).Value & "/" & _
                                     Range("D" & l).Value
        lastrow = lastrow + 1
        CountComb = CountComb + 1
    Next: Next
    Next: Next

    Range("G1").Value = CountComb
    Range("G3").Value = Now

    Application.ScreenUpdating = True
End Sub

СНАПШОТ

НОТ: Выше был небольшой пример. Я сделал тест на 4 столбца с 200 строк в каждом. Общая комбинация, возможная в таком сценарии, равна1600000000 и это заняло 16 секунд.

В таком случае он пересекает предел строк Excel. Еще один вариант, о котором я могу подумать, - записать вывод в текстовый файл в таком сценарии. Если ваши данные невелики, вы можете уйти без использования массивов и прямой записи в ячейки. :) Но в случае больших данных я бы рекомендовал использовать массивы.

+ 1 Да, VBA в этом случае должен быть быстрее. Pradeep Kumar
Привет Сиддхарт, Спасибо за ответ. Одна из проблем, с которыми я сталкиваюсь, заключается в том, что число входных столбцов может изменяться и будет изменяться. Иногда его 13, иногда 6, иногда 12. Я всегда могу настроить его, но я ищу что-то, что не является одноразовым. Я ценю ваш пример и определенно помогаю мне двигаться в правильном направлении. Kelvin
Ты всегда можешь использоватьws.Cells(1, ws.Columns.Count).End(xlToLeft).Column чтобы найти последний столбец Siddharth Rout
4

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

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

Предполагается, что вы хотите объединить выходные данные на основе выбранных ячеек (убедитесь, что вы выбрали их все)

Предполагается, что вы хотите, чтобы вывод начинался на один столбец после текущего выбора.

Как это работает (кратко): сначала для каждого столбца и для каждой строки: вычисляется общее количество строк, необходимое для поддержки всех комбинаций в N столбцах (элементы в столбце 1 * элементы в столбце 2 ... * элементы в столбце N )

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

ValueCycles (сколько раз вам нужно циклически проходить все значения в текущем столбце) ValueRepeats (сколько раз повторять каждое значение в столбце последовательно)

Sub sub_CrossJoin()

Dim rg_Selection As Range
Dim rg_Col As Range
Dim rg_Row As Range
Dim rg_Cell As Range
Dim rg_DestinationCol As Range
Dim rg_DestinationCell As Range
Dim int_PriorCombos As Long
Dim int_TotalCombos As Long
Dim int_ValueRowCount As Long
Dim int_ValueRepeats As Long
Dim int_ValueRepeater As Long
Dim int_ValueCycles As Long
Dim int_ValueCycler As Long

int_TotalCombos = 1
int_PriorCombos = 1
int_ValueRowCount = 0
int_ValueCycler = 0
int_ValueRepeater = 0

Set rg_Selection = Selection
Set rg_DestinationCol = rg_Selection.Cells(1, 1)
Set rg_DestinationCol = rg_DestinationCol.Offset(0, rg_Selection.Columns.Count)

'get total combos
For Each rg_Col In rg_Selection.Columns
    int_ValueRowCount = 0
    For Each rg_Row In rg_Col.Cells
        If rg_Row.Value = "" Then
            Exit For
        End If
        int_ValueRowCount = int_ValueRowCount + 1
    Next rg_Row
    int_TotalCombos = int_TotalCombos * int_ValueRowCount
Next rg_Col

int_ValueRowCount = 0

'for each column, calculate the repeats needed for each row value and then populate the destination
For Each rg_Col In rg_Selection.Columns
    int_ValueRowCount = 0
    For Each rg_Row In rg_Col.Cells
        If rg_Row.Value = "" Then
            Exit For
        End If
        int_ValueRowCount = int_ValueRowCount + 1
    Next rg_Row
    int_PriorCombos = int_PriorCombos * int_ValueRowCount
    int_ValueRepeats = int_TotalCombos / int_PriorCombos


    int_ValueCycles = (int_TotalCombos / int_ValueRepeats) / int_ValueRowCount
    int_ValueCycler = 0

    int_ValueRepeater = 0

    Set rg_DestinationCell = rg_DestinationCol

    For int_ValueCycler = 1 To int_ValueCycles
        For Each rg_Row In rg_Col.Cells
            If rg_Row.Value = "" Then
                Exit For
            End If

                For int_ValueRepeater = 1 To int_ValueRepeats
                    rg_DestinationCell.Value = rg_Row.Value
                    Set rg_DestinationCell = rg_DestinationCell.Offset(1, 0)
                Next int_ValueRepeater

        Next rg_Row
    Next int_ValueCycler

    Set rg_DestinationCol = rg_DestinationCol.Offset(0, 1)
Next rg_Col
End Sub
3

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

Я начинаю с ваших образцов данных. Я добавил счета в верхнем ряду для удобства. Я также добавил общее количество комбинаций (произведение подсчетов). ЭтоSheet1:

OnSheet2:

Formulae:

A2:C2 (оранжевые ячейки) жестко закодированы=0

A3=IF(SUM(B3:C3)=0,MOD(A2+1,Sheet1!$E$1),A2)

B3=IF(C3=0,MOD(B2+1,Sheet1!$G$1),B2)

C3=MOD(C2+1,Sheet1!$J$1)

D2=INDEX(Sheet1!$E$2:$E$5,Sheet2!A2+1)

E2=INDEX(Sheet1!$G$2:$G$6,Sheet2!B2+1)

F2=INDEX(Sheet1!$J$2:$J$5,Sheet2!C2+1)

Заполните строку 3 на столько строк, сколькоTotal показывает наSheet1

Ваше решение может быть проще адаптировать к разному количеству столбцов, но мне не нравятся относительные решения (в которых каждая строка определяется как функция предыдущего ряда), когда в этом нет необходимости. Вы можете вычислитьA, B, а такжеC с абсолютными формулами:A2=INT((ROW()-2)/($G$1*$J$1)), B2=MOD(INT((ROW()-2)/$J$1), $G$1), а такжеC2=MOD((ROW()-2), $J$1). FWIW, ваши формулы будут перенесены после 80 (4 × 5 × 4) строк, а мои позволят столбецA расти без границ; конечно, это тривиально исправить. Scott
0

который будет уменьшен в методе (извините за англ)

образец

    sub MyAdd(i as integer)
      if i > 1 then
        MyAdd = i + MyAdd(i-1)
      else
        MyAdd = 1
      end if
    end sub

Похожие вопросы