4 Fungsi Pencarian Excel untuk Mencari Spreadsheets dengan cekap

4 Fungsi Pencarian Excel untuk Mencari Spreadsheets dengan cekap

Selalunya, mencari a Microsoft Excel hamparan cukup mudah. Sekiranya anda tidak dapat mengimbas baris dan lajur untuknya, anda boleh menggunakan Ctrl + F untuk mencarinya. Sekiranya anda menggunakan spreadsheet yang sangat besar, ia dapat menjimatkan banyak masa untuk menggunakan salah satu daripada empat fungsi carian ini.





Sebaik sahaja anda mengetahui cara mencari di Excel menggunakan carian, tidak kira seberapa besar hamparan anda, anda akan dapat mencari sesuatu di Excel!



1. Fungsi VLOOKUP

Fungsi ini membolehkan anda menentukan lajur dan nilai, dan akan mengembalikan nilai dari baris yang sesuai dari lajur yang berbeza (jika itu tidak masuk akal, akan menjadi jelas dalam sekejap). Dua contoh yang mungkin anda lakukan ialah mencari nama belakang pekerja dengan nombor pekerja mereka atau mencari nombor telefon dengan menentukan nama belakang.





Inilah sintaks fungsi:

=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])
  • [look__value] adalah maklumat yang anda sudah ada. Sebagai contoh, jika anda perlu mengetahui keadaan bandar ini, nama bandar itu akan menjadi sebutan.
  • [table_array] membolehkan anda menentukan sel di mana fungsi tersebut akan mencari nilai carian dan nilai kembali. Semasa memilih julat anda, pastikan bahawa lajur pertama yang disertakan dalam larik anda adalah yang merangkumi nilai carian anda!
  • [col_index_num] ialah bilangan lajur yang mengandungi nilai kembali.
  • [jangkauan_panduan] adalah argumen pilihan, dan memerlukan 1 atau 0. Sekiranya anda memasukkan 1 atau menghilangkan argumen ini, fungsi mencari nilai yang anda masukkan atau nombor terendah berikutnya. Jadi dalam gambar di bawah ini, VLOOKUP yang mencari skor SAT 652 akan mengembalikan 646, kerana ini adalah nombor terdekat dalam senarai yang kurang dari 652, dan [range_lookup] lalai menjadi 1.

Mari lihat bagaimana anda boleh menggunakan ini. Hamparan ini mengandungi nombor ID, nama pertama dan nama belakang, skor bandar, negeri, dan SAT. Katakan anda mahu mencari skor SAT seseorang dengan nama belakang 'Winters.' VLOOKUP menjadikannya senang. Inilah formula yang akan anda gunakan:



=VLOOKUP('Winters', C2:F101, 4, 0)

Kerana skor SAT adalah lajur keempat dari lajur nama terakhir, 4 adalah argumen indeks lajur. Perhatikan bahawa semasa anda mencari teks, menetapkan [range_lookup] ke 0 adalah idea yang baik. Tanpa itu, anda boleh mendapat hasil yang buruk.

Inilah hasilnya:



Ia mengembalikan 651, skor SAT milik pelajar bernama Kennedy Winters, yang berada di barisan 92 (ditunjukkan dalam sisipan di atas). Perlu lebih lama untuk menelusuri mencari namanya daripada menaip sintaks dengan cepat!

Catatan mengenai VLOOKUP

Beberapa perkara yang perlu diingat semasa anda menggunakan VLOOKUP. Pastikan bahawa lajur pertama dalam julat anda adalah yang merangkumi nilai carian anda. Sekiranya tidak berada di lajur pertama, fungsi akan mengembalikan hasil yang salah. Sekiranya lajur anda teratur, ini tidak menjadi masalah.



Perlu diingat juga bahawa VLOOKUP hanya akan mengembalikan satu nilai. Sekiranya anda menggunakan 'Georgia' sebagai nilai pencarian, ia akan mengembalikan skor pelajar pertama dari Georgia, dan tidak memberi petunjuk bahawa sebenarnya ada dua pelajar dari Georgia.

2. Fungsi HLOOKUP

Di mana VLOOKUP mencari nilai yang sesuai di lajur lain, HLOOKUP mencari nilai yang sesuai dalam baris yang berbeza. Oleh kerana biasanya paling mudah untuk mengimbas tajuk lajur sehingga anda menemui yang tepat dan menggunakan penapis untuk mencari apa yang anda cari, HLOOKUP paling baik digunakan apabila anda mempunyai hamparan yang sangat besar atau anda bekerja dengan nilai yang disusun mengikut masa .

Inilah sintaks fungsi:

=HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup])
  • [look__value] adalah nilai yang anda tahu dan ingin mencari nilai yang sesuai untuknya.
  • [table_array] adalah sel di mana anda ingin mencari.
  • [baris_indeks_num] menentukan baris dari mana nilai kembali akan datang.
  • [jangkauan_panduan] sama seperti dalam VLOOKUP, biarkan kosong untuk mendapatkan nilai terdekat apabila mungkin, atau masukkan 0 untuk hanya mencari padanan yang tepat.

Spreadsheet ini berisi baris untuk setiap negara, bersama dengan skor SAT pada tahun 2000–2014. Anda boleh menggunakan HLOOKUP untuk mendapatkan skor purata di Minnesota pada tahun 2013. Inilah caranya kami melakukannya:

=HLOOKUP(2013, A1:P51, 24)

