Vlookup dalam excel - formula mana yang terpantas?

Hari ini, kami akan melakukan vookup dalam Excel dengan beberapa formula yang berbeza, mengukur kelajuan pengiraan mereka dan menilai konsistensi, jadi anda boleh memilih pemenang anda.

Mencari maklumat di seluruh jadual yang berbeza adalah salah satu tugas yang paling biasa dalam Excel. Malangnya, fungsi VLOOKUP klasik terkenal ketika memproses kuasa dan fleksibiliti. Tidak hairanlah selama bertahun -tahun pengguna Excel telah menghasilkan penyelesaian mereka sendiri seperti formula perlawanan indeks. Nasib baik, Microsoft akhirnya menyedari bahawa VLOOKUP mempunyai terlalu banyak kelemahan dan mengeluarkan pengganti yang lebih kuat - fungsi XLOOKUP. Jadi, mana yang terbaik untuk digunakan?

Jika jadual anda hanya mempunyai beberapa baris sedozen, maka kemungkinan besar mana -mana kaedah akan cukup cepat, dan anda tidak akan melihat perbezaannya. Tetapi jika bilangan baris diukur dalam ribuan, pilihan fungsi yang betul adalah penting - perbezaan prestasi boleh lebih daripada 10 kali! Jadi, mari kita mulakan pemasa dan lihat siapa yang terpantas :)

• Data sumber
• Fungsi VLOOKUP
• Formula perlawanan indeks
• Formula perlawanan mengimbangi
• Fungsi xlookup
• Vlookup dalam jadual excel
• Vlookup dengan susunan dinamik
• Menarik perlawanan dengan pertanyaan kuasa
• Bonus Tambahan: Gabungan Jadual Wizard
• Ringkasan dan kesimpulan

Data sumber

Dalam semua contoh, kami menggunakan set data berikut:

• Jadual utama yang mengandungi 500,000 baris
• Jadual carian yang mengandungi 500 baris

Matlamat kami adalah untuk memadankan nama item dan menarik harga dari jadual carian ke dalam jadual utama.

Untuk setiap kaedah, kami akan memasukkan formula dalam C2 dan menyalinnya melalui C500001, mengukur masa yang diperlukan untuk mengira setengah juta sel.

Secara semulajadi, hasilnya bergantung kepada banyak faktor seperti prestasi CPU anda, saiz RAM, versi Excel, dan lain -lain. Dalam eksperimen ini, ia bukan nombor mutlak yang penting. Lebih penting untuk memahami prestasi setiap formula berbanding, kelebihan dan kelemahan mereka.

Contoh kami menganggap bahawa anda mempunyai pengetahuan asas mengenai fungsi -fungsi, dan kami tidak akan tinggal banyak pada sintaks mereka. Pautan ke tutorial mendalam dimasukkan untuk kemudahan anda.

Semua ujian dijalankan pada komputer riba Dell saya (Intel Core i5-8250U; RAM 16 GB) dengan aplikasi Microsoft 365 untuk perniagaan yang dipasang; Excel 32-bit, versi 2011, membina 13415, saluran beta. Ujian yang sama juga dilakukan oleh rakan sekerja saya di Excel 64-bit, dan beberapa hasilnya berbeza secara drastik!

Formula VLOOKUP

Ketika datang untuk mencari dan mengambil data yang sepadan dalam Excel, fungsi pertama yang masuk ke dalam fikiran adalah vookup lama yang baik. Sebenarnya, ia tidak begitu baik, tetapi kita akan bercakap ini sedikit kemudian :)

Vlookup (lookup_value, table_array, col_index_num, [range_lookup])

Untuk menarik harga dari jadual carian (E2: F501) ke jadual utama (A2: B500001), kami menentukan hujah -hujah berikut untuk formula VLOOKUP kami:

