Excel 随机数总变？教你用 LAMBDA 打造可复现的种子随机数生成器

葡萄城技术团队
37 阅读7分钟

在这里插入图片描述

🔥 要点概览

  1. Excel 原生 RAND/RANDARRAY 为易失性函数，工作表任意编辑都会触发重算，无法保留固定随机结果；
  2. 基于 Park-Miller 算法+Excel LAMBDA 函数可构建种子随机数生成器，实现可复现、按需更新的随机数；
  3. 搭配自定义 HASH 函数，支持字符串种子（如"apple"），大幅提升生成器的鲁棒性和易用性；
  4. 最终实现的 RAND_SEQUENCE 自定义函数可直接替代原生随机函数，适配随机分组、抽奖、模拟等各类场景。

🤔 痛点：Excel 原生随机函数的致命问题

用过 Excel 生成随机数的同学都知道，RAND、RANDBETWEEN、RANDARRAY 这类函数都属于易失性函数——只要对工作表做任何编辑（哪怕只是在空白单元格输入一个值），这些函数就会自动重算，随机结果直接改变。

比如老师想给学生随机分组，用公式=SORTBY(B5:B13,RANDARRAY(ROWS(B5:B13)))实现了随机排序，结果修改其他单元格时，分组结果反复变化，完全无法固定。

传统的解决办法是复制→选择性粘贴→数值，但这种方式会破坏原公式，想要重新生成随机结果又要重新写公式，步骤繁琐且低效。

而种子随机数生成器（Seeded RNG）就是解决这个问题的最优解：给一个种子值，生成固定的随机序列；更换种子值，生成新序列；相同种子值，永远返回相同结果，完美实现可复现的随机性。

🧠 核心原理：Park-Miller 最小标准 LCG 算法

本次我们基于 Park-Miller 算法实现种子随机数，这是一种成熟的线性同余生成算法，核心逻辑是通过初始种子→乘法→取模的循环操作，生成伪随机整数，再归一化到 0～1 之间。

核心公式逻辑：

  1. 定义常数：乘数a=16807，模数m=2147483647（2^31-1，32 位有符号整数最大值）；
  2. 初始种子seed经过计算：MOD(a*seed, m)，得到下一个随机整数；
  3. 将随机整数除以m，归一化得到 0～1 之间的随机小数；
  4. 循环执行，以上一次的结果作为新种子，生成连续的随机序列。

示例计算（种子=42）：

=LET(
  seed,42,
  a,16807,
  m,2147483647,
  MOD(a*seed,m)
) // 返回705894

以 705894 为新种子，再次计算得到 1126542223，以此类推，最终除以 m 得到 0～1 的随机数。

🛠 分步实现：用 LAMBDA 构建 RAND_SEQUENCE 函数

Excel 的 LAMBDA 函数支持自定义可复用函数，结合 SCAN、LET 等函数，我们可以实现支持溢出输出的种子随机数函数，并命名为RAND_SEQUENCE，最终实现传种子+生成数量，直接返回随机序列。

步骤 1：用 SCAN 实现种子序列的循环生成

SCAN 函数的核心作用是累积计算并返回每一步结果，完美适配 Park-Miller 算法的循环种子生成逻辑，语法：

=SCAN([initial_value],array,lambda)

利用 SEQUENCE 控制生成数量，SCAN 循环计算种子序列，示例公式：

=LET(
  seed,42,
  n,3,
  a,16807,
  m,2147483647,
  step,LAMBDA(s,_,MOD(a*s,m)),
  results,SCAN(seed,SEQUENCE(n),step),
  results/m
) // 返回{705894/m;1126542223/m;1579310009/m}

注：step 函数的第二个参数用_表示，因为我们仅用 SEQUENCE 控制循环次数，无需使用其具体值。

步骤 2：封装为 LAMBDA 自定义函数

将上述公式封装为 LAMBDA 函数，把seed和n设为入参，实现可调用的基础版本：

=LAMBDA(seed,n,
  LET(
    a,16807,
    m,2147483647,
    step,LAMBDA(s,_,MOD(a*s,m)),
    results,SCAN(seed,SEQUENCE(n),step),
    results/m
  )
)

