Cara Menghasilkan Dropdown List Bertingkat Pada Excel | Kelas Excel

Cara menghasilkan Dropdown list bertingkat atau list data validasi bertingkat pada microsoft excel

Dropdown List Bertingkat - Saat menghasilkan Dropdown List dengan fitur data validation , ada kalanya kita ingin dropdown list untuk pilihan sel tersebut berubah secara otomatis menyesuaikan dengan isi data pada sel lainnya.

Kasus seperti ini sering disebut dengan dropdown list bertingkat (nested dropdown list) atau list validasi bertingkat (nested list validation).

Kasus ini berlainan dengan dropdown list dinamis yang sudah kita pelajari sebelumnya.

Misalnya kita sudah menghasilkan list validasi berupa pilihan nama-nama provinsi , kemudian kita mengharapkan untuk isian nama kabupaten/kota , dropdown listnya akan menyesuikan dengan isian data provinsi yang sudah dipilih. Begitu juga dengan isian data nama kecamatan yang secara otomatis pilihan dropdown listnya akan menyesuaikan dengan data kabupaten yang dipilih.

Dropdown list atau list validasi sendiri digunakan untuk menangkal isi cell di excel dengan beberapa pilihan tertentu. Misalnya menghasilkan pilihan jenis kelamin Laki-laki/Perempuan , pilihan Ya/Tidak dan lain sebagainya.

Untuk menghasilkan pilihan sel seperti ini , kita memakai fitur data validation di excel. Silahkan dibaca pada tautan berikut: Cara Membuat Dropdown List Dengan Data Validation Excel.

Kembali terhadap pokok permasalahan ihwal dropdown list bertingkat di excel , bagaimana cara membuatnya? Silahkan simak klarifikasi berikut ini.

• Dropdown List Validasi Bertingkat Dengan Rumus IF
• Dropdown List Validasi Bertingkat Dengan Rumus INDIRECT
• Download Contoh File List Validasi bertingkat

Pada bimbingan "Cara menghasilkan dropdown list bertingkat pada excel" kali ini , saya anggap anda sudah sanggup menghasilkan list data validasi pada excel. Sehingga dimungkinkan akan ada beberapa langkah yang saya lewati.

Jika dikehendaki silahkan baca 2 bimbingan mencar ilmu excel berikut:

1. Data Validation Excel - Cara Membatasi Isi Cell di Excel
2. Cara Membuat Dropdown List Dengan Data Validation Excel

Ada dua cara yang sanggup kita lakukan untuk menghasilkan dropdown list validasi data bertingkat pada excel. Yang pertama dengan memakai Fungsi Excel IF dan yang kedua dengan memakai Fungsi Excel INDIRECT.

Cara Membuat Dropsdown List Bertingkat Dengan Rumus IF Excel

Untuk cara pertama ini , kita akan memakai fungsi IF excel.

Langkah-langkah yang dikehendaki untuk menghasilkan dropdown list bertingkat dengan rumus IF excel merupakan selaku berikut:

1. Buatlah sumber data untuk list validasi tingkat pertama yang hendak kita gunakan selaku pilihan data untuk dropdown list.
2. Siapkan juga sumber data untuk list validasi tingkat kedua. Daftar list ini yang nantinya akan digunakan untuk dropdown list kedua. Sebagai contoh saya akan memakai sumber data menyerupai pada gambar berikut:
   
   
3. Beri nama range untuk masing-masing list data yang sudah kita buat. Gunakan named range yang gampang diingat. Cara menghasilkan nama range sanggup anda pelajari pada link ini: 3 Cara Memberi Nama Range Pada Excel
4. Selain nama range untuk masing-masing list , buat lagi satu (1) named range yang mengarah pada acuan sel yang tidak ada isinya (Sel kosong). Misal saya menghasilkan nama range "Kosong" yang mengarah pada sel E1 menyerupai digambar berikut:
   
   
   
   Pada contoh ini range untuk tingkat pertama (A2:A3) Saya beri nama List_Jenis. Sedangkan untuk Range tingkat kedua pertama (C2:C4) saya beri nama List_Tumbuhan dan Range tingkat kedua selanjutnya (D2:D5) saya beri nama List_Hewan. Sedangkan untuk nama range List_Kosong saya pakai untuk menamai sel E1.