• Lookup_Value : B2 - Nilai untuk mencari dalam jadual carian.
• Table_array : $ e $ 3: $ f $ 501 - Sila perhatikan bahawa kami mengunci rujukan dengan tanda $ supaya mereka tidak beralih apabila menyalin formula.
• COL_INDEX_NUM : 2 - Data akan diambil dari lajur 2 ^ND jadual carian.
• Range_lookup : FALSE - Mencari perlawanan tepat.

Formula lengkap ialah:

=VLOOKUP(B2, $E$3:$F$501, 2, FALSE)

Formula di atas pergi ke C2, dan kemudian kita klik dua kali tanda tambah di sudut kanan bawah untuk menyalin formula di seluruh lajur. Pada saat mengklik dua kali, kami menjalankan jam randik dan melihat bahawa pengiraan ini mengambil masa 6.6 saat.

Apabila melakukan vlookup dalam lembaran kerja sebenar, ramai orang membekalkan seluruh lajur untuk Table_Array untuk memenuhi kemungkinan penambahan pada masa akan datang:

=VLOOKUP(B2, E:F, 2, FALSE)

Adakah ia mempengaruhi prestasi? Ya, ia tidak. Ia mengambil masa 14.2 saat untuk mengira keseluruhan lajur. Itu sukar untuk dipercayai, jadi saya diperiksa dua kali. Hasil yang sama - kurang daripada separuh kelajuan julat.

Selain menjadi pantas, formula anda juga perlu teguh dan tahan lama, bukan? Malangnya, Vlookup tidak dapat berbangga dengan kebolehpercayaan dan daya tahan.

Batasan VLOOKUP

Seperti yang telah disebutkan, Excel Vlookup mempunyai beberapa kekangan yang menjengkelkan. Yang paling penting adalah:

• Tidak dapat melihat kirinya. Fungsi VLOOKUP hanya dapat dilihat di lajur paling kiri dari array jadual dan mengembalikan maklumat dari kanan.
• Tidak bertahan dalam penyisipan lajur atau penghapusan. Kerana lajur pulangan ditentukan sebagai nombor indeks, formula VLOOKUP berhenti berfungsi sebaik sahaja lajur baru ditambah atau dikeluarkan dari array meja.
• Nilai carian terhad kepada 255 aksara.

Masa Pengiraan : Julat - 6.6 saat; keseluruhan lajur - 14.2 saat.

Tutorial mendalam : Contoh Excel Vlookup untuk pemula

Formula perlawanan indeks

Bagi kebanyakan pengguna, bentuk carian lanjutan dalam Excel adalah formula perlawanan indeks ajaib. Dalam bentuk generik, nampaknya ini:

Indeks ( return_column , padanan ( lookup_value , lookup_column , 0))

Untuk set data kami, formula mengambil borang ini:

=INDEX($F$2:$F$501, MATCH(B2, $E$2:$E$501, 0))

Seperti dengan VLOOKUP, sila ingat untuk menggunakan rujukan mutlak untuk mencari dan mengembalikan julat untuk memastikan formula itu menyalin dengan betul ke sel -sel di bawah.

Dengan kedua -dua jadual pada lembaran yang sama, perlawanan indeks dilakukan lebih perlahan daripada VLOOKUP (8.9 berbanding 6.6 saat).

Tetapi jika kita menggerakkan jadual carian ke lembaran kerja lain , formula mula bekerja lebih cepat (~ 5 saat), yang lebih baik daripada VLOOKUP.

Bagaimanapun, perlawanan indeks membentuk masa dengan beberapa faedah penting.

Kelebihan Perlawanan Indeks

