Membuat Dropdown List Dinamis dengan Data Validation di Excel

oleh | 20 Nov 2023 | Belajar Excel, Excel Elementary, Excel Intermediate, Tips Excel | 0 Komentar

Membuat dropdown list dinamis adalah fitur yang memungkinkan pengguna Excel untuk memilih nilai dari daftar yang dapat berubah secara otomatis sesuai dengan kondisi atau kriteria tertentu. Fitur ini sangat berguna untuk menyederhanakan input data dan meningkatkan keakuratan dalam pekerjaan sehari-hari. Dalam artikel ini, kita akan membahas cara membuat dropdown list dinamis menggunakan Data Validation di Excel.

Langkah 1: Persiapkan Data

Sebelum kita membuat dropdown list, pertama-tama kita perlu menyiapkan data yang akan digunakan. Misalnya, kita akan membuat dropdown list untuk memilih nama desa berdasarkan kategori kecamatan. Buatlah dua kolom di lembar kerja Excel, satu untuk nama-nama desanya dan satu lagi untuk kategori kecamatan. Isi data nama-nama desa dan kategori kecamatan sesuai kebutuhan.

Sebagai contoh, berikut adalah tabel yang akan kita buat:

Langkah 2: Membuat Dropdown List untuk Kategori

Kita akan membuat dropdown list dinamis untuk kategori kecamatan terlebih dahulu. Dan karena kategori yang kita punya cuma dua maka kita buat dropdown list tanpa memakai rumus lagi. Caranya klik Data – Data Validation:

Langkah 3: Membuat Dropdown List Dinamis

  1. Pilih sel di mana Anda ingin menampilkan dropdown list (sebagai contoh, di sel C13).
  2. Pergi ke tab “Data” dan pilih “Data Validation.”
  3. Dalam jendela Data Validation, pilih “List” sebagai kriteria.
  4. Masukkan formula berikut sebagai sumber data: =OFFSET($B$2;MATCH(B13;$C$3:$C$9;0)-1;0;COUNTIF($C$3:$C$9;B13);1).
Penjelasan Rumus

Formula di Data Validation ini memiliki beberapa komponen yang perlu dijelaskan. Mari kita bahas satu per satu:

Baca Juga  Membuat Aplikasi Koreksi Pilihan Ganda di Excel Menggunakan Rumus Sederhana

Formula Data Validation:

=OFFSET($B$2;MATCH(B13;$C$3:$C$9;0)-1;0;COUNTIF($C$3:$C$9;B13);1)

Komponen Formula:

1. OFFSET:

  • OFFSET(reference; rows; cols; height; width) Fungsi OFFSET digunakan untuk menggeser atau menentukan suatu rentang sel berdasarkan sel referensi. Dalam konteks ini, kita menggunakan $B$2 sebagai sel referensi.

2. MATCH:

  • MATCH(lookup_value; lookup_array; match_type) Fungsi MATCH digunakan untuk mencari nilai dalam rentang sel tertentu dan mengembalikan posisinya. Dalam konteks ini, kita mencari nilai yang terdapat di sel B13 dalam rentang $C$3:$C$9.

3. COUNTIF:

  • COUNTIF(range, criteria) Fungsi COUNTIF digunakan untuk menghitung berapa kali suatu kriteria muncul dalam rentang sel. Dalam konteks ini, kita menghitung berapa kali nilai yang terdapat di sel B13 muncul dalam rentang $C$3:$C$9.

Detail Formula:

  • MATCH(B13;$C$3:$C$9;0)-1:
  • MATCH digunakan untuk mencari posisi nilai di sel B13 dalam rentang $C$3:$C$9 dengan pencarian eksak (match_type = 0).
  • -1 digunakan untuk mengkompensasi fakta bahwa kita ingin merujuk pada sel di atas sel yang ditemukan.
  • OFFSET($B$2;...:
  • $B$2 adalah sel referensi awal yang akan digunakan sebagai titik awal dari pergeseran.
  • COUNTIF($C$3:$C$9;B13):
  • COUNTIF digunakan untuk menghitung berapa kali nilai di sel B13 muncul dalam rentang $C$3:$C$9.
  • Hasil ini digunakan sebagai tinggi (height) dalam fungsi OFFSET.
  • 1):
  • Angka 1 ini digunakan sebagai lebar (width) dalam fungsi OFFSET. Karena kita hanya ingin merujuk pada satu kolom.

Langkah 4: Uji Dropdown List

Sekarang, cobalah pilih kategori pada sel B13. Daftar dropdown list pada sel C13 akan otomatis berubah sesuai dengan kategori yang Anda pilih.

Kesimpulan

Dengan mengikuti langkah-langkah di atas, Anda telah berhasil membuat dropdown list dinamis menggunakan Data Validation di Excel. Fitur ini akan membantu mempercepat dan menyederhanakan proses penginputan data, meningkatkan akurasi, dan memudahkan analisis data dalam pekerjaan sehari-hari Anda. Selamat mencoba!

Baca Juga  Panduan Lengkap Membuat Tabel Angsuran Kredit di Excel

Bagi yang membutuhkan file Membuat Dropdown List Dinamis dengan Data Validation di Excel ini untuk latihan, file Excel bisa didownload melalui tautan di bawah ini:

0 Komentar

Trackbacks/Pingbacks

  1. Membuat Rekap Angsuran dan Perhitungan Sisa Pinjaman di Excel - […] diterima. Dan untuk menghindari kesalahan penulisan nama, di kolom Nama Nasabah sebaiknya kita buat dropdown list menggunakan Data Validation.…
  2. Cara Jitu dan Praktis Mengelompokkan Data Berdasarkan Kriteria di Excel - Depot Excel - […] Data validasi memberikan kita kemampuan untuk membatasi jenis input yang dapat dimasukkan ke dalam sel. Dengan data validasi, kita…
  3. Membuat Dropdown List Dinamis di Excel Berdasarkan Kategori dengan Data Validation - Depot Excel - […] memudahkan pemahaman alurnya, sebelum kita melangkah ke pembuatan dropdown listnya terlebih dahulu kita filter data “Distributor” yang masih banyak…
  4. Cara Hebat untuk Analisis Penjualan Produk di Excel dengan Formula Array - Depot Excel - […] formula array di range G5:J13, untuk memudahkan pencarian kategori produk di sel J2 kita gunakan dropdown list data validation…
  5. Cara Cepat Merekap Data Penjualan dengan Kriteria Mendatar dan Menurun di Excel - Depot Excel - […] dari itu untuk menghindari typo kita bisa menggunakan dropdown list dari Data Validation. Langkahnya klik di sel E3 kemudian…
  6. Cara Membuat Rekap Angsuran Nasabah dan Perhitungan Sisa Pinjaman di Excel - Depot Excel - […] diterima. Dan untuk menghindari kesalahan penulisan nama, di kolom Nama Nasabah sebaiknya kita buat dropdown list menggunakan Data Validation.…

Kirim Komentar

Alamat email Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *

Join Our Newsletter