Semua rahsia fungsi Excel VLOOKUP untuk mencari data dalam jadual dan mengekstraknya ke dalam yang lain

2024 Pengarang: Malcolm Clapton | [email protected]. Diubah suai terakhir: 2023-12-17 04:06

Selepas membaca artikel tersebut, anda bukan sahaja akan belajar cara mencari data dalam hamparan Excel dan mengekstraknya ke dalam hamparan lain, tetapi juga teknik yang boleh digunakan bersama dengan fungsi VLOOKUP.

Apabila bekerja dalam Excel, selalunya terdapat keperluan untuk mencari data dalam satu jadual dan mengekstraknya ke dalam yang lain. Jika anda masih tidak tahu bagaimana untuk melakukan ini, maka selepas membaca artikel itu, anda bukan sahaja akan belajar bagaimana untuk melakukan ini, tetapi juga mengetahui dalam keadaan apa anda boleh memerah prestasi maksimum daripada sistem. Kebanyakan teknik yang sangat berkesan yang harus digunakan bersama dengan fungsi VLOOKUP dipertimbangkan.

Walaupun anda telah menggunakan fungsi VLOOKUP selama bertahun-tahun, maka dengan tahap kebarangkalian yang tinggi artikel ini akan berguna kepada anda dan tidak akan meninggalkan anda acuh tak acuh. Sebagai contoh, sebagai pakar IT, dan kemudian ketua dalam IT, saya telah menggunakan VLOOKUP selama 15 tahun, tetapi saya hanya berjaya menangani semua nuansa sekarang, apabila saya mula mengajar orang Excel secara profesional.

VLOOKUP ialah singkatan untuk vmenegak NSpemeriksaan. Begitu juga, VLOOKUP - LOOKUP Menegak. Nama fungsi itu memberi petunjuk kepada kita bahawa ia mencari dalam baris jadual (menegak - melelakan ke atas baris dan membetulkan lajur), dan bukan dalam lajur (mendatar - mengulang ke atas lajur dan membetulkan baris). Perlu diingatkan bahawa VLOOKUP mempunyai seorang kakak - anak itik hodoh yang tidak akan menjadi angsa - ini adalah fungsi HLOOKUP. HLOOKUP, berbanding VLOOKUP, melakukan carian mendatar, tetapi konsep Excel (dan sememangnya konsep organisasi data) membayangkan bahawa jadual anda mempunyai lebih sedikit lajur dan lebih banyak baris. Itulah sebabnya kita perlu mencari mengikut baris berkali-kali lebih kerap daripada mengikut lajur. Jika anda terlalu kerap menggunakan fungsi HLO dalam Excel, maka berkemungkinan anda tidak memahami sesuatu dalam kehidupan ini.

Sintaks

Fungsi VLOOKUP mempunyai empat parameter:

= VLOOKUP (;; [;]), di sini:

- nilai yang diingini (jarang) atau rujukan kepada sel yang mengandungi nilai yang dikehendaki (sebahagian besar kes);

- rujukan kepada julat sel (tatasusunan dua dimensi), dalam lajur PERTAMA (!) yang mana nilai parameter akan dicari;

- nombor lajur dalam julat dari mana nilai akan dikembalikan;

- ini ialah parameter yang sangat penting yang menjawab soalan sama ada lajur pertama julat diisih dalam tertib menaik. Jika tatasusunan diisih, kami menentukan nilai BENAR atau 1, sebaliknya - SALAH atau 0. Jika parameter ini ditinggalkan, ia lalai kepada 1.

Saya yakin bahawa ramai daripada mereka yang mengetahui fungsi VLOOKUP sebagai mengelupas, selepas membaca penerangan parameter keempat, mereka mungkin berasa tidak selesa, kerana mereka sudah biasa melihatnya dalam bentuk yang sedikit berbeza: biasanya mereka bercakap tentang padanan tepat apabila mencari (FALSE atau 0) atau imbasan julat (TRUE atau 1).

Sekarang anda perlu menegangkan dan membaca perenggan seterusnya beberapa kali sehingga anda merasakan maksud apa yang dikatakan hingga akhir. Setiap perkataan adalah penting di sana. Contoh akan membantu anda memikirkannya.

Bagaimana sebenarnya formula VLOOKUP berfungsi?

• Jenis formula I. Jika parameter terakhir ditinggalkan atau dinyatakan sama dengan 1, maka VLOOKUP menganggap bahawa lajur pertama diisih dalam tertib menaik, jadi carian berhenti pada baris yang serta-merta mendahului baris yang mengandungi nilai yang lebih besar daripada yang dikehendaki … Jika tiada rentetan sedemikian ditemui, baris terakhir julat dikembalikan.

  

