Perkongsian kemahiran Excel Praktikal: membuat menu lungsur turun carian yang cekap

Dalam artikel sebelumnya "Berkongsi kemahiran Excel praktikal: Membuat carta Gantt dinamik", kami belajar cara membuat carta Gantt dinamik dalam Excel. Hari ini kita akan bercakap tentang menu lungsur turun data Excel dan memperkenalkan cara membuat menu lungsur turun gaya carian yang cekap Datang dan lihat!

Di tempat kerja, kami sering menggunakan pengesahan data Excel untuk membuat menu lungsur untuk menyeragamkan input data dan menjimatkan masa kemasukan data. Tetapi apabila terdapat banyak pilihan data dalam menu lungsur, ia akan menjadi sukar untuk mencari data. Sebagai contoh, dalam gambar di bawah, terdapat terlalu banyak pilihan data dalam menu lungsur turun Memakan masa untuk "mencari" item data yang diperlukan dengan menyeret bar skrol di sebelahnya, yang secara langsung mengurangkan kecekapan kerja kami.

Adakah terdapat cara untuk menyelesaikan masalah terlalu banyak pilihan dan sukar untuk mencari data ini?

Ya, kaedah saya ialah menu lungsur turun carian!

Sama seperti mencari di Baidu, selepas memasukkan kata kunci, menu lungsur turun akan muncul untuk memaparkan soalan carian yang mengandungi kata kunci untuk pemilihan. Kesan yang ingin kami capai ialah memasukkan kata kunci dalam sel, dan kemudian klik menu lungsur turun Hanya data yang mengandungi kata kunci akan dipaparkan dalam menu, dengan itu meningkatkan kecekapan kemasukan data.

Gambar di bawah ialah sumber data untuk tutorial kami. Ambil perhatian bahawa sumber data mesti diisih mengikut kata kunci, sama ada dalam susunan menaik atau menurun.

Pilih julat sel E2:E6 dan klik tab [Data], klik [Pengesahan Data] dan pilih "Tetapan" dalam pop timbul "Pengesahan Data" kotak dialog. Tetapkan syarat pengesahan dalam kad kepada "jujukan".

Masukkan formula dalam sumber:

=OFFSET($A,MATCH(E2&"*",$A:$A,0),0,COUNTIF($A:$A,E2&"*"),1)

Penerangan formula:

Di sini kami menggunakan fungsi OFFSET untuk mengembalikan data yang mengandungi kata kunci. Fungsi fungsi OFFSET adalah untuk mengembalikan data rujukan baharu melalui offset yang diberikan berdasarkan sel rujukan yang ditentukan.

OFFSET（参照系，行偏移量，列偏移量，新引用区域的行数，新引用区域的列数）

• 1 Parameter pertama merujuk kepada sel A1 sebagai sistem rujukan.

• 2 Parameter kedua menggunakan MATCH(E2&"*",$A$2:$A$17,0) untuk menentukan offset baris. MATCH ialah fungsi carian. Ia mencari di kawasan $A$2:$A$17 berdasarkan nilai carian E2&"*" (* ialah aksara kad bebas, mewakili sebarang aksara yang tidak pasti ialah 0 (carian tepat). Apabila kata kunci dimasukkan dalam sel E2, fungsi ini akan mencari kejadian pertama data yang mengandungi kata kunci dalam julat $A$2:$A$17.

• 3 Parameter ketiga ialah 0, kerana sumber data kami hanya mempunyai satu lajur, lajur A, jadi lajur mengimbangi ialah 0, yang bermaksud tiada pengimbangan. Secara ringkasnya, fungsi offset menggunakan sel A1 sebagai rujukan dan tidak mengimbangi secara mendatar, tetapi hanya mengimbangi ke bawah.

• 4 Parameter keempat COUNTIF($A$2:$A$17,E2&"*") mengira syarat E2&"*" dalam kawasan A2-A17, iaitu, ia. mengandungi unit E2 Bilangan kali kata kunci dalam sel muncul, iaitu, bilangan baris yang akan muncul dalam menu lungsur turun pengesahan data.

• 5 Parameter kelima ialah bilangan lajur dalam kawasan rujukan baharu Oleh kerana hanya terdapat lajur A, ia adalah 1.

Seperti yang ditunjukkan di bawah: fungsi offset menggunakan A1 sebagai sistem rujukan untuk mencari ke bawah, dan menggunakan fungsi padanan untuk mencari kejadian pertama data yang mengandungi kata kunci E2 "Bunga" dalam A2 -A17 Kedudukan ialah baris ke-10 bermula dari A2, dan kemudian gunakan fungsi countif untuk mencari sejumlah 3 baris, dan akhirnya mengembalikan data 3 baris dan 1 lajur ini dalam menu lungsur.

Kembali kepada tutorial. Kami memasukkan formula dalam "Sumber" kotak dialog "Pengesahan Data" dan klik "OK". Tetapi apabila kita memasukkan kata kunci "bunga pecah" dalam sel E2, kotak amaran akan muncul serta-merta. Mengapa ini?

Sebabnya selepas kita memasukkan kata kunci "bunga patah", menu lungsur yang diperolehi oleh formula tidak mempunyai pilihan yang hanya mengandungi perkataan "bunga patah ", jadi ralat akan dilaporkan .

Seperti yang ditunjukkan dalam rajah di bawah, kita perlu memilih julat sel E2:E6 sekali lagi, klik butang tab [Data] [Pengesahan Data] untuk memasuki kotak dialog "Pengesahan Data" dan nyahtanda "Amaran Ralat" tab [Tunjukkan amaran ralat apabila memasukkan data tidak sah] pilihan, dan kemudian klik "OK".

Akhir sekali masukkan formula =IFERROR(VLOOKUP(E2,$A$2:$B$17,2,0),"") dalam sel F2. Gunakan fungsi VLOOKUP untuk mencari lokasi nilai E2 dalam julat sel A2-B17 dan kembalikan inventori yang sepadan dalam lajur 2 (iaitu, lajur 0 mewakili carian yang tepat. Apabila nilai ralat tidak ditemui dan nilai ralat dikembalikan, gunakan fungsi IFERROR untuk menukar nilai ralat kepada null.

Pada ketika ini, menu lungsur turun carian telah selesai!

Menu lungsur gaya carian boleh meningkatkan kecekapan kemasukan data secara eksponen, terutamanya apabila terdapat banyak pilihan menu lungsur. Rakan sekelas, cepat buka excel anda dan lakukan beberapa operasi.

Cadangan pembelajaran berkaitan: tutorial cemerlang

Atas ialah kandungan terperinci Perkongsian kemahiran Excel Praktikal: membuat menu lungsur turun carian yang cekap. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!