• Boleh melihat dari kanan ke kiri. Yep, formula perlawanan indeks tidak peduli di mana lajur carian terletak, kerana tidak seperti VLOOKUP ia secara eksplisit mentakrifkan julat carian dan bukannya array meja.
• Imun ke penyisipan lajur dan penghapusan. Dengan perlawanan indeks, anda boleh menambah dan mengeluarkan lajur dengan selamat kerana anda menentukan julat pulangan, bukan nombor indeks.
• Tiada had untuk saiz nilai carian. Walaupun VLOOKUP terhad kepada 255 aksara, perlawanan indeks tidak mempunyai masalah dengan memproses rentetan yang lebih panjang.
• Boleh melakukan VLOOKUP dengan pelbagai kriteria seperti yang ditunjukkan dalam contoh yang dipautkan di atas.
• Boleh melakukan carian 2-dimensi dan mengembalikan nilai di persimpangan baris dan lajur tertentu.

Waktu Pengiraan : Ranges - 8.9 saat; keseluruhan lajur - 17.7 saat; Dari lembaran lain - 5.2 saat.

Tutorial mendalam : Formula Perlawanan Indeks di Excel

Formula perlawanan mengimbangi

Berikut adalah satu lagi formula untuk melihat secara menegak dalam Excel, yang bebas daripada banyak batasan VLOOKUP:

Offset ( lookup_table , match ( lookup_value , offset ( lookup_table , 0, n , baris ( lookup_table ), 1), 0) -1, m , 1, 1)

Di mana:

• N - adalah lajur carian yang mengimbangi yang menentukan berapa lajur yang akan bergerak dari awal jadual ke lajur carian.
• M - adalah lajur kembali mengimbangi yang menentukan berapa lajur yang akan bergerak ke lajur kembali.

Dalam kes kami, lajur carian mengimbangi (n) adalah 0 kerana kami mencari dalam lajur pertama, jadi tiada peralihan diperlukan. Offset lajur kembali (m) adalah 1 kerana perlawanan berada dalam lajur kedua, dan kita perlu memindahkan 1 lajur ke kanan untuk mendapatkannya:

=OFFSET($E$2:$F$501, MATCH(B2, OFFSET($E$2:$F$501, 0, 0, ROWS($E$2:$F$501), 1), 0) -1, 1, 1, 1)

Berbanding dengan penyelesaian terdahulu, formula terlalu rumit, bukan? Walau bagaimanapun, ia lebih cepat daripada perlawanan VLOOKUP atau indeks. Mengira 500 ribu baris mengambil masa kurang dari 3 saat! Pada keseluruhan lajur, Offset hanya sedikit lebih perlahan - 3.5 saat.

=OFFSET(E:F, MATCH(B2, OFFSET(E:F, 0, 0, ROWS(E:F), 1), 0) -1, 1, 1, 1)

Walau bagaimanapun, dalam Excel 64 -bit, hasilnya tidak begitu mengagumkan - sekitar 7.5 saat. Kenapa itu? Soalan yang baik kepada lelaki Microsoft :)

Kelebihan perlawanan mengimbangi

Selain dari kelajuan, formula ini mempunyai beberapa merit lain:

• Boleh melakukan vlookup kanan-ke-kiri dan hlookup atas.
• Boleh melakukan pencarian dua hala berdasarkan nilai lajur dan baris.
• Tidak pecah apabila lajur dimasukkan ke dalam atau dipadam dari jadual carian.

Kelemahan perlawanan mengimbangi

Sintaks kompleks.

Waktu Pengiraan : Ranges - 2.9 saat; keseluruhan lajur - 3.5 saat.

Tutorial mendalam :

• Fungsi mengimbangi dengan contoh formula
• Cara menggunakan fungsi perlawanan di excel

Formula XLLOOKUP

Pelanggan Microsoft 365 disediakan dengan fungsi baru dan lebih berkuasa untuk mencari maklumat dalam lembaran kerja mereka:

Xlookup (lookup_value, lookup_array, return_array, [match_mode], [search_mode], [if_not_found])

Untuk tujuan kami, kegagalan 3 hujah terakhir berfungsi dengan baik, jadi kami hanya menentukan 3 parameter pertama, yang diperlukan. Nama argumen adalah intuitif, dan saya percaya anda dapat memahami formula tanpa penjelasan tambahan:

