Excel 制作随机抽奖函数公式分享

原文标题：《哪位 Excel 高人琢磨出的这个随机抽奖技巧，太绝了！》

年前我们发布过一篇文章《我用 Excel 做了一个年会抽奖器，然后老板点了一下……》，这其中的核心是获取不重复随机整数。

获取不重复随机整数的方法有很多，迭代计算太过鸡肋，VBA 编程未免牛刀杀鸡。

今天，小花就跟大家分享几个实用的函数公式，超赞的那种哦！

1、辅助排名法

RAND 函数的作用是生成 0-1 之间的随机数，于是我们只需通过 RAND 函数生成一组随机数，再将它们进行排名，就有极大概率可以得到一组不重复随机整数。

C 列 —— 辅助列公式：

=RAND()

B2—— 不重复随机整数公式：

=RANK(C2,$C$2:$C$11)

公式说明：

RAND 函数可以返回 0-1 之间的随机数；

RANK 函数则是返回某个数在一列数字中相对于其他数值的大小排名。

所以，先生成随机数值，再计算每个数的排名值，即可得到一组随机整数。

PS : 公式按【F9】键会重新计算，重新计算即可刷新排名组，获得新的不重复随机整数。本文其他案例同理。

这两个函数是我们取不重复随机数中最常见的一种方法。

它比较简单，容易学会。

不过这个公式有个致命 Bug 是，每一个 RAND 函数生成的随机数都是独立的，也就是说，这些随机数还是有极小的概率彼此相等，进而出现重复的排名值。如下图：

RAND 函数辅助法并不严谨，且需要辅助列，因此只能算是不重复随机整数的入门级解法。

接下来，小花来介绍两种比较严谨的方法，可能比较难 ，大家重在理解解决的思路，干货满满，一定要坚持看下去哦！

2、逐次消除法

获取不重复随机整数的核心问题是：如何让已经出现的数字不再出现？

解决思路：把出现的数字从抽取数字池中消除掉，然后再从剩余数字中随机取数，这样就能够生成一组不重复随机整数。

下面这个公式就是按照这个思路设置的。

B3—— 逐次消除法公式：

{=SMALL(    IF(      COUNTIF($B$1:B2,ROW($1:$10)),      "",      ROW($1:$10)),  RANDBETWEEN(1,12-ROW()))}

PS：该公式内含数组运算，输入公式后需按【Ctrl+Shift+Enter】函数才能正确运算。

公式比较复杂，大家跟着我的思路，进一步理解公式的原理。

B3 单元格公式说明：

① COUNTIF($B$1:B2,ROW($1:$10))

这一步的主要目的是：通过 Countif 函数进行数值计数，进而判断当前单元格的上一单元格数值是否有在 1 到 10 中出现过 。

当前单元格是 B3 ，$B$1:B2 是它前面的单元格。$B$1:B2 仅锁定起始单元格，表示从首个单元格 B1 到当前单元格的上一单元格，这就涵盖了全部已抽取的整数。

ROW ($1:$10) 返回 1 到 10 的有序数组 {1;2;3;4;5;6;7;8;9;10}，COUNTIF 函数统计这组数是否在全部已抽取的整数中出现，如出现返回 1，否则返回 0。

由于仅有数字 7 出现在 $B$1:B2 中，B2 单元格目前是 7 , 所以 COUNTIF 数组运算后返回数组：

{ 1 ; 2 ; 3 ; 4 ; 5 ; 6 ; 7 ; 8 ; 9 ; 10 }

{ 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 1 ; 0 ; 0 ; 0 }

② IF(①,"",ROW($1:$10))

如下图，将①中结果数组 {0;0;0;0;0;0;1;0;0;0} 作为逻辑判断值，1 等价于 TRUE，0 等价于 FALSE。

IF 函数根据逻辑判断值，TRUE 返回空 ""，FALSE 返回对应的数字。

于是整个公式片段②就完成了将有序数组 {1;2;3;4;5;6;7;8;9;10} 中在 $B$1:B2 已出现的数字替换为空，实现逐次消除。

将 B2 单元格中的 7 替换为空 ""，这样后续随机取数不会抽到前面所出现的数值。

{ 1 ; 2 ; 3 ; 4 ; 5 ; 6 ; "" ; 8 ; 9 ; 10 }

③ {SMALL(②,RANDBETWEEN(1,12-ROW()))}

这一步的目的主要是随机取数。

SMALL 函数主要用来提取数组中第 k 个最小值的数值，忽略空值。

RANDBETWEEN 则负责在指定范围内，随机抽取一个值作为 k。

为确保每个数被抽取的概率一致且不出错，k 的最小值（即 Bottom）必须等于 1，最大值（即 TOP）必须始终与②结果数组中的数字个数一致。

