Công thức chung
=MATCH(RAND(),cumulative_probability)
Tóm lược
Để tạo một số ngẫu nhiên, có trọng số với xác suất nhất định, bạn có thể sử dụng bảng trợ giúp cùng với công thức dựa trên các hàm RAND và MATCH.
Trong ví dụ được hiển thị, công thức trong F5 là:
=MATCH(RAND(),D$5:D$10)
Giải trình
Công thức này dựa vào bảng trợ giúp hiển thị trong phạm vi B4: D10. Cột B chứa sáu số mà chúng tôi muốn là kết quả cuối cùng. Cột C chứa trọng số xác suất được gán cho mỗi số, được nhập dưới dạng phần trăm. Cột D chứa xác suất tích lũy, được tạo bằng công thức này trong D5, được sao chép xuống:
=SUM(D4,C4)
Lưu ý, chúng tôi đang cố ý chuyển xác suất tích lũy xuống một hàng, để giá trị trong D5 bằng 0. Điều này là để đảm bảo MATCH có thể tìm thấy vị trí cho tất cả các giá trị xuống 0 như được giải thích bên dưới.
Để tạo một giá trị ngẫu nhiên, sử dụng xác suất có trọng số trong bảng trợ giúp, F5 chứa công thức này, được sao chép xuống:
=MATCH(RAND(),D$5:D$10)
Bên trong MATCH, giá trị tra cứu được cung cấp bởi hàm RAND. RAND tạo ra một giá trị ngẫu nhiên từ 0 đến 1. Mảng tra cứu là phạm vi D5: D10, bị khóa để nó sẽ không thay đổi khi công thức được sao chép xuống cột.
Đối số thứ ba cho MATCH, loại đối sánh, bị bỏ qua. Khi loại đối sánh bị bỏ qua, MATCH sẽ trả về vị trí của giá trị lớn nhất nhỏ hơn hoặc bằng giá trị tra cứu *. Về mặt thực tế, điều này có nghĩa là hàm MATCH di chuyển dọc theo các giá trị trong D5: D10 cho đến khi gặp giá trị lớn hơn, sau đó "lùi lại" về vị trí cũ. Khi MATCH gặp giá trị lớn hơn giá trị cuối cùng lớn nhất trong D5: D10 (.7 trong ví dụ), nó sẽ trả về vị trí cuối cùng (6 trong ví dụ). Như đã đề cập ở trên, giá trị đầu tiên trong D5: D10 cố tình bằng 0 để đảm bảo rằng các giá trị bên dưới .1 được bảng tra cứu "bắt" và trả về vị trí 1.
* Các giá trị trong phạm vi tra cứu phải được sắp xếp theo thứ tự tăng dần.
Giá trị văn bản có trọng số ngẫu nhiên
Để trả về giá trị văn bản có trọng số ngẫu nhiên (tức là giá trị không phải số), bạn có thể nhập giá trị văn bản trong phạm vi B5: B10, sau đó thêm INDEX để trả về giá trị trong phạm vi đó, dựa trên vị trí được trả về bởi MATCH:
=INDEX($B$5:$B$10,MATCH(RAND(),D$5:D$10))
Ghi chú
- Tôi gặp phải phương pháp này trong một bài đăng trên diễn đàn trên mrexcel.com
- RAND là một hàm dễ bay hơi và sẽ tính toán lại với mọi thay đổi trang tính
- Khi bạn có (các) giá trị ngẫu nhiên, hãy sử dụng dán các giá trị> đặc biệt để thay thế công thức nếu cần
