Hàm ngẫu nhiên không trùng trong excel

     

Trong thừa trình cung ứng học viên và một số khách hàng, một sự việc mà các bạn rất tốt hỏi sẽ là làm cố gắng nào để hoàn toàn có thể lấy được một danh sách không trùng lặp ngẫu nhiên trong Excel. Trong bài viết lần này, họ sẽ thuộc nhau đi kiếm hiểu phương án cho vấn đề này rất có thể áp dụng cho toàn bộ các phiên phiên bản Excel 365, Excel 2019, Excel 2016, Excel 2013 và các phiên bạn dạng trước đó.

Bạn đang xem: Hàm ngẫu nhiên không trùng trong excel

Bài viết này còn có kèm theo một video clip hướng dẫn ngơi nghỉ cuối phần cuối.


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

Lưu ý phương pháp này chỉ chuyển động trên phiên bạn dạng Excel 365 có hỗ trợ các hàm mảng động

Để tạo ra một list ngẫu nhiên không trùng lặp trong Excel, thì bí quyết chung của bọn họ như sau

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 ô tài liệu bạn đề xuất lấy


*

Hãy cùng cả nhà đi thử cách làm này cho trường đúng theo sau đây:

*

Trong hình minh họa trên, họ sẽ đi mang 5 giá chỉ trị ngẫu nhiên không trùng lặp của những tên trong cột A, họ sẽ vận dụng công thức như sau:

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

Để tiện dụng cho việc biến đổi số lượng giá bán trị thốt nhiên lấy ra, chúng ta có thể thay số 5 trong phương pháp trên bằng tham chiếu tới ô C2, cùng công thức hiện giờ của họ trở thành:

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

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

Để đọc được cách làm trên vận động thế nào, chúng ta hãy đi từ trong ra bên ngoài như bí quyết làm với những công thức Excel khác:

– Hàm RANDARRAY sẽ tạo nên ra một mảng các số ngẫu nhiên phụ thuộc giá trị trả về của hàm ROWS()– Hàm SORTBY sẽ sắp xếp các giá trị tự nhiên được trả về này– Hàm INDEX sẽ lấy con số các giá chỉ trị bất chợt do chúng ta chỉ định vì chưng hàm SEQUENCE

Cách lấy nhiều dòng bỗng dưng không đụng hàng trong Excel

Lưu ý giải pháp làm này chỉ vận dụng cho phiên bản Excel 365

Để kéo ra nhiều dòng bất chợt không trùng lặp trong Excel, chúng ta cũng có thể lấy theo một công thức chung như sau:

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

Trong đó:– n là số dòng tình cờ không trùng lặp nên lấy ra– 1,2,… là số trang bị tự những cột yêu cầu lấy dữ liệu về

Chúng ta hãy cùng đi vận dụng công thức trên cho bảng tính trong ví dụ như sau đây:

*

Áp dụng công thức tổng quát của bọn họ và thay những vùng giá trị, họ có công thức sau đây:

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

Cách công thức này vận động như núm nào?

Công thức này có logic hệt như công thức tại đoạn trước của bài viết, chỉ khác một đưa ra tiết nhỏ dại là họ sẽ thực hiện hàm INDEX cùng với một cỗ tham số khác, thông số row_num đang nhận quý giá từ hàm SEQUENCE cùng column_num sẽ nhận biết trị tự mảng lắp thêm tự của những cột.

Cách rước giá trị thiên nhiên không trùng lặp trong Excel 2010 – 2019

Bởi vày chỉ Excel 365 mới rất có thể sử dụng hồ hết hàm mảng động, nên kiến thức trong nhì phần trên sẽ không áp dụng được cho các phiên bạn dạng Excel trường đoản cú Excel 2019 về bên trước.

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

Trong cột B của lấy ví dụ minh họa, họ sẽ áp dụng hàm RAND() cho những ô từ bỏ B2 cho tới B10Trích xuất ra cực hiếm ngẫu nhiên thứ nhất với công thức sau đây trong ô E2:=INDEX($A$2:$A$10, RANK.EQ(B2, $B$2:$B$10) + COUNTIF($B$2:B2, B2) - 1)Copy phương pháp trong E2 mang lại số dòng tương xứng với số cực hiếm ngẫu nhiên bạn muốn lấy ra từ bỏ danh sách. Nếu bạn muốn 3 giá bán trị thiên nhiên không trùng lặp, chúng ta kéo bí quyết từ E2 tính đến ô E4. Nếu như muốn 4 giá trị tự dưng không trùng lặp, bọn họ kéo cách làm từ E2 tới E5

Và hiệu quả nhận được đã là:

*

Công thức này chuyển động thế nào?

Đầu tiên, hàm RAND() vào cột B đã cho chúng ta một danh sách các số ngẫu nhiên, tuy nhiên, như các bạn thấy sống trong ảnh ví dụ trên, thì list này mặc dù được tạo nên một bí quyết ngẫu nhiên bằng hàm RAND(), tuy thế vẫn có tác dụng có số trùng nhau. Họ sẽ đi lý giải cách bọn họ kết thích hợp hàm để hoàn toàn có thể giải quyết vụ việc này.

Hàm RANK.EQ sẽ thống kê giám sát thứ hạng (rank) của những con số trong cột B sống mỗi dòng. Ví dụ, trong ô E2, RANK.EQ(B2, $B$2:$B$10) sẽ bố trí thứ hạng của quý giá 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, cũng chính vì B2 được tham chiếu kha khá (không gồm ký trường đoản cú $ trong địa chỉ tham chiếu), vậy nên, B2 gửi thành B3 và bí quyết sẽ trả về giao diện của B3, …

Hàm COUNTIF sẽ giúp bạn đếm tần số một con số mở ra trong vùng dữ liệu. Ví dụ, trong công thức ở ô E2, COUNTIF($B$2:B2, B2) sẽ đánh giá xem có bao nhiêu cực hiếm trong ô B2 trong vùng $B$2:B2, tất yếu nhìn vào đây các bạn sẽ thắc mắc bởi vì sao bọn họ lại viết như vậy, trông tất cả hơi bi lụy cười không? Câu trả lời là có và không, chính vì với chuyên môn này, lúc tham chiếu ô chuyển đổi lúc chúng ta kéo công thức xuống phía dưới, thì vùng đếm bởi vì hàm COUNTIF cũng thay đổi theo. Khi chúng ta kéo bí quyết xuống cho tới E5, thì bí quyết COUNTIF($B$2:B5, B5) đã đếm số lần xuất hiện thêm của quý giá trong ô B5 vào vùng B2:B5.

Xem thêm: Mắc Nối Tiếp Và Mắc Song Song

Với bí quyết COUNTIF như trên, nếu giá trị trong cột B chỉ lộ diện 1 lần, thì tác dụng của hàm đã là 1; chúng ta sẽ trừ đi 1 đơn vị chức năng để giữ hình trạng được tạo nên bởi hàm RANK.EQ. Nếu hàm COUNTIF trả về kết quả là 2, tương ứng với câu hỏi có 2 công dụng trùng lặp, thì chúng ta cũng trừ đi 1 để tăng hiệu quả thứ hạng lên, để đảm bảo an toàn không bị trùng lặp trong lắp thêm hạng.

Trong lấy một ví dụ trên, với bí quyết trong ô E2 thì hàm RANK.EQ đã trả về hiệu quả là 1. Vì đây là giá trị đầu tiên, hàm COUNTIF cũng sẽ trả về hiệu quả là 1. RANK.EQ và COUNTIF đang cho công dụng bằng 2. Bọn họ trừ đi 1 1-1 vị, vì thế thứ hạng vẫn trở về 1.

Bây giờ họ sẽ xem, việc gì sẽ xẩy ra khi có lộ diện 2 giá trị trùng lặp vì chưng hàm RAND. Lấy một ví dụ trong ô B5, hàm RANK.EQ đến giá trị là một trong những trong lúc COUNTIF sẽ cho giá trị là 2. Tổng hai tác dụng là 3, bọn họ trừ đi 1 sẽ được 2, cùng 2 cũng chính là thứ hạng của quý hiếm trong ô B5.

Thứ hạng được tính toán ra bởi vì sự kết hợp giữa hàm RANK.EQ cùng hàm COUNTIF sẽ tiến hành đưa vào tham số row_num của hàm INDEX. Hàm INDEX sẽ nhận nhiệm vụ lôi ra dòng tương ứng. Đây là lý do tại sao, họ không thể bao gồm 2 dòng có cùng hình dáng (cùng Rank), cũng chính vì khi đó hàm INDEX sẽ lôi ra giá trị bị trùng lặp.

Xem thêm: Soạn Tiếng Anh 11 Unit 3 Listening, Unit 3 Lớp 11: Listening

Làm rứa nào để Excel không thay đổi các quý hiếm ngẫu nhiên

Trong Excel, toàn bộ các hàm tài liệu ngẫu nhiên như RAND, RANDBETWEEN cùng RANDARRAY đa số sẽ được giám sát và đo lường lại với từng sự biến đổi trên Worksheet. Bởi vì vậy, nếu bạn có nhu cầu Excel ko phải đo lường và thống kê quá nhiều, gây lờ đờ 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, tiếp đến chọn ô vẫn đổ dữ liệu ra trên tệp tin Excel, bấm chuột hải, lựa chọn Paste Special > Values.

Hi vọng với câu chữ này, chúng ta cũng có thể giải quyết được vấn đề lôi ra danh sách thiên nhiên không trùng lặp trong Excel của bạn. Nếu như khách hàng chưa hiểu, thử theo dõi đoạn phim sau trên đây của Thanh: