获取整数的位数_【Excel公式教程】生成指定范围不重复随机整数，这个公式你看得懂么？...-CSDN博客

本文链接：https://blog.csdn.net/weixin_39991531/article/details/111695606

点上方关注 Excel基础学习园地

公众号“Excel基础学习园地”是一个免费发布Excel基础知识、函数应用、操作技巧、学习方法等资讯的公众号，请点击上方“Excel基础学习园地”添加关注，方便我们每天向您推送精彩资讯。

加老师微信带你入Excel的坑

验证信息：加Excel交流群
Excel交流群免费加入

公众号回复2016，可以获得office2016的下载链接

如何快速生成一组不重复的随机数，这个问题历来就是很多人都在讨论的话题，通常都是使用辅助列来完成。

偶然在网上看到一个非常经典的公式，特此与诸位朋友共同解读此公式，一定记得分享出去让更多人知道。

引子：公式介绍

为了便于分析公式原理，我们以得到十个不重复随机数为例，公式为：

=MOD(SMALL(RANDBETWEEN(-Z1:Z10,10)/1%+ROW(1:10),ROW()),100)

例如需要得到5个1到10之间的不重复随机数，就需要选中五个单元格，输入公式后同时按住Ctrl、shift再回车，这个数组公式是不能通过下拉完成输入的，是区域数组公式，如图：

这个公式的随机数范围就是十个，如果单元格数量多于10的话，多出来的部分将会是错误值，如图：

通过上面这个图可以看出来，确实是十个不重复的数值，而且每按一次f9，或者进行一次操作后，数值都会发生变化。

(f9或者操作是为了使公式进行重新计算从而得到新的一组结果)

如果想得到10个1到50的不重复随机数，公式可以修改为：

=MOD(SMALL(RANDBETWEEN(-Z1:Z50,50)/1%+ROW(1:50),ROW()),100)

选择10个单元格后输入数组公式即可。

解析1：RANDBETWEEN介绍

以上是对公式用法的一个简单说明，其实重点是这个公式的原理分析。为了让新手也能够大致看明白，就需先来了解这个问题的一般处理原理，希望大家能够耐心看下去。

这个公式的核心部分是RANDBETWEEN(-Z1:Z10,10)，RANDBETWEEN本来是一个非常容易理解的函数，就是得到两个数值之间的随机整数：

本例中RANDBETWEEN第一参数使用了一个数组，实际上原本是这样写的：RANDBETWEEN(ROW(1:10)^0,10)，目的是得到10个1到10的随机整数，ROW(1:10)这个在数组公式的应用中很常见了，就是1到10的十个数字，加^0是为了得到十个1，需要说明一点的是，这个地方的RANDBETWEEN只是为了得到十个随机数，第二参数不一定非要用10，关键是第一参数是十个数字构成的数组。最终结果里的随机数范围不是通过这里指定的，后面会解释这一点。

高手们追求的就是尽可能的缩短公式的字数，因此在得到十个数字的数组这一点上，放弃了常用的ROW(1:10)，直接使用了单元格引用，也就是Z1:Z10，这里要说明的有两点，这个区域必须是10个单元格，同时单元格都是空的。因此G1:G10也是可以的，不一定非得是Z1:Z10。第二点，直接使用空白的单元格区域在数组计算的时候会发生错误，如图：

高手们在这个区域前加了运算符就搞定了：

这里-换成+同样有效。

这就是RANDBETWEEN(-Z1:Z10,10)的由来，相比RANDBETWEEN(ROW(1:10)^0,10)，少了好几个字符。

作用是得到十个随机数，并不是一定要一到十的随机数，同时也不需要理会是否有重复。

解析2：不重复随机数的本质

接下来的部分比较烧脑了，为了便于新手理解，需要简单的解释一下获取不重复随机数的原理，通常是使用辅助列，先得到一组随机数：

随机数的个数决定最终的随机数范围，例如要得到1到50的不重复随机整数，这里就要有五十个随机数。

接下来对这组随机数进行排名次：

最终我们需要的实际上就是这列排名次的结果，随着随机数变化了以后，名次也会变化，但是不管怎么变，肯定都是1到10，因为只有10个数字(随机数的个数决定了最终需要的随机数的大小原因在于此)，同时这些随机数大小不会出现相同的(这一点很重要)，因此不会出现名次相同的情况(不重复就是这样来的)。

假如明白了这一点，后面的内容相对就会好理解一些，第一部分已经得到了十个随机数个数是确定的，但是会有重复的数据，排名次也会有重复，因此接下来就是要解决重复随机数的问题……

解析3：排除重复值的套路

RANDBETWEEN(-Z1:Z10,10)/1%+ROW(1:10)这部分就是排除重复数的一种常见套路，本来是这样的：RANDBETWEEN(0,10)*100+ROW(a1)，我们对比一下效果：

看懂了吗？

还是要说明两点：为什么是*100？

因为随机数的位数最高是两位(有可能是10)，*100就能保证后两位都是0，换句话说，*100的数字里最右边的两位就是之前的随机数。

第二点，+ROW(a1)的作用就是当出现重复数据的时候，人为的分个高低出来。

现在来说说为什么*100变成了/1%？

因为这两个方法得到的结果一样，/1%比*100少了一个字，仅此而已，由此也可看出大神们为了节省一个字都是挖空心思啊，所谓一字千金也不过如是……

最后，因为是数组公式，同时是十个不重复随机数，所以+ROW(a1)变成了+ROW(1:10)，这是RANDBETWEEN(-Z1:Z10,10)/1%+ROW(1:10)的由来，作用说白了就是得到了10个不重复的随机数。

解析4：排名次

结合第二部分的解释，10个不重复随机数有了，接下来就需要对这10个随机数排名次，而名次的结果就是我们需要的。

与使用辅助列的方法不同，现在我们得到的10个不重复随机数实际是一个内存数组，也就是无法实际看到，都是公式的一部分内容，要给这样的数据排名次几乎不可能，所以把排名次换了个思路，变成从小到大的排序，而使用公式排序，自然少不了SMALL函数，这就有了：SMALL(RANDBETWEEN(-Z1:Z10,10)/1%+ROW(1:10),ROW())

注意，如果公式不是从第一行写的话，SMALL第二参数要写成ROW(1:10)

图中这个公式的结果就是从小到大排序的10个不重复随机数，根据前面的分析可以知道，这个数字是两部分，随机数*100+ROW(1:10)的，因为无法排名次，是通过排序得到的结果，排序之前，这组数据中的最右边两位也就是+ROW(1:10)是从1到10递增的，排序之后，这个顺序也乱了(这与使用辅助列得到名次的效果一样)，由此得知，只要再把这个结果的右边两位提取出来就OK了。

解析5：终成

最后这一步就非常简单了，常见的方法是mod(数据，100)或者是right(数据，2)*1。

因为right得到的结果是文本类型，需要经过计算变成数值，高手们肯定选择短的啊，这就是=MOD(SMALL(RANDBETWEEN(-Z1:Z10,10)/1%+ROW(1:10),ROW()),100)的全部解释。

为什么是mod(数据，100)？

一个数除以100得到的余数就是百位以下的数，小学数学问题哦~~~~