Trong công việc văn phòng, bạn sẽ có lúc cần một dãy số ngẫu nhiên mà không số nào lặp lại. Phòng marketing cần bốc thăm khách hàng trúng thưởng, phòng nhân sự cần chia nhân viên mới thành các nhóm đào tạo, còn kế toán cần chọn ngẫu nhiên vài hóa đơn trong tháng để kiểm tra chứng từ.

Cách nhiều người nghĩ tới đầu tiên là hàm RANDBETWEEN. Hàm này cho ra số ngẫu nhiên trong đúng khoảng bạn muốn, nhưng hàm không có cơ chế chống trùng. Kết quả là danh sách bốc thăm có thể có một người trúng hai lần, và bạn phải ngồi sửa tay.

Bài viết này hướng dẫn 4 cách tạo dãy số ngẫu nhiên không trùng lặp trong Excel: 2 cách dùng công thức mảng động trên Excel 365, 1 cách dùng cột phụ cho Excel đời cũ và 1 cách lọc trùng kèm các hạn chế cần biết. Sau đó, bài hướng dẫn cách cố định kết quả để số không tự đổi, và 4 ứng dụng thực tế: bốc thăm trúng thưởng, chia nhóm ngẫu nhiên, xáo trộn câu hỏi trắc nghiệm, chọn mẫu kiểm tra.

Mọi công thức trong bài đã được chạy thử trên Excel 365, mỗi công thức được tính lại 30 tới 50 lần để kiểm tra đủ số lượng, đúng khoảng và không có số trùng. Vì đây là số ngẫu nhiên, các con số trong ảnh chỉ là kết quả của một lần tính cụ thể. File của bạn sẽ ra các số khác, và mỗi lần tính lại kết quả cũng thay đổi.

Vì sao RANDBETWEEN tạo ra số bị trùng?

Hàm =RANDBETWEEN(1,50) trả về một số nguyên ngẫu nhiên từ 1 tới 50. Khi bạn kéo công thức xuống 10 ô, mỗi ô rút số một cách độc lập. Ô thứ hai không biết ô thứ nhất đã ra số nào, nên hai ô hoàn toàn có thể ra cùng một số.

Hàm RANDBETWEEN rút 10 số trong khoảng 1 đến 50 cho ra số 12 hai lần
10 ô RANDBETWEEN độc lập vẫn có thể ra cùng một số

Trong lượt kiểm tra cho bài viết này, 10 ô RANDBETWEEN(1,50) được tính lại 50 lần, và có 28 lần xuất hiện ít nhất một cặp số trùng. Tính theo xác suất, khả năng có số trùng khi rút 10 số trong 50 số vào khoảng 62%. Nếu bạn rút đủ 50 số trong khoảng 1 tới 50, gần như chắc chắn danh sách sẽ có số trùng.

Vì vậy, 3 cách đầu trong bài đều đi theo một nguyên tắc: bạn tạo sẵn một danh sách không trùng, sau đó xáo trộn thứ tự của danh sách đó. Phép xáo trộn chỉ đổi vị trí của các số, không tạo ra số mới, nên kết quả không bao giờ có số lặp lại.

Bảng tóm tắt 4 cách tạo dãy số ngẫu nhiên không trùng lặp

Cách

Công thức chính

Phiên bản Excel

Dùng khi

1. Xáo trộn dãy 1 đến n

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

Excel 365, Excel 2021, 2024

Bạn cần toàn bộ các số từ 1 tới n theo thứ tự ngẫu nhiên

2. Lấy k số trong một khoảng

=TAKE(SORTBY(SEQUENCE(n),RANDARRAY(n)),k)

Excel 365, Excel 2024 (Excel 2021 dùng INDEX)

Bạn cần k số không trùng, ví dụ 10 số trong 1 tới 50

3. Cột phụ RAND và RANK

=RANK(B2,$B$2:$B$51)+COUNTIF($B$2:B2,B2)-1

