Loncat ke daftar isi utama
 

Daftar drop-down Excel: buat, edit, hapus, dan operasi lebih lanjut

Daftar drop-down mirip dengan kotak daftar yang memungkinkan pengguna untuk memilih satu nilai dari daftar pilihan. Tutorial ini akan mendemonstrasikan operasi dasar untuk daftar drop-down: membuat, mengedit, dan menghapus daftar drop-down di excel. Selain itu, tutorial ini menyediakan operasi lanjutan untuk daftar drop-down guna meningkatkan fungsionalitasnya guna menyelesaikan lebih banyak masalah Excel.

Daftar Isi: [ Bersembunyi ]

(Klik judul mana pun dalam daftar isi di bawah atau di sebelah kanan untuk menavigasi ke bab terkait.)

Buat daftar drop-down sederhana

Untuk menggunakan daftar drop-down, Anda perlu mempelajari cara membuatnya terlebih dahulu. Bagian ini menyediakan 6 cara untuk membantu Anda membuat daftar drop-down di Excel.

Buat daftar drop-down dari berbagai sel

Di sini menunjukkan langkah-langkah untuk membuat daftar drop-down dari rentang sel di Excel. Silakan lakukan sebagai berikut

1. Pilih rentang sel untuk menemukan daftar drop-down.

Tips: Anda dapat membuat daftar drop-down untuk beberapa sel yang tidak bersebelahan secara bersamaan dengan menahan Ctrl kunci saat memilih sel satu per satu.

2. klik Data > Validasi Data > Validasi Data.

3. Dalam Validasi Data kotak dialog, di bawah Settings tab, harap konfigurasikan sebagai berikut.

3.1) Di mengizinkan daftar drop-down, pilih daftar;
3.2) Di sumber kotak, pilih rentang sel yang nilai-nilai yang akan Anda tampilkan dalam daftar drop-down;
3.3) Klik OK .

Catatan:

1) Anda dapat mencentang atau menghapus centang Abaikan kosong kotak tergantung pada bagaimana Anda ingin menangani sel kosong dalam kisaran yang dipilih;
2) Pastikan file Dropdown dalam sel kotak dicentang. Jika kotak ini tidak dicentang, panah drop-down tidak akan muncul saat memilih sel.
3) Di sumber kotak, Anda dapat mengetikkan nilai yang dipisahkan koma secara manual seperti gambar di bawah ini.

Sekarang daftar drop-down dibuat. Saat mengklik sel daftar drop-down, panah akan ditampilkan di sebelahnya, klik panah untuk memperluas daftar, lalu Anda dapat memilih item darinya.

Buat daftar drop-down dinamis dari tabel

Anda dapat mengonversi rentang data Anda ke tabel Excel dan kemudian membuat daftar turun bawah dinamis berdasarkan rentang tabel.

1. Pilih kisaran data asli, lalu tekan Ctrl + T kunci.

2. klik OK dalam bermunculan Buat tabel kotak dialog. Kemudian rentang data diubah menjadi tabel.

3. Pilih rentang sel untuk meletakkan daftar drop-down, lalu klik Data > Validasi Data > Validasi Data.

4. Dalam Validasi Data kotak dialog, Anda perlu:

4.1) Pilih Daftar dalam mengizinkan daftar drop-down;
4.2) Pilih rentang tabel (tidak termasuk header) di sumber kotak;
4.3) Klik OK .

Kemudian daftar drop-down dinamis dibuat. Saat menambahkan atau menghapus data dari rentang tabel, nilai dalam daftar drop-down akan diperbarui secara otomatis.

Buat daftar drop-down dinamis dengan rumus

Selain membuat daftar drop-down dinamis dari rentang tabel, Anda juga dapat menggunakan rumus untuk membuat daftar drop-down dinamis di Excel.

1. Pilih sel yang akan menampilkan daftar drop-down.

2. klik Data > Validasi Data > Validasi Data.

3. Dalam Validasi Data kotak dialog, konfigurasikan sebagai berikut.

3.1) Di mengizinkan kotak, pilih Daftar;
3.2) Di sumber kotak, masukkan rumus di bawah ini ke dalamnya;
= OFFSET ($ A $ 13,0,0, COUNTA ($ A $ 13: $ A $ 24), 1)
Note: Dalam rumus ini, $ A $ 13 adalah sel pertama dari rentang data, dan $ A $ 13: $ A $ 24 adalah rentang data yang akan Anda buat berdasarkan daftar drop-down.
3.3) Klik OK tombol. Lihat tangkapan layar:

Kemudian daftar drop-down dinamis dibuat. Saat menambahkan atau menghapus data dari rentang tertentu, nilai dalam daftar drop-down akan diperbarui secara otomatis.

