如何生成随机不重复整数序列

楼主 w83365040371	如图1.所示, 生成的序列是1-44之间的整数, 且组成序列的元素个数为44图1. A: 生成随机不重复序列, 我们一般使用辅助列或者数组函数公式 =SMALL(IF(COUNTIF(D$1:D3,ROW($1:$45)),99,ROW($1:$45)),RANDBETWEEN(1,44-COUNT(D$1:D3))) (结果见图2.,操作方法见附件) 这里我们重点讲述的是, 利用以生成随机不重复整数序列, (结果见图3.)公式为： =--RIGHT(SMALL(RANDBETWEEN(1,99+0*ROW(1:44))/1%+ROW(1:44),ROW(1:44)),2) 备注：--right(,2),可以被mod(,100)替代图2. 图3. 对于方法3，使用randbetween函数构建区域数组,存在的疑问是，这个公式的结果是否是正确的，即公式所生成的序列不能有重复. 对于这个问题，使用下面的公式对结果进行判断（输入公式后,重复按F9)，结果见图4. =IF(COUNT(0/(COUNTIF(H4:H47,ROW(1:44))<>1)),"错误","正确") 图4. 由图4.的结果我们可以得出结论，区域数组公式中的randbetween函数，不会在区域内的每个单元格中！！ randbetween.zip
2楼 w83365040371	图5. 如上图所示：当只有参数2为数组时,randbetween返回的具有跟参数2相同元素个数的数组; 当参数1和参数2同为数组，那么randbetween函数就以，参数1和参数2数组中，同一位置上的参数进行计算，当元素个数不一致时，多出的元素位置返回#N/A
3楼 w83365040371	图6. 随机排名的问题,跟生成随机不重复序列的思路和公式是一样的，公式如下 =--RIGHT(SMALL(RANDBETWEEN(1,99+0ROW(1:44))/1%+ROW(1:44),ROW(1:44)),2) 结果见图7. 图7. 使用引用函数index和上面的区域数组公式，可以解决此类为题,选中区域D2:D4,输入公式 =INDEX(A2:A45,RIGHT(SMALL(RANDBETWEEN(1,99+0ROW(1:44))/1%+ROW(1:44),ROW(1:44)),2)) 结果见图8. 图8. 针对这个问题，我们使用下面的函数来判断其是否有重复的，如果有重复就返回"错误", 结果见图13.中的E2 =IF(COUNT(0/(COUNTIF(D2:D45,A2:A45)<>1)),"错误","正确") 图13. 根据上图，我们可以知道，引用函数index能够跟randbetween函数完美的结合，以得到我们想要的结果
4楼 w83365040371	用函数返回图6.中的班级成员时, 我们在图8.中使用的是index函数能得到正确的结果, 那么对于其他的引用函数(offset和indirect)是否能够跟randbetween函数同时使用，正确的返回我们想要的结果呢？在F2:F45中输入区域数组公式 =OFFSET(A$1,--RIGHT(SMALL(RANDBETWEEN(1,99+0ROW(1:44))/1%+ROW(1:44),ROW(1:44)),2),) 结果见图9. 图9. 由上图,我们发现offset的结果返回#VALUE!, 那么意味着offset函数跟randbetween函数结合，不能直接返回正确结果, 那么再offset外面套上T函数是否能够返回正确的结果？ =T(OFFSET(A$1,--RIGHT(SMALL(RANDBETWEEN(1,99+0ROW(1:44))/1%+ROW(1:44),ROW(1:44)),2),)) 结果见图10. 从图10.中可以看到t(offset())返回了结果,但是从绿色的单元格我们看到结果中出现了两个关羽,而且运用公式对其不重复性进行验证,结果见图11. =IF(COUNT(0/(COUNTIF(G2:G45,A2:A45)<>1)),"错误","正确") 图11. 那么根据这个结果我们可以得出结论,t(offset(,randbetween()))的结果并不能随机不重复,具体原因应该是T函数强制使得randbetween函数在区域中重启计算,具体讨论见下面链接 http://club.excelhome.net/thread-775214-1-1.html Indirect: indirect函数引用的结果跟offset函数结果是一样的,这里就不详细叙述,indirect引用结果见图12. 图12. 区域数组公式为 =LOOKUP(MOD(SMALL(RANDBETWEEN(1,99+0ROW(1:44))/1%+ROW(1:44),ROW(1:44)),100),ROW(1:44),A2:A45) 使用下面的公式对其进行判定，结果见图14. =IF(COUNT(0/(COUNTIF(L2:L45,A2:A45)<>1)),"错误","正确") 图14. vlookup的公式跟lookup的公式差不多，结果见图15. =VLOOKUP(MOD(SMALL(RANDBETWEEN(1,99+0ROW(1:44))/1%+ROW(1:44),ROW(1:44)),100),IF({1,0},ROW(1:44),A2:A45),2,) 图15. 根据上面几张图中index,offset,indirect,lookup,vlookup的结果我们可以知道,
5楼滴水穿石	抢沙发，学习~ 图文并茂，易于理解，感谢分享
6楼 eliane_lei	顶一个！感谢分享！
7楼 larkzh	不错，继续关注。
8楼悠悠雨	好棒
9楼 shwgknet	相当棒的教程了。
10楼一轻骑	大大的好