Cara Mengacak Angka di Excel

Cara Mengacak Angka di Excel – Selama beberapa bulan terakhir, saya telah menulis tentang beberapa fungsi array dinamis baru Excel. Mereka kuat dan mudah diimplementasikan—membebaskan kita dari beberapa pengembangan ekspresi yang sangat melelahkan yang diperlukan sebelumnya. Dalam artikel ini, kami akan menggabungkan beberapa fungsi larik dinamis baru ini untuk menghasilkan larik nilai acak yang unik. Sangat mudah untuk menghasilkan nilai acak; sama mudahnya untuk membatasi batas bawah dan atas untuk sekumpulan nilai acak. Apa yang tidak mudah, seperti yang akan Anda lihat, adalah menghasilkan larik nilai acak yang unik. Untungnya, dengan menggunakan fungsi array dinamis, Anda hanya perlu melewati beberapa rintangan.

Cara Mengacak Angka di Excel

Saya menggunakan Microsoft 365 pada sistem Windows 10 64-bit. Fungsi array dinamis didukung oleh 365, Excel untuk Web, Excel 2021, Excel untuk iPad dan iPhone, Excel untuk tablet dan ponsel Android. Tidak ada file demonstrasi; Anda tidak akan membutuhkannya.

Tentang fungsi acak

Excel memiliki tiga fungsi nilai acak: RAND(), RANDBETWEEN(), dan RANDARRAY(). RAND() menghasilkan nilai acak antara 0 dan 1, jadi nilai desimal acak. RANDBETWEEN() memungkinkan Anda menentukan nilai bawah dan atas. Misalnya, Anda mungkin menggunakan
RANDBETWEEN(10,1000)
untuk menghasilkan bilangan bulat acak antara 10 dan 1.000.

Terakhir, RANDARRAY() mengembalikan array nilai acak menggunakan sintaks berikut:
RANDARRAY([rows], [columns], [min], [max], [integer])
Perhatikan bahwa semua argumen adalah opsional. Argumen ini memungkinkan Anda untuk menentukan jumlah baris dan kolom yang akan diisi, nilai minimum dan maksimum yang akan dikembalikan, dan apakah akan mengembalikan bilangan bulat atau desimal. Argumen integer salah secara default, yang mengembalikan nilai desimal. Untuk mengembalikan bilangan bulat, Anda harus menentukan TRUE. Selain itu, min dan max adalah nilai inklusif. Misalnya, jika kedua argumen ini masing-masing adalah 1 dan 5, fungsi akan mengembalikan 1, 2, 3, 4 dan 5, bukan hanya 2, 3 dan 4.

Saat mempertimbangkan argumen, ingatlah aturan berikut:

• RANDARRAY() bekerja seperti RAND(), mengembalikan nilai antara 0 dan 1 jika Anda menghilangkan semua argumen.
• RANDARRAY() bekerja seperti RAND(), mengembalikan nilai antara 0 dan 1 jika Anda menghilangkan baris dan kolom.
• RANDARRAY() akan mengembalikan 0 atau 1 masing-masing, jika Anda menghilangkan min dan max.
• Argumen min harus kurang dari maks.
• RANDARRAY() akan menyertakan nilai desimal jika Anda tidak secara eksplisit menyetel integer ke TRUE.
• RANDARRAY() akan membuat rentang ukuran yang sesuai untuk menyelesaikan perhitungan. Rentang ini disebut rentang tumpahan. Jika tidak ada sel yang memadai dalam rentang ini, RANDARRAY() mengembalikan kesalahan tumpahan. Untuk mempelajari selengkapnya tentang rentang tumpahan Excel, Anda dapat membaca Cara menggunakan rentang tumpahan di Excel
• RANDARRAY() akan memperbarui hasil dan mengubah ukuran ketika data sumber berubah jika data sumber berada dalam objek Tabel.
• Saat ini, jika sumber data ada di buku kerja lain, kedua buku kerja harus dibuka agar RANDARRAY() berfungsi dengan benar.
• Untuk menghasilkan array acak dari nilai non-duplikat, kita akan menggunakan tiga fungsi array dinamis: RANDARRAY(), UNIQUE(), dan SEQUENCE().

