跳到主要内容

如何在Excel中生成没有重复的随机数？

在许多情况下，您可能想在Excel中生成随机数？ 但是，使用将数字随机化的通用公式，可能会有一些重复的值。 在这里，我将告诉您一些技巧，以在Excel中生成没有重复的随机数。

使用公式生成唯一的随机数

使用 Kutools for Excel 插入随机数据生成唯一的随机数（简单！） 好主意3

箭头蓝色右气泡 使用公式生成唯一的随机数

要在Excel中生成唯一的随机数，您需要使用两个公式。

1。 假设您需要生成不重复的随机数到A列和B列中，现在选择单元格E1，然后键入此公式 = RAND（），然后按 输入 键，请参见屏幕截图：
文档随机化-无重复-1

2.然后选择整个列E 按Ctrl + 太空 同时键，然后按 按Ctrl + D 应用公式的键 = RAND（） 到整列E。请参见屏幕截图：
文档随机化-无重复-2

3.然后在单元格D1中，键入所需的随机数的最大值。 在这种情况下，我想插入随机数而不在1到50之间重复，因此我将在D50中键入1。
文档随机化-无重复-3

4.现在转到A列，选择单元格A1，键入以下公式 =IF(ROW()-ROW(A$1)+1>$D$1/2,"",RANK(OFFSET($E$1,ROW()-ROW(A$1)+(COLUMN()-COLUMN($A1))*($D$1/2),),$E$1:INDEX($E$1:$E$1000,$D$1)))，然后将填充手柄拖到下一列B，然后将填充手柄向下拖到所需的范围。 看截图：
文档随机化-无重复-4

现在，在此范围内，不再需要您的随机数。

请注意：

1.在上述长公式中，A1表示您使用长公式的单元格，D1表示随机数的最大值，E1是您应用公式= RAND（）的列的第一个单元格，而2则表示您要插入随机数分为两列。 您可以根据需要更改它们。

2.当所有唯一编号都生成到范围中时，冗余单元将显示为空白。

3.使用此方法，您只能从1开始生成随机数。但是，使用第二种方法，您可以轻松指定随机数范围。

箭头蓝色右气泡 使用 Kutools for Excel 插入随机数据生成唯一的随机数

使用上述公式，有太多的不便要处理。 但是随着 Kutools for Excel“ 插入随机数据 功能，您可以根据需要快速轻松地插入唯一的随机数，这将节省大量时间。

时间更少，但生产率更高

包括适用于Excel 300-2019的2003多种专业工具
1.0年发布了第一个版本2011，现在是版本18.0
在几秒钟内解决大多数Excel日常复杂任务，节省您的时间
30 天免费试用，无任何限制

 kte包装盒

安装后 Kutools for Excel，请执行以下操作：（立即免费下载Kutools for Excel！）

1.选择生成随机数所需的范围，然后单击 库工具 > 插页 > 插入随机数据。 看截图：

2.在 插入随机数据 对话框，转到 整数 标签，将所需的数字范围输入 从 和 至 文本框，并记得检查 独特价值 选项。 看截图：

3。 点击 Ok 生成随机数并退出对话框。

请注意：如果您选择的单元格大于随机数，则多余的单元格将显示为空白。

您还可以通过以下方式插入随机唯一日期，随机唯一时间： 插入随机数据。 如果您想免费试用 插入随机数据, 请立即放贷！
插入随机数据

小费。如果您想随机选择或排序数据，请尝试使用 Kutools for Excel 随机排序范围 如以下屏幕截图所示。 它在30天内无限制地提供完整功能， 请下载并立即免费试用。

doc随机选择

箭头蓝色右气泡 插入无重复的随机数据



快速将多个复选框或按钮插入工作表中的单元格区域
在Excel中，一次只能将一个复选框/按钮插入到一个单元格中，如果需要同时插入多个复选框/按钮，将很麻烦。 Kutools for Excel 具有强大的实用程序- 批量插入检查 盒 / 批量插入选项按钮 一键即可将复选框/按钮插入选定的单元格。  点击即可获得 30 天的全功能免费试用！
doc插入复选框选项按钮
 
