Sejak menu drop-down Pengesahan Data ditambahkan ke Excel pada tahun 1997, orang telah berusaha mencari cara untuk menukar menu drop-down kedua berdasarkan pilihan pada drop-down pertama.
Contohnya, jika anda memilih Buah di A2, menu drop-down di A4 akan menawarkan Apple, Banana, Cherry. Tetapi jika anda memilih Herba dari A2, senarai di A4 akan menawarkan Anise, Basil, Cinnamon. Terdapat banyak penyelesaian selama bertahun-tahun. Saya telah membahasnya sekurang-kurangnya dua kali dalam Podcast:
- Kaedah klasik menggunakan banyak julat bernama seperti yang ditunjukkan dalam episod 383.
- Kaedah lain menggunakan formula OFFSET dalam Episode 1606.
Dengan pelancaran formula Dynamic Array baru dalam Public Preview, fungsi FILTER baru akan memberi kita cara lain untuk melakukan Dependent Validation.
Katakan bahawa ini adalah pangkalan data produk anda:
Gunakan formula =SORT(UNIQUE(B4:B23))dalam D4 untuk mendapatkan senarai klasifikasi yang unik. Ini adalah jenis formula baru. Satu formula dalam D4 mengembalikan banyak jawapan yang akan tumpah ke banyak sel. Untuk merujuk kepada Spiller Range, anda akan menggunakan =D4#bukan =D4.
Pilih sel untuk menahan menu Pengesahan Data. Pilih Alt + DL untuk membuka Pengesahan Data. Tukar Izinkan ke "Senarai". Nyatakan =D4#sebagai sumber senarai. Perhatikan bahawa Hashtag (#) adalah Spiller - ini bermaksud bahawa anda merujuk kepada keseluruhan Spiller Range.
Rancangannya ialah seseorang akan memilih klasifikasi dari menu lungsur pertama. Kemudian, formula =FILTER(A4:A23,B4:B23=H3,"Choose Class First")di E4 akan mengembalikan semua produk dalam kategori tersebut. Perhatikan bahawa penggunaan "Pilih Kelas Pertama" sebagai argumen ketiga pilihan. Ini akan mengelakkan # NILAI! ralat daripada muncul.
Mungkin ada jumlah item yang berbeza dalam senarai bergantung pada kategori yang dipilih. Menetapkan Pengesahan Data yang ditujukan =E4#akan berkembang atau berkontraksi dengan panjang senarai.
Tonton Video
Transkrip Video
Belajar Excel Dari, Podcast Episode 2248: Pengesahan Bergantung Menggunakan Susunan.
Baiklah, hei. Perkara ini telah ditangani dua kali sebelumnya di podcast, bagaimana melakukan pengesahan bergantung, dan apa yang menjadi pengesahan bergantung adalah anda boleh memilih, pertama, kategori dan kemudian, sebagai tindak balas, drop-down kedua akan berubah menjadi item dari kategori itu, dan, sebelumnya, ini rumit, dan dengan susunan dinamik baru yang diumumkan pada bulan September 2018… dan ini akan dilancarkan, jadi anda mesti mempunyai Office 365. Sekarang 10 Oktober, saya telah mendengar bahawa mereka menggunakan sekitar 50% orang dalam Office, jadi mereka melancarkannya dengan sangat perlahan. Mungkin akan melalui separuh pertama 2019 sebelum anda mendapatkannya, tetapi ini akan membolehkan kami melakukan pengesahan bergantung secara lebih mudah.
Jadi, saya ada dua formula di sini. Formula pertama adalah UNIK dari semua klasifikasi dan saya menghantarnya ke perintah SORT. Jadi, itu memberi saya 1 formula yang mengembalikan 5 keputusan dan yang tinggal di D4. Jadi, di sini, di mana saya ingin memilih pengesahan data, saya akan (DL - 1:09) … SUMBER akan menjadi = D4 #. Yang # - kami namakan sebagai spiller - pastikan ia mengembalikan semua hasil dari D4. Jadi, jika saya menambah kategori baru di sini dan ini bertambah, D4 # akan mengambil jumlah tambahan itu, ok? (= SORT (UNIK (B4: B23)))
Oleh itu, pengesahan pertama agak mudah, tetapi sekarang kita tahu bahawa kita telah memilih CITRUS - ini akan menjadi lebih sukar - saya ingin menapis senarai di lajur A di mana item di lajur B sama dengan item yang dipilih , baiklah? Jadi, pertama-tama kita harus membiarkan mereka memilih sesuatu dan kemudian, setelah saya tahu itu CITRUS, kemudian beri saya LIME, ORANGE, dan TANGERINE, mereka akan memilih yang lain. BERRY. Lihat ini. Jurnal ilmiah mengatakan bahawa pisang adalah buah beri. Saya tidak setuju dengan itu. Tidak merasa seperti beri untuk saya tetapi jangan salahkan saya. Saya hanya, anda tahu, menggunakan Internet. BANANA, ELDERBERRY, dan RASPBERRY.
Sekarang, anda tahu, kerumitan dengan ini adalah seseorang pada mulanya akan datang ke sini tanpa memilih apa-apa, dan, dalam kes itu, kita mempunyai PILIH KELAS PERTAMA yang merupakan argumen ketiga yang mengatakan jika tidak ada yang dijumpai, baiklah? Jadi, anda tahu, dengan cara itu, jika kita mulai dalam senario ini, pilihannya adalah PILIH KELAS PERTAMA. Ideanya ialah mereka memilih KELAS, SAYURAN, kemas kini ini, dan kemudian barang-barang tersebut berasal dari senarai itu. PENGESAHAN DATA di sini, tentu saja, itu satu lagi spiller, = E4 # untuk membuatnya berfungsi, baiklah? Oleh itu, ini sangat bagus. (= FILTER (A4: A23, B4: B23 = H3, "Pilih Kelas Pertama"))
Lihat buku saya Excel Dynamic Arrays. Ini … ia akan percuma hingga akhir tahun 2018. Lihat pautan di sana dalam keterangan YouTube, bagaimana anda boleh memuat turunnya, untuk contoh ini ditambah 29 contoh lain tentang cara menggunakan item ini.
Baiklah, bungkus untuk hari ini. Susunan dinamik memberi kita cara lain untuk melakukan pengesahan bergantung. Sekiranya anda belum menggunakan Office 365 dan belum memilikinya, jangan ragu untuk kembali ke, saya rasa, video 1606 yang menunjukkan cara lama untuk melakukan ini.
Saya ingin mengucapkan terima kasih kerana berhenti. Kami akan berjumpa anda di lain kali untuk siaran net lain dari.
Muat turun Fail Excel
Untuk memuat turun fail excel: dependen-validation-using-arrays.xlsx
Untuk mengetahui lebih lanjut mengenai Dynamic Arrays, lihat Excel Dynamic Arrays Straight To The Point.
Pemikiran Excel Hari Ini
Saya telah meminta nasihat rakan Excel saya mengenai Excel. Pemikiran hari ini untuk merenungkan:
"Jangan sekali-kali memadam fail Excel tanpa menyandarkannya terlebih dahulu."
Mike Alexander
