INDEX(SORTBY(cột_dữ_liệu, RANDARRAY(ROWS(cột_dữ_liệu))), SEQUENCE(n))
Trong đó:
– cột_dữ_liệu
là cột dữ liệu nguồn
– n
là số lượng ô dữ liệu bạn cần lấy
Hãy cùng nhau đi thử công thức này cho trường hợp sau đây:
=INDEX(SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10))), SEQUENCE(5))
Để tiện dụng cho việc thay đổi số lượng giá trị ngẫu nhiên lấy ra, chúng ta có thể thay số 5 trong công thức trên bằng tham chiếu tới ô C2, và công thức bây giờ của chúng ta trở thành:
=INDEX(SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10))), SEQUENCE(C2))
Công thức này hoạt đông như thế nào?
Để hiểu được công thức trên hoạt động thế nào, chúng ta hãy đi từ trong ra ngoài như cách làm với những công thức Excel khác:
– Hàm RANDARRAY sẽ tạo ra một mảng các số ngẫu nhiên dựa vào giá trị trả về của hàm ROWS()
– Hàm SORTBY sẽ sắp xếp các giá trị ngẫu nhiên được trả về này
– Hàm INDEX sẽ lấy số lượng các giá trị ngẫu nhiên do chúng ta chỉ định bởi hàm SEQUENCE
=INDEX(SORTBY(data, RANDARRAY(ROWS(data))), SEQUENCE(n), {1,2,…})
Trong đó:
– n
là số dòng ngẫu nhiên không trùng lặp cần lấy ra
– {1,2,…}
là số thứ tự các cột cần lấy dữ liệu về
Chúng ta hãy cùng đi áp dụng công thức trên cho bảng tính trong ví dụ sau đây:
=INDEX(SORTBY(A2:C10, RANDARRAY(ROWS(A2:C10))), SEQUENCE(F1), {1,2,3})
Cách công thức này hoạt động như thế nào?
Công thức này có logic giống như công thức ở trong phần trước của bài viết, chỉ khác một chi tiết nhỏ là chúng ta sẽ sử dụng hàm INDEX với một bộ tham số khác, tham số row_num sẽ nhận giá trị từ hàm SEQUENCE và column_num sẽ nhận ra trị từ mảng thứ tự của các cột.
=INDEX($A$2:$A$10, RANK.EQ(B2, $B$2:$B$10) + COUNTIF($B$2:B2, B2) - 1)
RAND()
trong cột B sẽ cho chúng ta một danh sách các số ngẫu nhiên, tuy nhiên, như bạn thấy ở trong ảnh ví dụ trên, thì danh sách này mặc dù được tạo ra một cách ngẫu nhiên bằng hàm RAND()
, nhưng vẫn có khả năng có số trùng nhau. Chúng ta sẽ đi giải thích cách chúng ta kết hợp hàm để có thể giải quyết vấn đề này.
Hàm RANK.EQ sẽ tính toán thứ hạng (rank) của các con số trong cột B ở mỗi dòng. Ví dụ, trong ô E2, RANK.EQ(B2, $B$2:$B$10)
sẽ sắp xếp thứ hạng của giá trị trong ô B2, so sánh với giá trị trong vùng B2:B10. Khi công thức này được copy xuống E3, bởi vì B2 được tham chiếu tương đối (không có ký tự $ trong địa chỉ tham chiếu), vậy nên, B2 chuyển thành B3 và công thức sẽ trả về thứ hạng của B3, …
Hàm COUNTIF sẽ giúp bạn đếm số lần một con số xuất hiện trong vùng dữ liệu. Ví dụ, trong công thức ở ô E2, COUNTIF($B$2:B2, B2)
sẽ kiểm tra xem có bao nhiêu giá trị trong ô B2 trong vùng $B$2:B2, tất nhiên nhìn vào đây các bạn sẽ thắc mắc vì sao chúng ta lại viết như vậy, trông có hơi buồn cười không? Câu trả lời là có và không, bởi vì với kỹ thuật này, khi tham chiếu ô thay đổi lúc bạn kéo công thức xuống phía dưới, thì vùng đếm bởi hàm COUNTIF cũng thay đổi theo. Khi bạn kéo công thức xuống tới E5, thì công thức COUNTIF($B$2:B5, B5)
sẽ đếm số lần xuất hiện của giá trị trong ô B5 trong vùng B2:B5.
Với công thức COUNTIF như trên, nếu giá trị trong cột B chỉ xuất hiện 1 lần, thì kết quả của hàm sẽ là 1; chúng ta sẽ trừ đi 1 đơn vị để giữ thứ hạng được tạo ra bởi hàm RANK.EQ. Nếu hàm COUNTIF trả về kết quả là 2, tương ứng với việc có 2 kết quả trùng lặp, thì chúng ta cũng trừ đi 1 để tăng kết quả thứ hạng lên, để đảm bảo không bị trùng lặp trong thứ hạng.
Trong ví dụ trên, với công thức trong ô E2 thì hàm RANK.EQ sẽ trả về kết quả là 1. Vì đây là giá trị đầu tiên, hàm COUNTIF cũng sẽ trả về kết quả là 1. RANK.EQ và COUNTIF sẽ cho kết quả bằng 2. Chúng ta trừ đi 1 đơn vị, như vậy thứ hạng sẽ trở về 1.
Bây giờ chúng ta sẽ xem, việc gì sẽ xảy ra khi có xuất hiện 2 giá trị trùng lặp bởi hàm RAND. Ví dụ trong ô B5, hàm RANK.EQ cho giá trị là 1 trong khi COUNTIF sẽ cho giá trị là 2. Tổng hai kết quả là 3, chúng ta trừ đi 1 sẽ được 2, và 2 cũng chính là thứ hạng của giá trị trong ô B5.
Thứ hạng được tính toán ra bởi sự kết hợp giữa hàm RANK.EQ và hàm COUNTIF sẽ được đưa vào tham số row_num của hàm INDEX. Hàm INDEX sẽ nhận nhiệm vụ lấy ra dòng tương ứng. Đây là lý do tại sao, chúng ta không thể có 2 dòng có cùng thứ hạng (cùng Rank), bởi vì khi đó hàm INDEX sẽ lấy ra giá trị bị trùng lặp.
Làm thế nào để Excel không thay đổi các giá trị ngẫu nhiên
Trong Excel, tất cả các hàm dữ liệu ngẫu nhiên như RAND, RANDBETWEEN và RANDARRAY đều sẽ được tính toán lại với mỗi sự thay đổi trên Worksheet. Do vậy, nếu bạn muốn Excel không phải tính toán quá nhiều, gây chậm file, bạn có thể chuyển dữ liệu sau khi tính toán thành dạng Value bằng cách copy toàn bộ dữ liệu, sau đó chọn ô sẽ đổ dữ liệu ra trên file Excel, bấm chuột hải, chọn Paste Special > Values.
Hi vọng với nội dung này, bạn có thể giải quyết được vấn đề lấy ra danh sách ngẫu nhiên không trùng lặp trong Excel của bạn. Nếu bạn chưa hiểu, hãy thử theo dõi video sau đây của Thanh:
Đăng ký học qua Email Listen@hocexcel.online
Hộ kinh doanh Học Excel Online.
Số ĐK: 17A80048102
© Học Excel Online. All rights reserved.