如何重新分配1-100间有序重复数值列表并去重且不越界

刚好处理过类似的排名数据清洗需求，给你两个靠谱的方案，完美避开循环引用和超范围的问题：

一、Excel公式方案（无循环引用，首选） #

假设你的源数据在A列（从A1开始），处理后结果放在B列，步骤如下：

1. 第一个单元格（B1）：输入公式 =MIN(A1, 100)，确保初始值不超出1-100范围。
2. 第二个单元格（B2）：输入公式 =MIN(MAX(B1+1, A2), 100)，然后下拉填充到所有行。

公式逻辑拆解 #

• MAX(B1+1, A2)：保证当前处理后的值严格大于前一个值（避免重复），同时如果原数据A2比前一个处理后的值+1还大，就保留原数据（尽量贴合原始排名逻辑）。
• MIN(..., 100)：强制限制所有值不超过100上限。
• 因为源列表是升序的，这个公式完全不会出现循环引用，也能保证处理后的列表始终是升序、无重复、在1-100范围内。

测试你的示例数据

源数据前10个值：2, 6, 6, 10, 10, 10, 10, 11, 12, 13
处理后结果：2, 6, 7, 10, 11, 12, 13, 14, 15, 16，完全符合你给出的第一个示例要求。

如果你想要让重复值填充为连续序列（比如得到2, 6, 7, 8, 9, 10, 11, 12, 13, 14），可以把B2的公式改成：
=MIN(IF(A2>B1, A2, B1+1), 100)
这个逻辑会在遇到重复值时直接递增，直到遇到更大的原数据再跳转。

二、VBA方案（兼容元素数超100的情况） #

如果你的列表元素数量可能超过100，或者需要批量处理多个列表，可以用VBA脚本自动处理：

打开Excel的开发者工具，插入模块，粘贴以下代码：

Sub CleanRankedList()
    Dim sourceRange As Range
    Dim targetRange As Range
    Dim lastRow As Long
    Dim i As Long
    Dim currentVal As Integer
    Dim nextVal As Integer
    
    ' 这里修改源数据所在的工作表和列（示例为Sheet1的A列）
    Set sourceRange = ThisWorkbook.Sheets("Sheet1").Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    lastRow = sourceRange.Rows.Count
    
    ' 目标数据输出到B列
    Set targetRange = ThisWorkbook.Sheets("Sheet1").Range("B1:B" & lastRow)
    
    ' 初始化第一个值
    currentVal = WorksheetFunction.Min(sourceRange.Cells(1, 1).Value, 100)
    targetRange.Cells(1, 1).Value = currentVal
    
    ' 遍历处理每一行
    For i = 2 To lastRow
        nextVal = sourceRange.Cells(i, 1).Value
        ' 确保严格递增且不超100
        currentVal = WorksheetFunction.Min(WorksheetFunction.Max(currentVal + 1, nextVal), 100)
        targetRange.Cells(i, 1).Value = currentVal
        
        ' 如果已经到100，后续所有值设为100（提前退出循环提升效率）
        If currentVal = 100 Then
            targetRange.Cells(i + 1, 1).Resize(lastRow - i).Value = 100
            Exit For
        End If
    Next i
End Sub

运行脚本后，B列就会生成符合要求的清洗后数据。

内容的提问来源于stack exchange，提问作者Life Of A Guardian