Buat daftar drop-down dari rentang bernama

Anda juga dapat membuat daftar drop-down dari rentang bernama di Excel.

1. Pertama, buat rentang bernama. Pilih rentang sel yang akan Anda buat berdasarkan rentang bernama, lalu ketik nama rentang ke dalam Nama kotak, dan tekan Enter kunci.

2. klik Data > Validasi Data > Validasi Data.

3. Dalam Validasi Data kotak dialog, konfigurasikan sebagai berikut.

3.1) Di mengizinkan kotak, pilih Daftar;
3.2) Klik pada sumber kotak, lalu tekan F3 kunci.
3.3) Di Tempel Nama kotak dialog, pilih nama rentang yang Anda buat sekarang dan kemudian klik OK tombol;
Tips: Anda juga bisa masuk secara manual = nama rentang ke dalam sumber kotak. Dalam hal ini, saya akan masuk = Kota.
3.4) Klik OK saat kembali ke Validasi Data kotak dialog. Lihat tangkapan layar:

Sekarang daftar drop-down menggunakan data dari rentang bernama dibuat.

Buat daftar turun bawah dari buku kerja lain

Misalkan ada workbook bernama “Sumber data", Dan Anda ingin membuat daftar drop-down di workbook lain berdasarkan data di"Sumber data"Buku kerja, lakukan hal berikut.

1. Buka buku kerja "SourceData". Di buku kerja ini, pilih data yang akan Anda buat berdasarkan daftar drop-down, ketikkan nama rentang ke dalam Nama kotak, lalu tekan Enter kunci.

Di sini saya menamai rentang tersebut sebagai Kota.

2. Buka lembar kerja yang akan Anda masukkan daftar drop-down. Klik Rumus > Tentukan Nama.

3. Dalam Nama baru kotak dialog, Anda perlu membuat rentang bernama berdasarkan nama rentang yang Anda buat di buku kerja "SourceData", harap konfigurasikan sebagai berikut.

3.1) Masukkan nama ke Nama kotak;
3.2) Di Mengacu pada kotak, masukkan rumus di bawah ini ke dalamnya.
= SourceData.xlsx! Kota
3.3) Klik OK untuk menyimpannya

Catatan:

1). Dalam rumusnya, Sumber data adalah nama buku kerja yang berisi data yang akan Anda buat daftar drop-down berdasarkan; Kota adalah nama rentang yang Anda tentukan di buku kerja SourceData.
2). Jika spasi atau karakter lain seperti -, #… termasuk dalam nama buku kerja data sumber, Anda perlu mengapit nama buku kerja dengan tanda kutip tunggal seperti = 'Sumber Data.xlsx'! Kota.

4. Buka buku kerja Anda akan memasukkan daftar drop-down, pilih sel untuk daftar drop-down, dan kemudian klik Data > Validasi Data > Validasi Data.

5. Dalam Validasi Data kotak dialog, konfigurasikan sebagai berikut.

5.1) Di mengizinkan kotak, pilih Daftar;
5.2) Klik pada sumber kotak, lalu tekan F3 kunci.
5.3) Di Tempel Nama kotak dialog, pilih nama rentang yang Anda buat sekarang dan kemudian klik OK tombol;
Tips: Anda juga dapat memasukkan secara manual = nama rentang ke dalam sumber kotak. Dalam hal ini, saya akan masuk = Tes.
5.4) Klik OK saat kembali ke Validasi Data kotak dialog.

Sekarang daftar drop-down telah dimasukkan dalam kisaran yang dipilih. Dan nilai drop-down berasal dari buku kerja lain.

Mudah membuat daftar drop-down dengan alat yang luar biasa

Di sini sangat merekomendasikan Buat daftar drop-down sederhana kegunaan Kutools untuk Excel. Dengan fitur ini, Anda dapat dengan mudah membuat daftar drop-down dengan nilai sel tertentu atau membuat daftar drop-down dengan preset daftar kustom di Excel.

1. Pilih sel yang ingin Anda sisipkan daftar buka-bawah, lalu klik Kutools > Daftar Drop-down > Buat daftar drop-down sederhana.

2. Dalam Buat daftar drop-down sederhana kotak dialog, konfigurasikan sebagai berikut.

3.1) Di berlaku untuk kotak, Anda dapat melihat kisaran yang dipilih ditampilkan di sini. Anda dapat mengubah rentang sel yang diterapkan sesuai kebutuhan;
3.2) Di sumber bagian, jika Anda ingin membuat daftar drop-down berdasarkan data rentang sel atau Anda hanya perlu memasukkan nilai secara manual, pilih Masukkan nilai atau rujuk nilai sel pilihan. Di kotak teks, pilih rentang sel atau ketik nilai (dipisahkan dengan koma) Anda akan membuat daftar drop-down berdasarkan;
3.3) Klik OK.