步骤 3：通过名称管理器注册函数

  1. 点击 Excel 菜单栏「公式」→「名称管理器」→「新建」；
  2. 名称输入：RAND_SEQUENCE；
  3. 引用位置粘贴上述 LAMBDA 公式，点击确定；
  4. 完成后即可像原生函数一样调用：=RAND_SEQUENCE(42,3)。

步骤 4：优化种子归一化，提升鲁棒性

基础版本对种子值有要求（非 0、小于 m），添加种子归一化逻辑，避免非法种子导致的错误：

=LAMBDA(seed,n,
  LET(
    a,16807,
    m,2147483647,
    start,IF(INT(seed)=0,1,MOD(INT(seed),m)),
    step,LAMBDA(s,_,MOD(a*s,m)),
    results,SCAN(start,SEQUENCE(n),step),
    results/m
  )
)

🚀 进阶优化：添加 HASH 函数，支持字符串种子

基础版本仅支持整数种子，存在两个问题：

  1. 小种子（1-100）生成的第一个随机数偏小；
  2. 相邻种子（如 42 和 43）生成的随机序列高度相似。

解决方案是实现自定义 HASH 函数，将字符串转换为大整数种子，既解决上述问题，又让种子更易记忆（如"apple"、"orange"）。

步骤 1：实现 DJB2 哈希的 LAMBDA 函数

采用经典的 DJB2 哈希算法，将字符串转换为 32 位整数，公式如下：

=LAMBDA(str,
  LET(
    s,TEXT(str,"@"),
    bytes,UNICODE(MID(s,SEQUENCE(LEN(s)),1)),
    h0,5381,
    step,LAMBDA(h,c,MOD(h*33+c,2^32)),
    REDUCE(h0,bytes,step)
  )
)

同样通过「名称管理器」注册为HASH，调用示例：

=HASH("apple") // 返回253337143
=HASH("orange") // 返回319921761

步骤 2：整合 HASH 到 RAND_SEQUENCE

让 RAND_SEQUENCE 支持字符串种子，最终完整版公式：

=LAMBDA(seed,n,
  LET(
    a,16807,
    m,2147483647,
    h,HASH(seed),
    start,IF(INT(h)=0,1,MOD(INT(h),m)),
    step,LAMBDA(s,_,MOD(a*s,m)),
    results,SCAN(start,SEQUENCE(n),step),
    results/m
  )
)

此时可直接用字符串作为种子，调用更友好：

=RAND_SEQUENCE("apple",3) // 返回{0.710028;0.444729;0.560484}
=RAND_SEQUENCE("orange",10) // 溢出输出10个随机数

✅ 实战场景：用 RAND_SEQUENCE 实现稳定的学生随机分组

替代原生的 RANDARRAY，用 RAND_SEQUENCE 实现永不随意变化的随机排序，核心公式：

在这里插入图片描述

=LET(
  students,B3:B11,
  SORTBY(students,RAND_SEQUENCE(G3,ROWS(students)))
)

核心优势：

  1. 工作表任意编辑，分组结果不会改变；
  2. 更换 G3 的种子值（如从"apple"改为"orange"），立即生成新的随机分组；
  3. 恢复原种子值，可精准复现之前的分组结果，完美适配教学、抽奖等场景。

📦 快速复用：将自定义函数复制到其他工作簿

无需手动重新注册 HASH 和 RAND_SEQUENCE，直接复制已有工作表即可实现函数复用：

  1. 打开包含两个自定义函数的 Excel 文件，找到对应工作表；
  2. 右键工作表名称→「移动或复制」；
  3. 「目标工作簿」选择需要添加函数的文件，勾选「建立副本」，点击确定；
  4. 可删除复制过来的工作表，自定义函数会保留在目标工作簿中，直接调用即可。

💬 写在最后

本文基于 Excel 的 LAMBDA 生态，实现了可复现、健壮性 的种子随机数生成器，彻底解决了原生随机函数易失性的痛点，适配随机分组、金融模拟、抽奖、数据洗牌等几乎所有需要稳定随机数的场景。

其实 Excel 的 LAMBDA 函数还能实现更多高级功能，比如自定义数据校验、批量处理、复杂数组计算等，完全可以把 Excel 打造成轻量级的无代码开发工具。

最后想问问大家：你在工作中是否遇到过 Excel 随机数乱变的问题？又是怎么解决的？还有哪些 LAMBDA 函数的高级用法想了解？欢迎在评论区留言交流～

avatar
文章
阅读
粉丝