Daftar drop down excel 365

Batasi pilihan dalam satu daftar drop-down Excel, berdasarkan nilai di sel lain. Contoh ini menggunakan larik dinamis dan menumpahkan untuk membuat daftar yang digunakan di drop down. Pilih nama kawasan, lalu pilih karyawan dari kawasan tersebut

CATATAN. Array dinamis tersedia dalam paket Microsoft Excel 365. Di versi Excel tanpa larik dinamis, lihat cara lain ini untuk menyiapkan daftar turun bawah yang bergantung

Daftar Drop Down yang Bergantungan

Video ini menunjukkan cara menyiapkan daftar drop-down dependen, berdasarkan larik dinamis yang memiliki item untuk setiap daftar. Pilih nama wilayah dari drop-down pertama, dan drop-down dependen hanya menampilkan karyawan yang bekerja di wilayah tersebut

Ada instruksi tertulis di bawah video, dan file sampel ada di bagian bawah halaman

Pengaturan Lembar Kerja

Di spreadsheet Entri Data, ada daftar dengan nama wilayah dan karyawan. Daftar ini diformat sebagai Tabel Excel. Jika nama ditambahkan atau dihapus, ukuran daftar akan disesuaikan secara otomatis

• Judul ada di sel A1 dan sel B1
• Nama wilayah dimulai di sel A2, dan nama karyawan dimulai di sel B2

Di kolom F dan kolom G, sel entri data disiapkan di bagian atas lembar, tempat Anda akan memilih wilayah, lalu Anda akan memilih karyawan dari wilayah tersebut

Pada tangkapan layar di bawah, hanya judul, warna isian, dan batas yang telah ditambahkan ke sel entri data

Setelah semuanya diatur, sel-sel tersebut akan memiliki daftar drop-down, seperti yang dapat Anda lihat di tangkapan layar animasi ini

Daftar Unik Dinamis

Kami ingin membuat daftar drop-down nama wilayah di sel F2. Untuk mendapatkan nama wilayah tersebut, ikuti langkah-langkah berikut untuk membuat daftar dinamis dari nama wilayah yang unik. Ini akan menjadi rentang sumber untuk daftar drop-down

• Di sel D6, ketik judul daftar -- Reg
• Di sel D7, ketik rumus ini untuk membuat larik dinamis dengan nama wilayah
• Tekan Enter, untuk melihat daftar nama wilayah -- Excel menggunakan Spilling untuk membuat daftar dinamis yang akan menyesuaikan ukurannya secara otomatis

CATATAN. Saat Anda membuat larik dinamis, usahakan untuk menyisakan cukup banyak baris kosong di bawah sel rumus, dan kosongkan kolom di sebelah kanan (jika perlu). Jika tidak ada cukup ruang, Excel akan memperlihatkan kesalahan #SPILL, bukan daftar

Sortir Daftar

Dalam beberapa daftar drop-down, item akan lebih mudah ditemukan jika diurutkan berdasarkan abjad. Ikuti langkah berikut untuk mengurutkan daftar nama wilayah

• Pilih sel D7, tempat rumus array dinamis dimasukkan
• Klik setelah tanda sama dengan, dan ketik. MENYORTIR(
• Klik setelah tanda kurung tutup, dan ketik tanda kurung tutup lainnya. )
• Rumus yang direvisi adalah. =SORT(UNIK(A2. A11))
• Tekan tombol Enter, dan daftar akan mengurutkan secara otomatis, A-Z

Lakukan Drop Down Pertama

Selanjutnya, ikuti langkah berikut untuk membuat daftar dropdown nama wilayah, di sel F2

• Pilih sel F2
• Pada Pita, klik tab Data
• Di grup Alat Data, klik tombol Validasi Data
• Di kotak dialog Validasi Data, buka tab Pengaturan
• Klik di kotak Izinkan
• Dari daftar drop-down Perbolehkan, pilih Daftar
• Dalam kotak Sumber, ketikkan tanda sama dengan
• Selanjutnya, klik sel D7, tempat rumus daftar wilayah dimasukkan
• Terakhir, ketik tanda angka -- #. Itu adalah operator rentang yang tumpah, yang memberi tahu Excel untuk menggunakan seluruh rentang array yang tumpah
• Rumus lengkap dalam kotak Sumber adalah. =$D$7#
• (Opsional) Buka tab Pesan Input dan masukkan tip input data tentang pemilihan nama Wilayah
• (Opsional) Buka tab Peringatan Kesalahan dan masukkan pesan kesalahan untuk data yang tidak valid, atau ubah pengaturan Tampilkan Peringatan Kesalahan
• Kemudian, klik OK, untuk menyelesaikan penyiapan validasi data

Uji Daftar Drop Down

Untuk menguji daftar drop down nama Wilayah

• Pilih sel F2
• Klik panah drop-down di kanan sel
• Pilih nama wilayah dari daftar drop-down

Buat Daftar Karyawan

Di sel G2, kami menginginkan daftar drop-down karyawan yang dinamis. Daripada menampilkan semua nama, kami hanya ingin melihat karyawan dari wilayah yang dipilih di sel F2

Untuk membuat daftar drop-down dependen ini, kita akan membuat rumus array dinamis lainnya, menggunakan fungsi FILTER

• Dalam argumen pertama, kami akan memberi tahu Excel di mana daftar sumbernya
• Dalam argumen kedua, kami akan memberi tahu Excel bahwa nama wilayah di kolom A harus cocok dengan nama wilayah yang dipilih di sel F2

Ikuti langkah-langkah ini untuk membuat rumus

• Di sel F6 dan F7, ketik 2 judul untuk daftar -- Reg dan Emp
• Di sel F7, ketik rumus ini untuk membuat larik dinamis dengan nama karyawan untuk wilayah yang dipilih
  • =FILTER(A2. B11,A2. A11=F2)
• Tekan Enter, untuk melihat daftar nama pegawai di wilayah yang dipilih. Excel menggunakan Spilling untuk membuat daftar dinamis yang ukurannya akan disesuaikan secara otomatis

Untuk menguji daftar dinamis, pilih nama wilayah yang berbeda di sel F2. Daftar nama karyawan berubah secara otomatis. Berikut daftar setelah wilayah Timur dipilih, dengan hanya menampilkan 2 nama karyawan

Urutkan Nama Karyawan

Sama seperti yang kita lakukan dengan daftar nama wilayah, kita akan menggunakan fungsi SORT dalam daftar ini, untuk memasukkan nama karyawan dalam urutan abjad. Karena daftar ini memiliki 2 kolom, kami akan mengetikkan 2 sebagai argumen kedua, untuk memberi tahu Excel agar mengurutkan berdasarkan kolom ke-2

Ikuti langkah-langkah ini untuk mengurutkan nama karyawan

• Pilih sel F7, tempat rumus array dinamis dimasukkan
• Klik setelah tanda sama dengan, dan ketik. MENYORTIR(
• Klik setelah tanda kurung tutup, dan ketik koma
• Ketik 2, untuk nomor indeks sortir, lalu ketik tanda kurung tutup. )
• Rumus yang direvisi adalah
  • =SORT(FILTER(A2. B11,A2. A11=F2),2)
• Tekan tombol Enter, dan nama karyawan diurutkan secara otomatis, A-Z

Daftar Drop Down yang Bergantungan

Selanjutnya, kita akan menggunakan validasi data untuk membuat daftar drop-down nama karyawan. Ini adalah daftar drop-down dependen -- isinya bergantung pada wilayah mana yang dipilih di sel F2

Karena nama karyawan ada dalam daftar 2 kolom, kami akan menggunakan fungsi INDEX untuk mengembalikan nama dari kolom ke-2. Dalam fungsi INDEX,

• Argumen pertama adalah rentang dinamis yang dimulai pada F7
• Argumen ke-2 (baris) akan dibiarkan kosong
• Argumen ke-3 (kolom) akan menjadi 2

Ikuti langkah-langkah ini untuk membuat daftar drop-down nama karyawan, di sel G2

• Pilih sel G2
• Di Pita, klik tab Data, lalu klik Validasi Data
• Di kotak dialog Validasi Data, klik tab Pengaturan
• Dari daftar drop-down Perbolehkan, pilih Daftar
• Di kotak Sumber, ketik. =INDEKS(
• Selanjutnya, klik sel F7, tempat rumus daftar karyawan wilayah dimasukkan
• Kemudian ketik operator rentang yang tumpah. #
  • Nama lain untuk # adalah tanda angka, hash, tanda pound, octothorpe
• Ketik 2 koma -- ,,
• Ketik 2 dan tanda kurung tutup -- 2)
• Rumus lengkap dalam kotak Sumber adalah. =INDEX($F$7#,,2)
• Terakhir, klik OK, untuk menyelesaikan penyiapan validasi data

Uji Daftar Drop Down

Untuk menguji daftar drop down nama karyawan

• Pilih sel G2
• Klik panah tarik-turun, dan pilih nama karyawan
  • Daftar karyawan di drop down harus sama dengan daftar di rentang dinamis

• Kosongkan sel F2 dan G2, lalu pilih wilayah yang berbeda
• Drop-down di sel G2 akan menampilkan nama yang berbeda, berdasarkan wilayah yang Anda pilih

Perbaiki Kesalahan

Saat sel F2 kosong, sel F2 menampilkan #CALC. error, karena tidak ada region untuk filter

Jika Anda lebih suka tidak melihat kesalahan itu, gunakan fungsi IFERROR untuk menampilkan sel kosong, atau menampilkan pesan

Di mana Validasi Data di Excel 365?

Bagaimana cara membuat daftar drop-down di Excel Online?

Bagaimana cara membuat daftar drop-down di Excel 2022?

Bagaimana cara memasukkan daftar drop-down di Excel?