Microsoft Excel menyediakan berbagai fitur yang memudahkan pengguna untuk mengelola dan menyusun data dengan efisien. Salah satu fitur yang sangat bermanfaat adalah membuat Dropdown List dinamis di Excel di dalam sel, dan dalam artikel ini, kita akan membahas cara membuat dropdown list dinamis di Excel menggunakan Data Validation berdasarkan kategori.
Langkah-langkah Membuat Dropdown List Dinamis di Excel
1. Organisasi Data
Mulailah dengan mengorganisasi data Anda. Pastikan data yang ingin Anda kategorikan terletak dalam kolom tertentu. Misalnya, kita akan menggunakan kolom F (untuk dropdown list “Kategori”) dan H (untuk dropdown list “Produk”) sebagai contoh. Berikut adalah contoh tabelnya:

2. Memfilter Nama Distributor
Untuk memudahkan pemahaman alurnya, sebelum kita melangkah ke pembuatan dropdown listnya terlebih dahulu kita filter data “Distributor” yang masih banyak data gandanya supaya ketika kita reffer untuk dropdown list tidak ada lagi data- kembarnya.
Disini kita akan menggunakan formula array. Untuk itu klik sel F4 kemudian ketik formula array berikut:
=IFERROR(INDEX($C$3:$C$13;MATCH(0;COUNTIF($F$3:F3;$C$3:$C$13);0));"")
Karena ini formula array maka akhiri penulisan rumus dengan menekan Ctrl + Shift dan Enter (tidak hanya enter). Kemudian copy dan paste formula tersebut ke sel di bawahnya. Dan berikut adalah hasilnya:
Cara Formula Bekerja:
Formula ini dirancang untuk diaplikasikan pada kolom data tertentu (contoh: C3 hingga C13). Di dalamnya, terdapat langkah-langkah kunci:
- INDEX($C$3:$C$13;…): Fungsi INDEX digunakan untuk menghasilkan nilai dari sel di dalam rentang C3:C13.
- MATCH(0;COUNTIF($F$3:F3;$C$3:$C$13);0): Fungsi MATCH digunakan untuk mencari posisi pertama dari nilai yang belum muncul di dalam rentang data yang telah diproses sebelumnya. COUNTIF digunakan untuk menghitung berapa kali nilai tersebut telah muncul. Dengan cara ini, kita dapat menemukan nilai unik yang belum diambil.
- IFERROR(…;””): Fungsi IFERROR digunakan untuk menangani kesalahan yang mungkin muncul selama proses. Jika tidak ada kesalahan (nilai unik ditemukan), INDEX akan memberikan nilai tersebut. Jika ada kesalahan (tidak ada nilai unik lagi), sel akan tetap kosong (“”).
Manfaat Formula Ini:
- Penghapusan Data Ganda: Formula ini secara otomatis memfilter dan memberikan nilai unik dari data yang diidentifikasi sebagai ganda, membantu mempertahankan kekonsistenan dan kebersihan data.
- Dinamis dan Otomatis: Dengan formula ini, Anda dapat mengaplikasikan filter data ganda secara otomatis tanpa perlu mengidentifikasi setiap duplikat secara manual.
- Skalabilitas: Formula ini dapat diterapkan pada rentang data yang lebih besar tanpa perlu modifikasi ekstensif, membuatnya skalabel untuk proyek-proyek dengan jumlah data yang beragam.
Dengan menggunakan formula array yang cerdas ini, Anda dapat dengan mudah memfilter data ganda dalam lembar kerja Excel Anda. Memaksimalkan fungsionalitas Excel seperti ini membantu memastikan keakuratan dan integritas data, memudahkan analisis dan pengambilan keputusan yang lebih baik.
3. Mengambil Data Produk Berdasarkan Distributornya
Dalam dunia pengelolaan data di Excel, sering kali kita dihadapkan pada kebutuhan untuk mengambil data berdasarkan kategorinya. Dan sekarang kita akan menggunakan cara efisien menggunakan formula array untuk mengekstrak data sekaligus di banyak kolom. Formula array yang bisa kita gunakan di range I4:I13 adalah:
{=IFERROR(INDEX($B$3:$B$13,SMALL(IF($C$3:$C$13=$F$2,ROW($1:$11)),ROW($1:$11))),"")}
Ingat ya, ini formula array yang ditulis sekaligus di banyak kolom, jadi cara pengaplikasiannya adalah blok dulu range I4:I13 kemudian di formula bar ketik formula di atas. Akhiri penulisan formula dengan menekan Ctrl + Shift dan Enter (tidak hanya enter). Dan berikut adalah hasilnya:
Formula ini memanfaatkan fungsi INDEX, SMALL, dan IFERROR untuk mencari dan menampilkan data yang sesuai dengan kategori yang telah ditentukan di sel F2. Mari kita kupas setiap bagian dari formula ini:
- INDEX($B$3:$B$13,…): Mengambil nilai dari kolom data yang ingin kita ekstrak, dalam contoh ini, dari kolom B.
- SMALL(IF($C$3:$C$13=$F$2,ROW($1:$11)),ROW($1:$11)): Mencari baris-baris di mana kategori (kolom C) sama dengan nilai yang terdapat di sel F2. Fungsi ROW digunakan untuk mendapatkan nomor baris yang sesuai. SMALL kemudian digunakan untuk mengurutkan baris-baris ini.
- IFERROR(…,””): Mengatasi potensi kesalahan yang mungkin muncul, memastikan bahwa formula tetap berjalan bahkan jika tidak ada nilai yang ditemukan.
Dengan menggunakan formula array ini di banyak kolom, Anda dapat dengan cepat dan efisien mengekstrak data berdasarkan kategori yang telah ditentukan, menjadikannya solusi yang efektif untuk pengelolaan data yang kompleks dalam proyek Excel Anda. Jangan lupa untuk bereksperimen dan menyesuaikan formula ini sesuai dengan kebutuhan spesifik proyek Anda!
4. Pengelompokan Data Menggunakan Dropdown List dengan OFFSET dan COUNTA
Ini adalah langkah penting untuk membuat dropdown list dinamis di Excel. Di sini kita akan membuat dropdown list untuk “Distributor”. Caranya klik sel F2 kemudian pergi ke tab “Data” >>Data Validation, dan setting dropdown listnya sebagai berikut:
Gunakan formula OFFSET dan COUNTA untuk mengelompokkan data yang telah Anda atur. Masukkan formula berikut di kolom Source:
=OFFSET($F$4;0;0;COUNTA($F$4:$F$13)-COUNTBLANK($F$4:$F$13);1)
Formula ini akan menghasilkan rentang dinamis yang mencakup data di dalam kolom F, dan ini akan otomatis disesuaikan dengan perubahan dalam jumlah data.
Dan di sel I2 gunakan langkah yang sama seperti di atas, untuk kemudian “Source”-nya isi dengan formula berikut:
=OFFSET($I$4;0;0;COUNTIF($C:$C;$F$2);1)
Mari kita kupas masing-masing bagian dari rumus ini:
1. OFFSET($I$4, 0, 0, COUNTIF($C:$C, $F$2), 1)
:
- $I$4: Merupakan referensi sel awal atau sel referensi yang menjadi dasar perhitungan. Dalam hal ini, dropdown list akan dimulai dari sel I4.
- 0, 0: Menunjukkan bahwa pergeseran (offset) dalam baris dan kolom adalah nol, sehingga dropdown list dimulai tepat dari sel I4.
- COUNTIF($C:$C, $F$2): Ini digunakan untuk menghitung jumlah sel dalam kolom C yang cocok dengan nilai yang terdapat di sel F2. Hasilnya digunakan sebagai parameter tinggi (height) dalam fungsi OFFSET. Dengan kata lain, ini menentukan berapa banyak item yang akan ada dalam dropdown list.
- 1: Menunjukkan bahwa dropdown list hanya akan memiliki satu kolom.
2. Penggunaan COUNTIF($C:$C, $F$2)
:
- COUNTIF digunakan untuk menghitung berapa kali nilai yang terdapat di sel F2 muncul dalam kolom C. Ini berguna untuk menentukan jumlah item yang akan muncul dalam dropdown list.
3. Hasilnya untuk Dropdown List:
- Sebagai hasilnya, dropdown list akan muncul di sel I4 dengan jumlah item yang sesuai dengan jumlah kali nilai di sel F2 muncul dalam kolom C. Dropdown list ini akan dinamis dan otomatis berubah sesuai dengan perubahan dalam kolom C.
Rumus membuat dropdown list dinamis di Excel ini sangat berguna ketika Anda ingin membuat dropdown list yang fleksibel dan terkait dengan data dalam kolom lain berdasarkan kondisi tertentu, seperti yang ditentukan oleh nilai di sel F2 dalam contoh ini.
5. Menguji Dropdown List
Sekarang, Anda akan melihat dropdown list yang muncul saat Anda memilih sel yang telah Anda tentukan. Dropdown list ini akan secara otomatis menyesuaikan diri dengan perubahan dalam kategori yang telah Anda tentukan.
Berikut adalah hasil akhirnya:
Kesimpulan
Menggunakan Data Validation dan formula OFFSET di Excel memungkinkan kita untuk membuat dropdown list dinamis yang sangat responsif berdasarkan kategori data. Dengan langkah-langkah sederhana ini, Anda dapat meningkatkan efisiensi kerja dan memudahkan pengelolaan data dalam proyek Excel Anda.
Semoga tutorial membuat dropdown list dinamis di Excel ini membantu Anda dalam membuat dropdown list dinamis di Excel. Jangan ragu untuk bereksperimen dengan formula ini dan menyesuaikannya sesuai dengan kebutuhan proyek Anda. Selamat mencoba!
Terakhir, untuk lebih jelasnya silahkan teman-teman download saja file Excel: Membuat Dropdown List Dinamis di Excel Berdasarkan Kategori dengan Data Validation dan pelajari pelan-pelan. Link download ada di bawah ini:
Atau juga jika ingin mempelajari langkah demi langkahnya secara visual, bisa kunjungi Youtube Channel Depot Excel. Di sana Anda akan melihat bagaimana rumusnya bekerja. Semoga bermanfaat!
0 Komentar
Trackbacks/Pingbacks