У меня есть сетка 6 x 6. У меня есть 36 образцов, разделенных на три равные группы (A, B и C).
Мне нужна формула для случайного расположения образцов в этой сетке. Однако каждая строка и каждый столбец должны содержать по два образца каждой группы. Сделать это вручную один или два раза легко, но существует огромное количество комбинаций.
Я открыт в плане выбора программного обеспечения, если оно бесплатное или основано на Microsoft Office.
Спасибо!
решение1
Я собрал инструмент для генерации случайных перестановок строк/столбцов с помощью пары формул и немного VBA. Макет листа выглядит так:
Сетка ссылок — это тривиальный пример допустимой матрицы, как опубликовано в предварительном ответе Gary's Student (возможно, после удаления). Перестановки строк и столбцов включают все возможные уникальные комбинации перестановок для сетки 6x6. (Это можно легко изменить, включив неуникальные перестановки, если это необходимо.) Значения в E12:E26
и L12:L26
случайным образом задаются либо нулем, либо единицей, чтобы обеспечить основу для того, следует ли выполнять данную перестановку. Столбцы D
и K
просто преобразуют их в логические значения для упрощенной обработки в VBA (см. ниже). Переставленная сетка генерируется пользовательской функцией doSwap
, введенной как формула массива. Нажатие F9
для запуска пересчета листа заставляет различные RAND
функции повторно генерировать свои случайные значения, изменяя серию выполняемых перестановок.
Код VBA, который обеспечивает такое поведение, следующий:
Function doSwap(srcRg As Range, rowSwaps As Range, colSwaps As Range) As Variant
Dim workVt As Variant
Dim iter As Long
workVt = srcRg.Value
' Do row swaps
For iter = 1 To rowSwaps.Rows.Count
With rowSwaps
If .Cells(iter, 3).Value Then
workVt = swapRow(workVt, .Cells(iter, 1), .Cells(iter, 2))
End If
End With
Next iter
' Do col swaps
For iter = 1 To colSwaps.Rows.Count
With colSwaps
If .Cells(iter, 3).Value Then
workVt = swapCol(workVt, .Cells(iter, 1), .Cells(iter, 2))
End If
End With
Next iter
' Store and return
doSwap = workVt
End Function
Function swapCol(ByVal inArr As Variant, idx1 As Long, idx2 As Long) As Variant
Dim tempVal As Variant, workVt As Variant
Dim iter As Long
' Check if Range or Array input
If IsObject(inArr) Then
If TypeOf inArr Is Range Then
workVt = inArr.Value
Else
swapCol = "ERROR"
Exit Function
End If
Else
workVt = inArr
End If
' Just crash if not correct size
' Do swap
For iter = LBound(workVt, 1) To UBound(workVt, 1)
tempVal = workVt(iter, idx1)
workVt(iter, idx1) = workVt(iter, idx2)
workVt(iter, idx2) = tempVal
Next iter
' Return
swapCol = workVt
End Function
Function swapRow(ByVal inArr As Variant, idx1 As Long, idx2 As Long) As Variant
Dim tempVal As Variant, workVt As Variant
Dim iter As Long
' Check if Range or Array input
If IsObject(inArr) Then
If TypeOf inArr Is Range Then
workVt = inArr.Value
Else
swapRow = "ERROR"
Exit Function
End If
Else
workVt = inArr
End If
' Just crash if not correct size
' Do swap
For iter = LBound(workVt, 2) To UBound(workVt, 2)
tempVal = workVt(idx1, iter)
workVt(idx1, iter) = workVt(idx2, iter)
workVt(idx2, iter) = tempVal
Next iter
' Return
swapRow = workVt
End Function
Приведенный выше код не очень хорошо защищен, но служит текущей цели. Расширение/обобщение должно быть довольно простым, если необходимо. В частности, он должен обрабатывать как есть любой размер двумерной сетки ссылок, даже неквадратной. Главное — убедиться, что массивы инструкций перестановки настроены правильно.
РЕДАКТИРОВАТЬ:Поигравшись немного, стало ясно, что это решение не обеспечивает доступа ко всему пространству возможных перестановок. Поэтому я подправил его, добавив случайный "битовый сдвиг" для обмена метками типов между собой. Чтобы упростить задачу, я перешел от ABC
меток к 123
меткам, что позволяет реализовать простую MOD
операцию, а также провести быструю проверку работоспособности в виде сумм строк и столбцов:
решение2
Есть очень простой способ сделать это. Сначала предварительно назначьтеслотык каждому из трех типов:
Затем возьмите первый образец, например SAMPLE_A_1, и поместите егослучайнов одном из слотов A. Затем продолжайте обрабатывать каждый из оставшихся 35 образцов.
Если этот подход приемлем, я выложу короткую программу для заполнения матрицы. Если подход неприемлем, я удалю этот пост.