excelive.com - Pada edisi kali ini, tutorial yang akan saya share berikut sangat cocok bagi anda yang ingin membagi atau memisah beberapa item atau anggota ke dalam beberapa grup atau kelompok yang berbeda, serta dilakukan secara acak. Konsep seperti ini bisa dijumpai pada hasil pembagian siswa-siswi di setiap kelas, atau hasil pembagian peserta kelompok pramuka, hingga bisa anda jumpai pada hasil drawing piala champions, yang mana semuanya dilakukan secara acak.

Okelah, mungkin contoh terakhir agak sedikit terlalu memaksa - tapi memang konsep tutorial ini bisa menghasilkan pembagian anggota kelompok ke dalam grup yang berbeda. Silahkan anda lihat tampilan berikut ini.


Untuk membuat seperti yang tampak di atas, saya menggunakan kombinasi antara formula Excel dengan Macro VBA, di mana formula excel saya gunakan untuk mengecek dan menentukan item atau anggota ke dalam grup atau tim dengan ukuran tertentu, sementara Macro VBA saya gunakan untuk menempatkan hasil pembagian tersebut dalam beberapa ListBox yang ada dalam UserForm.

Sebelum melangkah lebih jauh lagi, ada baiknya kita tengok beberapa data yang terdapat dalam lembar kerja yang akan kita gunakan sebagai sumber pembuatan dalam tutorial ini.

Dua kolom yang akan kita isi dengan formula excel adalah kolom B dan kolom C, yang mana masing-masing kolom tersebut dapat diisi dengan formula sebagai berikut :
Formula Excel
isi sel B2:
=RAND()
isi sel C2:
=ROUNDUP(RANK(C2;$B$2:$B$13)/$F$1;0)
Anda bisa copy-paste kedua formula tersebut hingga sel terakhir yang berisi data, atau hingga sel B13 dan C13.

Saya pikir disini saya tidak akan banyak menjelaskan fungsi dari kedua formula di atas, karena saya yakin anda sudah cukup familier dengan keduanya. Akan tetapi untuk formula yang kedua atau yang ditulis di kolom C, terdapat sebuah nilai yang mengacu ke sel F1 (warna merah), hal ini bertujuan untuk menentukan berapa banyaknya jumlah anggota yang ada dalam satu grup.

Sehingga, untuk formula yang terdapat di dalam kolom C bisa diartikan sebagai berikut :
ROUNDUP atau melakukan pembulatan terhadap hasil yang didapatkan dari formula RANK dengan kriteria yang ada di range B2 hingga B13, kemudian dibagi dengan jumlah atau nilai yang ada di sel F1.

Oke, untuk lebih memahami tutorial kali ini, silahkan anda baca lagi beberapa tutorial terkait berikut :

Panduan untuk menempatkan data ke dalam grup

Menggunakan RANK
Cara membuat nilai Acak
Berbagai cara menampilkan hasil ke kotak ListBox
ListBox berisi data-data unik
Nah, karena saya menginginkan hasil penempatan anggota tim yang ada dalam grup dibagi secara otomatis dan acak, maka kedua formula di atas saya konversi ke dalam kode Macro VBA, hingga menjadi seperti ini :
Macro VBA untuk mendapatkan Nilai Acak

Dim i As Long
Call IsiData
For i = 2 To Akhir
    Cells(i, 3) = Rnd
Next

Macro VBA untuk menentukan hasil pembagian anggota

Dim r As Double
For r = 2 To Akhir
    Cells(r, 2) = WorksheetFunction.RoundUp _
    (WorksheetFunction.Rank(Cells(r, 3), _
    Range("C2:C" & Akhir)) / [F1], 0)
Next

Sebagai tambahan, saya akan membuat sebuah variabel yang sifatnya public (kode berwarna merah) sehingga ia bisa digunakan di seluruh project vba yang sedang aktif, sehingga kita hanya tinggal memanggil nama variabel tersebut ketika ingin menggunakannya tanpa harus menuliskan lagi kode-kodenya.