Note: Jika Anda ingin membuat daftar drop-down berdasarkan preset daftar kustom di Excel, pilih Daftar kustom pilihan dalam sumber bagian, pilih daftar kustom di Daftar kustom kotak, dan kemudian klik OK .

Sekarang daftar drop-down telah dimasukkan dalam kisaran yang dipilih.

Edit daftar drop-down

Jika Anda ingin mengedit daftar drop-down, metode di bagian ini dapat membantu Anda.

Edit daftar drop-down berdasarkan rentang sel

Untuk mengedit daftar drop-down berdasarkan rentang sel, lakukan hal berikut.

1. Pilih sel yang berisi daftar drop-down yang ingin Anda edit, lalu klik Data > Validasi Data > Validasi Data.

2. Dalam Validasi Data kotak dialog, ubah referensi sel di sumber kotak dan kemudian klik OK .

Edit daftar drop-down berdasarkan rentang bernama

Misalkan Anda menambahkan atau menghapus nilai dalam rentang bernama, dan daftar drop-down dibuat berdasarkan rentang bernama ini. Untuk memunculkan nilai yang diperbarui dalam daftar drop down, lakukan hal berikut.

1. klik Rumus > Manajer nama.

Tips: Anda dapat membuka Manajer nama jendela dengan menekan Ctrl + F3 kunci.

2. Dalam Manajer nama jendela, Anda perlu mengonfigurasi sebagai berikut:

2.1) Di Nama kotak, pilih rentang bernama yang ingin Anda perbarui;
2.2) Di Mengacu pada bagian, klik tombol untuk memilih kisaran yang diperbarui untuk daftar drop-down Anda;
2.3) Klik Penyelesaian .

3. Kemudian a Microsoft Excel kotak dialog muncul, klik Yes Tombol untuk menyimpan perubahan.

Kemudian daftar drop-down berdasarkan rentang bernama ini diperbarui.

Hapus daftar drop-down

Bagian ini berbicara tentang menghapus daftar drop-down di Excel.

Hapus daftar drop-down dengan Excel build-in

Excel menyediakan fitur built-in untuk membantu menghapus daftar drop-down dari lembar kerja. Silakan lakukan sebagai berikut.

1. Pilih rentang sel yang berisi daftar drop-down yang ingin Anda hapus.

2. klik Data > Validasi Data > Validasi Data.

3. Dalam Validasi Data kotak dialog, klik Batal Semua tombol, dan kemudian klik OK Untuk menyimpan perubahan

Sekarang daftar drop-down dihapus dari kisaran yang dipilih.

Hapus daftar drop-down dengan mudah menggunakan alat yang luar biasa

Kutools untuk Excel menyediakan alat praktis - Hapus Batasan Validasi Datas untuk membantu dengan mudah menghapus daftar drop-down dari satu atau beberapa rentang yang dipilih sekaligus. Silakan lakukan sebagai berikut.

1. Pilih rentang sel yang berisi daftar drop-down yang ingin Anda hapus.

2. klik Kutools > Cegah Pengetikan > Hapus Batasan Validasi Data. Lihat tangkapan layar:

3. Kemudian a Kutools untuk Excel kotak dialog muncul untuk menanyakan apakah Anda menghapus daftar drop-down, silakan klik OK .

Kemudian daftar drop-down dalam rentang yang dipilih ini segera dihapus.

Tambahkan warna ke daftar drop-down

Dalam beberapa kasus, Anda mungkin perlu membuat daftar drop-down yang diberi kode warna untuk membedakan data di sel daftar drop-down dengan cepat. Bagian ini memberikan dua metode untuk membantu Anda menyelesaikan masalah secara mendetail.

Tambahkan warna ke daftar drop-down dengan Conditional Formatting

Anda dapat membuat aturan bersyarat ke sel yang berisi daftar drop-down untuk membuatnya diberi kode warna. Silakan lakukan sebagai berikut.

1. Pilih sel yang berisi daftar drop-down yang ingin Anda jadikan kode warna.

2. klik Beranda > Format Bersyarat > Kelola Aturan.

3. Dalam Manajer Pemformatan Bersyarat kotak dialog, klik Aturan baru .

4. Dalam Aturan Pemformatan Baru kotak dialog, konfigurasikan sebagai berikut.

