4 Fungsi Pencarian Excel untuk Mencari Spreadsheet Secara Efisien

4 Fungsi Pencarian Excel untuk Mencari Spreadsheet Secara Efisien

Sebagian besar waktu, mencari Microsoft Excel spreadsheet cukup mudah. Jika Anda tidak bisa hanya memindai melalui baris dan kolom untuk itu, Anda dapat menggunakan Ctrl + F untuk mencarinya. Jika Anda bekerja dengan spreadsheet yang sangat besar, dapat menghemat banyak waktu untuk menggunakan salah satu dari empat fungsi pencarian ini.





Setelah Anda tahu cara mencari di Excel menggunakan pencarian, tidak peduli seberapa besar spreadsheet Anda, Anda akan selalu dapat menemukan sesuatu di Excel!



1. Fungsi VLOOKUP

Fungsi ini memungkinkan Anda untuk menentukan kolom dan nilai, dan akan mengembalikan nilai dari baris yang sesuai dari kolom yang berbeda (jika itu tidak masuk akal, itu akan menjadi jelas dalam beberapa saat). Dua contoh di mana Anda dapat melakukan ini adalah mencari nama belakang karyawan dengan nomor karyawan mereka atau menemukan nomor telepon dengan menentukan nama belakang.





Berikut sintaks dari fungsinya:

=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])
  • [nilai lookup] adalah informasi yang sudah Anda miliki. Misalnya, jika Anda perlu mengetahui di negara bagian mana sebuah kota berada, itu akan menjadi nama kota tersebut.
  • [tabel_array] memungkinkan Anda menentukan sel tempat fungsi akan mencari pencarian dan mengembalikan nilai. Saat memilih rentang Anda, pastikan bahwa kolom pertama yang disertakan dalam larik Anda adalah kolom yang akan menyertakan nilai pencarian Anda!
  • [kol_index_number] adalah jumlah kolom yang berisi nilai kembalian.
  • [range_lookup] adalah argumen opsional, dan mengambil 1 atau 0. Jika Anda memasukkan 1 atau menghilangkan argumen ini, fungsi akan mencari nilai yang Anda masukkan atau angka terendah berikutnya. Jadi pada gambar di bawah, VLOOKUP yang mencari skor SAT 652 akan menghasilkan 646, karena itu adalah angka terdekat dalam daftar yang kurang dari 652, dan [range_lookup] default ke 1.

Mari kita lihat bagaimana Anda bisa menggunakan ini. Spreadsheet ini berisi nomor ID, nama depan dan belakang, kota, negara bagian, dan skor SAT. Katakanlah Anda ingin mencari skor SAT seseorang dengan nama belakang 'Musim Dingin.' VLOOKUP membuatnya mudah. Berikut rumus yang akan Anda gunakan:



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

Karena skor SAT adalah kolom keempat dari kolom nama belakang, 4 adalah argumen indeks kolom. Perhatikan bahwa saat Anda mencari teks, menyetel [range_lookup] ke 0 adalah ide yang bagus. Tanpa itu, Anda bisa mendapatkan hasil yang buruk.

Berikut hasilnya:



Ini mengembalikan 651, skor SAT milik siswa bernama Kennedy Winters, yang berada di baris 92 (ditampilkan pada sisipan di atas). Butuh waktu lebih lama untuk menggulir mencari nama daripada mengetik sintaks dengan cepat!

Catatan tentang VLOOKUP

Beberapa hal yang baik untuk diingat ketika Anda menggunakan VLOOKUP. Pastikan kolom pertama dalam rentang Anda adalah kolom yang menyertakan nilai pencarian Anda. Jika tidak ada di kolom pertama, fungsi akan mengembalikan hasil yang salah. Jika kolom Anda tertata dengan baik, ini seharusnya tidak menjadi masalah.



Juga, perlu diingat bahwa VLOOKUP hanya akan mengembalikan satu nilai. Jika Anda menggunakan 'Georgia' sebagai nilai pencarian, itu akan mengembalikan skor siswa pertama dari Georgia, dan tidak memberikan indikasi bahwa sebenarnya ada dua siswa dari Georgia.

2. Fungsi HLOOKUP

Di mana VLOOKUP menemukan nilai yang sesuai di kolom lain, HLOOKUP menemukan nilai yang sesuai di baris yang berbeda. Karena biasanya paling mudah untuk memindai melalui judul kolom sampai Anda menemukan yang tepat dan menggunakan filter untuk menemukan apa yang Anda cari, HLOOKUP paling baik digunakan saat Anda memiliki spreadsheet yang sangat besar atau Anda bekerja dengan nilai yang diatur berdasarkan waktu .

Berikut sintaks dari fungsinya:

=HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup])
  • [nilai lookup] adalah nilai yang Anda ketahui dan ingin dicari nilai yang sesuai.
  • [tabel_array] adalah sel di mana Anda ingin mencari.
  • [nomor_indeks_baris] menentukan baris dari mana nilai kembalian akan berasal.
  • [range_lookup] sama seperti di VLOOKUP, biarkan kosong untuk mendapatkan nilai terdekat jika memungkinkan, atau masukkan 0 hanya untuk mencari kecocokan persis.

Spreadsheet ini berisi satu baris untuk setiap negara bagian, bersama dengan skor SAT pada tahun 2000–2014. Anda dapat menggunakan HLOOKUP untuk menemukan skor rata-rata di Minnesota pada tahun 2013. Berikut cara kami melakukannya:

=HLOOKUP(2013, A1:P51, 24)