5. Setelah simpulan menghasilkan nama range kita teruskan dengan mengendalikan list validation untuk tingkat pertama. Pada contoh ini saya terapkan pada sel B7.
   
   
   
   Cara menghasilkan dropdown list untuk tingkat pertama ini sama menyerupai cara menghasilkan dropdown list pada umumnya. Hanya saja kali ini kita memakai named range selaku source datanya. Sehingga kita cukup memakai nama range untuk source data validation-nya menyerupai dalam gambar diatas.
   
   

   =List_Jenis

6. Selanjutnya kita setting untuk list validation tingkat kedua dengan memakai rumus excel berikut pada source list data validationnya:
   
   

   =IF($B$7="Tumbuhan";List_Tumbuhan;IF($B$7="Hewan";List_Hewan;List_Kosong))

   
   
   
   Perhatikan bahwa nama range pada rumus diatas , tidak perlu ditulis diantara tanda petik ganda.
   
   Rumus diatas sanggup diartikan bahwa kalau sel B7 berisi teks "Tumbuhan" maka data validasi akan memakai named range "List_Tumbuhan" selaku source datanya. Jika B7 berisi teks "Hewan" maka akan memakai nama range "List_Hewan" selaku sumber data list validasinya. Dan apabila B7 tidak berisi teks "Tumbuhan" atau "Hewan" maka akan memakai nama range "List_Kosong".
   
   Untuk klarifikasi yang lebih rincian ihwal Fungsi IF bertingkat sanggup anda dapatkan pada tautan berikut: Rumus IF Bertingkat pada Excel.
7. Selesai. Jika tindakan yang anda lakukan benar maka kesudahannya akan nampak menyerupai berikut:
   
   

Dengan memakai cara ini anda mesti paham ihwal cara memakai rumus IF bertingkat.

Bagi yang gres mencar ilmu excel menuliskan rumus secara pribadi di kepingan source Data Validation biasanya akan sedikit kesulitan.

Tips dari saya , tulis dahulu rumusnya di lembar kerja atau sel tertentu. Kalau perlu hilangkan dahulu tanda sama dengan diawal(=).

Jika dirasa sudah benar lakukan copy paste rumus excel tersebut ke kepingan source Data Validation

Cara diatas merupakan alternatif pertama untuk menghasilkan dropdown list bertingkat atau list validasi bertingkat di excel.

Langkah-langkah diatas relatif lebih gampang dijalankan dan difahami kalau jumlah pilihan selnya cuma sedikit.

Untuk kendala dengan jenjang atau tingkat dropdown list yang banyak ada cara lain yang relatif lebih mudah.

Mari kita pelajari cara kedua berikut ini.

Cara Membuat Dropdown List Bertingkat Dengan Fungsi INDIRECT Excel

Selain memakai fungsi IF menyerupai cara pertama , kita juga sanggup memakai fungsi INDIRECT Excel untuk menghasilkan dropdown list bertingkat.

Langkah-langkah untuk menghasilkan dropdown list validasi bertingkat dengan rumus Indirect merupakan selaku berikut:

1. Buat daftar data yang hendak kita jadikan sumber list validasi menyerupai pada cara pertama.
   
   
   
   Untuk cara ke-2 ini judul kolom mesti "sama persis" dengan list sumber untuk masing-masing pilihan validasi data.
2. Gunakan fitur Create from Selection pada santapan Defined Names untuk menamai masing-masing kolom sumber data tersebut. Caranya:
   
   
   
   • Seleksi range sumber pada kolom pertama.
   • Pilih Tab Formulas--Create from Selection.
   • Pada kotak pilihan Create Names from Selection , centang cuma pada kepingan Top row.
   • Klik OK.
   • Ulangi langkah 1-4 di atas untuk kolom-kolom sumber data yang lainnya.
   
   
   Selain menghasilkan nama range satu persatu anda sanggup juga menyeleksi seluruh list data yang dibentuk kemudian memakai Create form selection untuk menamai masing-masing kolom pada range tersebut.
   
   Hanya saja kalau memakai cara ini anda perlu menyesuiakan lagi range data pada masing-masing nama lantaran banyak data untuk masing-masing nama kemungkinan tidak sama.
   
   Setelah simpulan menghasilkan nama range untuk masing-masing kolom sumber list validasi maka anda akan memiliki beberapa nama range menyerupai yang nampak pada kotak name manager berikut:
   
   
   
