Trong bài viết này, chúng ta sẽ thảo luận về một số công thức khác nhau để tạo ngẫu nhiên trong Excel mà không lặp lại số. Ngoài ra, chúng tôi sẽ hiển thị cho bạn một Trình tạo Ngẫu nhiên đa năng có thể tạo ra một danh sách các số, ngày tháng và chuỗi ngẫu nhiên không lặp lại.
Như bạn có thể biết, Microsoft Excel có một số hàm để tạo số ngẫu nhiên, chẳng hạn như RAND, RANDBETWEEN và RANDARRAY. Tuy nhiên, không có gì đảm bảo rằng kết quả của bất kỳ hàm nào sẽ không bị trùng lặp.
Hướng dẫn này giải thích một số công thức để tạo danh sách các số ngẫu nhiên duy nhất. Xin lưu ý rằng một số công thức chỉ hoạt động trong phiên bản Excel 365 mới nhất trong khi những công thức khác có thể được sử dụng trong bất kỳ phiên bản nào của Excel 2019, Excel 2016, Excel 2013 và các phiên bản cũ hơn.
Nhận danh sách các số ngẫu nhiên duy nhất với bước xác định trước
Chỉ hoạt động trong các phiên bản Excel 365 mới nhất hỗ trợ mảng động.
Nếu bạn có đăng ký Microsoft 365, cách dễ nhất để bạn có được danh sách các số ngẫu nhiên duy nhất là kết hợp 3 hàm mảng động mới: SORTBY, SEQUENCE và RANDARRAY:
SORTBY (SEQUENCE (n), RANDARRAY (n))
Ở đâu n là số giá trị ngẫu nhiên bạn muốn nhận.
Ví dụ: để tạo danh sách gồm 5 số ngẫu nhiên, hãy sử dụng 5 cho n:
=SORTBY(SEQUENCE(5), RANDARRAY(5))
Nhập công thức vào ô trên cùng, nhấn Đi vào và kết quả sẽ tự động tràn qua số ô được chỉ định.
Như bạn có thể thấy trong ảnh chụp màn hình bên dưới, công thức này thực sự sắp xếp các số từ 1 đến 5 theo thứ tự ngẫu nhiên. Nếu bạn cần một trình tạo số ngẫu nhiên cổ điển không có số lặp lại, vui lòng xem các ví dụ khác sau đây.
Trong công thức trên, bạn chỉ xác định có bao nhiêu hàng để điền. Tất cả các đối số khác được để ở giá trị mặc định của chúng, có nghĩa là danh sách sẽ bắt đầu từ 1 và sẽ được tăng dần lên 1. Nếu bạn muốn một số đầu tiên và số gia khác, thì hãy đặt các giá trị của riêng bạn cho 3rd (khởi đầu) và 4thứ tự (bươc) đối số của hàm SEQUENCE.
Ví dụ: để bắt đầu từ 100 và tăng lên 10, hãy sử dụng công thức sau:
=SORTBY(SEQUENCE(5, , 100, 10), RANDARRAY(5))
Công thức này hoạt động như thế nào:
Làm việc từ trong ra ngoài, đây là những gì công thức thực hiện:
- Hàm SEQUENCE tạo một mảng số tuần tự dựa trên giá trị bắt đầu được chỉ định hoặc mặc định và kích thước bước tăng dần. Trình tự này chuyển đến mảng đối số của SORTBY.
- Các RANDARRAY hàm tạo một mảng các số ngẫu nhiên có cùng kích thước với chuỗi (5 hàng, 1 cột trong trường hợp của chúng ta). Giá trị tối thiểu và tối đa không thực sự quan trọng, vì vậy chúng ta có thể để các giá trị này thành mặc định. Mảng này chuyển đến by_array đối số của SORTBY.
- Các SORTBY hàm sắp xếp các số tuần tự được tạo bởi SEQUENCE bằng cách sử dụng một mảng các số ngẫu nhiên do RANDARRAY tạo ra.
Hãy nhớ rằng công thức đơn giản này tạo ra một danh sách các số ngẫu nhiên không lặp lại với bước xác định trước. Để vượt qua giới hạn này, hãy sử dụng phiên bản nâng cao của công thức được mô tả bên dưới.
Tạo danh sách các số ngẫu nhiên không trùng lặp
Chỉ hoạt động trong các phiên bản Excel 365 mới nhất hỗ trợ mảng động.
Để tạo số ngẫu nhiên trong Excel mà không có số trùng lặp, hãy sử dụng một trong các công thức chung dưới đây.
Số nguyên ngẫu nhiên:
INDEX (DUY NHẤT (RANDARRAY (n* 2, 1, min, tối đa, TRUE)), SEQUENCE (n))
Số thập phân ngẫu nhiên:
INDEX (DUY NHẤT (RANDARRAY (n* 2, 1, min, tối đa, FALSE)), SEQUENCE (n))
Ở đâu:
- N là số lượng giá trị cần tạo.
- Min là giá trị nhỏ nhất.
- Max là giá trị lớn nhất.
Ví dụ, để tạo danh sách 5 số nguyên ngẫu nhiên từ 1 đến 100 không lặp lại, hãy sử dụng công thức này:
=INDEX(UNIQUE(RANDARRAY(10, 1, 1, 100, TRUE)), SEQUENCE(5))
Để tạo ra 5 ngẫu nhiên duy nhất số thập phân, đặt FALSE trong đối số cuối cùng của RANDARRAY hoặc bỏ qua đối số này:
=INDEX(UNIQUE(RANDARRAY(10, 1, 1, 100)), SEQUENCE(5))
Công thức này hoạt động như thế nào:
Thoạt nhìn, công thức có thể hơi phức tạp, nhưng khi xem xét kỹ hơn, logic của nó rất đơn giản:
- Hàm RANDARRAY tạo một mảng các số ngẫu nhiên dựa trên các giá trị tối thiểu và tối đa mà bạn chỉ định. Để xác định có bao nhiêu giá trị cần tạo, bạn nhân số mong muốn ít nhất với 2 (vì mảng kết quả có thể có trên một mảng biết bao nhiêu bản sao sẽ được xử lý sau này). Trong ví dụ này, chúng ta chỉ cần 5 số ngẫu nhiên duy nhất nhưng buộc RANDARRAY tạo ra 10, sao cho UNIQUE có đủ giá trị để chọn.
- Các ĐỘC NHẤT hàm loại bỏ tất cả các bản sao và “cấp” một mảng không có bản sao thành INDEX.
- Từ mảng được truyền bởi UNIQUE, MỤC LỤC chức năng trích xuất đầu tiên n các giá trị như được chỉ định bởi SEQUENCE (5 số trong trường hợp của chúng tôi). Bởi vì các giá trị đã có thứ tự ngẫu nhiên, không thực sự quan trọng là giá trị nào tồn tại.
Tạo một dải số ngẫu nhiên không lặp lại trong Excel
Chỉ hoạt động trong các phiên bản Excel 365 mới nhất hỗ trợ mảng động.
Để tạo một dải số ngẫu nhiên không có số lặp lại, hãy sửa đổi công thức trên theo cách này:
INDEX (DUY NHẤT (RANDARRAY (hàng* 2, cột, min, tối đa)), SỰ NỐI TIẾP(hàng), {1,2,…})
Ở đâu:
- Hàng là số hàng cần điền.
- Cột là số cột cần điền.
- Min là giá trị thấp nhất.
- Max là giá trị cao nhất.
- {1,2,…} là một hằng số mảng bao gồm các số / chỉ số của tất cả các cột được điền các giá trị ngẫu nhiên. Ví dụ, để điền vào 2 cột, hãy sử dụng hằng mảng {1,2}; để điền vào 3 cột, hãy sử dụng {1,2,3}, v.v.
Ví dụ: để điền vào phạm vi 10 hàng và 3 cột với các số ngẫu nhiên duy nhất từ 1 đến 100, hãy sử dụng công thức sau:
=INDEX(UNIQUE(RANDARRAY(20, 3, 1, 100)), SEQUENCE(10), {1,2,3})
Và nó sẽ tạo ra một mảng các số thập phân ngẫu nhiên không có số lặp lại:
Nếu bạn cần số nguyên, thì hãy đặt đối số cuối cùng của RANDARRAY thành TRUE:
=INDEX(UNIQUE(RANDARRAY(20, 3, 1, 100, TRUE)), SEQUENCE(10), {1,2,3})
Cách ngăn các số ngẫu nhiên thay đổi
Tất cả các hàm ngẫu nhiên hóa trong Excel bao gồm RAND, RANDBETWEEN và RANDARRAY đều dễ bay hơi, có nghĩa là chúng sẽ tính toán lại mỗi khi thay đổi bảng tính. Kết quả là, các giá trị ngẫu nhiên mới được tạo ra với mọi thay đổi. Để ngăn tự động tạo các số mới, hãy sử dụng Dán Đặc biệt> Giá trị tính năng thay thế các công thức bằng các giá trị tĩnh. Đây là cách thực hiện:
- Chọn tất cả các ô với công thức ngẫu nhiên của bạn và nhấn Ctrl + C để sao chép chúng.
- Nhấp chuột phải vào phạm vi đã chọn và nhấp vào Dán đặc biệt > Giá trị. Ngoài ra, bạn có thể nhấn Shift + F10 và sau đó V, là phím tắt cho tùy chọn này.
Để biết các bước chi tiết, vui lòng xem Cách thay đổi công thức thành giá trị trong Excel.
Cách tạo số ngẫu nhiên duy nhất trong Excel 2019, 2016 trở về trước
Vì không có phiên bản nào ngoài Excel 365 hỗ trợ mảng động nên không có giải pháp nào ở trên hoạt động trong các phiên bản Excel trước. Tuy nhiên, điều này không có nghĩa là không có giải pháp nào cả, bạn chỉ cần thực hiện thêm một số bước sau:
- Tạo một danh sách các số ngẫu nhiên. Dựa trên nhu cầu của bạn, hãy sử dụng:
- Các RAND hàm để tạo các số thập phân ngẫu nhiên từ 0 đến 1, hoặc
- Các RANDBETWEEN hàm để tạo ra các số nguyên ngẫu nhiên trong phạm vi mà bạn chỉ định.
Đảm bảo tạo nhiều giá trị hơn bạn thực sự cần vì một số giá trị sẽ bị trùng lặp và bạn sẽ xóa chúng sau đó.
Đối với ví dụ này, chúng tôi đang tạo một danh sách 10 số nguyên ngẫu nhiên từ 1 đến 20 bằng cách sử dụng công thức dưới đây:
=RANDBETWEEN(1,20)Để nhập công thức vào nhiều ô cùng một lúc, hãy chọn tất cả các ô (A2: A15 trong ví dụ của chúng tôi), nhập công thức vào thanh công thức và nhấn Ctrl + Enter. Hoặc bạn có thể nhập công thức vào ô đầu tiên như bình thường, sau đó kéo công thức xuống bao nhiêu ô nếu cần.
Dù sao, kết quả sẽ giống như thế này:
Như bạn có thể nhận thấy, chúng tôi đã nhập công thức vào 14 ô, mặc dù cuối cùng chúng tôi chỉ cần 10 số ngẫu nhiên duy nhất.
- Thay đổi công thức thành giá trị. Vì cả RAND và RANDBETWEEN đều tính toán lại với mọi thay đổi trên trang tính, danh sách các số ngẫu nhiên của bạn sẽ liên tục thay đổi. Để ngăn điều này xảy ra, hãy sử dụng Dán Đặc biệt> Giá trị để chuyển đổi công thức thành giá trị như được giải thích trong Cách ngăn các số ngẫu nhiên tính toán lại.Để đảm bảo bạn đã làm đúng, hãy chọn bất kỳ số nào và nhìn vào thanh công thức. Bây giờ nó sẽ hiển thị một giá trị, không phải một công thức:
- Xóa các bản sao. Để hoàn thành, hãy chọn tất cả các số, chuyển đến Dữ liệu tab> Công cụ dữ liệu nhóm và nhấp vào Loại bỏ các bản sao. bên trong Loại bỏ các bản sao hộp thoại xuất hiện, chỉ cần nhấp vào OK mà không thay đổi bất cứ điều gì. Để biết các bước chi tiết, vui lòng xem Cách xóa các bản sao trong Excel.
Làm xong! Tất cả các bản sao đã biến mất và bây giờ bạn có thể xóa các số dư thừa.
Trình tạo số ngẫu nhiên cho Excel không có lặp lại
Người dùng của chúng tôi Suite Ultimate không thực sự cần bất kỳ giải pháp nào ở trên vì họ đã có Trình tạo ngẫu nhiên chung trong Excel của họ. Công cụ này có thể dễ dàng tạo ra một danh sách các số nguyên, số thập phân, ngày tháng và mật khẩu duy nhất không lặp lại. Đây là cách thực hiện:
- Trên Công cụ Ablebits tab, nhấp vào Ngẫu nhiên hóa > Máy phát ngẫu nhiên.
- Chọn phạm vi để điền các số ngẫu nhiên.
- Trên Máy phát ngẫu nhiên , hãy làm như sau:
- Chọn loại giá trị mong muốn: số nguyên, số thực, ngày tháng, Boolean, danh sách tùy chỉnh hoặc chuỗi (lý tưởng để tạo mật khẩu mạnh duy nhất!).
- Thiết lập Từ và Đến các giá trị.
- Chọn Giá trị duy nhất hộp kiểm.
- Nhấp chuột Tạo ra.
Đó là nó! Phạm vi đã chọn được lấp đầy bằng các số ngẫu nhiên không lặp lại cùng một lúc:
Nếu bạn tò mò muốn dùng thử công cụ này và khám phá các tính năng hấp dẫn khác đi kèm với Suite Ultimate, bạn đều được chào đón tải xuống phiên bản dùng thử 14 ngày. Nếu bạn thích các công cụ và quyết định lấy giấy phép, đừng bỏ lỡ mã phiếu giảm giá 15% mà chúng tôi cung cấp riêng cho độc giả blog của mình: AB14-BlogSpo
Đó là cách ngẫu nhiên hóa các số trong Excel mà không bị trùng lặp. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!
The post Cách tạo số ngẫu nhiên trong Excel mà không bị lặp appeared first on Kế Toán MVB | Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng.
source https://ketoanmvb.com/cach-tao-so-ngau-nhien-trong-excel-ma-khong-bi-lap.html
Không có nhận xét nào:
Đăng nhận xét