Cách tạo dãy số ngẫu nhiên không trùng lặp trong Excel

Như bạn đã biết, Microsoft Excel có một số hàm để tạo chuỗi số ngẫu nhiên như RAND, RANDBETWEEN và RANDARRAY. Tuy nhiên, không có gì đảm bảo rằng kết quả của các hàm trên sẽ không bị trùng lặp.

Trong bài viết này sẽ hướng dẫn bạn xây dựng các công thức để tạo ra danh sách các số ngẫu nhiên là duy nhất. Tuy nhiên bạn cần chú ý là có một số hàm chỉ khả dụng tại phiên bản Excel 365 nên nếu bạn sử dụng phiên bản thấp hơn thì sẽ không tìm thấy hàm được áp dụng trong bài viết. Tuy nhiên có giải pháp khác cho những bạn sử dụng phiên bản thấp hơn tại cuối bài viết.

Tạo danh sách các số ngẫu nhiên duy nhất

Công thức tạp danh sách các số ngẫu nhiên duy nhất

Để tạo ra danh sách các số ngẫu nhiên duy nhất là kết hợp 3 hàm mảng động mới: SORTBY, SEQUENCE và RANDARRAY:

SORTBY (SEQUENCE (n), RANDARRAY (n))

Trong đó n là số giá trị ngẫu nhiên bạn muốn nhận.

Ví dụ, để tạo một danh sách gồm 5 số ngẫu nhiên, hãy sử dụng 5 cho n:

=SORTBY (SEQUENCE (5), RANDARRAY (5))

Nhập công thức vào ô trên cùng, nhấn phím Enter và kết quả sẽ tự động trả về tại số ô được chỉ định.

Như bạn có thể thấy trong ảnh chụp màn hình bên dưới, công thức này thực sự sắp xếp các số từ 1 đến 5 theo thứ tự ngẫu nhiên.

Trong công thức trên, bạn chỉ xác định có bao nhiêu hàng để điền. Tất cả các đối số khác được để giá trị mặc định của chúng, có nghĩa là danh sách sẽ bắt đầu từ 1 và bước tiến tăng dần là 1. Nếu bạn muốn một số đầu tiên và số tăng khác, thì hãy đặt giá trị của riêng bạn cho đối số thứ 3 (Start) và đối số thứ 4 (Step) của hàm SEQUENCE.

Ví dụ: để bắt đầu (Start) từ 100 và bước tiến (Step) 10, hãy sử dụng công thức sau:

= SORTBY (SEQUENCE (5, 100, 10), RANDARRAY (5))

Top khóa học tin học văn phòng ứng dụng thực tế cho dân văn phòng

Công thức này hoạt động như thế nào?

Ta sẽ tách nhỏ công thức ra như sau:

• Hàm SEQUENCE tạo một mảng số tuần tự dựa trên giá trị bắt đầu (Start) được chỉ định hoặc mặc định và có đối số tăng dần. Chuỗi này chuyển đến đối số mảng của SORTBY.
• Hàm RANDARRAY tạo ra một mảng các số ngẫu nhiên có cùng kích thước với chuỗi (5 hàng, 1 cột trong trường hợp này). Giá trị tối thiểu và tối đa không thực sự quan trọng, vì vậy chúng ta có thể để các giá trị này thành mặc định. Mảng này chuyển đến đối số by_array của SORTBY.
• Hàm SORTBY sắp xếp các số tuần tự được tạo bởi SEQUENCE bằng cách sử dụng một mảng các số ngẫu nhiên do RANDARRAY tạo ra.

Hãy nhớ rằng công thức đơn giản này tạo ra một danh sách các số ngẫu nhiên không lặp lại với một bước được xác định trước. Để vượt qua giới hạn này, hãy sử dụng phiên bản nâng cao của công thức được mô tả bên dưới.

Cách khác tạo danh sách các số ngẫu nhiên không trùng lặp

Công thức này chỉ hoạt động trong các phiên bản Excel 365 mới nhất hỗ trợ mảng động.

Công thức tạo danh sách số ngẫu nhiên không trùng lặp

Để tạo số ngẫu nhiên trong Excel mà không có số trùng lặp, hãy sử dụng một trong các công thức chung dưới đây.

Tạo danh sách số nguyên ngẫu nhiên

=INDEX (UNIQUE (RANDARRAY (n * 2, 1, min, max, TRUE)), SEQUENCE (n))
 

Tạo danh sách số thập phân ngẫu nhiên

=INDEX (UNIQUE (RANDARRAY (n * 2, 1, min, max, FALSE)), SEQUENCE (n))

Trong đó:

• N là số giá trị cần tạo.
• Min là giá trị nhỏ nhất.
• Max là giá trị lớn nhất.

Ví dụ: để tạo danh sách 5 số ngẫu nhiên là số nguyên từ 1 đến 100 không có số lặp, hãy sử dụng công thức sau:

=INDEX (UNIQUE (RANDARRAY (10, 1, 1, 100, TRUE)), SEQUENCE (5))

Để tạo 5 số ngẫu nhiên là số thập phân duy nhất, hãy đặt FALSE trong đối số cuối cùng của RANDARRAY hoặc bỏ qua đối số này:

=INDEX (UNIQUE(RANDARRAY (10, 1, 1, 100)), SEQUENCE (5))

Công thức này hoạt động như thế nào?

Thoạt nhìn, công thức có thể hơi phức tạp, nhưng khi xem xét kỹ hơn, logic của nó rất đơn giản:

• Hàm RANDARRAY tạo một mảng các số ngẫu nhiên dựa trên các giá trị tối thiểu và tối đa mà bạn chỉ định. Để xác định có bao nhiêu giá trị cần tạo, bạn nhân số mong muốn ít nhất với 2 (vì mảng kết quả có thể có trên một mảng biết bao nhiêu bản sao sẽ được xử lý sau này). Trong ví dụ này, chúng ta chỉ cần 5 số ngẫu nhiên duy nhất nhưng buộc RANDARRAY tạo ra 10, sao cho UNIQUE có đủ giá trị để chọn.
• Hàm UNIQUE loại bỏ tất cả các bản sao và "cấp" một mảng không có bản sao thành INDEX.
• Từ mảng được truyền bởi UNIQUE, hàm INDEX trích xuất n giá trị đầu tiên như được chỉ định bởi SEQUENCE (5 số trong trường hợp của chúng tôi). Bởi vì các giá trị đã có thứ tự ngẫu nhiên, không thực sự quan trọng là giá trị nào tồn tại.

Tạo một dải số ngẫu nhiên không lặp lại trong Excel

Chỉ hoạt động trong các phiên bản Excel 365 mới nhất hỗ trợ mảng động.

Để tạo một dải số ngẫu nhiên không có số lặp lại, hãy sửa đổi công thức trên theo cách này:

=INDEX (UNIQUE (RANDARRAY (rows * 2, columns, min,max)), SEQUENCE (rows), {1,2,…})

Trong đó:

• Rows là số hàng cần điền.
• Columns là số cột cần điền.
• Min là giá trị thấp nhất.
• Max là giá trị cao nhất.
• {1,2,…} là một hằng số mảng bao gồm các số / chỉ số của tất cả các cột được điền các giá trị ngẫu nhiên. Ví dụ, để điền vào 2 cột, hãy sử dụng hằng mảng {1,2}; để điền vào 3 cột, hãy sử dụng {1,2,3}, v.v.

Ví dụ: để điền vào phạm vi 10 hàng và 3 cột với các số ngẫu nhiên duy nhất từ 1 đến 100, hãy sử dụng công thức sau:

=INDEX (DUY NHẤT (RANDARRAY (20, 3, 1, 100)), SEQUENCE (10), {1,2,3})

Và nó sẽ tạo ra một mảng các số ngẫu nhiên là số thập phân, không có số lặp lại:

Nếu bạn cần số nguyên, thì hãy đặt đối số cuối cùng của RANDARRAY thành TRUE:

= INDEX (UNIQUE (RANDARRAY (20, 3, 1, 100, TRUE)), SEQUENCE (10), {1,2,3})

Cách ngăn các số ngẫu nhiên thay đổi

Tất cả các hàm ngẫu nhiên trong Excel bao gồm RAND, RANDBETWEEN và RANDARRAY đều dễ thay đổi, có nghĩa là chúng sẽ tính toán lại mỗi khi bảng tính được thay đổi. Kết quả là, các giá trị ngẫu nhiên mới được tạo ra với mọi thay đổi. Để ngăn tự động tạo số mới, hãy sử dụng tính năng Paste Special > Values feature để thay thế các công thức bằng các giá trị tĩnh. Đây là cách thực hiện:

1.    Chọn tất cả các ô có công thức ngẫu nhiên của bạn và nhấn Ctrl + C để sao chép chúng.

2.    Nhấp chuột phải vào phạm vi đã chọn và nhấp vào Paste Special > Values feature. Ngoài ra, bạn có thể nhấn Shift + F10 rồi nhấn V, đây là phím tắt cho tùy chọn này.

Cách tạo số ngẫu nhiên duy nhất trong phiên bản Excel 2019, 2016 trở về trước

Vì không có phiên bản nào ngoài Excel 365 hỗ trợ mảng động nên không có giải pháp nào ở trên hoạt động trong các phiên bản Excel trước. Tuy nhiên, điều này không có nghĩa là không có giải pháp nào cả, bạn chỉ cần thực hiện thêm một số bước sau:

1.    Tạo một danh sách các số ngẫu nhiên. Dựa trên nhu cầu của bạn, hãy sử dụng

• Hàm RAND để tạo các số thập phân ngẫu nhiên giữa 0 và 1
• Hàm RANDBETWEEN để tạo ra các số nguyên ngẫu nhiên trong phạm vi mà bạn chỉ định.
• Đảm bảo tạo nhiều giá trị hơn bạn thực sự cần vì một số giá trị sẽ bị trùng lặp và bạn sẽ xóa chúng sau đó.
• Đối với ví dụ này, chúng tôi đang tạo một danh sách 10 số nguyên ngẫu nhiên từ 1 đến 20 bằng cách sử dụng công thức dưới đây:

= RANDBETWEEN (1,20)

• Để nhập công thức vào nhiều ô cùng một lúc, hãy chọn tất cả các ô (A2: A15 trong ví dụ của chúng tôi), nhập công thức vào thanh công thức và nhấn Ctrl + Enter. Hoặc bạn có thể nhập công thức vào ô đầu tiên như bình thường, sau đó kéo công thức xuống bao nhiêu ô nếu cần. Dù sao, kết quả sẽ giống như thế này:
   

Như bạn có thể nhận thấy, chúng tôi đã nhập công thức vào 14 ô, mặc dù cuối cùng chúng tôi chỉ cần 10 số ngẫu nhiên duy nhất.

2. Thay đổi công thức thành giá trị. Vì cả RAND và RANDBETWEEN đều tính toán lại với mọi thay đổi trên trang tính, danh sách các số ngẫu nhiên của bạn sẽ liên tục thay đổi. Để ngăn điều này xảy ra, hãy sử dụng Paste Special > Values feature để chuyển đổi công thức sang giá trị.

Để đảm bảo bạn đã làm đúng, hãy chọn bất kỳ số nào và nhìn vào thanh công thức. Bây giờ nó sẽ hiển thị một giá trị, không phải một công thức:

3. Xóa các bản sao. Để hoàn tất, hãy chọn tất cả các số, chuyển đến tab Data> Data tools và nhấp vào Remove Duplicates. Trong hộp thoại Remove Duplicates xuất hiện, bạn chỉ cần nhấp vào OK mà không cần thay đổi gì. 

Làm xong! Tất cả các bản sao đã biến mất và bây giờ bạn có thể xóa các số dư thừa.

Tổng kết

Trong bài viết này, chúng ta đã cùng nhau đi xây dựng công thức để tạo ra danh sách các số ngẫu nhiên là duy nhất bằng cách kết hợp các hàm như SORTBY, RANDBETWEEN, RANDARRAY… Mong rằng bạn có thể kết hợp vào công việc của mình để làm việc hiệu quả hơn. 

Chúc các bạn áp dụng thành công!