Formula umum
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Ringkasan
Dengan kumpulan data yang besar, pencocokan tepat VLOOKUP bisa sangat lambat, tetapi Anda dapat membuat VLOOKUP menjadi lebih cepat dengan menggunakan dua VLOOKUPS, seperti yang dijelaskan di bawah ini.
Catatan:
- Jika Anda memiliki kumpulan data yang lebih kecil, pendekatan ini berlebihan. Hanya gunakan dengan kumpulan data besar jika kecepatan benar-benar penting.
- Anda harus mengurutkan data dengan nilai pencarian agar trik ini berfungsi.
- Contoh ini menggunakan rentang bernama. Jika Anda tidak ingin menggunakan rentang bernama, gunakan referensi absolut sebagai gantinya.
VLOOKUP pencocokan persis lambat
Saat Anda menggunakan VLOOKUP dalam "mode pencocokan tepat" pada kumpulan data yang besar, ini benar-benar dapat memperlambat waktu penghitungan di lembar kerja. Dengan, katakanlah, 50.000 rekaman, atau 100.000 rekaman, penghitungan dapat memakan waktu beberapa menit.
Pencocokan tepat ditetapkan dengan memberikan FALSE atau nol sebagai argumen keempat:
=VLOOKUP(val,data,col,FALSE)
Alasan VLOOKUP dalam mode ini lambat adalah karena ia harus memeriksa setiap record dalam kumpulan data hingga ditemukan kecocokan. Ini terkadang disebut sebagai pencarian linier.
VLOOKUP pertandingan perkiraan sangat cepat
Dalam mode perkiraan pertandingan, VLOOKUP sangat cepat. Untuk menggunakan VLOOKUP yang cocok dengan perkiraan, Anda harus mengurutkan data Anda menurut kolom pertama (kolom pencarian), lalu tentukan TRUE untuk argumen ke-4:
=VLOOKUP(val,data,col,TRUE)
(VLOOKUP default ke true, yang merupakan default menakutkan, tapi itu cerita lain).
Dengan kumpulan data yang sangat besar, mengubah ke perkiraan kecocokan VLOOKUP dapat berarti peningkatan kecepatan yang dramatis.
Jadi, no-brainer, bukan? Cukup urutkan data, gunakan perkiraan kecocokan, dan selesai.
Tidak secepat itu (heh).
Masalah dengan VLOOKUP dalam mode "perkiraan kecocokan" adalah ini: VLOOKUP tidak akan menampilkan kesalahan jika nilai pencarian tidak ada. Lebih buruk lagi, hasilnya mungkin terlihat sangat normal, meskipun sangat salah (lihat contoh). Bukan sesuatu yang ingin Anda jelaskan kepada atasan Anda.
Solusinya adalah menggunakan VLOOKUP dua kali, keduanya dalam mode perkiraan pencocokan:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Penjelasan
Contoh pertama VLOOKUP hanya mencari nilai pencarian ( id dalam contoh ini):
=IF(VLOOKUP(id,data,1,TRUE)=id
dan mengembalikan TRUE hanya jika nilai pencarian ditemukan. Dalam kasus tersebut,
rumus menjalankan VLOOKUP lagi dalam mode pencocokan perkiraan untuk mengambil nilai dari tabel tersebut:
VLOOKUP(id,data,col,TRUE)
Tidak ada bahaya nilai pencarian yang hilang, karena bagian pertama rumus sudah diperiksa untuk memastikannya ada.
Jika nilai pencarian tidak ditemukan, bagian "nilai jika FALSE" dari fungsi IF berjalan, dan Anda dapat mengembalikan nilai apa pun yang Anda suka. Dalam contoh ini, kami menggunakan NA () kami mengembalikan kesalahan # N / A, tetapi Anda juga bisa mengembalikan pesan seperti "Hilang" atau "Tidak ditemukan".
Ingat: Anda harus mengurutkan data berdasarkan nilai pencarian agar trik ini berfungsi.
