Trong vượt trình hỗ trợ học viên và một số khách hàng, một vụ việc mà các bạn rất giỏi hỏi sẽ là làm nỗ lực nào để hoàn toàn có thể lấy được một list không trùng lặp bỗng dưng trong Excel. Trong nội dung bài viết lần này, họ sẽ cùng nhau đi tìm hiểu giải pháp cho sự việc 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à những phiên bản trước đó.

Bạn đang xem: Tạo dãy số ngẫu nhiên không trùng

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


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

Lưu ý giải pháp này chỉ hoạt động trên phiên bạn dạng Excel 365 có cung cấp các hàm mảng động

Để tạo ra một danh sách ngẫu nhiên không giống nhau trong Excel, thì công thức chung của 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 tài liệu nguồn– n là số lượng ô tài liệu bạn cần lấy


*

Hãy cùng cả nhà đi thử cách làm này đến trường hợp sau đây:

*

Trong hình minh họa trên, bọn họ sẽ đi mang 5 giá bán trị thiên 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 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á trị bỗng dưng lấy ra, chúng ta cũng có thể thay số 5 trong bí quyết trên bởi tham chiếu tới ô C2, cùng công thức hiện nay của bọn họ 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?

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

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

Cách lấy các dòng bất chợt 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

Để lôi ra nhiều dòng thốt nhiên không giống nhau trong Excel, chúng ta 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 đề nghị lấy ra– 1,2,… là số lắp thêm tự các cột đề nghị lấy dữ liệu về

Chúng ta hãy cùng đi áp dụng công thức trên đến bảng tính trong lấy ví dụ sau đây:

*

Áp dụng công thức tổng quát của chúng ta và thay các vùng giá chỉ trị, chúng ta có công thức sau đây:

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

Cách bí quyết này hoạt động như thế nào?

Công thức này còn có logic giống hệt như công thức ở phần trước của bài viết, chỉ không giống một đưa ra tiết bé dại là chúng ta sẽ sử dụng hàm INDEX với một bộ tham số khác, tham số row_num vẫn nhận giá trị từ hàm SEQUENCE cùng column_num sẽ phân biệt trị trường đoản cú mảng vật dụng tự của những cột.

Cách mang giá trị đột nhiên không đụng hàng trong Excel 2010 – 2019

Bởi vì chưng chỉ Excel 365 mới có thể sử dụng rất nhiều hàm mảng động, nên kỹ năng và 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 từ bỏ Excel 2019 về bên trước.

Chúng ta vẫn có thể giải quyết sự việc này như sau:

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

Và kết quả nhận được đã là:

*

Công thức này vận động thế nào?

Đầu tiên, hàm RAND() vào cột B đã cho họ một danh sách những số ngẫu nhiên, mặc dù nhiên, như các bạn thấy ngơi nghỉ trong hình ảnh ví dụ trên, thì list này mặc dù được tạo nên một phương pháp ngẫu nhiên bởi hàm RAND(), dẫu vậy vẫn có chức năng có số trùng nhau. Chúng ta sẽ đi phân tích và lý giải cách họ kết hợp hàm để hoàn toàn có thể giải quyết vấn đề này.

Hàm RANK.EQ sẽ đo lường và tính toán thứ hạng (rank) của những con số vào cột B ở mỗi dòng. Ví dụ, vào ô E2, RANK.EQ(B2, $B$2:$B$10) sẽ thu xếp thứ hạng của quý giá trong ô B2, so sánh với quý hiếm trong vùng B2:B10. Khi phương pháp này được copy xuống E3, bởi vì B2 được tham chiếu kha khá (không gồm ký tự $ trong địa chỉ tham chiếu), vậy nên, B2 đưa thành B3 và công thức sẽ trả về kiểu dáng của B3, …

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

Với cách làm COUNTIF như trên, nếu cực hiếm trong cột B chỉ xuất hiện 1 lần, thì hiệu quả của hàm vẫn là 1; bọn họ sẽ trừ đi 1 đơn vị chức năng để giữ vẻ bên ngoài được tạo ra bởi hàm RANK.EQ. Giả dụ hàm COUNTIF trả về tác dụng là 2, khớp ứng với bài toán có 2 công dụng trùng lặp, thì chúng ta cũng trừ đi 1 nhằm tăng hiệu quả thứ hạng lên, để đảm bảo an toàn không bị giống nhau trong lắp thêm hạng.

Trong lấy một ví dụ trên, với cách làm trong ô E2 thì hàm RANK.EQ đã trả về tác dụng là 1. Vì đó là giá trị đầu tiên, hàm COUNTIF cũng biến thành trả về tác dụng là 1. RANK.EQ với COUNTIF vẫn cho tác dụng bằng 2. Họ trừ đi 1 đối kháng vị, vì vậy thứ hạng đang trở về 1.

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

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

Xem thêm: Hướng Dẫn Xông Mặt Bằng Nước Nóng Có Tác Dụng Gì, Tốt Không?

Làm cầm nào nhằm Excel không chuyển đổi các giá trị ngẫu nhiên

Trong Excel, toàn bộ các hàm tài liệu ngẫu nhiên như RAND, RANDBETWEEN với RANDARRAY hầu như sẽ được đo lường lại với mỗi sự chuyển đổi trên Worksheet. Vị vậy, nếu bạn có nhu cầu Excel không phải đo lường và tính toán quá nhiều, gây chậm file, chúng ta có thể chuyển tài liệu sau khi tính toán thành dạng Value bằng cách copy cục bộ dữ liệu, sau đó chọn ô sẽ đổ dữ liệu ra trên tệp tin Excel, click chuột hải, lựa chọn Paste Special > Values.

Hi vọng với nội dung này, bạn cũng có thể giải quyết được vấn đề kéo ra danh sách thốt 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, hãy thử theo dõi clip sau trên đây của Thanh: