randbetween函数验证公式

今天作者要特别来讲一下关于提取指定数字范围内的随机不重复的数字，所应用的可套公式，是否能够达到准确的效果？！作者将通过几种方式来验证并解析公式的结果！

本文内容绝对干货，很细致地解析了公式的各组成部分的作用和含义。

在前面作者介绍了一个可以直接套用的公式来解答随机取不重复值的案例，大家可以参照《Excel如何随机生成指定区间范围内不重复的数字？看这个可套公式》！

但作者在评论区看到许多质疑的声音，认为公式不仅复杂，而且无法获取需要的随机不重复值，没有任何实际意义。

但公式逻辑是很清晰的，作者也进行了多次核验，因此通过这篇文章来验证公式，并讲解公式的运算逻辑。

首先我们使用条件格式功能来高亮显示重复值。

具体操作如下动图所示，比较简单，首先选取数据区域，然后点击条件格式——突出显示单元格规则--重复值，随后设置填充颜色来显示重复值的单元格。

但通过动图的操作演示，可以看到并没有重复值被高亮强调显示！

那是不是可以认为这个公式是准确无误的呢！？

接下来我们再来看看公式验证的过程。

也比较简单，首先创建一个计算数据区域单元格个数的公式，如下：

=COUNTA(F1:F109)

counta函数的含义是计算区域内的非空单元格个数，这里得到结果为109.

然后再输入sum+countif函数的组合公式：

{=SUM(COUNTIF(F1:F109,F1:F109))}

这里countif函数的条件使用了单元格区域，其结果也会得到一个数组，会依次计算从F1到F109的值在单元格区域F1:F109中的个数。

那很明显，如果有重复值，那么countif函数计数则不等于1，重复一次，就等于2，重复2次，就等于3.

最后再组合sum函数进行汇总求和。而从下图可见公式结果等于109，则代表数据区域中不存在重复值。

通过上面两种方法来验证公式的有效性，我们姑且认为公式的结果是准确的。

但公式的运算逻辑到底是怎么样的呢？

现在上公式：

{=--RIGHT(SMALL(RANDBETWEEN(ROW($1:$109)^0,1000)*1000+ROW($1:$109),ROW($1:$109)),3)}

我们来分解一下公式，right函数是提取字符串右侧指定个数的字符，它的第1参数是small函数表达式，而small函数的第1参数是randbetween函数表达式与row函数的相加运算。

现在来看下表达式“RANDBETWEEN(ROW($1:$109)^0,1000)*1000”的解析结果。

“ROW($1:$109)”的结果是1至109的数组，它的0次幂等于1。所以randbetween函数将随机1到109之间任意数字。那这里为什么要使用row函数并进行0次幂运算？

理由很简单，因为row函数能够得到一个数组结果，通过嵌套row函数，randbetween函数表达式将同样可以得到一个数组结果，所以当randbetween函数的结果再乘以1000，就得到了1至109乘以1000的随机数字，并且得到的数字后三位都是0，其结果正如下图所示。

接下来再加上“ROW($1:$109)”，由于它的结果值是1-109的数字，而且row函数得出的结果值是不会重复的。而randbetween函数得到的都是后3位为0的数字，不管randbetween函数随机了哪组数字，它的后三位总是为0，因此与row函数相加后，其数字后3位将得到的1-109的数字，且是不重复的！

于是当我们使用right函数来提取右侧3位字符时，正好是之前randbetween函数+row函数得到的1-109之间不重复的数字。

讲到这里，不知道大家能不能理解公式的运算逻辑。

它有两个特点，randbetween函数是随机出三个0之前的数字，row函数是获取不重复的指定区间的数值，两者相加，再利用small函数进行排序，最后输出不重复的随机结果值！

关于small函数在公式中的作用，作者举个例子，比如randbetween函数随机生成了939000,246000……，加上row函数的1,99……，便得到结果939001,246099……，使用small函数来排序，那明显246099要排在939001，于是在运算公式时，第1个单元格等于99，而第2个单元格等于1。

而如果不添加small函数，得到的结果就是从1开始，然后2、3、4……直到109，也就不算是随机取值了！

讲到这里，作者也是重温了一次公式应用，也更加笃定这个可套公式是可以输出有效结果的！

童鞋们还有质疑或者不懂的，可以复制公式到表格中实操练习！

举报/反馈