• Formula II. Jika parameter terakhir ditentukan sebagai 0, maka VLOOKUP mengimbas lajur pertama tatasusunan secara berurutan dan segera menghentikan carian apabila padanan tepat pertama dengan parameter ditemui, jika tidak, kod ralat # N / A (# N / A) dikembalikan.

  

Aliran kerja formula

VPR jenis I

VPR jenis II

Akibat untuk formula bentuk I

1. Formula boleh digunakan untuk mengedarkan nilai merentas julat.
2. Jika lajur pertama mengandungi nilai pendua dan diisih dengan betul, maka baris terakhir dengan nilai pendua akan dikembalikan.
3. Jika anda mencari nilai yang jelas lebih besar daripada yang boleh terkandung dalam lajur pertama, maka anda boleh mencari baris terakhir jadual dengan mudah, yang boleh menjadi agak berharga.
4. Pandangan ini akan mengembalikan ralat # N / A hanya jika ia tidak menemui nilai yang kurang daripada atau sama dengan yang dikehendaki.
5. Agak sukar untuk memahami bahawa formula mengembalikan nilai yang salah jika tatasusunan anda tidak diisih.

Akibat untuk formula jenis II

Jika nilai yang diingini berlaku beberapa kali dalam lajur pertama tatasusunan, maka formula akan memilih baris pertama untuk mendapatkan semula data berikutnya.

Prestasi VLOOKUP

Anda telah mencapai kemuncak artikel. Nampaknya, apakah perbezaannya jika saya menentukan sifar atau satu sebagai parameter terakhir? Pada asasnya, semua orang menunjukkan, sudah tentu, sifar, kerana ini agak praktikal: anda tidak perlu risau tentang menyusun lajur pertama tatasusunan, anda boleh segera melihat sama ada nilai itu ditemui atau tidak. Tetapi jika anda mempunyai beberapa ribu formula VLOOKUP pada helaian anda, anda akan perasan bahawa VLOOKUP II adalah perlahan. Pada masa yang sama, biasanya semua orang mula berfikir:

• Saya memerlukan komputer yang lebih berkuasa;
• Saya memerlukan formula yang lebih cepat, contohnya, ramai orang tahu tentang INDEX + MATCH, yang kononnya lebih cepat dengan 5-10%.

Dan beberapa orang berfikir bahawa sebaik sahaja anda mula menggunakan VLOOKUP jenis I dan memastikan lajur pertama diisih dengan apa-apa cara, kelajuan VLOOKUP akan meningkat 57 kali ganda. Saya menulis dengan perkataan - LIMA PULUH TUJUH KALI! Bukan 57%, tetapi 5,700%. Saya menyemak fakta ini dengan agak boleh dipercayai.

Rahsia kerja pantas itu terletak pada fakta bahawa algoritma carian yang sangat cekap boleh digunakan pada tatasusunan yang disusun, yang dipanggil carian binari (kaedah separuh, kaedah dikotomi). Jadi VLOOKUP jenis I menggunakannya, dan VLOOKUP jenis II mencari tanpa sebarang pengoptimuman sama sekali. Perkara yang sama berlaku untuk fungsi MATCH, yang termasuk parameter yang serupa, dan fungsi LOOKUP, yang hanya berfungsi pada tatasusunan yang diisih dan disertakan dalam Excel untuk keserasian dengan Lotus 1-2-3.

Kelemahan formula

Kelemahan VLOOKUP adalah jelas: pertama, ia hanya mencari dalam lajur pertama tatasusunan yang ditentukan, dan kedua, hanya di sebelah kanan lajur ini. Dan seperti yang anda fahami, mungkin berlaku bahawa lajur yang mengandungi maklumat yang diperlukan akan berada di sebelah kiri lajur yang akan kami cari. Gabungan formula INDEX + MATCH yang telah disebutkan tidak mempunyai kelemahan ini, yang menjadikannya penyelesaian paling fleksibel untuk mengekstrak data daripada jadual berbanding dengan VLOOKUP (VLOOKUP).

Beberapa aspek mengaplikasikan formula dalam kehidupan sebenar

Carian julat

Ilustrasi klasik bagi carian julat ialah tugas untuk menentukan diskaun mengikut saiz pesanan.

Cari rentetan teks

Sudah tentu, VLOOKUP bukan sahaja mencari nombor, tetapi juga untuk teks. Perlu diingat bahawa formula tidak membezakan antara kes aksara. Jika anda menggunakan kad bebas, anda boleh mengatur carian kabur. Terdapat dua kad bebas: "?" - menggantikan mana-mana satu aksara dalam rentetan teks, "*" - menggantikan mana-mana nombor mana-mana aksara.

Ruang pertempuran