公式中，用 12-ROW() 作为 TOP 的值。ROW () 用于返回当前单元格的行数，在 B2 时，12-ROW () 为 10，逐行递减 1，B3 为 9，刚好始终与②{ 1 ; 2 ; 3 ; 4 ; 5 ; 6 ; "" ; 8 ; 9 ; 10 } 中剩余可抽取的数字个数一致。

RANDBETWEEN 函数在 1 和剩余个数 9 之间随机取数 k，再使用 SMALL 函数来取对应第 k 小的数字 m，最终再获得一个不重复随机数，以此类推，即可得到一组不重复随机整数。

掌握了随机不重复整数公式后，只需稍加变形，我们就可以借助 INDEX 函数实现随机抽奖等高端操作。下图就是利用逐次消除法随机抽取 5 名获奖人员的案例。

D3—— 随机抽奖公式：

{=INDEX($A$2:$A$11,SMALL(IF(COUNTIF($D$1:D2,$A$2:$A$11),"",ROW($1:$10)),RANDBETWEEN(1,12-ROW())))}

3、乱序取余法

如果能够将指定范围内的数字随机打乱，那么只需按打乱后的顺序依次抽取数字就能够得到一组不重复随机数。

下面这个公式就是将有序数组加上 10 的任意倍数来打乱数字的大小次序，再使用 LARGE 或 SMALL 函数来依次取值，取余，即最终生成随机不重复整数。

B2:B11 数组公式 —— 乱序取余法：

{=MOD(  LARGE(    RANDBETWEEN(ROW(1:10)^0,10)*100+ROW(1:10),    ROW(1:10)),100)}

PS：该公式为数组公式，其输出结果为一组数，需占用 B2:B11 单元格才能正确显示。

因此，需先选择 B2:B11 单元格，输入公式后，再按【Ctrl+Shift+Enter】。不能像逐次消除法公式那样在单个单元格里按【Ctrl+Shift+Enter】执行数组运算后再拖动填充，切记！

下面进行简单的公式解释。

公式说明：

ROW (1:10)^0 将 1 到 10 的有序数组 A {1;2;3;4;5;6;7;8;9;10} 取其 0 次幂，得到 10 个 1 组成的常数数组 B {1;1;1;1;1;1;1;1;1;1}。

PS : 在数学中，任何非 0 的 0 次幂，结果都为 1 。

RANDBETWEEN(ROW(1:10)^0,10)

使用 RANDBETWEEN 函数来生成 10 个彼此独立的从 1 到 10 的随机整数（可以重复），得到随机数组 C {10;6;3;8;4;2;8;5;4;7}。

数组 C*100+ROW (1:10) 将数组 C 扩大 100 倍再加上有序数组 A，得到 (不重复) 数组 D {1001;602;303;804;405;206;807;508;409;710}。

数组 D 的特点是其尾数依次是有序数组 A {1;2;3;4;5;6;7;8;9;10}，但其大小却由位于百位的随机数组随机数组 C {10;6;3;8;4;2;8;5;4;7} 决定，这就实现了将有序数组 A 随机打乱。

{=MOD(  LARGE(    RANDBETWEEN(ROW(1:10)^0,10)*100+ROW(1:10),    ROW(1:10)),100)}

通过 LARGE 函数依次取最大的第 1 到第 10 的数字，再取 100 的余数，就可以将数组 A {1;2;3;4;5;6;7;8;9;10} 中的每个数字重新剥离出来，

但此时他们的次序已经按随机数组 C 的大小进行了重排，得到公式计算结果数组 E {1;7;4;10;2;8;9;5;3;6}，数组运算将结果数组依次填列在 B2:B11 单元格中。

运用乱序取余法公式，可以轻松解决随机分组问题。

我们来简单看看一个基础案例。

如下图，将一组姓名随机分为 2 组。

B2:B11 数组公式 —— 乱序取余法：

{=MOD(    MOD(      LARGE(RANDBETWEEN(ROW(1:10)^0,10)*100+ROW(1:10),ROW(1:10))    ,100), 2)+1&"组"}

学会这三种方法，不重复随机取值的做法就难不倒大家啦~

4、最后的话

本文小花分享的获取随机不重复整数的三个函数公式，包括：

❶ 辅助排名法：

通过 RAND 构建辅助列，再使用 RANK 进行排名；

❷ 逐次消除法：

使用 IF+COUNTIF 消除已出现值，在用 SMALL 随机取值；

❸ 乱序取余法：

构建随机数组 * 100 + 有序数组，用 LARGE 进行排序后再由 MOD 取余数；

大家学会了吗？这下平时的抽奖和团建随机组队不用愁了吧！

本文来自微信公众号：秋叶 Excel （ID：excel100），作者：小花

广告声明：文内含有的对外跳转链接（包括不限于超链接、二维码、口令等形式），用于传递更多信息，节省甄选时间，结果仅供参考，IT之家所有文章均包含本声明。