Kutools for Excel：拥有300多个便捷的Excel加载项，可以在30天内免费试用。

最佳办公生产力工具

🤖 Kutools 人工智能助手：基于以下内容彻底改变数据分析： 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup  |   多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧   |  工作簿和工作表管理器   |  资源库 （自动文本）   |  日期选择器   |  合并工作表   |  加密/解密单元格   |  按列表发送电子邮件   |  超级筛选   |   特殊过滤器 （过滤粗体/斜体/删除线...）...
前 15 个工具集:  12 文本 工具 (添加文本, 删除字符，...）   |   50+ 图表 类型 (甘特图，...）   |   40+ 实用 公式 (根据生日计算年龄，...）   |   19 插入 工具 (插入二维码, 从路径插入图片，...）   |   12 转化 工具 (小写金额转大写, 货币兑换，...）   |   7 合并与拆分 工具 (高级组合行, 分裂细胞，...）   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能，体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

产品描述

Office Tab 为 Office 带来选项卡式界面，让您的工作更加轻松

  • 在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50％，并减少数百次鼠标单击！
Comments (17)
No ratings yet. Be the first to rate!
jorge
jorge
This comment was minimized by the moderator on the site
En la parte Genere números aleatorios únicos con fórmulas como hago para ampliar el numero de columnas pasar de 2 a 5 sin repetir los numeros segun la formula que ud puso: =IF(ROW()-ROW(A$1)+1>$D$1/2,"",RANK(OFFSET($E$1,ROW()-ROW(A$1)+(COLUMN()-COLUMN($A1))*($D$1/2),),$E$1:INDEX($E$1:$E$1000,$D$1)))
Reply
0
0
Janel
Janel
This comment was minimized by the moderator on the site
Hello. I just found this site, and am trying to figure out best way to assign a new code to people. I'd like to assign a random code for people to use between 1000 and 65000, that does not equal any codes already assigned. I'd also like it to be formatted with 5 numbers (00000), but that's not a big deal if it doesn't take care of that. Any ideas on how to make this work? TIA :-)
Reply
0
0
Sunny
Sunny     Janel
This comment was minimized by the moderator on the site
Hi, Janel, why you donot try the Data Validation in Excel? It can only allow whole numbers between 1000 and 65000 to be entered in a cell range.
View Attachments (1 / 5)
Reply
Report
0
0
Jensen
Jensen
This comment was minimized by the moderator on the site
Help anyone :-) This one I cannot figure out. At our school we often put together students in groups - 2 and 2 together for a number of working sessions. 9 in this case. There are 18 students (vary) in the class. What I am chasing is a formula that gives me a random result of who should work together in the 9 working sessions without having students meeting each other twice. So I need Excel to give me a result of 18 students spread across 9 working sessions and any student must not be match with another student twice. How the xxxx do I fix that? I have search all over the place for inspiration without luck. Any input is appreciated :-)
Reply
Report
0
0
Sunny
Sunny     Jensen
This comment was minimized by the moderator on the site
Hi, Anders, you need to list all names in column A, then in column B, apply formula =rand(), then specify 2 as the size in cell F2, now apply this formula =ROUNDUP(RANK(B3,$B$3:$B$19)/$F$2,0) in column C as below screenshot shown
View Attachments (1 / 5)
Reply
Report
0
0
Jensen
Jensen     Sunny
This comment was minimized by the moderator on the site
Hi. Thanks for your reply.
I understand you suggestions, but how do I achieve a setup where all students are assigned to maksimum number of workshops where 2 students are randomly assigned to first one workshop, then the second workshop, then the third workshop etc. etc. and to take this even further a student must not be matched with another student that he/she already have worked with. So when having 18 students I need "the system" to allocate all student to (in this case) 9 workshop (e.g. one workshop per week) and no student should be a student they have already worked with.
Reply
Report
0
0
Elias
Elias     Jensen
This comment was minimized by the moderator on the site
Hi there, did you ever manage to work out a solution to this, I've encountered the same issue. Any help would be appreciated.
Reply
0
0
Guest
Guest     Jensen
This comment was minimized by the moderator on the site
Or you could use a sudoku setup.
Reply
0
0
Michel
Michel
This comment was minimized by the moderator on the site
How can I do the same thing with a custom list? Not numbers?
Reply
0
0
Paulo Lira
Paulo Lira     Michel
This comment was minimized by the moderator on the site
Your custom list is in Column A. Lets say it has 100 values and it is located in A1:A100

Column B is a counter:
1 for B1 and (B1+1) for B2:B100

Column C is a random list:
RAND() for C1:C100

Column D is a random rank based on the random list:
RANK.EQ(C1;$C$1:$C$100)

Column E is your output:
INDEX($A$1:$A$100;MATCH(D1;$B$1:$B$100;0))

Note: There is virtually no chance of generating a duplicate value on column C since RAND() have billions of possibilities. But, if you really want to erase that chance you can type the following formula at column D2:D100
IF(COUNTIFS($C$2:C2;C2)>0;D1+1;RANK.EQ(C2;$C$1:$C$100))
Reply
0
0
Moodley
Moodley
This comment was minimized by the moderator on the site
I need to create 5000 random numbers 1-90 no duplicates 30 columes I and using the formula =RAND() and =IF(ROW()-ROW(A$1)+1>$D$1/2,"",RANK(OFFSET($E$1,ROW()-ROW(A$1)+(COLUMN()-COLUMN($A1))*($D$1/2),),$E$1:INDEX($E$1:$E$1000,$D$1))) is there way that i could change the formula to extract the 5000 numbers
Reply
Report
0
0
Sunny
Sunny     Moodley
This comment was minimized by the moderator on the site
Sorry I have no idea, you can place this problem to our forum https://www.extendoffice.com/forum.html, maybe someone can help you.
Reply
Report
0
0
JIGGLY BALLS
JIGGLY BALLS
This comment was minimized by the moderator on the site
After about three hours of trying to completely understand the long complicated formula to generate random numbers without duplicates, I figured out a far simpler formula that has the same results. After you randomly generate the numbers in a column using the RAND() function, You can simplify the formula:=IF(ROW()-ROW(A$1)+1>$D$1/2,"",RANK(OFFSET($E$1,ROW()-ROW(A$1)+(COLUMN()-COLUMN($A1))*($D$1/2),),$E$1:INDEX($E$1:$E$1000,$D$1))) in cell a1 to =Rank(E1,$E$1:$E$50). Then if you would like 50 random generated "unique numbers." you simply drag the formula through column a to cell a50. It is a lot simpler. Thank you for answering my question regardless of the huge unneeded formula. If you did not have this website. I don't think I would have been able to solve my problem. #WAR Jiggly
Reply
0
0
joe78man@gmail.com
    JIGGLY BALLS
This comment was minimized by the moderator on the site
Actually, if you have two equal values, rank will give the same value to both. Rank formula does return repeated rank values.
Reply
Report
0
0
Paulo Lira
Paulo Lira    
This comment was minimized by the moderator on the site
Manuel Ramirez.

The formula RAND() has hundreds of billions of possibilities, but even so you can use a conditional to not repeat the numbers:
A1 = Rank(E1,$E$1:$E$50)
A2 = IF(COUNTIFS($E$1:E1;E1)>1;A1+1;Rank(E2,$E$1:$E$50)
Reply
0
0
Bernz
Bernz
This comment was minimized by the moderator on the site
Hi, Base on Generate unique random numbers with formulas , How can I specify the starting point of a random number? Let's say I want to random "15 to 30".
Reply
0
0
RK Mohanty
RK Mohanty     Bernz
This comment was minimized by the moderator on the site
Use RANDBETWEEN() to get random numbers lies between two numbers.
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations