跳到主要內容

如何在Excel中生成沒有重複的隨機數？

在許多情況下，您可能想在Excel中生成隨機數？ 但是，使用將數字隨機化的通用公式，可能會有一些重複的值。 在這裡，我將告訴您一些技巧，以在Excel中生成沒有重複的隨機數。

使用公式生成唯一的隨機數

用Kutools為Excel的插入隨機數據生成唯一的隨機數（容易！） 好主意3

箭頭藍色右氣泡 使用公式生成唯一的隨機數

要在Excel中生成唯一的隨機數，您需要使用兩個公式。

1。 假設您需要生成不重複的隨機數到A列和B列中，現在選擇單元格E1，然後鍵入此公式 = RAND（），然後按 Enter 鍵，請參見屏幕截圖：
文檔隨機化-無重複-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為Excel的插入隨機數據生成唯一的隨機數

使用上述公式，會帶來很多不便。 但是隨著 Excel的Kutools“ 插入隨機數據 功能，您可以根據需要快速輕鬆地插入唯一的隨機數，這將節省大量時間。

時間更少但生產率更高

包括 300 多個 Excel 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中，一次只能將一個複選框/按鈕插入到一個單元格中，如果需要同時插入多個複選框/按鈕，將很麻煩。 Excel的Kutools 具有強大的實用程序- 批量插入檢查 盒 / 批量插入選項按鈕 一鍵即可將復選框/按鈕插入選定的單元格。  點擊即可獲得 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選項卡為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