Untuk tutorial kali ini saya membuat sebuah variabel yang berfungsi untuk menentukan jumlah data yang terdapat dalam lembar kerja (variabel : isi) serta membuat sebuah variabel yang bertugas untuk menentukan baris terakhir yang berisi data (variabel : Akhir). Kedua variabel ini saya masukkan ke dalam Module, seperti berikut

Option Explicit
Public Akhir As Long
Public isi As Integer

Sub IsiData()
isi = Application.WorksheetFunction.CountA(Range("A1:A" & Akhir))
Akhir = Range("A1").End(xlDown).Row
End Sub

Sementara untuk menempatkan nama-nama anggota ke dalam masing-masing ListBox seperti contoh di atas, saya menuliskan kode berikut di dalam sebuah module

Sub grup1()
Dim Data As Variant
Dim i As Long

Data = Range("A2:C" & Akhir)
UserForm1.ListBox1.Clear
For i = 1 To UBound(Data)
If Data(i, 2) = 1 Then
With UserForm1.ListBox1
   .AddItem Data(i, 1)
End With
End If
Next i
End Sub

Kode di atas digunakan untuk menempatkan nama-nama anggota ke dalam grup1 yang letaknya ada di dalam sebuah kotak bernama ListBox1 yang terdapat dalam userform dengan Name UserForm1, artinya jika anda mempunyai Name UserForm serta ListBox yang berbeda maka silahkan ganti nama dari masing-masing kontrol tersebut.
Untuk membuat grup2 dan grup3, silahkan ganti nilai 1 menjadi nilai 2 dan 3.

Setelah selesai membuat kode-kode yang ada dalam module, kini saatnya kita mengerjakan kode yang terdapat di dalam UserForm dengan asumsi bahwa objek-objek yang terdapat dalam UserForm anda memiliki beberapa objek seperti contoh di atas, antara lain :

• Nama Userform = UserForm1
• TextBox1 = Jumlah Group
• CommandButton1 = Acak
• CommandButton2 = Pasang
• ListBox1 = Grup1
• ListBox2 = Grup2
• ListBox3 = Grup3

Prosedur ketika UserForm Aktif

Private Sub UserForm_Activate()
Dim i As Long
Call IsiData
For i = 2 To Akhir
    Cells(i, 3) = Rnd
Next
End Sub

Prosedur ketika pengguna memasukkan nilai di kotak TextBox1

Private Sub TextBox1_Change()
On Error Resume Next
[F1] = isi / (TextBox1 * 1)
End Sub

Kode yang terdapat dalam CommandButton1 = Acak
Untuk kode yang ada dalam CommandButton ini, silahkan masukkan kode Macro VBA untuk mendapatkan nilai acak yang sudah saya tulis disini

Script Macro VBA untuk menempatkan anggota ke dalam masing-masing ListBox
Saya berasumsi bahwa anda sudah berhasil menulis module untuk masing-masing grup, karena untuk kode yang terdapat di CommandButton ini bertugas memanggil kode-kode tersebut, serta di gabung dengan kode ini. Dan kode akhir untuk tombol ini adalah seperti berikut :

Private Sub CommandButton1_Click()
Dim r As Double
For r = 2 To Akhir
    Cells(r, 2) = WorksheetFunction.RoundUp(WorksheetFunction.Rank(Cells(r, 3), Range("C2:C" & Akhir)) / [F1], 0)
Next

Call grup1
Call grup2
Call grup3
End Sub

Selesai.... silahkan anda lakukan test dengan cara menekan tombol yang terdapat di menubar.
Semoga tutorial ini ada manfaatnya untuk anda. Dan jangan lupa silahkan klik tombol subscribe jika anda tidak ingin ketinggalan tutorial excel lainnya dari excelive.com Gracias

Masih mau belajar lagi !

Yuk, klik tautan berikut ini :