Cara menggunakan combobox bertingkat di excel

Dependent Dropdown List Excel sering juga disebut dengan Dropdown List Bertingkat. Pada Panduan sebelumnya, Saya sudah menunjukkan contoh hasil dropdown list bertingkat, yaitu 3 dropdown list.

Sebetulnya seperti apa sih DropDown list bertingkat itu ? Intinya begini, katakanlah Dropdown list pertama berisi A dan B. Sedangkan Dropdown List kedua berisi 1, 2, 3, dan 4.

Ketika pengguna memilih A pada Dropdown list pertama, maka pengguna hanya bisa memilih 1 atau 2 pada Dropdown list kedua.

Sementara ketika pengguna memilih B pada Dropdown List Pertama, maka pengguna hanya bisa memilih 3 atau 4 pada dropdown list kedua.

Untuk tujuan tersebut, Anda bisa mengikuti panduan ini.

• Contoh Dropdown List Bertingkat Excel Online
• Cara Membuat 3 Dropdown List Bertingkat
  • #1 Persiapkan Struktur List Data
  • #2 Gunakan Named Range / Tabel Bernama
  • #3 Buat Dropdown List Tingkat 1 (Provinsi)
  • #4 Buat Dropdown List Tingkat 2 (Kabupaten)
  • #5 Buat Dropdown List Tingkat 3 (Kecamatan)
• Bonus: Dropdown List Bertingkat Dengan Fungsi INDIRECT
  • Jadi, Perhatikan Named Range Anda
  • Dropdown List Tingkat 2 (Fungsi INDIRECT)
  • Dropdown List Tingkat 3 (Fungsi INDIRECT + SUBSTITUTE)
• Pintasan Panduan Data Validation Excel

Contoh Dropdown List Bertingkat Excel Online

Sebagai bahan latihan kali ini, Silahkan download atau gunakan file Excel Online berikut:

Note:
1. Silahkan klik Icon Full Screen Pada Sudut Kanan Bawah File Excel Online berikut untuk menggunakan fitur Excel Online.
2. Klik Icon Download (gambar selembar kertas dengan tanda panah kebawah) pada baris sudut kanan bawah File Excel Online berikut untuk download file.
3. Jika Link Download atau Excel Online Error, silahkan beritahu Saya melalui kolom komentar (Terima kasih 🙂 ).

Dalam contoh ini, Saya akan membuat 3 buah dropdown list bertingkat (saling terhubung) dengan kriteria sebagai berikut:

Kemudian, untuk membuat dropdown list bertingkat, ikuti 5 tahap berikut:

#1 Persiapkan Struktur List Data

Yang kita ketahui, dalam contoh ini ada 3 buah dropdown list. Oleh karena itu, kita akan mempersiapkan 3 Struktur data untuk masing-masing dropdown yaitu Provinsi, Kab/Kota dan Kecamatan.

Buatlah struktur list data Anda seperti gambar berikut:

Terdapat 8 list yang akan kita gunakan untuk filter masing-masing Dropdown list. Perhatikan tujuan kedelapan dropdown list tersebut:

#2 Gunakan Named Range / Tabel Bernama

Ubahlah ke delapan list tersebut menjadi Named Range atau Tabel Bernama untuk mengidentifikasi list. Dalam contoh ini Saya menggunakan Named Range.

Saya memberi nama masing-masing list seperti gambar berikut:

Penting! Judul setiap list pada gambar (seperti Provinsi, Jambi, Jakarta, Kota_Jambi, Kota_Sungai_Penuh) bukan Cara membuat Named Range / Tabel Bernama. Namun hanya untuk memudahkan Anda memahami Panduan ini. Untuk membuat Named Range / Tabel bernama silahkan kunjungi Panduan Named Range atau Tabel Bernama.

Selanjutnya kita akan menggunakan named range ini untuk mengidentifikasi dan filter list ke ketiga dropdown list. Perhatikan langkah-langkah berikut:

#3 Buat Dropdown List Tingkat 1 (Provinsi)

Untuk membuat Dropdown list tingkat pertama, silahkan ikuti cara berikut:

1. Pertama, klik Cell tempat Anda menerapkan Dropdown list tingkat pertama. Dalam contoh ini Cell B1
2. Kedua, Buka Window Data Validation. Caranya, klik Tab Data kemudian klik Data Validation.
3. Ketiga, ubah Validation Criteria – Allow menjadi List.
4. Keempat, pada Kolom Source, masukkan =Nama_List. Gunakan Named Range Data Anda. Dalam contoh ini menjadi =Provinsi
5. Terakhir, klik Ok untuk melanjutkan. Berikut hasilnya:

Dengan menggunakan rumus =Nama_List atau =Provinsi, maka Dropdown list pertama hanya akan mengambil data dari list yang bernama Provinsi.

#4 Buat Dropdown List Tingkat 2 (Kabupaten)

Kali ini kita akan menggunakan bantuan Fungsi IF untuk memfilter 2 buah list kabupaten (Jambi dan Jakarta).

Penting! Jika Anda belum tahu cara kerja Rumus IF, silahkan kunjungi Panduan Fungsi IF Excel M Jurnal.

Untuk membuat dropdown list kedua, silahkan ikuti tahap-tahap berikut:

1. Pertama, ulangi tahap 1 – 3 pada Cara membuat dropdown list pertama di atas. Note: sesuaikan cell tempat Anda menerapkan dropdown list kedua. Dalam contoh ini, cell B2
2. Kedua, pada kolom Source, gunakan fungsi IF berikut: =IF(B1=”Jambi”,Jambi,IF(B1=”Jakarta”,Jakarta,”Pilih Provinsi”)) Note: Ketik manual sesuai data Anda.
3. Ketiga, klik Ok untuk melanjutkan. Berikut hasilnya:

Penting! Aplikasi Excel Saya menggunakan tanda koma (,) sebagai operator pemisah rumus. Aplikasi Excel Anda belum tentu demikian. Silahkan cek di Panduan Mengubah Koma menjadi Titik Excel.

Pada gambar di atas, Saya pilih Jambi pada Dropdown list pertama (Cell B1). Sehingga pada Dropdown list kedua (Cell B2), hanya tersedia list Kota Jambi dan Kota Sungai Penuh.

Begitu juga jika pada Dropdown list pertama Anda memilih Jakarta, maka sudah pasti Dropdown list kedua hanya tersedia list Jakarta Utara, Jakarta Pusat, dan Jakarta Selatan seperti gambar berikut:

Kenapa bisa demikian ? sedikit Saya jelaskan makna dari Rumus IF yang kita gunakan:

Tulis Rumus / Fungsi IF:
=IF(B1=”Jambi”,Jambi,IF(B1=”Jakarta”,Jakarta,“Pilih Provinsi”)

Artinya:
JikaCell B1 berisi Jambi maka Gunakan List Jambi namun JikaCell B1 berisi Jakarta maka Gunakan List Jakarta Selain dari itu Pilih Provinsi

Setelah Anda membuat Named Range atau Tabel Bernama, maka ketika Anda memasukkan Nama Range / Tabel tersebut ke dalam Rumus / Fungsi, Excel akan menggunakan Range / Tabel tersebut sebagai referensi.

Misalnya seperti argument =IF(B1=”Jambi”,Jambi. Jika cell B1 berisi Jambi, maka Excel akan menggunakan data pada List Jambi.

Begitu juga dengan argument =IF(B1=”Jakarta”,Jakarta. Jika cell B1 berisi Jakarta, maka Excel akan menggunakan data pada list Jakarta.

#5 Buat Dropdown List Tingkat 3 (Kecamatan)

Caranya hampir sama dengan membuat Dropdown list tingkat kedua. Perbedaannya Anda akan menggunakan Rumus IF bertumpuk (bertingkat) yang lebih banyak.

Seberapa banyak ? Coba lihat list tingkat dua yaitu Jambi dan Jakarta. Ada berapa banyak isi nya ? 5 bukan ? Yiatu Kota Jambi, Kota Sungai Penuh, Jakarta Utara, Jakarta Pusat, dan Jakarta Selatan.

Oleh karena itu, Anda akan menggunakan 5 Fungsi IF bertumpuk agar Excel bisa memfilter list sesuai dengan List yang terpilih pada Dropdown list kedua.

Saya menggunakan Fungsi IF berikut:

=IF(B2=”Kota Jambi”,Kota_Jambi,IF(B2=”Kota Sungai Penuh”,Kota_Sungai_Penuh,IF(B2=”Jakarta Utara”,Jakarta_Utara,IF(B2=”Jakarta Pusat”,Jakarta_Pusat,IF(B2=”Jakarta Selatan”,Jakarta_Selatan,”Pilih Kab/Kota”)))))

Hasilnya Excel akan memfilter List jika Cell B2 memenuhi kriteria seperti gambar berikut:

Namun, menggunakan Rumus IF untuk memfilter dropdown list bertingkat hanya akan Efektif jika jumlah data untuk setiap list tidak banyak.

Seperti contoh ini, Saya hanya menggunakan 2 list Provinsi serta list Kabupaten dan Kecamatan masing-masing Provinsi yang tidak lebih dari 5.

Bayangkan jika Anda memiliki list data yang sangat banyak, misalnya List Seluruh Provinsi se-Indonesia serta Seluruh kabupaten/Kota hingga Kecamatan di Indonesia. Apakah Fungsi IF masih efektif ?

Tentu tidak efektif. Namun jika ini yang Anda alami, Saya punya Solusinya. Silahkan gunakan Fungsi INDIRECT.

Bonus: Dropdown List Bertingkat Dengan Fungsi INDIRECT

Sebelum Anda menggunakan Fungsi INDIRECT untuk Dropdown List bertingkat (dependent), silahkan pahami syarat utama berikut:

Named Range / Tabel Bernama untuk Dropdown list tingkat 2 harus sama dengan list data yang terdapat pada dropdown tingkat 1.

Sedangkan Named Range / Tabel Bernama untuk Dropdown List tingkat 3 harus sama dengan list data yang terdapat pada dropdown tingkat 2.

Jadi, Perhatikan Named Range Anda

Ilustrasinya begini… Perhatikan gambar berikut:

Range A2:A3 untuk Dropdown list tingkat 1 Saya beri Named Range: Provinsi. List ini berisi: Jambi dan Jakarta. Ingat isinya.

Selanjutnya untuk list tingkat 2. Dalam contoh ini, Range A7:A8 akan digunakan jika Dropdown list tingkat 1 terpilih: Jambi. Jadi, berikan Named Range Jambi untuk list ini.

Sedangkan Range C7:C9 akan digunakan jika Dropdown list tingkat 1 terpilih: Jakarta. Jadi berikan Named Range Jakarta untuk list ini.

Pahami baik-baik ya… Named Range tingkat 2 harus sama dengan list data yang terdapat pada tingkat 1.

Selanjutnya list tingkat 3 akan melanjutkan data list tingkat 2. Konsepnya sama saja, namun ada perbedaan penting, Perhatikan gambar:

Lihat isi pada list tingkat 2. Misalnya untuk List Jambi berisi Kota Jambi dan Kota Sungai Penuh. Untuk List Jakarta berisi Jakarta Utara, Jakarta Pusat, dan Jakarta Selatan

Semua list terdiri lebih dari 1 kata, bahkan ada yang 3 kata yaitu Kota Sungai Penuh.

Apa masalahnya ? Anda tidak bisa membuat Named Range lebih dari 1 kata yang menggunakan spasi. Jika Named Range lebih dari 1 kata, maka Anda harus mengubah spasi menjadi underscore (_).

Sementara itu, apa Anda ingat Syarat Utama Fungsi INDIRECT untuk Dropdown List bertingkat yang Saya sebutkan sebelumnya ?

Named Range List tingkat 2 = data list tingkat 1.

Named Range List tingkat 3 = data list tingkat 2 dan seterusnya.

Nah disinilah kekeliruan sering terjadi sebagai penyebab kenapa Dropdown List Excel tidak muncul.

Sebagai Solusinya…

Upayakan ubah semua list data menjadi 1 kata saja agar semua Named Range bisa menggunakan 1 kata.

Namun, jika tidak memungkinkan. Silahkan gunakan list lebih dari 1 kata. Kemudian buat Named Range yang lebih dari 1 kata menggunakan underscore (ubah spasi antar kata menjadi underscore).

Kita akan menggunakan bantuan Fungsi SUBSTITUTE pada Rumus INDIRECT list tingkat ke 3.

Dalam contoh ini, Saya menggunakan Named Range list tingkat ke 3 yang sama dengan sebelumnya seperti gambar berikut:

Kemudian, silahkan buat Dropdown List Tingkat 1 seperti cara sebelumnya. Gunakan rumus =Provinsi pada kolom Source.

Dropdown List Tingkat 2 (Fungsi INDIRECT)

Perhatikan Gambar berikut untuk membuat Dropdown List tingkat kedua menggunakan Fungsi INDIRECT:

1. Pertama, buka Window Data Validation seperti cara sebelumnya.
2. Kedua, Ubah Validation Criteria – Allow menjadi List
3. Ketiga, pada kolom Source, masukkan rumus =INDIRECT(
4. Keempat, ketik nama Cell tempat Dropdown tingkat 1. (Cell B1)
5. Kelima, ketik tanda tutup kurung
6. Terakhir, klik Ok. berikut hasilnya:

Biar semakin paham, berikut makna Fungsi INDIRECT tersebut:

Tulis Rumus / Fungsi INDIRECT:
=INDIRECT(B1)

Artinya:
Ambil Referece Data Dari: Cell B1

Dengan kata lain, rumus INDIRECT akan mengambil reference data dari cell B1. Reference data tersebut bisa berupa cell, range, named range, ataupun table bernama.

Karena kita sudah membuat Named Range Semua List, maka jika Cell B1 berisi Jambi, maka Excel akan menggunakan Named Range Jambi untuk List tingkat 2. Begitu juga dengan Jakarta.

Dropdown List Tingkat 3 (Fungsi INDIRECT + SUBSTITUTE)

Karena Named Range list tingkat 2 (Kabupaten) terdiri dari 2 kata yang dipisah dengan underscore, maka kita akan menggunakan Fungsi INDIRECT dan SUBSTITUTE untuk Dropdown List tingkat 3. Perhatikan langkah-langkah berikut:

1. Pertama, ulangi tahap 1 – 3 pada dropdown list tingkat 2 INDIRECT diatas
2. Kedua, masukkan Fungsi SUBSTITUTE berikut: SUBSTITURE(
3. Ketiga, ketik nama Cell tempat Dropdown tingkat 2. (Cell B2)
4. Keempat, ketik Operator pemisah rumus ( , atau ; )
5. Kelima, ketik ” “,”_”)) atau ” “;”_”)) untuk mengganti underscore menjadi spasi.
6. Terakhir, klik Ok. berikut hasilnya:

Benar bukan ? Excel menggunakan Named Range yang tepat sebagai acuan Dropdown list tingkat 3.

Biar semakin paham, berikut Saya jelaskan makna Fungsi INDIRECT + SUBSTITUTE tersebut:

Tulis Rumus / Fungsi INDIRECT + SUBSTITUTE:
=INDIRECT(SUBSTITUTE(B2,” “,“_”))

Artinya:
Ambil Reference Data Dari: Terlebih Dahulu Ubah Isi Cell B2 Menjadi Spasi Untuk Setiap Underscore (_)

Dengan kata lain, Fungsi SUBSTITUTE akan mengubah setiap Underscore (_) menjadi Spasi. Kemudian, Fungsi INDIRECT akan mengambil Reference Data berdasarkan isi Pada Cell B2.

Dalam contoh ini (lihat gambar), Cell B2 berisi Kota Jambi. Sementara Named Range List tersebut= Kota_Jambi. Kemudian, Fungsi SUBSTITUTE akan mengubah Underscore (_) menjadi spasi.

Sehingga, Fungsi INDIRECT akan menganggap Named Range Kota_Jambi = Kota Jambi.

Pembuktian:

Jika Anda masih penasaran apakah fungsi INDIRECT hanya menggunakan Named Range yang sama dengan list data tingkat diatas nya, coba ubah source untuk Dropdown list tingkat 3 menjadi =INDIRECT(B2)

Pasti Dropdown list tingkat 3 (Kecamatan) pada cell B3 tidak akan muncul.

Saya yakin, banyak keadaan lain yang tidak serupa dengan contoh pada panduan ini. Jika Anda tidak menemukan solusi dari Panduan ini, silahkan diskusikan pada Forum Excel M Jurnal. Saya akan dengan senang hati membantu Anda.

Pintasan Panduan Data Validation Excel

1. Data Validations: Untuk mengisi Cell dengan pilihan khusus (Contoh: Cell B2 hanya bisa diisi dengan angka 1, 2, 3, atau 4).
2. Function + Data Validation: 4 Contoh cara menggunakan Rumus / Fungsi pada Data Validation Excel
3. Drop-Down List: Membuat Data Validations menggunakan Drop-Down List
4. Dependent Drop-Down Lists: (Anda Disini).
5. Kg to lbs: Contoh Validasi untuk mengkonversi satuan Kg ke pon

×

"Hidup ini singkat - bermimpilah yang besar dan wujudkan impianmu di tahun 2022 sebaik- baiknya!"