Seperti yang anda lihat dalam gambar di bawah, skor dikembalikan:

Minnesotans rata-rata skor 1014 pada tahun 2013. Perhatikan bahawa 2013 tidak dalam tanda petikan kerana ia adalah angka, dan bukan rentetan. Juga, 24 berasal dari Minnesota berada di barisan ke-24.

Inilah cara mengira purata wajaran dalam Excel .

Catatan mengenai HLOOKUP

Seperti VLOOKUP, nilai carian harus berada di baris pertama susunan jadual anda. Ini jarang menjadi masalah dengan HLOOKUP, kerana biasanya anda akan menggunakan tajuk lajur untuk mencari nilai. HLOOKUP juga hanya mengembalikan satu nilai.

3-4. Fungsi INDEX dan MATCH

INDEX dan MATCH adalah dua fungsi yang berbeza, tetapi apabila mereka digunakan bersama, mereka dapat membuat pencarian pada spreadsheet yang besar jauh lebih cepat. Kedua-dua fungsi mempunyai kelemahan, tetapi dengan menggabungkannya, kami akan membina kekuatan kedua-duanya.

Pertama, sintaks kedua fungsi:

=INDEX([array], [row_number], [column_number])
  • [susunan] adalah tatasusunan yang anda cari.
  • [baris_nombor] dan [ruangan_nombor] boleh digunakan untuk mempersempit carian anda (kami akan melihatnya sebentar lagi.)
=MATCH([lookup_value], [lookup_array], [match_type])
  • [look__value] adalah istilah carian yang boleh berupa rentetan atau angka.
  • [lookup_array] adalah susunan di mana Microsoft Excel akan mencari istilah carian.
  • [padanan_jenis] adalah argumen pilihan yang boleh menjadi 1, 0, atau -1. 1 akan mengembalikan nilai terbesar yang lebih kecil daripada atau sama dengan istilah carian anda. 0 hanya akan mengembalikan istilah tepat anda, dan -1 akan mengembalikan nilai terkecil yang lebih besar daripada atau sama dengan istilah carian anda.

Mungkin tidak jelas bagaimana kita akan menggunakan kedua fungsi ini bersama-sama, jadi saya akan meletakkannya di sini. MATCH mengambil istilah carian dan mengembalikan rujukan sel. Dalam gambar di bawah, anda dapat melihat bahawa dalam mencari nilai 646 di lajur F, MATCH mengembalikan 4.

INDEX, sebaliknya, melakukan sebaliknya: ia memerlukan rujukan sel dan mengembalikan nilai di dalamnya. Anda dapat lihat di sini bahawa, apabila diberitahu untuk mengembalikan sel keenam lajur City, INDEX mengembalikan 'Anchorage', nilainya dari baris 6.

Apa yang akan kami lakukan adalah menggabungkan keduanya sehingga MATCH mengembalikan rujukan sel dan INDEX menggunakan rujukan itu untuk mencari nilai dalam sel. Katakan anda ingat bahawa ada pelajar yang nama belakangnya Waters, dan anda ingin melihat apa skor pelajar ini. Inilah formula yang akan kami gunakan:

cara membuat penapis untuk snapchat
=INDEX(F:F, MATCH('Waters', C:C, 0))

Anda akan melihat bahawa jenis padanan ditetapkan ke 0 di sini. Apabila anda mencari tali, itulah yang anda mahu gunakan. Inilah yang kami dapat semasa menjalankan fungsi tersebut:

Seperti yang anda lihat dari inset, Owen Waters menjaringkan 1720, angka yang muncul ketika kita menjalankan fungsi. Ini mungkin tidak begitu berguna apabila anda hanya dapat melihat beberapa lajur, tetapi bayangkan berapa banyak masa yang anda akan jimatkan jika anda perlu melakukannya 50 kali pada hamparan pangkalan data yang besar yang mengandungi beberapa ratus lajur!

Biarkan Pencarian Excel Bermula

Microsoft Excel mempunyai banyak fungsi yang sangat kuat kerana memanipulasi data, dan keempat yang tertera di atas hanya menggaru permukaannya. Mempelajari cara menggunakannya akan menjadikan hidup anda lebih mudah.

Sekiranya anda benar-benar ingin menguasai Microsoft Excel, anda semestinya mendapat manfaat daripada menyimpan Lembaran Menipu Essential Excel berdekatan!

Kredit Gambar: Cico / Shutterstock

Berkongsi Berkongsi Tweet E-mel Canon vs Nikon: Jenama Kamera mana yang lebih baik?

Canon dan Nikon adalah dua nama terbesar dalam industri kamera. Tetapi jenama mana yang menawarkan barisan kamera dan lensa yang lebih baik?

Baca Seterusnya Topik-topik yang berkaitan
  • Produktiviti
  • Hamparan
  • Microsoft Excel
  • Caranya Mencari
Mengenai Pengarang Ian Buckley(216 Artikel Diterbitkan)

Ian Buckley adalah wartawan bebas, pemuzik, pemain dan penerbit video yang tinggal di Berlin, Jerman. Ketika dia tidak menulis atau di atas pentas, dia bermain-main dengan elektronik DIY atau kod dengan harapan menjadi saintis gila.

Lagi Dari Ian Buckley

Langgan buletin kami

Sertailah buletin kami untuk mendapatkan petua, ulasan, ebook percuma, dan tawaran eksklusif!

Klik di sini untuk melanggan