如何在Excel中按指定条件为2023列分配均等随机数？

假设你的数据表头在第1行，2020、2021、2022列对应A、B、C列，2023列对应D列。以下提供两种满足需求的方案：

方法一：动态数组公式法（Excel 365/2021及以上版本） #

步骤1：设置初始配额 #

在E1到J1单元格，分别输入每个数字的初始分配数量（总行数÷6，需保证总行数为6的倍数；若不是，可让前N个数字多分配1次，N=总行数%6）。

步骤2：2023列分配公式 #

在D2单元格输入以下公式，下拉填充至最后一行：

=LET(
    usedNums, A2:C2,
    available, FILTER({1,2,3,4,5,6}, ISNA(MATCH({1,2,3,4,5,6}, usedNums, 0))),
    quotaLeft, XLOOKUP(available, {1,2,3,4,5,6}, $E$1:$J$1),
    pickNum, INDEX(available, MATCH(MAX(quotaLeft), quotaLeft, 0)),
    OFFSET($E$1, 0, pickNum-1) - 1,
    pickNum
)

步骤3：配额实时更新 #

在E2单元格输入：

=IF(E1>0, E1 - (D2=1), 0)

向右拖动填充至J2，再将E2:J2下拉至最后一行，实现配额自动递减。

方法二：VBA宏方法（全Excel版本通用） #

代码实现 #

按Alt+F11打开VBA编辑器，插入新模块，粘贴以下代码：

Sub Assign2023Numbers()
    Dim ws As Worksheet
    Set ws = ActiveSheet ' 可改为指定工作表，比如Sheets("你的表名")
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Dim totalRows As Long
    totalRows = lastRow - 1 ' 排除表头行
    Dim perNum As Integer
    perNum = totalRows \ 6 ' 基础配额
    Dim extra As Integer
    extra = totalRows Mod 6 ' 处理非6倍数的情况
    
    ' 初始化配额数组
    Dim quotas(1 To 6) As Integer
    Dim i As Integer
    For i = 1 To 6
        quotas(i) = perNum
        If i <= extra Then quotas(i) = quotas(i) + 1
    Next i
    
    Dim row As Long
    Dim usedNums As Variant
    Dim availableNums As Collection
    Dim randIndex As Integer
    
    For row = 2 To lastRow
        ' 获取当前行已用数字
        usedNums = ws.Range("A" & row & ":C" & row).Value
        
        ' 筛选可用数字（配额未耗尽且未在当前行出现）
        Set availableNums = New Collection
        For i = 1 To 6
            If quotas(i) > 0 And IsError(Application.Match(i, usedNums, 0)) Then
                availableNums.Add i
            End If
        Next i
        
        ' 随机选择可用数字（要固定顺序则改为randIndex=1）
        randIndex = Int((availableNums.Count) * Rnd + 1)
        ws.Range("D" & row).Value = availableNums(randIndex)
        
        ' 更新配额
        quotas(availableNums(randIndex)) = quotas(availableNums(randIndex)) - 1
    Next row
End Sub

使用说明 #

1. 确认数据列对应正确（A-C为2020-2022，D为2023）。
2. 运行宏前建议保存文件，避免数据丢失。
3. 代码自动兼容总行数非6倍数的情况，无需手动调整配额。

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