Cara Membuat Dropdown List Bertingkat Di Excel

Membuat dropdown list dalam Excel adalah salah satu fitur yang sangat berguna untuk mempermudah pengolahan data dan analisis. Tetapi, bagaimana jika kita ingin membuat dropdown list yang terdiri dari beberapa tingkat dan menggunakan data dari sheet yang berbeda? Dalam artikel ini, kami akan membahas cara membuat dropdown list bertingkat di Excel dengan data beda sheet.

Baca Cepat show

Mengapa Kita Perlu Membuat Dropdown List Bertingkat?

Dropdown list yang terdiri dari beberapa tingkat sangat berguna ketika kita ingin membatasi input data yang valid dari user dan mempermudah proses pengolahan data. Dengan menggunakan dropdown list, kita dapat membantu user dalam memilih data yang terkait dengan input yang mereka berikan. Contoh penggunaan dropdown list bertingkat adalah dalam memilih wilayah, kabupaten/kota, dan kecamatan dalam formulir registrasi.

Cara Membuat Dropdown List Bertingkat

Untuk membuat dropdown list bertingkat di Excel, kita akan menggunakan fungsi INDIRECT yang digabungkan dengan penggunaan nama range dan validasi data. Berikut adalah langkah-langkahnya:

  1. Pertama-tama, kita akan membuat sheet baru yang akan digunakan sebagai data source untuk dropdown list. Sheet ini dapat berisi data dari berbagai kategori, seperti wilayah, kabupaten/kota, dan kecamatan. Untuk contoh ini, kita akan membuat sheet yang berisi daftar wilayah.
  2. Buka sheet baru dan beri nama sheet tersebut dengan nama yang mudah diingat, seperti “Wilayah”.
  3. Masukkan data wilayah ke dalam sheet tersebut. Kolom pertama dapat diisi dengan nomor wilayah atau kode yang unik, sedangkan kolom kedua diisi dengan nama wilayah. Pastikan bahwa data tersebut terurut secara alfabetis atau numerik, dan tidak ada data duplikat.
  4. Setelah data wilayah telah dimasukkan, kita akan memberi nama range untuk data tersebut. Pilih kolom nama wilayah, kemudian pergi ke ribbon Insert dan klik Name > Define Name.
  5. Di jendela “New Name”, ketikkan nama range yang mudah diingat, seperti “WilayahList”. Pastikan bahwa opsi “Refers to” sudah terisi dengan referensi range yang benar (misalnya, =Wilayah!$B$2:$B$10 untuk data wilayah yang ada di range B2 hingga B10 pada sheet “Wilayah”). Klik OK untuk menyimpan nama range tersebut.
  6. Selanjutnya, kita akan membuat dropdown list yang akan ditampilkan di sheet utama. Pilih cell tempat dropdown list tersebut akan tampil.
  7. Pergi ke ribbon Data dan klik Data Validation. Di jendela “Data Validation”, pilih opsi “List” dan ketikkan formula =WilayahList pada bagian “Source”. Klik OK untuk menyimpan pengaturan validasi data tersebut.
  8. Sekarang kita akan membuat dropdown list bertingkat dengan menggunakan fungsi INDIRECT. Misalkan kita ingin membuat dropdown list untuk kabupaten/kota yang terkait dengan wilayah yang dipilih oleh user. Kita akan melakukan hal berikut:
    1. Tambahkan kolom kedua pada sheet “Wilayah”, yang akan digunakan sebagai input untuk dropdown list kabupaten/kota.
    2. Masukkan data untuk kabupaten/kota di kolom kedua tersebut. Pastikan bahwa data tersebut terurut secara alfabetis atau numerik, dan tidak ada data duplikat.
    3. Beri nama range pada kolom kedua tersebut, misalnya “KotaList”.
    4. Pada cell tempat dropdown list kabupaten/kota akan ditampilkan, pilih ribbon Data dan klik Data Validation lagi. Di jendela “Data Validation”, pilih opsi “List” dan ketikkan formula =INDIRECT(“‘” & A1 & “‘!KotaList”) pada bagian “Source”. Klik OK untuk menyimpan pengaturan validasi data tersebut.
  9. Selesai! Sekarang dropdown list wilayah dan kabupaten/kota telah terbuat. Jika user memilih wilayah tertentu, maka dropdown list kabupaten/kota akan menampilkan data yang terkait dengan wilayah tersebut.
Baca Juga :  Cara Membuat Absensi Ada Excel

Cara Membuat Dropdown List Bertingkat Menggunakan VBA

