Excel生成符合重复次数限制的非重复随机整数方法问询

核心思路 #

先构建符合目标次数的数字候选池，随机打乱后，再调整确保每行值与B列对应行不重复，同时保留次数分布。

步骤1：生成C列目标次数的候选数组 #

根据需求确定数字分布（比如1出现4次、2出现3次、3出现4次、4出现3次，或放宽为2个数字3次、其余4次），先把数字按次数列成一维数组。示例数组（对应14行数据）：

{1,1,1,1,2,2,2,3,3,3,3,4,4,4}

步骤2：随机打乱候选数组 #

用Excel函数生成随机排序的候选值，放到辅助列（比如E列）：

=SORTBY({1,1,1,1,2,2,2,3,3,3,3,4,4,4}, RANDARRAY(14))

注：公式里的14替换为你的实际数据行数

步骤3：调整候选值，确保与B列无重复 #

方法1：VBA宏自动调整（高效推荐） #

打开VBA编辑器（按Alt+F11），插入模块后粘贴以下代码，运行即可自动调整并生成C列值：

Sub AdjustCColumn()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long, j As Long
    Dim cVals As Variant, bVals As Variant
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    ' 获取B列和辅助列（E列）的值
    bVals = ws.Range("B2:B" & lastRow).Value
    cVals = ws.Range("E2:E" & lastRow).Value
    
    ' 遍历检查并交换重复值
    For i = 1 To lastRow - 1
        If cVals(i, 1) = bVals(i, 1) Then
            ' 寻找可交换的目标行
            For j = 1 To lastRow - 1
                If cVals(j, 1) <> bVals(i, 1) And cVals(i, 1) <> bVals(j, 1) Then
                    ' 交换两个候选值
                    Dim temp As Variant
                    temp = cVals(i, 1)
                    cVals(i, 1) = cVals(j, 1)
                    cVals(j, 1) = temp
                    Exit For
                End If
            Next j
        End If
    Next i
    
    ' 将调整后的值写入C列
    ws.Range("C2:C" & lastRow).Value = cVals
End Sub

方法2：手动调整（适合小数据量） #

筛选出E列与B列重复的行，找其他行的候选值交换，只要保证交换后两行的候选值都不等于各自B列的值即可。

步骤4：D列生成逻辑 #

D列需排除B、C列对应行的值，若无需严格次数控制，直接用以下公式：

=LET(
    exclude, {B2,C2},
    candidates, FILTER({1,2,3,4}, NOT(ISNUMBER(MATCH({1,2,3,4}, exclude, 0)))),
    INDEX(candidates, RANDBETWEEN(1, COUNTA(candidates)))
)

若需要控制D列次数，可复用C列的候选池+调整逻辑，先根据剩余次数构建候选数组再处理。

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