4.1) Di Pilih Jenis Aturan kotak, pilih Format hanya sel yang berisi pilihan;
4.2) Di Format hanya sel dengan bagian, pilih Teks Tertentu dari daftar drop-down pertama, pilih mengandung dari daftar turun bawah kedua, lalu pilih item pertama dari daftar sumber di kotak ketiga;
Tips: Di sini saya memilih sel A16 di kotak teks ketiga. A16 adalah item pertama dari daftar sumber yang saya buat berdasarkan daftar drop-down.
4.3) Klik dibentuk .
4.4) Di Format Cells kotak dialog, pergi ke Mengisi tab, pilih warna latar belakang untuk teks yang ditentukan, lalu klik OK tombol. Atau Anda dapat memilih warna font tertentu untuk teks yang Anda butuhkan.
4.5) Klik OK tombol saat kembali ke Aturan Pemformatan Baru kotak dialog.

5. Ketika kembali ke Manajer Aturan Pemformatan Bersyarat kotak dialog, ulangi langkah 3 dan 4 di atas untuk menentukan warna untuk item drop down lainnya. Setelah selesai menentukan warna, klik OK Untuk menyimpan perubahan

Mulai sekarang, saat memilih item dari daftar drop-down, sel akan disorot dengan warna latar yang ditentukan berdasarkan teks yang dipilih.

Tambahkan warna dengan mudah ke daftar drop-down dengan alat yang luar biasa

Berikut perkenalkan Daftar Drop-down Berwarna fitur dari Kutools untuk Excel untuk membantu Anda menambahkan warna dengan mudah ke daftar drop-down di Excel.

1. Pilih sel yang berisi daftar drop-down yang ingin Anda tambahkan warna.

2. klik Kutools > Daftar Drop-down > Daftar Drop-down Berwarna.

3. Dalam Daftar tarik-turun berwarna kotak dialog, lakukan hal berikut.

3.1) Di berlaku untuk bagian, pilih Sel daftar tarik-turun pilihan;
3.2) Di Rentang validasi data (Daftar Drop-down) kotak, Anda dapat melihat referensi sel yang dipilih ditampilkan di dalamnya. Anda dapat mengubah rentang sel sesuai kebutuhan;
3.3) Di Daftar item kotak (semua item drop-down dalam kisaran yang dipilih ditampilkan di sini), pilih item yang akan Anda tentukan warnanya;
3.4) Di Pilih warna bagian, pilih warna latar belakang;
Note: Anda perlu mengulangi langkah 3.3 dan 3.4 untuk menentukan warna yang berbeda untuk item lainnya;
3.5) Klik OK tombol. Lihat tangkapan layar:

Tips: Jika Anda ingin menyorot baris berdasarkan pilihan daftar tarik-turun, pilih Deretan rentang data pilihan dalam berlaku untuk bagian, lalu pilih baris yang akan Anda sorot di Sorot baris kotak.

Sekarang daftar drop-down diberi kode warna seperti gambar di bawah ini.

Sorot sel berdasarkan pilihan daftar drop-down

Sorot baris berdasarkan pilihan daftar drop-down

Buat daftar drop-down dependen di Excel atau lembar google

Sebuah daftar drop-down dependen membantu menampilkan pilihan-pilihan yang bergantung pada nilai yang diseleksi pada daftar drop-down pertama. Jika Anda perlu membuat daftar drop-down dependen (berjenjang) di lembar kerja Excel atau di lembar google, metode di bagian ini dapat membantu Anda.

Buat daftar drop-down dependen di lembar kerja Excel

Demo di bawah ini menampilkan daftar drop-down dependen di lembar kerja Excel.

Silahkan klik Bagaimana Cara Membuat Daftar Drop Down Cascading Tergantung Di Excel? untuk tutorial panduan langkah demi langkah.

Buat daftar drop-down dependen di lembar google

Jika Anda ingin membuat daftar drop-down dependen di google sheet, silakan lihat Bagaimana Cara Membuat Daftar Drop Down Yang Bergantung Di Google Sheet?

Buat daftar drop-down yang dapat dicari

Untuk daftar drop-down yang berisi daftar item yang panjang di lembar kerja, tidak mudah bagi Anda untuk mengambil item tertentu dari daftar tersebut. Jika Anda mengingat karakter awal atau beberapa karakter berurutan dari suatu item, Anda dapat melakukan fitur pencarian di daftar drop-down untuk memfilternya dengan mudah. Bagian ini akan menunjukkan cara membuat daftar drop-down yang dapat dicari di Excel.

Misalkan data sumber yang ingin Anda buat daftar drop-down berdasarkan lokasi di kolom A Sheet1 seperti gambar di bawah ini. Lakukan hal berikut untuk membuat daftar drop-down yang dapat dicari di Excel dengan data ini.