=XLOOKUP(B2, $E$2:$E$501, $F$2:$F$501)

Kelebihan Xlookup

Berbanding dengan vlookup tradisional, fungsi xlookup mempunyai banyak penambahbaikan seperti:

• Sintaks yang lebih mudah dan lebih bermakna
• Keupayaan untuk mencari secara menegak dan mendatar ke mana -mana arah: kanan, kiri, bawah atau ke atas.
• Untuk data yang disusun, ia mempunyai mod carian binari khas yang jauh lebih cepat daripada carian biasa.
• Cari dalam susunan terbalik untuk mendapatkan kejadian terakhir.
• Keupayaan untuk mengembalikan pelbagai nilai.
• Mengendalikan lebih daripada satu keadaan seperti yang dijelaskan dalam Excel Xlookup dengan pelbagai kriteria.
• Terbina jika fungsi ralat.

Kekurangan Xlookup

Xlookup hanya boleh didapati di Excel 365 dan 2021. Dalam Excel 2019, Excel 2016 dan versi terdahulu, ia tidak disokong.

Dan sekarang, mari kita lihat betapa cepatnya fungsi baru ini. 11.2 saat - agak mengecewakan :(

Bagaimana jika kita menggunakan rujukan lajur dan bukannya julat? =XLOOKUP(B2, E:E, F:F)

24.5 saat. Tiada kata -kata ... hampir dua kali lebih perlahan seperti vlookup.

Masa Pengiraan : Ranges - 11.2 saat; keseluruhan lajur - 24.1 saat.

Tutorial Komprehensif : Excel Xlookup Fungsi dengan contoh

Vlookup dalam jadual excel

Seperti yang anda ketahui, data dalam jadual Excel boleh dirujuk dengan cara yang istimewa - dengan menggunakan nama jadual dan lajur dan bukannya alamat sel. Ini dipanggil rujukan berstruktur , dan saya tertanya -tanya jika ia mempunyai kesan ke atas kelajuan pengiraan.

Untuk memeriksa ini, mari kita menukar julat ke meja dan menguji semula formula kami.

Untuk kemudahan, jadual kami dinamakan main_table (A1: C500001) dan lookup_table (E1: F5001).

Untuk membuat rujukan jadual, mula menaip formula dalam sel pertama (C2), pilih sel dan julat yang anda ingin rujuk, dan Excel akan memasukkan rujukan berstruktur secara automatik.

Sebagai contoh, inilah caranya formula VLOOKUP seperti:

=VLOOKUP([@Item], Lookup_table, 2, FALSE)

Ciri -ciri hebat dari jadual Excel ialah sebaik sahaja anda memasuki formula dalam satu sel, ia segera dihuni di semua sel lain dalam lajur yang sama. Selain itu, jadual adalah secara dinamik dan berkembang secara automatik untuk memasukkan sebarang data baru yang anda taip di sebelah jadual.

Dalam jadual kami, formula VLOOKUP dikira dalam 2.3 saat, perlawanan indeks dalam 2.6 saat, mengimbangi dan perlawanan dalam 2.7 saat, dan XLOOKUP dalam 3.3 saat. Seperti yang anda lihat, kelajuan pengiraan meningkat dengan ketara berbanding dengan julat.

Formula disenaraikan di bawah untuk rujukan anda:

=INDEX(Lookup_table[Price], MATCH([@Item], Lookup_table[Item], 0))

=OFFSET(Lookup_table, MATCH([@Item], OFFSET(Lookup_table, 0, 0, ROWS(Lookup_table), 1), 0) -1, 1, 1, 1)

=XLOOKUP([@Item], Lookup_table[Item], Lookup_table[Price])

Cukup menarik, jadual Excel sangat cepat walaupun dengan rujukan biasa . Iaitu, jika anda menukar hanya julat pertama (A1: C500001) ke meja dan menggunakan formula VLOOKUP biasa untuk menarik data dari julat carian , keseluruhan lajur dalam jadual utama akan dikira dalam kira -kira 2.5 saat!

Kelajuan pengiraan : Dari 2.3 hingga 3.3 saat bergantung kepada formula.

Tutorial akhir-ke-akhir :

• Jadual Excel dengan contoh
• Rujukan berstruktur dalam jadual excel

Vlookup dengan susunan dinamik

Perubahan terobosan dalam enjin pengiraan Excel 365 yang berlaku pada Januari 2020 telah menambah sokongan untuk susunan dinamik yang dipanggil. Ringkasnya, ini adalah array yang boleh disesuaikan yang mengira secara automatik dan mengembalikan nilai ke dalam pelbagai sel berdasarkan formula yang dimasukkan dalam satu sel.

Salah satu perkara terbaik mengenai tatasusunan dinamik ialah mereka boleh digunakan dengan hampir mana -mana fungsi Excel tradisional. Untuk formula vlookup kami, ia akan kelihatan seperti ini:

=VLOOKUP(B2:B500001, E2:F500001, 2, FALSE)

Perbezaan dengan fungsi VLOOKUP klasik ialah anda membekalkan keseluruhan array carian untuk hujah pertama, bukan satu nilai carian. Kerana formula dimasukkan hanya dalam satu sel, anda tidak perlu bimbang tentang mengunci julat dengan rujukan mutlak.

Bagi prestasi, array dinamik berfungsi lebih cepat daripada jadual Excel! Setengah juta sel dipenuhi dengan hasilnya dengan segera: 1.8 saat - sangat mengagumkan!

Keputusan lain disenaraikan di bawah:

Perlawanan Indeks - 4.4 saat

=INDEX(F2:F501, MATCH(B2:B500001, E2:E501, 0))

XLOOKUP - 7.3 saat

=XLOOKUP(B2:B500001, E2:E501, F2:F501)

Hmm ... xlookup yang sepatutnya dinamik oleh reka bentuk melakukan lebih buruk daripada fungsi yang lebih lama. Pelik!

Kelajuan pengiraan : Dari 1.8 hingga 7.3 saat bergantung kepada formula.

Tutorial yang mendalam : Arahan, fungsi dan formula dinamik Excel

Menarik perlawanan dengan pertanyaan kuasa

Demi kesempurnaan, mari kita menguji satu penyelesaian yang lebih mungkin untuk tugas kami - pertanyaan kuasa. Sudah tentu, tidak betul untuk membandingkan pengiraan formula dengan mengemas kini pertanyaan, tetapi saya hanya ingin tahu yang lebih cepat :)