Mọi phiên bản

Máy của bạn hoặc người nhận file dùng Excel 2010, 2013, 2016, 2019

4. Rút nhiều số rồi lọc trùng

=UNIQUE(RANDARRAY(9999,1,1,50,TRUE))

Excel 365, Excel 2021, 2024

Bạn cần biết cách này để hiểu hạn chế, nên ưu tiên cách 1 và 2

Cách 1: SORTBY kết hợp SEQUENCE và RANDARRAY xáo trộn dãy 1 đến n

Đây là cách gọn nhất trên Excel 365. Bạn chỉ gõ một công thức ở một ô, kết quả tự tràn xuống các ô bên dưới.

Cú pháp: =SORTBY(SEQUENCE(n),RANDARRAY(n))

Ví dụ, công thức =SORTBY(SEQUENCE(10),RANDARRAY(10)) trả về 10 số từ 1 tới 10 theo thứ tự ngẫu nhiên. Công thức gồm 3 hàm lồng vào nhau:

Hàm

Cú pháp

Vai trò trong công thức

SEQUENCE

=SEQUENCE(số dòng, [số cột], [bắt đầu], [bước nhảy])

SEQUENCE(10) tạo dãy 1, 2, 3, ..., 10 theo cột dọc. Đây là danh sách không trùng ban đầu.

RANDARRAY

=RANDARRAY([số dòng], [số cột], [min], [max], [số nguyên])

RANDARRAY(10) tạo 10 số thập phân ngẫu nhiên từ 0 tới 1. Dãy này đóng vai trò "khóa xáo trộn".

SORTBY

=SORTBY(mảng, mảng làm khóa, [thứ tự])

SORTBY sắp xếp dãy 1 đến 10 theo dãy số ngẫu nhiên. Thứ tự của khóa ngẫu nhiên nên thứ tự kết quả cũng ngẫu nhiên.

Công thức SORTBY kết hợp SEQUENCE và RANDARRAY xáo trộn dãy số 1 đến 10 trong Excel
SORTBY xáo trộn dãy 1 đến 10, mỗi số xuất hiện đúng một lần

Bạn có thể hình dung SORTBY gắn cho mỗi số một "vé bốc thăm" ngẫu nhiên rồi xếp các số theo vé. SORTBY chỉ đổi vị trí của 10 số có sẵn, nên kết quả luôn đủ 10 số và không có số nào lặp lại. Trong lượt kiểm tra, công thức với n = 10 và n = 50 đều được tính lại 50 lần, lần nào cũng ra đủ các số từ 1 tới n.

Lưu ý khi dùng cách 1:

  • Số n trong SEQUENCE và RANDARRAY phải bằng nhau. Công thức =SORTBY(SEQUENCE(10),RANDARRAY(9)) báo lỗi #VALUE! vì hai mảng lệch kích thước.

  • Vùng tràn phải trống. Nếu bên dưới ô công thức có dữ liệu, Excel báo lỗi #SPILL!. Bạn xóa dữ liệu chặn đường hoặc chuyển công thức sang chỗ trống.

  • Phiên bản cần có: SEQUENCE, RANDARRAY và SORTBY có trong Excel 365, Excel 2021, Excel 2024 và Excel trên web. Excel 2019 trở về trước không có các hàm này, nên công thức sẽ báo lỗi #NAME?.

Cách 2: TAKE lấy k số không trùng trong một khoảng bất kỳ

Cách 1 cho ra toàn bộ dãy 1 đến n. Trong thực tế, bạn thường chỉ cần một phần, ví dụ 10 số trong khoảng 1 tới 50. Khi đó, bạn xáo trộn cả dãy rồi lấy k số đầu tiên bằng hàm TAKE.