Cara menghasilkan nilai acak tanpa duplikat, semacam

RANDARRAY() sendiri memiliki potensi untuk mengembalikan nilai duplikat, seperti halnya semua fungsi acak. Jika Anda punya waktu, ada cara sederhana untuk mencapai nilai acak tanpa duplikat. Ubah nilai dalam rentang tumpahan ke nilai statis (juga menggantikan fungsi) lalu hapus duplikat apa pun menggunakan alat Hapus Duplikat pada tab Data. Ini mudah, tetapi itu bukan cara yang masuk akal untuk bekerja kecuali apa yang Anda lakukan adalah pass satu kali.

Anda tidak dapat memaksa RANDARRAY() untuk mengembalikan hanya nilai unik, tetapi Anda dapat menggabungkannya dengan UNIQUE() untuk mendapatkan hasil yang diinginkan. Misalnya, fungsi berikut, ditunjukkan pada Gambar Aakan mengembalikan satu kolom berisi lima (tampaknya) nilai unik antara 1 dan 20:

=UNIQUE(RANDARRAY(5, 1, 1, 20,TRUE))

Dua hal mungkin terjadi: 1.) Anda melihat daftar nilai unik acak yang kurang dari yang Anda tentukan; 2.) Anda melihat kesalahan tumpahan (ke kanan di Gambar A).

Ketika RANDARRAY() mengulangi nilai yang cukup sehingga ekspresi tidak dapat mengembalikan lima nilai unik dari kumpulan nilai acak RANDARRAY(), itu akan mengembalikan apa yang bisa—yang mungkin berupa satu atau dua nilai pendek. Peluang memiliki begitu banyak nilai berulang dalam kumpulan 20 (nilai minimum dan maksimum masing-masing 1 dan 20) sehingga Anda tidak dapat mengembalikan daftar lima kecil, tetapi bukan tidak mungkin. Tekan F9 dan lihat pembaruan ekspresi; akhirnya Anda akan melihat array yang kurang dari lima digit.

Tembakan di sebelah kanan (Gambar A) menunjukkan ekspresi di bilah rumus tempat saya menghitung RANDARRAY() secara manual dengan memilih hanya fungsi itu dan menekan F9. Seperti yang Anda lihat, array melakukan ulangi nilai, nilai 1. Oleh karena itu, ekspresi hanya dapat mengembalikan empat nilai unik.

Gambar A

Anda mungkin berharap melihat lima nilai unik.

Gambar B menunjukkan kesalahan tumpahan, rentang tumpahan tidak diketahui. Saya telah mengubah argumen baris dari 5 menjadi 19 memaksa itu terjadi lebih sering, tetapi jika Anda cukup sering menekan F9, argumen baris 5 pada akhirnya akan mengembalikan kesalahan tumpahan. Ini ada hubungannya dengan sifat volatil dari fungsi-fungsi ini—terkadang yang satu menghitung lebih cepat dari yang lain. A lincah fungsi menghitung ulang setiap kali perubahan dibuat.

Gambar B

Kesalahan tumpahan dimungkinkan dengan ekspresi sederhana ini.

Salah satu cara untuk memastikan bahwa ekspresi tidak mengembalikan nilai yang lebih sedikit dari yang ditentukan adalah dengan membuat kumpulan besar nilai acak. Mungkin cara termudah adalah mengubah argumen baris dari nilai tertentu menjadi ekspresi singkat: n^2. Sebagai contoh,

=UNIQUE(RANDARRAY(5^2, 1, 1, 20,TRUE))

Ekspresi baris, 5^2, akan mengembalikan 25 nilai acak, tetapi sekarang ekspresi kembali hingga 25 nilai (baris) dan Anda masih rentan terhadap kesalahan rentang tumpahan sesekali. Namun, tidak mungkin mengembalikan kurang dari lima baris.

