Sejak menu drop-down Validasi Data ditambahkan ke Excel pada tahun 1997, orang-orang telah mencoba mencari cara agar menu drop-down kedua berubah berdasarkan pilihan di drop-down pertama.
Misalnya, jika Anda memilih Buah dalam A2, tarik-turun di A4 akan menawarkan Apple, Pisang, Ceri. Tetapi jika Anda memilih Herbal dari A2, daftar di A4 akan menawarkan Anise, Basil, Cinnamon. Ada banyak solusi selama bertahun-tahun. Saya telah membahasnya setidaknya dua kali di Podcast:
- Metode klasik menggunakan banyak rentang bernama seperti yang ditunjukkan di episode 383.
- Metode lain yang digunakan rumus OFFSET di Episode 1606.
Dengan rilis rumus Dynamic Array baru di Public Preview, fungsi FILTER baru akan memberi kita cara lain untuk melakukan Dependent Validation.
Katakanlah ini adalah database produk Anda:
Gunakan rumus =SORT(UNIQUE(B4:B23))di D4 untuk mendapatkan daftar unik klasifikasi. Ini adalah jenis formula baru. Satu rumus di D4 mengembalikan banyak jawaban yang akan tersebar ke banyak sel. Untuk merujuk pada Spiller Range, Anda akan menggunakan =D4#bukan =D4.
Pilih sel untuk menahan menu Validasi Data. Pilih Alt + DL untuk membuka Validasi Data. Ubah Izinkan ke "Daftar". Tentukan =D4#sebagai sumber daftar. Perhatikan bahwa Hashtag (#) adalah Spiller - itu berarti Anda merujuk ke seluruh Spiller Range.
Rencananya seseorang akan memilih klasifikasi dari menu drop-down pertama. Kemudian, rumus =FILTER(A4:A23,B4:B23=H3,"Choose Class First")di E4 akan mengembalikan semua produk dalam kategori tersebut. Perhatikan bahwa penggunaan "Pilih Kelas Pertama" sebagai argumen opsional ketiga. Ini akan mencegah kesalahan #VALUE! kesalahan muncul.
Mungkin ada sejumlah item berbeda dalam daftar tergantung pada kategori yang dipilih. Menyiapkan Validasi Data yang ditunjuk =E4#akan memperluas atau menyusut dengan panjang daftar.
Menonton video
Transkrip Video
Pelajari Excel Dari, Podcast Episode 2248: Validasi Bergantung Menggunakan Array.
Baiklah. Ini telah dibahas dua kali sebelumnya di podcast, bagaimana melakukan validasi dependen, dan validasi dependen apa Anda bisa memilih, pertama, kategori dan kemudian, sebagai tanggapan, untuk itu, drop-down kedua akan berubah menjadi hanya item dari kategori itu, dan, sebelumnya, ini rumit, dan dengan larik dinamis baru yang diumumkan pada September 2018… dan ini diluncurkan, jadi Anda harus memiliki Office 365. Saat ini 10 Oktober, saya pernah mendengarnya bahwa mereka termasuk dalam sekitar 50% orang dalam Office, jadi mereka meluncurkannya dengan sangat lambat. Mungkin akan melewati paruh pertama tahun 2019 sebelum Anda mendapatkannya, tetapi ini akan memungkinkan kami melakukan validasi dependen dengan cara yang jauh lebih mudah.
Jadi, saya punya dua rumus di sini. Rumus pertama adalah UNIK dari semua klasifikasi dan saya mengirimkannya ke perintah SORT. Jadi, itu memberi saya 1 rumus mengembalikan 5 hasil dan itu hidup di D4. Jadi, di sini, di mana saya ingin memilih validasi data, saya akan (DL - 1:09)… SUMBER-nya akan menjadi = D4 #. # Itu - kami menyebutnya spiller - pastikan bahwa ia mengembalikan semua hasil dari D4. Jadi, jika saya akan menambahkan kategori baru di sini dan ini berkembang, D4 # akan mengambil jumlah ekstra itu, oke? (= SORT (UNIK (B4: B23)))
Jadi, validasi pertama itu cukup sederhana, tetapi sekarang kita tahu bahwa kita telah memilih CITRUS - ini akan menjadi lebih sulit - saya ingin memfilter daftar di kolom A di mana item di kolom B sama dengan item yang dipilih , baik? Jadi, pertama-tama kita harus membiarkan mereka memilih sesuatu dan kemudian, setelah saya tahu itu CITRUS, lalu beri saya KAPUR, JERUK, dan TANGERIN, mereka akan memilih yang lain. BERRY. Lihat ini. Jurnal ilmiah mengatakan bahwa pisang adalah buah beri. Saya tidak setuju dengan itu. Tidak terasa seperti berry bagiku tapi jangan salahkan aku. Saya hanya, Anda tahu, menggunakan Internet. BANANA, ELDERBERRY, dan RASPBERRY.
Sekarang, Anda tahu, kerumitan dengan ini adalah seseorang awalnya akan datang ke sini tanpa memilih apa pun, dan, jadi dalam hal ini, kami memiliki PILIH KELAS PERTAMA yang merupakan argumen ketiga yang mengatakan jika tidak ada yang ditemukan, oke? Jadi, Anda tahu, dengan begitu, jika kita mulai dalam skenario ini, pilihannya adalah PILIH KELAS PERTAMA. Idenya adalah mereka memilih KELAS, SAYURAN, pembaruan ini, dan kemudian item tersebut berasal dari daftar itu. VALIDASI DATA di sini, tentu saja, itu spiller lain, = E4 # untuk membuatnya berfungsi, oke? Jadi, ini keren. (= FILTER (A4: A23, B4: B23 = H3, ”Pilih Kelas Pertama”))
Lihat buku saya Excel Dynamic Arrays. Ini… ini akan menjadi gratis hingga akhir 2018. Periksa tautan di bawah sana di deskripsi YouTube, bagaimana Anda dapat mengunduhnya, untuk contoh ini ditambah 29 contoh lain tentang bagaimana menggunakan barang-barang ini.
Baiklah, selesaikan untuk hari ini. Array dinamis memberi kita cara lain untuk melakukan validasi dependen. Jika Anda tidak menggunakan Office 365 dan Anda belum memilikinya, silakan kembali ke, saya kira, video 1606 yang memperlihatkan cara lama untuk melakukan ini.
Saya ingin mengucapkan terima kasih telah mampir. Sampai jumpa di lain waktu untuk netcast lain dari.
Unduh File Excel
Untuk mengunduh file excel: dependen-validation-using-arrays.xlsx
Untuk mempelajari selengkapnya tentang Dynamic Arrays, lihat Excel Dynamic Arrays Straight To The Point.
Pemikiran Excel Hari Ini
Saya telah meminta saran dari teman-teman Master Excel saya tentang Excel. Hari ini pemikiran untuk direnungkan:
"Jangan pernah menghapus file Excel tanpa mencadangkannya terlebih dahulu."
Mike Alexander