Cú pháp: =TAKE(SORTBY(SEQUENCE(n),RANDARRAY(n)),k)

  • SORTBY(SEQUENCE(n),RANDARRAY(n)) là dãy 1 đến n đã xáo trộn, giống cách 1.

  • TAKE(mảng, số dòng) lấy số dòng đầu tiên của mảng. Vì cả dãy đã không trùng, k số đầu tiên cũng không trùng.

Khi khoảng không bắt đầu từ 1, bạn dùng tham số bắt đầu của SEQUENCE. Khoảng từ a tới b có tất cả b - a + 1 số, nên công thức tổng quát là:

=TAKE(SORTBY(SEQUENCE(b-a+1,1,a),RANDARRAY(b-a+1)),k)

Ví dụ, khoảng 100 tới 1000 có 901 số, công thức lấy 8 số là =TAKE(SORTBY(SEQUENCE(901,1,100),RANDARRAY(901)),8). Nếu muốn kết quả xếp tăng dần cho dễ đọc, bạn bọc thêm hàm SORT bên ngoài, ví dụ =SORT(TAKE(SORTBY(SEQUENCE(45),RANDARRAY(45)),6)) lấy 6 số trong 1 tới 45 rồi xếp từ nhỏ tới lớn.

Hàm TAKE lấy 10 số trong 1 đến 50, 6 số trong 1 đến 45 và 8 số trong 100 đến 1000 không trùng lặp
TAKE lấy k số đầu tiên của dãy đã xáo trộn

Nếu bạn hay đổi khoảng số, hàm LET giúp công thức dễ sửa hơn. Bạn chỉ cần đổi 3 con số a, b, k ở đầu công thức:

=LET(a,100,b,1000,k,8,n,b-a+1,TAKE(SORTBY(SEQUENCE(n,1,a),RANDARRAY(n)),k))

Trong lượt kiểm tra, các công thức ở cách 2 đều được tính lại 30 tới 50 lần. Lần nào kết quả cũng đủ k số, nằm trong khoảng a tới b và không có số trùng.

Lưu ý khi dùng cách 2:

  • Excel 2021 chưa có TAKE. Bạn thay TAKE bằng INDEX kết hợp SEQUENCE: =INDEX(SORTBY(SEQUENCE(50),RANDARRAY(50)),SEQUENCE(10)). Công thức này cho kết quả tương đương.

  • k lớn hơn n cho kết quả khác nhau giữa hai hàm. TAKE chỉ trả về đủ n số và không báo lỗi. INDEX báo lỗi #REF! ở các dòng vượt quá n.

  • TAKE có trong Excel 365, Excel 2024 và Excel trên web. Nếu file sẽ gửi cho người dùng Excel 2021, bạn nên dùng phiên bản INDEX.

Cách 3: Cột phụ RAND kết hợp RANK cho mọi phiên bản Excel

Nếu máy của bạn hoặc máy của người nhận file dùng Excel 2010, 2013, 2016 hay 2019, các hàm mảng động ở trên sẽ báo lỗi #NAME?. Khi đó, bạn dùng một cột phụ chứa số ngẫu nhiên rồi xếp hạng cột đó. Ví dụ sau tạo dãy 1 đến 50 theo thứ tự ngẫu nhiên ở vùng C2:C51.

  1. Bạn nhập =RAND() vào ô B2 rồi kéo xuống tới B51. Mỗi ô có một số thập phân ngẫu nhiên từ 0 tới 1.

  2. Bạn nhập công thức sau vào ô C2 rồi kéo xuống tới C51: =RANK(B2,$B$2:$B$51)+COUNTIF($B$2:B2,B2)-1

  3. Cột C bây giờ chứa đủ các số từ 1 tới 50 theo thứ tự ngẫu nhiên. Nếu chỉ cần 10 số, bạn lấy 10 dòng đầu C2:C11.

Cột phụ RAND và hàm RANK tạo dãy số ngẫu nhiên không trùng lặp trên Excel đời cũ
Cột phụ RAND kết hợp RANK chạy được trên mọi phiên bản Excel

