Tin Nóng
Trong quá trình sử dụng Excel ta đã gặp nhiều bài toán yêu cầu một hoặc nhiều dãy số hoặc danh sách ngẫu nhiên, cụ thể là một dãy số ngẫu nhiên không trùng lặp. Bài toán tưởng đơn giản này hóa ra lại là một thách thức chính bởi yêu cầu về tính độc nhất của các phần tử trong dãy. Với bài viết này, Học Excel Online sẽ chỉ ra những vấn đề cơ bản và một vài cách tiếp cận để làm bài toán này.
Bài toán dãy số ngẫu nhiên không trùng lặp
Yêu cầu
Chúng ta sẽ bắt đầu bằng yêu cầu nhận được sau:
Với yêu cầu trên, ta sẽ chia nhỏ ra thành từng bước:
Bước 1: Tạo danh sách từ 1 đến 50
Bước 2: Chọn ra 25 cặp số không trùng lặp
Cách tiếp cận
Với yêu cầu này, ta có thể hiểu và tiếp cận theo nhiều hướng. Học Excel Online sẽ gợi ý một vài cách tiếp cận để giải bài toán, còn lại các bạn tự bổ sung thêm nhé.
Dãy số theo thứ tự, chọn lựa theo thứ tự
Rõ ràng là, nếu ta đưa ra dãy số từ 1 tới 50, sau đó lấy 25 cặp từ trên xuống dưới, ta cũng sẽ có 25 cặp không trùng lặp. Để tạo ra dãy từ 1 tới 50 có rất nhiều phương pháp, chẳng hạn như dùng các hàm, công thức hay tính năng Fill
Sau đó ta lấy ra từng cặp từ trên xuống dưới. Cách lấy chi tiết sẽ được nhắc đến ở phần dưới.
Dãy số không theo thứ tự, chọn lựa theo thứ tự
Trên thực tế, ta thường muốn và ứng dụng các cặp số có tính “ngẫu nhiên”. Vấn đề nhiều người dùng hay mắc phải nằm ở đây, đó làm thế nào tạo được một dãy số không theo thứ tự mà không trùng lặp. Lý do cho sự khó khăn này là do các hàm “ngẫu nhiên” có sẵn của Excel không thể đảm bảo điều đó. Ví dụ như hàm RANDBETWEEN rất thông dụng, nhưng nếu bạn áp dụng cùng lúc 50 hàm cho 50 ô, chắc chắn sẽ có ít nhất 2 ô trùng nhau.
Vậy, để tạo ra dãy số không theo thứ tự trong Excel ta làm như thế nào?
Tạo số ngẫu nhiên và lọc trùng
Với phương pháp này, ta sẽ sử dụng hàm RANDBETWEEN hoặc RANDARRAY (trong trường hợp máy bạn sử dụng Office 365) cho khoảng…. 9999 ô (càng nhiều ô càng tốt, mục đích là để các số đều xuất hiện ít nhất 1 lần), sau đó kết hợp với hàm UNIQUE (phiên bản Office 365) hoặc kết hợp với hàm RANK tại cột phụ để lấy ra các giá trị độc nhất.
Sử dụng Office 365:
Với Office 365 ta có công thức sau:
=UNIQUE(RANDARRAY(9999,1,1,50,TRUE))
Vậy là ta đã có được 1 dãy số ngẫu nhiên từ 1 tới 50 không trùng nhau. Công việc tiếp theo là chọn cặp, sẽ được nói tới ở phần dưới.
Sử dụng phiên bản Office ngoài 365
Nếu không sử dụng Office 365, ta có thể kết hợp với hàm RANK hoặc RANK.EQ (có từ phiên bản Excel 2010) trong cột phụ để lấy ra danh sách 50 số không trùng lặp từ 1 tới 50. Cụ thể:
Đầu tiên, ta vẫn tạo 1 dãy số với hàm RANDBETWEEN, tuy nhiên lần này không phải 9999 mà ta chỉ cần 50 hàm RANDBETWEEN như sau:
=RANDBETWEEN(1,50)
Sau khi tạo, ta nhận ra sẽ có kha khá số trùng lặp với nhau, bởi vậy ta cần sử dụng cột phụ. Phương pháp này có ý tưởng từ việc xếp hạng các số, nếu 2 số bằng nhau sẽ tăng thêm 1 hạng cho số ở sau. Bởi vậy, đến cuối cùng ta sẽ có 1 dãy từ 1 tới 50 ngẫu nhiên mà không cần quan tâm có bao nhiêu số bị trùng tại cột chính.
=RANK.EQ(A2,$A$2:$A$50)+COUNTIFS($A$2:A2,A2)-1
Chọn cặp số từ dãy cho trước
Với dãy số đã có, ta sẽ tiến hành chọn ra 25 cặp số. Bởi dãy số đã ngẫu nhiên nên ta không cần chọn ngẫu nhiên ô nào nữa, mà hoàn toàn có thể chọn từ trên xuống dưới.
Trong Office 365, hàm WRAPCOLS hoặc WRAPROWS sẽ giúp ta làm công việc này dễ dàng:
=WRAPROWS($B$2:$B$50,25)
hoặc
=WRAPCOLS($B$2:$B$50,2)
Còn nếu bạn đang dùng phiên bản khác? Ta hoàn toàn có thể sử dụng INDEX kết hợp với một chút kiến thức toán để lấy ra từng ô trong dãy.
=INDEX($B$2:$B$50,ROW(A1)+(COLUMN(A1)-1)*2)
Vì sao lại là ROW + COLUMN? Các bạn hãy nhìn gợi ý bên dưới được tô màu và đoán nhé 😉 (gợi ý: 1=1+(1-1)*2, 3=1+(2-1)*2).
Trên đây là một số cách cơ bản để có thể tạo ra dãy số ngẫu nhiên không trùng lặp. Về các ứng dụng khác, các bạn có thể tham khảo bài viết:
Lấy danh sách ngẫu nhiên không trùng lặp trong Excel