Loncat ke daftar isi utama

Buat Daftar Drop-Down Ketergantungan Dinamis di Excel (Langkah demi Langkah)

Di sini, di tutorial ini, kami akan memperkenalkan langkah demi langkah cara membuat daftar drop-down dependen yang menampilkan pilihan tergantung pada nilai yang dipilih di daftar dropdown pertama. Dengan kata lain, kita akan membuat daftar validasi data excel berdasarkan nilai dari daftar yang lain.

Buat daftar drop-down dependen yang dinamis
10 detik untuk membuat daftar drop-down dependen dengan alat praktis
Buat daftar drop-down dependen dinamis di Excel 2021 atau Excel 365
Beberapa pertanyaan yang mungkin Anda tanyakan tentang tutorial ini

daftar drop-down tergantung dokumen 1 1 1

Unduh gratis file sampel sampel doc


Video: Buat daftar drop-down yang bergantung pada Excel

 


Buat daftar drop-down dependen yang dinamis

 

Langkah 1: Ketik Entri Untuk Daftar Drop-Down

1. Pertama, ketikkan entri yang ingin Anda tampilkan di daftar drop-down, setiap daftar dalam kolom secara terpisah.

Melihat bahwa item di kolom pertama (Produk) akan menjadi nama Excel untuk daftar dependen nanti. Misalnya, di sini Buah dan Sayur akan menjadi Nama untuk kolom B2:B5 dan C2:C6 secara terpisah.

Lihat tangkapan layar:

daftar drop-down tergantung dokumen 1 2

2. Kemudian buat tabel untuk setiap daftar data.

Pilih rentang kolom A1:A3, klik Menyisipkan > tabel, lalu di dialog Buat Tabel, centang Tabel saya memiliki header kotak centang. Klik OK.

daftar drop-down tergantung dokumen 1 3

Kemudian ulangi langkah ini untuk membuat tabel untuk dua daftar lainnya.

Anda dapat melihat semua tabel dan referensi ke rentang di Name Manager (tekan Ctrl + F3 untuk membukanya).

daftar drop-down tergantung dokumen 1 4

Langkah 2: Buat Nama Rentang

Pada langkah ini, Anda perlu membuat nama untuk daftar utama dan setiap daftar dependen.

1. Pilih item yang muncul di daftar utama (A2: A3).

2. Lalu pergi ke Kotak nama yang disamping Bilah formula.

3. Ketik nama di dalamnya, di sini beri nama sebagai Produk.

4. tekan Enter kunci untuk menyelesaikan.

daftar drop-down tergantung dokumen 1 5

Kemudian ulangi langkah-langkah di atas untuk membuat Nama secara terpisah untuk setiap daftar dependen.

Di sini beri nama kolom kedua (B2:B5) sebagai Buah, dan kolom ketiga (C2:C6) sebagai Sayuran.

daftar drop-down tergantung dokumen 1 15

daftar drop-down tergantung dokumen 1 6

Anda dapat melihat semua nama rentang di Pengelola Nama (tekan Ctrl + F3 untuk membukanya).

daftar drop-down tergantung dokumen 1 7

Langkah 3: Tambahkan Daftar Drop Down Utama

Selanjutnya, tambahkan daftar drop-down utama (Produk), yang merupakan daftar drop-down validasi data normal, bukan daftar drop-down dependen.

1. Pertama, buat tabel.

Pilih sel (E1), dan ketik tajuk kolom pertama (Produk), dan berpindah ke sel kolom berikutnya (F1), ketik tajuk kolom kedua (Barang). Anda akan menambahkan daftar drop-down ke tabel ini.

Kemudian pilih dua tajuk ini (E1 dan F1), klik Menyisipkan tab, dan pilih tabel dalam grup Tabel.

Dalam dialog Buat Tabel, centang Tabel saya memiliki header kotak, dan klik OK.

daftar drop-down tergantung dokumen 1 8