Công thức ở cột C gồm 2 phần:

  • RANK(B2,$B$2:$B$51) cho biết số ở ô B2 đứng thứ mấy trong 50 số, xếp từ lớn tới nhỏ. Cú pháp đầy đủ là =RANK(số, vùng, [thứ tự]), bỏ trống thứ tự nghĩa là số lớn nhất được hạng 1. Vì 50 số RAND khác nhau, 50 thứ hạng cũng khác nhau và chạy từ 1 tới 50.

  • COUNTIF($B$2:B2,B2)-1 là phần chống trùng. Vùng $B$2:B2 có điểm đầu khóa cố định và điểm cuối thay đổi, nên khi kéo xuống, vùng này mở rộng dần. Phần này bằng 0 khi một số xuất hiện lần đầu, bằng 1 khi số đó xuất hiện lần thứ hai, và cứ thế tăng dần.

Với RAND, khả năng hai ô ra đúng cùng một số thập phân là cực nhỏ, nhưng phần COUNTIF vẫn nên giữ lại để công thức an toàn trong mọi trường hợp. Trong lượt kiểm tra 50 lần tính lại, cột C lần nào cũng ra đủ 50 số từ 1 tới 50.

Khi cần khoảng khác 1 đến n, bạn cộng thêm phần bù. Ví dụ, công thức =RANK(B2,$B$2:$B$51)+COUNTIF($B$2:B2,B2)-1+99 cho ra các số không trùng trong khoảng 100 tới 149.

Dùng RANDBETWEEN ở cột phụ thì cần COUNTIF

Một số hướng dẫn dùng RANDBETWEEN thay cho RAND ở cột phụ. Cách này vẫn đúng, nhưng lúc này cột phụ chắc chắn có số trùng, nên phần COUNTIF trở thành bắt buộc. Thiếu COUNTIF, hai số bằng nhau sẽ nhận cùng một thứ hạng.

So sánh RANK.EQ và RANK.EQ kết hợp COUNTIF khi cột RANDBETWEEN có hai số bằng nhau
COUNTIF tách hạng cho hai số bằng nhau, nhờ vậy không còn hạng trùng

Trong lượt kiểm tra, cột RANDBETWEEN(1,50) được tính lại 50 lần. Nếu chỉ dùng =RANK.EQ(D2,$D$2:$D$51), cả 50 lần đều có thứ hạng trùng. Khi cộng thêm COUNTIF($D$2:D2,D2)-1, cả 50 lần đều ra đủ 50 số từ 1 tới 50.

Lưu ý khi dùng cách 3:

  • Vùng trong RANK phải phủ đủ mọi dòng dữ liệu. Dữ liệu nằm ở D2:D51 thì vùng phải là $D$2:$D$51. Nếu bạn viết thiếu một dòng thành $D$2:$D$50, dòng 51 nằm ngoài vùng xếp hạng. Trong 50 lần thử với vùng thiếu này, không lần nào ra đủ dãy 1 tới 50: có lần báo lỗi #N/A, có lần sinh số trùng, và số 50 không bao giờ xuất hiện.

  • Vùng phải khóa bằng dấu $. Nếu bạn quên khóa, vùng B2:B51 sẽ trượt xuống khi kéo công thức và thứ hạng sẽ sai.

  • RANK chạy trên mọi phiên bản Excel. RANK.EQ có từ Excel 2010 và cho kết quả giống RANK trong trường hợp này.

Cách 4: UNIQUE kết hợp RANDARRAY rút nhiều số rồi lọc trùng

Cách này đi theo hướng ngược lại: bạn rút thật nhiều số ngẫu nhiên có thể trùng, rồi dùng hàm UNIQUE giữ lại mỗi số một lần.

Cú pháp: =UNIQUE(RANDARRAY(số lần rút,1,min,max,TRUE))

  • RANDARRAY(9999,1,1,50,TRUE) rút 9999 số nguyên từ 1 tới 50. Tham số TRUE yêu cầu RANDARRAY trả về số nguyên.

  • UNIQUE(...) giữ lại mỗi số một lần theo thứ tự xuất hiện đầu tiên. Thứ tự xuất hiện là ngẫu nhiên, nên dãy kết quả cũng có thứ tự ngẫu nhiên.

Muốn lấy 10 số trong 1 tới 50, bạn kết hợp với TAKE: =TAKE(UNIQUE(RANDARRAY(200,1,1,50,TRUE)),10).

Trong lượt kiểm tra 50 lần, công thức rút 9999 số luôn cho đủ 50 số, và công thức rút 200 số lấy 10 luôn cho đủ 10 số không trùng. Tuy vậy, cách 4 có 3 hạn chế bạn cần biết:

  1. Cách này không đảm bảo tuyệt đối về mặt lý thuyết. Số lượng kết quả phụ thuộc vào may rủi, chỉ là khi số lần rút đủ lớn thì khả năng thiếu số rất nhỏ.

  2. Bạn phải tự chọn số lần rút đủ lớn. Khi thử rút 60 số trong khoảng 1 tới 50, công thức =ROWS(UNIQUE(RANDARRAY(60,1,1,50,TRUE))) chỉ cho ra từ 30 tới 39 số khác nhau qua 50 lần tính, không lần nào đủ 50.

  3. Công thức tạo hàng nghìn số chỉ để lấy vài chục số, nên file nặng hơn.

Vì 3 lý do trên, bạn nên ưu tiên cách 1 và cách 2 khi dùng Excel 365. Cách 4 phù hợp để bạn hiểu vì sao hướng "xáo trộn danh sách có sẵn" chắc chắn hơn hướng "rút rồi lọc trùng".

Khi nào bạn nên dùng cách nào?

Tình huống

Cách nên dùng

Bạn dùng Excel 365 và cần cả dãy 1 tới n theo thứ tự ngẫu nhiên

Cách 1: SORTBY + SEQUENCE + RANDARRAY

Bạn dùng Excel 365 hoặc 2024 và cần k số trong khoảng a tới b

Cách 2: TAKE, có thể bọc thêm LET cho dễ sửa

Bạn dùng Excel 2021

Cách 1, và cách 2 thay TAKE bằng INDEX

Bạn hoặc người nhận file dùng Excel 2019 trở về trước

Cách 3: cột phụ RAND + RANK + COUNTIF

Bạn đang dùng RANDBETWEEN và bị trùng

Bạn chuyển sang cách 1 hoặc cách 3, không cần sửa tay từng số

Cách cố định dãy số ngẫu nhiên để kết quả không tự đổi

RAND, RANDBETWEEN và RANDARRAY là các hàm tự tính lại. Mỗi khi bạn sửa một ô bất kỳ, nhấn F9 hay mở lại file, Excel lại rút số mới. Điều này tiện khi bạn muốn bốc lại, nhưng gây rắc rối khi đã công bố kết quả. Bạn có 2 cách giữ kết quả cố định.

Bạn dán giá trị bằng Paste Values

  1. Bạn chọn vùng kết quả. Với công thức mảng động, bạn chọn cả vùng tràn, ví dụ từ C2 tới C11.

  2. Bạn nhấn Ctrl + C để sao chép.

  3. Bạn nhấn Ctrl + Alt + V để mở hộp Paste Special, chọn Values rồi nhấn Enter. Bạn cũng có thể vào Home, bấm mũi tên dưới nút Paste và chọn biểu tượng Values (123).

Sau bước này, các ô chỉ còn con số, không còn công thức, nên kết quả đứng yên vĩnh viễn. Cách làm hay dùng là giữ một sheet chứa công thức để bốc, rồi dán giá trị sang sheet "Kết quả" kèm ngày bốc thăm. Bạn xem thêm các tổ hợp phím tiết kiệm thời gian khác trong bài Phím tắt Excel thông dụng.

Bạn chuyển chế độ tính sang thủ công

Bạn vào Formulas, chọn Calculation Options, chọn Manual. Từ lúc này, Excel chỉ tính lại khi bạn nhấn F9. Cách này hợp khi bạn muốn tự quyết định thời điểm bốc, ví dụ bấm F9 trong buổi quay số.

Lưu ý: chế độ Manual áp dụng cho cả ứng dụng Excel, nên các file khác đang mở cũng ngừng tự tính. Bạn nhớ chuyển lại Automatic sau khi dùng xong, nếu không các bảng tính khác sẽ hiện số cũ mà bạn không để ý.

4 ứng dụng thực tế của dãy số ngẫu nhiên không trùng lặp

1. Bốc thăm khách hàng trúng thưởng

Bạn có danh sách 20 khách hàng ở vùng A2:A21 và cần chọn ngẫu nhiên 3 người trúng giải. Trên Excel 365, bạn chỉ cần một công thức:

=TAKE(SORTBY(A2:A21,RANDARRAY(ROWS(A2:A21))),3)

Công thức này xáo trộn trực tiếp danh sách tên thay vì dãy số. Hàm ROWS(A2:A21) đếm số dòng của danh sách, nên khi danh sách dài thêm, bạn chỉ cần sửa vùng mà không phải đếm lại.

Trên Excel đời cũ, bạn dùng cột phụ như cách 3 rồi lấy tên theo thứ tự bằng INDEX và MATCH:

  • Ô B2 tới B21 chứa =RAND().

  • Ô C2 tới C21 chứa =RANK(B2,$B$2:$B$21)+COUNTIF($B$2:B2,B2)-1, tức thứ tự bốc thăm của từng người.

  • Ô E2 chứa =INDEX($A$2:$A$21,MATCH(ROWS($E$2:E2),$C$2:$C$21,0)), kéo xuống E4. ROWS($E$2:E2) lần lượt bằng 1, 2, 3, nên E2 là người có thứ tự 1, E3 là người có thứ tự 2, E4 là người có thứ tự 3.

Bốc thăm ngẫu nhiên 3 khách hàng trúng thưởng trong danh sách 20 người bằng RAND, RANK và INDEX MATCH
Bốc thăm 3 người trúng thưởng, ai có thứ tự 1, 2, 3 là người trúng

Trong 50 lần tính lại, cả công thức Excel 365 và cách cột phụ đều cho đúng 3 người khác nhau, đều thuộc danh sách. Sau khi có người trúng, bạn có thể dùng XLOOKUP để lấy số điện thoại, địa chỉ của từng người từ bảng khách hàng, xem cách làm trong bài Hàm XLOOKUP trong Excel.

2. Chia nhân viên thành các nhóm ngẫu nhiên

Bạn có 12 nhân viên ở vùng A2:A13 và cần chia thành 3 nhóm, mỗi nhóm 4 người. Trên Excel 365, bạn dùng:

=WRAPCOLS(SORTBY(A2:A13,RANDARRAY(ROWS(A2:A13))),4)

SORTBY xáo trộn danh sách 12 người, sau đó hàm WRAPCOLS(mảng, số phần tử mỗi cột, [giá trị bù]) cắt danh sách thành từng cột 4 người. Mỗi cột là một nhóm. Nếu bạn muốn mỗi nhóm nằm trên một hàng ngang, bạn đổi WRAPCOLS thành WRAPROWS.

Chia ngẫu nhiên 12 nhân viên thành 3 nhóm 4 người bằng WRAPCOLS và SORTBY
WRAPCOLS cắt danh sách đã xáo trộn thành các nhóm 4 người

Khi số người không chia hết cho số người mỗi nhóm, nhóm cuối sẽ thiếu người và các ô thiếu hiện lỗi #N/A. Bạn thêm đối số thứ ba là chuỗi rỗng để các ô đó hiện trống: =WRAPCOLS(SORTBY(H2:H11,RANDARRAY(ROWS(H2:H11))),4,""). Trong lượt thử với 10 người chia nhóm 4, nhóm cuối có 2 người và 2 ô trống thay vì 2 lỗi #N/A. WRAPCOLS và WRAPROWS có trong Excel 365 và Excel 2024.

Trên Excel đời cũ, bạn tạo cột thứ tự bằng RAND và RANK như cách 3, rồi thêm một cột tính số nhóm: =ROUNDUP(C2/4,0). Người có thứ tự 1 tới 4 vào nhóm 1, thứ tự 5 tới 8 vào nhóm 2, cứ thế tiếp tục. Trong 30 lần thử với 12 người, mỗi nhóm luôn có đúng 4 người.

3. Xáo trộn thứ tự câu hỏi trắc nghiệm

Bạn có ngân hàng 10 câu hỏi, cột A là mã câu, cột B là nội dung. Bạn muốn mỗi đề có thứ tự câu khác nhau để hạn chế nhìn bài. Công thức sau xáo trộn cả bảng 2 cột:

=SORTBY(A2:B11,RANDARRAY(ROWS(A2:A11)))

Điểm quan trọng là SORTBY xếp lại cả dòng theo cùng một khóa ngẫu nhiên, nên mã câu và nội dung luôn đi cùng nhau, không bị lệch dòng. Bạn có thể thêm cột đáp án A, B, C, D vào bảng, chỉ cần mở rộng vùng thành A2:F11.

Xáo trộn thứ tự 10 câu hỏi trắc nghiệm bằng SORTBY, mã câu và nội dung giữ nguyên trên cùng dòng
SORTBY xáo trộn cả bảng câu hỏi, mỗi dòng vẫn giữ nguyên mã câu và nội dung

Trong 50 lần kiểm tra, lần nào kết quả cũng đủ 10 câu, không câu nào lặp lại và mọi mã câu đều đứng cạnh đúng nội dung của mình. Để tạo nhiều đề, bạn nhấn F9 một lần cho mỗi đề, rồi dán giá trị sang sheet "Đề A", "Đề B" và tiếp tục như vậy.

4. Chọn ngẫu nhiên mẫu hóa đơn để kiểm tra

Kế toán hoặc kiểm soát nội bộ thường chọn ngẫu nhiên một số chứng từ để kiểm tra thay vì soát toàn bộ. Giả sử cột A chứa 40 mã hóa đơn từ HD001 tới HD040 ở vùng A2:A41, bạn cần chọn 5 hóa đơn:

=SORT(TAKE(SORTBY(A2:A41,RANDARRAY(ROWS(A2:A41))),5))

Hàm SORT bên ngoài xếp 5 mã được chọn theo thứ tự tăng dần, giúp bạn tìm chứng từ trong tủ hồ sơ nhanh hơn. Một lần tính trong lượt kiểm tra cho ra 5 mã HD019, HD023, HD026, HD033, HD040. Kết quả này chỉ là ví dụ, file của bạn sẽ ra các mã khác. Bạn nên dán giá trị danh sách mẫu và ghi lại ngày chọn để làm hồ sơ kiểm tra.

Những lỗi thường gặp khi tạo dãy số ngẫu nhiên không trùng

Bạn thấy

Nguyên nhân

Cách sửa

Danh sách có số trùng

Bạn dùng nhiều ô RANDBETWEEN độc lập

Chuyển sang cách 1 (SORTBY) hoặc cách 3 (RAND + RANK)

Lỗi #NAME?

Phiên bản Excel không có hàm SEQUENCE, RANDARRAY, SORTBY, TAKE hoặc WRAPCOLS

Dùng cách 3 cho Excel đời cũ, hoặc thay TAKE bằng INDEX trên Excel 2021

Lỗi #SPILL!

Vùng kết quả tràn ra bị dữ liệu khác chặn

