|
如何快速生成一组不重复的随机数，这个问题历来就是很多人都在讨论的话题，通常都是使用辅助列来完成。偶然在网上看到一个非常经典的公式，欣喜万分，特此与诸位朋友共同解读此公式，一定记得分享出去让更多人知道，同时向公式的原作者 梦幻小丑 致敬。 为了便于分析公式原理，我们以得到十个不重复随机数为例，公式为：=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个单元格后输入数组公式即可。 以上是对公式用法的一个简单说明，其实重点是这个公式的原理分析。为了让新手也能够大致看明白，就需先来了解这个问题的一般处理原理，希望大家能够耐心看下去。 这个公式的核心部分是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)，少了好几个字符。 作用是得到十个随机数，并不是一定要一到十的随机数，同时也不需要理会是否有重复。 接下来的部分比较烧脑了，为了便于新手理解，需要简单的解释一下获取不重复随机数的原理，通常是使用辅助列，先得到一组随机数： 随机数的个数决定最终的随机数范围，例如要得到1到50的不重复随机整数，这里就要有五十个随机数。 接下来对这组随机数进行排名次： 最终我们需要的实际上就是这列排名次的结果，随着随机数变化了以后，名次也会变化，但是不管怎么变，肯定都是1到10，因为只有10个数字（随机数的个数决定了最终需要的随机数的大小原因在于此），同时这些随机数大小不会出现相同的（这一点很重要），因此不会出现名次相同的情况（不重复就是这样来的）。 假如明白了这一点，后面的内容相对就会好理解一些，第一部分已经得到了十个随机数个数是确定的，但是会有重复的数据，排名次也会有重复，因此接下来就是要解决重复随机数的问题…… 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个不重复的随机数。 结合第二部分的解释，10个不重复随机数有了，接下来就需要对这10个随机数排名次，而名次的结果就是我们需要的。 与使用辅助列的方法不同，现在我们得到的1哦给不重复随机数实际是一个内存数组，也就是无法实际看到，都是公式的一部分内容，要给这样的数据排名次几乎不可能，所以把排名次换了个思路，变成从小到大的排序，而使用公式排序，自然少不了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了。 最后这一步就非常简单了，常见的方法是mod（数据，100）或者是right（数据，2）*1 因为right得到的结果是文本类型，需要经过计算变成数值，高手们肯定选择短的啊，这就是=MOD(SMALL(RANDBETWEEN(-Z1:Z10,10)/1%+ROW(1:10),ROW()),100)的全部解释。 为什么是mod（数据，100）？ 一个数除以100得到的余数就是百位以下的数，小学数学问题哦~~~~ 不愧为神公式，解释都用了这么长，更别说能想到这样的思路了，再次像大神致敬！ 其实对于我们这样的普通用户来说，大多数问题都是可以使用辅助列解决的，而掌握一些常用的函数就能解决，更重要的是扎实的基础，至于以后能够有多高的水平，还是需要一些悟性的
|
|
|