2. Pilih sel E2 ke mana Anda ingin memasukkan daftar drop-down utama, klik Data tab dan pergi ke Data Alat grup untuk diklik Validasi Data > Validasi Data.

daftar drop-down tergantung dokumen 1 9

3. Dalam dialog Validasi Data,

  • Pilih Daftar dalam mengizinkan bagian,
  • Ketik rumus di bawah ini ke dalam sumber bar, Produk adalah Nama dari daftar utama,
  • Klik OK.
=Product

daftar drop-down tergantung dokumen 1 10

Anda dapat melihat daftar drop-down utama telah dibuat.

daftar drop-down tergantung dokumen 1 11

Langkah 4: Tambahkan Daftar Drop Down Ketergantungan

1. Pilih sel F2 ke mana Anda ingin menambahkan daftar drop-down dependen, klik Data tab, dan buka grup Alat Data untuk mengklik Validasi Data > Validasi Data.

2. Dalam dialog Validasi Data,

  • Pilih Daftar dalam mengizinkan bagian,
  • Ketik rumus di bawah ini ke dalam sumber bar, E2 adalah sel yang berisi daftar drop-down utama.
  • Klik OK.
=INDIRECT(SUBSTITUTE(E2," ","_"))

daftar drop-down tergantung dokumen 1 12

Jika E2 kosong (Anda tidak memilih salah satu item di daftar drop-down utama), Anda akan melihat pesan muncul seperti di bawah ini, klik Yes untuk melanjutkan.

daftar drop-down tergantung dokumen 1 13

Sekarang daftar drop-down dependen telah dibuat.

daftar drop-down tergantung dokumen 1 14

Langkah 5: Uji Daftar Drop-Down Ketergantungan.

1. Memilih Buah dalam daftar drop-down utama (E2), lalu buka daftar drop-down dependen (F2) untuk mengklik ikon panah, lihat apakah item buah ada dalam daftar, lalu pilih satu item dari daftar drop-down yang bergantung.

2. tekan Tab kunci untuk memulai baris baru di tabel entri data, pilih Sayuran, dan berpindah ke sel berikutnya di sebelah kanan, lihat apakah item sayuran ada dalam daftar, lalu pilih satu item dari daftar drop-down yang bergantung.

gif 1

Catatan:

10 detik untuk membuat daftar drop-down dependen dengan alat praktis

 

Kutools untuk Excel menyediakan alat yang ampuh untuk membuat daftar drop-down dependen menjadi lebih mudah dan lebih cepat, mari kita lihat:

kte gif 1

Sebelum mengikuti langkah-langkah di bawah ini, silakan klik untuk mengunduh Kutools for Excel untuk uji coba gratis 30 hari pertama.

Langkah 1: Ketik Entri Untuk Daftar Drop Down

Pertama, atur data Anda seperti screenshot di bawah ini:

doc kutools daftar drop-down dinamis 1

Langkah 2: Menerapkan alat Kutools

1. Pilih data yang telah Anda buat, klik Kutools tab, dan klik Daftar tarik-turun untuk menampilkan sub menu, klik Daftar Drop-down Dinamis.

doc kutools daftar drop-down dinamis 2

2. Dalam Daftar Drop-down Tanggungan

  • Periksalah Modus B yang cocok dengan mode data Anda,
  • Pilih jangkauan keluaran, kolom rentang keluaran harus sama dengan kolom rentang data,
  • Klik Ok.

doc kutools daftar drop-down dinamis 3

Sekarang daftar drop-down dependen telah dibuat.

doc kutools daftar drop-down dinamis 4

Tips:
  • Mode B mendukung untuk membuat daftar drop-down level ketiga atau lebih:
    daftar drop-down dinamis doc kutools 5 1
  • Jika data Anda diatur seperti yang ditunjukkan tangkapan layar di bawah ini, Anda perlu menggunakan Mode A, Mode A hanya mendukung untuk membuat daftar drop-down bergantung 2 tingkat.
    doc kutools daftar drop-down dinamis 6
  • Detail lebih lanjut tentang cara menggunakan Kutools untuk membuat daftar drop-down yang bergantung, silakan kunjungi tutorial ini .

