Hàm Random Không Lặp Lại Trong Excel: Cách Tạo Dữ Liệu Ngẫu Nhiên Độc Đáo, Ứng Dụng Thực Tiễn Và Mẹo Tối Ưu

Có thể bạn quan tâm: Cách Đếm Giá Trị Độc Nhất (distinct) Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Mở Đầu

Có thể bạn quan tâm: Cách Tính Số Ngày Giữa Hai Ngày Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Trong thế giới dữ liệu hiện đại, việc tạo ra các giá trị ngẫu nhiên là một nhu cầu phổ biến trong nhiều lĩnh vực: từ thống kê, phân tích dữ liệu, kiểm thử phần mềm, cho tới việc thiết kế các trò chơi, mô phỏng và thậm chí là trong giáo dục. Microsoft Excel, với tính linh hoạt và phổ biến của mình, đã cung cấp một số hàm tạo số ngẫu nhiên như RAND() và RANDBETWEEN(). Tuy nhiên, những hàm này có một hạn chế lớn: chúng có thể sinh ra các giá trị trùng lặp, điều này không phù hợp khi chúng ta cần một danh sách các giá trị duy nhất (non‑duplicate) – ví dụ như danh sách số thứ tự ngẫu nhiên, mã số khách hàng, hoặc bộ câu hỏi trắc nghiệm ngẫu nhiên.

Có thể bạn quan tâm: Cách Đếm Ô Trống Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Bài viết này sẽ đi sâu vào cách tạo hàm random không lặp lại trong Excel, bao gồm:

1. Giải thích nguyên lý và tại sao các hàm ngẫu nhiên mặc định lại có khả năng lặp.
2. Các phương pháp tạo danh sách không trùng lặp bằng công thức (sử dụng hàm SORTBY, UNIQUE, SEQUENCE, RANDARRAY, INDEX, MATCH, COUNTIF, RANK.EQ, …).
3. Sử dụng VBA để tạo random không lặp lại, bao gồm macro đơn giản và các hàm tùy chỉnh.
4. Ứng dụng thực tiễn: tạo danh sách thí sinh ngẫu nhiên, phân phối công việc, tạo bộ câu hỏi trắc nghiệm, mô phỏng quay số, v.v.
5. Mẹo tối ưu: giảm thiểu tính toán lại, tránh lỗi “circular reference”, bảo vệ dữ liệu, và cách làm việc với dữ liệu lớn.
6. So sánh các phương pháp: ưu nhược điểm, thời gian thực thi, độ phức tạp, tính mở rộng.
7. Kết luận và một số đề xuất để bạn tự tin áp dụng trong công việc hàng ngày.

1. Tại Sao Hàm Random Mặc Định Có Thể Lặp?

• RAND() trả về một số thực ngẫu nhiên trong khoảng 0,1). Khi sao chép công thức xuống nhiều ô, Excel tính lại mỗi lần mở file hoặc thay đổi bất kỳ ô nào, nên khả năng hai ô nhận cùng một giá trị là rất cao (có khoảng 2^53 giá trị có thể, nhưng trong thực tế, khi làm việc với hàng nghìn ô, khả năng trùng lặp vẫn đáng kể).
• RANDBETWEEN(bottom, top) trả về một số nguyên trong khoảng chỉ định. Nếu khoảng giá trị nhỏ so với số ô cần, việc trùng lặp gần như chắc chắn xảy ra.
• RANDARRAY(rows, cols, min, max, whole_number) (Excel 365) tạo mảng ngẫu nhiên, nhưng cũng không đảm bảo tính duy nhất.

Vì vậy, để đảm bảo không có giá trị nào lặp lại, chúng ta phải thực hiện một bước “loại bỏ trùng lặp” hoặc “sắp xếp lại” sau khi tạo mảng ngẫu nhiên.

2. Phương Pháp 1: Sử Dụng Công Thức Mảng Kết Hợp SORTBY và SEQUENCE

2.1. Nguyên Lý

• SEQUENCE(n) tạo một mảng số nguyên liên tiếp từ 1 đến n.
• RANDARRAY(n,1) tạo mảng ngẫu nhiên có n hàng, 1 cột.
• SORTBY(array, sort_index) sắp xếp array dựa trên sort_index. Khi sort_index là một mảng ngẫu nhiên, kết quả là đảo ngẫu nhiên các phần tử của array mà không có trùng lặp.

