如何在Excel中生成符合规则的员工交叉检查动态随机数组？

需求明确 #

要为8名员工（行1-8）生成1-7月（列1-7）的交叉检查安排，需满足：

• 每名员工不得自查（单元格值≠行号）
• 每名员工不会重复检查同一人（每行内7个值无重复）
• 类似数独的行列无重复：每个月（列）内，每名员工仅被检查一次（列内8个值为1-8的无重复排列，且无自查）

解决方案 #

方法1：VBA代码（推荐，稳定满足所有约束）

直接运行以下VBA代码，会在工作表的A10:H17区域生成符合要求的随机数组，每次运行都会生成全新的随机安排：

Sub GenerateCrossCheck()
    Dim arr(1 To 8, 1 To 7) As Integer
    Dim used(1 To 8, 1 To 8) As Boolean ' 标记检查员是否已检查过某员工
    Dim i As Integer, j As Integer, k As Integer, randNum As Integer
    
    ' 初始化：禁止检查员自查
    For i = 1 To 8
        used(i, i) = True
    Next i
    
    ' 为每个检查员生成7个不重复、非自查的被检查对象
    For i = 1 To 8
        For j = 1 To 7
            Do
                randNum = Int((8 * Rnd) + 1)
            Loop While used(i, randNum) = True
            arr(i, j) = randNum
            used(i, randNum) = True
        Next j
    Next i
    
    ' 调整每列，确保当月每个员工仅被检查一次
    For j = 1 To 7
        Dim colUsed(1 To 8) As Boolean
        Dim duplicates As Collection
        Set duplicates = New Collection
        
        ' 定位列内重复的检查对象
        For i = 1 To 8
            If colUsed(arr(i, j)) = True Then
                duplicates.Add i
            Else
                colUsed(arr(i, j)) = True
            End If
        Next i
        
        ' 交换重复项，修正列的唯一性
        For Each dup In duplicates
            For k = 1 To 8
                If colUsed(k) = False Then
                    For i = 1 To 8
                        If arr(i, j) <> k And used(i, k) = False Then
                            ' 交换两个检查员的当月检查对象
                            Dim temp As Integer
                            temp = arr(dup, j)
                            arr(dup, j) = k
                            arr(i, j) = temp
                            ' 更新检查记录标记
                            used(dup, temp) = False
                            used(dup, k) = True
                            used(i, k) = False
                            used(i, temp) = True
                            colUsed(k) = True
                            colUsed(temp) = False
                            Exit For
                        End If
                    Next i
                    Exit For
                End If
            Next k
        Next dup
    Next j
    
    ' 将结果输出到工作表（可修改输出区域）
    Range("A10:H17").Value = arr
End Sub

方法2：Excel动态数组公式（仅满足行约束，需手动调整列）

如果无法使用VBA，可先用公式生成每行的随机非重复、非自查列表，再手动调整列内的重复项：
在B1单元格输入以下公式，下拉至B8，即可生成每行的随机检查安排（列内可能有重复，需手动调整）：

=LET(
    emp, SEQUENCE(8),
    self, A1,
    others, FILTER(emp, emp<>self),
    rand, RANDARRAY(ROWS(others)),
    SORTBY(others, rand)
)

注：A1:A8需预先输入员工编号1-8。

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