Kutools untuk Excel

Uji coba gratis fitur lengkap 30 hari, tidak perlu kartu kredit.

Lebih dari 300 fitur dan fungsi canggih canggih untuk Excel.

Tidak membutuhkan keahlian khusus, menghemat waktu berjam-jam setiap hari.

Buat daftar drop-down dependen dinamis di Excel 2021 atau Excel 365

 

Jika Anda menggunakan Excel 2021 atau Excel 365, ada cara lain yang dapat dengan cepat membuat daftar drop-down dependen dinamis dengan menggunakan fungsi baru UNIK dan FILTER.

Misalkan data sumber Anda diatur seperti tangkapan layar yang ditampilkan, ikuti langkah-langkah di bawah ini untuk membuat daftar drop-down dinamis.

Langkah 1: Menggunakan rumus untuk mendapatkan item untuk daftar drop-down utama

Pilih sel, misalnya sel G3, dan gunakan fungsi UNIK dan FILTER untuk mengekstrak nilai unik dari Produk list yang akan menjadi sumber daftar drop-down utama, dan tekan Enter kunci.

=UNIQUE(FILTER(A3:A20, A3:A20<>""))
Note: Dengan produk dalam A3:A12, kami menambahkan 8 sel ekstra ke larik untuk memenuhi kemungkinan entri baru. Selain itu, kami menyematkan fungsi FILTER ke UNIK untuk mengekstraksi nilai unik tanpa kosong.

Langkah 2: Buat daftar drop-down utama

1. Pilih sel yang ingin Anda tempatkan daftar drop-down utama, misalnya sel D3klik Data tab, dan pergi ke Data Alat grup untuk diklik Validasi Data > Validasi Data.

2. Dalam dialog Validasi Data,

  • Pilih Daftar dalam mengizinkan bagian,
  • Ketik rumus di bawah ini ke dalam sumber bar,
  • Klik OK.
=$G$3#
Note: Ini disebut referensi rentang tumpahan, dan sintaks ini merujuk ke seluruh rentang terlepas dari berapa banyak yang diperluas atau dikontrak.

Sekarang daftar drop-down utama dibuat.

Langkah 3: Menggunakan rumus untuk mendapatkan item untuk daftar drop-down dependen

Pilih sel, misalnya sel H3, gunakan fungsi FILTER untuk memfilter item berdasarkan nilai dalam sel D3 (item yang dipilih dalam daftar drop-down utama), tekan Enter kunci.

=FILTER(B3:B20, A3:A20=D3)
Note: Jika ada yang kosong di daftar drop-down utama, rumus akan kembali ke nol.

Langkah 4: Buat daftar drop-down dependen

1. Pilih sel yang akan menempatkan daftar drop-down dependen, misalnya sel E3klik Data tab, dan pergi ke Data Alat grup untuk diklik Validasi Data > Validasi Data.

2. Dalam dialog Validasi Data,

  • Pilih Daftar dalam mengizinkan bagian,
  • Ketik rumus di bawah ini ke dalam sumber bar,
  • Klik OK.
=$H$3#
Note: Ini disebut referensi rentang tumpahan, dan sintaks ini merujuk ke seluruh rentang terlepas dari berapa banyak yang diperluas atau dikontrak.

Sekarang daftar drop-down dependen berhasil dibuat.

Saat Anda menambahkan item baru atau melakukan beberapa perubahan pada A3:A20, daftar drop-down akan diperbarui secara otomatis.

Tips:

Urutkan daftar drop-down menurut abjad

Jika Anda ingin mengatur item dalam daftar drop-down berdasarkan abjad, Anda dapat menggunakan rumus di bawah ini ke tabel persiapan.

Untuk dropdown utama (rumus di sel G3):

=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))

Untuk dropdown dependen (rumus di sel H3):

=SORT(FILTER(B3:B20, A3:A20=D3))

Sekarang kedua daftar drop-down diurutkan berdasarkan abjad A hingga Z.

