Formula generik
=MATCH(RAND(),cumulative_probability)
Ringkasan
Untuk menghasilkan nombor rawak, berwajaran dengan kebarangkalian tertentu, anda boleh menggunakan jadual pembantu bersama formula berdasarkan fungsi RAND dan MATCH.
Dalam contoh yang ditunjukkan, formula dalam F5 adalah:
=MATCH(RAND(),D$5:D$10)
Penjelasan
Formula ini bergantung pada helper table yang dapat dilihat dalam julat B4: D10. Lajur B mengandungi enam nombor yang kami mahukan sebagai hasil akhir. Lajur C mengandungi bobot kebarangkalian yang diberikan untuk setiap nombor, dimasukkan sebagai peratusan. Lajur D mengandungi kebarangkalian kumulatif, dibuat dengan formula ini di D5, disalin ke bawah:
=SUM(D4,C4)
Perhatikan, kami sengaja mengubah kebarangkalian kumulatif ke satu baris, sehingga nilai dalam D5 adalah sifar. Ini untuk memastikan MATCH dapat mencari kedudukan untuk semua nilai hingga sifar seperti yang dijelaskan di bawah.
Untuk menghasilkan nilai rawak, dengan menggunakan kebarangkalian tertimbang dalam jadual pembantu, F5 mengandungi formula ini, disalin ke bawah:
=MATCH(RAND(),D$5:D$10)
Di dalam MATCH, nilai carian disediakan oleh fungsi RAND. RAND menghasilkan nilai rawak antara sifar dan 1. Susunan carian adalah julat D5: D10, terkunci sehingga tidak akan berubah kerana formula disalin ke bawah lajur.
Argumen ketiga untuk MATCH, jenis padanan, dihilangkan. Apabila jenis padanan ditinggalkan, MATCH akan mengembalikan kedudukan nilai terbesar kurang dari atau sama dengan nilai pencarian *. Secara praktikal, ini bermaksud fungsi MATCH bergerak mengikut nilai di D5: D10 sehingga nilai yang lebih besar ditemui, kemudian "melangkah ke belakang" ke kedudukan sebelumnya. Apabila MATCH menemui nilai yang lebih besar daripada nilai terakhir terbesar di D5: D10 (.7 dalam contoh), ia mengembalikan kedudukan terakhir (6 dalam contoh). Seperti disebutkan di atas, nilai pertama di D5: D10 sengaja sifar untuk memastikan bahawa nilai di bawah .1 "ditangkap" oleh jadual pencarian dan mengembalikan kedudukan 1.
* Nilai dalam julat carian mesti disusun mengikut urutan menaik.
Nilai teks berwajaran rawak
Untuk mengembalikan nilai teks berwajaran rawak (iaitu nilai bukan angka), anda boleh memasukkan nilai teks dalam julat B5: B10, kemudian menambahkan INDEX untuk mengembalikan nilai dalam julat tersebut, berdasarkan kedudukan yang dikembalikan oleh MATCH:
=INDEX($B$5:$B$10,MATCH(RAND(),D$5:D$10))
Catatan
- Saya menemui pendekatan ini dalam catatan forum di mrexcel.com
- RAND adalah fungsi yang tidak menentu dan akan dikira semula dengan setiap perubahan lembaran kerja
- Setelah anda mempunyai nilai rawak, gunakan nilai khas> tampal untuk menggantikan formula jika diperlukan