Persoalan sering dibangkitkan bagaimana untuk menyelesaikan masalah ruang tambahan semasa mencari. Jika jadual carian masih boleh dikosongkan daripadanya, maka parameter pertama formula VLOOKUP tidak selalu bergantung kepada anda. Oleh itu, jika terdapat risiko menyumbat sel dengan ruang tambahan, maka anda boleh menggunakan fungsi TRIM untuk mengosongkannya.

Format data yang berbeza

Jika parameter pertama fungsi VLOOKUP merujuk kepada sel yang mengandungi nombor, tetapi yang disimpan dalam sel sebagai teks, dan lajur pertama tatasusunan mengandungi nombor dalam format yang betul, maka carian akan gagal. Keadaan sebaliknya juga mungkin. Masalahnya boleh diselesaikan dengan mudah dengan menterjemah parameter 1 ke dalam format yang diperlukan:

= VLOOKUP (−− D7; Produk! $ A $ 2: $ C $ 5; 3; 0) - jika D7 mengandungi teks dan jadual mengandungi nombor;

= VLOOKUP (D7 & ""); Produk! $ A $ 2: $ C $ 5; 3; 0) - dan sebaliknya.

Dengan cara ini, anda boleh menterjemah teks ke dalam nombor dalam beberapa cara sekaligus, pilih:

• Penafian berganda -D7.
• Pendaraban dengan satu D7 * 1.
• Penambahan sifar D7 + 0.
• Meningkatkan kuasa pertama D7 ^ 1.

Menukar nombor kepada teks dilakukan melalui penggabungan dengan rentetan kosong, yang memaksa Excel untuk menukar jenis data.

Bagaimana untuk menyekat # N / A

Ini sangat mudah dilakukan dengan fungsi IFERROR.

Contohnya: = IFERROR (VLOOKUP (D7; Produk! $ A $ 2: $ C $ 5; 3; 0); "").

Jika VLOOKUP mengembalikan kod ralat # N / A, maka IFERROR akan memintasnya dan menggantikan parameter 2 (dalam kes ini, rentetan kosong), dan jika tiada ralat berlaku, maka fungsi ini akan berpura-pura bahawa ia tidak wujud sama sekali, tetapi hanya terdapat VLOOKUP yang mengembalikan hasil normal.

Susunan

Selalunya mereka terlupa untuk membuat rujukan tatasusunan mutlak, dan apabila meregangkan tatasusunan "terapung". Ingat untuk menggunakan $ A $ 2: $ C $ 5 dan bukannya A2: C5.

Adalah idea yang baik untuk meletakkan tatasusunan rujukan pada helaian berasingan buku kerja. Ia tidak jatuh ke bawah, dan ia akan menjadi lebih selamat.

Idea yang lebih baik ialah mengisytiharkan tatasusunan ini sebagai julat bernama.

Ramai pengguna, apabila menentukan tatasusunan, menggunakan pembinaan seperti A: C, menentukan keseluruhan lajur. Pendekatan ini mempunyai hak untuk wujud, kerana anda diselamatkan daripada perlu menjejaki fakta bahawa tatasusunan anda termasuk semua rentetan yang diperlukan. Jika anda menambah baris pada helaian dengan tatasusunan asal, maka julat yang ditentukan sebagai A: C tidak perlu dilaraskan. Sudah tentu, binaan sintaksis ini memaksa Excel untuk melakukan lebih sedikit kerja daripada menentukan julat dengan tepat, tetapi overhed ini boleh diabaikan. Kita bercakap tentang seperseratus saat.

Nah, di ambang genius - untuk mengatur tatasusunan dalam bentuk.

Menggunakan fungsi COLUMN untuk menentukan lajur untuk diekstrak

Jika jadual di mana anda mengambil data menggunakan VLOOKUP mempunyai struktur yang sama seperti jadual carian, tetapi hanya mengandungi lebih sedikit baris, maka anda boleh menggunakan fungsi COLUMN () dalam VLOOKUP untuk mengira nombor lajur yang akan diambil secara automatik. Dalam kes ini, semua formula VLOOKUP akan sama (dilaraskan untuk parameter pertama, yang berubah secara automatik)! Ambil perhatian bahawa parameter pertama mempunyai koordinat lajur mutlak.

Mencipta kunci komposit dengan & "|" &

Sekiranya perlu untuk mencari mengikut beberapa lajur pada masa yang sama, maka perlu membuat kunci komposit untuk carian. Jika nilai pulangan bukan tekstual (seperti yang berlaku dengan medan "Kod"), tetapi angka, maka formula SUMIFS yang lebih mudah akan sesuai untuk ini dan kunci lajur komposit tidak diperlukan sama sekali.

Ini adalah artikel pertama saya untuk Lifehacker. Jika anda menyukainya, saya menjemput anda untuk melawat, dan juga dengan senang hati membaca dalam komen tentang rahsia anda menggunakan fungsi VLOOKUP dan sebagainya. terima kasih.:)