Cara Memautkan Senarai Jatuh Turun dalam Excel

Adakah anda ingin belajar cara memautkan senarai juntai bawah dalam Excel ? Senarai juntai bawah Excel ialah ciri yang berguna apabila anda mencipta borang kemasukan data atau papan pemuka Excel.

Anda memaparkan senarai item sebagai menu lungsur turun dalam sel, dan pengguna boleh membuat pilihan daripada menu lungsur turun. Ini mungkin berguna apabila anda mempunyai senarai nama, produk atau wilayah yang sering anda perlu masukkan ke dalam set sel.

Contoh senarai juntai bawah dalam Excel

Berikut ialah contoh senarai lungsur turun Excel:

Di sini anda boleh membaca tentang: Cara Menyalin Helaian Excel ke Buku Kerja Lain – Tutorial

Dalam contoh yang ditunjukkan dalam imej, elemen dalam A2: A6 telah digunakan untuk mencipta menu lungsur dalam C3. Kadangkala, walau bagaimanapun, anda mungkin mahu menggunakan lebih daripada satu senarai juntai bawah dalam Excel supaya item yang tersedia dalam senarai juntai bawah kedua bergantung pada pemilihan yang dibuat dalam senarai juntai bawah yang pertama.

Ini dipanggil senarai juntai bawah bergantung dalam Excel.

Di bawah ialah contoh perkara yang ingin kami jelaskan dengan senarai juntai bawah bergantung dalam Excel:

Anda boleh melihat bahawa pilihan dalam menu lungsur 2 bergantung pada pilihan yang dibuat dalam menu lungsur 1.

Jika anda memilih 'Buah-buahan' dalam menu lungsur turun 1, nama buah-buahan akan dipaparkan, tetapi jika anda memilih 'Sayur-sayuran' dalam menu lungsur turun 1, maka nama sayur-sayuran akan dipaparkan dalam menu lungsur turun 2. Ini dipanggil senarai lungsur turun bersyarat atau bergantung dalam Excel.

Buat senarai lungsur turun bergantung dalam Excel

Berikut ialah langkah-langkah untuk membuat senarai jatuh turun bergantung dalam Excel:

• langkah 1: Pilih sel di mana anda mahu senarai juntai bawah (utama) pertama.
• langkah 2: pergi ke Data -> Pengesahan Data. Ini akan membuka kotak dialog pengesahan data.

• langkah 3: Dalam kotak dialog pengesahan data, dalam tab konfigurasi, pilih pilihan senarai.

• langkah 4: Di kawasan luar bandar Source, menentukan julat yang mengandungi item untuk dipaparkan dalam senarai juntai bawah yang pertama.

• langkah 5: Klik menerima. Ini akan mencipta menu lungsur 1.

• langkah 6: Pilih keseluruhan set data (A1:B6 dalam contoh ini).

• langkah 7: Pergi ke Formula -> Nama yang ditentukan -> Buat daripada pilihan (atau anda boleh menggunakan pintasan papan kekunci Kawalan + Shift + F3).

• langkah 8: Dalam kotak dialog 'Buat nama daripada pilihan', semak pilihan baris atas dan nyahtanda semua yang lain. Melakukan ini menghasilkan 2 julat nama ('Buah-buahan' dan 'Sayur-sayuran'). Rangkaian Buah Dinamakan merujuk kepada semua buah-buahan dalam senarai dan julat Sayuran Dinamakan merujuk kepada semua sayur-sayuran dalam senarai.

• langkah 9: Klik menerima.
• langkah 10: Pilih sel di mana anda mahu senarai juntai bawah Bergantung/Bersyarat (E3 dalam contoh ini).
• langkah 11: Pergi ke Data -> Pengesahan Data.

• langkah 12: Dalam kotak dialog Pengesahan Data, di dalam tab Daripada konfigurasi, Pastikan bahawa senarai dipilih.

• langkah 13: Dalam Medan sumber, masukkan formula = TIDAK LANGSUNG (D3). Di sini, D3 ialah sel yang mengandungi menu lungsur utama.

• langkah 14: Klik menerima.

Kini, apabila anda membuat pilihan dalam lungsur 1, pilihan yang disenaraikan dalam lungsur 2 akan dikemas kini secara automatik.

Bagaimana ia berfungsi?

Bagaimanakah ini berfungsi? – Senarai juntai bawah bersyarat dalam Excel (dalam sel E3) merujuk kepada =INDIRECT(D3). Ini bermakna apabila anda memilih ' Buah-buahan ' dalam sel D3, senarai juntai bawah dalam E3 merujuk kepada julat yang dinamakan 'Buah-buahan' (melalui fungsi INDIRECT ) dan oleh itu menyenaraikan semua item dalam kategori tersebut.

