Cách lấy danh sách ngẫu nhiên không trùng lặp trong Excel

Trong quá trình hỗ trợ sinh viên và một số khách hàng, một câu hỏi mà các bạn thường đặt ra là làm thế nào để có được danh sách ngẫu nhiên không trùng lặp trong Excel. Trong bài viết này, chúng ta sẽ cùng nhau đi tìm giải pháp cho vấn đề này có thể áp dụng cho tất cả các phiên bản Excel 365, Excel 2019, Excel 2016, Excel 2013 và các phiên bản cũ hơn.

Bài viết này có kèm theo video hướng dẫn ở cuối phần.

Cách lấy danh sách ngẫu nhiên không có bản sao trong Excel

Ghi chú Điều này chỉ hoạt động trên các phiên bản Excel 365 hỗ trợ các hàm mảng động

Để tạo một danh sách ngẫu nhiên không có bản sao trong Excel, công thức chung của chúng tôi như sau

INDEX(SORTBY(cột_dữ_liệu, RANDARRAY(ROWS(cột_dữ_liệu))), SEQUENCE(n))

Tải game crack việt hoá tại: https://daominhha.com

Bên trong:

cột_dữ_liệu là cột dữ liệu nguồn
n là số ô dữ liệu bạn cần lấy

Hãy thử công thức này cho trường hợp sau:

Cách lấy dữ liệu ngẫu nhiên không trùng lặp trong Excel

Trong hình minh họa trên, chúng ta sẽ lấy 5 giá trị ngẫu nhiên không trùng lặp của các tên trong cột A, chúng ta sẽ áp dụng công thức sau:

=INDEX(SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10))), SEQUENCE(5))

Để thuận tiện cho việc thay đổi số lượng giá trị ngẫu nhiên được truy xuất, chúng ta có thể thay thế số 5 trong công thức trên bằng một tham chiếu đến ô 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ách hoạt động của công thức trên, hãy đi vào bên trong giống như chúng ta làm với các 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 trên 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 giá trị ngẫu nhiên mà chúng ta đã chỉ định bởi SEQUENCE. hàm số

>>> Xem thêm:  Hàm Hlookup trong Excel; dùng Hlookup, Vlookup hay Xlookup trong trường hợp nào

Cách lấy nhiều dòng ngẫu nhiên không trùng lặp trong Excel

Ghi chú Phương pháp này chỉ áp dụng cho phiên bản Excel 365

Để có được nhiều hàng ngẫu nhiên mà không có bản sao trong Excel, chúng ta có thể lấy theo công thức chung như sau:

=INDEX(SORTBY(data, RANDARRAY(ROWS(data))), SEQUENCE(n), {1,2,…})

Bên trong:
n là số dòng ngẫu nhiên không trùng lặp được lấy ra
{1,2,…} là số thứ tự của cột để truy xuất dữ liệu từ

Hãy áp dụng công thức trên cho bảng tính trong ví dụ sau:

Nhận các dòng ngẫu nhiên không có bản sao trong Excel

Áp dụng công thức chung của chúng tôi và thay thế phạm vi giá trị, chúng tôi có công thức sau:

=INDEX(SORTBY(A2:C10, RANDARRAY(ROWS(A2:C10))), SEQUENCE(F1), {1,2,3})

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

Công thức này có logic tương tự như công thức ở 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 hàm số. sẽ nhận ra giá trị từ mảng thứ tự của các cột.

Cách nhận giá trị ngẫu nhiên không trùng lặp trong Excel 2010-2019

Vì chỉ Excel 365 mới có thể sử dụng các hàm mảng động nên kiến ​​thức trong hai phần trên sẽ không áp dụng cho các phiên bản Excel từ Excel 2019 trở về trước.

Chúng tôi vẫn có thể giải quyết vấn đề này như sau:

  1. Trong cột B của ví dụ, chúng ta sẽ sử dụng hàm RAND () cho các ô từ B2 đến B10
  2. Trích xuất giá trị ngẫu nhiên đầu tiên với công thức sau trong ô E2:
    =INDEX($A$2:$A$10, RANK.EQ(B2, $B$2:$B$10) + COUNTIF($B$2:B2, B2) - 1)
  3. Sao chép công thức trong E2 cho số dòng tương ứng với số giá trị ngẫu nhiên bạn muốn trích xuất từ ​​danh sách. Nếu bạn muốn 3 giá trị ngẫu nhiên không trùng nhau, bạn kéo công thức từ ô E2 đến ô E4. Nếu chúng ta muốn 4 giá trị ngẫu nhiên không trùng nhau, chúng ta kéo công thức từ E2 sang E5
>>> Xem thêm:  Cách tính số ngày công, ngày nghỉ, ngày làm việc trong Excel

Và kết quả sẽ là:

Nhận dữ liệu ngẫu nhiên bằng công thức Excel

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

Đầu tiên, hàm RAND() trong cột B sẽ cung cấp cho chúng ta một danh sách các số ngẫu nhiên, tuy nhiên, như bạn có thể thấy trong hình ảnh ví dụ ở trên, danh sách này mặc dù được tạo ngẫu nhiên bởi hàm RAND(), nhưng vẫn có khả năng trùng lặp số. Chúng tôi sẽ giải thích cách chúng tôi kết hợp các chức năng để có thể giải quyết vấn đề này.

Hàm RANK.EQ tính toán thứ hạng của các số trong cột B trong mỗi hà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 nó với giá trị trong phạm vi B2: B10. Khi công thức này được sao chép xuống E3, vì B2 là tham chiếu tương đối (không có $ trong địa chỉ tham chiếu), vì vậy B2 chuyển đổi thành B3 và công thức trả về thứ hạng của B3, …

Hàm COUNTIF sẽ giúp bạn đếm số lần một số xuất hiện trong phạm vi dữ liệu. Ví dụ, trong công thức ở ô E2, COUNTIF($B$2:B2, B2) sẽ kiểm tra xem ô B2 có bao nhiêu giá trị trong phạm vi $ B $ 2: B2, tất nhiên nhìn vào ô này bạn sẽ thắc mắc tại sao chúng ta lại viết như vậy, trông hơi buồn cười phả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 khi bạn kéo công thức xuống, vùng được tính bởi hàm COUNTIF cũng thay đổi theo. Khi bạn kéo công thức xuống E5, 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 phạm vi B2: B5.

Với công thức COUNTIF trên, nếu giá trị ở cột B chỉ xuất hiện một 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 do hàm RANK.EQ tạo ra. 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ì ta cũng trừ đi 1 để tăng kết quả xếp hạng, đảm bảo không có kết quả trùng lặp nào trong bảng xếp hạng.

>>> Xem thêm:  Chuyển đổi độ phút giây (góc) sang giá trị dùng để tính toán trong Excel

Trong ví dụ trên, với công thức trong ô E2, hàm RANK.EQ sẽ trả về 1. Vì đây là giá trị đầu tiên nên hàm COUNTIF cũng sẽ trả về 1. RANK.EQ và COUNTIF sẽ cho kết quả. kết quả là 2. Chúng tôi trừ đi 1 đơn vị, vì vậy hạng sẽ trở về 1.

Bây giờ chúng ta sẽ xem, điều gì sẽ xảy ra khi có 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 của hai kết quả là 3, chúng ta trừ 1 để được 2 và 2 cũng là thứ hạng của giá bán. giá trị trong ô B5.

Thứ hạng được tính bằng sự kết hợp củ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 hàng tương ứng. Đây là lý do tại sao, chúng ta không thể có 2 hàng có cùng thứ hạng (cùng Thứ hạng), vì khi đó hàm INDEX sẽ nhận giá trị trùng lặp.

Cách 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 sẽ được tính toán lại với mỗi thay đổi trên Trang tính. Do đó, nếu muốn Excel không phải tính toán quá nhiều và làm chậm file, bạn có thể chuyển dữ liệu sau khi tính toán sang dạng Value bằng cách copy toàn bộ dữ liệu, sau đó chọn ô sẽ kết xuất dữ liệu trên file. Excel, bấm đúp, chọn Dán đặc biệt> Giá trị.

Hy vọng với nội dung này, bạn có thể giải quyết vấn đề nhận được danh sách ngẫu nhiên không có bản sao trong Excel của mình. Nếu bạn chưa hiểu, hãy thử xem video sau của Thành:

Video trên YouTube

Nguồn : Blog hocexcel