Untuk mempermudah proses pembuatan dropdown list bertingkat, kita juga dapat menggunakan VBA (Visual Basic for Applications). Dengan VBA, kita dapat membuat fungsi macro yang akan menghasilkan dropdown list secara otomatis. Berikut adalah langkah-langkahnya:

  1. Buat sheet baru dan beri nama sheet tersebut dengan nama yang mudah diingat, seperti “Wilayah”.
  2. Masukkan data wilayah ke dalam sheet tersebut. Kolom pertama dapat diisi dengan nomor wilayah atau kode yang unik, sedangkan kolom kedua diisi dengan nama wilayah. Pastikan bahwa data tersebut terurut secara alfabetis atau numerik, dan tidak ada data duplikat.
  3. Beri nama range untuk data wilayah yang telah dimasukkan, misalnya “WilayahList”.
  4. Buat module baru dengan cara pilih ribbon Developer dan klik “Visual Basic”. Di jendela VBA Editor, pilih Insert dan klik Module.
  5. Ketikkan kode VBA berikut untuk membuat dropdown list wilayah:

    6. “`VBA
    Sub CreateDropDownList()
    Dim ws As Worksheet
    Dim wsList As Worksheet
    Dim strListName As String
    Dim rngList As Range
    Dim strSheetName As String
    Dim rngSheet As Range

    ‘ Define worksheet and range for dropdown list
    strSheetName = “Sheet1”
    Set ws = ThisWorkbook.Sheets(strSheetName)
    Set rngSheet = ws.Range(“A1”)

    ‘ Define worksheet and range for dropdown list source
    strListName = “WilayahList”
    Set wsList = ThisWorkbook.Sheets(“Wilayah”)
    Set rngList = wsList.Range(strListName)

    ‘ Clear any existing data validation
    rngSheet.Validation.Delete

    ‘ Set data validation source
    With rngSheet.Validation
    .Add Type:=xlValidateList, _
    AlertStyle:=xlValidAlertStop, _
    Formula1:=”=” & wsList.Name & “!” & rngList.Address
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = “”
    .ErrorTitle = “”
    .InputMessage = “”
    .ErrorMessage = “”
    .ShowInput = True
    .ShowError = True
    End With
    End Sub
    “`

  6. Simpan kode VBA tersebut, kemudian kembali ke sheet utama dan jalankan macro dengan cara tekan tombol F5 atau pilih ribbon Developer dan klik “Macros”. Pilih macro “CreateDropDownList” dan klik Run.
  7. Selanjutnya, kita akan membuat dropdown list bertingkat dengan menggunakan VBA. Kita akan menggunakan fungsi INDIRECT yang serupa dengan yang telah dibahas sebelumnya. Berikut adalah langkah-langkahnya:
    1. Tambahkan kolom kedua pada sheet “Wilayah”, yang akan digunakan sebagai input untuk dropdown list kabupaten/kota.
    2. Masukkan data untuk kabupaten/kota di kolom kedua tersebut. Pastikan bahwa data tersebut terurut secara alfabetis atau numerik, dan tidak ada data duplikat.
    3. Beri nama range pada kolom kedua tersebut, misalnya “KotaList”.
    4. Ketikkan kode VBA berikut untuk membuat dropdown list kabupaten/kota:

      5. “`VBA
      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim strTarget As String
      Dim rngList As Range
      Dim strListName As String
      Dim wsList As Worksheet
      Dim ws As Worksheet
      Dim strSheetName As String
      Dim rngSheet As Range

      ‘ Define worksheet and range for dropdown list
      strSheetName = “Sheet1”
      Set ws = ThisWorkbook.Sheets(strSheetName)
      Set rngSheet = ws.Range(“B1”)

      ‘ Define worksheet and range for dropdown list source
      strListName = “KotaList”

      ‘ Check if target cell is in the same row
      If Not Intersect(Target, Range(“A:A”)) Is Nothing Then
      ‘ Get the selected value
      strTarget = Target.Value

      ‘ Delete any existing data validation
      rngSheet.Validation.Delete

      ‘ Define worksheet and range for dropdown list source
      Set wsList = ThisWorkbook.Sheets(strTarget)
      Set rngList = wsList.Range(strListName)

      ‘ Set data validation source
      With rngSheet.Validation
      .Add Type:=xlValidateList, _
      AlertStyle:=xlValidAlertStop, _
      Formula1:=”=” & wsList.Name & “!” & rngList.Address
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = “”
      .ErrorTitle = “”
      .InputMessage = “”
      .ErrorMessage = “”
      .ShowInput = True
      .ShowError = True
      End With
      End If
      End Sub
      “`

    5. Simpan kode VBA tersebut, kemudian kembali ke sheet utama. Ketika user memilih wilayah tertentu dari dropdown list, maka dropdown list kabupaten/kota akan menampilkan data yang terkait dengan wilayah tersebut.
    6. Selesai! Sekarang dropdown list wilayah dan kabupaten/kota telah terbuat secara otomatis dengan menggunakan VBA.
Baca Juga :  CARA BUAT LINK DALAM SATU FOLDER DI EXCEL

FAQ

Q: Apakah fitur dropdown list bertingkat hanya bisa dilakukan di Excel?

A: Tidak, fitur dropdown list bertingkat juga dapat dilakukan pada aplikasi spreadsheet lainnya, seperti Google Sheets, LibreOffice Calc, dan sebagainya. Cara pembuatannya mungkin sedikit berbeda, tetapi konsep dan prinsipnya tetap sama.

Q: Apakah fitur dropdown list bertingkat dapat digunakan untuk membatasi input data pada formulir online?

A: Ya, dropdown list bertingkat juga dapat digunakan pada formulir online, tergantung pada kemampuan tools atau software yang digunakan. Sebagai contoh, dalam Google Forms, kita dapat menggunakan fitur “Multiple Choice Grid” untuk membuat dropdown list bertingkat dengan memanfaatkan kolom-kolom yang berbeda.

Video Tutorial: Membuat Dropdown List Bertingkat di Excel

Berikut adalah video tutorial dari channel YouTube “iTech Academy” yang menjelaskan cara membuat dropdown list bertingkat di Excel dengan menggunakan VBA:

Warga.Co.Id
© Copyright 2024, All Rights Reserved