• Nota Penting:jika kategori induk adalah lebih daripada satu perkataan (cth. 'Buah-buahan bermusim'sebaliknya'Buah-buahan'), maka anda mesti menggunakan formula = TIDAK LANGSUNG (GANTIAN (D3,”“,”_”)), bukannya fungsi TIDAK LANGSUNG mudah yang ditunjukkan di atas.

Sebabnya ialah Excel tidak membenarkan ruang dalam julat bernama. Oleh itu, apabila anda mencipta julat bernama menggunakan lebih daripada satu perkataan, Excel secara automatik memasukkan garis bawah antara perkataan.

Contohnya : apabila anda mencipta julat bernama dengan 'Buah-buahan Bermusim' , ia akan dipanggil Buah_Buah_Seasonal di bahagian belakang . Menggunakan fungsi SUBSTITUTE dalam fungsi INDIRECT memastikan ruang ditukar kepada garis bawah.

Tetapkan semula/kosongkan kandungan senarai jatuh turun bergantung secara automatik

Apabila anda telah membuat pilihan dan kemudian menukar dropdown induk, dropdown bergantung tidak akan berubah dan oleh itu akan menjadi entri yang salah.

• Sebagai contoh: Jika anda memilih 'Buah' suka kategori dan kemudian pilih Apple sebagai item, dan kemudian kembali dan tukar kategori kepada 'Sayur-sayuran', lungsur turun bergantung akan terus dipaparkan Apple sebagai unsur.

Anda boleh menggunakan VBA untuk memastikan bahawa kandungan senarai jatuh turun bergantung ditetapkan semula apabila senarai jatuh turun induk ditukar. Berikut ialah kod VBA untuk mengosongkan kandungan senarai jatuh turun bergantung:

Subsheet_Change Peribadi (Sasaran ByVal Sebagai Julat)

Sekiranya berlaku kesilapan, sambung semula seterusnya

Jika Sasaran.Lajur = 4 Kemudian

Jika Target.Validation.Type = 3 Kemudian

Application.EnableEvents = Palsu

Target.Offset(0, 1).ClearContents

Ia akan berakhir jika

Ia akan berakhir jika

exitHandler:

Application.EnableEvents = Benar

Keluar Sub

Akhir Sub

Bagaimana ia berfungsi?

Inilah cara untuk membuat kod ini berfungsi:

• langkah 1: Salin kod VBA.
• langkah 2: Dalam buku kerja Excel di mana anda mempunyai senarai juntai bawah bergantung, pergi ke Tab pembangun, dan dalam kumpulan 'Kod', Klik pada Visual Basic (anda juga boleh menggunakan pintasan papan kekunci – ALT + F11).

• langkah 3: Dalam tetingkap editor VB, di sebelah kiri dalam penjelajah projek, anda akan melihat semua nama lembaran kerja. Klik dua kali pada senarai juntai bawah.

• langkah 4: Tampal kod ke dalam tetingkap kod di sebelah kanan.

• langkah 5: Tutup editor VB.

Kini, setiap kali anda menukar senarai lungsur turun induk, kod VBA akan dicetuskan dan kandungan senarai juntai bawah bergantung akan dikosongkan (seperti yang ditunjukkan di bawah).

Jika anda bukan pakar VBA, anda juga boleh menggunakan helah pemformatan bersyarat mudah yang akan menyerlahkan sel apabila terdapat ketidakpadanan. Ini boleh membantu anda melihat dan membetulkan ketidakpadanan secara visual (seperti yang ditunjukkan di bawah).

Berikut ialah langkah untuk menyerlahkan percanggahan dalam senarai lungsur turun bergantung:

• langkah 1: Pilih sel yang mempunyai senarai juntai bawah bergantung.
• langkah 2: Pergi ke Laman Utama -> Pemformatan bersyarat -> Peraturan baharu.

• langkah 3: Dalam kotak dialog Peraturan baru format, Pilih 'Gunakan formula untuk menentukan sel mana format'.

• langkah 4: Dalam medan formula, masukkan formula berikut:=ESERROR(VLOOKUP(E3,INDEX($A$2:$B$6,,MATCH(D3,$A$1:$B$1)), 1,0))

• langkah 5: Tetapkan format.
• langkah 6: Klik OK.

Anda juga mungkin berminat untuk mengetahui tentang: Cara Mengumpulkan Jadual Pangsi mengikut Bulan dalam Excel

Formula ini menggunakan fungsi VLOOKUP untuk menyemak sama ada item dalam senarai juntai bawah bergantung adalah item dalam kategori induk. Jika tidak, formula akan mengembalikan ralat. Ini digunakan oleh fungsi ISERROR untuk mengembalikan TRUE, yang memberitahu pemformatan bersyarat untuk menyerlahkan sel.

Seperti yang anda lihat, ini adalah cara yang betul untuk memautkan senarai juntai bawah dalam Excel. Bila-bila masa yang boleh, gunakan tutorial latihan ringkas ini untuk mempelajari cara menggunakan ciri yang berguna ini. Kami harap ini dapat membantu.