1. Pertama, buat kolom pembantu di samping daftar data sumber dengan rumus array.

Dalam hal ini, saya memilih sel B2, masukkan rumus di bawah ini ke dalamnya lalu tekan Ctrl + perubahan + Enter kunci untuk mendapatkan hasil pertama.

=IFERROR(INDEX($A$2:$A$50,SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),$A$2:$A$50)>0,$A$2:$A$50,""),$A$2:$A$50,0),""),ROW(A1))),"")

Pilih sel hasil pertama, lalu seret Isi Handle terus ke bawah hingga mencapai akhir daftar.

Note: Dalam rumus array ini, $ A $ 2: $ A $ 50 adalah rentang data sumber yang akan Anda buat berdasarkan daftar drop-down. Harap ubah berdasarkan rentang data Anda.

2. klik Rumus > Tentukan Nama.

3. Dalam Edit Nama kotak dialog, konfigurasikan sebagai berikut.

3.1) Di Nama kotak, masukkan nama untuk rentang bernama;
3.2) Di Mengacu pada kotak, masukkan rumus di bawah ini ke dalamnya;
=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$50)-COUNTIF(Sheet1!$B$2:$B$50,""),1)
3.3) Klik OK tombol. Lihat tangkapan layar:

Sekarang Anda perlu membuat daftar drop-down berdasarkan rentang bernama. Dalam hal ini, saya akan membuat daftar drop-down yang dapat dicari di Sheet2.

4. Buka Sheet2, pilih kisaran sel untuk daftar drop-down, lalu klik Data > Validasi Data > Validasi Data.

5. Dalam Validasi Data kotak dialog, lakukan hal berikut.

5.1) Di mengizinkan kotak, pilih Daftar;
5.2) Klik sumber kotak, lalu tekan F3 kunci;
5.3) Dalam bermunculan Tempel Nama dialog, pilih rentang bernama yang Anda buat di langkah3 dan kemudian klik OK;
Tips: Anda dapat langsung memasukkan rentang bernama sebagai = rentang bernama ke dalam sumber kotak.
5.4) Klik Peringatan Kesalahan tab, hapus centang pada Tampilkan peringatan kesalahan setelah data yang tidak valid dimasukkan kotak, dan terakhir klik OK .

6. Klik kanan tab lembar (Lembar2) dan pilih Lihat kode dari menu klik kanan.

7. Dalam pembukaan Microsoft Visual Basic untuk Aplikasi jendela, salin kode VBA di bawah ini ke editor Kode.

Kode VBA: buat daftar drop-down yang dapat dicari di Excel

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub

8. tekan lain + Q kunci untuk menutup Microsoft Visual Basic untuk Aplikasi jendela.

Sekarang daftar drop-down yang dapat dicari dibuat. Jika Anda ingin mengambil sebuah item, cukup masukkan satu atau beberapa karakter berturut-turut dari item ini ke dalam sel drop-down, klik panah drop-down, dan kemudian item berdasarkan konten yang dimasukkan terdaftar di daftar drop-down. Lihat tangkapan layar:

Note: Metode ini peka huruf besar / kecil.

Buat daftar drop-down tetapi tunjukkan nilai yang berbeda

Misalkan Anda telah membuat daftar drop-down, saat memilih item darinya, Anda ingin sesuatu yang lain ditampilkan di sel. Seperti yang ditunjukkan pada demo di bawah ini, Anda telah membuat daftar drop-down berdasarkan daftar nama negara, saat memilih nama negara dari drop-down, Anda ingin menampilkan singkatan nama negara yang dipilih di sel drop-down. Bagian ini menyediakan metode VBA untuk membantu Anda menyelesaikan masalah.

1. Di sisi kanan data sumber (kolom nama negara), buat kolom baru yang berisi abbriviasi dari nama negara yang ingin Anda tampilkan di sel drop-down.

2. Pilih daftar nama negara dan daftar singkatan, ketikkan nama di Nama kotak dan kemudian tekan Enter kunci.

3. Pilih sel untuk daftar drop-down (di sini saya pilih D2: D8), lalu klik Data > Validasi Data > Validasi Data.

4. Dalam Validasi Data kotak dialog, konfigurasikan sebagai berikut.

4.1) Di mengizinkan kotak, pilih Daftar;
4.2) Di sumber kotak, pilih rentang data sumber (daftar nama negara dalam kasus ini);
4.3) Klik OK.

5. Setelah membuat daftar drop-down, klik kanan tab sheet, lalu pilih Lihat kode dari menu klik kanan.

6. Dalam pembukaan Microsoft Visual Basic untuk Aplikasi jendela, salin kode VBA di bawah ini ke editor Kode.

