如何快速生成一組不重復(fù)的隨機(jī)數(shù)，這個(gè)問(wèn)題歷來(lái)就是很多人都在討論的話題，通常都是使用輔助列來(lái)完成。偶然在網(wǎng)上看到一個(gè)非常經(jīng)典的公式，欣喜萬(wàn)分，特此與諸位朋友共同解讀此公式，一定記得分享出去讓更多人知道，同時(shí)向公式的原作者 夢(mèng)幻小丑 致敬。
為了便于分析公式原理，我們以得到十個(gè)不重復(fù)隨機(jī)數(shù)為例，公式為：=MOD(SMALL(RANDBETWEEN(-Z1:Z10,10)/1%+ROW(1:10),ROW()),100)
例如需要得到5個(gè)1到10之間的不重復(fù)隨機(jī)數(shù)，就需要選中五個(gè)單元格，輸入公式后同時(shí)按住Ctrl、shift再回車，這個(gè)數(shù)組公式是不能通過(guò)下拉完成輸入的，是區(qū)域數(shù)組公式，如圖：
這個(gè)公式的隨機(jī)數(shù)范圍就是十個(gè)，如果單元格數(shù)量多于10的話，多出來(lái)的部分將會(huì)是錯(cuò)誤值，如圖：
通過(guò)上面這個(gè)圖可以看出來(lái)，確實(shí)是十個(gè)不重復(fù)的數(shù)值，而且每按一次f9，或者進(jìn)行一次操作后，數(shù)值都會(huì)發(fā)生變化。（f9或者操作是為了使公式進(jìn)行重新計(jì)算從而得到新的一組結(jié)果）
如果想得到10個(gè)1到50的不重復(fù)隨機(jī)數(shù)，公式可以修改為：
=MOD(SMALL(RANDBETWEEN(-Z1:Z50,50)/1%+ROW(1:50),ROW()),100)
選擇10個(gè)單元格后輸入數(shù)組公式即可。
以上是對(duì)公式用法的一個(gè)簡(jiǎn)單說(shuō)明，其實(shí)重點(diǎn)是這個(gè)公式的原理分析。為了讓新手也能夠大致看明白，就需先來(lái)了解這個(gè)問(wèn)題的一般處理原理，希望大家能夠耐心看下去。
這個(gè)公式的核心部分是RANDBETWEEN(-Z1:Z10,10)，RANDBETWEEN本來(lái)是一個(gè)非常容易理解的函數(shù)，就是得到兩個(gè)數(shù)值之間的隨機(jī)整數(shù)：
本例中RANDBETWEEN第一參數(shù)使用了一個(gè)數(shù)組，實(shí)際上原本是這樣寫的：RANDBETWEEN(ROW(1:10)^0,10)，目的是得到10個(gè)1到10的隨機(jī)整數(shù)，ROW(1:10)這個(gè)在數(shù)組公式的應(yīng)用中很常見(jiàn)了，就是1到10的十個(gè)數(shù)字，加^0是為了得到十個(gè)1，需要說(shuō)明一點(diǎn)的是，這個(gè)地方的RANDBETWEEN只是為了得到十個(gè)隨機(jī)數(shù)，第二參數(shù)不一定非要用10，關(guān)鍵是第一參數(shù)是十個(gè)數(shù)字構(gòu)成的數(shù)組。最終結(jié)果里的隨機(jī)數(shù)范圍不是通過(guò)這里指定的，后面會(huì)解釋這一點(diǎn)。
高手們追求的就是盡可能的縮短公式的字?jǐn)?shù)，因此在得到十個(gè)數(shù)字的數(shù)組這一點(diǎn)上，放棄了常用的ROW(1:10)，直接使用了單元格引用，也就是Z1:Z10，這里要說(shuō)明的有兩點(diǎn)，這個(gè)區(qū)域必須是10個(gè)單元格，同時(shí)單元格都是空的。因此G1:G10也是可以的，不一定非得是Z1:Z10。第二點(diǎn)，直接使用空白的單元格區(qū)域在數(shù)組計(jì)算的時(shí)候會(huì)發(fā)生錯(cuò)誤，如圖：
高手們?cè)谶@個(gè)區(qū)域前加了運(yùn)算符就搞定了：
這里-換成+同樣有效。
這就是RANDBETWEEN(-Z1:Z10,10)的由來(lái)，相比RANDBETWEEN(ROW(1:10)^0,10)，少了好幾個(gè)字符。
作用是得到十個(gè)隨機(jī)數(shù)，并不是一定要一到十的隨機(jī)數(shù)，同時(shí)也不需要理會(huì)是否有重復(fù)。
接下來(lái)的部分比較燒腦了，為了便于新手理解，需要簡(jiǎn)單的解釋一下獲取不重復(fù)隨機(jī)數(shù)的原理，通常是使用輔助列，先得到一組隨機(jī)數(shù)：
隨機(jī)數(shù)的個(gè)數(shù)決定最終的隨機(jī)數(shù)范圍，例如要得到1到50的不重復(fù)隨機(jī)整數(shù)，這里就要有五十個(gè)隨機(jī)數(shù)。
接下來(lái)對(duì)這組隨機(jī)數(shù)進(jìn)行排名次：
最終我們需要的實(shí)際上就是這列排名次的結(jié)果，隨著隨機(jī)數(shù)變化了以后，名次也會(huì)變化，但是不管怎么變，肯定都是1到10，因?yàn)橹挥?0個(gè)數(shù)字（隨機(jī)數(shù)的個(gè)數(shù)決定了最終需要的隨機(jī)數(shù)的大小原因在于此），同時(shí)這些隨機(jī)數(shù)大小不會(huì)出現(xiàn)相同的（這一點(diǎn)很重要），因此不會(huì)出現(xiàn)名次相同的情況（不重復(fù)就是這樣來(lái)的）。
假如明白了這一點(diǎn)，后面的內(nèi)容相對(duì)就會(huì)好理解一些，第一部分已經(jīng)得到了十個(gè)隨機(jī)數(shù)個(gè)數(shù)是確定的，但是會(huì)有重復(fù)的數(shù)據(jù)，排名次也會(huì)有重復(fù)，因此接下來(lái)就是要解決重復(fù)隨機(jī)數(shù)的問(wèn)題……
RANDBETWEEN(-Z1:Z10,10)/1%+ROW(1:10)這部分就是排除重復(fù)數(shù)的一種常見(jiàn)套路，本來(lái)是這樣的：RANDBETWEEN(0,10)*100+ROW(a1)，我們對(duì)比一下效果：
看懂了嗎？
還是要說(shuō)明兩點(diǎn)：為什么是*100？
因?yàn)殡S機(jī)數(shù)的位數(shù)最高是兩位（有可能是10），*100就能保證后兩位都是0，換句話說(shuō)，*100的數(shù)字里最右邊的兩位就是之前的隨機(jī)數(shù)。
第二點(diǎn)，+ROW(a1)的作用就是當(dāng)出現(xiàn)重復(fù)數(shù)據(jù)的時(shí)候，人為的分個(gè)高低出來(lái)。
現(xiàn)在來(lái)說(shuō)說(shuō)為什么*100變成了/1%？
因?yàn)檫@兩個(gè)方法得到的結(jié)果一樣，/1%比*100少了一個(gè)字，僅此而已，由此也可看出大神們?yōu)榱斯?jié)省一個(gè)字都是挖空心思啊，所謂一字千金也不過(guò)如是……
最后，因?yàn)槭菙?shù)組公式，同時(shí)是十個(gè)不重復(fù)隨機(jī)數(shù)，所以+ROW(a1)變成了+ROW(1:10)，這是RANDBETWEEN(-Z1:Z10,10)/1%+ROW(1:10)的由來(lái)，作用說(shuō)白了就是得到了10個(gè)不重復(fù)的隨機(jī)數(shù)。
結(jié)合第二部分的解釋，10個(gè)不重復(fù)隨機(jī)數(shù)有了，接下來(lái)就需要對(duì)這10個(gè)隨機(jī)數(shù)排名次，而名次的結(jié)果就是我們需要的。
與使用輔助列的方法不同，現(xiàn)在我們得到的1哦給不重復(fù)隨機(jī)數(shù)實(shí)際是一個(gè)內(nèi)存數(shù)組，也就是無(wú)法實(shí)際看到，都是公式的一部分內(nèi)容，要給這樣的數(shù)據(jù)排名次幾乎不可能，所以把排名次換了個(gè)思路，變成從小到大的排序，而使用公式排序，自然少不了SMALL函數(shù)，這就有了：SMALL(RANDBETWEEN(-Z1:Z10,10)/1%+ROW(1:10),ROW())
注意，如果公式不是從第一行寫的話，SMALL第二參數(shù)要寫成ROW(1:10)
圖中這個(gè)公式的結(jié)果就是從小到大排序的10個(gè)不重復(fù)隨機(jī)數(shù)，根據(jù)前面的分析可以知道，這個(gè)數(shù)字是兩部分，隨機(jī)數(shù)*100+ROW(1:10)的，因?yàn)闊o(wú)法排名次，是通過(guò)排序得到的結(jié)果，排序之前，這組數(shù)據(jù)中的最右邊兩位也就是+ROW(1:10)是從1到10遞增的，排序之后，這個(gè)順序也亂了（這與使用輔助列得到名次的效果一樣），由此得知，只要再把這個(gè)結(jié)果的右邊兩位提取出來(lái)就OK了。
最后這一步就非常簡(jiǎn)單了，常見(jiàn)的方法是mod（數(shù)據(jù)，100）或者是right（數(shù)據(jù)，2）*1
因?yàn)閞ight得到的結(jié)果是文本類型，需要經(jīng)過(guò)計(jì)算變成數(shù)值，高手們肯定選擇短的啊，這就是=MOD(SMALL(RANDBETWEEN(-Z1:Z10,10)/1%+ROW(1:10),ROW()),100)的全部解釋。
為什么是mod（數(shù)據(jù)，100）？
一個(gè)數(shù)除以100得到的余數(shù)就是百位以下的數(shù)，小學(xué)數(shù)學(xué)問(wèn)題哦~~~~
不愧為神公式，解釋都用了這么長(zhǎng)，更別說(shuō)能想到這樣的思路了，再次像大神致敬！
其實(shí)對(duì)于我們這樣的普通用戶來(lái)說(shuō)，大多數(shù)問(wèn)題都是可以使用輔助列解決的，而掌握一些常用的函數(shù)就能解決，更重要的是扎實(shí)的基礎(chǔ)，至于以后能夠有多高的水平，還是需要一些悟性的
聯(lián)系客服