2.2. Công Thức Cụ Thể

Giả sử chúng ta muốn tạo danh sách 1‑100 ngẫu nhiên không lặp lại, đặt công thức vào ô A1:

=SORTBY(SEQUENCE(100,1,1,1), RANDARRAY(100,1))

• SEQUENCE(100,1,1,1) tạo mảng {1;2;3;…;100}.
• RANDARRAY(100,1) tạo 100 giá trị ngẫu nhiên.
• SORTBY sắp xếp dãy 1‑100 dựa trên thứ tự ngẫu nhiên, cho ra một hoán vị ngẫu nhiên.

2.3. Ưu Điểm & Nhược Điểm

2.4. Ứng Dụng Thực Tiễn

• Rút thăm ngẫu nhiên: Chọn 10 người từ 200 nhân viên.
• Bài kiểm tra ngẫu nhiên: Lập danh sách câu hỏi từ ngân hàng câu hỏi.
• Giao nhiệm vụ: Phân phối 30 công việc cho 30 nhân viên một cách công bằng.

3. Phương Pháp 2: Dùng UNIQUE Kết Hợp RANDBETWEEN Để Tạo Số Ngẫu Nhiên Độc Đáo

Nếu bạn muốn tạo một dải số nguyên ngẫu nhiên trong một khoảng mà số lượng yêu cầu ít hơn hoặc bằng độ rộng của khoảng (ví dụ: tạo 15 số ngẫu nhiên trong khoảng 1‑30), có thể dùng:

=UNIQUE(RANDBETWEEN(1,30), FALSE, FALSE)

Tuy nhiên, công thức trên sẽ không đảm bảo đủ 15 số vì RANDBETWEEN có thể tạo trùng. Để chắc chắn, chúng ta cần lặp lại cho đến khi đủ:

=LET( n, 15, low, 1, high, 30, arr, UNIQUE(RANDBETWEEN(low, high), FALSE, FALSE), IF(COUNTA(arr) < n, TAKE(arr, n), // nếu đủ, lấy n giá trị TAKE(UNIQUE(VSTACK(arr, RANDBETWEEN(low, high))), n) // nếu chưa đủ, bổ sung )
)

Excel không hỗ trợ vòng lặp trực tiếp trong công thức, nhưng với hàm LET và LAMBDA (Excel 365) chúng ta có thể xây dựng một hàm đệ quy để lặp lại cho tới khi đủ số lượng. Dưới đây là một ví dụ sử dụng LAMBDA:

=LAMBDA(n, low, high, LET( gen, RANDBETWEEN(low, high), uniq, UNIQUE(gen), IF(COUNTA(uniq) >= n, TAKE(uniq, n), LAMBDA(newN, newLow, newHigh, LAMBDA(uniq2, IF(COUNTA(uniq2) >= newN, TAKE(uniq2, newN), LAMBDA(_, LAMBDA(uniq3, LAMBDA(_, LAMBDA(_, LAMBDA(_, _, _))))) // placeholder ) )(UNIQUE(VSTACK(uniq, RANDBETWEEN(newLow, newHigh)))) )(n, low, high) ) )
)(15,1,30)

Mặc dù cú pháp hơi phức tạp, nhưng nó minh họa khả năng lập trình hàm tùy chỉnh trong Excel mà không cần VBA.

Có thể bạn quan tâm: Cách Đếm Số Dòng Được Lọc Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

4. Phương Pháp 3: Sử Dụng INDEX + RANK.EQ Để Lấy Hoán Vị Ngẫu Nhiên

4.1. Ý Tưởng

• Tạo một mảng các số ngẫu nhiên (có thể trùng).
• Dùng RANK.EQ để xác định vị trí sắp xếp của từng giá trị.
• Dùng INDEX để lấy giá trị thứ k trong dãy gốc dựa trên thứ hạng.

4.2. Bước Thực Hiện

Giả sử chúng ta có dãy gốc 1‑50 ở cột B (B1:B50). Chúng ta muốn tạo một hoán vị ngẫu nhiên trong cột C.

1. Tạo mảng ngẫu nhiên (cột D) – đây là “trọng số” ngẫu nhiên:

excel =RAND()

Sao chép công thức này xuống D1:D50.

1. Xác định thứ hạng (cột E):

excel =RANK.EQ(D1, $D$1:$D$50, 1) // 1 = thứ hạng tăng dần

Sao chép xuống E1:E50.

1. Lấy giá trị gốc theo thứ hạng (cột C):

excel =INDEX($B$1:$B$50, E1)

Kết quả: C1:C50 là dãy 1‑50 đã được xáo trộn ngẫu nhiên, không trùng.

4.3. Ưu Nhược Điểm

5. Phương Pháp 4: Sử Dụng VBA – Tạo Hàm RandomNoDup Tùy Chỉnh

Đối với người dùng muốn tự động hoá và giảm bớt số cột phụ trợ, VBA là lựa chọn mạnh mẽ.

5.1. Mã VBA Cơ Bản

Option Explicit ' Hàm trả về một mảng 1‑D gồm n số nguyên ngẫu nhiên không trùng trong khoảng low, high
Function RandomNoDup(low As Long, high As Long, n As Long) As Variant Dim total As Long total = high - low + 1 If n > total Then Err.Raise vbObjectError + 513, "RandomNoDup", "Số phần tử yêu cầu lớn hơn số giá trị khả dụng." End If Dim arr() As Long ReDim arr(1 To total) Dim i As Long, v As Long ' Khởi tạo mảng với các giá trị liên tiếp For i = 1 To total arr(i) = low + i - 1 Next i ' Thuật toán Fisher‑Yates shuffle Dim j As Long, tmp As Long Randomize For i = total To 2 Step -1 j = Int(Rnd i) + 1 ' 1 ≤ j ≤ i tmp = arr(i) arr(i) = arr(j) arr(j) = tmp Next i ' Lấy n phần tử đầu tiên Dim result() As Variant ReDim result(1 To n, 1 To 1) For i = 1 To n result(i, 1) = arr(i) Next i RandomNoDup = result
End Function

Cách dùng trong Excel:

• Chọn một dải ô (ví dụ A1:A20), nhập công thức mảng:
  excel =RandomNoDup(1, 100, 20)
• Nhấn Ctrl+Shift+Enter (trong Excel phiên bản cũ) hoặc chỉ Enter nếu bạn đang dùng Excel 365 (dynamic array).

5.2. Giải Thích Thuật Toán Fisher‑Yates

• Thuật toán này đảm bảo hoán vị ngẫu nhiên đồng đều và không tạo trùng lặp.
• Thời gian thực thi O(N) – rất nhanh ngay cả với hàng trăm ngàn phần tử.

5.3. Mở Rộng Hàm

• Thêm tham số isString để trả về chuỗi ngẫu nhiên (ví dụ, mã số khách hàng dạng “A001”, “B023”).
• Thêm khả năng trả về mảng 2‑D (ví dụ, tạo bảng ngẫu nhiên không trùng trong cả hàng và cột).

Function RandomNoDup2D(rows As Long, cols As Long, low As Long, high As Long) As Variant Dim total As Long, n As Long total = (high - low + 1) n = rows cols If n > total Then Err.Raise vbObjectError + 514, "RandomNoDup2D", "Không đủ giá trị duy nhất." Dim flat() As Variant flat = RandomNoDup(low, high, n) ' Gọi hàm đã viết ở trên Dim out() As Variant ReDim out(1 To rows, 1 To cols) Dim i As Long, r As Long, c As Long For i = 1 To n r = Int((i - 1) / cols) + 1 c = ((i - 1) Mod cols) + 1 out(r, c) = flat(i, 1) Next i RandomNoDup2D = out
End Function

Sử dụng:

=RandomNoDup2D(5,4,1,100) // trả về bảng 5×4 với 20 số duy nhất.

5.4. Ưu Nhược Điểm VBA

6. So Sánh Tổng Quan Các Phương Pháp

7. Ứng Dụng Thực Tiễn

7.1. Rút Thăm Ngẫu Nhiên Cho Nhân Viên

Giả sử công ty có 150 nhân viên, muốn chọn 5 người để tham gia dự án đặc biệt.

• Bước 1: Tạo danh sách nhân viên ở cột A (A2:A151).
• Bước 2: Dùng công thức:

excel =INDEX($A$2:$A$151, SORTBY(SEQUENCE(150,1,1,1), RANDARRAY(150,1)))

• Bước 3: Lấy 5 người đầu tiên (B2:B6).

7.2. Tạo Bộ Câu Hỏi Trắc Nghiệm Ngẫu Nhiên

• Ngân hàng câu hỏi trong Sheet “Bank” cột A (A2:A200).
• Muốn lấy 30 câu ngẫu nhiên không lặp:

excel =INDEX(Bank!$A$2:$A$200, SORTBY(SEQUENCE(200,1,1,1), RANDARRAY(200,1)))

Sau đó =TAKE(…,30) hoặc chỉ lấy 30 hàng đầu.

7.3. Phân Phối Công Việc

Có 12 công việc và 12 nhân viên, muốn gán ngẫu nhiên mỗi công việc cho một người.

• Dòng 1: Danh sách công việc (B2:B13).
• Dòng 2: Công thức:

excel =INDEX($C$2:$C$13, SORTBY(SEQUENCE(12,1,1,1), RANDARRAY(12,1)))

(Ở đây C2:C13 là danh sách nhân viên.)

7.4. Mô Phỏng Quay Số Lôtto

• Tạo 6 số ngẫu nhiên duy nhất trong khoảng 1‑49:

excel =SORTBY(SEQUENCE(49,1,1,1), RANDARRAY(49,1)) =TAKE(…,6)

7.5. Tạo Mã Số Đơn Độc (Unique ID)

Nếu muốn tạo 1000 ID dạng “INV2023‑XXXX” (XXXX là số 4 chữ số duy nhất):

=LET( n,1000, ids, TEXT(SORTBY(SEQUENCE(10000,1,1,1), RANDARRAY(10000,1)), "0000"), TAKE(ids, n), "INV2023-" & TAKE(ids, n)
)

Kết quả là 1000 ID không trùng.

8. Mẹo Tối Ưu và Tránh Sai Lầm Thường Gặp

1. Tắt tính toán tự động khi làm việc với công thức mảng lớn: Formulas → Calculation Options → Manual. Khi cần cập nhật, nhấn F9.
2. Sử dụng RANDARRAY chỉ một lần cho toàn bộ bảng để tránh việc Excel tính lại liên tục.
3. Giữ lại bản sao dữ liệu gốc (đặc biệt khi dùng công thức phụ trợ) để tránh “lệch” khi dãy ngẫu nhiên thay đổi.
4. Đối với VBA, luôn thêm Application.ScreenUpdating = False và Application.Calculation = xlCalculationManual trong macro để tăng tốc.
5. Bảo mật macro: ký số (digital signature) hoặc sử dụng Trusted Locations để tránh cảnh báo bảo mật.
6. Kiểm tra độ duy nhất: dùng =COUNTUNIQUE(range) (Excel 365) hoặc =SUMPRODUCT(1/COUNTIF(range, range)) để chắc chắn không có trùng.
7. Khi cần “random cố định”, sao chép‑dán giá trị (Paste Values) sau khi tạo để giữ nguyên kết quả.

9. Kết Luận

Việc tạo hàm random không lặp lại trong Excel không còn là thách thức khó khăn. Nhờ các hàm mảng hiện đại (SORTBY, RANDARRAY, UNIQUE, SEQUENCE) và khả năng lập trình VBA, người dùng có thể:

• Tự động tạo danh sách duy nhất trong vài giây mà không cần tới phần mềm bên ngoài.
• Linh hoạt áp dụng cho mọi trường hợp: rút thăm, tạo câu hỏi, phân phối công việc, mô phỏng xổ số, tạo mã số.
• Tối ưu hiệu suất bằng cách lựa chọn phương pháp phù hợp với phiên bản Excel và khối lượng dữ liệu.

Lời khuyên cuối cùng: Nếu bạn chỉ làm việc trên Excel 365 và không cần “cố định” kết quả, hãy dùng công thức SORTBY(SEQUENCE…, RANDARRAY…) – nhanh, ngắn gọn và không cần VBA. Nếu dự án của bạn yêu cầu tính ổn định, không muốn kết quả thay đổi khi mở file, hoặc làm việc trên phiên bản Excel cũ, hãy cân nhắc sử dụng VBA với thuật toán Fisher‑Yates – độ tin cậy và tốc độ cao.

Hy vọng bài viết đã cung cấp cho bạn một bộ công cụ toàn diện để giải quyết mọi nhu cầu về random không lặp lại trong Excel. Chúc bạn thành công và sáng tạo trong mọi dự án dữ liệu!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.