Kode VBA: Tampilkan nilai yang berbeda dalam daftar drop-down

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20201027
    selectedNa = Target.Value
    If Target.Column = 4 Then
        selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown"), 2, False)
        If Not IsError(selectedNum) Then
            Target.Value = selectedNum
        End If
    End If
End Sub

Catatan:

1) Di kode, angka 4 di baris Jika Target.Column = 4 Kemudian mewakili nomor kolom dari daftar drop-down yang Anda buat pada langkah 3 dan 4. Jika daftar drop-down Anda terletak di kolom F, silahkan ganti angka 4 dengan 6;
2) "dropdown”Di baris kelima adalah nama rentang yang Anda buat di langkah 2. Anda dapat mengubahnya sesuai kebutuhan.

7. tekan lain + Q kunci untuk menutup Microsoft Visual Basic untuk Aplikasi jendela.

Mulai sekarang, saat memilih nama negara tertentu dari daftar drop-down, singkatan yang sesuai dari nama negara yang dipilih akan ditampilkan di sel.

Buat daftar drop-down dengan kotak centang

Banyak pengguna Excel cenderung membuat daftar drop-down dengan beberapa kotak centang sehingga mereka dapat memilih beberapa item dari daftar hanya dengan mencentang kotak tersebut.

Seperti yang ditunjukkan demo di bawah ini, ketika mengklik sel yang berisi daftar drop-down, kotak daftar muncul. Di kotak daftar, ada kotak centang sebelum setiap item. Anda dapat mencentang kotak untuk menampilkan item yang sesuai di dalam sel.

Jika Anda ingin membuat daftar drop-down dengan kotak centang di Excel, silakan lihat Bagaimana Cara Membuat Daftar Drop Down Dengan Beberapa Kotak Centang Di Excel?.

Tambahkan pelengkapan otomatis ke daftar tarik-turun

Jika Anda memiliki daftar drop-down validasi data dengan item besar, Anda perlu menggulir ke atas dan ke bawah dalam daftar untuk menemukan yang tepat, atau ketik seluruh kata ke dalam kotak daftar secara langsung. Jika drop down list bisa otomatis selesai saat mengetik huruf pertama di dalamnya, semuanya akan menjadi lebih mudah.

Untuk membuat daftar drop-down autocomplete di lembar kerja di Excel, silakan lihat Bagaimana Cara Melengkapi Otomatis Saat Mengetik Di Daftar Drop Down Excel?.

Filter data berdasarkan pilihan daftar drop-down

Bagian ini akan mendemonstrasikan cara menerapkan rumus untuk membuat filter daftar drop-down untuk mengekstrak data berdasarkan pilihan dari daftar drop-down.

1. Pertama, Anda perlu membuat daftar drop-down dengan nilai spesifik yang akan Anda ekstrak datanya.

Tips: Ikuti langkah-langkah di atas untuk buat daftar drop-down di Excel.

Buat daftar drop-down dengan daftar item unik

Jika ada duplikat dalam rentang Anda, dan Anda tidak ingin membuat daftar drop-down dengan pengulangan item, Anda dapat membuat daftar unik item sebagai berikut.

1) Salin sel yang akan Anda buat daftar drop-down berdasarkan Ctrl + C kunci, lalu tempel ke rentang baru.

2) Pilih sel di kisaran baru, klik Data > Hapus Duplikat.

3) Di Hapus Duplikat kotak dialog, klik OK .

4) Kemudian a Microsoft Excel muncul untuk memberi tahu Anda berapa banyak duplikat yang dihapus, klik OK.

Sekarang Anda mendapatkan daftar item unik, Anda dapat membuat daftar drop-down berdasarkan daftar unik ini sekarang.

2. Kemudian Anda perlu membuat tiga kolom pembantu sebagai berikut.

2.1) Untuk kolom first helper (disini saya pilih kolom D sebagai kolom first helper), masukkan rumus di bawah ini ke dalam sel pertama (kecuali kolom header), lalu tekan Enter kunci. Pilih sel hasil, lalu seret Isi Handle terus ke bawah hingga mencapai bagian bawah jangkauan.
= BARIS ($ A $ 2: A2)
2.2) Untuk kolom pembantu kedua (kolom E), masukkan rumus di bawah ini di sel E2 dan kemudian tekan Enter kunci. Pilih E2 lalu seret Isi Handle ke bagian bawah rentang.
Catatan: Jika tidak ada nilai yang dipilih di daftar turun bawah, di sini hasil rumus akan ditampilkan sebagai kosong.
= JIKA (A2 = $ H $ 2, D2, "")
2.3) Untuk kolom pembantu ketiga (kolom F), masukkan rumus di bawah ini di F2 dan kemudian tekan Enter kunci. Pilih F2 lalu seret Isi Handle ke bagian bawah rentang.
Note: Jika tidak ada nilai yang dipilih dalam daftar drop-down, hasil rumus akan ditampilkan kosong.
= IFERROR (KECIL ($ E $ 2: $ E $ 17, D2), "")