Langkah -langkah terperinci menggunakan pertanyaan kuasa diterangkan dalam tutorial berasingan yang disebutkan di bawah. Di sini, kami hanya akan menilai hasilnya:

Jadual yang digabungkan telah dimuatkan dari editor pertanyaan kuasa ke dalam Excel dalam 8.5 saat. Tidak seperti formula, pertanyaan tidak mengemas kini secara automatik. Selepas setiap perubahan dalam data sumber, anda perlu mengemas kini jadual yang dihasilkan secara manual dengan mengklik butang Refresh pada sama ada data atau tab pertanyaan . 500,000 baris kami disegarkan semula dalam kira -kira 7 saat. Tidak buruk, tetapi formula Excel boleh melakukan lebih baik. Memandangkan bahawa menubuhkan pertanyaan adalah jauh dari proses satu klik, ini mungkin kaedah terakhir yang saya akan gunakan, hanya jika tidak ada yang lain berfungsi.

Prestasi : Memuatkan ke Excel 8.5 saat; menyegarkan 7.6 saat

Tutorial mendalam :

• Cara Menggabungkan Jadual Dengan Kuasa Kuasa Excel
• Cara Menggunakan Pertanyaan Kuasa Dalam Excel - Contoh Praktikal
• Pertanyaan Kuasa dalam Tutorial Excel untuk Pemula

Bonus Tambahan: Gabungan Jadual Wizard

Pengguna Suite Ultimate kami mempunyai satu lagi alat dalam kotak alat Excel mereka untuk menggabungkan dua jadual berdasarkan lajur yang sama. Mari lihat bagaimana ia dibandingkan dengan Excel's One.

Untuk menjalankan Wizard Jadual Gabungan, klik butang Gabungkan Dua Jadual pada tab Data Cabebits . Dan kemudian, ikuti langkah -langkah penyihir, dan ia akan membimbing anda melalui proses.

Nah, ia mengambil alat kira -kira 3 saat untuk disiapkan. Tidak begitu buruk untuk setengah juta rekod!

Dengan melihat lebih dekat mesej di atas, anda mungkin mendapati bahawa tidak semua perlawanan telah dijumpai. Walau bagaimanapun, tidak bermakna bahawa alat itu cacat. Ia hanya membolehkan anda tahu bahawa beberapa item (nilai carian) tidak wujud dalam jadual carian. Fungsi VLOOKUP mengembalikan kesilapan #N/A dalam kes ini, sementara wizard Gabungan Tables meninggalkan sel kosong.

Prestasi : 3.2 saat

Maklumat lanjut : Gabungkan dua jadual di Excel

Ringkasan dan kesimpulan

Jika anda membaca dengan teliti walaupun semua contoh, maka kemungkinan besar anda telah membuat kesimpulan anda sendiri. Jika anda melangkau butiran, maka anda boleh mencari ringkasan cepat dalam jadual perbandingan ini:

Fungsi	Kelajuan pengiraan dalam beberapa saat
	Julat	Keseluruhan lajur	Jadual	Tatasusunan dinamik
Vlookup	6.6	14.2	2.3	1.8
Perlawanan indeks	8.9	17.7	2.6	4.4
Perlawanan mengimbangi	2.9	3.5	2.7	-
Xlookup	11.2	24.1	3.3	7.3
Pertanyaan kuasa	8.5
Gabungkan jadual	3.2

Di bawah ini, terdapat beberapa pemerhatian yang saya buat berdasarkan keputusan ujian. Mungkin, mereka juga berguna untuk anda.

• Walaupun semua batasan dan kelemahannya, VLOOKUP berfungsi dengan baik, terutamanya dengan array dinamik.
• Perlawanan indeks tidak secepat yang diharapkan. Kepada saya, ini kelihatan sangat pelik kerana ia memproses lajur individu, bukan array meja seperti vlookup.
• XlookUp mempunyai banyak keupayaan yang menakjubkan tetapi lebih perlahan daripada perlawanan VLOOKUP dan indeks pada set data yang besar. Mudah -mudahan, Microsoft akan meningkatkan prestasinya dalam versi masa depan.
• Perlawanan Offset adalah yang terpantas dalam Excel 32-bit. Tetapi kerana sintaks kompleksnya, terdapat peluang besar untuk membuat kesilapan. Juga, ia tidak berfungsi dengan array dinamik, sekurang -kurangnya saya tidak dapat memaksanya.
• Tidak masuk akal untuk mengira keseluruhan lajur melainkan benar -benar diperlukan. Ini menjadikan formula lebih daripada dua kali lebih perlahan.
• Excel Tables Rock! Untuk mendapatkan sebahagian besar kecemerlangan anda, gunakannya di mana mungkin.
• Arahan dinamik adalah masa depan.

Perlu diingat bahawa pemerhatian ini berdasarkan ujian saya dalam dinamik Excel 365, saya tidak mempunyai peluang untuk menguji dalam versi lain. Sekiranya anda melakukannya, komen anda dialu -alukan dan akan sangat dihargai!

Buku Kerja Amalan untuk Muat turun

Formula VLOOKUP terpantas di Excel (fail .xlsx, 74MB )

Atas ialah kandungan terperinci Vlookup dalam excel - formula mana yang terpantas?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!