多列随机分配任务：行内及列内重复次数限制的实现需求

公式实现方案 #

行内重复限制（基础版） #

如果你的场景是每行12个任务，6个任务各最多出现2次（刚好填满12列），可以通过以下步骤实现：

1. 新增辅助列（如P列），生成每个任务重复指定次数的序列：

   =TOCOL(REPT($A$3:$A$8,2),,TRUE)
   

   这个公式会将A列的6个任务各重复2次，合并为12条数据的序列。
2. 在D3单元格输入公式，随机打乱上述序列并生成横向的任务列表：

   =INDEX(SORTBY(P3:P14,RANDARRAY(12)),SEQUENCE(1,12))
   

   下拉公式到所有姓名对应的行即可。

行+列双重重复限制（进阶版） #

要同时控制行内任务重复次数和列内任务重复次数（比如每列同一任务最多分配给1人），需要启用迭代计算并使用动态数组公式：

1. 启用迭代计算：文件→选项→公式→勾选「启用迭代计算」，设置迭代次数为100。
2. 在D3单元格输入以下公式，横向、纵向填充到整个输出区域：

   =LET(
       taskList,$A$3:$A$8,
       maxRowRepeat,2,
       maxColRepeat,1,
       rowTaskCount,COUNTIF(D$2:D2,taskList),
       colTaskCount,COUNTIF($D$2:D2,taskList),
       available,FILTER(taskList,rowTaskCount<maxRowRepeat,colTaskCount<maxColRepeat),
       IFERROR(INDEX(SORTBY(available,RANDARRAY(COUNTA(available))),1),"无可用任务")
   )
   

   公式会自动筛选当前行、当前列均未达重复上限的任务，随机选择一个填充。

VBA实现方案（更灵活可控） #

当限制条件复杂时，VBA的性能和灵活性更优，以下是可直接使用的代码：

Sub GenerateRestrictedRandomTasks()
    Dim taskRange As Range, nameRange As Range, outputRange As Range
    Dim tasks() As Variant, names() As Variant
    Dim maxRowRepeat As Integer, maxColRepeat As Integer
    Dim i As Integer, j As Integer, k As Integer
    Dim rowCount As Integer, colCount As Integer
    Dim randIndex As Integer, selectedTask As String
    
    ' 配置参数，根据你的表格修改
    Set taskRange = ThisWorkbook.Sheets("Sheet1").Range("A3:A8")
    Set nameRange = ThisWorkbook.Sheets("Sheet1").Range("C3:C8")
    Set outputRange = ThisWorkbook.Sheets("Sheet1").Range("D3:O8")
    maxRowRepeat = 2 ' 每行同一任务最多出现次数
    maxColRepeat = 1 ' 每列同一任务最多出现次数
    
    tasks = taskRange.Value
    names = nameRange.Value
    outputRange.ClearContents
    
    ' 遍历每行（每个人员）
    For i = 1 To UBound(names)
        ' 记录当前行各任务的已使用次数
        Dim rowTaskTracker As Object
        Set rowTaskTracker = CreateObject("Scripting.Dictionary")
        For k = 1 To UBound(tasks)
            rowTaskTracker(tasks(k, 1)) = 0
        Next k
        
        ' 遍历每列（每个周）
        For j = 1 To outputRange.Columns.Count
            Dim validTasks As Collection
            Set validTasks = New Collection
            
            ' 筛选符合双重限制的任务
            For k = 1 To UBound(tasks)
                rowCount = rowTaskTracker(tasks(k, 1))
                colCount = Application.CountIf(outputRange.Columns(j), tasks(k, 1))
                If rowCount < maxRowRepeat And colCount < maxColRepeat Then
                    validTasks.Add tasks(k, 1)
                End If
            Next k
            
            ' 随机选择任务填充
            If validTasks.Count > 0 Then
                randIndex = Int(Rnd * validTasks.Count) + 1
                selectedTask = validTasks(randIndex)
                outputRange.Cells(i, j).Value = selectedTask
                rowTaskTracker(selectedTask) = rowTaskTracker(selectedTask) + 1
            Else
                outputRange.Cells(i, j).Value = "无可用任务"
            End If
        Next j
    Next i
End Sub

使用方法 #

1. 按Alt+F11打开VBA编辑器，插入新模块。
2. 粘贴上述代码，修改区域和参数匹配你的表格。
3. 运行宏，即可生成符合限制的随机任务列表。

内容的提问来源于stack exchange，提问作者Its_Hink