3. Buat rentang berdasarkan rentang data asli untuk mengeluarkan data yang diekstrak dengan rumus di bawah ini.

3.1) Pilih sel keluaran pertama (Di sini saya pilih J2), masukkan rumus di bawah ini ke dalamnya dan kemudian tekan Enter kunci.
=IFERROR(INDEX($A$2:$C$17,$F2,COLUMNS($J$2:J2)),"")
3.2) Pilih sel hasil, lalu seret Isi Handle menyeberang ke dua sel kanan.
3.3) Pertahankan rentang J2: l2 dipilih, seret Fill Handle ke bawah hingga mencapai bagian bawah rentang.

Catatan:

1) Jika tidak ada nilai yang dipilih dalam daftar drop-down, hasil rumus akan ditampilkan sebagai kosong.
2) Anda dapat menyembunyikan tiga kolom pembantu sesuai kebutuhan.

Sekarang filter daftar drop-down dibuat, Anda dapat dengan mudah mengekstrak data dari rentang data asli berdasarkan pilihan daftar drop-down.

Pilih beberapa item dari daftar drop-down

Secara default, daftar drop-down memungkinkan pengguna untuk memilih hanya satu item per waktu di dalam sel. Saat memilih ulang item dalam daftar drop-down, item yang dipilih sebelumnya akan ditimpa. Namun, jika Anda diminta untuk memilih beberapa item dari daftar drop-down dan menampilkan semuanya di sel drop-down seperti yang ditunjukkan pada demo di bawah ini, bagaimana Anda bisa melakukannya?

Untuk memilih beberapa item dari daftar drop-down di Excel, silakan lihat Bagaimana Cara Membuat Daftar Drop Down Dengan Beberapa Pilihan Atau Nilai Di Excel?. Tutorial ini memberikan dua metode secara detail untuk membantu Anda memecahkan masalah.

Tetapkan nilai default (dipilih sebelumnya) untuk daftar drop-down

Secara default, sel daftar drop-down ditampilkan sebagai kosong, panah drop-down hanya muncul saat Anda mengklik sel. Bagaimana cara mengetahui sel mana yang berisi daftar drop-down di lembar kerja secara sekilas?

Bagian ini akan mendemonstrasikan cara menyetel nilai default (dipilih sebelumnya) untuk daftar drop-down di Excel. Silakan lakukan sebagai berikut.

Sebelum menerapkan dua metode di bawah ini, Anda perlu membuat daftar drop-down dan melakukan beberapa konfigurasi sebagai berikut.

1. Pilih sel untuk daftar drop-down, klik Data > Validasi Data > Validasi Data.

Tips: Jika Anda sudah membuat daftar drop-down, pilih sel yang berisi daftar drop-down, lalu klik Data > Validasi Data > Validasi Data.

2. Dalam Validasi Data kotak dialog, konfigurasikan sebagai berikut.

2.1) Di mengizinkan kotak, pilih Daftar;
2.2) Di sumber kotak, pilih data sumber yang akan Anda tampilkan di daftar drop-down.
Tips: Untuk daftar tarik-turun yang telah Anda buat, lewati dua langkah ini.
2.3) Lalu pergi ke Peringatan Kesalahan tab, hapus centang pada Tampilkan peringatan kesalahan setelah data yang tidak valid dimasukkan kotak;
2.4) Klik OK .

Setelah membuat daftar tarik-turun, harap terapkan salah satu metode di bawah ini untuk menyetel nilai default untuk mereka.

Tetapkan nilai default untuk daftar drop-down dengan rumus

Anda dapat menerapkan rumus di bawah ini untuk menetapkan nilai default untuk daftar drop-down yang Anda buat seperti langkah-langkah di atas yang ditunjukkan.

1. Pilih sel daftar drop-down, masukkan rumus di bawah ini ke dalamnya dan kemudian tekan Enter kunci untuk menampilkan nilai default. Jika sel daftar drop-down berurutan, Anda dapat menyeret file Isi Handle dari sel hasil untuk menerapkan rumus ke sel lain.

= IF (C2 = "", "--Pilih item dari daftar--")

Catatan:

1) Dalam rumusnya, C2 adalah sel kosong di sebelah sel daftar drop-down, Anda dapat menentukan sel kosong apa pun yang Anda butuhkan.
2) --Pilih item dari daftar-- adalah nilai default untuk ditampilkan di sel daftar drop-down. Anda juga dapat mengubah nilai default berdasarkan kebutuhan Anda.
3) Rumus hanya berfungsi sebelum memilih item dari drop-down, setelah memilih item dari drop-down, nilai default akan ditimpa dan rumus akan hilang.
Tetapkan nilai default untuk semua daftar drop-down di lembar kerja sekaligus dengan kode VBA

Misalkan ada banyak daftar drop-down yang berada di rentang berbeda di lembar kerja Anda, untuk mengatur nilai default untuk semuanya, Anda perlu menerapkan rumus berulang kali. Itu memakan waktu. Bagian ini menyediakan kode VBA yang berguna bagi Anda untuk mengatur nilai default untuk semua daftar drop-down di lembar kerja sekaligus.

1. Buka lembar kerja yang berisi daftar drop-down yang ingin Anda setel nilai defaultnya, tekan lain + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. Dalam Microsoft Visual Basic untuk Aplikasi window, klik Menyisipkan > Modul, lalu tempel kode VBA di bawah ini ke jendela Kode.

Kode VBA: Tetapkan nilai default untuk semua daftar drop-down di lembar kerja sekaligus

Sub SetDropDownListToDefaultValue()
'Updated by Extendoffice 20201026
Dim xWs As Worksheet
Dim xRg, xFRg As Range
Dim xET: xET = Null
Dim xStr As String
xStr = "- Choose from the list -"
Set xWs = Application.ActiveSheet
Set xRg = xWs.UsedRange.Cells
    On Error Resume Next
    For Each xFRg In xRg
    xET = Null
    xET = xFRg.Validation.Type
    If Not IsNull(xET) Then
        If xFRg.Validation.Type = 3 Then
            xFRg.Value = "'" & xStr
        End If
    End If
    Next
End Sub

Catatan: Pada kode di atas, - Pilih dari daftar - adalah nilai default untuk ditampilkan di sel daftar drop-down. Anda juga dapat mengubah nilai default berdasarkan kebutuhan Anda.

3. tekan F5 kunci, lalu kotak dialog Makro muncul, pastikan file DropDownListToDefault dipilih di Nama Makro kotak, dan kemudian klik Run tombol untuk menjalankan kode.

Kemudian nilai default yang ditentukan segera diisi ke dalam sel daftar drop-down.

Tingkatkan ukuran font daftar drop-down

Biasanya, daftar drop-down memiliki ukuran font tetap, jika ukuran font sangat kecil untuk dibaca, Anda dapat mencoba metode VBA di bawah ini untuk memperbesarnya.

1. Buka lembar kerja yang berisi daftar drop-down yang ingin Anda perbesar ukuran fontnya, klik kanan tab lembar lalu pilih Lihat kode dari menu klik kanan.

2. Dalam Microsoft Visual Basic untuk Aplikasi jendela, salin kode VBA di bawah ini ke editor Kode.

Kode VBA: Memperbesar ukuran font dari daftar drop-down di lembar kerja

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'updateby Extendoffice 20201027
    On Error GoTo LZoom
    Dim xZoom As Long
    xZoom = 100
    If Target.Validation.Type = xlValidateList Then xZoom = 130
LZoom:
    ActiveWindow.Zoom = xZoom
End Sub

Note: di sini xZoom = 130 dalam kode berarti Anda akan memperbesar ukuran font dari semua daftar drop-down di lembar kerja saat ini menjadi 130. Anda dapat mengubahnya sesuai kebutuhan.

3. tekan lain + Q kunci untuk menutup Microsoft Visual Basic untuk Aplikasi jendela.

Mulai sekarang, saat mengklik sel drop-down, tingkat zoom lembar kerja saat ini akan diperbesar, klik panah drop-down, Anda dapat melihat ukuran font dari semua item drop-down juga diperbesar.

Setelah memilih item dari daftar drop-down, Anda dapat mengklik sel mana saja di luar sel drop-down untuk kembali ke tingkat zoom asli.

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 Teratas:  12 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 (1)
No ratings yet. Be the first to rate!
通りすがりのわんこ
通りすがりのわんこ
This comment was minimized by the moderator on the site
こちらはOffice365ですが、どうやらそのコーディングでは動作しないようです。
代わりに初歩的ですが、以下にて動作を確認出来ました。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim xZoom As Variant
If (Target.Row >= 11 And Target.Row <= 35 And Target.Column >= 3 And Target.Column <= 6) Then
ActiveWindow.zoom = 150
Else
ActiveWindow.zoom = 60
End If
End Sub
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations