Formula generik
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Ringkasan
Dengan set data yang besar, VLOOKUP pencocokan tepat dapat menjadi lambat, tetapi anda dapat membuat VLOOKUP menjadi cepat dengan menggunakan dua VLOOKUPS, seperti yang dijelaskan di bawah.
Catatan:
- Sekiranya anda mempunyai sekumpulan data yang lebih kecil, pendekatan ini berlebihan. Gunakan hanya dengan set data yang besar apabila kelajuan benar-benar dikira.
- Anda mesti menyusun data mengikut nilai carian agar helah ini berjaya.
- Contoh ini menggunakan julat bernama. Sekiranya anda tidak mahu menggunakan julat bernama, gunakan rujukan mutlak.
VLOOKUP padanan tepat lambat
Apabila anda menggunakan VLOOKUP dalam "mode padanan tepat" pada sekumpulan besar data, ia benar-benar dapat memperlambat waktu pengiraan dalam lembaran kerja. Dengan, katakanlah, 50,000 rekod, atau 100,000 rekod, pengiraan boleh memakan masa beberapa minit.
Padanan tepat ditetapkan dengan memberikan SALAH atau sifar sebagai argumen keempat:
=VLOOKUP(val,data,col,FALSE)
Sebab VLOOKUP dalam mod ini lambat adalah kerana ia mesti memeriksa setiap rekod dalam set data sehingga perlawanan dijumpai. Ini kadang-kadang disebut sebagai carian linear.
VLOOKUP padanan anggaran sangat pantas
Dalam mod padanan anggaran, VLOOKUP sangat pantas. Untuk menggunakan VLOOKUP padanan anggaran, anda mesti menyusun data anda dengan lajur pertama (lajur carian), kemudian tentukan BENAR untuk argumen ke-4:
=VLOOKUP(val,data,col,TRUE)
(VLOOKUP lalai menjadi benar, yang merupakan lalai yang menakutkan, tetapi itu cerita lain).
Dengan set data yang sangat besar, mengubah menjadi VLOOKUP yang hampir sama dapat berarti peningkatan kecepatan yang dramatik.
Jadi, tidak ada otak, bukan? Urutkan data, gunakan padanan anggaran, dan anda sudah selesai.
Tidak begitu pantas (heh).
Masalah dengan VLOOKUP dalam mod "perkiraan hampir" adalah: VLOOKUP tidak akan menunjukkan ralat jika nilai pencarian tidak ada. Lebih buruk lagi, hasilnya mungkin kelihatan normal, walaupun salah sama sekali (lihat contoh). Bukan sesuatu yang ingin anda jelaskan kepada atasan anda.
Penyelesaiannya adalah dengan menggunakan VLOOKUP dua kali, dua kali dalam mod padanan anggaran:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Penjelasan
Contoh pertama VLOOKUP hanya mencari nilai carian ( id dalam contoh ini):
=IF(VLOOKUP(id,data,1,TRUE)=id
dan mengembalikan BENAR hanya apabila nilai carian dijumpai. Dalam kes itu,
formula menjalankan VLOOKUP sekali lagi dalam mod padanan anggaran untuk mendapatkan nilai dari jadual itu:
VLOOKUP(id,data,col,TRUE)
Tidak ada bahaya nilai pencarian yang hilang, kerana bahagian pertama formula sudah diperiksa untuk memastikannya ada.
Sekiranya nilai pencarian tidak dijumpai, bahagian "value if FALSE" dari fungsi IF dijalankan, dan anda boleh mengembalikan nilai yang anda suka. Dalam contoh ini, kami menggunakan NA () kami mengembalikan ralat # N / A, tetapi anda juga dapat mengembalikan mesej seperti "Hilang" atau "Tidak dijumpai".
Ingat: anda mesti menyusun data mengikut nilai carian agar helah ini berjaya.
