Cara Menciptakan Dropdown List Bertingkat Pada Excel

Cara menciptakan dropdown list bertingkat di excel Cara Membuat Dropdown List Bertingkat Pada Excel

Saat menciptakan 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 semacam ini sering disebut dengan dropdown list bertingkat (nested dropdown list) atau list validasi bertingkat (nested list validation).

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

Misalnya kita telah menciptakan list validasi berupa pilihan nama-nama provinsi, kemudian kita menginginkan untuk isian nama kabupaten/kota, dropdown listnya akan menyesuikan dengan isian data provinsi yang telah 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 dipakai untuk membatasi isi cell di excel dengan beberapa pilihan tertentu. Misalnya menciptakan pilihan jenis kelamin Laki-laki/Perempuan, pilihan Ya/Tidak dan lain sebagainya.

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

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

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

Jika diharapkan silahkan baca 2 tutorial berguru excel berikut:

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

Cara Membuat Dropsdown List Bertingkat Dengan Rumus IF Excel

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

Langkah-langkah yang diharapkan untuk menciptakan dropdown list bertingkat dengan rumus IF excel yaitu sebagai berikut:

Buatlah sumber data untuk list validasi tingkat pertama yang akan kita gunakan sebagai pilihan data untuk dropdown list.
Siapkan juga sumber data untuk list validasi tingkat kedua. Daftar list ini yang nantinya akan dipakai untuk dropdown list kedua. Sebagai contoh saya akan menggunakan sumber data menyerupai pada gambar berikut:
Beri nama range untuk masing-masing list data yang telah kita buat. Gunakan named range yang gampang diingat. Cara menciptakan nama range sanggup anda pelajari pada link ini: 3 Cara Memberi Nama Range Pada Excel
Selain nama range untuk masing-masing list, buat lagi satu (1) named range yang mengarah pada rujukan sel yang tidak ada isinya (Sel kosong). Misal saya menciptakan 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 gunakan untuk menamai sel E1.
Setelah tamat menciptakan nama range kita lanjutkan dengan mengatur list validation untuk tingkat pertama. Pada contoh ini saya terapkan pada sel B7.

Cara menciptakan dropdown list untuk tingkat pertama ini sama menyerupai cara menciptakan dropdown list pada umumnya. Hanya saja kali ini kita menggunakan named range sebagai source datanya. Sehingga kita cukup menggunakan nama range untuk source data validation-nya menyerupai dalam gambar diatas.
```
=List_Jenis
```
Selanjutnya kita setting untuk list validation tingkat kedua dengan menggunakan 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 jikalau sel B7 berisi teks "Tumbuhan" maka data validasi akan menggunakan named range "List_Tumbuhan" sebagai source datanya. Jika B7 berisi teks "Hewan" maka akan menggunakan nama range "List_Hewan" sebagai sumber data list validasinya. Dan apabila B7 tidak berisi teks "Tumbuhan" atau "Hewan" maka akan menggunakan nama range "List_Kosong".

Untuk klarifikasi yang lebih detail wacana Fungsi IF bertingkat sanggup anda temukan pada tautan berikut: Rumus IF Bertingkat pada Excel.
Selesai. Jika langkah-langkah yang anda lakukan benar maka risikonya akan nampak menyerupai berikut:

Dengan menggunakan cara ini anda harus paham wacana cara menggunakan rumus IF bertingkat.

Bagi yang gres berguru excel menuliskan rumus secara pribadi di pecahan source Data Validation biasanya akan sedikit kesulitan.

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

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

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

Langkah-langkah diatas relatif lebih gampang dilakukan dan difahami jikalau jumlah pilihan selnya hanya sedikit.

Untuk kasus 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 menggunakan fungsi IF menyerupai cara pertama, kita juga sanggup menggunakan fungsi INDIRECT Excel untuk menciptakan dropdown list bertingkat.

Langkah-langkah untuk menciptakan dropdown list validasi bertingkat dengan rumus Indirect yaitu sebagai berikut:

Buat daftar data yang akan kita jadikan sumber list validasi menyerupai pada cara pertama.

Untuk cara ke-2 ini judul kolom harus "sama persis" dengan list sumber untuk masing-masing pilihan validasi data.
Gunakan fitur Create from Selection pada sajian 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 opsi Create Names from Selection, centang hanya pada pecahan Top row.
- Klik OK.
- Ulangi langkah 1-4 di atas untuk kolom-kolom sumber data yang lainnya.
Selain menciptakan nama range satu persatu anda sanggup juga menyeleksi seluruh list data yang dibentuk kemudian menggunakan Create form selection untuk menamai masing-masing kolom pada range tersebut.

Hanya saja jikalau menggunakan cara ini anda perlu menyesuiakan lagi range data pada masing-masing nama alasannya banyak data untuk masing-masing nama kemungkinan tidak sama.

Setelah tamat menciptakan nama range untuk masing-masing kolom sumber list validasi maka anda akan mempunyai beberapa nama range menyerupai yang nampak pada kotak name manager berikut:
Setelah tamat menciptakan nama range, langkah berikutnya yaitu 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.
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 pola yang akan memilih source range mana yang akan dipakai untuk dropdown list tingkat ke-2 ini.
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)
```
Selesai. Jika langkah yang anda lakukan benar maka risikonya akan menyerupai pada gambar dibawah ini.

Rumu excel "=INDIRECT(Alamat_Sel_Rujukan)" menyerupai yang kita gunakan di atas berlaku jikalau list pilihan yang kita gunakan tidak mengandung spasi atau hanya 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 dikala menciptakan nama range dengan menggunakan cara "Create form Selection" di atas, secara otomatis spasi tersebut akan diganti dengan garis bawah/ underscore (_).

Lalu bagaimana solusinya?

Solusinya yaitu dengan menggunakan rumus yang merubah spasi dengan garis bawah tersebut.

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

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

Perhatikan contoh berikut:

Cara menciptakan dropdown list bertingkat di excel Cara Membuat Dropdown List Bertingkat Pada Excel

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

- Sel B8 :

=List_Kabupaten

- Sel B9 :

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

Pada dikala menggunakan rumus data diatas mungkin anda akan mendapat pesan error berikut.

Cara menciptakan dropdown list bertingkat di excel Cara Membuat Dropdown List Bertingkat Pada Excel

Hal ini masuk akal alasannya untuk dikala ini sel B8 masih kosong sehingga excel menganggap nama range yang dicari tidak ditemukan. Makara abaikan saja pesan error ini dan lanjut klik YES.

- Sel B10 :

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

Download File Contoh List Validasi Betingkat di Excel

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

Dropbox
GDrive

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

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

Kalau masih gagal juga silahkan tinggalkan pesan di pecahan bawah halaman ini.

Untuk tutorial menciptakan 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 bermanfaat jangan sungkan-sungkan untuk share artikel tutorial berguru excel ini ke media umum yang anda gunakan.

Semoga bermanfaat. Salam .