Dalam pengolahan data menggunakan Microsoft Excel, sering kali kita perlu membuat dropdown list dinamis yang berubah berdasarkan kategori tertentu. Misalnya, ketika pengguna memilih “Buah” pada dropdown pertama, maka dropdown kedua hanya menampilkan daftar buah saja. Teknik ini sangat berguna untuk membuat formulir, laporan interaktif, atau sistem input data yang rapi dan terstruktur.
Artikel ini akan membahas cara membuat dropdown list dinamis di Excel berdasarkan kategori yang dipilih. Ada dua cara untuk membuat dropdown kategori di sel F2, yaitu secara manual dan menggunakan VBA (Visual Basic for Applications).
1. Struktur Data
Sebelum memulai cara membuat dropdown list dinamis di Excel, susun data Anda di Sheet1 seperti ini:
Kolom B (Distributor) berisi kategori dan kolom C(Produk) berisi item yang sesuai. Dan kita akan membuat dropdown listnya di sel F2 untuk kategori dan F3 untuk item pilihannya.
2. Cara Manual Membuat Dropdown List Dinamis di Excel
Jika Anda tidak ingin menggunakan kode VBA, berikut langkah-langkah manual cara membuat dropdown list dinamis di Excel:
- Buat Daftar Kategori Unik:
Gunakan fitur “Remove Duplicates” atau formula seperti=UNIQUE(B3:B13)
untuk menampilkan kategori unik. - Buat Named Range untuk setiap kategori berisi item terkait. Misalnya:
- Range untuk “PT. Danone” dinamai
Danone
- Range untuk “PT. Indofood” dinamai
Indofood
- Di sel F2, buat dropdown kategori menggunakan Data Validation:
Data
>Data Validation
>List
>=DaftarKategori
- Di sel F3, buat dropdown list dinamis dengan rumus
=INDIRECT(F2)
, agar isinya tergantung pada kategori yang dipilih di F2.
Ini adalah cara membuat dropdown list dinamis di Excel secara manual, namun bisa menjadi rumit jika banyak kategori.
3. Cara Otomatis Menggunakan VBA
Cara membuat dropdown list dinamis di Excel yang lebih cepat dan otomatis adalah menggunakan VBA. Anda hanya perlu sekali menuliskan skrip, dan dropdown akan dibuat serta berubah secara otomatis berdasarkan kategori.
Script VBA Pertama: Buat Dropdown Kategori Otomatis di F2
Sub BuatDropdownKategori()
Dim ws As Worksheet
Dim dictKategori As Object
Dim i As Long, lastRow As Long
Dim listKategori As String
Set ws = ThisWorkbook.Sheets("Sheet1")
Set dictKategori = CreateObject("Scripting.Dictionary")
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
For i = 2 To lastRow
If ws.Cells(i, "B").Value <> "" Then
dictKategori(ws.Cells(i, "B").Value) = 1
End If
Next i
For Each key In dictKategori.Keys
listKategori = listKategori & key & ","
Next key
If Len(listKategori) > 0 Then
listKategori = Left(listKategori, Len(listKategori) - 1)
End If
With ws.Range("F2").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=listKategori
.IgnoreBlank = True
.InCellDropdown = True
End With
End Sub
Penjelasan:
- Script ini membaca semua kategori di kolom B dan menghapus duplikatnya menggunakan Dictionary.
- Kemudian membuat dropdown kategori otomatis di sel F2.
Berikut penampakannya:
Cara Menjalankan Script Ini:
- Tekan
ALT + F11
untuk membuka VBA Editor. - Klik kanan
ThisWorkbook
>Insert
>Module
. - Paste script di atas ke jendela modul.
- Jalankan macro
BuatDropdownKategori
.
4. Script Kedua: Dropdown List Dinamis Berdasarkan Kategori di F3
Private Sub Worksheet_Change(ByVal Target As Range)
Dim dictKategori As Object
Dim ws As Worksheet
Dim i As Long
Dim lastRow As Long
Dim kategoriInput As String
Dim listItems As String
Set ws = Me
Set dictKategori = CreateObject("Scripting.Dictionary")
If Not Intersect(Target, ws.Range("F2")) Is Nothing Then
Application.EnableEvents = False
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
For i = 2 To lastRow
If ws.Cells(i, "B").Value <> "" And ws.Cells(i, "C").Value <> "" Then
If Not dictKategori.exists(ws.Cells(i, "B").Value) Then
dictKategori.Add ws.Cells(i, "B").Value, ws.Cells(i, "C").Value
Else
dictKategori(ws.Cells(i, "B").Value) = _
dictKategori(ws.Cells(i, "B").Value) & "," & ws.Cells(i, "C").Value
End If
End If
Next i
ws.Range("F3").Validation.Delete
kategoriInput = ws.Range("F2").Value
If kategoriInput <> "" And dictKategori.exists(kategoriInput) Then
listItems = dictKategori(kategoriInput)
With ws.Range("F3").Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=listItems
.IgnoreBlank = True
.InCellDropdown = True
End With
End If
Application.EnableEvents = True
End If
End Sub
Penjelasan:
- Script ini aktif otomatis setiap kali isi sel F2 berubah.
- Akan membuat dropdown dinamis di F3 berdasarkan kategori terpilih di F2.
- Menyesuaikan isi dropdown dengan item yang cocok dari kolom C.
Berikut penampakannya:
Cara Memasang Script Ini:
- Di VBA Editor, klik
Sheet1 (Sheet1)
pada jendela Project. - Paste script tersebut ke jendela kode Sheet1.
Jika benar semua maka berikut adalah hasil akhirnya:
Kesimpulan
Dengan mengikuti langkah di atas, Anda dapat dengan mudah memahami cara membuat dropdown list dinamis di Excel. Anda bisa memilih metode manual jika data sedikit, atau metode otomatis menggunakan VBA untuk efisiensi maksimal.
Berikut manfaat utama dari cara membuat dropdown list dinamis di Excel ini:
- Menghemat waktu input data.
- Menghindari kesalahan input.
- Membuat worksheet lebih interaktif dan profesional.
Cobalah sekarang dan rasakan kemudahan dalam membuat dropdown list dinamis di Excel berdasarkan kategori!
Download Template Gratis
Bagi Anda yang ingin mencoba teknik ini secara langsung, kami telah menyiapkan template gratis yang dirancang khusus untuk membantu Anda mempraktikkan Cara Membuat Dropdown List Dinamis di Excel Berdasarkan Kategori yang Dipilih ini. File Excel bisa didownload melalui tautan berikut:
Atau juga jika ingin mempelajari langkah demi langkah secara visual, bisa kunjungi Youtube Channel Depot Excel. Di sana teman-teman akan melihat bagaimana rumusnya bekerja. Semoga bermanfaat!
0 Komentar