Formula umum
=MATCH(RAND(),cumulative_probability)
Ringkasan
Untuk menghasilkan nomor acak, berbobot dengan probabilitas tertentu, Anda dapat menggunakan tabel pembantu bersama dengan rumus yang didasarkan pada fungsi RAND dan MATCH.
Dalam contoh yang ditunjukkan, rumus di F5 adalah:
=MATCH(RAND(),D$5:D$10)
Penjelasan
Rumus ini bergantung pada tabel pembantu yang terlihat dalam rentang B4: D10. Kolom B berisi enam angka yang kita inginkan sebagai hasil akhir. Kolom C berisi bobot probabilitas yang ditetapkan untuk setiap angka, dimasukkan sebagai persentase. Kolom D berisi probabilitas kumulatif, dibuat dengan rumus ini di D5, disalin:
=SUM(D4,C4)
Perhatikan, kami sengaja menggeser probabilitas kumulatif ke bawah satu baris, sehingga nilai di D5 adalah nol. Ini untuk memastikan MATCH dapat menemukan posisi untuk semua nilai hingga nol seperti yang dijelaskan di bawah ini.
Untuk menghasilkan nilai acak, menggunakan probabilitas tertimbang di tabel pembantu, F5 berisi rumus ini, disalin ke bawah:
=MATCH(RAND(),D$5:D$10)
Di dalam MATCH, nilai pencarian disediakan oleh fungsi RAND. RAND menghasilkan nilai acak antara nol dan 1. Array pencarian adalah kisaran D5: D10, dikunci sehingga tidak akan berubah saat rumus disalin ke bawah kolom.
Argumen ketiga untuk MATCH, jenis pencocokan, dihilangkan. Saat jenis pencocokan dihilangkan, MATCH akan mengembalikan posisi nilai terbesar kurang dari atau sama dengan nilai pencarian *. Dalam istilah praktis, ini berarti fungsi MATCH berjalan di sepanjang nilai di D5: D10 sampai nilai yang lebih besar ditemukan, lalu "mundur" ke posisi sebelumnya. Saat MATCH menemukan nilai yang lebih besar dari nilai terakhir terbesar di D5: D10 (0,7 dalam contoh), ia mengembalikan posisi terakhir (6 dalam contoh). Seperti disebutkan di atas, nilai pertama di D5: D10 sengaja nol untuk memastikan bahwa nilai di bawah .1 "ditangkap" oleh tabel pemeta dan mengembalikan posisi 1.
* Nilai dalam rentang pencarian harus diurutkan dalam urutan menaik.
Nilai teks berbobot acak
Untuk mengembalikan nilai teks berbobot acak (yaitu nilai non-numerik), Anda dapat memasukkan nilai teks dalam rentang B5: B10, lalu menambahkan INDEX untuk mengembalikan nilai dalam rentang tersebut, berdasarkan posisi yang dikembalikan oleh MATCH:
=INDEX($B$5:$B$10,MATCH(RAND(),D$5:D$10))
Catatan
- Saya menemukan pendekatan ini dalam posting forum di mrexcel.com
- RAND adalah fungsi yang mudah menguap dan akan dihitung ulang dengan setiap perubahan lembar kerja
- Setelah Anda memiliki nilai acak, gunakan paste special> values untuk mengganti rumus jika diperlukan
