基于单元格值生成唯一单列随机数数组的公式优化需求

针对你遇到的四舍五入后随机数重复的问题，我们可以通过先生成天然唯一的基础序列，再结合随机偏移和差值调整的方式，确保最终生成的所有值满足所有要求且唯一。以下是优化后的公式及说明：

优化后的公式 #

=SORT(LET(
    n, INT(N4/1000),
    min_val, 1000.01,
    max_extra, (N4 - n*min_val)/n,
    base_vals, min_val + SEQUENCE(n,1,0,0.01),
    x_, base_vals + RANDARRAY(n,1,0,max_extra,TRUE),
    scaled_x, x_ * (N4 / SUM(x_)),
    rounded_x, ROUND(scaled_x,2),
    sum_diff, N4 - SUM(rounded_x),
    adjusted_z, VSTACK(
        DROP(rounded_x,-1),
        ROUND(TAKE(rounded_x,-1) + sum_diff,2)
    ),
    final_z, IF(TAKE(adjusted_z,-1)=TAKE(adjusted_z,-2),
        VSTACK(
            DROP(adjusted_z,-2),
            TAKE(adjusted_z,-2) - SIGN(sum_diff)*0.01,
            TAKE(adjusted_z,-1) + SIGN(sum_diff)*0.01
        ),
        adjusted_z
    ),
    final_z
))

核心优化逻辑 #

1. 生成天然唯一的基础序列
   用SEQUENCE(n,1,0,0.01)生成间隔0.01的序列，加上最小值1000.01，确保初始基础值彼此唯一（如1000.01、1000.02、1000.03...），从根源避免后续四舍五入导致的重复。

2. 添加随机偏移保证随机性
   在基础序列上叠加RANDARRAY生成的随机值，既保留随机性，又因为基础序列的间隔存在，叠加后的值依然唯一。

3. 按比例缩放匹配目标总和
   将生成的随机序列按比例缩放，使其总和接近目标单元格N4的值，再通过四舍五入保留两位小数。

4. 差值调整与重复修正

   • 计算四舍五入后的总和与N4的差值，调整最后一个值补全总和。
   • 若调整后的最后一个值与前一个重复，通过微调前一个值和最后一个值（一个减0.01，一个加0.01），在保证总和不变的前提下消除重复。

满足的所有要求 #

• 生成数量为N4的千位整数部分（INT(N4/1000)）
• 最终总和严格等于N4
• 单列从小到大排序（SORT函数）
• 所有值保留两位小数
• 最小值≥1000.01（满足1000.xx及以上的要求）
• 所有生成值唯一

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