doc dependen drop-down 365 8

Untuk mengurutkan berdasarkan abjad Z ke A, silakan gunakan rumus di bawah ini:

Untuk dropdown utama (rumus di sel G3):

=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)

Untuk dropdown dependen (rumus di sel H3):

=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)

Beberapa pertanyaan yang mungkin Anda tanyakan:

1. Mengapa menyisipkan tabel untuk setiap daftar data?

Memasukkan tabel untuk daftar data akan membantu Anda memperbarui daftar drop-down secara otomatis berdasarkan perubahan dalam daftar data. Misalnya, menambahkan 'Lainnya' di daftar data pertama, maka daftar drop down utama akan otomatis ditambahkan dengan 'Lainnya'.

pembaruan daftar tarik-turun yang bergantung pada dokumen

2. Mengapa menggunakan tabel untuk menempatkan daftar drop-down?

Saat Anda menekan tombol Tab untuk menambahkan baris baru ke tabel, daftar drop-down juga akan ditambahkan secara otomatis di baris baru.

3. Bagaimana cara kerja fungsi INDIRECT?

TIDAK LANGSUNG fungsi digunakan untuk mengubah string teks menjadi referensi yang valid.

4. Bagaimana rumus INDIRECT(SUBSTITUTE(E2&F2," ","")) bekerja?

Pertama, PENGGANTI fungsi mengganti teks dengan teks lain. Di sini digunakan untuk menghapus spasi dari gabungan nama (E2 dan F2). Kemudian TIDAK LANGSUNG fungsi mengubah string teks (konten gabungan oleh E2 dan F2) menjadi referensi yang valid.

Alat Produktivitas Kantor Terbaik

🤖 Kutools AI Ajudan: Merevolusi analisis data berdasarkan: Eksekusi Cerdas   |  Hasilkan Kode  |  Buat Rumus Khusus  |  Analisis Data dan Hasilkan Grafik  |  Aktifkan Fungsi Kutools...
Fitur Populer: Temukan, Sorot, atau Identifikasi Duplikat   |  Hapus Baris Kosong   |  Gabungkan Kolom atau Sel tanpa Kehilangan Data   |   Putaran tanpa Formula ...
Pencarian Super: VLookup Beberapa Kriteria    VLookup Nilai Berganda  |   VLookup di Beberapa Lembar   |   Pencarian Fuzzy ....
Daftar Drop-down Lanjutan: Buat Daftar Drop Down dengan Cepat   |  Daftar Drop Down yang Bergantung   |  Multi-pilih Drop Down List ....
Manajer Kolom: Tambahkan Jumlah Kolom Tertentu  |  Pindahkan Kolom  |  Alihkan Status Visibilitas Kolom Tersembunyi  |  Bandingkan Rentang & Kolom ...
Fitur Unggulan: Fokus Kisi   |  Tampilan Desain   |   Bar Formula Besar    Manajer Buku Kerja & Lembar   |  Perpustakaan Sumberdaya (Teks otomatis)   |  Pemetik tanggal   |  Gabungkan Lembar Kerja   |  Enkripsi/Dekripsi Sel    Kirim Email berdasarkan Daftar   |  Filter Super   |   Filter Khusus (filter tebal/miring/coret...) ...
15 Perangkat Teratas12 Teks Tools (Tambahkan Teks, Hapus Karakter, ...)   |   50 + Grafik jenis (Gantt Chart, ...)   |   40+ Praktis Rumus (Hitung usia berdasarkan ulang tahun, ...)   |   19 Insersi Tools (Masukkan Kode QR, Sisipkan Gambar dari Jalur, ...)   |   12 Konversi Tools (Angka ke Kata, Konversi Mata Uang, ...)   |   7 Gabungkan & Pisahkan Tools (Lanjutan Gabungkan Baris, Pisahkan Sel, ...)   |   ... dan banyak lagi

Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu.  Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...

Deskripsi Produk


Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah

  • Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
  • Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
  • Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations