Cara Membuat Dropdown List Bertingkat Pada Excel

Saat membuat 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 membuat 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 digunakan untuk membatasi isi cell di excel dengan beberapa pilihan tertentu. Misalnya membuat pilihan jenis kelamin Laki-laki/Perempuan, pilihan Ya/Tidak dan lain sebagainya.

Untuk membuat 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 tentang dropdown list bertingkat di excel, bagaimana cara membuatnya? Silahkan simak penjelasan berikut ini.



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

Jika diperlukan silahkan baca 2 tutorial belajar 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 bisa kita lakukan untuk membuat dropdown list validasi data bertingkat pada excel. Yang pertama dengan menggunakan Fungsi Excel IF dan yang kedua dengan menggunakan Fungsi Excel INDIRECT.


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

Langkah-langkah yang diperlukan untuk membuat dropdown list bertingkat dengan rumus IF excel adalah sebagai berikut:
  1. Buatlah sumber data untuk list validasi tingkat pertama yang akan kita gunakan sebagai 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 menggunakan sumber data seperti pada gambar berikut:

    Sumber list validasi bertingkat
  3. Beri nama range untuk masing-masing list data yang telah kita buat. Gunakan named range yang mudah diingat. Cara membuat nama range bisa 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 referensi sel yang tidak ada isinya (Sel kosong). Misal saya membuat nama range "Kosong" yang mengarah pada sel E1 seperti digambar berikut:

    Nama range untuk list validasi bertingkat

    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_Tumbuhandan Range tingkat kedua selanjutnya (D2:D5) saya beri nama List_Hewan. Sedangkan untuk nama range List_Kosong saya gunakan untuk menamai sel E1.
  5. Setelah selesai membuat nama range kita lanjutkan dengan mengatur list validation untuk tingkat pertama. Pada contoh ini saya terapkan pada sel B7.

    Dropdown list tingkat pertama

    Cara membuat dropdown list untuk tingkat pertama ini sama seperti cara membuat 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 seperti dalam gambar diatas.

    =List_Jenis
  6. 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))

    Dropdown list tingkat kedua

    Perhatikan bahwa nama range pada rumus diatas, tidak perlu ditulis diantara tanda petik ganda.

    Rumus diatas dapat diartikan bahwa jika 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 penjelasan yang lebih detail tentang Fungsi IF bertingkat bisa anda temukan pada tautan berikut: Rumus IF Bertingkat pada Excel.
  7. Selesai. Jika langkah-langkah yang anda lakukan benar maka hasilnya akan nampak seperti berikut: 

    Dropdown List Bertingkat Dengan Fungsi IF Excel

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

Bagi yang baru belajar excel menuliskan rumus secara langsung di bagian 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 bagian source Data Validation

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

Langkah-langkah diatas relatif lebih mudah dilakukan dan difahami jika 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.


Selain menggunakan fungsi IF seperti cara pertama, kita juga bisa menggunakan fungsi INDIRECT Excel untuk membuat dropdown list bertingkat.

Langkah-langkah untuk membuat dropdown list validasi bertingkat dengan rumus Indirect adalah sebagai berikut:
  1. Buat daftar data yang akan kita jadikan sumber list validasi seperti pada cara pertama.

    Sumber Dropdown List Bertingkat

    Untuk cara ke-2 ini judul kolom harus "sama persis" dengan list sumber untuk masing-masing pilihan validasi data.
  2. Gunakan fitur Create from Selection pada menu Defined Names untuk menamai masing-masing kolom sumber data tersebut. Caranya:

    Named Range dengan Create From Selection
    • Seleksi range sumber pada kolom pertama.
    • Pilih Tab Formulas -- Create from Selection.
    • Pada kotak opsi Create Names from Selection, centang hanya pada bagian Top row.
    • Klik OK.
    • Ulangi langkah 1-4 di atas untuk kolom-kolom sumber data yang lainnya.


© Kelas Excel | https://www.kelasexcel.web.id/2017/05/cara-membuat-dropdown-list-bertingkat-excel.html

Comments

Post a Comment

Popular posts from this blog

Menyajikan Angka Dalam Ribuan atau Jutaan Pada Laporan Keuangan

Image
Menyajikan Angka Dalam Ribuan atau Jutaan Pada Laporan Keuangan Pernahkah Anda membaca laporan keuangan perusahaan terbuka (Tbk) yang telah diaudit yang biasanya ditampilkan di surat kabar? Jika Anda cermati, Anda akan melihat ciri khas laporan keuangan yaitu dinyatakan atau disajikan dengan satuan yang lebih besar misalnya dalam ribuan atau jutaan. Pada judul laporan keuangan seperti tampak pada gambar di bawah, dijelaskan bahwa laporan keuangan disajikan dalam jutaan rupiah sebagai berikut. Baca Juga:  Cara Membuat DropDown List di Excel Dengan kata lain, angka yang ditampilkan pada laporan keuangan biasanya lebih kecil dari angka sebenarnya. Sebagai contoh, angka Rp 35,000,000,000 (35 milyar) pada laporan keuangan, biasanya ditampilkan sebagai 35,000,000 (disajikan dalam ribuan) atau 35,000 (disajikan dalam jutaan). Tujuannya adalah, untuk menampilkan laporan keuangan secara efisien dengan tidak banyak memakan tempat atau space. Hal ini mengingat besarnya angka-angka...
Read more

Menampilkan Tombol Data Form Pada Ribbon Excel

Image
Berikut langkah-langkah untuk mengaktifkan perintah atau tombol data form pada ribbon excel: Aktifkan kotak dialog Customize Ribbon dengan cara membuka  Tab FILE -- Options -- Customize Ribbon  atau Klik kanan pada sembarang bagian ribbon lalu pilih  menu Customize Ribbon . Setelah muncul opsi Customize the ribbon, pada bagian  Choose Command From  pilih  All Commands  kemudian cari dan pilih menu perintah  Form... . Pada bagian kanan buatlah  New Group  atau  New TAB  sebagai tempat baru untuk meletakkan posisi perintah Data Form. Apabila sudah  klik Add  untuk menambahkan perintah atau menu Data Form pada Group atau TAB baru tersebut. Klik OK dan selesai. © Kelas Excel |  https://www.kelasexcel.web.id/2018/02/data-entry-form-excel.html?m=1
Read more

12 LARANGAN PERANGKAT DESA

Image
12 Larangan bagi Perangkat Desa Diterbitkan  21.39  TAGS POJOK DESA Dalam ulasan sebelumnya, sepintas lalu telah diulas  seputar pengangkatan dan pemberhentian Perangkat Desa menurut  peraturan terbaru Menteri Dalam Negeri No.83 tahun 2015 .   Dalam peraturan yang ada dijelaskan, pemberhentian Perangkat Desa ditetapkan oleh Kepala Desa setelah dikonsultasikan dengan Camat atas nama Bupati/Walikota.  "Artinya tidak boleh sepihak, harus melalui musyawarah mufakat desa dan kemudian dikonsultasi dengan camat".   Namun, yang perlu  digaris bawahi , ketika berbicara pengangkatan dan pemberhentian, pasti ada sebab musabanya sehingga seorang perangkat desa diberhentikan oleh Kepala Desa, baik sementara atau sampai pada pemecatan.   Perangkat Desa dapat diberhentikan oleh Kades ketika yang bersangkutan melanggar larangan-larangan yang sudah ditetapkan dalam peraturan. Baik peraturan yang dikeluarkan pemerintah maupun la...
Read more