Xóa dữ liệu bên dưới hoặc bên phải ô công thức

Lỗi #VALUE! ở SORTBY

Mảng cần xếp và mảng khóa ngẫu nhiên có số dòng khác nhau

Dùng ROWS(vùng) cho RANDARRAY để hai mảng luôn bằng nhau

Lỗi #REF! ở INDEX

Bạn lấy số lượng k lớn hơn số phần tử n

Giảm k, hoặc dùng TAKE trên Excel 365

Cột thứ hạng có số trùng

Công thức RANK thiếu phần COUNTIF chống trùng

Cộng thêm COUNTIF($B$2:B2,B2)-1

Cột thứ hạng có lỗi #N/A hoặc thiếu số lớn nhất

Vùng trong RANK không phủ đủ các dòng dữ liệu hoặc chưa khóa $

Sửa vùng cho khớp với dữ liệu, ví dụ $B$2:$B$51 cho 50 dòng

Nhóm cuối hiện #N/A

Số người không chia hết cho số người mỗi nhóm

Thêm đối số "" vào WRAPCOLS hoặc WRAPROWS

Kết quả tự đổi sau khi đã công bố

Các hàm ngẫu nhiên tự tính lại mỗi khi file thay đổi

Dán giá trị bằng Ctrl + Alt + V, chọn Values

Câu hỏi thường gặp về dãy số ngẫu nhiên không trùng lặp trong Excel

Hàm RAND và RANDBETWEEN khác nhau thế nào?

RAND trả về số thập phân ngẫu nhiên từ 0 tới nhỏ hơn 1 và không cần đối số. RANDBETWEEN trả về số nguyên ngẫu nhiên trong khoảng bạn chỉ định, ví dụ =RANDBETWEEN(1,50). Cả hai hàm đều không chống trùng khi dùng ở nhiều ô, nên bạn cần kết hợp với RANK và COUNTIF như cách 3.

Làm sao để dãy số ngẫu nhiên không đổi mỗi lần mở file?

Bạn sao chép vùng kết quả, nhấn Ctrl + Alt + V, chọn Values để thay công thức bằng giá trị. Nếu cần giữ công thức để bốc lại sau, bạn chuyển chế độ tính sang Manual trong Formulas, Calculation Options.

Excel 2016 có tạo được dãy số ngẫu nhiên không trùng không?

Có. Bạn dùng cách 3: một cột =RAND() và một cột =RANK(B2,$B$2:$B$51)+COUNTIF($B$2:B2,B2)-1. Các hàm này có trên mọi phiên bản Excel.

Danh sách có tên bị lặp thì bốc thăm thế nào để một người không trúng hai lần?

Bạn lọc danh sách không trùng trước rồi mới xáo trộn. Trên Excel 365, bạn dùng =LET(ds,UNIQUE(A2:A201),TAKE(SORTBY(ds,RANDARRAY(ROWS(ds))),3)). Hàm UNIQUE giữ mỗi tên một lần, sau đó công thức chọn 3 tên khác nhau.

Có tạo được dãy số ngẫu nhiên không trùng nằm theo hàng ngang không?

Có. Bạn đổi tham số số dòng thành 1 và đặt số phần tử vào tham số số cột: =SORTBY(SEQUENCE(1,10),RANDARRAY(1,10)). Kết quả là 10 số từ 1 tới 10 nằm trên một hàng.

Bạn muốn dùng Excel thành thạo hơn cho công việc?

Các công thức trong bài kết hợp nhiều nhóm hàm: hàm mảng động (SEQUENCE, SORTBY, TAKE), hàm thống kê (RANK, COUNTIF) và hàm tra cứu (INDEX, MATCH). Khi nắm chắc từng nhóm, bạn sẽ tự ghép được công thức cho bài toán của mình. Bạn có thể xem thứ tự học các nhóm hàm trong bài Lộ trình học Excel từ cơ bản đến nâng cao.

Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.