Seperti yang Anda lihat pada gambar di bawah, skor dikembalikan:

Minnesotans rata-rata mendapat skor 1014 pada tahun 2013. Perhatikan bahwa 2013 tidak dalam tanda kutip karena itu angka, dan bukan string. Juga, 24 berasal dari Minnesota berada di baris ke-24.

ini cara menghitung rata-rata tertimbang di Excel .

Catatan tentang HLOOKUP

Seperti halnya VLOOKUP, nilai pencarian harus berada di baris pertama larik tabel Anda. Ini jarang menjadi masalah dengan HLOOKUP, karena Anda biasanya akan menggunakan judul kolom untuk nilai pencarian. HLOOKUP juga hanya mengembalikan satu nilai.

3-4. Fungsi INDEX dan MATCH

INDEX dan MATCH adalah dua fungsi yang berbeda, tetapi ketika digunakan bersama, keduanya dapat membuat pencarian spreadsheet besar menjadi jauh lebih cepat. Kedua fungsi tersebut memiliki kekurangan, tetapi dengan menggabungkannya, kita akan membangun kekuatan dari keduanya.

Pertama, sintaks dari kedua fungsi:

=INDEX([array], [row_number], [column_number])
  • [Himpunan] adalah larik di mana Anda akan mencari.
  • [nomor_baris] dan [nomor_kolom] dapat digunakan untuk mempersempit pencarian Anda (kita akan melihatnya sebentar lagi.)
=MATCH([lookup_value], [lookup_array], [match_type])
  • [nilai lookup] adalah istilah pencarian yang dapat berupa string atau angka.
  • [larik_pencarian] adalah larik di mana Microsoft Excel akan mencari istilah pencarian.
  • [jenis_pertandingan] adalah argumen opsional yang dapat berupa 1, 0, atau -1. 1 akan mengembalikan nilai terbesar yang lebih kecil dari atau sama dengan istilah pencarian Anda. 0 hanya akan mengembalikan istilah persis Anda, dan -1 akan mengembalikan nilai terkecil yang lebih besar dari atau sama dengan istilah penelusuran Anda.

Mungkin tidak jelas bagaimana kita akan menggunakan kedua fungsi ini bersama-sama, jadi saya akan menjelaskannya di sini. MATCH mengambil istilah pencarian dan mengembalikan referensi sel. Pada gambar di bawah, Anda dapat melihat bahwa dalam pencarian nilai 646 di kolom F, MATCH mengembalikan 4.

INDEX, di sisi lain, melakukan yang sebaliknya: dibutuhkan referensi sel dan mengembalikan nilai di dalamnya. Anda dapat melihat di sini bahwa, ketika diminta untuk mengembalikan sel keenam dari kolom Kota, INDEX mengembalikan 'Anchorage,' nilai dari baris 6.

Apa yang akan kita lakukan adalah menggabungkan keduanya sehingga MATCH mengembalikan referensi sel dan INDEX menggunakan referensi itu untuk mencari nilai dalam sel. Katakanlah Anda ingat bahwa ada seorang siswa yang nama belakangnya adalah Waters, dan Anda ingin melihat berapa skor siswa ini. Berikut rumus yang akan kita gunakan:

apa yang Anda dapatkan dengan linkedin premium?
=INDEX(F:F, MATCH('Waters', C:C, 0))

Anda akan melihat bahwa jenis pencocokan diatur ke 0 di sini. Saat Anda mencari string, itulah yang ingin Anda gunakan. Inilah yang kami dapatkan ketika kami menjalankan fungsi itu:

Seperti yang Anda lihat dari inset, Owen Waters mencetak 1720, angka yang muncul saat kita menjalankan fungsi tersebut. Ini mungkin tampak tidak terlalu berguna ketika Anda hanya dapat melihat beberapa kolom, tetapi bayangkan berapa banyak waktu yang akan Anda hemat jika Anda harus melakukannya 50 kali dalam satu waktu. spreadsheet basis data besar yang berisi beberapa ratus kolom!

Biarkan Pencarian Excel Dimulai

Microsoft Excel memiliki banyak fungsi yang sangat kuat untuk memanipulasi data, dan empat yang tercantum di atas hanya menggores permukaan. Mempelajari cara menggunakannya akan membuat hidup Anda lebih mudah.

Jika Anda benar-benar ingin menguasai Microsoft Excel, Anda benar-benar dapat mengambil manfaat dari menyimpan Lembar Cheat Excel Penting di dekat Anda!

Kredit Gambar: Cico/ Shutterstock

Membagikan Membagikan Menciak Surel Canon vs. Nikon: Merek Kamera Mana yang Lebih Baik?

Canon dan Nikon adalah dua nama besar di industri kamera. Tetapi merek mana yang menawarkan jajaran kamera dan lensa yang lebih baik?

Baca Selanjutnya Topik-topik yang berkaitan
  • Produktifitas
  • Spreadsheet
  • Microsoft Excel
  • Trik Pencarian
Tentang Penulis Ian Buckley(216 Artikel Diterbitkan)

Ian Buckley adalah jurnalis lepas, musisi, pemain, dan produser video yang tinggal di Berlin, Jerman. Ketika dia tidak menulis atau di atas panggung, dia bermain-main dengan elektronik atau kode DIY dengan harapan menjadi ilmuwan gila.

More From Ian Buckley

Berlangganan newsletter kami

Bergabunglah dengan buletin kami untuk kiat teknologi, ulasan, ebook gratis, dan penawaran eksklusif!

Klik di sini untuk berlangganan