Jika Anda bisa hidup dengan semua perilaku ini, Anda bisa berhenti di sini. Namun, kemungkinannya adalah Anda menginginkan sesuatu yang lebih stabil.

Cara menghasilkan nilai acak tanpa duplikat

Di bagian terakhir, Anda belajar banyak tentang RANDARRAY() dan UNIQUE() dan bagaimana mereka bekerja bersama, meskipun hasilnya tidak stabil. Kebutuhan Anda akan menentukan apakah Anda perlu membawa masalah ini ke tingkat berikutnya.

Saat ini, kami memiliki ekspresi yang tidak selalu mengembalikan jumlah nilai yang tepat dan berpotensi mengembalikan kesalahan tumpahan. Untuk menghindari dua masalah ini, Anda memerlukan ekspresi yang lebih kompleks dalam bentuk

=INDEX(UNIQUE(RANDARRAY(rows^2, columns, min, max, TRUE)), SEQUENCE(rows))

Kelihatannya mengerikan, tapi jangan khawatir. Setelah Anda memahami cara kerjanya, itu akan masuk akal, dan Anda tidak akan kesulitan menerapkannya pada pekerjaan Anda sendiri.

RANDARRAY() menghasilkan array nilai acak berdasarkan min dan maks. Argumen baris dengan pangkat 2 menghasilkan kumpulan besar nilai acak—jauh lebih besar dari sekadar nilai baris itu sendiri. UNIQUE() menghapus semua nilai duplikat dan mengembalikan array nilai unik. Kami sampai sejauh ini di bagian terakhir.

Perbedaannya kali ini, UNIQUE() tidak mengembalikan array ke sheet. Setelah UNIQUE() menghapus duplikat, INDEX() menggunakan jumlah baris yang dinyatakan dalam SEQUENCE() untuk menentukan berapa banyak nilai acak unik yang akan dikembalikan sebagai larik ke sheet.

Gambar C menunjukkan ekspresi ini menggunakan nilai argumen kami sebelumnya:

=INDEX(UNIQUE(RANDARRAY(5^2,1,1,10,TRUE)), SEQUENCE(5))

Pertama, RANDARRAY() mengembalikan larik satu kolom dari 25 bilangan bulat acak antara 1 dan 10. UNIQUE() menghapus semua duplikat dari hasil tersebut. INDEX() kemudian mengembalikan lima nilai unik dan acak teratas, sebagaimana ditentukan oleh SEQUENCE(5).

Gambar C

Anda membutuhkan ekspresi yang lebih stabil.

Perlu diingat bahwa min dan max masih penting. Misalnya, jika min dan max masing-masing adalah 1 dan 5, Anda tidak dapat mengembalikan array 10 nilai—tidak ada banyak nilai unik antara 1 dan 5. Ekspresi akan mengembalikan lima nilai unik, tetapi sel yang tersisa dalam rentang tumpahan akan menampilkan kesalahan. Itu penyederhanaan yang berlebihan, tapi itu intinya.

Selain itu, array besar membutuhkan waktu. Pertimbangkan nilai baris 2000^2. Secara internal, Excel menghitung 4.000.000 angka acak! Anda mungkin tidak membutuhkan kolam sebesar itu. Sebagai gantinya, kalikan argumen baris dengan nilai lain, seperti 10. Itu mengembalikan 20.000 nilai acak, yang mungkin cukup, bergantung pada nilai min dan maks.

Anda harus bereksperimen untuk menentukan apakah kolam cukup besar. Tidak ada peluru perak yang sebenarnya karena hubungannya: peluang atau pengembalian rentang tumpahan yang tidak lengkap meningkat karena jumlah angka acak yang diteruskan ke UNIQUE() berkurang.

Saatnya pengakuan. Ini adalah ekspresi kreatif dan kuat yang memanfaatkan fungsi larik dinamis Excel yang lebih baru. Saya ingin mengklaim kredit penuh untuk merancang ekspresi ini, tetapi sudah ada di internet sekarang untuk sementara waktu. Jika saya dapat memuji jenius kreatif yang pertama kali menciptakannya, saya akan melakukannya.