3. Setelah simpulan menghasilkan nama range , langkah selanjutnya merupakan men-setting validasi data untuk tingkat pertama , untuk contoh ini pada sel B8. Gunakan rumus berikut pada source list validasinya.
   
   

   =Kabupaten

   
   
   
   Untuk list validasi tingkat pertama ini masih sama menyerupai cara sebelumnya.
   
4. Gunakan fungsi Indirect Excel untuk source validasi data tingkat kedua. Tuliskan rumus berikut untuk mengisi kolom source list validasinya.
   
   

   =INDIRECT($B$8)

   
   
   
   $B$8 Merupakan sel contoh yang hendak menyeleksi source range mana yang hendak digunakan untuk dropdown list tingkat ke-2 ini.
   
5. Atur validasi data tingkat ketiga untuk cell B10. Seperti pada tingkat ke-2 sebelumnya , pada Source list validasinya masukkan rumus excel berikut:
   
   

   =INDIRECT($B$9)

   
   
6. Selesai. Jika langkah yang anda lakukan benar maka kesudahannya akan menyerupai pada gambar dibawah ini.
   
   

Rumu excel "=INDIRECT(Alamat_Sel_Rujukan)" menyerupai yang kita gunakan di atas berlaku kalau list pilihan yang kita gunakan tidak mengandung spasi atau cuma satu kata saja.

Jika mengandung spasi atau lebih dari satu kata maka cara diatas tidak berlaku. Sebab nama range dilarang mengandung spasi.

Apabila mengandung spasi maka di saat menghasilkan nama range dengan memakai cara "Create form Selection" di atas , secara otomatis spasi tersebut akan diganti dengan garis bawah/ underscore (_).

Lalu bagaimana solusinya?

Solusinya merupakan dengan memakai rumus yang merubah spasi dengan garis bawah tersebut.

Salah satu cara yang sanggup kita lakukan merupakan dengan memakai fungsi SUBSTITUTE. Sehingga rumus excel untuk source validasinya akan menyerupai dibawah ini:

INDIRECT(SUBSTITUTE(Referensi_Sel_Acuan;" ";"_"))

Perhatikan contoh berikut:



Dengan data diatas masing-masing source list validasi yang di gunakan adalah:

- Sel B8 :

=List_Kabupaten

- Sel B9 :

=INDIRECT(SUBSTITUTE($B$8;" ";"_"))

Pada di saat memakai rumus data diatas mungkin anda akan mendapat pesan error berikut.



Hal ini masuk akal lantaran untuk di sekarang ini sel B8 masih kosong sehingga excel menilai nama range yang dicari tidak ditemukan. Kaprikornus abaikan saja pesan error ini dan lanjut klik YES.

- Sel B10 :

=INDIRECT(SUBSTITUTE($B$9;" ";"_"))

Download File Contoh List Validasi Betingkat di Excel

Masih gelisah juga? File untuk latihan saya sertakan pada tautan dibawah ini:

Dropbox
GDrive

*Jika link mati / tidak sanggup diakses silahkan lapor via kontak yang tersedia

FacebookTwitterWhatsapp

LINK DOWNLOAD TERKUNCI.
Silahkan SHARE untuk membuka kunci!

Apabila masih belum sanggup juga coba baca ulang dari awal. setidaknya 10x.

Kalau masih gagal juga silahkan lewati pesan di kepingan bawah halaman ini.

Untuk bimbingan menghasilkan dropdown list dinamis yang otomatis menyesuaikan sumber data yang kita buat sanggup anda pelajari pada link berikut: Cara Membuat Dropdown List Dinamis di Excel.

Jika dirasa berharga jangan sungkan-sungkan untuk share postingan bimbingan mencar ilmu excel ini ke media biasa yang anda gunakan.

Semoga bermanfaat. Salam Kelas Excel.

>> Gabung Telegram Channel <<